23.JdbcTemplate的使用(xml方式)

Posted on Edited on In

 

使用JdbcTemplate实现对数据库表的CURD，使用xml配置文件方式。

项目目录结构:

项目依赖包:

Vo: User.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
package com.course.vo;

import java.util.Date;

public class User {

	private Integer id;
	private String name;
	private int age;
	private String address;
	private Date hireDate;
	
	public User() {
		super();
	}
	
	public User(String name, int age, String address, Date hireDate) {
		super();
		this.name = name;
		this.age = age;
		this.address = address;
		this.hireDate = hireDate;
	}

	public User(Integer id, String name, int age, String address, Date hireDate) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
		this.address = address;
		this.hireDate = hireDate;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public Date getHireDate() {
		return hireDate;
	}

	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", age=" + age + ", address=" + address + ", hireDate=" + hireDate
				+ "]";
	}
	
}

Dao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.course.dao;

import java.util.List;

import com.course.vo.User;

public interface UserDao {
	
	public int addUser(User user);
	public int updateUser(User user);
	public int deleteUser(User user);
	
	public int queryCount();
	public User queryUserById(Integer id);
	
	public List<User> queryAllUser();
	
	public List<User> queryUserByPage(int pageIndex, int pageSize);

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
package com.course.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.course.dao.UserDao;
import com.course.vo.User;

public class UserDaoImpl implements UserDao {

	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public int addUser(User user) {
		String sqlInsert = "insert into User(name, age, address, hire_date) values(?, ?, ?, ?)";
		Object[] paramList = {user.getName(), user.getAge(), user.getAddress(), user.getHireDate()};
		int rowCount = jdbcTemplate.update(sqlInsert, paramList);
		return rowCount;
	}

	@Override
	public int updateUser(User user) {
		String sqlUpdate = "update User set age = ?, address = ?, hire_date = ? where name = ?";
		Object[] paramList = {user.getAge(), user.getAddress(), user.getHireDate(), user.getName()};
		int rowCount = jdbcTemplate.update(sqlUpdate, paramList);
		return rowCount;
	}

	@Override
	public int deleteUser(User user) {
		String sqlDelete = "delete from User where name = ? and address = ?";
		Object[] paramList = {user.getName(), user.getAddress()};
		int rowCount = jdbcTemplate.update(sqlDelete, paramList);
		return rowCount;
	}

	@Override
	public int queryCount() {
		String sql = "select count(*) from User";
		int rowCount = jdbcTemplate.queryForObject(sql, Integer.class);
		return rowCount;
	}

	@Override
	public User queryUserById(Integer id) {
		String sql = "select * from User where id = ?";
		Object[] paramList = {id};
		User user = jdbcTemplate.queryForObject(sql, paramList, new RowMapper<User>() {

			@Override
			public User mapRow(ResultSet resultSet, int rowNum) throws SQLException {
				Integer UserId = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String address = resultSet.getString("address");
				Date hireDate = resultSet.getDate("hire_date");
				User userObj = new User(UserId, name, age, address, hireDate);
				return userObj;
			}
			
		});
		return user;
	}

	@Override
	public List<User> queryAllUser() {
		String sql = "select * from User";
		List<User> userList = jdbcTemplate.query(sql, new RowMapper<User>() {

			@Override
			public User mapRow(ResultSet resultSet, int rowNum) throws SQLException {
				Integer UserId = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String address = resultSet.getString("address");
				Date hireDate = resultSet.getDate("hire_date");
				User user = new User(UserId, name, age, address, hireDate);
				return user;
			}
			
		});
		return userList;
	}

	@Override
	public List<User> queryUserByPage(int pageIndex, int pageSize) {
		String sql = "select * from User limit ?, ?";
		Object[] paramList = {(pageIndex-1)*pageSize, pageSize};
		List<User> userList = jdbcTemplate.query(sql, paramList, new RowMapper<User>() {

			@Override
			public User mapRow(ResultSet resultSet, int rowNum) throws SQLException {
				Integer UserId = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				String address = resultSet.getString("address");
				Date hireDate = resultSet.getDate("hire_date");
				User user = new User(UserId, name, age, address, hireDate);
				return user;
			}
			
		});
		return userList;
	}
}

Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.course.service;

import java.util.List;

import com.course.vo.User;

public interface UserService {

