JDBC in 10 minutes


Java Database Connectivity is the essential technology under any modern persistence framework for relational databases. Learn the basics of JDBC in a few minutes.

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).

The model

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
}

The database

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.

Insert data

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.

Select data

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 >= ?

Delete data

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;
}

Update data

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;
}

Review

JDBC is straight-forward:

  1. Connection.
  2. Preparation of the SQL statement.
  3. Execution and return of the result.
  4. Disconnection.

7 Responses to JDBC in 10 minutes

  1. […] JDBC in 10 minutes Possibly related posts: (automatically generated)Sources for Java Programming articles […]

  2. Samuel says:

    Very good! Always needed such good tutorial about that. Thanks!

  3. rip747 says:

    i just vomited in my mouth. why would you want to write all that code when there are numerous orms for java out there?

  4. Tony says:

    I would highly recommend against using raw JDBC anymore. Check out Apache DBUtils and DBCP if you like staying close to JDBC. Also check out the JPA.

  5. Spencer K says:

    Spring JDBC is also quite usable even when not using the dependency injection associated with Spring.

  6. Emeric says:

    @Nikos
    Excellent, it was just clear and complete. Thanks.
    Every architect / senior developer shoud know this, without jpa / spring or apache of course. Because it helps to know how and when to use them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: