Java 基础

Spring Boot 操作数据库

2022-05-04  本文已影响0人  yjtuuige

一、Spring Data 简介

二、整合 JDBC

2.1 环境搭建

/* MySQL - 8.0.28 */
CREATE DATABASE `springboot`;
USE `springboot`;

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '部门id',
    `department_name` varchar(20) NOT NULL COMMENT '部门名字',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4;

insert into `department`(`id`,`department_name`) values 
(101,'技术部'),
(102,'销售部'),
(103,'售后部'),
(104,'后勤部'),
(105,'运营部');

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '员工id',
    `last_name` varchar(100) NOT NULL COMMENT '名字',
    `email` varchar(100) NOT NULL COMMENT '邮箱',
    `gender` int NOT NULL COMMENT '性别1 男, 0 女',
    `department` int NOT NULL COMMENT '部门id',
    `birth` datetime NOT NULL COMMENT '生日',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4;

insert into
`employee`(`id`,`last_name`,`email`,`gender`,`department`,`birth`) values
(1001,'张三','aa@qq.com',0,101,'2000-01-06 15:04:33'),
(1002,'李四','bb@qq.com',1,102,'2002-03-06 15:04:36'),
(1003,'王五','cc@qq.com',0,103,'2000-05-08 15:04:37'),
(1004,'赵六','dd@qq.com',0,104,'2001-06-06 15:04:39'),
(1005,'孙七','ee@qq.com',1,105,'2000-03-03 15:04:45');

2.2 测试数据源

<!--jdbc-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--web-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

# MySQL - 8.0.28
spring:
  datasource:
    username: 数据库用户名
    password: 数据库密码
    # serverTimezone=UTC解决时区报错
    url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=true
    driver-class-name: com.mysql.cj.jdbc.Driver

@SpringBootTest
class Springboot05DataApplicationTests {
    // 自动装配数据源
    @Autowired
    DataSource dataSource;

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

@Configuration(proxyBeanMethods = false)
@Conditional(PooledDataSourceCondition.class)
@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
// 导入了默认的数据源
@Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
        DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
        DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
protected static class PooledDataSourceConfiguration {

}

2.3 JdbcTemplate

JdbcTemplate 主要提供以下几类方法：

JdbcTemplate 测试

// 直接返回字符串
@RestController
public class JdbcController {
    /*
        Spring Boot 默认提供了数据源和 org.springframework.jdbc.core.JdbcTemplate
        JdbcTemplate 中会自己注入数据源,用于简化JDBC操作
        还能避免一些常见的错误,使用时会自动关闭数据库连接
     */
    @Autowired
    JdbcTemplate jdbcTemplate;