	public int addUser(User user);
	public int updateUser(User user);
	public int deleteUser(User user);
	
	public int queryCount();
	public User queryUserById(Integer id);
	
	public List<User> queryAllUser();
	
	public List<User> queryUserByPage(int pageIndex, int pageSize);
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package com.course.service.impl;

import java.util.List;

import com.course.dao.UserDao;
import com.course.service.UserService;
import com.course.vo.User;

public class UserServiceImpl implements UserService {
	
	private UserDao userDao;
	
	public void setUserDao(UserDao userDao) {
		this.userDao = userDao;
	}

	@Override
	public int addUser(User user) {
		int rowCount = userDao.addUser(user);
		return rowCount;
	}

	@Override
	public int updateUser(User user) {
		return userDao.updateUser(user);
	}

	@Override
	public int deleteUser(User user) {
		return userDao.deleteUser(user);
	}

	@Override
	public int queryCount() {
		return userDao.queryCount();
	}

	@Override
	public User queryUserById(Integer id) {
		return userDao.queryUserById(id);
	}

	@Override
	public List<User> queryAllUser() {
		return userDao.queryAllUser();
	}

	@Override
	public List<User> queryUserByPage(int pageIndex, int pageSize) {
		return userDao.queryUserByPage(pageIndex, pageSize);
	}
}

Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
package com.course.controller;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.course.service.UserService;
import com.course.vo.User;

public class UserController {

	private UserService userService;
	
	public void setUserService(UserService userService) {
		this.userService = userService;
	}
	
	public int addUser() {
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
		Date hireDate = null;
		try {
			hireDate = simpleDateFormat.parse("2005-01-01");
		} catch (ParseException e) {
			e.printStackTrace();
		}
		User user = new User("name100", 10, "address100", hireDate);
		int rowCount = userService.addUser(user);
		return rowCount;
	}
	
	public int updateUser() {
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date hireDate = null;
		try {
			hireDate = simpleDateFormat.parse("2005-01-01 08:30:20");
		} catch (ParseException e) {
			e.printStackTrace();
		}
		User user = new User("name100", 10, "address100", hireDate);
		int rowCount = userService.updateUser(user);
		return rowCount;
	}
	
	public int deleteUser() {
		User user = new User();
		user.setName("name100");
		user.setAddress("address100");
		int rowCount = userService.deleteUser(user);
		return rowCount;
	}
	
	public int queryCount() {
		int count = userService.queryCount();
		return count;
	}
	
	public User queryUserById(Integer id) {
		return userService.queryUserById(id);
	}

	public List<User> queryAllUser() {
		return userService.queryAllUser();
	}
	
	public List<User> queryUserByPage(int pageIndex, int pageSize) {
		return userService.queryUserByPage(pageIndex, pageSize);
	}
	
}

测试类
TestMain.java: jdbcTemplate的使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package com.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.course.dao.impl.UserDaoImpl;

public class TestMain {

