Spring 框架对 JDBC 进行了封装,使用 JdbcTemplate 方便实现对数据库操作。
新建一个maven工程,引入依赖:
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.0.0</version>
<scope>compile</scope>
</dependency>
数据库脚本:
CREATE DATABASE /*!32312 IF NOT EXISTS*/`spring` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `spring`;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`status` varchar(20) DEFAULT NULL,
`money` decimal(16,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
spring配置文件(spring-config.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"
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 ">
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql:///spring?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="12345"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<!-- JdbcTemplate 对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入 dataSource-->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 组件扫描 -->
<context:component-scan base-package="top.wushanghui"/>
</beans>
实体类
public class User {
private BigInteger id;
private String name;
private String status;
private BigDecimal money;
public User(BigInteger id, String name, String status, BigDecimal money) {
this.id = id;
this.name = name;
this.status = status;
this.money = money;
}
public User(String name, String status, BigDecimal money) {
this.name = name;
this.status = status;
this.money = money;
}
public User() {
}
public BigInteger getId() {
return id;
}
public void setId(BigInteger id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public BigDecimal getMoney() {
return money;
}
public void setMoney(BigDecimal money) {
this.money = money;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", status='" + status + '\'' +
", money=" + money +
'}';
}
}
dao层:
public interface UserDao {
public int add(User user);
public int[] batchAdd(List<User> userList);
public int update(User user);
public int[] batchUpdate(List<User> userList);
public int delete(BigInteger id);
public int[] batchDelete(List<BigInteger> list);
public User getOne(BigInteger id);
public List<User> getAll();
public int queryCount();
}
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int add(User user) {
String sql = "insert into user(name, status, money) values(?,?,?)";
Object[] args = {user.getName(), user.getStatus(), user.getMoney()};
return jdbcTemplate.update(sql, args);
}
@Override
public int[] batchAdd(List<User> userList) {
List<Object[]> list = new ArrayList<>();
userList.forEach(u -> list.add(new Object[]{u.getName(), u.getStatus(), u.getMoney()}));
String sql = "insert into user(name, status, money) values(?,?,?)";
return jdbcTemplate.batchUpdate(sql, list);
}
@Override
public int update(User user) {
String sql = "update user set name = ?, status = ?, money = ? where id = ?";
Object[] args = {user.getName(), user.getStatus(), user.getMoney(), user.getId()};
return jdbcTemplate.update(sql, args);
}
@Override
public int[] batchUpdate(List<User> userList) {
String sql = "update user set name = ?, status = ?, money = ? where id = ?";
List<Object[]> list = new ArrayList<>();
userList.forEach(u -> list.add(new Object[]{u.getName(), u.getStatus(), u.getMoney(), u.getId()}));
return jdbcTemplate.batchUpdate(sql, list);
}
@Override
public int delete(BigInteger id) {
String sql = "delete from user where id = ?";
return jdbcTemplate.update(sql, id);
}
@Override
public int[] batchDelete(List<BigInteger> ids) {
String sql = "delete from user where id = ?";
List<Object[]> list = new ArrayList<>();
ids.forEach(i -> list.add(new Object[]{i}));
return jdbcTemplate.batchUpdate(sql, list);
}
@Override
public User getOne(BigInteger id) {
String sql = "select * from user where id = ?";
//调用方法
return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
}
@Override
public List<User> getAll() {
String sql = "select * from user";
//调用方法
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}
@Override
public int queryCount() {
String sql = "select count(*) from user";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
}
service 层
public interface UserService {
public int add(User user);
public int[] batchAdd(List<User> userList);
public int update(User user);
public int[] batchUpdate(List<User> userList);
public int delete(BigInteger id);
public int[] batchDelete(List<BigInteger> list);
public User getOne(BigInteger id);
public List<User> getAll();
public int queryCount();
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public int add(User user) {
return userDao.add(user);
}
@Override
public int[] batchAdd(List<User> list) {
return userDao.batchAdd(list);
}
@Override
public int update(User user) {
return userDao.update(user);
}
@Override
public int[] batchUpdate(List<User> userList) {
return userDao.batchUpdate(userList);
}
@Override
public int delete(BigInteger id) {
return userDao.delete(id);
}
@Override
public int[] batchDelete(List<BigInteger> list) {
return userDao.batchDelete(list);
}
@Override
public User getOne(BigInteger id) {
return userDao.getOne(id);
}
@Override
public List<User> getAll() {
return userDao.getAll();
}
@Override
public int queryCount() {
return userDao.queryCount();
}
}
测试类:
package top.wushanghui.main;
import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import top.wushanghui.entity.User;
import top.wushanghui.service.UserService;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class Main {
private UserService userService;
{
ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-config.xml");
userService = ctx.getBean(UserService.class);
}
/**
* 单条数据添加
*/
@Test
public void testAdd() {
int num = userService.add(new User("Tom", "0", new BigDecimal("1000")));
System.out.println("新增个数:" + num);
}
/**
* 批量添加
*/
@Test
public void testBatchAdd() {
List<User> userList = new ArrayList<>();
userList.add(new User("批量添加1", "0", new BigDecimal("1000")));
userList.add(new User("批量添加2", "0", new BigDecimal("2000")));
int[] ints = userService.batchAdd(userList);
System.out.println("批量添加: " + Arrays.toString(ints));
}
/**
* 单条数据更新
*/
@Test
public void testUpdate() {
User user = new User(new BigInteger("7"), "Tom", "1", new BigDecimal("2000"));
int num = userService.update(user);
System.out.println("更新个数:" + num);
}
/**
* 批量更新
*/
@Test
public void testBatchUpdate() {
List<User> userList = new ArrayList<>();
userList.add(new User(new BigInteger("8"), "批量添加1", "1", new BigDecimal("3000")));
userList.add(new User(new BigInteger("9"), "批量添加2", "1", new BigDecimal("3000")));
int[] ints = userService.batchUpdate(userList);
System.out.println("批量更新: " + Arrays.toString(ints));
}
/**
* 单条数据删除
*/
@Test
public void testDelete() {
int delete = userService.delete(new BigInteger("7"));
System.out.println("删除个数:" + delete);
}
/**
* 批量数据删除
*/
@Test
public void testBatchDelete() {
List<BigInteger> list = new ArrayList<>();
list.add(new BigInteger("8"));
list.add(new BigInteger("9"));
int[] ints = userService.batchDelete(list);
System.out.println("批量删除: " + Arrays.toString(ints));
}
/**
* 获取单条数据
*/
@Test
public void testGetOne() {
User user = userService.getOne(new BigInteger("9"));
System.out.println(user);
}
/**
* 获取全部数据
*/
@Test
public void testGetAll() {
List<User> all = userService.getAll();
System.out.println(all);
}
/**
* 全部条数
*/
@Test
public void queryCount() {
int count = userService.queryCount();
System.out.println("num:" + count);
}
}