spring Boot(七)——数据库(JDBC、Druid、MyBatis、JPA)

一、JDBC

导入依赖：

<!--jdbc-->
        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <!--<scope>runtime</scope>-->
        </dependency>

数据源：

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://ip:端口/test1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

参数：

#指定
spring.datasource.schema = 建表sql
spring.datasource.data  = 表数据
#不指定  默认只需将文件命名为：
schema-*.sql
data-*.sql

使用：

spring.datasource.initialization-mode=always
spring.datasource.schema=classpath:sql/schema-ddl.sql

操作数据库：

@Autowired
    private JdbcTemplate jdbcTemplate;
    @ResponseBody
    @GetMapping(value = "/hello")
    public Map<String, Object> hel() {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from login");
        return list.get(0);
    }

二、Druid

2.1引入依赖

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

2.2配置

# 初始化大小，最小，最大
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query: SELECT 1 FROM DUAL
spring.datasource.druid.test-while-idle: true
spring.datasource.druid.test-on-borrow: false
spring.datasource.druid.test-on-return: false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.druid.pool-prepared-statements=true
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.max-pool-prepared-statement-per-connection-size: 20
spring.datasource.druid.filters: stat,wall
spring.datasource.druid.use-global-data-source-stat: true
# 打开mergeSql功能；慢SQL记录
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=500
# 配置监控服务器
spring.datasource.druid.stat-view-servlet.enabled=true
# 登录名
spring.datasource.druid.stat-view-servlet.login-username=tchuhu
# 登录密码
spring.datasource.druid.stat-view-servlet.login-password=tchuhu
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
# 添加IP白名单
#spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
# 添加IP黑名单，当白名单和黑名单重复时，黑名单优先级更高
#spring.datasource.druid.stat-view-servlet.deny=127.0.0.1
# 添加过滤规则
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
# 忽略过滤格式
spring.datasource.druid.web-stat-filter.exclusions= "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"

2.3监控

三、整合MyBatis

mybatis参考文档:https://mybatis.org/mybatis-3/zh/configuration.html

3.1引入依赖

<!--引入mybatis -->
<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
</dependency>

3.2注解版

public interface UserMapper {

    @Select("select * from login where id = #{id}")
    public User getUserById(Integer id);

    @Delete("delete from login where id =#{id}")
    public int deleteById(Integer id);

    @Options(useGeneratedKeys = true,keyProperty = "id")//会返回自增的id
    @Insert("insert into login(name,pwd) values(#{name},#{pwd})")
    public int insertLogin(User user);

    @Update("update login set name =#{name} where id = #{id}")
    public int updateById(User user);

    @Select("select * from login")
    public List<User> getUsers();
}

3.2.1mapper扫描

//mapper包扫描
@MapperScan("com.mapper")

@SpringBootApplication
public class SpringBootWebApplication {

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

}

3.3配置版

配置文件

3.3.1配置文件配置

#mybatis配置
mybatis.config-location=classpath:mybatis/mybatis_config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

3.3.2mapper扫描

@MapperScan("com.mapper")
@SpringBootApplication
public class SpringBootWebApplication {

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

}

四、整合JPA

Java Persistence Api（Java持久化API）https://www.yiibai.com/jpa/jpa-inheritance-overview.html

4.0Spring Data JPA

JAP与Hibernate的关系

JPA的优势

JPA包含的技术

4.1实现

引入依赖:

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

4.2映射

4.2.1一对一

比如一个丈夫只能有一个老婆，数据库分别为men表和women表

          

接下来，编写实体类：

@Entity
@Table(name = "men")
public class Men { //老公类
    @Id    //主键
    /*
        @GeneratedValue(generator =GenerationType.IDENTITY )  //自增ID
            -AUTO主键由程序控制, 是默认选项 ,不设置就是这个
            -IDENTITY 主键由数据库生成, 采用数据库自增长, Oracle不支持这种方式
            -SEQUENCE 通过数据库的序列产生主键, MYSQL  不支持
            -Table 提供特定的数据库产生主键, 该方式更有利于数据库的移植
    */
    @Column(name = "menId")
    private String id;
    @Column(name = "menName")
    private String name;
    @OneToOne(optional = false) //woMen的id 可以为空
    @JoinColumn(name = "womenId",unique = true)//当前表对应表的列名， 唯一外键
    private Women women;

    public Men() {
    }
    //省略setter getter
}

@Entity
@Table(name = "women")
public class Women {  //老婆类
    @Id
    @Column(name = "womenId")
    private String id;
    @Column(name = "womenName")
    private String name;
    @OneToOne(fetch = FetchType.LAZY,mappedBy = "women") //这里放弃关系维护，交给men表来维护
    private Men men;

    public Women() {
    }
    //省略setter getter
}

4.2一对多

4.3多对一

4.4多对多

参考https://www.yiibai.com/jpa/jpa-one-to-one-mapping.html

https://blog.csdn.net/qq_41764878/article/details/89500653?ops_request_misc=%7B%22request%5Fid%22%3A%22158295242119724845010506%22%2C%22scm%22%3A%2220140713.130056874..%22%7D&request_id=158295242119724845010506&biz_id=0&utm_source=distribute.pc_search_result.none-task