    // 查询employee表中所有数据
    // List中的1个Map对应数据库的1行数据
    // Map中的key对应数据库的字段名,value对应数据库的字段值
    @GetMapping("/userList")
    public List<Map<String, Object>> userList() {
        String sql = "select * from employee";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    // 新增一个用户
    @GetMapping("/add")
    public String addUser() {
        // 日期格式转换
        String date = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
        // 插入语句,注意时间问题,及单引号包裹
        String sql = "insert into `employee` (`last_name`, `email`, " +
                "`gender`, `department`,`birth`) value ('测试','tt@tt.com'," +
                "1,101,'" + date + "')";
        jdbcTemplate.update(sql);
        return "addOk";
    }

    // 修改用户信息
    @GetMapping("/update/{id}")
    public String updateUser(@PathVariable("id") int id) {
        String sql = "update `employee` set `last_name`=?,`email`=? where `id`=" + id;
        Object[] objects = new Object[2];
        objects[0] = "修改测试";
        objects[1] = "yy@aa.com";
        jdbcTemplate.update(sql, objects);
        return "updateOk";
    }

    // 删除用户
    @GetMapping("/delete/{id}")
    public String delUser(@PathVariable("id") int id) {
        String sql = "delete from employee where id=?";
        jdbcTemplate.update(sql, id);
        return "deleteOk";
    }
    
    // 按id查询用户
    @GetMapping("/user/{id}")
    public Map<String, Object> userById(@PathVariable("id") int id) {
        String sql = "select * from employee where id=" + id;
        Map<String, Object> user = jdbcTemplate.queryForMap(sql);
        return user;
    }
}

三、整合 Druid

3.1 Druid 简介

3.2 基本配置参数

配置 缺省值 说明
name 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是："DataSource-" + System.identityHashCode(this). 另外配置此属性至少在1.0.5版本中是不起作用的，强行设置name会出错。详情-点此处。
url 连接数据库的url，不同数据库不一样。例如： mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username 连接数据库的用户名
password 连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里
driverClassName 根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName
initialSize 0 初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive 8 最大连接池数量
maxIdle 8 已经不再使用，配置了也没效果
minIdle 最小连接池数量
maxWait 获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements false 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxPoolPreparedStatementPerConnectionSize -1 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
validationQueryTimeout 单位：秒，检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法
testOnBorrow true 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn false 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testWhileIdle false 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
keepAlive false （1.0.28） 连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作。
timeBetweenEvictionRunsMillis 1分钟（1.0.14） 有两个含义： 1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。 2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun 30分钟（1.0.14） 不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis 连接保持空闲而不被驱逐的最小时间
connectionInitSqls 物理连接初始化的时候执行的sql
exceptionSorter 根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall
proxyFilters 类型是List<com.alibaba.druid.filter.Filter>，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

3.3 配置数据源

<!--druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.9</version>
</dependency>

<!--也可以使用下面这个-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.9</version>
</dependency>

# MySQL - 8.0.28
spring:
  datasource:
    username: 数据库用户名
    password: 数据库密码
    # serverTimezone=UTC解决时区报错
    url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    # 自定义数据源 druid
    type: com.alibaba.druid.pool.DruidDataSource

# MySQL - 8.0.28
spring:
  datasource:
    username: 数据库用户名
    password: 数据库密码
    # serverTimezone=UTC解决时区报错
    url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    # 自定义数据源 druid
    type: com.alibaba.druid.pool.DruidDataSource

    # Spring Boot 默认是不注入这些属性值的，需要自定义配置类，进行绑定
    # druid 数据源专有配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true

    # 配置监控统计拦截的 filters，stat:监控统计、log4j：日志记录、wall：防御sql注入
    # 如果运行时报错：java.lang.ClassNotFoundException: org.apache.log4j.Priority
    # 则导入 log4j 依赖即可,如使用log4j2,对应导入相应依赖
    filters: stat,wall,log4j2
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

<!--Log4j2 日志-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-log4j2</artifactId>
    <version>2.6.7</version>
</dependency>

<!--或Log4j 日志，对应配置文件需加入Log4j-->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

自定义配置类

// 配置类
@Configuration
public class DruidConfig {
    /*
        将自定义的Druid数据源添加到容器中，不再由Spring Boot自动创建
        绑定全局配置文件中的druid数据源属性，到com.alibaba.druid.pool.DruidDataSource从而让它们生效
        @ConfigurationProperties(prefix = "spring.datasource")：作用就是将全局配置文件中，
        前缀为spring.datasource的属性值注入到com.alibaba.druid.pool.DruidDataSource的同名参数中
    */
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

@SpringBootTest
class Springboot05DataApplicationTests {
    // 自动装配数据源
    @Autowired
    DataSource dataSource;

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

        DruidDataSource druidDataSource = (DruidDataSource) dataSource;
        System.out.println("数据源最大连接数：" + druidDataSource.getMaxActive());
        System.out.println("数据源初始化连接数：" + druidDataSource.getInitialSize());

