Use Spring JDBCTemplate to simplify JDBC operations.

Use Spring JDBCTemplate to simplify JDBC operations.

Anyone who has been familiar with java web development must know the Hibernate framework. Although it does not deny its strength, I have never felt it is too inflexible and bloated.

Today, let's talk about a JDBC Template in Spring. It encapsulates JDBC operations and is very convenient to use.

 

Let's talk about the usage of "silly" (not dependent on xml configuration ):

Write a test unit directly:

 1 package com.lcw.spring.jdbc; 2  3 import org.junit.Test; 4 import org.springframework.jdbc.core.JdbcTemplate; 5 import org.springframework.jdbc.datasource.DriverManagerDataSource; 6  7 public class JDBCTemplate { 8      9     @Test10     public void demo(){11         DriverManagerDataSource dataSource=new DriverManagerDataSource();12         dataSource.setDriverClassName("com.mysql.jdbc.Driver");13         dataSource.setUrl("jdbc:mysql:///spring");14         dataSource.setUsername("root");15         dataSource.setPassword("");16         17         JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);18         jdbcTemplate.execute("create table temp(id int primary key,name varchar(32))");19     20     }21 22 }

 

It's easy. Let's take a look at how to use the configuration file to complete addition, deletion, modification, and query of a class.

First, the DEMO directory structure:

AppliactionContext. xml

1 <? Xml version = "1.0" encoding = "UTF-8"?> 2 <beans xmlns = "http://www.springframework.org/schema/beans" 3 xmlns: p = "http://www.springframework.org/schema/p" 4 xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" 5 xsi: schemaLocation = "6 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> 7 8 <! -- Data source configuration --> 9 <bean id = "dataSource" class = "org. springframework. jdbc. datasource. driverManagerDataSource "> 10 <property name =" driverClassName "value =" com. mysql. jdbc. driver "> </property> 11 <property name =" url "value =" jdbc: mysql: /// spring "> </property> 12 <property name =" username "value =" root "> </property> 13 <property name =" password "value = ""> </property> 14 </bean> 15 16 17 18 <bean id = "jdbcTemplate" class = "org. springframework. jdbc. core. jdbcTemplate "> 19 <property name =" dataSource "ref =" dataSource "> </property> 20 </bean> 21 22 23 <bean id =" userDao "class =" com. curd. spring. impl. userDAOImpl "> 24 <property name =" jdbcTemplate "ref =" jdbcTemplate "> </property> 25 </bean> 26 27 28 29 </beans>

Interface: IUserDAO. java

 1 package com.curd.spring.dao; 2  3 import java.util.List; 4  5 import com.curd.spring.vo.User; 6  7 public interface IUserDAO { 8  9     public void addUser(User user);10 11     public void deleteUser(int id);12 13     public void updateUser(User user);14 15     public String searchUserName(int id);16     17     public User searchUser(int id);18     19     public List<User> findAll();20 21 }

 

Interface implementation class: UserDAOImpl. java

According to the previous Spring dependency injection, we need to use the constructor in the interface implementation class to obtain the JdbcTemplate.

Spring has helped us think of this for a long time. It provides us with the JdbcDaoSupport class. All DAO inherited this class will automatically obtain the JdbcTemplate (provided that DataSource is injected ).

1     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">2         <property name="dataSource" ref="dataSource"></property>3     </bean>4     5     6     <bean id="userDao" class="com.curd.spring.impl.UserDAOImpl">7         <property name="jdbcTemplate" ref="jdbcTemplate"></property>8     </bean>

You can use getJdbcTemplate directly in our implementation class to obtain the operation object.

JdbcTemplate mainly provides the following methods:

1. execute method: it can be used to execute any SQL statement and is generally used to execute DDL statements;

2. update method and batchUpdate method: the update method is used to execute statements such as ADD, modify, and delete. The batchUpdate method is used to execute statements related to batch processing;

3. query method and queryForXXX method: used to execute query-related statements;

4. call method: used to execute stored procedures and function-related statements.

