Spring5 JdbcTemplate（四）JdbcTemplate是什么？Spring对JDBC的封装模板类一、准

JdbcTemplate是什么？

Spring对JDBC的封装模板类

一、准备工作

引入依赖

<dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring.version}</version>
        </dependency>
          <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
                <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.18</version>
        </dependency>

添加配置数据库连接池和jdbcTemplate

<?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:p="http://www.springframework.org/schema/p"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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/util http://www.springframework.org/schema/util/spring-util.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-cop.xsd">
<!--配置数据库连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
    <property name="username" value="root"></property>
    <property name="password" value="root"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/spring?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=GMT%2B8&amp;rewriteBatchedStatements=true&amp;allowPublicKeyRetrieval=true"></property>
</bean>
<!--  配置jdbcTemplate模板对象  -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
    <property name="dataSource" ref="dataSource"></property>
</bean>
<!--开启注自动扫描-->
    <context:component-scan base-package="com.bin.spring5"></context:component-scan>
</beans>

数据库表的创建

create database spring;
use spring;
create table User (
userId varchar(64)  primary key,
username varchar(64),
userstatus varchar(64)
);

实体类，dao的创建

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private String userid;
    private String username;
    private String userstatus;
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

二、练习jdbcTemplate基础

调用jdbcTemplate来执行持久化操作

本文只写dao实现类的方法

2.1 新增

用到jdbcTemplate.update方法

第一个参数-sql
第二个参数-可变数组参数

@Override
    public void add(User user) {
        String sql="INSERT INTO USER VALUES(?,?,?)";
        jdbcTemplate.update(sql,user.getUserid(),user.getUsername(),user.getUserstatus());
    }

测试方法

@org.junit.Test
    public void add(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        userDao.add(new User("2","lisi","1"));
    }

2.2 修改和删除使用的方法和上面一样

@Override
    public void update(User user) {
        String sql="UPDATE USER SET USERNAME = ?,USERSTATUS = ? WHERE USERID=?";
        jdbcTemplate.update(sql,user.getUsername(),user.getUserstatus(),user.getUserid());
    }

    @Override
    public void delete(String id) {
        String sql="DELETE FROM USER  WHERE USERID = ?";
        jdbcTemplate.update(sql,id);
    }

测试方法

@org.junit.Test
    public void update(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        userDao.update(new User("1","lisi","1"));
    }
    @org.junit.Test
    public void delete(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        userDao.delete("1");
    }

2.3 查询方法

2.3.1 查询总数量

jdbcTemplate.queryForObject(sql, Integer.class);

第一个-sql
第二个-返回值类型

public int selectCount() {
        String sql="SELECT COUNT(*) FROM USER";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        return integer;
    }

测试代码

    @org.junit.Test
    public void selectCount(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        System.out.println(userDao.selectCount());
    }

2.3.2 查询单个对象

User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper(User.calss),id);

第一个参数-sql
第二个参数-RowMapper接口，new BeanPropertyRowMapper(User.calss)-返回值类型
第三个参数-参数数组

@Override
    public User findById(String id) {
        String sql="SELECT * FROM USER WHERE USERID = ? ";
        User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User.class),id);
        return user;
    }

测试方法

   @org.junit.Test
    public void findById(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        System.out.println(userDao.findById("1"));
    }

2.3.3 查询集合

jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));

参数和上面前两个一样

  public List<User> findAll() {
        String sql="SELECT * FROM USER ";
        List<User> users = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));
        return users;
    }

测试方法

@org.junit.Test
    public void findAll(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        System.out.println(userDao.findAll());
    }

2.4 批量添加

jdbcTemplate.batchUpdate(sql,param); 第二个参数为List<Object[]>格式，一个参数数组的集合

public void batchAdd(List<Object[]> param){
        String sql="INSERT INTO USER VALUES(?,?,?)";
        jdbcTemplate.batchUpdate(sql,param);
    }

测试方法

@org.junit.Test
    public void batchAdd(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("jdbc.xml");
        UserDao userDao = applicationContext.getBean("userDao", UserDao.class);
        List<Object[]> list= new ArrayList<>();
        list.add(new Object[]{"3","wangwu","1"});
        list.add(new Object[]{"4","zhaoliu","1"});
        userDao.batchAdd(list);
    }

批量修改和删除类似，自己尝试完成！！！

Spring5 JdbcTemplate（四）

一、准备工作

二、练习jdbcTemplate基础

2.1 新增

2.2 修改和删除 使用的方法和上面一样

2.3 查询方法

2.3.1 查询总数量

2.3.2 查询单个对象

2.3.3 查询集合

2.4 批量添加

2.2 修改和删除使用的方法和上面一样