Spring JdbcTemplate

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);
    }


}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值