Easy JDBC using Spring

Spring has a well-written module for JDBC. We’ll use it to produce clean code for our application in 10 minutes.

• The model
• Libraries
• Design
• Insert data
• Select data
• Delete data
• Update data
• Remarks

The model

Our application handles information about persons. For every person there is interest about his name, age and a unique number (key).

In Java it’s a plain bean.

public class Person {
    private String name;
    private int age;
    private int id;

    // getters and setters
}

In the database it’s a simple table.

To create it in MySQL you may refer to MySQL in 3 minutes.

Libraries

Download Apache Commons Logging from apache.org → 1.1.1.zip.

• commons-logging-1.1.1.jar

Download Spring from springsource.com → Latest GA release: 2.5.6.SEC01 → spring-framework-2.5.6.SEC01.zip

• dist/spring.jar
• dist/modules/spring-jdbc.jar

Download MySQL JDBC driver from mysql.com → No thanks, just take me to the downloads → mysql-connector-java-5.1.8.zip.

• mysql-connector-java-5.1.8-bin.jar

These files should be included in the project’s classpath.

Design

We decide to create a parent class that handles all the connection details.

This parent class (Data Access Object) provides connection and query capabilities to its children in a transparent way.

public class ParentDAO extends JdbcDaoSupport {
   /**
    * Connects to the database.
    * It loads the JDBC driver, along with the username and password.
    * Also it provides the url of the database.
    */
    public ParentDAO() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String user = "root";
        String pass = "qweasdzxc";
        String url = "jdbc:mysql://127.0.0.1/hello";
        setDataSource(new DriverManagerDataSource(url, user, pass));
    }
}

Now, we can design the class that will handle persons.

public class PersonDAO extends ParentDAO {

    public PersonDataHandler() throws Exception {
    }

   /**
    * Inserts a new person in the database.
    */
    public void insert(Person person) throws Exception {
    }

   /**
    * Deletes a specific person from the database.
    */
    public void delete(int id) throws Exception {
    }

   /**
    * Updates the given person details.
    */
    public void update(Person person) throws Exception {
    }

   /**
    * Selects a specific person from the database.
    */
    public Person select(int id) throws Exception {
    }
}

The actual implementation of these methods is rather clean.

Insert data

public void insert(Person person) throws Exception {
    String sql = "INSERT INTO persons(name, age) VALUES (?, ?)";
    Object[] parameters = new Object[] { person.getName(), person.getAge() };
    getJdbcTemplate().update(sql, parameters);
}

Delete data

public void delete(int id) throws Exception {
    String sql = "DELETE FROM persons WHERE id = ?";
    Object[] parameters = new Object[] { id };
    getJdbcTemplate().update(sql, parameters);
}

Update data

public void update(Person person) throws Exception {
    String sql = "UPDATE persons SET name = ?, age = ? WHERE id = ?";
    Object[] parameters = new Object[] {
        person.getName(), person.getAge(), person.getId()
    };
    getJdbcTemplate().update(sql, parameters);
}

Select data

public Person select(int id) throws Exception {
    String sql = "SELECT id, name, age FROM persons WHERE id = ?";
    Object[] parameters = new Object[] { id };
    RowMapper set = new RowMapper() {
        public Object mapRow(ResultSet set, int row) throws SQLException {
            Person person = new Person();
            person.setId(set.getInt("id"));
            person.setAge(set.getInt("age"));
            person.setName(set.getString("name"));
            return person;
        }
    };
    Person person = (Person)getJdbcTemplate().queryForObject(sql, parameters, set);
    return person;
}

Remarks

Using Spring, it’s easy to write JDBC code.

And there’s more:

1. In an enterprise application, we wouldn’t provide these details in the constructor of ParentDAO. We should only properly declare the DataSource in the configuration file. Spring would then initialize it automatically.
2. There’s a well defined API for database exceptions, that are really developer-friendly: CannotAcquireLockException, ConcurrencyFailureException etc.

• JDBC in 10 minutes
• MySQL in 3 minutes
• DataSource, JdbcTemplate.

Tags: Java, JDBC, MySQL, Spring