Spring_JdbcTemplate的简单使用

推荐视频：尚硅谷-Spring5框架最新版教程（idea版）

这是我觉得很不错的一套视频，我的spring系列的文章全是学习上述视频的学习笔记

1 对数据库的增、删、改操作

使用JdbcTemplate的update方法

public int update(String sql, @Nullable Object... args);

sql 对应操作的sql语句

args 对应操作所需要的参数数组

1.1 导入依赖

<!--数据库连接池-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.12</version>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.9.RELEASE</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<!--事务-->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.2.9.RELEASE</version>
</dependency>

<!--整合mybatis、等数据持久层框架时需要-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.2.9.RELEASE</version>
</dependency>

1.2 配置文件

特别的：

MySQL8的driverClassName=“com.mysql.cj.jdbc.Driver”

MySQL5的driverClassName=“com.mysql.jdbc.Driver”

<?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">

    <context:component-scan base-package="com.xtc"/>

    <!-- 阿里 druid 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <!-- 数据库基本信息配置 -->
        <property name="url" value="jdbc:mysql://localhost:3306/db_spring?serverTimezone=UTC"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <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>
</beans>

1.3 基类

UserService

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

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

UserDao

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

UserDaoImpl

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

    public void add(User user) {
        String sql = "insert into t_user values(?,?,?)";
        Object[] args = {user.getId(),user.getUsername(),user.getPassword()};
        int update = jdbcTemplate.update(sql, args);
        System.out.println(update);
    }
}

User

public class User {
    private Integer id;
    private String username;
    private String password;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

1.4 测试类

public class UserTest {
    @Test
    public void test01(){
        ApplicationContext context = new ClassPathXmlApplicationContext("bean0.xml");
        UserService userService = context.getBean("userService", UserService.class);
        User user = new User();
        user.setId(2);
        user.setUsername("abc");
        user.setPassword("123");

        userService.add(user);
    }
}

2 对数据库的查询操作

依赖和配置文件同10.1

这里只写基类和测试类

2.1 基类

// 查询数据条数
public void selectCount() {
    String sql = "select count(*) from t_user";
    Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
    System.out.println(integer);

}

// 查询对象
public void selectOne(Integer id) {
    String sql = "select * from t_user where id=?";
    User user1 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
    System.out.println(user1.toString());
}

// 查询对象数组
public List<User> selectList() {
    String sql = "select * from t_user";
    List<User> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
    return query;
}

2.2 测试类

// 查询数据条数
// userService.selectCount();

// 查询对象，根据id
// userService.selectOne(1);

// 查询所有
List<User> users = userService.selectList();
System.out.println(users);

3 批量操作

3.1 批量添加

基类

// 批量添加
public void addUsers(List<Object[]> objs){
    String sql ="insert into t_user values(?,?,?)";
    int[] ints = jdbcTemplate.batchUpdate(sql, objs);
    System.out.println(Arrays.toString(ints));
}

测试类

// 批量添加
List<Object[]> objects = new ArrayList<Object[]>();
Object[] objects1 = {"6","abc","333"};
Object[] objects2 = {"7","abc","444"};
Object[] objects3 = {"8","abc","555"};
objects.add(objects1);
objects.add(objects2);
objects.add(objects3);

userService.addUsers(objects);

3.2 批量修改

基类

// 批量修改
public void updateUsers(List<Object[]> objects) {
    String sql = "update t_user set username=? where id=?";
    int[] ints = jdbcTemplate.batchUpdate(sql, objects);
    System.out.println(Arrays.toString(ints));
}

测试类

List<Object[]> objects = new ArrayList<Object[]>();
Object[] objects1 = {"abc","6"};
Object[] objects2 = {"abc","7"};
Object[] objects3 = {"abc","8"};
objects.add(objects1);
objects.add(objects2);
objects.add(objects3);

userService.updateUsers(objects);

3.3 批量删除

基类

// 批量删除
public void deleteUser(List<Object[]> objects) {
    String sql = "delete from t_user where id=?";
    int[] ints = jdbcTemplate.batchUpdate(sql, objects);
    System.out.println(Arrays.toString(ints));
}

测试类

List<Object[]> objects = new ArrayList<Object[]>();
Object[] objects1 = {3};
Object[] objects2 = {4};
Object[] objects3 = {5};
objects.add(objects1);
objects.add(objects2);
objects.add(objects3);

userService.deleteUser(objects);

4 本文用到的数据库脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

#  Table structure for t_user

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int NOT NULL,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

# Records of t_user

INSERT INTO `t_user` VALUES (1, 'abc', 'abc');
INSERT INTO `t_user` VALUES (2, 'abc', 'abc');
INSERT INTO `t_user` VALUES (5, 'dehi', '555');
INSERT INTO `t_user` VALUES (6, 'abc', '333');
INSERT INTO `t_user` VALUES (7, 'abc', '444');
INSERT INTO `t_user` VALUES (8, 'abc', '555');

SET FOREIGN_KEY_CHECKS = 1;