开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