SpringBoot使用JDBC操作Mysql数据库

SpringBootJDBC操作Mysql数据库

pom文件依赖

    <properties>
        <java.version>1.8</java.version>
    </properties>

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

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

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

properties文件配置

spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

User实体类

package com.sx.dbdemo.pojo;

public class User {
    private int id;
    private String user_name;
    private String user_password;

    public User(int id, String user_name, String user_password){
        this.id = id;
        this.user_name = user_name;
        this.user_password = user_password;
    }

    public User(){

    };

    public int getId() {
        return id;
    }

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

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_password() {
        return user_password;
    }

    public void setUser_password(String user_password) {
        this.user_password = user_password;
    }
}

创建UserCOntroller，并注入jdbcTemplate

    @Autowired
    private JdbcTemplate jdbcTemplate;

execute用法

  execute可以直接执行sql语句，很直接

    @GetMapping("createTable")
    public String createTable(){
        String sql =
                "CREATE TABLE `user`(\n"+
                "`id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                "`user_name` varchar(255) NOT NULL, \n" +
                "`user_password` varchar(255) DEFAULT NULL,\n" +
                "PRIMARY KEY(`id`)\n" +
                ")ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;";
        jdbcTemplate.execute(sql);
        return "创建User表成功";
    }

update用法

  update适合用来进行增删查改更新操作，默认返回int值

    @GetMapping("saveUserSql")
    public String saveUserSql(){
        String sql = "INSERT INTO user (user_name,user_password) VALUES ('xiaocaino', '1234')";
        int row = jdbcTemplate.update(sql);
        return "执行成功， 影响" + row + "行";
    }

    @GetMapping("saveUser")
    public String saveUser(String userName, String password){
        String sql = "INSERT INTO USER(USER_NAME, USER_PASSWORD) VALUES (?, ?)";
        int rows = jdbcTemplate.update(sql, userName, password);
        return "执行成功，影响" + rows + "行";
    }

    @GetMapping("updateUser")
    public String updateUser(int id, String password){
        String sql = "update user set user_password=? where id = ?";
        int rows = jdbcTemplate.update(sql, password, id);
        return "执行成功，影响" + rows + "行";
    }

图片

query用法

  query，很容易看出，这个方法适合用来查询，可用list，map，实体类接收结果

    @GetMapping("getUserByUserName")
    public List getUserByUserName(String userName){
        String sql = "select * from user where user_name = ?";
        List<User> list = jdbcTemplate.query(sql, new Object[]{userName}, new BeanPropertyRowMapper<>(User.class));
        return list;
    }

图片

总结

  不得不说，要手写sql的jdbc比起mybatis，jpa确实要麻烦很多，在实际开发中也用的很少，还有就是mysql不久前的更新中，在配置中必须要写上时区serverTimezone=Asia/Shanghai,否则就会报错