How to get a list of the columns (in order) in a database table using a Hibernate session?

Question

Hi,

I need to retrieve a list of the columns in a table in the order in which they appear. Is this possible using Hibernate? I've got a Hibernate session object, and I don't mind whether I can do it in Hibernate itself, or using JDBC.

The database is MySQL, and the equivalent SQL command I want to execute is:

"SHOW COLUMNS FROM " + tableName

I tried the following and Hibernate complained:

    Table table = object.getClass().getAnnotation(Table.class);
    String tableName = table.name();
    session.beginTransaction();
    // the following line causes an error
    List columns = session.createSQLQuery("SHOW COLUMNS FROM " + tableName).list();

    session.getTransaction().commit();
    session.close();

Thanks

-- edit --

My final working code is as follows.

/**
 * @param Object object A Hibernate POJO
 */
private ArrayList<String> getColumns(Object object)
{
    Table table = object.getClass().getAnnotation(Table.class);
    String tableName = table.name();
    Session session = this.sessionFactory.openSession();

    Statement statement = null;
    ResultSet resultSet = null;

    ArrayList<String> columns = new ArrayList<String>();
    String columnName;

    Connection connection = session.connection();
    try
    {
        statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        resultSet = statement.executeQuery("SHOW COLUMNS FROM " + tableName);
        while (resultSet.next())
        {
            columnName = resultSet.getString("field");
            columns.add(columnName);
        }
    }
    catch (SQLException ex)
    {
        this.logger.fatal("getColumns: Unable to get column names", ex);
        System.exit(1);
    }
    finally
    {
        session.close();
    }

    return columns;
}

Answer 1

I need to retrieve a list of the columns in a table in the order in which they appear. Is this possible using Hibernate?

Hibernate is not really made for such a need.

I've got a Hibernate session object, and I don't mind whether I can do it in Hibernate itself, or using JDBC.

That's good because I think you will have to use raw JDBC here. So get the underlying JDBC connection from the session and either use DatabaseMetaData or perform your custom query.

Connection conn = session.connection();
...