SQL server 2008 - how to get column name and value together?
I'm not sure if it's possible..
I'm using SQL server 2008 and Java.
I have a table
--------------
| users |
--------------
| name |
| address |
| password |
|____________|
Then I do:
SELECT name, address, password FROM users;
I got all values from each column.
But wh开发者_高级运维at if I ALSO want to get column names too.
When query result is returned, I want to know which column is being queried.
How do I get value and columns together? Is it possible without changing the query using Java?
code
em = EmProvider.getInstance().getEntityManagerFactory().createEntityManager();
Query query = em.createNativeQuery("SELECT name, address, password, birthday FROM users");
//query result is stored in list consists of object array
List<Object[]> out = query.getResultList();
//so result is like this
List<
[john, 1st ave, 1234, 12/12/2010]
[mike, 2st ave, 1111, 12/11/2011]
>
//now I create JSON
{name:john, address:1st ave, birthday....}
But the query could change so when I construct JSON I don't want to hard-code "name" or "address".
The column names come from the ResultSetMetaData.
For example:
String sql = "Select * from Activity";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery( sql );
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
// Get column names
for (int i = 1; i <= columns; i++)
{
columnNames.addElement( md.getColumnName(i) );
}
// Get row data
while (rs.next())
{
Vector row = new Vector(columns);
for (int i = 1; i <= columns; i++)
{
row.addElement( rs.getObject(i) );
}
data.addElement( row );
}
You can just do this - Assuming you need columnname followed by columnvalue -
SELECT "name" as column1, name, "address" as column2, address, "password" as column3, password FROM users;
精彩评论