Spring Boot_数据访问

2020.03.02

fighting！fighting！

Spring Boot 数据访问

1. JDBC

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

spring:
  datasource:
    username: root
    password: 5211
    url: jdbc:mysql://localhost:3306/sb_jdbc
    driver-class-name: com.mysql.jdbc.Driver

效果:

默认使用:class org.apache.tomcat.jdbc.pool.DataSource数据源
数据源的相关配置都在DataSourceProperties里面

自动配置原理:

org.springframework.boot.autoconfigure.jdbc
DataSourcelnitializer: ApplicationListener

作用:
- runSchemaScripts();运行建表语句
- runDataScripts();运行插入数据的sql语句
默认只要将文件命名为:
```
schema-*.sql,data-*.sql
默认规则:schema.sql,schema-all.sql
可以使用 
	schema:
		- classpath:department.sql
		指定位置
```

2. Druid*

<!--引入druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.8</version>
</dependency>

//导入druid的数据源
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

    //配置Druid的监控
    //1.配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();

        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默认允许所有访问
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);
        return bean;
    }
    //2.配置一个监控的filter
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));

        return bean;
    }
}

3. MyBatis

4.1 整合 MyBatis

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

配置数据源相关属性(Druid)
给数据库建表
创建JavaBean

4.2 MyBatis 注解版

//这是一个操作数据库的mapper
@Mapper
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDeptById(int id);

    @Delete("delete from department where id=#{id}")
    public int deleteDept(int id);

    //@Options 插入成功后显示主键
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @GetMapping("/dept/{id}")
    public Department getDepartment(@PathVariable("id") int id){
        return departmentMapper.getDeptById(id);
    }

    @GetMapping("/dept")
    public Department insertDept(Department department){
        departmentMapper.insertDept(department);
        return department;
    }
}

问题:

自定义MyBatis的配置规则;给容器中添加一个ConfigurationCustomizer

@Configuration
public class MyBatisConfig {

    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer(){
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

//批量扫描所有得mapper接口
@MapperScan(value = "work.idler.springboot.mapper")

4.3 MyBatis 配置版

mybatis:
  # 全局配置
  config-location: classpath:mybatis/mybatis-config.xml
  # sql配置文件
  mapper-locations: classpath:mybatis/mapper/*.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        命名问题
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="work.idler.springboot.mapper.EmployeeMapper">
<!--public Employee getEmpById(int id);

    public void insetEmp(Employee employee);-->
    <select id="getEmpById" resultType="work.idler.springboot.bean.Employee">
        select * from employee where id=#{id}
    </select>

    <insert id="insertEmp">
        insert into employee(lastName,email,gender,d_id) values (#{lastName},#{email},#{gender},#{dId})
    </insert>
</mapper>

4. Spring Data*

4.1 整合JPA

JPA:ORM(Object Relational Mapping);

1.编写一个实体类(bean)和数据库进行映射,并且配置好映射关系

//使用JPA注解配置映射关系
@Entity //告诉JPA者是一个实体类(和数据表映射的类)
@Table(name = "tb1_user") //@Table 来指定和那个数据表对应 如果省略默认表名就是user
public class User {

    @Id //这是一个主键
    @GeneratedValue(strategy = GenerationType.IDENTITY) //自增主键
    private Integer id;

    @Column(name = "last_name", length = 50) //这是和数据表对应的一个列
    private String lastName;

    @Column //省略默认列名就是属性名
    private String email;

2.编写一个Dao接口来操作实体类对应的数据表(Repository)

//继承JpaRepository来完成对数据库的操作
public interface UserRepository extends JpaRepository<User, Integer> {
}

3.基本配置

spring:
  datasource:
    url: jdbc:mysql://localhost/sb_jpa
    username: root
    password: 5211
    driver-class-name: com.mysql.jdbc.Driver

  jpa:
    hibernate:
    # 更新或者创建数据表结构
      ddl-auto: update
    # 控制台显示SQL
    show-sql: true

Spring

△

想要上位的学渣