2021-11（java-springboot学习笔记三）

目录

一、整合jdbc

1.创建项目，引入响应模块

2.编写yaml配置文件，连接数据库

 3.springboot已经默认帮我们配置好了，去测试类测试。

4.编写类测试

二、整合Druid数据源

1.依赖druid

2.切换数据源

3.可以设置初始化大小，最大最小值等

4.我们需要自己绑定我们配置的参数，然后添加到容器中

5.druid提供数据源监控功能

6.druid提供过滤器

三、整合mybatis

1.导入依赖

2.配置数据库连接信息

3.创建实体类

4.创建mapper包，创建mapper接口

5.创建对应的映射文件

6.进行测试

---

一、整合jdbc

1.创建项目，引入响应模块

2.编写yaml配置文件，连接数据库

spring:
  datasource:
    username: root
    password: root
    #?serverTimezone=UTC解决时区的报错
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

 3.springboot已经默认帮我们配置好了，去测试类测试。

@SpringBootTest
class Springboot04DataApplicationTests {
    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads() throws SQLException {
//        查看默认数据源  class com.zaxxer.hikari.HikariDataSource
        System.out.println(dataSource.getClass());
//        获得数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        connection.close();
    }

}

关于数据源我们并不做介绍，有了数据库连接，显然就可以 CRUD 操作数据库了。但是我们需要先了解一个对象 JdbcTemplate

JdbcTemplate：

1. 有了数据源，可以拿到数据库连接，有了连接，就可以使用原生的jdbc语句来操作数据库。
2. 即使不使用第三方，JdbcTemplate也对其封装
3. 数据库所有的方法都在JdbcTemplate
4. spring默认配置好了JdbcTemplate，需要时只需注入

JdbcTemplate只要有以下几类方法：

• execute：可以实行sql语句，一般用于执行ddl语句
• update和batchUpdate：前者用于增删改，后者用于实行批处理
• query和queryFor***：用于查询
• call：用于执行储存过程、函数

4.编写类测试

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

//    查询数据库的所有信息
    @GetMapping("/user")
    public List<Map<String,Object>> userList(){
        String sql = "select * from mybatis.user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }
    @GetMapping("/add")
    public String add(){
        String sql="insert into mybatis.user(id, name, pwd) VALUES (10,'jdbc','00')";
        jdbcTemplate.update(sql);
        return "update-ok";
    }
//修改用户信息
    @GetMapping("/update/{id}")
    public String updateUser(@PathVariable("id") int id){
        //插入语句
        String sql = "update employee set name=?,pwd=? where id="+id;
        //数据
        Object[] objects = new Object[2];
        objects[0] = "小鱼";
        objects[1] = "123";
        jdbcTemplate.update(sql,objects);
        //查询
        return "updateOk";
    }
}

二、整合Druid数据源

1.依赖druid

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency

2.切换数据源

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

3.可以设置初始化大小，最大最小值等

导入log4j依赖

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

#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 依赖即可，Maven 地址：https://mvnrepository.com/artifact/log4j/log4j
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

4.我们需要自己绑定我们配置的参数，然后添加到容器中

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
}

去测试类测试，发现数据源已经更改为自己配置的

5.druid提供数据源监控功能

package com.kun.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
//    后台监控
    @Bean
    public ServletRegistrationBean a(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
//        后台需要有人登录
        HashMap<String, String> init = new HashMap<>();
//        增加配置
        init.put("loginUsername","admin");
        init.put("loginPassword","123456");
//        允许谁能访问
        init.put("allow","");
        bean.setInitParameters(init);   //初始化参数
        return bean;
    }
}

配置完毕登录：http://localhost:8080/druid

6.druid提供过滤器

//    过滤器
    @Bean
    public FilterRegistrationBean webFilter(){
        FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
        bean.setFilter(new WebStatFilter());
        HashMap<String, String> init = new HashMap<>();

        init.put("exclusions","*.js,*.css./druid/*");
        
        bean.setInitParameters(init);
        return bean;
    }

三、整合mybatis

1.导入依赖

        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

2.配置数据库连接信息

spring:
  datasource:
    username: root
    password: 123456
    #?serverTimezone=UTC解决时区的报错
    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

3.创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

    private int id;
    private String name;
    private String pwd;

}

4.创建mapper包，创建mapper接口

//这个注解
@Mapper
@Repository
public interface UserMapper {
    List<User> queryUserList();
    User queryUserByID(int id);
    int addUser(User user);
    int updateUser(User user);
    int deleteUser(int id);

}

5.创建对应的映射文件

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kun.mapper.UserMapper">
    <select id="queryUserList" resultType="User">
        select * from mybatis.user;
    </select>

    <select id="queryUserByID" resultType="User" parameterType="int">
        select * from mybatis.user where id = #{id};
    </select>

    <insert id="addUser" parameterType="User">
        insert into mybatis.user (id, name, pwd)
        values (#{id},#{name},#{pwd});
    </insert>
    <update id="updateUser" parameterType="User">
        update mybatis.user
        set name=#{name},pwd =#{pwd}
        where id=#{id};
    </update>
    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>
</mapper>

6.进行测试

package com.kun.controller;
import com.kun.mapper.UserMapper;
import com.kun.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
@RestController
public class UserController {

    //private final UserMapper userMapper;
    //public UserController(UserMapper userMapper) {
    //    this.userMapper = userMapper;
    //}

    @Autowired
    private UserMapper userMapper;
    @GetMapping("/query")
    public List<User> queryUserList(){
        List<User> users = userMapper.queryUserList();
        for (User user : users) {
            System.out.println(user);
        }
        return users;
    }
    @GetMapping("/id/{id}")
    public User queryUserByID(@PathVariable("id") int id){
        User user = userMapper.queryUserByID(id);
        return user;
    }
    @GetMapping("/add/{id}/{name}/{pwd}")
    public List<User> addUser(@PathVariable("id") int id,@PathVariable("name") String name,@PathVariable("pwd") String pwd){
        User user = new User(id, name, pwd);
        userMapper.addUser(user);
        List<User> users = userMapper.queryUserList();
        return users;
    }
    @GetMapping("delete/{id}")
    public String delete(@PathVariable("id") int id){
        userMapper.deleteUser(id);
        return "ok";
    }
}