The Java Advantage in MySQL

Today’s first session — “The Java Advantage in MySQL” by Philip Antoniades. I’ve seen Philip speak before (at the Boston MySQL User Group) so I knew it was going to be good.


Enterprise level Java
Connector/J
Bulk of the code in Connector/J code is that it’s tested and works with most major application servers.
Pure Java
SSL

Entirely in-house code base (java/mysql)
open source, gpl
created by Mark Matthews
need java 1.4 to compile
small footprint driver

can set up config to cache prepared statements.

Profiling tools for developers:
Time executions
Time prepared statements
log prepare vs. execute occurrences (are you executing more than preparing?)

Debugging tools for developers:
Logging — StandardLogger or roll your own

http://dev.mysql.com/doc/refman/5.0/en/cj-configuration-properties.html

profileSQL — trace execution times, including latency, to logger

bad SQL warnings — useUsageAdvisor

Developer extensions are light, less than 1% additional load

ReplicationDriver — substitute for standard driver for replication/cluster aware apps or use standard with options. Works for asynchronous replication and synchronous clustering. Basic round-robin load balancing. Automated load balancing and automatic failover (works [best] for readonly stuff on replication). If master and 3 slaves and one slave goes down it will automatically use another slave. If cluster, automatic failover, can do sticky or load balanced.

use Connection.setReadOnly(). (ie, I’m doing read only)

Connection con = ....
con.setReadOnly(true);
ResultSet rs = con.createStatement().executeQuery("select * from foo");

can setReadOnly(false) when you want to execute DML.

Driver string goes from
jdbc:mysql:\\DB1\appdb
to
jdbc:mysql\\DB1,slave1,slave2,slave3/appdb


Small Form Java (ie, desktop)
Goals — simplified maintenance, easy deployment, ease of use. instead of using a java db, use mysql with connector/J.

Connector/MXJ = wrapper around MySQL, platform neutral
what does it do?
1 jar file. auto-detects platform, auto-installs MySQL (has copies of ’em all), starts server on command/connect, stops on command/connect, 1-line db delete/server uninstall.

connect using
Connection conn=null;
try { conn=DriverManager.getConnection(url,userName,password)
} catch (Exception never){}

String url="jdbc:mysql:mxj://localhost:" + port + "\test" + "?" + "server.basedir=" + dbDir;

Therefore, easy to embed MySQL in a java application. .jar with Sun, Windows, Linux is 40MB. For smaller, you can make your own .jar.

Can configure to run always or on demand.

Can uninstall/data delete when application terminates (or is deleted).


jUDF
UDF’s in Java — ie, write UDF’s in Java instead of C++.
(would have to install Java server side, of course)
Also has hooks that could be extended to other langs. Imagine writing UDF’s in perl or PHP!
GPL available
Written by MySQL Engineers
linux-only
alpha status
not being worked on now, but you can cast your vote, because the engineers want to work on it (but it’s lower priority).

Future Directions for Java in MySQL
Next JDBC driver version is 5.0, to work with MySQL 5.0.
Will support major java frameworks, ie Struts, Hybernate
MySQL will continue to be the core Open Source java development database


I left the tutorial thinking, “wow! PHP can’t do that!”

Tangent: Carl Zimmer, “Parasite Rex” is a great book about parasites according to Philip.

Today’s first session — “The Java Advantage in MySQL” by Philip Antoniades. I’ve seen Philip speak before (at the Boston MySQL User Group) so I knew it was going to be good.


Enterprise level Java
Connector/J
Bulk of the code in Connector/J code is that it’s tested and works with most major application servers.
Pure Java
SSL

Entirely in-house code base (java/mysql)
open source, gpl
created by Mark Matthews
need java 1.4 to compile
small footprint driver

can set up config to cache prepared statements.

Profiling tools for developers:
Time executions
Time prepared statements
log prepare vs. execute occurrences (are you executing more than preparing?)

Debugging tools for developers:
Logging — StandardLogger or roll your own

http://dev.mysql.com/doc/refman/5.0/en/cj-configuration-properties.html

profileSQL — trace execution times, including latency, to logger

bad SQL warnings — useUsageAdvisor

Developer extensions are light, less than 1% additional load

ReplicationDriver — substitute for standard driver for replication/cluster aware apps or use standard with options. Works for asynchronous replication and synchronous clustering. Basic round-robin load balancing. Automated load balancing and automatic failover (works [best] for readonly stuff on replication). If master and 3 slaves and one slave goes down it will automatically use another slave. If cluster, automatic failover, can do sticky or load balanced.

use Connection.setReadOnly(). (ie, I’m doing read only)

Connection con = ....
con.setReadOnly(true);
ResultSet rs = con.createStatement().executeQuery("select * from foo");

can setReadOnly(false) when you want to execute DML.

Driver string goes from
jdbc:mysql:\\DB1\appdb
to
jdbc:mysql\\DB1,slave1,slave2,slave3/appdb


Small Form Java (ie, desktop)
Goals — simplified maintenance, easy deployment, ease of use. instead of using a java db, use mysql with connector/J.

Connector/MXJ = wrapper around MySQL, platform neutral
what does it do?
1 jar file. auto-detects platform, auto-installs MySQL (has copies of ’em all), starts server on command/connect, stops on command/connect, 1-line db delete/server uninstall.

connect using
Connection conn=null;
try { conn=DriverManager.getConnection(url,userName,password)
} catch (Exception never){}

String url="jdbc:mysql:mxj://localhost:" + port + "\test" + "?" + "server.basedir=" + dbDir;

Therefore, easy to embed MySQL in a java application. .jar with Sun, Windows, Linux is 40MB. For smaller, you can make your own .jar.

Can configure to run always or on demand.

Can uninstall/data delete when application terminates (or is deleted).


jUDF
UDF’s in Java — ie, write UDF’s in Java instead of C++.
(would have to install Java server side, of course)
Also has hooks that could be extended to other langs. Imagine writing UDF’s in perl or PHP!
GPL available
Written by MySQL Engineers
linux-only
alpha status
not being worked on now, but you can cast your vote, because the engineers want to work on it (but it’s lower priority).

Future Directions for Java in MySQL
Next JDBC driver version is 5.0, to work with MySQL 5.0.
Will support major java frameworks, ie Struts, Hybernate
MySQL will continue to be the core Open Source java development database


I left the tutorial thinking, “wow! PHP can’t do that!”

Tangent: Carl Zimmer, “Parasite Rex” is a great book about parasites according to Philip.