Hello,
I have a (hopefully!) simple problem that I hope that someone with more experience than me will be able to solve quickly.
I am updating my first Hibernate application to use a remote MySQL database rather than a local one. The two database are identical (the remote version was created by "restoring" a backup copy of the local one. The problem is that although Hibernate can apparently provide the schema definition for the remote database, when it comes to accessing the data in a table, it throws a table not found error.
Here's some demonstration code. My intention is, for the local and remote databases in turn, to:
1. Connect to the database
2. Demonstrate the existence of the relevant table using generateSchemaCreationScript()
3. Execute a simple query on the table.
Note that, for obvious reasons, I've obscured usernames, passwords and URLs.
The problem occurs regardless of whether I test the local or remote connection first.
Hibernate version: 3.2
MySQL version: 5.0 (both local and remote)
IDE: Netbeans 5.5.1
Thanks in advance for your help.
John
Example code:
Code:
package test;
import java.util.ArrayList;
import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Expression;
import org.hibernate.dialect.MySQLDialect;
public class Main
{
public Main()
{
//Set up logging for Hibernate
BasicConfigurator.configure();
Logger.getLogger("org.hibernate").setLevel(Level.WARN);
//Run the tests
System.out.println("Testing local connection...");
test(*******, *******, "jdbc:mysql://localhost:3306/*****");
System.out.println("\nTesting remote connection...");
test(*******, *******, "jdbc:mysql://*********:3307/*****");
}
public void test(String user, String password, String url)
{
Configuration cfg = new Configuration();
SessionFactory sf;
Session session;
String[] sArray;
Criteria crit;
ArrayList<DictionaryEntry> list;
System.out.println("Connecting to database...");
cfg.addResource("\\test\\DictionaryEntry.hbm.xml");
cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLMyISAMDialect");
cfg.setProperty("hibernate.connection.driver_class","com.mysql.jdbc.Driver");
cfg.setProperty("hibernate.connection.url", url);
cfg.setProperty("hibernate.connection.username", user);
cfg.setProperty("hibernate.connection.password", password);
sf = cfg.buildSessionFactory();
System.out.println("Checking that tables exist...");
session = sf.openSession();
sArray = cfg.generateSchemaCreationScript(new MySQLDialect());
for (String s: sArray) System.out.println(s);
System.out.println("Testing a search...");
crit = session.createCriteria(DictionaryEntry.class);
crit.add(Expression.like("word", "C_T"));
list = (ArrayList<DictionaryEntry>) crit.list();
for (DictionaryEntry e: list) System.out.println(e.getWord());
session.close();
}
public static void main(String[] args)
{
Main m = new Main();
}
}
Output:
Code:
init:
deps-jar:
Compiling 1 source file to F:\Insight\java\Test\build\classes
Note: F:\Insight\java\Test\src\test\Main.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
compile:
run:
Testing local connection...
Connecting to database...
Checking that tables exist...
create table WORDS (ID bigint not null, WORD varchar(255), ENTRY varchar(255), IDX varchar(255), LEN integer, WORD_COUNT integer, LENGTH_INDICATION varchar(255), primary key (ID))
Testing a search...
cat
cit
cot
cut
Testing remote connection...
Connecting to database...
Checking that tables exist...
create table WORDS (ID bigint not null, WORD varchar(255), ENTRY varchar(255), IDX varchar(255), LEN integer, WORD_COUNT integer, LENGTH_INDICATION varchar(255), primary key (ID))
Testing a search...
16 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1146, SQLState: 42S02
16 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Table '********.WORDS' doesn't exist
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at test.Main.test(Main.java:60)
at test.Main.<init>(Main.java:34)
at test.Main.main(Main.java:66)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'tilebarn_xword.WORDS' doesn't exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
... 8 more
Java Result: 1
BUILD SUCCESSFUL (total time: 12 seconds)
Login
FAQ
Search