I'm in the unfortunate position of building a Hibernate layer on top of a very messy existing database that can't be changed. The code below is from a JUnit TestCase designed to benchmark some fetches so I can test various strategies for loading data.

I've settled on a SQL Query to do what I need to in this case, since the legacy database schema is messy as hell and doesn't play well with Hibernate.

Unfortunately, it fails with a SQLGrammarException and a bunch of "Invalid Column" errors (see below). I've only used SQL Queries in Hibernate a little before, so I expect I'm missing a sublety someplpace.

I really appreciate any feedback!


Hibernate version: 2

Code:

This code is a quick benchmarking routine to test new versions of the SQL Query

Calendar tranDate = Calendar.getInstance();
tranDate.set(2003, 10, 1);

Query q = session.createSQLQuery("select {t.*}, {e.*} from dbo.FINANCIAL_TRAN {t}, dbo.TRAN_EXPLANATION {e} "
+ "where {t.date} = :date "
+ "and {e.transactionNum} = {t.transactionNum} "
+ "and {e.batchNum} = {t.batchNum} "
+ "and {e.accountId} = {t.accountId}",
new String[] { "t", "e" },
new Class[] {
FinancialTransaction.class,
FinancialTransactionExplanation.class }
);


Stopwatch watch = new Stopwatch();
watch.start(log);

for (int n = 0; n < 30; n++) {
q.setDate("date", tranDate.getTime());
List results = q.list();
log.info(tranDate.getTime() + ": " + results.size());
tranDate.add(Calendar.DAY_OF_YEAR, 1);
session.clear();
}

watch.check("Total time for fetches ", log, true);


Full stack trace of any exception that occurs:

net.sf.hibernate.exception.SQLGrammarException: error performing findBySQL
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3858)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at gov.doi.dtp.itrust.search.ExplanationTest.testExplanationSearch(ExplanationTest.java:65)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: Invalid column name 'Entry_Dt0_'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2708)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2150)
at net.sourceforge.jtds.jdbc.TdsCore.clearResponseQueue(TdsCore.java:687)
at net.sourceforge.jtds.jdbc.TdsCore.submitSQL(TdsCore.java:849)
at net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1062)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareSQL(ConnectionJDBC2.java:520)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:666)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:88)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3855)
... 17 more


Name and version of the database you are using: SQL Server 2003

The generated SQL (show_sql=true):

Hibernate: select t.Batch_Num as Batch_Num0_, t.Transaction_Num as Transact2_0_, t.Account_ID as Account_ID0_, t.Entry_Dt as Entry_Dt0_, t.Posting_Location_ID as Posting_5_0_, t.Action_Tx as Action_Tx0_, t.Explanation_Tx as Explanat7_0_, t.Cash_Effect_Amt as Cash_Eff8_0_, t.Suppress_Display_Fl as Suppress9_0_, t.Additional_Nm as Additio10_0_, t.Check_Num as Check_Num0_, t.Transaction_Type_Cd as Transac12_0_, t.Disbursement_Cd as Disburs13_0_, t.Receipt_Cd as Receipt_Cd0_, t.Portfolio_Num as Portfol15_0_, e.Batch_Num as Batch_Num1_, e.Transaction_Num as Transact2_1_, e.Account_ID as Account_ID1_, e.Entry_Dt as Entry_Dt1_, e.Posting_Location_ID as Posting_5_1_, e.Explanation_Tx_1 as Explanat6_1_, e.Explanation_Tx_2 as Explanat7_1_, e.Explanation_Tx_3 as Explanat8_1_, e.Explanation_Tx_4 as Explanat9_1_, e.Poster_Initials_Tx as Poster_10_1_, e.Continuation_Fl as Continu11_1_, e.Suppress_Display_Fl as Suppres12_1_, e.More_Explanation_Fl as More_Ex13_1_, e.Last_Update_Dt as Last_Up14_1_, e.Portfolio_Num as Portfol15_1_ from dbo.FINANCIAL_TRAN t, dbo.TRAN_EXPLANATION e where Entry_Dt0_ = ? and Transact2_1_ = Transact2_0_ and Batch_Num1_ = Batch_Num0_ and Account_ID1_ = Account_ID0_


Debug level Hibernate log excerpt:

17:45:59,694 INFO ExplanationTest:20 - Starting
17:45:59,725 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:45:59,733 ERROR JDBCExceptionReporter:58 - Invalid column name 'Entry_Dt0_'.
17:45:59,733 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:45:59,734 ERROR JDBCExceptionReporter:58 - Invalid column name 'Transact2_1_'.
17:45:59,735 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:45:59,735 ERROR JDBCExceptionReporter:58 - Invalid column name 'Transact2_0_'.
17:46:00,394 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,396 ERROR JDBCExceptionReporter:58 - Invalid column name 'Batch_Num1_'.
17:46:00,396 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,397 ERROR JDBCExceptionReporter:58 - Invalid column name 'Batch_Num0_'.
17:46:00,398 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,398 ERROR JDBCExceptionReporter:58 - Invalid column name 'Account_ID1_'.
17:46:00,399 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,402 ERROR JDBCExceptionReporter:58 - Invalid column name 'Account_ID0_'.
17:46:00,405 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,406 ERROR JDBCExceptionReporter:58 - Invalid column name 'Entry_Dt0_'.
17:46:00,406 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,407 ERROR JDBCExceptionReporter:58 - Invalid column name 'Transact2_1_'.
17:46:00,408 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,408 ERROR JDBCExceptionReporter:58 - Invalid column name 'Transact2_0_'.
17:46:00,409 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,409 ERROR JDBCExceptionReporter:58 - Invalid column name 'Batch_Num1_'.
17:46:00,410 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,410 ERROR JDBCExceptionReporter:58 - Invalid column name 'Batch_Num0_'.
17:46:00,411 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,412 ERROR JDBCExceptionReporter:58 - Invalid column name 'Account_ID1_'.
17:46:00,413 WARN JDBCExceptionReporter:57 - SQL Error: 207, SQLState: 42S22
17:46:00,414 ERROR JDBCExceptionReporter:58 - Invalid column name 'Account_ID0_'.

Bump.

Scouring MS-SQL information, but I can't figure out what problem MS-SQL has with "Entry_Dt0_" as a column name.