JDBCTemplate 的简单使用

什么是JDBCTemplate

Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

使用JDBCTemplate

1.在xml文件中配置 JdbcTemplate 对象，注入 DataSource（即数据库、数据源）

<context:component-scan base-package="transaction" />

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!--数据库驱动-->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <!--连接数据库URL-->
        <property name="url" value="jdbc:mysql://localhost:3306/0908?characterEncoding=UTF-8&amp;serverTimezone=GMT"/>
        <property name="username" value="root"/>
        <property name="password" value="root" />

    </bean>

    <!--配置JDBCTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--配置数据源-->
        <property name="dataSource" ref="dataSource"/>
    </bean>

2.创建实体类

public class User {

    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

3.测试

package jdbc_template;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.Arrays;
import java.util.List;
import java.util.Map;


/**
 * @Author ywzh
 * @Date 2023/9/8 10:05
 * @注释 JdbcTemplate测试
 */
public class JdbcTest {

    private JdbcTemplate jdbcTemplate;

    @Before//标识单元测试启动方法（初始化工作）
    public void before(){

        DataSource dataSource = new DataSource();

        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/0908?characterEncoding=UTF-8&&serverTimezone=GMT");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSource.setInitialSize(5);

        jdbcTemplate = new JdbcTemplate(dataSource);

        System.out.println("开始测试");
    }

    @After//标识单元测试销毁方法（清理工作）
    public void after(){
        System.out.println("结束测试");
    }

    @Test//查询所有用户
    public void Test1(){
        List<Map<String,Object>> maps = jdbcTemplate.queryForList("select * from user");
        System.out.println(maps);
    }

    @Test//单行记录转换为对象
    public void Test3(){

        String sql = "select * from user where id = ?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        User user = jdbcTemplate.queryForObject(sql,rowMapper,1);
        System.out.println(user);
    }

    @Test//条件查询
    public void Test4(){
        String sql = "select * from user where id > ?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        List<User> users = jdbcTemplate.query(sql,rowMapper,1);
        System.out.println(users);

    }

    @Test//使用execute()方法插入
    public void Test5(){
        String sql = "insert into user (id,name,pwd) values (4,'ls','123')";
        jdbcTemplate.execute(sql);

    }

    @Test//使用update()方法插入
    public void Test6(){
        String sql = "insert into user (id,name,pwd) values (5,'ls','123')";
        int update = jdbcTemplate.update(sql);//影响行数
        System.out.println(update);
    }

    @Test //插入（有参）
    public void Test7(){
        String sql = "insert into user (id,name,pwd) values (?,?,?)";

        int update = jdbcTemplate.update(sql,6,"zl","123");
        System.out.println(update);

    }

    @Test //批量插入
    public void Test8(){
        //将数组转化为List集合
        List<Object[]> list = Arrays.asList(
                new Object[] {7,"ll1","123"},
                new Object[] {8,"ll2","123"},
                new Object[] {9,"ll3","123"},
                new Object[] {10,"ll4","123"}
        );

        int[] updateRows = jdbcTemplate.batchUpdate("insert  into user (id,name,pwd) value(?,?,?)",list);

        for (int i = 0; i < updateRows.length; i++) {
            System.out.println(updateRows[i]);
        }

    }

    @Test//删除
    public void Test9(){
        int row = jdbcTemplate.update("delete from user where id = ?",1);
        System.out.println(row);
    }


}