        // 关闭连接
        connection.close();
    }
}

配置 Druid 数据源监控

// 配置Druid监控管理后台的Servlet（固定格式）
// 内置Servlet容器时没有web.xml文件，所以使用Spring Boot注册Servlet方式
@Bean
public ServletRegistrationBean statViewServlet() {
    ServletRegistrationBean bean = new ServletRegistrationBean(
            // /druid/*：访问路径，可自定义
            new StatViewServlet(), "/druid/*");
    // 这些参数可以在 com.alibaba.druid.support.http.StatViewServlet
    // 的父类 com.alibaba.druid.support.http.ResourceServlet 中找到
    Map<String, String> initParams = new HashMap<>();
    // 后台管理界面的登录账号，K 为固定名称
    initParams.put("loginUsername", "自定义用户名");
    // 后台管理界面的登录密码，K 为固定名称
    initParams.put("loginPassword", "自定义密码");

    // 后台允许访问
    // initParams.put("allow", "localhost")：表示只有本机可以访问
    // initParams.put("allow", "")：为空或者为null时，表示允许所有访问
    initParams.put("allow", "");

    // deny：Druid 后台拒绝谁访问
    // initParams.put("test", "192.168.1.20"); 表示禁止此ip访问

    // 设置初始化参数
    bean.setInitParameters(initParams);
    return bean;
}

// 配置Druid web监控filter过滤器
// WebStatFilter：用于配置Web和Druid数据源之间的管理关联监控统计
@Bean
public FilterRegistrationBean webStatFilter() {
    FilterRegistrationBean bean = new FilterRegistrationBean();
    bean.setFilter(new WebStatFilter());
    // exclusions：设置哪些请求进行过滤排除掉，从而不进行统计
    Map<String, String> initParams = new HashMap<>();
    initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*");
    bean.setInitParameters(initParams);
    // "/*" 表示过滤所有请求
    bean.setUrlPatterns(Arrays.asList("/*"));
    return bean;
}

四、整合 MyBatis

4.1 环境配置

<!--mybatis-spring-boot-starter：非spring官方，作用整合-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

# MySQL - 8.0.28
spring:
  datasource:
    username: 数据库用户名
    password: 数据库密码
    # serverTimezone=UTC解决时区报错
    url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&useSSL=true
    driver-class-name: com.mysql.cj.jdbc.Driver

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

4.2 整合测试

部门表测试 Department

// 导入 Lombok
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Department {
    private Integer id;
    private String departmentName;
}

// @Mapper：表示本类是一个MyBatis的Mapper接口类
// 或在主程序类中，使用：@MapperScan("com.study.mapper") 扫描包
@Mapper
// 注册组件：@Component --> dao或mapper的层的表示方式
@Repository
public interface DepartmentMapper {
    // 获取所有部门信息
    List<Department> getDepartmentList();
   
    // 通过id获得部门
    Department getDepartmentById(@Param("id") Integer id);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.mapper.DepartmentMapper">
    <!--查询全部部门-->
    <select id="getDepartmentList" resultType="Department">
        select *
        from `department`;
    </select>