	/**
	 * 连接数据库
	 * 1.创建数据源
	 * 2.创建jdbcTemplate
	 * 3.进行curd
	 * @param args
	 */
	public static void main(String[] args) {
		
		// 1.创建数据源对象
		DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
		// 2.设置连接的参数
		driverManagerDataSource.setDriverClassName("com.mysql.jdbc.Driver");
		driverManagerDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mybatis_test");
		driverManagerDataSource.setUsername("root");
		driverManagerDataSource.setPassword("123456");
		
		// 3.创建jdbcTemplate
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		// jdbcTemplate关联数据源
		jdbcTemplate.setDataSource(driverManagerDataSource);
		
		// 4.创建sql
		/*
		 * jdbcTemplate
		 * - update: 新增，修改，删除
		 * - queryForObject: 查询并返回一个对象(结果集只能包含一个列)
		 * - queryForList: 查询并返回一个List(结果集只能包含一个列)
		 */
		String sqlInsert = "insert into User(name, age, address) values(?, ?, ?)";
		int rowCount = jdbcTemplate.update(sqlInsert, "name160", 160, "address160");
		System.out.println("rowCount = " + rowCount);
		// rowCount = 1
		
		String sqlUpdate = "update User set age = 10 where name = ?";
		int rowCount = jdbcTemplate.update(sqlUpdate, "user2");
		System.out.println("rowCount = " + rowCount);
		// rowCount = 3
		
		String sqlDelete = "delete from User where address < ?";
		int rowCount = jdbcTemplate.update(sqlDelete, "address2");
		System.out.println("rowCount = " + rowCount);
		// rowCount = 72
		
		String sqlQuery01 = "select count(*) from User";
		int count = jdbcTemplate.queryForObject(sqlQuery01, Integer.class);
		System.out.println("count = " + count);
		// count = 83
		
		String sqlQuery02 ="select name from User";
		Object obj = jdbcTemplate.queryForList(sqlQuery02, Object.class);
		System.out.println("obj = " + obj);
        // obj = [user2, user2, user2, user3, user12, ...]
	}
}

TestApp: 使用IoC获取jdbcTemplate对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class TestApp {

	public static void main(String[] args) {
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
		JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
		String sqlQuery01 = "select count(*) from User";
		int count = jdbcTemplate.queryForObject(sqlQuery01, Integer.class);
		System.out.println("count = " + count);
		// count = 83
	}
}

TestJdbcTemplate: 使用IoC获取所有对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
package com.test;

import java.util.List;

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

import com.course.controller.UserController;
import com.course.vo.User;

public class TestJdbcTemplate {

	
	public static void main(String[] args) {
		ApplicationContext applicationContext = 
				new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
		UserController userController =(UserController) applicationContext.getBean("userController");
		int rowCount = userController.addUser();
		System.out.println("rowCount = " + rowCount);
		// rowCount = 1
		
		int rowCount = userController.updateUser();
		System.out.println("rowCount = " + rowCount);
		// rowCount = 3
		
		int rowCount = userController.deleteUser();
		System.out.println("rowCount = " + rowCount);
		// rowCount = 3
		
		int count = userController.queryCount();
		System.out.println("count = " + count);
		// count = 83
		
		Integer userId = 5;
		User user = userController.queryUserById(userId);
		System.out.println(user);
		// User [id=5, name=user2, age=10, address=address2, hireDate=2021-08-13]

		List<User> userList = userController.queryAllUser();
		System.out.println(userList);
		// [User [id=2, name=user2, age=10, address=address2, hireDate=2021-08-13], User [id=5, name=user2, age=10, address=address2, hireDate=2021-08-13], User [id=8, name=user2, age=10, address=address2, hireDate=2021-08-13], User [id=9, name=user3, age=9, address=address3, hireDate=2021-08-13], User [id=12, name=user12, age=12, address=address2, hireDate=2021-08-16], ...]
		
		List<User> userList = userController.queryUserByPage(1, 5);
		for (User user : userList) {
			System.out.println(user);
		}
        // User [id=2, name=user2, age=10, address=address2, hireDate=2021-08-13]
        // User [id=5, name=user2, age=10, address=address2, hireDate=2021-08-13]
        // User [id=8, name=user2, age=10, address=address2, hireDate=2021-08-13]
        // User [id=9, name=user3, age=9, address=address3, hireDate=2021-08-13]
        // User [id=12, name=user12, age=12, address=address2, hireDate=2021-08-16]
	}
}

applicationContext.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<?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"
	xmlns:aop="http://www.springframework.org/schema/aop"
	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
	    http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
        
        
	<!-- 声明数据源 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<!-- 注入相关属性 -->
		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis_test"></property>
		<property name="username" value="root"></property>
		<property name="password" value="123456"></property>
	</bean>
	
	<!-- 声明JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<!-- 注入数据源 -->
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 声明Dao -->
	<bean id="userDao" class="com.course.dao.impl.UserDaoImpl">
		<!-- 注入jdbcTemplate -->
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
	
	<!-- 声明Service -->
	<bean id="userService" class="com.course.service.impl.UserServiceImpl">
		<!-- 注入userDao -->
		<property name="userDao" ref="userDao"></property>
	</bean>
	
	<!-- 声明Controller -->
	<bean id="userController" class="com.course.controller.UserController">
		<!-- 注入userService -->
		<property name="userService" ref="userService"></property>
	</bean>
	
</beans>