logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

Spring JdbcTemplate Example | Examples Java Code Geeks

 小米钱 2015-08-31

This is a tutorial of Spring JDBC Framework. When we need to interface with databases the Spring JDBC framework provides solutions to all the low-level details, like open/close a connection, prepare and execute SQL statements, process exceptions and handle transactions. Thus, the only thing a developer must do is just define connection parameters and specify the SQL statement to be executed.

Spring JDBC provides several approaches and different classes to form the basis for a JDBC database access. The most popular approach makes use of JdbcTemplate class. This is the central framework class that manages all the database communication and exception handling.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

 

In order to work with JDBC in Spring we will make use of the Data Access Objects. DAOs in Spring are commonly used for database interaction, using data access technologies like JDBC, Hibernate, JPA or JDO in a consistent way. DAOs provide a means to read and write data to the database and they can expose this functionality through an interface by which the rest of the application can access them.

Here, we shall begin by showing you a simple example of JDBC integration. We will use a simple DAO, to make a simple insert and select to a database. We will continue with examples of the JdbcTemplate class to make SQL operations even easier. We will make use of the methods JdbcTemplate class provides to perform various selects and updates to the database.

Our preferred development environment is Eclipse. We are using Eclipse Juno (4.2) version, along with Maven Integration plugin version 3.1.0. You can download Eclipse from here and Maven Plugin for Eclipse from here. The installation of Maven plugin for Eclipse is out of the scope of this tutorial and will not be discussed. We are also using Spring version 3.2.3 and the JDK 7_u_21. The database used in the example is MySQL Database Server 5.6.

Let’s begin,

1. Create a new Maven project

Go to File -> Project ->Maven -> Maven Project.

New-Maven-Project

In the “Select project name and location” page of the wizard, make sure that “Create a simple project (skip archetype selection)” option is checked, hit “Next” to continue with default values.

Maven-Project-Name-Location

In the “Enter an artifact id” page of the wizard, you can define the name and main package of your project. We will set the “Group Id” variable to "com.javacodegeeks.snippets.enterprise" and the “Artifact Id” variable to "springexample". The aforementioned selections compose the main project package as "com.javacodegeeks.snippets.enterprise.springexample" and the project name as "springexample". Hit “Finish” to exit the wizard and to create your project.

Configure-Maven-Project

The Maven project structure is shown below:

Maven-project-structure

    It consists of the following folders:

  • /src/main/java folder, that contains source files for the dynamic content of the application,
  • /src/test/java folder contains all source files for unit tests,
  • /src/main/resources folder contains configurations files,
  • /target folder contains the compiled and packaged deliverables,
  • the pom.xml is the project object model (POM) file. The single file that contains all project related configuration.

2. Add Spring 3.2.3 dependency

  • Locate the “Properties” section at the “Overview” page of the POM editor and perform the following changes:
    Create a new property with name org.springframework.version and value 3.2.3.RELEASE.
  • Navigate to the “Dependencies” page of the POM editor and create the following dependencies (you should fill the “GroupId”, “Artifact Id” and “Version” fields of the “Dependency Details” section at that page):
    Group Id : org.springframework Artifact Id : spring-web Version : ${org.springframework.version}

Alternatively, you can add the Spring dependencies in Maven’s pom.xml file, by directly editing it at the “Pom.xml” page of the POM editor, as shown below:
 
pom.xml:

03    <modelVersion>4.0.0</modelVersion>
04    <groupId>com.javacodegeeks.snippets.enterprise</groupId>
05    <artifactId>springexample</artifactId>
06    <version>0.0.1-SNAPSHOT</version>
07 
08    <dependencies>
09        <dependency>
10            <groupId>org.springframework</groupId>
11            <artifactId>spring-core</artifactId>
12            <version>${spring.version}</version>
13        </dependency>
14        <dependency>
15            <groupId>org.springframework</groupId>
16            <artifactId>spring-context</artifactId>
17            <version>${spring.version}</version>
18        </dependency>
19    </dependencies>
20 
21    <properties>
22        <spring.version>3.2.3.RELEASE</spring.version>
23    </properties>
24</project>

