Can't get column name when using HibernateTemplate

Question

I'm using Spring-orm and HibernateTemplate to execute a native SQL query (DB is Oracle 11 for the reference), like this:

@Override public List<Object> executeNativeQuery(final String queryStr, final Map<String, String> params) { List<Object> results = this.template.execute(new HibernateCallback<List<Object>>() { @Override public List<Object> doInHibernate(Session session) throws HibernateException, SQLException { // Get the query Query query = session.createSQLQuery(queryStr); // Assign parameters to the query, if any if (params != null) { for (Map.Entry<String, String> entry : params.entrySet()) { query.setString(entry.getKey(), entry.getValue()); } } // fire the query @SuppressWarnings("unchecked") List<Object> res = query.list(); return res; } }); return results; }

I've managed to successfully execute the query and get the results back. But I couldn't figure out a way to also get the resulting column names, and I'm starting to think that's not possible using this approach. My problem is that I have to execute a query that comes from user input and I have no clues about parameter names.

Any ideas?

loscuropresagio · Accepted Answer · 2012-05-18 12:51:48Z

I finally found a way through it, so I post it hoping it'll be useful for others. I was doing it the wrong way, the correct way (at least for what my needs are) is to use doWork.

instad of:

session.createSQLQuery(queryStr);

I had to get the connection like this:

session.doWork(new Work() { @Override public void execute(Connection con) throws SQLException { try { Statement st = con.createStatement(); ResultSet rs = st.executeQuery(queryStr); ResultSetMetaData md = rs.getMetaData(); int col = md.getColumnCount(); System.out.println("Number of Column : " + col); System.out.println("Columns Name: "); for (int i = 1; i <= col; i++) { String col_name = md.getColumnName(i); System.out.println(col_name); } } catch (SQLException s) { System.out.println("SQL statement is not executed!"); } } });

Olle Sjögren · Accepted Answer · 2012-11-22 07:43:32Z

Try the following to get column names in Hibernate:

public ArrayList<String> getTableDesc(String tableName){ System.out.println("getFieldNames:start"+tableName); Object[] a; List<Object[]> fieldNames = new ArrayList<Object[]>(); ArrayList<String> tabFieldNames = new ArrayList<String>(); Session session = getHibernateTemplate().getSessionFactory().openSession(); try{ String queryStr = "desc "+tableName; fieldNames = (List<Object[]>) session.createSQLQuery(queryStr).list(); for(int i=0;i<fieldNames.size();i++){ a = fieldNames.get(i); tabFieldNames.add(a[0].toString()); } } catch(Exception e){ System.out.println("exception "+e); } finally{ session.close(); } System.out.println("getFieldNames:end"+tabFieldNames.toString()); return tabFieldNames; }

fmucar · Accepted Answer · 2012-05-18 11:08:21Z

0

You can use ResultTransformer class to map the query result to an entity class.

http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/querysql.html#d0e17313

EDIT 1:

for( int i= 0; i< ((Object[])res.get(0)).length ; i++){ //do something with data res.get(0)[i] }

edited May 18, 2012 at 11:08

answered May 18, 2012 at 10:52

fmucar

14.6k2 gold badges48 silver badges50 bronze badges

3 Comments

loscuropresagio Over a year ago

That's not what I'm looking for, unfortunately. To use ResultTransformer you must provide a bean, which means that I know in advance how many colums will the query return, which I don't.

loscuropresagio Over a year ago

the fact is that res does not contain information about the columns, just the data returned by the query.

loscuropresagio Over a year ago

Thanks @fmucar, i finally found an answer and I posted it.

Collectives™ on Stack Overflow

Can't get column name when using HibernateTemplate

3 Answers 3

Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Related