转载

java小白的成长记录之JdbcTemplate实践

距离上一次成长记录已经过去了3周了，罪过罪过。

话不多说，作为一个标准的增删改查工程师，那么mysql的使用无疑是重中之重了。

JdbcTemplate是Spring提供的访问数据库的方式之一，是Spring中最基本、最底层的访问数据库的实现方式。

今天的目标就是使用JdbcTemplate实现增删改查功能咯。

二、环境配置

1.首先是要确保安装好mysql了。

2.建一个test的数据库

3.新增个简单的user表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
复制代码

三、导入核心包

在pom.xml将加入下面内容

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

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

复制代码

配置properties文件,将数据库信息填写好：

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
复制代码

四、建立User实体类

实体类对应数据库中的表结构

package com.example.demo.controller;

public class User {

    private int id;
    private String name;
    private int age;

    public User() {
    }

    public User(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '/'' +
                ", age=" + age +
                '}';
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

复制代码

五、增删改查接口实现

新建MysqlController.java

package com.example.demo.controller;

import com.alibaba.fastjson.JSONObject;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


@RestController
public class MysqlController {

    @Resource
    private JdbcTemplate jdbcTemplate;


    @RequestMapping("/getAllData")
    public Object getAllData() {
        String sql = "select * from user";
        List<User> Users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>() {
        //这边使用了匿名类
        //原生JDBC会把从数据库查询出来的记录全都被保存在ResultSet结果集中，使用RowMapper来处理返回数据到User的实体类上
        //目前有两个主要的RowMapper实现SingleColumnRowMapper和BeanPropertyRowMapper，分别对应单列和多列数据。
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User User = new User();
                User.setId(resultSet.getInt("id"));
                User.setName(resultSet.getString("name"));
                User.setAge(resultSet.getInt("age"));
                return User;
            }
        });
        for (User s : Users) {
            System.out.println(s);
        }
        return Users;
    }

    //更新、插入、删除的操作都是使用jdbcTemplate的update
    @RequestMapping("/updateNameById")
    public String updateNameById(@RequestParam(value = "name") String name, Integer id) {
        String sql = "update  user set name=? where id=?";
        int flag = jdbcTemplate.update(sql, new Object[]{name, id});
        String returnStr;

        if (flag > 0) {
            returnStr = "更新成功";
        } else {
            returnStr = "更新失败";
        }
        System.out.println(flag);
        return returnStr;
    }

    @RequestMapping("/insertData")
    public String insertData(@RequestParam(value = "name") String name, Integer age) {
        String sql = "insert into user(name,age) values (?,?)";
        int flag = jdbcTemplate.update(sql, new Object[]{name, age});
        String returnStr;

        if (flag > 0) {
            returnStr = "插入成功";
        } else {
            returnStr = "插入失败";
        }
        System.out.println(flag);
        return returnStr;
    }
    
    @RequestMapping("/deleteData")
    public String deleteData(@RequestParam(value = "id") Integer id) {
        String sql = "delete from user where id=?";
        int flag = jdbcTemplate.update(sql, new Object[]{id});
        String returnStr;

        if (flag > 0) {
            returnStr = "删除成功";
        } else {
            returnStr = "删除失败";
        }
        System.out.println(flag);
        return returnStr;
    }

}

复制代码

六、实现成果

初始情况-->http://localhost:8080/getAllData
[{"id":1,"name":"caohaoyu","age":3},{"id":3,"name":"chy","age":20}]
______________________________________________________________________________
新增一条数据,http://localhost:8080/insertData?name=chy2&age=21
[{"id":1,"name":"caohaoyu","age":3},{"id":3,"name":"chy","age":20},{"id":5,"name":"chy2","age":21}]
______________________________________________________________________________
修改id为5的name，http://localhost:8080/updateNameById?id=5&name=caohaoyu2
[{"id":1,"name":"caohaoyu","age":3},{"id":3,"name":"chy","age":20},{"id":5,"name":"caohaoyu2","age":21}]
______________________________________________________________________________
删除id为1的记录，http://localhost:8080/deleteData?id=1
[{"id":3,"name":"chy","age":20},{"id":5,"name":"caohaoyu2","age":21}]

复制代码

基本的增删改查完成！