Courtesy from http://www.javacoding.net/articles/technical/java-mysql.html
=== Introduction
Many people requested a tutorial on how to access MySQL from Java.
The standard way of working against a relational database from Java is by using the JDBC API.
(the Java Database Connectivity API). This API allows you to access any relational database from Java, no matter whether you’re on Linux, Solaris or Windows. As long as there’s a JDBC driver for the database, you can use the same standard API to access it.
To give you a kickstart in using MySQL from your applications, we decided to provide a little tutorial on this subject.
enjoy!
=== Prerequisites
When you’re going to access a relational db from Java, you’ll need the following:
* JDBC API (part of the JDK)
* JDBC Driver for your database
(or the JDBC-ODBC bridge driver from Sun that comes with the JDK)
The JDBC driver for MySQL can be found at MySQL download page …
=== Using the JDBC Driver
In order to use the JDBC driver, you just need to put it on your classpath, or installing it as a Java extension library.
to put it on the classpath:
WINDOWS:
set classpath=c:\path\to\jar\mysql-connector-java-2.0.14-bin.jar;%classpath%
UNIX:
export CLASSPATH=/path/to/jar/mysql-connector-java-2.0.14-bin.jar:$CLASSPATH
(of course, you’ll need to change the folders to the folder you put the JAR into)
if you put it in your jre/lib/ext folder of your JDK/JRE, it will also be found automatically.
=== Preparing the test database
In order to let our little demo program do it’s job, you’ll need to create a database on the MySQL instance.
Startup the mySQL console (mysql.exe on windows, mysql.sh on unix).
use:
create database test;
you can use any other name you like for your database.
=== The demo program
/* import needed for JDBC access */
import java.sql.*;
/**
* MySQL Demo Program
* this program is just a little demonstration of the usage
* of MySQL in combination with Java JDBC
*
* http://www.javacoding.net
*/
public class TestMySQL {
public void test ( String host, String database ) throws Exception {
/* first, we’ll test whether the MySQL driver is installed */
testDriver ( );
/* then, we’ll get a connection to the database */
Connection con = getConnection ( host, database );
/* we create a table */
executeUpdate ( con, “create table test (id int not null,text varchar(20))” );
/* we insert some data */
executeUpdate(con,”insert into test (id,text) values (1,’first entry’)”);
executeUpdate(con,”insert into test (id,text) values (2,’second entry’)”);
executeUpdate(con,”insert into test (id,text) values (3,’third entry’)”);
/* then we’ll fetch this data */
executeQuery ( con, “select * from test” );
/* and we’ll destroy the table … */
executeUpdate ( con, “drop table test” );
/* finally, we close the database */
con.close ( );
}
/**
* Checks whether the MySQL JDBC Driver is installed
*/
protected void testDriver ( ) throws Exception {
try {
Class.forName ( “org.gjt.mm.mysql.Driver” );
System.out.println ( “MySQL Driver Found” );
} catch ( java.lang.ClassNotFoundException e ) {
System.out.println(“MySQL JDBC Driver not found … “);
throw ( e );
}
}
/**
* Returns a connection to the MySQL database
*
*/
protected Connection getConnection ( String host, String database )
throws Exception {
String url = “”;
try {
url = “jdbc:mysql://” + host + “/” + database;
Connection con = DriverManager.getConnection(url);
System.out.println(“Connection established to ” + url + “…”);
return con;
} catch ( java.sql.SQLException e ) {
System.out.println(“Connection couldn’t be established to ” + url);
throw ( e );
}
}
/**
* This method executes an update statement
* @param con database connection
* @param sqlStatement SQL DDL or DML statement to execute
*/
protected void executeUpdate ( Connection con, String sqlStatement )
throws Exception {
try {
Statement s = con.createStatement ( );
s.execute ( sqlStatement );
s.close ( );
} catch ( SQLException e ) {
System.out.println ( “Error executing sql statement” );
throw ( e );
}
}
/**
* This method executes a select statement and displays the result
* @param con database connection
* @param sqlStatement SQL SELECT statement to execute
*/
protected void executeQuery( Connection con, String sqlStatement )
throws Exception {
try {
Statement s = con.createStatement ( );
ResultSet rs = s.executeQuery( sqlStatement );
while ( rs.next ( ) ) {
String id = ( rs.getObject (“id”).toString() );
String text = ( rs.getObject (“text”).toString() );
System.out.println ( “found record : ” + id + ” ” + text );
}
rs.close ( );
} catch ( SQLException e ) {
System.out.println ( “Error executing sql statement” );
throw ( e );
}
}
/**
* This one is used to start the program.
*/
public static void main ( String args[] ) throws Exception {
if ( args.length == 2 ) {
new TestMySQL ( ).test ( args[0], args[1] );
} else {
System.out.println ( “usage: java TestMySQL host database” );
}
}
}
=== Compiling and running the test program
Compile the test program:
javac TestMySQL.java
Run it by the following command:
java TestMySQL localhost test
(the first argument is the host with the MySQL server, the second argument is the name of the database you created for our test program).
If an error occurs, the most probable reason is that the MySQL JDBC driver is not on your classpath, so it can’t be found. Refer to our prior explanation for details on how to fix this.
=== Where to go next
Now that you have a little JDBC-MySQL example working with this tutorial, you can explore further by:
* Reading the MySQL and Java Developer’s Guide written by Mark Matthews
* Playing with the example (change it a bit, see what happens…)
* Read through the JDBC specs to learn the API (can be found at java.sun.com
enjoy !
if you have some questions/remarks regarding this article, please feel free to post them at our forums…