As you can see Maven manages library dependencies declaratively. A local repository is created (by default under {user_home}/.m2 folder) and all required libraries are downloaded and placed there from public repositories. Furthermore intra – library dependencies are automatically resolved and manipulated.

3. Add the JDBC dependencies

The dependencies needed for Spring JDBC are the ones below:

pom.xml

03    <modelVersion>4.0.0</modelVersion>
04    <groupId>com.javacodegeeks.snippets.enterprise</groupId>
05    <artifactId>springexample</artifactId>
06    <version>0.0.1-SNAPSHOT</version>
07 
08    <dependencies>
09        <dependency>
10            <groupId>org.springframework</groupId>
11            <artifactId>spring-core</artifactId>
12            <version>${spring.version}</version>
13        </dependency>
14        <dependency>
15            <groupId>org.springframework</groupId>
16            <artifactId>spring-context</artifactId>
17            <version>${spring.version}</version>
18        </dependency>
19        <dependency>
20            <groupId>mysql</groupId>
21            <artifactId>mysql-connector-java</artifactId>
22            <version>5.1.26</version>
23        </dependency>
24         <dependency>
25        <groupId>org.springframework</groupId>
26        <artifactId>spring-jdbc</artifactId>
27        <version>${spring.version}</version>
28      </dependency>
29    </dependencies>
30 
31    <properties>
32        <spring.version>3.2.3.RELEASE</spring.version>
33    </properties>
34</project>

4. Execute a simple insert and select example

Let’s start, by creating a simple table in the database. We create a simple Employee table, that has three columns. The SQL statement that is executed in MySQL Workbench is shown below:

Create a table

1CREATE TABLE `Employee` (
2  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3  `NAME` VARCHAR(100) NOT NULL,
4  `AGE` INT(10) UNSIGNED NOT NULL,
5  PRIMARY KEY (`CUST_ID`)
6) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

We also create a class, Employee.java, that has three fields that are the same to the columns of Employee table.

Employee.java

