【SpringBoot框架篇】28.使用JdbcTemplate操作数据库

皓亮君

已于 2024-01-10 20:06:09 修改

阅读量1.6k

点赞数 1

分类专栏： SpringBoot 文章标签： spring boot 数据库 java

于 2022-08-29 20:49:41 首次发布

本文链接：https://blog.csdn.net/ming19951224/article/details/126592129

版权

SpringBoot 专栏收录该内容

37 篇文章 69 订阅

订阅专栏

文章目录

1.引入依赖
2.配置数据集连接信息
3.操作数据库
4.项目配套代码

1.引入依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

2.配置数据集连接信息

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/jdbctest?createDatabaseIfNotExist=true&useSSL=false&serverTimezone=GMT%2b8&characterEncoding=utf8&connectTimeout=1000&socketTimeout=15000&autoReconnect=true
    #MySQL5.7以上的版本使用com.mysql.cj.jdbc.Driver
    #MySQL5.7以下的版本使用com.mysql.jdbc.Driver
    driver-class-name: com.mysql.cj.jdbc.Driver

3.操作数据库

自动装配Bean对象

@Autowired
JdbcTemplate jdbcTemplate;

3.1.执行DDL语句

jdbcTemplate通过execute方法执行DDL语句

        String sql = "create table sys_user(id int AUTO_INCREMENT,username varchar(255),password varchar(255),age int,PRIMARY KEY (id))ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_0900_ai_ci;";
        jdbcTemplate.execute(sql);
        System.out.println("创建表成功");

表对应的实体类为

public class SysUser implements Serializable {

    public SysUser(){}

    public SysUser(Integer id, String username, String password, Integer age){
        this.id=id;
        this.username=username;
        this.password=password;
        this.age=age;
    }

    private Integer id;

    private String username;

    private String password;

    private Integer age;
		
	//省略 get,set方法
    @Override
    public String toString() {
        return "SysUsers{" +
                "id=" + id  +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", age='" + age + '\'' +
                '}';
    }
}

3.2.添加数据

执行update(sql)方法直接插入拼接好的sql数据
执行update(sql,Object… args)方法通过占位符的方式插入数据
通过KeyHolder+PreparedStatementCreator()配置返回自增的主键信息

        //1.插入数据 占位符
        String sql = "insert into sys_user (username,password,age) values(?,?,?);";
        int ret = jdbcTemplate.update(sql,"王五","123456",18);
        if (ret == 0) {
            System.out.println("添加失败!");
        }
        //插入测试数据方便后面分页测试
        sql = "insert into sys_user (username,password,age) values('张三','李四',18);";
        for (int i = 0; i < 20; i++) {
            ret = jdbcTemplate.update(sql);
            if (ret == 0) {
                System.out.println("添加失败!");
            }
        }

        //2.插入数据并返回主键
        KeyHolder keyHolder1 = new GeneratedKeyHolder();
        String finalSql = sql;
        ret = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                // 设置返回的主键字段名
                PreparedStatement ps = con.prepareStatement(finalSql, Statement.RETURN_GENERATED_KEYS);
                return ps;
            }
        }, keyHolder1);
        if (ret == 1) {
            // 获取到插入数据生成的自增ID
            int id = keyHolder1.getKey().intValue();
            System.out.println(id);
        }

3.3.查询数据

执行queryForList()返回map映射好的数据
通过实现RowMapper接口手动注入数据到Bean中
使用BeanPropertyRowMapper类克隆数据到Bean中

       String sql = "select * from sys_user";
        //1.使用map封装查询出来的字段名称和字段值
        System.out.println("返回map数据");
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map : mapList) {
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                System.out.println("key=" + entry.getKey() + ",value=" + entry.getValue());
            }
        }
        
        //2.通过RowMapper获取ResultSet手动赋值给bean
        System.out.println("手动注入");
        List<SysUser> sysUserList = jdbcTemplate.query(sql, new RowMapper<SysUser>() {
            @Override
            public SysUser mapRow(ResultSet resultSet, int i) throws SQLException {
                SysUser sysUser = new SysUser(resultSet.getInt("id"), resultSet.getString("username"), resultSet.getString("password"), resultSet.getInt("age"));
                return sysUser;
            }
        });
        for (SysUser sysUser : sysUserList) {
            System.out.println(sysUser.toString());
        }

        //3.通过BeanPropertyRowMapper 克隆字段
        System.out.println("BeanPropertyRowMapper 克隆");
        sysUserList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SysUser.class));
        for (SysUser sysUser : sysUserList) {
            System.out.println(sysUser.toString());
        }

        //4.分页查询
        Integer totalElements = jdbcTemplate.queryForObject("SELECT count(id) FROM sys_user", Integer.class);
        //当前页
        Integer currentPage = 1;
        //页码大小
        Integer pageSize = 5;
        sysUserList = jdbcTemplate.query("SELECT * FROM sys_user limit ?,?", new BeanPropertyRowMapper<>(SysUser.class), (currentPage - 1) * pageSize, pageSize);
        for (SysUser sysUser : sysUserList) {
            System.out.println(sysUser.toString());
        }
        System.out.println("总记录数=" + totalElements + ",总页数=" + (totalElements % pageSize == 0 ? totalElements / pageSize : totalElements / pageSize + 1));

3.4.修改数据

也可像3.2一样使用占位符方式操作

       String sql = "update  sys_user set username='李四' where id=1";
        int ret = jdbcTemplate.update(sql);
        if (ret == 1) {
            System.out.println("修改成功");
        }

3.5.删除数据

      String sql = "delete from sys_user where id=?";
        int ret = jdbcTemplate.update(sql, 1);
        if (ret == 1) {
            System.out.println("删除成功");
        }