使用JdbcTemplate操作数据库

最新推荐文章于 2023-01-10 16:08:42 发布

jinangailiuzi

最新推荐文章于 2023-01-10 16:08:42 发布

阅读量630

点赞数

jinangailiuzi

本文链接：https://blog.csdn.net/y20190609/article/details/103468394

版权

环境：MYSQL8、JDK1.8、Spring Boot2.2

一、配置数据源

1）添加依赖

        <!--MYSQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--JDBC数据源-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

2）application.properties配置文件中配置数据库

#服务器端口
server.port=8090

spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_part4
spring.datasource.username=root
spring.datasource.password=root
#MYSQL驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#最大等待连接中的数据量，设置0，没有限制
spring.datasource.tomcat.max-idle=10
#最大连接活动数
spring.datasource.tomcat.max-active=50
#最大等待毫秒数，单位ms，超过时间会出现错误信息
spring.datasource.tomcat.max-wait=10000
#数据库连接池初始化连接数
spring.datasource.tomcat.initial-size=5

二、创建表、实体类

1）创建用户表user

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` int(3) NULL DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2）创建user实体类

public class User {
    private Long id;
    private String userName;
    private GenderEnum gender;
    private String note;

    public Long getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public GenderEnum getGender() {
        return gender;
    }

    public void setGender(GenderEnum gender) {
        this.gender = gender;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }
}


public enum GenderEnum {
    MALE(1, "男"),
    FEMALE(2, "女");

    private int id;
    private String name;

    GenderEnum(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public static GenderEnum getEnumById(int id) {
        for(GenderEnum gender: GenderEnum.values()) {
            if(gender.getId() == id) {
                return gender;
            }
        }
        return null;
    }

    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;
    }
}

三、操作数据库

1）Service
接口

package com.mvp.www.service;

import com.mvp.www.model.User;

import java.util.List;

public interface JdbcTmplUserService {
    public User getUser(Long id);
    public List<User> findUsers(String userName, String note);
    public int insertUser(User user);
    public int updateUser(User user);
    public int deleteUser(Long id);
}

实现类

package com.mvp.www.service.serviceimpl;

import com.mvp.www.model.GenderEnum;
import com.mvp.www.model.User;
import com.mvp.www.service.JdbcTmplUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.util.List;

@Service
public class JdbcTmplUserServiceImpl implements JdbcTmplUserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取影射关系
     *
     * @return
     */
    private RowMapper<User> getUserMapper(){
        RowMapper<User> userRowMapper = (ResultSet rs, int rownum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setUserName(rs.getString("user_name"));
            user.setGender(GenderEnum.getEnumById(rs.getInt("gender")));
            user.setNote(rs.getString("note"));
            return user;
        };
        return userRowMapper;
    }

    @Override
    public User getUser(Long id) {
        String sql = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER WHERE ID = ?";
        Object[] params = new Object[]{id};
        User user = jdbcTemplate.queryForObject(sql, params, getUserMapper());
        return user;
    }

    @Override
    public List<User> findUsers(String userName, String note) {
        String sql = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER "
                + "WHERE USER_NAME LIKE CONCAT('%', ?, '%') AND "
                + "NOTE LIKE CONCAT('%', ?, '%')";
        Object[] params = new Object[] {userName, note};
        List<User> userList = jdbcTemplate.query(sql, params, getUserMapper());
        return userList;
    }

    @Override
    public int insertUser(User user) {
        String sql = "INSERT INTO USER(USER_NAME, GENDER, NOTE) VALUES(?,?,?)";
        Object[] params = new Object[] {user.getUserName(), user.getGender().getId(), user.getNote()};
        int count = jdbcTemplate.update(sql, params);
        return count;
    }

    @Override
    public int updateUser(User user) {
        String sql = "UPDATE USER SET USER_NAME = ?, GENDER = ?, NOTE = ? WHERE ID = ?";
        Object[] params = new Object[] {user.getUserName(), user.getGender().getId(), user.getNote(), user.getId()};
        return jdbcTemplate.update(sql, params);
    }

    @Override
    public int deleteUser(Long id) {
        String sql = "DELETE FROM USER WHERE ID = ?";
        Object[] params = new Object[] {id};
        return jdbcTemplate.update(sql, params);
    }
}

2）controller

package com.mvp.www.controller;

import com.mvp.www.model.User;
import com.mvp.www.service.JdbcTmplUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class UserController {
    @Autowired
    private JdbcTmplUserService jdbcTmplUserServiceImpl;

    @RequestMapping(value = "/getUser/{id}", method = RequestMethod.GET)
    public User getUser(@PathVariable("id") Long id) {
        return jdbcTmplUserServiceImpl.getUser(id);
    }

    @RequestMapping(value = "/findUsers", method = RequestMethod.GET)
    public List<User> findUsers(String userName, String note) {
        return jdbcTmplUserServiceImpl.findUsers(userName, note);
    }

    @RequestMapping(value = "/insertUser", method = RequestMethod.POST)
    public int insertUser(@RequestBody User user) {
        return jdbcTmplUserServiceImpl.insertUser(user);
    }

    @RequestMapping(value = "/updateUser", method = RequestMethod.POST)
    public int updateUser(@RequestBody User user) {
        return jdbcTmplUserServiceImpl.updateUser(user);
    }

    @RequestMapping(value = "/deleteUser", method = RequestMethod.GET)
    public int deleteUser(Long id) {
        return jdbcTmplUserServiceImpl.deleteUser(id);
    }
}

在这里插入图片描述

四、其他

1）time zone报错

com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_191]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_191]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_191]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_191]

修改application.properties中url连接，改为spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_part4?serverTimezone=UTC
2）修改数据库连接池类型
默认com.zaxxer.hikari.HikariDataSource

修改为dbcp2
添加依赖

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
        </dependency>

application.properties中添加spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

3）StatementCallback或者ConnectionCallback

    public User getUser2(Long id) {
        User result = jdbcTemplate.execute((Statement stmt) -> {
            String sql1 = "SELECT COUNT(*) TOTAL FROM USER WHERE ID = " + id;
            ResultSet result1 = stmt.executeQuery(sql1);
            while(result1.next()) {
                int total = result1.getInt("TOTAL");
                System.out.println(total);
            }
            String sql2 = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER WHERE ID = " + id;
            ResultSet result2 = stmt.executeQuery(sql2);
            User user = null;
            while (result2.next()) {
                int rowNum = result2.getRow();
                user = getUserMapper().mapRow(result2, rowNum);
            }
            return user;
        });
        return result;
    }

    public User getUser3(Long id){
        return jdbcTemplate.execute((Connection conn) -> {
            String sql1 = "SELECT COUNT(*) TOTAL FROM USER WHERE ID = ?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql1);
            preparedStatement.setLong(1, id);
            ResultSet rs1 = preparedStatement.executeQuery();
            while(rs1.next()){
                System.out.println(rs1.getInt("TOTAL"));
            }
            String sql2 = "SELECT ID, USER_NAME, GENDER, NOTE FROM USER WHERE ID = ?";
            PreparedStatement preparedStatement2 = conn.prepareStatement(sql2);
            preparedStatement2.setLong(1, id);
            ResultSet result2 = preparedStatement2.executeQuery();
            User user = null;
            while (result2.next()) {
                user = getUserMapper().mapRow(result2, result2.getRow());
            }
            return user;
        });
    }