01package com.javacodegeeks.snippets.enterprise;
02 
03public class Employee {
04     
05    private int id;
06     
07    private String name;
08     
09    private int age;
10 
11    public Employee(){
12         
13    }
14     
15    public Employee(int id, String name, int age) {
16        this.id = id;
17        this.name = name;
18        this.age = age;
19    }
20 
21    public int getId() {
22        return id;
23    }
24 
25    public void setId(int id) {
26        this.id = id;
27    }
28 
29    public String getName() {
30        return name;
31    }
32 
33    public void setName(String name) {
34        this.name = name;
35    }
36 
37    public int getAge() {
38        return age;
39    }
40 
41    public void setAge(int age) {
42        this.age = age;
43    }
44 
45    @Override
46    public String toString() {
47        return Employee ["id= "+ id + ", name= "+ name + ", age= "+ age
48                +"]";
49    }
50     
51}

The DAO created to interact between the java class and the table is the EmployeeDAOImpl.java. It has two methods, insert(Employee employee) and findById(int id), that implement the insert and select statements to the database.

Both methods use the DataSource class, a utility class that provides connection to the database. It is part of the JDBC specification and allows a container or a framework to hide connection pooling and transaction management issues from the application code.

In addition, both methods open a Connection to the database and use the PreparedStatement, that is an object representing a precompiled SQL statement.

EmployeeDAO.java

1package com.javacodegeeks.snippets.enterprise.dao;
2 
3import com.javacodegeeks.snippets.enterprise.Employee;
4 
5public interface EmployeeDAO {
6 
7        public void insert(Employee employee);
8        public Employee findById(int id);
9}

EmployeeDAOImpl.java

01package com.javacodegeeks.snippets.enterprise.dao.impl;
02 
03import java.sql.Connection;
04import java.sql.PreparedStatement;
05import java.sql.ResultSet;
06import java.sql.SQLException;
07 
08import javax.sql.DataSource;
09 
10import com.javacodegeeks.snippets.enterprise.Employee;
11import com.javacodegeeks.snippets.enterprise.dao.EmployeeDAO;
12 
13public class EmployeeDAOImpl implements EmployeeDAO
14{
15    private DataSource dataSource;
16  
17    public void setDataSource(DataSource dataSource) {
18        this.dataSource = dataSource;
19    }
20  
21    public void insert(Employee employee){
22  
23        String sql = "INSERT INTO employee " +
24                "(ID, NAME, AGE) VALUES (?, ?, ?)";
25        Connection conn = null;
26  
27        try {
28            conn = dataSource.getConnection();
29            PreparedStatement ps = conn.prepareStatement(sql);
30            ps.setInt(1, employee.getId());
31            ps.setString(2, employee.getName());
32            ps.setInt(3, employee.getAge());
33            ps.executeUpdate();
34            ps.close();
35  
36        } catch (SQLException e) {
37            throw new RuntimeException(e);
38  
39        } finally {
40            if (conn != null) {
41                try {
42                    conn.close();
43                } catch (SQLException e) {}
44            }
45        }
46    }
47  
48    public Employee findById(int id){
49  
50        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
51  
52        Connection conn = null;
53  
54        try {
55            conn = dataSource.getConnection();
56            PreparedStatement ps = conn.prepareStatement(sql);
57            ps.setInt(1, id);
58            Employee employee = null;
59            ResultSet rs = ps.executeQuery();
60            if (rs.next()) {
61                employee = new Employee(
62                    rs.getInt("ID"),
63                    rs.getString("NAME"),
64                    rs.getInt("AGE")
65                );
66            }
67            rs.close();
68            ps.close();
69            return employee;
70        } catch (SQLException e) {
71            throw new RuntimeException(e);
72        } finally {
73            if (conn != null) {
74                try {
75                conn.close();
76                } catch (SQLException e) {}
77            }
78        }
79    }
80}

The Datasource is configured in applicationContext.xml file. All parameters needed for the connection to the database are set here. It is defined in other bean definitions using the ref element.

applicationContext.xml

01<beans xmlns="http://www./schema/beans"
03    xmlns:aop="http://www./schema/aop" xmlns:context="http://www./schema/context"
04    xmlns:jee="http://www./schema/jee" xmlns:tx="http://www./schema/tx"
05    xmlns:task="http://www./schema/task"
07 
08 
09    <bean id="employeeDAO" class="com.javacodegeeks.snippets.enterprise.dao.impl.EmployeeDAOImpl">
10        <property name="dataSource" ref="dataSource" />
11    </bean>
12     
13    <bean id="dataSource"
14        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
15 
16        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
17        <property name="url" value="jdbc:mysql://localhost:3306/test" />
18        <property name="username" value="root" />
19        <property name="password" value="root" />
20    </bean>
21</beans>

We can run the example, using the App.java class. We load the employeeBean and then create a new Employee object. We first insert it to the table and then make a select to find it.

App.java

01package com.javacodegeeks.snippets.enterprise;
02 
03import org.springframework.context.ConfigurableApplicationContext;
04import org.springframework.context.support.ClassPathXmlApplicationContext;
05 
06import com.javacodegeeks.snippets.enterprise.dao.EmployeeDAO;
07import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
08 
09public class App {
10 
11    public static void main(String[] args) {
12     
13            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
14            EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");
15            Employee employee1 = new Employee(123, "javacodegeeks", 30);
16            employeeDAO.insert(employee1);
17            Employee employee2 = employeeDAO.findById(123);
18            System.out.println(employee2); 
19            context.close();
20    }
21}

The output is shown below:

Output

Employee [id=123, name=javacodegeeks, age=30]

5. Use of JdbcTemplate Class

The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It handles the creation and release of resources, thus avoiding errors such as forgetting to close the connection. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

A simple insert example in JDBCEmployeeDAOImpl.java class, using the JdbcTemplate class is shown below:

JDBCEmployeeDAO.java

1package com.javacodegeeks.snippets.enterprise.dao;
2 
3import com.javacodegeeks.snippets.enterprise.Employee;
4 
5public interface JDBCEmployeeDAO {
6 
7    public void insert(Employee employee);
8  
9}

JDBCEmployeeDAOImpl.java

01package com.javacodegeeks.snippets.enterprise.dao.impl;
02 
03import javax.sql.DataSource;
04 
05import org.springframework.jdbc.core.JdbcTemplate;
06 
07import com.javacodegeeks.snippets.enterprise.Employee;
08import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
09 
10public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
11    private DataSource dataSource;
12    private JdbcTemplate jdbcTemplate;
13  
14    public void setDataSource(DataSource dataSource) {
15        this.dataSource = dataSource;
16    }
17  
18    public void insert(Employee employee){
19  
20        String sql = "INSERT INTO EMPLOYEE " +
21            "(ID, NAME, AGE) VALUES (?, ?, ?)";
22  
23        jdbcTemplate = new JdbcTemplate(dataSource);
24  
25        jdbcTemplate.update(sql, new Object[] { employee.getId(),
26                employee.getName(), employee.getAge() 
27        });
28    }  
29}

