Spring Framework JDBC (JdbcTemplate)

Connecting to relational database (MySQL) from Java application using Spring framework JDBC JdbcTemplate class.

Objective

  • How to use Spring JDBC (JdbcTemplate) to connect database (MySql)?
  • How to:
  1. insert(object)
  2. insert(object) return id
  3. select(id) return object
  4. select(id) return single column
  5. select() return list of objects
  6. batchUpdate() more than one records!
  7. count() return # of rows!

Environment

  • Eclipse (Indigo)
  • MySql

Libraries

( 1 ) Database

( 2 ) Java Project

  • Create new Java Project
  • Add new folder “lib”
  • Copy all jars to “lib” folder
  • Add jar file to java build path

( 3 ) Java Classes

  • Person.java
  • PersonDAO.java
  • JdbcTemplatePersonDAO.java
  • Main.java

Person.java

package com.hmkcode.vo;

public class Person {

	int id;
	String name;

	//setter & getter.....

	public String toString(){
		return "ID: "+id+" Name: "+name;
	}
}

PersonDAO.java

package com.hmkcode.dao;

import java.util.List;

import com.hmkcode.vo.Person;

public interface PersonDAO {
    
    public void insert(Person person);
    public int insertReturnId(Person person);
    public Person selectById(int id);
    public String selectPersonName(int id);
    public List<Person> select();
    public void batchUpdate(final List<Person> persons);
    public int count();
}

JdbcTemplatePersonDAO.java

package com.hmkcode.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.hmkcode.vo.Person;

public class JdbcTemplatePersonDAO implements PersonDAO {

    private JdbcTemplate jdbcTemplate;

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

    
    @Override
    public void insert(Person person) {
                    jdbcTemplate.update(
                    "INSERT INTO hmkcode.persons (id ,name) VALUES (NULL , ?)",
                    person.getName());
                     System.out.println("insert("+person+")");
    }
    @Override
    public int insertReturnId(final Person person){
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                new PreparedStatementCreator()  {
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps =
                            connection.prepareStatement("INSERT INTO hmkcode.persons (id ,name) VALUES (NULL , ?)", new String[] {"id"});
                        ps.setString(1, person.getName());
                        return ps;
                    }
                },
                keyHolder);
         System.out.println("insert("+person+") --> id: "+keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
        
    }

    @Override
    public Person selectById(int id){
        Person person = this.jdbcTemplate.queryForObject("SELECT * FROM hmkcode.persons WHERE id = ?",
                new Object[]{id},
                new RowMapper<Person>(){
                    public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Person person = new Person();
                        person.setId(Integer.parseInt(rs.getString("id")));
                        person.setName(rs.getString("name"));
                        return person;
                    }
                }
            );
         System.out.println("selectById("+id+") --> "+person);
        return person;
    }
    @Override
    public String selectPersonName(int id){
        String name = this.jdbcTemplate.queryForObject(
                "SELECT name FROM hmkcode.persons WHERE id = ?",
                new Object[]{id}, String.class);
         System.out.println("selectPersonName("+id+") --> name: "+name);
        return name;
    }
    @Override
    public int count(){
        int rowCount = this.jdbcTemplate.queryForInt("select count(*) from hmkcode.persons");
         System.out.println("count() --> "+rowCount);
        return rowCount;
    }
    @Override
    public List<Person> select() {
        List<Person> persons = this.jdbcTemplate.query(
                "SELECT * FROM hmkcode.persons",
                new RowMapper<Person>() {
                    public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                        Person person = new Person();
                        person.setId(Integer.parseInt(rs.getString("id")));
                        person.setName(rs.getString("name"));
                        return person;
                    }
                });
             System.out.println("select() --> "+persons);
             return persons;
    }
    @Override
    public void batchUpdate(final List<Person> persons){
        jdbcTemplate.batchUpdate("update hmkcode.persons set name = ? where id = ?",
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setString(1, persons.get(i).getName());
                        ps.setInt(2, persons.get(i).getId());
                    }
                    public int getBatchSize() {
                        return persons.size();
                    }
        });
         System.out.println("batchUpdate("+persons+")");
    }
}

Main.java

package com.hmkcode;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.hmkcode.dao.PersonDAO;
import com.hmkcode.vo.Person;



public class Main {

    public static void main(String args[]){
        
        // Laod spring-config.xml file
        ApplicationContext ctx = new ClassPathXmlApplicationContext("com/hmkcode/config/spring-config.xml");
        
        //get jdbcTemplatePersonDAO
        PersonDAO personDAO = (PersonDAO) ctx.getBean("jdbcTemplatePersonDAO");

        //create person bean to insert
        Person person = new Person();
        person.setName("Person 1");
        
        //( 1 ) insert person 
        personDAO.insert(person);

        //**set name of person
        person.setName("Person 2");    

        //( 2 ) insert return id
        int id = personDAO.insertReturnId(person);
        
        //( 3 ) select person by id
        personDAO.selectById(id);
        
        //( 4 ) select person name by id
        personDAO.selectPersonName(id);
        
        //( 5 ) select all
        List<Person> persons = personDAO.select();
        
        //**set name of all persons
        for(int i = 0; i < persons.size(); i++){
            persons.get(i).setName("Person Name "+i);
        }
        
        //( 6 ) update list of persons
        personDAO.batchUpdate(persons);
        
        //**check batch update
        personDAO.select();

        //( 7 ) count all
        personDAO.count();

    }
}

( 4 ) XML Configuration

you can use @Autowired annotation if you like!

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="

http://www.springframework.org/schema/beans


http://www.springframework.org/schema/beans/spring-beans.xsd


http://www.springframework.org/schema/context


http://www.springframework.org/schema/context/spring-context.xsd">

	<bean id="jdbcTemplatePersonDAO" class="com.hmkcode.dao.JdbcTemplatePersonDAO">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="dataSource" 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/hmkcode" />
		<property name="username" value="root" />
		<property name="password" value="" />
    </bean>
</beans>

( 5 ) Test Application

( 6 ) Note

You can use Spring DriverManagerDataSource which we did not use here” instead of Apache BasicDataSource, but as noted in by springsource that DriverManagerDataSource should be used for testing only!

 

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
......
</bean>