Spring 学习

Spring 学习(九)JdbcTemplate 基本使用

/ 13 阅读 / 约 5449 字

JdbcTemplate 是 Spring 框架使用 AOP 思想封装的 JDBC 一系列操作的工具。

1. 准备

添加依赖:

<dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
    </dependencies>

准备数据库:

CREATE DATABASE user_db;

USE user_db;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
    `address` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

准备实体类:

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

    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 getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

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

2. 开始使用

创建 XML 配置文件:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!--数据库的连接池-->
    <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="url" value="jdbc:mysql:///user_db?useUnicode=true&amp;characterEncoding=utf8"/>
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    </bean>

    <!--JdbcTemplate 对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

进行测试:

public class UserTest {
    
    private JdbcTemplate jdbcTemplate;

    @Before
    public void before() {
        ApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
        jdbcTemplate = context.getBean(JdbcTemplate.class);
    }

    // 插入一条数据
    @Test
    public void insert() {
        jdbcTemplate.update("insert into user (username, address) values (?,?);", "CareyQ", "careyq.cool");
    }

    // 更新一条数据
    @Test
    public void update() {
        jdbcTemplate.update("update user set username=? where id=?", "Lenovo", 1);
    }

    // 删除一条数据
    @Test
    public void delete() {
        jdbcTemplate.update("delete from user where id=?", 1);
    }

    // 查询表中的数据总数
    @Test
    public void selectCount() {
        Integer count = jdbcTemplate.queryForObject("select count(*) from user", Integer.class);
        System.out.println(count);
    }

    // 查询一条数据,返回对象
    @Test
    public void selectUser() {
        User user = jdbcTemplate.queryForObject("select * from user where id = ?", new BeanPropertyRowMapper<>(User.class), 1);
        System.out.println(user);
    }

    // 查询多条数据,返回对象集合
    @Test
    public void selectUsers() {
        List<User> userList = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(userList);
    }

    // 批量添加数据
    @Test
    public void batchInsert() {
        ArrayList<Object[]> objects = new ArrayList<>();
        Object[] o1 = {3, "李四", "中国"};
        Object[] o2 = {4, "王五", "杭州"};
        Object[] o3 = {5, "大雄", "日本"};
        objects.add(o1);
        objects.add(o2);
        objects.add(o3);
        jdbcTemplate.batchUpdate("insert into user values (?,?,?)", objects);
    }

    // 批量修改数据
    @Test
    public void batchUpdate() {
        ArrayList<Object[]> objects = new ArrayList<>();
        Object[] o1 = {"李四11", "中国", 3};
        Object[] o2 = {"王五22", "杭州", 4};
        Object[] o3 = {"大雄33", "日本", 5};
        objects.add(o1);
        objects.add(o2);
        objects.add(o3);
        jdbcTemplate.batchUpdate("update user set username=?, address=? where id=?", objects);
    }

    // 批量删除数据
    @Test
    public void batchDelete() {
        ArrayList<Object[]> objects = new ArrayList<>();
        Object[] o1 = {3};
        Object[] o2 = {4};
        Object[] o3 = {5};
        objects.add(o1);
        objects.add(o2);
        objects.add(o3);
        jdbcTemplate.batchUpdate("delete from user where id=?", objects);
    }
}

参考文献:

最后更新: 2021-06-21 11:43:44
版权声明: 本文著作权归 [ CareyQ ] 享有,均采用 CC BY-NC-SA 4.0 许可协议,转载请注明出处!
评论
  • 🇨🇳
  • 👆
  • 👇
  • 👈
  • 👉
  • 👊
  • 👋
  • 👌
  • 👍
  • 👎
  • 👏
  • 👨‍👦
  • 👨‍👧
  • 👨‍👨‍👦
  • 👨‍👨‍👧
  • 😀
  • 😁
  • 😂
  • 😃
  • 😄
  • 😅
  • 😆
  • 😇
  • 😈
  • 😉
  • 😊
  • 😋
  • 😌
  • 😍
  • 😎
  • 😏
  • 😐
  • 😑
  • 😒
  • 😓
  • 😔
  • 😕
  • 😖
  • 😗
  • 😘
  • 😙
  • 😚
  • 😛
  • 😜
  • 😝
  • 😞
  • 😟
  • 😠
  • 😡
  • 😢
  • 😣
  • 😤
  • 😦
  • 😧
  • 😨
  • 😩
  • 😪
  • 😫
  • 😬
  • 😭
  • 😮
  • 😯
  • 😰
  • 😱
  • 😲
  • 😳
  • 😴
  • 😵
  • 😶
  • 😷
  • 😸
  • 😹
  • 😺
  • 😻
  • 😼
  • 😽
  • 😾
  • 😿
  • 🙀
  • 🙁
  • 🙂
  • 🙃
  • 🙄
还没有评论 /(ㄒoㄒ)/~~