5.1 Select examples

Now, let’s see how to make use of the JdbcTemplate class to make select statements in different ways. We can add new queries in EmployeeDAOImpl.java class as shown in the following cases.

5.1.1 Select a single row

In order to make a single row select we can implement the RowMapper interface. Thus, we can override the mapRow(ResultSet rs, int rowNum) method of RowMapper to map the table fields to the object, as shown below:

EmployeeRowMapper.java

01package com.javacodegeeks.snippets.enterprise;
02 
03import java.sql.ResultSet;
04import java.sql.SQLException;
05 
06import org.springframework.jdbc.core.RowMapper;
07 
08@SuppressWarnings("rawtypes")
09public class EmployeeRowMapper implements RowMapper {
10        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
11            Employee employee = new Employee();
12            employee.setId(rs.getInt("ID"));
13            employee.setName(rs.getString("NAME"));
14            employee.setAge(rs.getInt("AGE"));
15            return employee;
16        }
17}

We add a new method findById(int id) to JDBCEmployeeDAO.java and JDBCEmployeeDAOImpl.java. Here, the queryForObject(String sql, Object[] args, RowMapper rowMapper) method of JDBCTemplate class will create the select with the given sql statement and the given id. It will then map the result that is a single row to the Employee object using the EmployeeRowMapper.java implementation.

JDBCEmployeeDAO.java

01package com.javacodegeeks.snippets.enterprise.dao;
02 
03import com.javacodegeeks.snippets.enterprise.Employee;
04 
05public interface JDBCEmployeeDAO {
06 
07    public void insert(Employee employee);
08    public Employee findById(int id);
09  
10}

JDBCEmployeeDAOImpl.java

01package com.javacodegeeks.snippets.enterprise.dao.impl;
02 
03import javax.sql.DataSource;
04 
05import org.springframework.jdbc.core.JdbcTemplate;
06 
07import com.javacodegeeks.snippets.enterprise.Employee;
08import com.javacodegeeks.snippets.enterprise.EmployeeRowMapper;
09import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
10 
11public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
12    private DataSource dataSource;
13    private JdbcTemplate jdbcTemplate;
14  
15    public void setDataSource(DataSource dataSource) {
16        this.dataSource = dataSource;
17    }
18  
19    public void insert(Employee employee){
20  
21        String sql = "INSERT INTO EMPLOYEE " +
22            "(ID, NAME, AGE) VALUES (?, ?, ?)";
23  
24        jdbcTemplate = new JdbcTemplate(dataSource);
25  
26        jdbcTemplate.update(sql, new Object[] { employee.getId(),
27                employee.getName(), employee.getAge() 
28        });
29    }
30     
31    @SuppressWarnings({ "unchecked" })
32    public Employee findById(int id){
33          
34        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
35 
36        jdbcTemplate = new JdbcTemplate(dataSource);
37        Employee employee = (Employee) jdbcTemplate.queryForObject(
38                sql, new Object[] { id }, new EmployeeRowMapper());
39      
40        return employee;
41    }
42 
43}

Another way to get a single result is to use the BeanPropertyRowMapper implementation of RowMapper that converts a row into a new instance of the specified mapped target class. The BeanPropertyRowMapper maps a row column value to a property of the object by matching their names.

JDBCEmployeeDAOImpl.java

