Spring DAO

Spring is one of the best data persistence frameworks for using with the DAO design pattern, providing an attractive Spring DAO combination. This short tutorial provides a brief introduction to Spring DAO JDBC.

FireStorm/DAO is a Spring DAO tool that can be used to automatically generate the Spring DAO code in this tutorial.

Download FireStorm/DAO

Setting up a DataSource

There are a number of ways to configure Spring to use DataSources. A simple method is to use spring beans to create a DataSource and inject it into a DAO / POJO class.

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
        <property name="username" value="test"/>
        <property name="password" value="*******"/>
    </bean>

     <bean id="companyDao" class="com.myapp.CompanyDao">
        <property name="dataSource" ref="myDataSource" />
    </bean>

The CompanyDao class will have this basic structure.

public class CompanyDao {

    DataSource dataSource;
    SimpleJdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        jdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    /* DAO methods go here */

}

The Spring DAO class can be instantiated using:

BeanFactory bf = new XmlBeanFactory( new ClassPathResource("config.xml") );
    return (CompanyDao) bf.getBean( "companyDao" );

Running a query

The Spring DAO JDBC Template class provides an elegant method of running SQL queries and mapping the result set to Java objects with a minimal amount of coding.

public Company getCompany(int id) throws SQLException {
    return jdbcTemplate.queryForObject(
            "SELECT id, name FROM company WHERE id = ?",
            new ParameterizedRowMapper<Company>() {
                public Company mapRow(ResultSet rs, int i) throws SQLException {
                    return new Company( rs.getInt(1), rs.getString(2) );
                }
            },
            id
    );
}

Inserts, Updates, and Deletes

These are even simpler than the query example.

public int createCompany(Company company) throws SQLException {
        jdbcTemplate.update(
                "INSERT INTO company (name) VALUES (?)",
                company.getName()
        );
        return jdbcTemplate.queryForInt( "select last_insert_id()" );
    }

One weakness of Spring JDBC is that it does not help with retrieving auto-generated keys and generally does not provide any support for assigning primary keys. The above code is specific to MySQL.

Additional Resources

FireStorm/DAO is a database access tool based on the Data Access Object design pattern.

Download FireStorm/DAO

CodeFutures provides a free program to analyze the performance of your MySQL database.

Free MySQL Performance Analysis

Read about how Database Sharding helps many major companies to linearly scale their database applications.

Request Database Sharding White Paper

dbShards economically scales large, high transaction volume databases using Database Sharding.

View Introduction to dbShards Video

Products
FireStorm/DAO
FireStorm/DAO Editions

Free

MySQL
Performance
Analysis

Click Here