SpringBoot学习之Jdbctemplate

新建User类

package org.hx.springboot_jdbctemplate_demo25.model;

public class User {
    private Integer id;
    private String name;
    private String passwd;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + passwd + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPasswd() {
        return passwd;
    }

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

新建UserService类

package org.hx.springboot_jdbctemplate_demo25.service;

import org.hx.springboot_jdbctemplate_demo25.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.List;

@Service
public class UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    public int addUser(User user){
        int result = jdbcTemplate.update("insert into t1 (name,passwd) values(?,?)",user.getName(),user.getPasswd());
        return result;
    }
    public int addUser2(User user){
        //GeneratedKeyHolder提供了一个可以返回新增记录对应主键值的方法
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        //  PreparedStatementCreator：通过回调获取JdbcTemplate提供的Connection，
        //  由用户使用该Conncetion创建相关的PreparedStatement；
        int result = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement=connection.prepareStatement( "insert into t1 (name,passwd) values(?,?)", Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1,user.getName());
                preparedStatement.setString(2,user.getPasswd());
                return  preparedStatement;
            }
        },keyHolder);
        user.setId(keyHolder.getKey().intValue());
        return result;
    }
    public int deleteById(Integer id)
    {
        return jdbcTemplate.update("delete from t1 where id=? ",id);
    }
    public int updateById(Integer id,String name){
        return jdbcTemplate.update("update t1 set name =? where id=?",name,id);
    }
    //如果数据库中的属性名和实体类中的属性名不一样可以用一下方法查询
    public List<User> getAllUsers(){
        //RowMapper可以将数据中的每一行数据封装成用户定义的类
        List<User> list =jdbcTemplate.query("select * from t1", new RowMapper<User>() {
            @Override
            //从数据库查询出来的记录全都被保存在ResultSet结果集中
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                String name = resultSet.getString("name");
                String passwd = resultSet.getString("passwd");
                Integer id = resultSet.getInt("id");
                User user = new User();
                user.setId(id);
                user.setName(name);
                user.setPasswd(passwd);
                return user;
            }
        });
        return list;
    }
    //如果数据库中的属性名和实体类中的属性名一样可以用一下方法查询
    public List<User> getAllUsers2(){
        List<User> list =jdbcTemplate.query("select * from t1", new BeanPropertyRowMapper<>(User.class));
        return list;
    }

}

在application.properties中配置数据库

spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

在测试类中进行测试

package org.hx.springboot_jdbctemplate_demo25;

import org.hx.springboot_jdbctemplate_demo25.model.User;
import org.hx.springboot_jdbctemplate_demo25.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class SpringbootJdbctemplateDemo25ApplicationTests {

    @Autowired
    UserService userService;
    @Test
    void contextLoads() {
        User user = new User();
        user.setName("hx");
        user.setPasswd("123456");
        int i = userService.addUser(user);
        System.out.println(i);
    }
    @Test
    void test1(){
        User user = new User();
        user.setName("xx");
        user.setPasswd("123456");
        int i = userService.addUser2(user);
        System.out.println(i);
        System.out.println(user.getId());
    }
    @Test
    void test2(){
        userService.deleteById(111);
        userService.updateById(113,"肖雪");
    }
    @Test
    void test3(){
        List<User> allUsers = userService.getAllUsers();
        for (User u:allUsers) {
            System.out.println(u);
        }
    }
    @Test
    void test4(){
        List<User> allUsers = userService.getAllUsers();
        for (User u:allUsers) {
            System.out.println(u);
        }
    }
}

新建的表如下