1、依赖配置
整体的项目结构如下
- 数据源:Spring自身提供了JDBC的数据源,也可以使用第三方的数据源,这里使用Druid数据源。
- 核心引入:引入基本的核心包、引入Spring-jdbc依赖包、lombok包、以及JDBC驱动包就行。
2、Spring核心配置
- Spring整合数据库的操作都需要配置数据源,数据源的配置是比较固定的,但是数据源是可以任意选的,常见的C3P0、DBCP、Druid以及Spring内置的数据源DriverManagerDataSource…
- 然后为封装好的JdbcTemplate创建bean对象,依赖注入可以选构造器注入、也可以使用set注入。JdbcTemplate在程序中注入到dao层,作为SQL的执行器。可以直接认为JdbcTemplate是封装好的PreparedStatement对象!
- 为了尽量脱离xml的配置,开启注解的扫描与支持。
<?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
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
https://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
https://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 开启注解支持 -->
<context:annotation-config/>
<context:component-scan base-package="com"/>
<!-- 配置数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useSSL=true&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!-- 创建bean -->
<bean id="JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg name="dataSource" ref="dataSource"/>
</bean>
</beans>
3、创建数据库、编写实体类
CREATE TABLE `user` (
`username` varchar(30) NOT NULL COMMENT '账号',
`password` varchar(30) NOT NULL COMMENT '密码',
`email` varchar(30) NOT NULL COMMENT '邮箱',
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
为了方便按照JavaBean规范编写属性,不用解决字段 - 属性不匹配的问题!并且使用lombok注解方便封装一些get/set、有参、无参构造方法。
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data //set/get方法
@NoArgsConstructor
@AllArgsConstructor
public class User {
private String username;
private String password;
private String email;
}
4、编写dao层
略去UserDao接口部分、使用Repository注解进行dao层bean对象的创建,并且在内部依赖JdbcTemplate对象,之后所有的jdbc操作全部手动释放出来,不用谢Ps对象、Rs对象!
import com.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.*;
@Repository("userDaoImpl")
public class UserDaoImpl implements UserDao{
@Autowired //可以不写set方法
private JdbcTemplate jdbc;
@Override
public int addUser(User user) {
String sql = "insert into user(`username`, `password`, `email`) value(?, ?, ?)";
Object args[] = {user.getUsername(), user.getPassword(), user.getEmail()};
int count = jdbc.update(sql,args);
return count;
}
@Override
public int deleteUser(String username) {
String sql = "delete from user where username = ?";
Object [] args = {username};
int i = jdbc.update(sql, args);
return i;
}
@Override
public int updateUser(Map<String,String> map) {
String sql = "update user set password = ?, email = ? where username = ?";
Object args[] = {map.get("password"), map.get("email"), map.get("username")};
int i = jdbc.update(sql, args);
return i;
}
@Override
public int count() {
String sql = "select count(1) from user";
Integer integer = jdbc.queryForObject(sql, Integer.class);
return integer;
}
@Override
public User selectOne(String username) {
String sql = "select *from user where username = ?";
Object args[] = {username};
User user = jdbc.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class), args);
return user;
}
@Override
public List<User> selectAll() {
String sql = "select *from user";
List<User> list = jdbc.query(sql, new BeanPropertyRowMapper<User>(User.class));
return list;
}
@Override
public int[] batchAdd(List<Object[]> args) {
String sql = "insert into user(`username`, `password`, `email`) value(?, ?, ?)";
int[] update = jdbc.batchUpdate(sql, args);
return update;
}
@Override
public int[] batchUpdate(List<Object[]> args) {
String sql = "update user set password = ? where username = ?";
int[] update = jdbc.batchUpdate(sql, args);
return update;
}
@Override
public int[] batchDelete(List<Object[]> args) {
String sql = "delete from user where username = ?";
int[] update = jdbc.batchUpdate(sql, args);
return update;
}
}
5、编写Service层
@Service("userService")
public class UserService {
@Autowired
private UserDaoImpl userDao;
public void addUser(User user){
int i = userDao.addUser(user);
System.out.println("受影响的行数 = " + i);
}
public void delUser(String name){
int i = userDao.deleteUser(name);
System.out.println("受影响的行数 = " + i);
}
public void updUser(Map<String,String> map){
int i = userDao.updateUser(map);
System.out.println("受影响的行数 = " + i);
}
public void count(){
System.out.println("查询到数据条数: "+userDao.count());
}
public void selectOne(String username){
User user = userDao.selectOne(username);
System.out.println(user.toString());
}
public void selectAll(){
List<User> list = userDao.selectAll();
System.out.println(list.toString());
}
public void batchAdd(List<Object[]> args){
int[] ints = userDao.batchAdd(args);
System.out.println(Arrays.toString(ints));
}
public void batchUpd(List<Object[]> args){
int[] ints = userDao.batchUpdate(args);
System.out.println(Arrays.toString(ints));
}
public void batchDel(List<Object[]> args){
int[] ints = userDao.batchDelete(args);
System.out.println(Arrays.toString(ints));
}
}
6、测试
写在前面,虽然现在开发中不经常使用或者干脆就不用Spring-JdbcTemplate进行数据库的操作,但是这对于使用Spring整合其他功能强大的ORM框架是有利的,了解基本的使用并不是什么坏事。
6.1、基本的CRUD
@Test
public void test_add(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
User user = new User();
user.setUsername("splaying");
user.setPassword("123456");
user.setEmail("111xxxx111@qq.com");
userService.addUser(user);
}
@Test
public void test_update(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
HashMap<String, String> map = new HashMap<>();
map.put("username", "splaying");
map.put("password", "000000");
map.put("email","123456@.qq.com");
userService.updUser(map);
}
@Test
public void test_delete(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
String username = "splaying0";
userService.delUser(username);
}
@Test
public void test_count(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.count();
}
6.2、查询单个对象
dao层核心代码
User user = jdbc.queryForObject(sql,new BeanPropertyRowMapper<User>(User.class), args);
@Test
public void test_Objcet(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.selectOne("splay");
}
6.3、查询所有的对象(List)
dao层核心代码
List<User> list = jdbc.query(sql, new BeanPropertyRowMapper<User>(User.class));
@Test
public void test_All(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
userService.selectAll();
}
6.3、批量添加
所有的批量增删改操作都是使用同样的接口,只需要在传入SQL的同时传入一个list< Obejct[] >集合即可,list封装批量操作的数目、Object数组封装单个操作对象的参数。
@Test
public void test_BatchAdd(){
ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
UserService userService = context.getBean("userService", UserService.class);
List<Object[]> list = new ArrayList<>();
Object args1[] = {"4","123","123@qq.com"}; list.add(args1);
Object args2[] = {"5","123","123@qq.com"}; list.add(args2);
Object args3[] = {"6","123","123@qq.com"}; list.add(args3);
userService.batchAdd(list);
}