MySQL Java tutorial:
This is a Java tutorial for the MySQL database. It covers the basics of
MySQL programming with Java. In this tutorial, we use the
MySQL Connector/J
driver. It is the official JDBC driver for MySQL.
JDBC
JDBC is an API for the Java programming language that defines
how a client may access a database. It provides methods for querying and
updating data in a database. JDBC is oriented towards relational databases. From
a technical point of view, the API is as a set of classes in the java.sql
package. To use JDBC with a particular database, we need a JDBC driver for that
database.
About
MySQL database
MySQL is a leading open source
database management system. It is a multi user, multithreaded database
management system. MySQL is especially popular on the web. It is one of the
parts of the very popular LAMP platform. Linux, Apache, MySQL, PHP.
Currently MySQL is owned by Oracle. MySQL database is available on most important
OS platforms. It runs under BSD Unix, Linux, Windows or Mac. Wikipedia and
YouTube use MySQL. These sites manage millions of queries each day. MySQL comes
in two versions. MySQL server system and MySQL embedded system.
Before
we start
For this tutorial, we need to have
several libraries installed. We need to install mysql-server and mysql-client
packages. The first package has the MySQL server and the second one contains,
among others, the mysql monitor tool. We need to install the JDK, Java
Development Kit, for compiling and running Java programs. Finally, we need the MySQL
Connector/J driver. If you are using Netbeans IDE, than you have already
the driver at hand. Inside the Projects tab, right click on the Libraries node
and select Add Library option. From the list of options, select MySQL JDBC
Driver.
Figure: Netbeans project libs
$
javac zetcode/Version.java
$
java -cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode.Version
5.5.9
Assuming, that you have put the
connector jar file into the lib directory and using package zetcode, you
compile and run the first example this way.
If you don't already have MySQL
installed, we must install it.
$
sudo apt-get install mysql-server
This command installs the MySQL
server and various other packages. While installing the package, we are
prompted to enter a password for the MySQL root account.
Next, we are going to create a new
database user and a new database. We use the mysql client.
$
service mysql status
mysql
start/running, process 1238
We check if the MySQL server is
running. If not, we need to start the server. On Ubuntu Linux, this can be done
with the service mysql start command.
$
mysql -u root -p
Enter
password:
Welcome
to the MySQL monitor. Commands end with
; or \g.
Your
MySQL connection id is 30
Server
version: 5.0.67-0ubuntu6 (Ubuntu)
Type
'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
SHOW DATABASES;
+--------------------+
|
Database |
+--------------------+
|
information_schema |
|
mysql |
+--------------------+
2
rows in set (0.00 sec)
We use the mysql monitor
client application to connect to the server. We connect to the database using
the root account. We show all available databases with the SHOW DATABASES
statement.
mysql>
CREATE DATABASE testdb;
Query
OK, 1 row affected (0.02 sec)
We create a new testdb
database. We will use this database throughout the tutorial.
mysql>
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query
OK, 0 rows affected (0.00 sec)
mysql>
USE testdb;
Database
changed
mysql>
GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query
OK, 0 rows affected (0.00 sec)
mysql>
quit;
Bye
We create a new database user. We
grant all privileges to this user for all tables of the testdb database.
MySQL
version
If the following program runs OK,
then we have everything installed OK. We check the version of the MySQL server.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Version {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(url, user, password);
st = con.createStatement();
rs = st.executeQuery("SELECT
VERSION()");
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Version.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Version.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
We connect to the database and get
some info about the MySQL server.
String
url = "jdbc:mysql://localhost:3306/testdb";
This is the connection url for the
MySQL database. Each driver has a different syntax for the url. In our case, we
provide a host, a port and a database name.
con
= DriverManager.getConnection(url, user, password);
We establish a connection to the
database, using the connection url, user name and password.
st
= con.createStatement();
The createStatement()
method of the connection object creates a Statement object
for sending SQL statements to the database.
rs
= st.executeQuery("SELECT VERSION()");
The createStatement()
method of the connection object executes the given SQL statement, which returns
a single ResultSet object. The ResultSet is a
table of data returned by a specific SQL statement.
if
(result.next()) {
System.out.println(result.getString(1));
}
A ResultSet object
maintains a cursor pointing to its current row of data. Initially the cursor is
positioned before the first row. The next() method moves the cursor to the next
row. If there are no rows left, the method returns false. The getString() method retrieves the value of a specified column. The first
column has index 1.
}
catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Version.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
In case of an exception, we log the
error message. For this console example, the message is displayed in the
terminal.
try
{
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
...
Inside the finally block, we close
the database resources. We also check if the objects are not equal to null.
This is to prevent null pointer exceptions. Otherwise we might get a NullPointerException, which would terminate the application and leave the
resources not cleaned up.
}
catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Version.class.getName());
lgr.log(Level.WARNING, ex.getMessage(),
ex);
}
We log an error message, when the
resources could not be closed.
java
-cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Version
5.5.9
This is the output of the program on
my system.
Creating
and populating tables
Next we are going to create database
tables and fill them with data. These tables will be used throughout this
tutorial.
DROP
TABLE IF EXISTS Books, Authors, Testing, Images;
CREATE
TABLE IF NOT EXISTS Authors(Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(25)) ENGINE=InnoDB;
CREATE
TABLE IF NOT EXISTS Books(Id INT PRIMARY KEY AUTO_INCREMENT,
AuthorId INT, Title VARCHAR(100),
FOREIGN KEY(AuthorId) REFERENCES
Authors(Id) ON DELETE CASCADE)
ENGINE=InnoDB;
CREATE
TABLE IF NOT EXISTS Testing(Id INT) ENGINE=InnoDB;
CREATE
TABLE IF NOT EXISTS Images(Id INT PRIMARY KEY AUTO_INCREMENT,
Data MEDIUMBLOB);
INSERT
INTO Authors(Id, Name) VALUES(1, 'Jack London');
INSERT
INTO Authors(Id, Name) VALUES(2, 'Honore de Balzac');
INSERT
INTO Authors(Id, Name) VALUES(3, 'Lion Feuchtwanger');
INSERT
INTO Authors(Id, Name) VALUES(4, 'Emile Zola');
INSERT
INTO Authors(Id, Name) VALUES(5, 'Truman Capote');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(1, 1, 'Call of the Wild');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(2, 1, 'Martin Eden');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(3, 2, 'Old Goriot');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(4, 2, 'Cousin Bette');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(5, 3, 'Jew Suess');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(6, 4, 'Nana');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(7, 4, 'The Belly of Paris');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(8, 5, 'In Cold blood');
INSERT
INTO Books(Id, AuthorId, Title) VALUES(9, 5, 'Breakfast at Tiffany');
We have a books.sql file. It creates
four database tables, Authors, Books, Testing and Images. Three tables are of
InnoDB type. InnoDB databases support foreign key constraints and transactions.
We place a foreign key constraint on the AuthorId column of the Books table. We
fill the Authors and Books tables with initial data.
mysql>
source books.sql
Query
OK, 0 rows affected (0.07 sec)
Query
OK, 0 rows affected (0.12 sec)
Query
OK, 1 row affected (0.04 sec)
...
We use the source command to execute
the books.sql script.
Prepared
statements
Now we will concern ourselves with
prepared statements. When we write prepared statements, we use placeholders
instead of directly writing the values into the statements. Prepared statements
increase security and performance.
In Java a PreparedStatement is an object which represents a precompiled SQL statement.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Prepared {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
String author = "Trygve
Gulbranssen";
con =
DriverManager.getConnection(url, user, password);
pst =
con.prepareStatement("INSERT INTO Authors(Name) VALUES(?)");
pst.setString(1, author);
pst.executeUpdate();
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Prepared.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Prepared.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
}
}
}
}
We add a new author to the Authors
table.
pst
= con.prepareStatement("INSERT INTO Authors(Name) VALUES(?)");
Here we create a prepared statement.
When we write prepared statements, we use placeholders instead of directly
writing the values into the statements. Prepared statements are faster and
guard against SQL injection attacks. The ? is a placeholder, which is going to
be filled later.
pst.setString(1,
author);
A value is bound to the placeholder.
pst.executeUpdate();
The prepared statement is executed.
We use the executeUpdate() method of the statement object when we don't expect any
data to be returned. This is when we create databases or execute INSERT,
UPDATE, DELETE statements.
$
java -cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Prepared
mysql>
select * from Authors;
+----+--------------------+
|
Id | Name |
+----+--------------------+
| 1 | Jack London |
| 2 | Honore de Balzac |
| 3 | Lion Feuchtwanger |
| 4 | Emile Zola |
| 5 | Truman Capote |
| 6 | Trygve Gulbranssen |
+----+--------------------+
6
rows in set (0.00 sec)
We have a new author inserted into
the table.
For the following two examples, we
will use the Testing table. We will execute a normal statement and a prepared
statement 1000 times. We check, if there is some difference in execution time.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class NotPrepared {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
String cs = "jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(cs, user, password);
st = con.createStatement();
for (int i=1; i<=1000; i++) {
String query = "INSERT
INTO Testing(Id) VALUES(" + 2*i + ")";
st.executeUpdate(query);
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(NotPrepared.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(NotPrepared.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
}
}
}
}
The first example uses the normal
Statement object.
for
(int i=1; i<=1000; i++) {
String query = "INSERT INTO
Testing(Id) VALUES(" + 2*i + ")";
st.executeUpdate(query);
}
We build the query and execute it
1000 times.
$
/usr/bin/time java -cp .:lib/mysql-connector-java-5.1.13-bin.jar
zetcode/NotPrepared
1.09user
0.18system 0:46.37elapsed 2%CPU (0avgtext+0avgdata 92144maxresident)k
0inputs+96outputs
(1major+6160minor)pagefaults 0swaps
We use the time command to
measure the time, that the program ran. Note that we use a standard linux
command, not the built-in bash time command. It took 46s to insert 1000 rows
into the table using the Statement object.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Prepared2 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
String cs =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(cs, user, password);
pst = con.prepareStatement("INSERT INTO
Testing(Id) VALUES(?)");
for (int i = 1; i <= 1000; i++)
{
pst.setInt(1, i * 2);
pst.executeUpdate();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Prepared2.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Prepared2.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
}
}
}
}
Now we use the PreparedStatement to do the same task.
pst
= con.prepareStatement("INSERT INTO Testing(Id) VALUES(?)");
We create the prepared statement
using the prepareStatement() method.
for
(int i = 1; i <= 1000; i++) {
pst.setInt(1, i * 2);
pst.executeUpdate();
}
We bind a value to the prepared
statement, execute it in a loop thousand times.
$
/usr/bin/time java -cp .:lib/mysql-connector-java-5.1.13-bin.jar
zetcode/Prepared2
1.08user
0.10system 0:32.99elapsed 3%CPU (0avgtext+0avgdata 90400maxresident)k
0inputs+96outputs
(1major+6129minor)pagefaults 0swaps
Now it took 33s to insert 1000 rows.
We have saved 13s using prepared statements.
Retrieving
data
Next we will show, how to retrieve
data from a database table. We get all data from the Authors table.
package
zetcode;
import
java.sql.PreparedStatement;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Retrieve {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(url, user, password);
pst =
con.prepareStatement("SELECT * FROM Authors");
rs = pst.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(":
");
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Retrieve.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Retrieve.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
We get all authors from the Authors
table and print them to the console.
pst
= con.prepareStatement("SELECT * FROM Authors");
rs
= pst.executeQuery();
We execute a query that selects all
columns from the Authors table. We use the executeQuery()
method. The method executes the given SQL statement, which returns a single ResultSet object. The ResultSet is the data table returned by the SQL
query.
while
(rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
The next() method advances the cursor to the
next record. It returns false, when there are no more rows in the result set.
The getInt() and getString() methods retrieve the value of the designated column in the
current row of this ResultSet object as an int/String in the Java programming language.
java
-cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Retrieve
1:
Jack London
2:
Honore de Balzac
3:
Lion Feuchtwanger
4:
Emile Zola
5:
Truman Capote
6:
Trygve Gulbranssen
We have Ids and Names of authors
printed to the console.
Properties
It is a common practice to put the
configuration data outside the program in a separate file. This way the
programmers are more flexible. We can change the user, a password or a
connection url without needing to recompile the program. It is especially useful
in a dynamic environment, where is a need for a lot of testing, debugging,
securing data etc.
In Java, the Properties is a class used often for this. The class is used for easy
reading and saving of key/value properties.
db.url=jdbc:mysql://localhost:3306/testdb
db.user=testuser
db.passwd=test623
We have a database.properties file,
in which we have three key/value pairs. These are dynamically loaded during
execution of the program.
package
zetcode;
import
java.io.FileInputStream;
import
java.io.FileNotFoundException;
import
java.io.IOException;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.sql.PreparedStatement;
import
java.util.Properties;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Retrieve2 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
Properties props = new Properties();
FileInputStream in = null;
try {
in = new
FileInputStream("database.properties");
props.load(in);
} catch (FileNotFoundException ex) {
Logger lgr =
Logger.getLogger(Retrieve2.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(),
ex);
} catch (IOException ex) {
Logger lgr =
Logger.getLogger(Retrieve2.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (in != null) {
in.close();
}
} catch (IOException ex) {
Logger lgr =
Logger.getLogger(Retrieve2.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(),
ex);
}
}
String url =
props.getProperty("db.url");
String user =
props.getProperty("db.user");
String passwd =
props.getProperty("db.passwd");
try {
con =
DriverManager.getConnection(url, user, passwd);
pst =
con.prepareStatement("SELECT * FROM Authors");
rs = pst.executeQuery();
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(":
");
System.out.println(rs.getString(2));
}
} catch (Exception ex) {
Logger lgr =
Logger.getLogger(Retrieve2.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
}
finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Retrieve2.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
We connect to the testdb database
and print the contents of the Authors table to the console. This time, we load
the connection properties from a file. They are not hard coded in the proram.
Properties
props = new Properties();
FileInputStream
in = null;
try
{
in = new
FileInputStream("database.properties");
props.load(in);
...
The Properties class is
created. The data is loaded from the file called database.properties, where we
have our configuration data.
String
url = props.getProperty("db.url");
String
user = props.getProperty("db.user");
String
passwd = props.getProperty("db.passwd");
The values are retrieved with the getProperty() method.
Multiple
statements
It is possible to execute multiple
SQL statements in one query. The allowMultiQueries
must be set to enable multiple statements in MySQL.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Multiple {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String cs =
"jdbc:mysql://localhost:3306/testdb?allowMultiQueries=true";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(cs, user, password);
String query = "SELECT Id,
Name FROM Authors WHERE Id=1;"
+ "SELECT Id, Name FROM
Authors WHERE Id=2;"
+ "SELECT Id, Name
FROM Authors WHERE Id=3";
pst = con.prepareStatement(query);
boolean isResult = pst.execute();
do {
rs = pst.getResultSet();
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(":
");
System.out.println(rs.getString(2));
}
isResult =
pst.getMoreResults();
} while (isResult);
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Multiple.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Multiple.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
In the code example, we retrieve
three rows from the Authors table. We use three SELECT statements to get three rows.
String
cs = "jdbc:mysql://localhost:3306/testdb?allowMultiQueries=true";
We enable multiple statements
queries in the database URL by setting the allowMultiQueries
parameter to true.
String
query = "SELECT Id, Name FROM Authors WHERE Id=1;"
+ "SELECT Id, Name FROM Authors
WHERE Id=2;"
+ "SELECT Id, Name FROM Authors
WHERE Id=3";
Here we have a query with multiple
statements. The statements are separated by a semicolon.
boolean
isResult = pst.execute();
We call the execute() method of the prepared statement object. The method returns
a boolean value indicating if the first result is a ResultSet object. Subsequent results are called using the getMoreResults() method.
do
{
rs = pst.getResultSet();
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.print(": ");
System.out.println(rs.getString(2));
}
isResult = pst.getMoreResults();
}
while (isResult);
The processing of the results is
done inside the do/while loop. The ResultSet is
retrieved with the getResultSet() method call. To find out, if there are other results, we
call the getMoreResults() method.
java
-cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Multiple
1:
Jack London
2:
Honore de Balzac
3:
Lion Feuchtwanger
The output of the example. The first
three rows were retrieved from the Authors table.
Column
headers
Next we will show, how to print
column headers with the data from the database table. We refer to column names
as MetaData. MetaData is data about the core data in the database.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.ResultSetMetaData;
import
java.sql.SQLException;
import
java.util.Formatter;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class ColumnHeaders {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String cs =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con = DriverManager.getConnection(cs,
user, password);
String query = "SELECT Name,
Title From Authors, " +
"Books WHERE
Authors.Id=Books.AuthorId";
pst = con.prepareStatement(query);
rs = pst.executeQuery();
ResultSetMetaData meta =
rs.getMetaData();
String colname1 =
meta.getColumnName(1);
String colname2 =
meta.getColumnName(2);
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s",
colname1, colname2);
System.out.println(fmt1);
while (rs.next()) {
Formatter fmt2 = new
Formatter();
fmt2.format("%-21s",
rs.getString(1));
System.out.print(fmt2);
System.out.println(rs.getString(2));
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(ColumnHeaders.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(ColumnHeaders.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
In this program, we select authors
from the Authors table and their books from the Books table. We print the names
of the columns returned in the result set. We format the output.
String
query = "SELECT Name, Title From Authors, " +
"Books WHERE Authors.Id=Books.AuthorId";
This is the SQL statement which
joins authors with their books.
ResultSetMetaData
meta = rs.getMetaData();
To get the column names we need to
get the ResultSetMetaData. It is an object that can be used to get information about
the types and properties of the columns in a ResultSet object.
String
colname1 = meta.getColumnName(1);
String
colname2 = meta.getColumnName(2);
From the obtained metadata, we get
the column names.
Formatter
fmt1 = new Formatter();
fmt1.format("%-21s%s",
colname1, colname2);
System.out.println(fmt1)
We print the column names to the
console. We use the Formatter object to format the data.
while
(rs.next()) {
Formatter fmt2 = new Formatter();
fmt2.format("%-21s",
rs.getString(1));
System.out.print(fmt2);
System.out.println(rs.getString(2));
}
We print the data to the console. We
again use the Formatter object to format the data. The first column is 21
characters wide and is aligned to the left.
$
java -cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/ColumnHeaders
Name Title
Jack
London Call of the Wild
Jack
London Martin Eden
Honore
de Balzac Old Goriot
Honore
de Balzac Cousin Bette
Lion
Feuchtwanger Jew Suess
Emile
Zola Nana
Emile
Zola The Belly of Paris
Truman
Capote In Cold blood
Truman
Capote Breakfast at Tiffany
Output of the program.
Writing
images
Some people prefer to put their
images into the database, some prefer to keep them on the file system for their
applications. Technical difficulties arise when we work with millions of
images. Images are binary data. MySQL database has a special data type to store
binary data called BLOB (Binary Large Object).
For this example, we use the Images
table.
package
zetcode;
import
java.io.File;
import
java.io.FileInputStream;
import
java.io.FileNotFoundException;
import
java.io.IOException;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class WriteImage {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
FileInputStream fin = null;
String cs =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password =
"test623";
try {
File img = new
File("woman.jpg");
fin = new FileInputStream(img);
con = DriverManager.getConnection(cs, user,
password);
pst =
con.prepareStatement("INSERT INTO Images(Data) VALUES(?)");
pst.setBinaryStream(1, fin, (int)
img.length());
pst.executeUpdate();
} catch (FileNotFoundException ex) {
Logger lgr =
Logger.getLogger(WriteImage.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(WriteImage.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
if (fin != null) {
fin.close();
}
} catch (IOException ex) {
Logger lgr =
Logger.getLogger(WriteImage.class.getName());
lgr.log(Level.WARNING, ex.getMessage(),
ex);
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(WriteImage.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
In the preceding example, we read a
jpg image from the current working directory and insert in into the Images
table.
pst
= con.prepareStatement("INSERT INTO Images(Data) VALUES(?)");
This is the SQL to insert an image.
File
img = new File("woman.jpg");
fin
= new FileInputStream(img);
We create a File
object for the image file. To read bytes from this file, we create a FileInputStream object.
pst.setBinaryStream(1,
fin, (int) img.length());
The binary stream is set to the
prepared statement. The parameters of the setBinaryStream()
method are the parameter index to bind, the input stream and the number of
bytes in the stream.
pst.executeUpdate();
We execute the statement.
Reading
images
In the previous example, we have
inserted an image into the database table. Now we are going to read the image
back from the table.
package
zetcode;
import
java.io.FileOutputStream;
import
java.io.IOException;
import
java.sql.Blob;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class ReadImage {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pst = null;
FileOutputStream fos = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(url, user, password);
String query = "SELECT Data
FROM Images LIMIT 1";
pst = con.prepareStatement(query);
ResultSet result =
pst.executeQuery();
result.next();
fos = new
FileOutputStream("woman2.jpg");
Blob blob =
result.getBlob("Data");
int len = (int) blob.length();
byte[] buf = blob.getBytes(1, len);
fos.write(buf, 0, len);
} catch (IOException ex) {
Logger lgr =
Logger.getLogger(ReadImage.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(ReadImage.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (pst != null) {
pst.close();
}
if (con != null) {
con.close();
}
if (fos != null) {
fos.close();
}
} catch (IOException ex) {
Logger lgr =
Logger.getLogger(ReadImage.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(ReadImage.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
We read one image from the Images
table.
String
query = "SELECT Data FROM Images LIMIT 1";
We select one record from the table.
fos
= new FileOutputStream("woman2.jpg");
The FileOutputStream
object is created to write to a file. It is meant for writing streams of raw
bytes such as image data.
Blob
blob = result.getBlob("Data");
We get the image data from the Data
column by calling the getBlob() method.
int
len = (int) blob.length();
We figure out the length of the blob
data. In other words, we get the number of bytes.
byte[]
buf = blob.getBytes(1, len);
The getBytes() method
retrieves all bytes of the Blob object, as an array of bytes.
fos.write(buf,
0, len);
The bytes are written to the output
stream. The image is created on the filesystem.
Transaction
support
A transaction is an atomic
unit of database operations against the data in one or more databases. The
effects of all the SQL statements in a transaction can be either all committed
to the database or all rolled back.
The MySQL database has different
types of storage engines. The most common are the MyISAM and the InnoDB
engines. There is a trade-off between data security and database speed. The
MyISAM tables are faster to process and they do not support transactions. On
the other hand, the InnoDB tables are more safe against the data loss. They
support transactions. They are slower to process.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Transaction {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(url, user, password);
st = con.createStatement();
con.setAutoCommit(false);
st.executeUpdate("UPDATE
Authors SET Name = 'Leo Tolstoy' "
+ "WHERE Id =
1");
st.executeUpdate("UPDATE Books
SET Title = 'War and Peace' "
+ "WHERE Id =
1");
st.executeUpdate("UPDATE Books
SET Titl = 'Anna Karenina' "
+ "WHERE Id =
2");
con.commit();
} catch (SQLException ex) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr =
Logger.getLogger(Transaction.class.getName());
lgr.log(Level.WARNING,
ex1.getMessage(), ex1);
}
}
Logger lgr =
Logger.getLogger(Transaction.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Transaction.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
In this program, we want to change
the name of the author on the first row of the Authors table. We must also
change the books associated with this author. A good example where a
transaction is necessary. If we change the author and do not change the
author's books, the data is corrupted.
con.setAutoCommit(false);
To work with transactions, we must
set the autocommit to false. By default, a database connection is in autocommit
mode. In this mode each statement is committed to the database, as soon as it
is executed. A statement cannot be undone. When the autocommit is turned off,
we commit the changes by calling the commit() or roll
it back by calling the rollback() method.
st.executeUpdate("UPDATE
Books SET Titl = 'Anna Karenina' "
+ "WHERE Id = 2");
The third SQL statement has an
error. There is no Titl column in the table.
con.commit();
If there is no exception, the
transaction is committed.
if
(con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr =
Logger.getLogger(Transaction.class.getName());
lgr.log(Level.WARNING,
ex1.getMessage(), ex1);
}
}
In case of an exception, the
transaction is rolled back. No changes are committed to the database.
$
java -cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Transaction
Sep
24, 2011 2:53:19 PM zetcode.Transaction main
SEVERE:
Unknown column 'Titl' in 'field list'
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
Unknown column 'Titl'
in 'field list'
...
mysql>
SELECT Name, Title From Authors, Books WHERE Authors.Id=Books.AuthorId;
+-------------------+----------------------+
|
Name | Title |
+-------------------+----------------------+
|
Jack London | Call of the Wild |
|
Jack London | Martin Eden |
|
Honore de Balzac | Old Goriot |
|
Honore de Balzac | Cousin Bette |
|
Lion Feuchtwanger | Jew Suess
|
|
Emile Zola | Nana |
|
Emile Zola | The Belly of
Paris |
|
Truman Capote | In Cold blood |
|
Truman Capote | Breakfast at Tiffany
|
+-------------------+----------------------+
9
rows in set (0.01 sec)
An exception was thrown. The
transaction was rolled back and no changes took place.
However, without a transaction, the
data is not safe.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class Update {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(url, user, password);
st = con.createStatement();
st.executeUpdate("UPDATE
Authors SET Name = 'Leo Tolstoy' "
+ "WHERE Id = 1");
st.executeUpdate("UPDATE Books
SET Title = 'War and Peace' "
+ "WHERE Id =
1");
st.executeUpdate("UPDATE Books
SET Titl = 'Anna Karenina' "
+ "WHERE Id =
2");
con.close();
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Update.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(Update.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
We have the same example. This time,
without the transaction support.
$
java -cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/Update
Sep
24, 2011 3:05:08 PM zetcode.Update main
SEVERE:
Unknown column 'Titl' in 'field list'
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
Unknown column 'Titl'
in 'field list'
...
mysql>
SELECT Name, Title From Authors, Books WHERE Authors.Id=Books.AuthorId;
+-------------------+----------------------+
|
Name | Title |
+-------------------+----------------------+
|
Leo Tolstoy | War and Peace |
|
Leo Tolstoy | Martin Eden |
|
Honore de Balzac | Old Goriot |
|
Honore de Balzac | Cousin Bette |
|
Lion Feuchtwanger | Jew Suess
|
|
Emile Zola | Nana |
|
Emile Zola | The Belly of
Paris |
|
Truman Capote | In Cold blood |
|
Truman Capote | Breakfast at Tiffany
|
+-------------------+----------------------+
9
rows in set (0.00 sec)
An exception is thrown again. Leo
Tolstoy did not write Martin Eden. The data is corrupted.
Batch
updates
When we need to update data with
multiple statements, we can use batch updates. Batch updates are available for
INSERT, UPDATE, DELETE statements as well as for CREATE TABLE and DROP TABLE
statements.
package
zetcode;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.SQLException;
import
java.sql.Statement;
import
java.util.logging.Level;
import
java.util.logging.Logger;
public
class BatchUpdate {
public static void main(String[] args)
{
Connection con = null;
Statement st = null;
String url =
"jdbc:mysql://localhost:3306/testdb";
String user = "testuser";
String password = "test623";
try {
con =
DriverManager.getConnection(url, user, password);
con.setAutoCommit(false);
st = con.createStatement();
st.addBatch("DELETE FROM
Authors");
st.addBatch("INSERT INTO
Authors(Id, Name) VALUES(1, 'Jack London')");
st.addBatch("INSERT INTO
Authors(Id, Name) VALUES(2, 'Honore de Balzac')");
st.addBatch("INSERT INTO Authors(Id,
Name) VALUES(3, 'Lion Feuchtwanger')");
st.addBatch("INSERT INTO
Authors(Id, Name) VALUES(4, 'Emile Zola')");
st.addBatch("INSERT INTO
Authors(Id, Name) VALUES(5, 'Truman Capote')");
st.addBatch("INSERT INTO
Authors(Id, Name) VALUES(6, 'Umberto Eco')");
int counts[] = st.executeBatch();
con.commit();
System.out.println("Committed
" + counts.length + " updates");
} catch (SQLException ex) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr =
Logger.getLogger(BatchUpdate.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(),
ex1);
}
}
Logger lgr =
Logger.getLogger(BatchUpdate.class.getName());
lgr.log(Level.SEVERE,
ex.getMessage(), ex);
} finally {
try {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger lgr =
Logger.getLogger(BatchUpdate.class.getName());
lgr.log(Level.WARNING,
ex.getMessage(), ex);
}
}
}
}
This is an example program for a
batch update. We delete all data from the Authors table and insert new data. We
add one new author, Umberto Eco to see the changes.
st.addBatch("DELETE
FROM Authors");
st.addBatch("INSERT
INTO Authors(Id, Name) VALUES(1, 'Jack London')");
st.addBatch("INSERT
INTO Authors(Id, Name) VALUES(2, 'Honore de Balzac')");
st.addBatch("INSERT
INTO Authors(Id, Name) VALUES(3, 'Lion Feuchtwanger')");
...
We use teh addBatch() method to add a new command to the statement.
int
counts[] = st.executeBatch();
After adding all commands, we call
the executeBatch() to perform a batch update. The method returns an array of
committed changes.
con.commit();
Batch updates are committed in a
transaction.
java
-cp .:lib/mysql-connector-java-5.1.13-bin.jar zetcode/BatchUpdate
Committed
7 updates
mysql>
SELECT * FROM Authors;
+----+-------------------+
|
Id | Name |
+----+-------------------+
| 1 | Jack London |
| 2 | Honore de Balzac |
| 3 | Lion Feuchtwanger |
| 4 | Emile Zola |
| 5 | Truman Capote |
| 6 | Umberto Eco |
+----+-------------------+
6
rows in set (0.00 sec)
We execute the BatchUpdate program.
The SELECT statement shows that the Authors table was successfully updated. It
has a new author, Umerto Eco.
