Use Spring JDBCTemplate to simplify JDBC operations
Those of you who have been exposed to JAVA WEB development are sure to know the Hibernate framework. Although I do not deny its power, I have never been impressed by it personally, always feeling that it is not flexible enough and too bloated.
Today we’ll take a look at a Spring helper class for JDBC (JDBC Template), which encapsulates JDBC operations and is very easy to use.
First, the use of “dumb-ass” (independent of 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 @Test
10 public void demo(a){
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 }
Copy the codeVery simple, let’s take a look at the use of combined configuration file, complete implementation of a class add, delete, change, check
First, 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>
Copy the codeInterface: 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(a);
20
21 }
Copy the codeInterface implementation class: UserDaoimp.java
With traditional Spring dependency injection, we need to use the constructor in the interface implementation class to get the JdbcTemplate
Spring has already figured this out by providing a JdbcDaoSupport class that all DAOs inherit from and automatically get the JdbcTemplate (provided that the 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>
Copy the codeWe use getJdbcTemplate directly in our implementation class to get the action object.
The JdbcTemplate mainly provides the following methods:
1, execute method: can be used to execute any SQL statement, generally used to execute DDL statement;
2, update method and batchUpdate method: update method is used to execute new, modify, delete statements; The batchUpdate method is used to execute batch-related statements;
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 addUser(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, query by ID, return 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(a) {// Complex queries return a List collection
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 return result set, encapsulated in each behavior unit
52 public User mapRow(ResultSet rs, int rowNum) throws SQLException {
53
54 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 }
Copy the codeTest 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/ / to add
15 public void demo1(a){
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(a){
29 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(a){
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, return string)
47 public void demo4(a){
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(a){
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, return object collection)
63 public void demo6(a){
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 }
Copy the codeHow about, very simple, in the lack of JDBC SQL flexible operation in addition to complex operation ~
【 References 】