01package com.javacodegeeks.snippets.enterprise.dao.impl;
02 
03import javax.sql.DataSource;
04 
05import org.springframework.jdbc.core.BeanPropertyRowMapper;
06import org.springframework.jdbc.core.JdbcTemplate;
07 
08import com.javacodegeeks.snippets.enterprise.Employee;
09import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
10 
11public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
12    private DataSource dataSource;
13    private JdbcTemplate jdbcTemplate;
14  
15    public void setDataSource(DataSource dataSource) {
16        this.dataSource = dataSource;
17    }
18  
19    public void insert(Employee employee){
20  
21        String sql = "INSERT INTO EMPLOYEE " +
22            "(ID, NAME, AGE) VALUES (?, ?, ?)";
23  
24        jdbcTemplate = new JdbcTemplate(dataSource);
25  
26        jdbcTemplate.update(sql, new Object[] { employee.getId(),
27                employee.getName(), employee.getAge() 
28        });
29    }
30     
31    @SuppressWarnings({ "unchecked", "rawtypes" })
32    public Employee findById(int id){
33          
34        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
35 
36        jdbcTemplate = new JdbcTemplate(dataSource);
37        Employee employee = (Employee) jdbcTemplate.queryForObject(
38                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
39      
40        return employee;
41    }
42 
43}

We add the jdbcEmployeeDAOBean to applicationContext.xml:

applicationContext.xml

01<beans xmlns="http://www./schema/beans"
03    xmlns:aop="http://www./schema/aop" xmlns:context="http://www./schema/context"
04    xmlns:jee="http://www./schema/jee" xmlns:tx="http://www./schema/tx"
05    xmlns:task="http://www./schema/task"
07 
08 
09    <bean id="employeeDAO" class="com.javacodegeeks.snippets.enterprise.dao.impl.EmployeeDAOImpl">
10        <property name="dataSource" ref="dataSource" />
11    </bean>
12     
13    <bean id="jdbcEmployeeDAO" class="com.javacodegeeks.snippets.enterprise.dao.impl.JDBCEmployeeDAOImpl">
14        <property name="dataSource" ref="dataSource" />
15    </bean>
16     
17    <bean id="dataSource"
18        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
19 
20        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
21        <property name="url" value="jdbc:mysql://localhost:3306/test" />
22        <property name="username" value="root" />
23        <property name="password" value="root" />
24    </bean>
25</beans>

After loading the new bean to App.java class we can call its methods, as shown below:

App.java

01package com.javacodegeeks.snippets.enterprise;
02 
03import org.springframework.context.ConfigurableApplicationContext;
04import org.springframework.context.support.ClassPathXmlApplicationContext;
05 
06import com.javacodegeeks.snippets.enterprise.dao.EmployeeDAO;
07import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
08 
09public class App {
10 
11    public static void main(String[] args) {
12     
13            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
14             
15            JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");
16            Employee employee3 = new Employee(456, "javacodegeeks", 34);
17            jdbcEmployeeDAO.insert(employee3);
18      
19            Employee employee4 = jdbcEmployeeDAO.findById(456);
20            System.out.println(employee4); 
21            context.close();
22    }
23}

The output is the one below:
Output

Employee [id=456, name=javacodegeeks, age=34]

5.1.2 Select total rows

Now, we can query for total number of rows in the database. Again, there are two ways to map the Result. The easiest way is to use the BeanPropertyRowMapper, as shown in the example above, but another way is to create our own mapping. We add a new method to query in JDBCEmployeeDAOImpl.java class. The new method is List<Employee> findAll(), and it uses the queryForInt(String sql) method of JdbcTemplate class execute the query, as shown below:

JDBCEmployeeDAO.java

01package com.javacodegeeks.snippets.enterprise.dao;
02 
03import java.util.List;
04 
05import com.javacodegeeks.snippets.enterprise.Employee;
06 
07public interface JDBCEmployeeDAO {
08 
09    public void insert(Employee employee);
10    public Employee findById(int id);
11    public List<Employee> findAll();
12}

JDBCEmployeeDAOImpl.java