    <!--通过id获得部门-->
    <select id="getDepartmentById" resultType="Department" parameterType="int">
        select *
        from `department`
        where id = #{id};
    </select>
</mapper>

# 整合 MyBatis
mybatis:
  # 对应实体类的路径
  type-aliases-package: com.study.pojo
  # 指定myBatis的核心配置文件与Mapper映射文件,classpath:后面不需要加 /
  mapper-locations: classpath:mybatis/mapper/*.xml

  # 开启驼峰命名规范自动映射
  configuration:
    map-underscore-to-camel-case: true

@RestController
public class DepartmentController {
    @Autowired
    DepartmentMapper departmentMapper;

    // 查询全部部门
    @GetMapping("/t1")
    public List<Department> getDepartmentList() {
        return departmentMapper.getDepartmentList();
    }

    // 通过id获得部门
    @GetMapping("/t2/{id}")
    public Department getDepartmentById(@PathVariable("id") Integer id) {
        return departmentMapper.getDepartmentById(id);
    }
}

员工表测试 Employee

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    // 性别：0 女  1 男
    private Integer gender;
    // 部门id
    private Integer department;
    private Date birth;

    // 部门信息（联表查询）
    private Department eDepartment;
}

// @Mapper：表示本类是一个MyBatis的Mapper接口类
// 或在主程序类中，使用：@MapperScan("com.study.mapper") 扫描包
@Mapper
// 注册组件：@Component --> dao或mapper的层的表示方式
@Repository
public interface EmployeeMapper {
    // 查询全部员工信息
    List<Employee> getAllEmployee();

    // 新增一个员工
    int addEmployee(Employee employee);

    // 修改员工信息
    int updateEmployee(Employee employee);

    // 通过id获得员工信息
    Employee getEmployeeById(@Param("id") Integer id);

    // 通过id删除员工
    int deleteEmployeeById(@Param("id") Integer id);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.mapper.EmployeeMapper">
    <!--查询全部员工信息：联表查询-->
    <!--结果集映射 id对应resultMap type对应实体类-->
    <resultMap id="EmployeeMap" type="Employee">
        <!-- id为主键 -->
        <id property="id" column="eid"/>
        <!-- property对应实体类的属性名,column数据库表的列名 ,  -->
        <result property="lastName" column="last_name"/>
        <result property="email" column="email"/>
        <result property="gender" column="gender"/>
        <result property="birth" column="birth"/>
        <!--association：关联对象  javaType：关联对象类型 -->
        <association property="eDepartment" javaType="Department">
            <id property="id" column="did"/>
            <result property="departmentName" column="dname"/>
        </association>
    </resultMap>
    <select id="getAllEmployee" resultMap="EmployeeMap">
        SELECT e.`id` eid,
               `last_name`,
               `email`,
               `gender`,
               `birth`,
               d.`id` did,
               `department_name` dname
        FROM `employee` e,
             `department` d
        WHERE d.`id` = e.`department`
    </select>

    <!--新增一个员工-->
    <insert id="addEmployee" parameterType="Employee">
        insert into `employee` (`last_name`, `email`, `gender`, `department`, `birth`)
        values (#{lastName}, #{email}, #{gender}, #{department}, #{birth});
    </insert>

    <!--修改员工信息-->
    <update id="updateEmployee" parameterType="Employee">
        update `employee`
        set `last_name`=#{lastName},
            `email`=#{email},
            `gender`=#{gender},
            `department`=#{department},
            `birth`=#{birth}
        where id = #{id};
    </update>

    <!--通过id获得员工信息-->
    <select id="getEmployeeById" resultType="Employee" parameterType="int">
        select *
        from `employee`
        where id = #{id};
    </select>

    <!--通过id删除员工-->
    <delete id="deleteEmployeeById" parameterType="int">
        delete
        from `employee`
        where id = #{id};
    </delete>
</mapper>

// 直接返回字符串
@RestController
public class EmployeeController {
    // 按类型自动装配
    @Autowired
    EmployeeMapper employeeMapper;

    // 查询全部员工
    @GetMapping("/empAll")
    public List<Employee> getAllEmployee() {
        return employeeMapper.getAllEmployee();
    }

    // 新增一个员工
    @GetMapping("/addEmp")
    public int addEmployee() {
        // 模拟前端数据
        Employee emp = new Employee();
        emp.setLastName("测试1");
        emp.setEmail("test@aa.com");
        emp.setGender(1);
        emp.setDepartment(105);
        emp.setBirth(new Date());
        return employeeMapper.addEmployee(emp);
    }

    // 修改员工信息
    @GetMapping("/updateEmp/{id}")
    public int updateEmployee(@PathVariable("id") Integer id) {
        // 根据id查询员工
        Employee emp = employeeMapper.getEmployeeById(id);
        // 模拟前端数据
        // 修改员工信息
        emp.setLastName("修改测试");
        emp.setEmail("yy@aa.com");
        emp.setGender(0);
        emp.setDepartment(101);
        emp.setBirth(new Date());
        return employeeMapper.updateEmployee(emp);
    }

    // 通过id获得员工信息
    @GetMapping("/getEmp/{id}")
    public Employee getEmployeeById(@PathVariable("id") Integer id) {
        return employeeMapper.getEmployeeById(id);
    }

    // 通过id删除员工
    @GetMapping("/delEmp/{id}")
    public int deleteEmployeeById(@PathVariable("id") Integer id) {
        return employeeMapper.deleteEmployeeById(id);
    }
}

总结：

上一篇下一篇

猜你喜欢

热点阅读