SpringBoot JdbcTemplate

JdbcTemplate基本使用

一、开发工具及环境

• 电脑操作系统：Win10

• Java版本：JDK1.8

• MySQL数据库版本：mysql-8.0.26-winx64

• 编辑器：IntelliJ IDEA 2021.2 企业版

• SpringBoot版本：2.6.3

• 工作目录

  

二、Spring Boot基本配置

1、Spring initializr 设置

2、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>jdbcTemplate</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>jdbcTemplate</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</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>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

3、JdbcTemplateApplication类

package com.example.jdbcTemplate;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class JdbcTemplateApplication {

	public static void main(String[] args) {
		SpringApplication.run(JdbcTemplateApplication.class, args);
	}

}

4、JdbcTemplateApplicationTests类

package com.example.jdbcTemplate;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class JdbcTemplateApplicationTests {

	@Test
	void contextLoads() {
	}

}

5、MyMvcConfig类

package com.example.jdbcTemplate.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.View;
import org.springframework.web.servlet.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

import java.util.Locale;

//全面扩展SpringMVC  dispatcherServlet
@Configuration
@EnableWebMvc
public class MyMvcConfig implements WebMvcConfigurer {

    //ViewResolver 实现了视图解析器接口的类，我们就可以把它看做视图解析器
    @Bean
    public ViewResolver myViewResolver(){
        return new MyViewResolver();
    }

    public static class MyViewResolver implements ViewResolver{
        @Override
        public View resolveViewName(String viewName, Locale locale) throws Exception {
            return null;
        }
    }
}

三、数据库搭建

1、数据准备

创建User表，并插入三行数据

CREATE TABLE `jdbctemplate`.`User`( `id` INT(4) NOT NULL, `username` VARCHAR(30) NOT NULL, `password` VARCHAR(30) NOT NULL, `email` VARCHAR(30), `gender` INT(1), `birth` DATE, PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('1', 'zhangsan', '123456', 'test01@qq.com', '1', '2000-06-14'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('2', 'xiaoming', '654321', 'test02@qq.com', '1', '2001-07-03'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('3', 'zhangcuihua', '123456789', 'test03@qq.com', '0', '1999-08-12'); 

2、application.properties 配置数据库连接

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/jdbcTemplate?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

3、测试是否能连接上数据库

package com.example.jdbcTemplate;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootTest
class JdbcTemplateApplicationTests {

	//DI注入数据源
	@Autowired
	DataSource dataSource;

	@Test
	void contextLoads() throws SQLException {
		//看一下默认数据源
		System.out.println(dataSource.getClass());
		//获得连接
		Connection connection = dataSource.getConnection();
		System.out.println(connection);
		//关闭连接
		connection.close();
	}

}

运行测试是否运行成功，如果成功，则说明成功连接数据库。

四、整合JdbcTemplate

先创建pojo、dao、controller三个文件夹

1、创建实体类User.java

在pojo文件夹中新建User类

package com.example.jdbcTemplate.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private Integer id;
    private String username;
    private String password;
    private String email;
    private Integer gender;//0:女生;1:男生
    private Date birth;

}

2、创建UserDao.java

在Dao文件夹中创建UserDao.java并整合JdbcTemplate

package com.example.jdbcTemplate.dao;

import com.example.jdbcTemplate.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class UserDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public List<Map<String,Object>> getUserList(){
        String sql = "select * from `user`";
        List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql);
        return userList;
    }

    public User getUserById(int id){
        String sql = "select * from `user` where id="+id;
        User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User.class));
        return user;
    }

    public String addUser(User user){
        String sql = "insert into `user`(id,username,password,email,gender,birth) values(?,?,?,?,?,?)";

        Object[] objects = new Object[6];
        objects[0] = user.getId();
        objects[1] = user.getUsername();
        objects[2] = user.getPassword();
        objects[3] = user.getEmail();
        objects[4] = user.getGender();
        objects[5] = user.getBirth();

        jdbcTemplate.update(sql,objects);
        return "add ok";
    }

    public String updateUser(User user){
        String sql = "update `user` set username=?,password=?,email=?,gender=?,birth=? where id=?";

        Object[] objects = new Object[6];
        objects[0] = user.getUsername();
        objects[1] = user.getPassword();
        objects[2] = user.getEmail();
        objects[3] = user.getGender();
        objects[4] = user.getBirth();
        objects[5] = user.getId();

        jdbcTemplate.update(sql,objects);
        return "update ok";
    }

    public String deleteUserById(int id){
        String sql = "delete from `user` where id="+id;
        jdbcTemplate.update(sql);
        return "delete ok";
    }
}

3、创建JdbcController

package com.example.jdbcTemplate.controller;

import com.example.jdbcTemplate.dao.UserDao;
import com.example.jdbcTemplate.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;
import java.util.List;
import java.util.Map;

@RestController
public class JdbcController {

    @Autowired
    UserDao userDao;

    @GetMapping({"/","/userList"})
    public String getUserList(){
        List<Map<String, Object>> userList = userDao.getUserList();
        return userList.toString();
    }

    @GetMapping("/user/{id}")
    public String getUserById(@PathVariable("id") Integer id){
        User user = userDao.getUserById(id);
        return user.toString();
    }

    @GetMapping("/addUser")
    public String addUser(){
        User user = new User(4, "chenyonwu", "xiangyue2035", "test04@qq.com", 1, new Date());
        String s = userDao.addUser(user);
        return s;
    }

    @GetMapping("/updateUser")
    public String updateUser(){
        User user = new User(4, "AAA", "123456", "test05@qq.com", 0, new Date());
        String s = userDao.updateUser(user);
        return s;
    }

    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") Integer id){
        String s = userDao.deleteUserById(id);
        return s;
    }
}