01package com.javacodegeeks.snippets.enterprise.dao.impl;
02 
03import java.util.ArrayList;
04import java.util.List;
05import java.util.Map;
06 
07import javax.sql.DataSource;
08 
09import org.springframework.jdbc.core.BeanPropertyRowMapper;
10import org.springframework.jdbc.core.JdbcTemplate;
11 
12import com.javacodegeeks.snippets.enterprise.Employee;
13import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
14 
15public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
16    private DataSource dataSource;
17    private JdbcTemplate jdbcTemplate;
18  
19    public void setDataSource(DataSource dataSource) {
20        this.dataSource = dataSource;
21    }
22  
23    public void insert(Employee employee){
24  
25        String sql = "INSERT INTO EMPLOYEE " +
26            "(ID, NAME, AGE) VALUES (?, ?, ?)";
27  
28        jdbcTemplate = new JdbcTemplate(dataSource);
29  
30        jdbcTemplate.update(sql, new Object[] { employee.getId(),
31                employee.getName(), employee.getAge() 
32        });
33    }
34     
35    @SuppressWarnings({ "unchecked", "rawtypes" })
36    public Employee findById(int id){
37          
38        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
39 
40        jdbcTemplate = new JdbcTemplate(dataSource);
41        Employee employee = (Employee) jdbcTemplate.queryForObject(
42                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
43      
44        return employee;
45    }
46 
47    @SuppressWarnings("rawtypes")
48    public List<Employee> findAll(){
49         
50        jdbcTemplate = new JdbcTemplate(dataSource);
51        String sql = "SELECT * FROM EMPLOYEE";
52      
53        List<Employee> employees = new ArrayList<Employee>();
54      
55        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
56        for (Map row : rows) {
57            Employee employee = new Employee();
58            employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
59            employee.setName((String)row.get("NAME"));
60            employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
61            employees.add(employee);
62        }
63      
64        return employees;
65    }  
66}

5.1.3 Select a single column

To get a specified column name we create a new method, String findNameById(int id), where we use the queryForObject(String sql, Object[] args, Class<String> requiredType) method of JdbcTemplate class. In this method we can set the type of the column that the query will return.

JDBCEmployeeDAO.java

01package com.javacodegeeks.snippets.enterprise.dao;
02 
03import java.util.List;
04 
05import com.javacodegeeks.snippets.enterprise.Employee;
06 
07public interface JDBCEmployeeDAO {
08 
09    public void insert(Employee employee);
10    public Employee findById(int id);
11    public List<Employee> findAll();
12    public String findNameById(int id);
13}

JDBCEmployeeDAOImpl.java

01package com.javacodegeeks.snippets.enterprise.dao.impl;
02 
03import java.util.ArrayList;
04import java.util.List;
05import java.util.Map;
06 
07import javax.sql.DataSource;
08 
09import org.springframework.jdbc.core.BeanPropertyRowMapper;
10import org.springframework.jdbc.core.JdbcTemplate;
11 
12import com.javacodegeeks.snippets.enterprise.Employee;
13import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
14 
15public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
16    private DataSource dataSource;
17    private JdbcTemplate jdbcTemplate;
18  
19    public void setDataSource(DataSource dataSource) {
20        this.dataSource = dataSource;
21    }
22  
23    public void insert(Employee employee){
24  
25        String sql = "INSERT INTO EMPLOYEE " +
26            "(ID, NAME, AGE) VALUES (?, ?, ?)";
27  
28        jdbcTemplate = new JdbcTemplate(dataSource);
29  
30        jdbcTemplate.update(sql, new Object[] { employee.getId(),
31                employee.getName(), employee.getAge() 
32        });
33    }
34     
35    @SuppressWarnings({ "unchecked", "rawtypes" })
36    public Employee findById(int id){
37          
38        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
39 
40        jdbcTemplate = new JdbcTemplate(dataSource);
41        Employee employee = (Employee) jdbcTemplate.queryForObject(
42                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
43      
44        return employee;
45    }
46 
47    @SuppressWarnings("rawtypes")
48    public List<Employee> findAll(){
49         
50        jdbcTemplate = new JdbcTemplate(dataSource);
51        String sql = "SELECT * FROM EMPLOYEE";
52      
53        List<Employee> employees = new ArrayList<Employee>();
54      
55        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
56        for (Map row : rows) {
57            Employee employee = new Employee();
58            employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
59            employee.setName((String)row.get("NAME"));
60            employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
61            employees.add(employee);
62        }
63      
64        return employees;
65    }
66     
67    public String findNameById(int id){
68          
69        String sql = "SELECT NAME FROM EMPLOYEE WHERE ID = ?";
70      
71        String name = (String)jdbcTemplate.queryForObject(
72                sql, new Object[] { id }, String.class);
73      
74        return name;
75    }
76 
77}

