MySQL and Java
November 2, 2009
1 Requirements
You will need:1. java and javac
2. MySQL installed. Directions for installing MySQL on CSIF machines can be found at
3. MySQL JDBC driver. You can download it from http://dev.mysql.com/downloads/connector/
j/3.1.html
Extract mysql-connector-java-3.1.12-bin.jar (or the latest version you have)
from the Connector/J archive to your home directory, you will not need the other
files.
2 Setting up the tutorial database
In this section we will create a new database, a new user, and a very simple table. MySQL
has a two level directory like hierarchy for keeping databases and tables. At the root there
is MySQL; under root you can only create “databases .” Database is almost like a directory,
you can create “tables” under a database. Follow the steps listed below.
1. Start the mysql server (follow the CSIF MySQL tutorial).
2. Check if mysql server is running.
$ mysqladmin -u root -p status
Uptime: 434 Threads: 1 Questions: 86 Slow queries: 0 . . .
3. Start the mysql client. We will use the command line client to create a new database,
a new user and a table in the new database.
1
Code:
(a) $ mysql -u root -p Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql> (b) Create a new database named tutorial. mysql> CREATE DATABASE tutorial; Query OK, 1 row affected (0.06 sec) (c) Create a user with all privileges on this database. The user name will be tutorialuser and the password will be 123456. Although this is NOT good practice, it will suffice. mysql> GRANT ALL ON tutorial.* TO tutorialuser@’%’ -> IDENTIFIED BY ’123456’; mysql> GRANT ALL ON tutorial.* TO tutorialuser@’localhost’ -> IDENTIFIED BY ’123456’; (d) Quit mysql client. We’ll reconnect as tutorialuser to the ecs160tutorial database to setup a table. mysql> quit Bye $ mysql -u tutorialuser -p tutorial Enter password: Welcome. . . ... mysql> (e) Now we will create a simple table with two columns, name and last name. mysql> CREATE TABLE simple_table (name CHAR(128), last_name CHAR(128)); Query OK, 0 rows affected (0.01 sec) show tables command will list all the tables created in this database. mysql> show tables; +--------------------------+ | Tables_in_tutorial | +--------------------------+ | simple_table | +--------------------------+ 1 row in set (0.00 sec)
I think it is a good idea to create a new database and user for your project as we did in this
tutorial. In the next section, I’ll describe how to connect to the ecs160tutorial database
from a Java program and execute simple queries.
2
3 Connecting to a MySQL database from a Java program
using the Connector/J JDBC driver
using the Connector/J JDBC driver
I assume that you downloaded and installed Connector/J. If you haven’t done so, read
section 1 for the requirements.
You can connect to the MySQL database in two steps. Those steps are detailed below.
1. First load the driver.
Code:
Class driver_class=null;
try {
driver_class = Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
System.out.println("Found driver " + driver_class);
requests to mysql databases.
2. Next step is to connect to the MySQL server and the tutorial database. Recall
that the user name is tutorialuser and the password is 123456.
Code:
Connection connection=null;
try {
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/","tutorialuser","123456");
} catch (SQLException e) {
e.printStackTrace();
}
try {
System.out.println
("Established connection to "+ connection.getMetaData().getURL());
} catch (SQLException e1) {
e1.printStackTrace();
}
The first argument is the URL of the server; URLs always start with jdbc:mysql://
and followed by the server address and the database name. Therefore, if you are
running the MySQL server on a different machine you should replace localhost with
the correct machine address, either name or IP address. Moreover, you’ll need to
3
replace 3306 with the number of the port your MySQL server is listening on. Next
component of the URL is the database name. The second argument is the user name
and the last one is the password.
Next, we will switch back to the mysql client to populate simple_table.
1. Connect to the database using the mysql client.
Code:
$ mysql -u tutorialuser -p ecs160tutorial Enter password: Welcome. . . ... mysql>
Code:
mysql> INSERT INTO simple_table VALUES ("ABCDE","FGHIJ");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO simple_table VALUES ("QWERTY","UIOP");
Query OK, 1 row affected (0.00 sec)
below.
[codemysql> SELECT * from simple_table;
+----------+-------------------+
| name | last_name |
+----------+-------------------+
| ABCDE| FGHIJ |
| QWERTY| UIOP |
+----------+-------------------+
2 rows in set (0.00 sec)[/code]
Now, we will execute the same SELECT query from our Java program.
1. We will use the connection to create an empty statement.
Code:
statement = connection.createStatement();
Code:
statement.execute("SELECT * FROM simple_table");
4
Code:
ResultSet resset = statement.getResultSet();
4. We are ready to print the result of the query. The result set returned by the statement
initially points before the first row, thus you must call next to advance to the first
row. See the code snippet below.
Code:
System.out.println("Row Name Last_Name");
while(resset.next())
{
System.out.print(resset.getRow());
System.out.print(" " + resset.getString("name"));
System.out.println(" " + resset.getString("last_name"));
}
resset.close();
types of the columns of simple_table; they are name and last_name and both are
type string. Therefore, we will use getString (remember column type) method with
the column names.
The output should be similar to the one below.
Code:
Row Name Last_Name 1 ABCDE FGHIJ 2 QWERTYUIOP
In this tutorial I explained, using MySQL, how to create a database, a user, and a simple
table. I also explained how to connect to a MySQL database from a Java program and
execute queries. The Java program I used as the example can be found in the appendix.
You can use javac to compile the program. Don’t forget to change the host address and the
port number. To run it, you will need to pass -classpath option:
java -classpath /home/<user_name>/mysql-connector-java-3.1.12-bin.jar:$CLASSP ATH:. Main
Code:
/**
*
* @author Rahulfl
*/
import java.sql.*;
public class DbConnector {
private Connection conn = null;
private ResultSet rs = null;
private Statement stmt = null;
public Connection getConnection()
{
try {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException cnfe) {
System.out.println("Couldn't find the driver!");
}
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/tutorial","tutorialuser","123456");
} catch (SQLException e) {
System.out.println("Could not connect");
e.printStackTrace();
} catch (Exception e) {
}
return conn;
}
public void closeConnection()
{
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch(SQLException se) { }
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch(SQLException se) { }
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch(SQLException se) { }
}
@Override
protected void finalize()
{
this.closeConnection();
}
public boolean executeQuery(String sqlStatement)
{
stmt = null;
boolean ret = false;
if(conn == null) conn = getConnection();
try {
stmt = conn.createStatement();
stmt.executeQuery(sqlStatement);
ret = true;
} catch (SQLException se){
System.out.println("" + se.getMessage());
ret = false;
}
finally {
closeConnection();
}
return ret;
}
public boolean executeUpdate(String sqlStatement)
{
//LogWriter lw = new LogWriter();
String firstword = sqlStatement.trim().substring(0, 6);
if(firstword.equals("insert") || firstword.equals("INSERT"));
// lw.writeLog(sqlStatement,"INSERT_LOGS");
else
if(firstword.equals("update") || firstword.equals("UPDATE"))
// lw.writeLog(sqlStatement,"UPDATE_LOGS");
stmt = null;
boolean ret = false;
if(conn == null) conn = getConnection();
try {
conn.setAutoCommit(true);
stmt = conn.createStatement();
stmt.executeUpdate(sqlStatement);
conn.commit();
ret = true;
} catch (SQLException se){
System.out.println("" + se.getMessage());
ret = false;
}
finally {
closeConnection();
}
return ret;
}
public ResultSet getResultSet(String sqlStatement)
{
rs = null;
stmt = null;
if(conn == null ) conn = getConnection();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sqlStatement);
return rs;
} catch (SQLException se){ }
return null;
}
}