Reading Notes for Spring 3 Core Components Tutorial Part V (Chapter 20)
This is a reading note from Spring Framework 3.1 Tutorial pdf, created by tutorialspoint. This pdf contains information for Spring 3 Core Basics, which is very useful for understanding defination and practive in Spring programming.
Note: All tips that is written by book will has a sign near it. All code example in this post comes from book.
This note includes Chapter 20 in tutorial.
Spring JDBC Framework
Spring JDBC Framework takes care of all the low-level details starting from opening the connection, prepare and execute the SQL statement, process exceptions, handing transactions and finally close the connection.
JDBCTemplate is the most popular approach of using Spring JDBC framework. JDBC Template Class
The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.
Since instance of JdbcTemplate classes are threadsafe once it is configured, you can just configure one instance of JdbcTemplate, then inject it to different DAOs.
A comment practice is to configure a DataSource in your spring configuration file, then inject that bean to your DAO classes. For example:
DAO means Data Access Object. DAO often implements an interface, which provides ablity to read/write data from database. Other parts of application access DAO.
The way to achieve CRUD(Create, Read, Update and Delete) operation is (Assume SQL ia a SQL statement):
Query:
Query for Integer: jdbcTemplateObject.queryForInt(SQL);
Query for Long: jdbcTemplateObject.queryForLong(SQL);
Query for Int, with a bind variable: jdbcTemplateObject.queryForInt(SQL, new Object[]{10});
Query for String, with a bind variable: jdbcTemplateObject.queryForInt(SQL, new Object[]{10}, String.class);
Query with ObjectMapper. This ObejctMapper will be returned after query finishes:
1
2
3
4
5
6
7
8
9
10
11
12
//Assume we want a Studnet object being returned
Student student = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, new StudentMapper());
public Student mapRow(ResultSet rs, int rowNum) throws SQLException{
Student student = new Student();
student.setName(rs.getSring("name"));
student.setId(rs.getInt("id"));
//Just apply these to setters
return Student;
}
}
Query with ObjectMapper, return multiple objects:
1
2
List<Student> students = jdbcTemplateObject.query(SQL,new StudentMapper());
//StudentMapper class is the same as example above
Insert: SQL Statement: insert into Student(name,age) values (?,?). JDBCTemplate: jdbcTemplateObject.update(SQL, new Object[]{"Tim", 11});. If you want to insert more, just add more field and and key/value pair to SQL statement and JDBCTemplate.
Update: SQL Statement: update Student set name=? where id=?. JDBCTemplate: jdbcTemplateObject.update(SLQ, new Object[]{"Time",1});
Delete: SQL Statement: delete Student where id=?. JDBCTemplate: jdbcTemplateObject.update(SQL, new Object[]{10});
Create: SQL Statement: CREATE TABLE STUDENT( + ID INT NOT NULL AUTO_INCREMENT, etc. JDBCTemplate: jdbcTemplateObject.execute(SQL);