1 package com. curd. spring. impl; 2 3 import java. SQL. resultSet; 4 import java. SQL. SQLException; 5 import java. util. list; 6 7 import org. springframework. jdbc. core. rowMapper; 8 import org. springframework. jdbc. core. support. jdbcDaoSupport; 9 import com. curd. spring. dao. IUserDAO; 10 import com. curd. spring. vo. user; 11 12 public class UserDAOImpl extends JdbcDaoSupport implements IUserDAO {13 14 public void DdUser (User user) {15 String SQL = "insert into user values (?,?,?) "; 16 this. getJdbcTemplate (). update (SQL, user. getId (), user. getUsername (), 17 user. getPassword (); 18} 19 20 public void deleteUser (int id) {21 String SQL = "delete from user where id =? "; 22 this. getJdbcTemplate (). update (SQL, id); 23 24} 25 26 public void updateUser (User user) {27 String SQL = "update User set username = ?, Password =? Where id =? "; 28 this. getJdbcTemplate (). update (SQL, user. getUsername (), 29 user. getPassword (), user. getId (); 30} 31 32 public String searchUserName (int id) {// simple query, by ID, returns the String 33 String SQL = "select username from user where id =? "; 34 // The return type is String (String. class) 35 return this. getJdbcTemplate (). queryForObject (SQL, String. class, id); 36 37} 38 39 public List <User> findAll () {// complex query return List set 40 String SQL = "select * from user "; 41 return this. getJdbcTemplate (). query (SQL, new UserRowMapper (); 42 43} 44 45 public User searchUser (int id) {46 String SQL = "select * from user where id =? "; 47 return this. getJdbcTemplate (). queryForObject (SQL, new UserRowMapper (), id); 48} 49 50 class UserRowMapper implements RowMapper <User> {51 // rs is the returned result set, 52 public User mapRow (ResultSet rs, int rowNum) throws SQLException {53 54 User user User = new user (); 55 User. setId (rs. getInt ("id"); 56 user. setUsername (rs. getString ("username"); 57 user. setPassword (rs. getString ("password"); 58 return user; 59} 60 61} 62 63}

Test class: UserTest. java

1 package com. curd. spring. test; 2 3 import java. util. list; 4 5 import org. junit. test; 6 import org. springframework. context. applicationContext; 7 import org. springframework. context. support. classPathXmlApplicationContext; 8 9 import com. curd. spring. dao. IUserDAO; 10 import com. curd. spring. vo. user; 11 12 public class UserTest {13 14 @ Test // Add 15 public void demo1 () {16 User user = new User (); 17 user. setId (3); 18 user. setUsername ("admin"); 19 user. setPassword ("123456"); 20 21 ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); 22 IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); 23 dao. addUser (user); 24 25} 26 27 @ Test // change 28 public void demo2 () {29 User user User = new user (); 30 user. setId (1); 31 user. setUsername ("admin"); 32 user. setPassword ("admin"); 33 34 ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); 35 IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); 36 dao. updateUser (user); 37} 38 39 @ Test // Delete 40 public void demo3 () {41 ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); 42 IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); 43 dao. deleteUser (3); 44} 45 46 @ Test // query (simple query, returns a string) 47 public void demo4 () {48 ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); 49 IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); 50 String name = dao. searchUserName (1); 51 System. out. println (name); 52} 53 54 @ Test // query (simple query, return object) 55 public void demo5 () {56 ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); 57 IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); 58 User user = dao. searchUser (1); 59 System. out. println (user. getUsername (); 60} 61 62 @ Test // query (complex query, returned object set) 63 public void demo6 () {64 ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); 65 IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); 66 List <User> users = dao. findAll (); 67 System. out. println (users. size (); 68} 69 70 71 72}

How about it? It's very easy. In JDBC, the flexible SQL operations are eliminated from complicated operations ~

 

Appendix:

1. Spring provides a support class for each persistence technology and injects the template tool class into DAO.
(1) JDBC: org. springframework. jdbc. core. support. JdbcDaoSupport
(2) Hibernate 3.0: org. springframework. orm. hibernate3.support. HibernateDaoSupport
(3) iBatis: org. springframework. orm. ibatis. support. SqlMapClientDaoSupport

You only need to inherit JdbcDaoSupport to write DAO by yourself, and you can inject JdbcTemplate

2. Use jdbcTemplate to provide int update (String SQL, Object... args) for addition, modification, and deletion.

 

3. Simple query: the original data type and String type are returned.
String SQL = "select count (*) from user"; // int queryForInt (String SQL)
String SQL = "select name from user where id =? "; // <T> T queryForObject (String SQL, Class <T> requiredType, Object... args)

 

4. Complex Query
JdbcTemplate does not have handler, and the object encapsulation is completed manually.

Write an object class RowMapper
Class UserRowMapper implements RowMapper <User> {
@ Override
Public User mapRow (ResultSet rs, int rowNum) throws SQLException {
// Rs already points to each data entry. You do not need to call next to direct rs to the data conversion User object.
User user = new User ();
User. setId (rs. getInt ("id "));
User. setName (rs. getString ("name "));
Return user;
}
}

Query a single Object <T> T queryForObject (String SQL, RowMapper <T> rowMapper, Object... args)
Return this. getJdbcTemplate (). queryForObject (SQL, new UserRowMapper (), id );

Query all Object List sets <T> List <T> query (String SQL, RowMapper <T> rowMapper, Object... args)
Return this. getJdbcTemplate (). query (SQL, new UserRowMapper ());

 

The methods provided above can basically meet our daily needs.

Is the JDBC class of simplejdbctemplate in spring 31 discarded? Which one is used instead?

You have not imported the package .....

How does spring obtain jdbctemplate?

Configuration in applicationContext. xml
<Bean id = "jt" class = "org. springframework. jdbc. core. JdbcTemplate">
<Property name = "dataSource" ref = "dataSource"> </property>
</Bean>

<! -- Configure the data source -->
<Bean id = "dataSource" class = "org. springframework. jdbc. datasource. DriverManagerDataSource">
<! -- Property name property: Call the corresponding setter Method -->
<Property name = "driverClassName" value = "com. mysql. jdbc. Driver"> </property>
<Property name = "url" value = "jdbc: mysql: // localhost: 3306/test"> </property>
<Property name = "username" value = "root"> </property>
<Property name = "password" value = "root"> </property>
</Bean>