We use the new queries in App.class as shown below:

App.java

01package com.javacodegeeks.snippets.enterprise;
02 
03import java.util.List;
04 
05import org.springframework.context.ConfigurableApplicationContext;
06import org.springframework.context.support.ClassPathXmlApplicationContext;
07 
08import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
09 
10public class App {
11 
12    public static void main(String[] args) {
13     
14            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
15         
16            JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");
17             
18            List<Employee> employees = jdbcEmployeeDAO.findAll();
19            System.out.println(employees); 
20 
21            String name = jdbcEmployeeDAO.findNameById(456);
22            System.out.println(name);  
23 
24            context.close();
25    }
26}

In the result below we first see the list of Employees from the findAll() method and then the value of the name column from the findNameById(int id) method.

Output

[Employee [id=123, name=javacodegeeks, age=30], Employee [id=456, name=javacodegeeks, age=34]]
javacodegeeks

5.2 BatchUpdate example

The batchUpdate() method of JdbcTemplate class can be used to perform all batch inserts to the database. Below there are two implementations of a batchUpdate() to the database.

The first one, insertBatch1(final List<Employee> employees) uses the BatchPreparedStatementSetter to insert a list of Objects to the database. The BatchPreparedStatementSetter is passed as the second parameter in the batchUpdate() method. It provides two methods that can be overriden. The getBatchSize() method provides the size of the current batch, whereas the setValues(PreparedStatement ps, int i) method is used to set the values for the parameters of the prepared statement.

The second method insertBatch2(final String sql) calls the batchUpdate() method of JDBCTemplate class to execute an sql statement.

JDBCEmployeeDAO.java

01package com.javacodegeeks.snippets.enterprise.dao;
02 
03import java.util.List;
04 
05import com.javacodegeeks.snippets.enterprise.Employee;
06 
07public interface JDBCEmployeeDAO {
08 
09    public void insert(Employee employee);
10    public Employee findById(int id);
11    public List<Employee> findAll();
12    public String findNameById(int id);
13    public void insertBatch1(final List<Employee> employees);
14    public void insertBatch2(final String sql);
15}

JDBCEmployeeDAO.java

