Friday, 8 August 2014

Mysql JDBC Issue

Hammered myself into a wall here. Turns out when I create my Java Entities from a Database Table (using NetBeans Wizards), I get the following:

@Table(name = "mm_log", catalog = "mmud", schema = "")
    @NamedQuery(name = "Log.findAll", query = "SELECT l FROM Log l"),
    @NamedQuery(name = "Log.findByName", query = "SELECT l FROM Log l WHERE = :name"),
    @NamedQuery(name = "Log.findByMessage", query = "SELECT l FROM Log l WHERE l.message = :message")
public class Log implements Serializable

Notice the catalog and the schema in the annotation for Table?

Now, it turns out1 that "schema" is unsupported in MySQL, and "catalog", apparently, is just a fancy way in MySQL of saying "database".

So, if I create a brand new database (call it "newmmud"), create a nice JDBC Connection Pool to it in my Glassfish server, yet this blasted Entity (Log, in the example) will still refer to the database "mmud"!

That's what I call confusing!

Here's the message, for posterity's sake:
“You're correct. For legacy reasons (including compatibility with ODBC, and SQL Server), JDBC's concept of "catalog" maps to MySQL's concept of "databases".

Starting in MySQL-4.1, "schema" is an alias for "database", but it doesn't act like a SQL-standard schema, so we don't support the concept in the JDBC driver.”
- Mark Matthews


[1] Forum - Re: catalog versus schema,137564,137629#msg-137629