SpringBoot-JDBC示例

JDBC连接数据库简单示例

准备数据

DROP TABLE IF EXISTS user;
CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	sex VARCHAR(4) NULL DEFAULT NULL COMMENT '性别',
	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
	Remark VARCHAR(50) NULL DEFAULT NULL COMMENT '说明',
	PRIMARY KEY (id)
);

新建一个普通spring-boot项目

https://start.spring.io 或直接使用IDEA创建（如何快速创建SpringBoot项目？戳这里）

application.properties配置 (test_jf_db:数据库名称;serverTimezone=UTC:设置时区)

<!-- 连接字符串 --> 
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test_jf_db?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5

启动类

package org.jianfeng.jfspringboot_jdbc;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class JfspringbootJdbcApplication {
	public static void main(String[] args) {
		SpringApplication.run(JfspringbootJdbcApplication.class, args);
	}
}

实体类(Entity) IDEA:alt+insert快捷生成Getter()、Setter()、toString()方法

package org.jianfeng.jfspringboot_jdbc.Entity;

public class User {
    private Integer Id;
    private String Name;
    private String Sex;
    private String Age;
    private String Email;
    private String Remark;

    public Integer getId() {
        return Id;
    }

    public void setId(Integer id) {
        Id = id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public String getSex() {
        return Sex;
    }

    public void setSex(String sex) {
        Sex = sex;
    }

    public String getAge() {
        return Age;
    }

    public void setAge(String age) {
        Age = age;
    }

    public String getEmail() {
        return Email;
    }

    public void setEmail(String email) {
        this.Email = email;
    }
    
    public String getRemark() {
        return Remark;
    }

    public void setRemark(String remark) {
        Remark = remark;
    }

    @Override
    public String toString() {
        return "User{" +
                "Id=" + Id +
                ", Name='" + Name + '\'' +
                ", Sex='" + Sex + '\'' +
                ", Age='" + Age + '\'' +
                ", Email='" + Email + '\'' +
                ", Remark='" + Remark + '\'' +
                '}';
    }
}

控制器(Controller)

package org.jianfeng.jfspringboot_jdbc.controller;

import org.jianfeng.jfspringboot_jdbc.Entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@RestController
@RequestMapping("/jdbc")
public class jdbcConteroller {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @RequestMapping("/userlist")
    public List<User> getUserList(ModelMap map) {
        String sql = "SELECT * FROM user";
        List<User> userList = jdbcTemplate.query(sql, new RowMapper<User>() {
            User user = null;
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                user = new User();
                user.setId(rs.getInt("Id"));
                user.setName(rs.getString("Name"));
                user.setSex(rs.getString("Sex"));
                user.setAge(rs.getString("Age"));
                user.setEmail(rs.getString("Email"));
                user.setRemark(rs.getString("Remark"));
                return user;
            }
        });
        /*for (User user : userList) {
            System.out.println(user.getName());
        }*/
        map.addAttribute("users", userList);

        return userList;
    }
}

启动项目访问：http://localhost:8080/jdbc/userlist 即可以显示查寻结果