001package com.javacodegeeks.snippets.enterprise.dao.impl;
002 
003import java.sql.PreparedStatement;
004import java.sql.SQLException;
005import java.util.ArrayList;
006import java.util.List;
007import java.util.Map;
008 
009import javax.sql.DataSource;
010 
011import org.springframework.jdbc.core.BatchPreparedStatementSetter;
012import org.springframework.jdbc.core.BeanPropertyRowMapper;
013import org.springframework.jdbc.core.JdbcTemplate;
014 
015import com.javacodegeeks.snippets.enterprise.Employee;
016import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
017 
018public class JDBCEmployeeDAOImpl implements JDBCEmployeeDAO{
019    private DataSource dataSource;
020    private JdbcTemplate jdbcTemplate;
021  
022    public void setDataSource(DataSource dataSource) {
023        this.dataSource = dataSource;
024    }
025  
026    public void insert(Employee employee){
027  
028        String sql = "INSERT INTO EMPLOYEE " +
029            "(ID, NAME, AGE) VALUES (?, ?, ?)";
030  
031        jdbcTemplate = new JdbcTemplate(dataSource);
032  
033        jdbcTemplate.update(sql, new Object[] { employee.getId(),
034                employee.getName(), employee.getAge() 
035        });
036    }
037     
038    @SuppressWarnings({ "unchecked", "rawtypes" })
039    public Employee findById(int id){
040          
041        String sql = "SELECT * FROM EMPLOYEE WHERE ID = ?";
042 
043        jdbcTemplate = new JdbcTemplate(dataSource);
044        Employee employee = (Employee) jdbcTemplate.queryForObject(
045                sql, new Object[] { id }, new BeanPropertyRowMapper(Employee.class));
046      
047        return employee;
048    }
049 
050    @SuppressWarnings("rawtypes")
051    public List<Employee> findAll(){
052         
053        jdbcTemplate = new JdbcTemplate(dataSource);
054        String sql = "SELECT * FROM EMPLOYEE";
055      
056        List<Employee> employees = new ArrayList<Employee>();
057      
058        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
059        for (Map row : rows) {
060            Employee employee = new Employee();
061            employee.setId(Integer.parseInt(String.valueOf(row.get("ID"))));
062            employee.setName((String)row.get("NAME"));
063            employee.setAge(Integer.parseInt(String.valueOf(row.get("AGE"))));
064            employees.add(employee);
065        }
066      
067        return employees;
068    }
069     
070    public String findNameById(int id){
071 
072        jdbcTemplate = new JdbcTemplate(dataSource);
073        String sql = "SELECT NAME FROM EMPLOYEE WHERE ID = ?";
074      
075        String name = (String)jdbcTemplate.queryForObject(
076                sql, new Object[] { id }, String.class);
077      
078        return name;
079    }
080 
081    public void insertBatchSQL(final String sql){
082          
083        jdbcTemplate.batchUpdate(new String[]{sql});
084      
085    }
086     
087    public void insertBatch1(final List<Employee> employees){
088 
089        jdbcTemplate = new JdbcTemplate(dataSource);
090          String sql = "INSERT INTO EMPLOYEE " +
091            "(ID, NAME, AGE) VALUES (?, ?, ?)";
092          
093          jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
094          
095            public void setValues(PreparedStatement ps, int i) throws SQLException {
096                Employee employee = employees.get(i);
097                ps.setLong(1, employee.getId());
098                ps.setString(2, employee.getName());
099                ps.setInt(3, employee.getAge() );
100            }
101          
102            public int getBatchSize() {
103                return employees.size();
104            }
105          });
106        }
107     
108    public void insertBatch2(final String sql){
109        jdbcTemplate = new JdbcTemplate(dataSource);
110        jdbcTemplate.batchUpdate(new String[]{sql});
111      
112    }
113         
114}

Let’s run App.java class again. We call the two new methods to insert two new rows to the Employee table and then update the table setting all values of a a column to a specified value.

App.java

01package com.javacodegeeks.snippets.enterprise;
02 
03import java.util.ArrayList;
04import java.util.List;
05 
06import org.springframework.context.ConfigurableApplicationContext;
07import org.springframework.context.support.ClassPathXmlApplicationContext;
08import org.springframework.jdbc.core.JdbcTemplate;
09 
10import com.javacodegeeks.snippets.enterprise.dao.JDBCEmployeeDAO;
11 
12public class App {
13 
14    public static void main(String[] args) {
15     
16            ConfigurableApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
17 
18            JDBCEmployeeDAO jdbcEmployeeDAO = (JDBCEmployeeDAO) context.getBean("jdbcEmployeeDAO");
19             
20            Employee emplNew1 = new Employee(23, "John", 23);
21            Employee emplNew2 = new Employee(223, "Mark", 43);
22            List<Employee> employeesN = new ArrayList();
23            employeesN.add(emplNew1);
24            employeesN.add(emplNew2);
25            jdbcEmployeeDAO.insertBatch1(employeesN);
26            System.out.println(" inserted rows: " + employeesN);
27 
28            System.out.println(" FindAll : " + jdbcEmployeeDAO.findAll());
29            jdbcEmployeeDAO.insertBatch2("UPDATE EMPLOYEE SET NAME ='Mary'");
30             
31            List<Employee> employees = jdbcEmployeeDAO.findAll();
32            System.out.println("Updated column name of table: " + employees);  
33             
34            System.out.println(" FindAll : " + jdbcEmployeeDAO.findAll());
35            context.close();
36    }
37}

The result is shown below:

Output

inserted rows: [Employee [id=23, name=John, age=23], Employee [id=223, name=Mark, age=43]]
 FindAll : [Employee [id=23, name=John, age=23], Employee [id=223, name=Mark, age=43]]
Updated column name of table: [Employee [id=23, name=Mary, age=23], Employee [id=223, name=Mary, age=43]]
 FindAll : [Employee [id=23, name=Mary, age=23], Employee [id=223, name=Mary, age=43]]

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： 小米钱 > 《java》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多