JdbcTemplate-CSDN博客

本文链接：https://blog.csdn.net/qq_41729148/article/details/113576163

1、什么是 JdbcTemplate

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

2、搭建环境

（1）引入相关 jar 包
在这里插入图片描述
（2）在 spring 配置文件配置数据库连接池

 <!--配置数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></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.atguigu"></context:component-scan>

（3）创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象，在service中注入dao的实现类。

package com.atguigu.spring.dao;

import com.atguigu.spring.entity.User;

import java.util.List;

public interface UserDao {
    public void add(User user);



    public void update(User user);

    public void delete(String id);

    public int count();

    User findOne(String id);

    List<User> findMore();

    //批量添加
    void batchadd(List<Object[]> batckargs);

    //批量修改
    void batchupdate(List<Object[]> batckargs);

    //批量删除
    void batchdelete(List<Object[]> batcharags);
}

package com.atguigu.spring.dao;

import com.atguigu.spring.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

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

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

    @Override
    public void add(User user) {
        //1创建sql语句
        String sql = "insert into user values(?,?,?)";
        //调用方法实现
        Object[] args = {user.getUserId (),user.getUsername (),user.getUstatus ()};
        int update = jdbcTemplate.update (sql, args);
        System.out.println (update);
    }

    @Override
    public void delete(String id) {
        //1创建sql语句
        String sql = "delete from user where userId = ?";
         int update = jdbcTemplate.update (sql, id);
         System.out.println (update);
    }

    @Override
    public int count() {
        String sql = "select count(*) from user";
        final Integer integer = jdbcTemplate.queryForObject (sql, Integer.class);
        return integer;
    }

    @Override
    public User findOne(String id) {
        String sql = "select * from user where userId = ?";
          User user = jdbcTemplate.queryForObject (sql, new BeanPropertyRowMapper<User> (User.class),id);
        return user;
    }

    @Override
    public List<User> findMore() {
        String sql = "select * from user";
        final List<User> query = jdbcTemplate.query (sql, new BeanPropertyRowMapper<User> (User.class));
        return query;
    }

    @Override
    public void batchadd(List<Object[]> batckargs) {
        String sql = "insert into user values(?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate (sql, batckargs);
        System.out.println (Arrays.toString (ints));
    }

    @Override
    public void batchupdate(List<Object[]> batckargs) {
        String sql = "update user set username = ?,ustatus = ? where userId = ?";
         int[] ints = jdbcTemplate.batchUpdate (sql, batckargs);
         System.out.println (Arrays.toString (ints));
    }

    @Override
    public void batchdelete(List<Object[]> batcharags) {
        String sql = "delete from user where userId  = ?";
        int[] ints = jdbcTemplate.batchUpdate (sql, batcharags);
         System.out.println (Arrays.toString (ints));
    }

    @Override
    public void update(User user) {
        //创建sql语句
        String sql = "update user set username = ?,ustatus = ? where userId = ?";
        Object[] args = {user.getUsername (),user.getUstatus (),user.getUserId ()};
        int update = jdbcTemplate.update (sql, args);
        System.out.println (update);
    }
}

package com.atguigu.spring.entity;

public class User {
    private String userId;
    private String username;
    private String ustatus;

    public String getUserId() {
        return userId;

    }

    public User() {
    }

    public User(String userId, String username, String ustatus) {
        this.userId = userId;
        this.username = username;
        this.ustatus = ustatus;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUstatus() {
        return ustatus;
    }

    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }
}

package com.atguigu.spring.service;

import com.atguigu.spring.dao.UserDao;
import com.atguigu.spring.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    //增加
    public void add(User user) {
        userDao.add (user);
    }

    //删除
    public void delete(String id) {
        userDao.delete (id);
    }

    //修改
    public void update(User user) {
        userDao.update (user);
    }

    //查询表记录数
    public int count() {
        return userDao.count ();
    }

    //查询返回一个对象
    public User findOne(String id) {
      return   userDao.findOne (id);
    }

    //查询返回对象集合
    public List<User> findMore() {
       return userDao.findMore ();
    }

    //批量添加
    public void batchadd(List<Object[]> batckargs){
         userDao.batchadd(batckargs);
    }
    //批量修改
    public void batchupdate(List<Object[]> batckargs){
        userDao.batchupdate(batckargs);
    }

    //批量删除
    public void batchdelete(List<Object []> batcharags)
    {
        userDao.batchdelete(batcharags);
    }

}

（4）创建测试类，测试这些方法。

package com.atguigu.spring.test;

import com.atguigu.spring.entity.User;
import com.atguigu.spring.service.UserService;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class test {
    @Test
    public void testadd(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
         UserService userService = applicationContext.getBean ("userService", UserService.class);
        User user = new User ();
        user.setUserId ("1");
        user.setUsername ("张三");
        user.setUstatus ("yes");
        userService.add (user);
    }
    @Test
    public void testdelete(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
        userService.delete ("1");
    }
    @Test
    public void testupdate(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
        User user = new User ();
        user.setUserId ("1");
        user.setUsername ("李四");
        user.setUstatus ("yesss");
        userService.update (user);
    }
    @Test
    public void testcount(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
       System.out.println (userService.count ());
    }
    @Test
    public void testfindone(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
         User one = userService.findOne ("1");
        System.out.println (one);
    }
    @Test
    public void testmore(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
         List<User> more = userService.findMore ( );
         System.out.println (more);
    }
    @Test
    public void testbatchadd(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
       List<Object[]> batchargs = new ArrayList<> ();
       Object[] o1 = {"11","李","yes"};
       Object[] o2 = {"13","王","no"};
       Object[] o3 = {"12","大","yes"};
       batchargs.add (o1);
       batchargs.add (o2);
       batchargs.add(o3);
       userService.batchadd (batchargs);
    }
    @Test
    public void testbatchupdate(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
        List<Object[]> batchargs = new ArrayList<> ();
        Object[] o1 = {"121","李","yes"};
        Object[] o2 = {"123","王","no"};
        Object[] o3 = {"122","大","yes"};
        batchargs.add (o1);
        batchargs.add (o2);
        batchargs.add(o3);
        userService.batchupdate (batchargs);
    }
    @Test
    public void testbatchdelete()
    {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("bean1.xml");
        UserService userService = applicationContext.getBean ("userService", UserService.class);
        List<Object[]> batchargs = new ArrayList<> ();
        Object[] o1 = {"11"};
        Object[] o2 = {"13"};
        Object[] o3 = {"12"};
        batchargs.add (o1);
        batchargs.add (o2);
        batchargs.add(o3);
        userService.batchdelete (batchargs);
    }

}

（5）结果。
在这里插入图片描述