Notorious Mr. Sang Shin of Sun Microsystems offers an excellent series of Java EE training.
Get information at
http://www.javapassion.com/j2ee/
To subscribe send an empty email at
java-ee-j2ee-programming-with-passion-subscribe@googlegroups.com
Thanks.
Let’s store information about persons in the database. For every person we are interested about its name, age and a unique number (key).
For this purpose we’ll make exactly three classes: a Connector that connects to the database, a Person class that represents an actual person and the PersonHandler class that handles the persons in the database (inserts, deletes etc).
It’s a simple javabean that holds the required information.
public class Person {
private String name;
private int age;
private int id;
// getters and setters
}
To create our database in MySQL just type.
create database hello;
and then:
use hello;
To create the table in MySQL write
CREATE TABLE persons ( name VARCHAR(255), age SMALLINT, id BIGINT AUTO_INCREMENT, primary key (id) );
For more information you may refer to MySQL in 3 minutes.
Get the JDBC driver from mysql.com → downloads → Connectors → Connector/J → Source and Binaries (zip) → Pick a mirror → No thanks, just take me to the downloads &rarr Greece [National Technical University of Athens] HTTP → mysql-connector-java-5.1.8.zip.
Inside the zip lies mysql-connector-java-5.1.8-bin.jar that should be included in the project’s classpath.
We need a comfortable class that is able to connect and disconnect. This is how we connect from a desktop application,
/**
* Returns a connection to the database.
* We load the JDBC driver, along with the username and password.
* Also we provide the url of the database.
*/
public static Connection getConnection() throws Exception {
Connection result = null;
Class.forName("com.mysql.jdbc.Driver");
String user = "nikos";
String pass = "12345";
String url = "jdbc:mysql://127.0.0.1/hello";
result = DriverManager.getConnection(url, user, pass);
result.setAutoCommit(false);
return result;
}
and this is how we disconnect.
/**
* Disconnects from the database.
*/
public static void disconnect(PreparedStatement statement,
ResultSet set, Connection connection) throws Exception {
if (statement != null) {
statement.close();
}
if (set != null) {
set.close();
}
if (connection != null) {
connection.close();
}
}
So these two methods belong to the Connector class.
To insert data in the database, first of all, we should connect to it. Then we prepare the SQL statement and we fill it with the values from the javabean. After executing and taking the result, we close the connection.
public boolean insert(Person person) throws Exception {
boolean result = false;
Connection connection = null;
PreparedStatement statement = null;
String sql = "INSERT INTO persons(name, age) VALUES(?, ?)";
try {
// Connection
connection = Connector.getConnection();
// Preparation
statement = connection.prepareStatement(sql);
statement.setString(1, person.getName());
statement.setInt(2, person.getAge());
// Execution
if (statement.executeUpdate() == 1) {
result = true;
connection.commit();
}
} catch (Exception e) {
connection.rollback();
throw e;
} finally {
// Disconnection
Connector.disconnect(statement, null, connection);
}
return result;
}
Take a look at the SQL statement, that contains question marks:
INSERT INTO persons(name, age) VALUES(?, ?)
In Java, this is a PreparedStatement.
The following method selects a person based on its unique identifier, the so-called primary key.
public Person select(int id) throws Exception {
Person person = null;
ResultSet set = null;
Connection connection = null;
PreparedStatement statement = null;
String sql = "SELECT * FROM persons WHERE id = ?";
try {
// Connection
connection = Connector.getConnection();
// Preparation
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
// Execution
set = statement.executeQuery();
if (set.next()) {
person = new Person();
person.setName(set.getString("name"));
person.setAge(set.getInt("age"));
person.setId(set.getInt("id"));
}
} finally {
// Disconnection
Connector.disconnect(statement, set, connection);
}
return person;
}
Of course you can select data based on any criteria defined by the business requirements. For example:
SELECT * FROM persons WHERE age <= ? AND age >= ?
public boolean delete(int id) throws Exception {
boolean result = false;
Connection connection = null;
PreparedStatement statement = null;
String sql = "DELETE FROM persons WHERE id = ?";
try {
// Connection
connection = Connector.getConnection();
// Preparation
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
// Execution
if (statement.executeUpdate() == 1) {
result = true;
connection.commit();
}
} catch (Exception e) {
connection.rollback();
throw e;
} finally {
// Disconnection
Connector.disconnect(statement, null, connection);
}
return result;
}
public boolean update(Person person) throws Exception {
boolean result = false;
Connection connection = null;
PreparedStatement statement = null;
String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
try {
// Connection
connection = Connector.getConnection();
// Preparation
statement = connection.prepareStatement(sql);
statement.setString(1, person.getName());
statement.setInt(2, person.getAge());
statement.setInt(3, person.getId());
// Execution
if (statement.executeUpdate() == 1) {
result = true;
connection.commit();
}
} catch (Exception e) {
connection.rollback();
throw e;
} finally {
// Disconnection
Connector.disconnect(statement, null, connection);
}
return result;
}
JDBC is straight-forward:
Sun is currently offering these online courses for free.
OpenSolaris Technical Essentials Guide (WS-1000-OS)
http://www.sun.com/training/catalog/courses/WS-1000-OS.xml
Glassfish Application Server: Introduction (WMT-SAS-1536)
http://www.sun.com/training/glassfish_login.html
Thank you.
JavaWorld SDN channel NetBeans TV JavaSpecialists
