Spring6—JdbcTemplate基础

原创

QGS

发布于 2023-10-31 21:33:38

3480

发布于 2023-10-31 21:33:38

文章被收录于专栏：QGS星球

JdbcTemplate是Spring提供的一个JDBC模板类，是对JDBC的封装，简化JDBC代码。可以让Spring集成其他的ORM框架:Mybatis、Hibernate等

环境：JDK17+IDEA+Maven+Spring6+JdbcTemplate

环境准备

准备数据库表
新建模板 New Module            Maven项目

pom.xml配置
<!--    仓库地址Spring io-->
    <repositories>
<!--        Spring6里程碑版本的仓库-->
        <repository>
            <id>repository.Spring.milestone</id>
            <name>Spring Milestone Repository</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>
    <dependencies>
<!--        Spring context依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>6.0.0-M2</version>
        </dependency>
        <!--        Spring jdbc依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>6.0.3</version>
        </dependency>
        <!--        mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>
        <!--junit依赖-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

Bean

数据源
//只要实现了DataSource接口都是数据源。
//数据源存在的目的是为了提供Connection对象
// dbcp连接池，C3p0连接池，都实现了DataSource接口
自己写的driver、url、name、password，需提供set方法，set注入

SpringConfig.xml配置

<!--配置数据源-->
    <bean id="ds" class="com.bean.DateSource.DateSourcedate">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://ip:3306/mysql?"/>
        <property name="username" value="root"/>
        <property name="password" value="111111"/>
    </bean>
    <!--    也可以集成其他开发的数据源c3p0 dbcp等-->

    <!--    配置JabcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="ds"></property>

测试类

JdbcTemplate-insert

在JdbcTemplate环境准备好的情况下
测试类

@Test
    public void JdbcInsertTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        //insert
        String sql ="INSERT INTO TEST1 VALUES(?,?,?,?,?)";
        //在JdbcTemplate模板中只要是增删改都是update
        int update = jabcTemplate.update(sql, 26, "李四", "南京", "2023-01-01", "女");
        System.out.println("影响了:"+update+"行");
    }

JdbcTemplate修改和删除

修改

删除

JdbcTemplate查一个对象

@Test
    public void JdbcSelectTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="SELECT p_id,p_name,address,birth,gender FROM TEST1 WHERE p_id=?";
        User user = jabcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class),25);
        System.out.println(user.toString());
    }

JdbcTemplate查多个对象

@Test
    public void JdbcSelectAllTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        //执行sql
        String sql ="SELECT p_id,p_name,address,birth,gender FROM TEST1 ";
        List<User> query = jabcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
        query.forEach(e-> System.out.println(e.toString()));
    }

JdbcTemplate查一个值

@Test
    public void selectOneValueTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="SELECT COUNT(*) FROM TEST1 WHERE gender='男'";
        Integer integer = jabcTemplate.queryForObject(sql, Integer.class);
        System.out.println(integer);
    }

JdbcTemplate批量添加

public void batchInsertTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="INSERT INTO TEST1 VALUES(?,?,?,?,?)";
        Object[] objects1 ={27, "小妮", "南京", "2023-01-01", "女"};
        Object[] objects2 ={28, "小红", "上海", "2023-01-01", "女"};
        Object[] objects3 ={29, "小月", "南宁", "2023-01-01", "女"};
        Object[] objects4 ={30, "小欣", "深圳", "2023-01-01", "女"};
        List<Object[]> list = new ArrayList<>();
        list.add(objects1);
        list.add(objects2);
        list.add(objects3);
        list.add(objects4);
        //在JdbcTemplate中只要是增删改都是update
        int[] count = jabcTemplate.batchUpdate(sql, list);
        System.out.println(count);
    }

JdbcTemplate批量更新

@Test
    public void batchUpdateTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="UPDATE TEST1 SET gender=? WHERE p_id=?";
        Object[] objects1 ={"女",27};
        Object[] objects2 ={"女",28};
        List<Object[]> list = new ArrayList<>();
        list.add(objects1);
        list.add(objects2);
        //在JdbcTemplate中只要是增删改都是update
        int[] count = jabcTemplate.batchUpdate(sql, list);
        System.out.println(count);
    }

JdbcTemplate批量删除

@Test
    public void batchDeleteTest(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="DELETE  FROM TEST1 WHERE p_id=?";
        Object[] objects1 ={27};
        Object[] objects2 ={28};
        List<Object[]> list = new ArrayList<>();
        list.add(objects1);
        list.add(objects2);
        int[] count = jabcTemplate.batchUpdate(sql, list);
        System.out.println(count.length);
    }

JdbcTemplate回调函数

写jdbc代码，可以使用回调函数
@Test
    public void callBackTest(){
        //写jdbc代码，可以使用回调函数
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml");
        JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="SELECT p_id,p_name,gender FROM TEST1 WHERE p_id=?";
        //注册回调函数，执行execute方法时会执行doInPreparedStatement()
        User execute = jabcTemplate.execute(sql, new PreparedStatementCallback<User>() {
            @Override
            public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                User user = null;
                ps.setInt(1, 26);
                ResultSet resultSet = ps.executeQuery();
                if (resultSet.next()) {
                    int id = resultSet.getInt("p_id");
                    String name = resultSet.getString("p_name");
                    String gender = resultSet.getString("gender");
                    user = new User();
                    user.setP_id(id);
                    user.setP_name(name);
                    user.setGender(gender);
                }
                return user;
            }
        });
        System.out.println(execute.toString());
    }

JdbcTemplate回调函数整合德鲁伊连接池

德鲁伊连接池(阿里巴巴开发)
Pom.xml
<!--        引入德鲁伊连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>