SpringBoot 二：配置文件与JPA操作mysql数据库

SpringBoot项目的搭建参见上篇文章记录一下自己用idea通过maven创建一个简单的spring boot例子

springboot的配置文件有两种格式，一种是application.yml一种是application.properties。

以设置端口号为例

application.yml

server:
  port: 8088

application.properties

server.port=8088

    本人喜欢yml格式的文件，看起来层次比较清晰，yml每一层要缩进2个空格，冒号（:）和值之间有一个空格。

    多个环境可以写多个配置文件，如application-dev.yml,application-prod.yml。application.yml可以放置一些与环境无关的配置，如除地址账号外的数据库连接配置。

application.yml:

#指定默认端口为8088
server:
  port: 8088

#指定默认启动环境为dev
spring:
  profiles:
    active: dev
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
#将Date类型在接口返回值中转为该格式的String
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss

application-dev.yml

spring：
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/match?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
    username: root
    password: 123456
  #    打印sql，不建议正式服也打印sql，所以只配置在dev文件中。
  jpa:
    show-sql: true

？后面前两个参数是编码设置，useSSL是数据库ssl设置问题Establishing SSL connection without server's identity verification is not recommended.，serverTimezone是时区问题，解决启动时的The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver问题。有时候mysql数据库设置好的话后面的参数其实不需要的，

使用jpa及连接mysql数据库需要的依赖，如果mysql版本过高的话，根据自己的数据库版本选择依赖版本，一般情况下可以选择下面注释掉的那个依赖。

        <!-- jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>
<!--
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
-->

实体类College:

package com.demo.entity;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;

/**
 * @Author fusheng
 */
@Data
@Entity
@Table(name = "college")
@DynamicInsert
@DynamicUpdate
public class College {

    @Id
    @GeneratedValue(generator = "id")
    @GenericGenerator(name = "id", strategy = "uuid")
    private String id;

    @Column(name = "college_name")
    private String name;

    private String collegeAddress;

    private String collegePhone;

    private String collegeWeb;

    private Date createdTime;

    private Date updatedTime;

    private Integer isDelete;

}

@Data是lombok插件的一个注解，包含getter,setter...等。

@Entity表明它是一个数据库表的实体类，@Table在实体类和表名不一致时说明tablename。@Id主键注解，可以使用jpa自带的一些方法。@Column字段注解，当实体类中字段名和表字段不一致时需要说明。jpa会自动进行驼峰转换，collageAddress会转成college_address。DynamicInsert，DynamicUpdate是动态插入和动态更新，当保存的值为null不插入这个值而不会向数据库插入null。@GeneratedValue@GenericGenerator上面的配置是设置UUID，更多内容可自行搜索了解。

数据操作层CollegeRepo，下面展示2种常用的查询

package com.demo.repo;

import com.demo.entity.College;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

/**
 * @Author fusheng
 */
@Repository
public interface CollegeRepo extends JpaRepository<College,String> {

    College findByName(String name);

    @Query("select c from College c where c.name = ?1")
    College findByCollegeName(String name);
}

@Repository说明这是一个操作数据库的类。此时可以直接使用jpa自带的一些简单方法，如findAll(),findOne(T id),delete,save等方法。也可以通过@Query注解写sql（第二个方法）。对符合以下命名规则的方法，jpa还会自动生成sql语句（第一个方法）。

DateUtil

package com.demo.util;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Author fusheng
 * java8之后LocalDate,LocalDateTime比Date更适合操作时间，后期完成后再重新修改替换
 */
public class DateUtil {
    
    public final static String PATTERN_DATE = "yyyy-MM-dd";
    public final static String PATTERN_DATETIME = "yyyy-MM-dd HH:mm:ss";

    public static Date now(){
        return new Date();
    }

    public static String dateToString(Date date, String format){
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(date);
    }

    public static String dateToString(Date date){
        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_DATETIME);
        return sdf.format(date);
    }

    public static Date stringToDate(String time, String format){
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        try {
            return sdf.parse(time);
        } catch (ParseException e) {
            return null;
        }
    }

    public static Date stringToDate(String time){
        SimpleDateFormat sdf = new SimpleDateFormat(PATTERN_DATETIME);
        try {
            return sdf.parse(time);
        } catch (ParseException e) {
            return null;
        }
    }
    

    public static void main(String[] args) {
        String time = "2018-09-11 12:00:00";
        Date date = stringToDate(time,PATTERN_DATETIME);
        System.out.println(date);
    }

}

数据插入请求实体CollegeReq

package com.demo.dto.req;

import lombok.Data;

import javax.validation.constraints.NotNull;

/**
 * @Author fusheng
 */
@Data
public class CollegeReq {
    @NotNull
    private String name;

    @NotNull
    private String address;

    private String phone;

    private String web;
}

CollegeService:

package com.demo.service;

import com.demo.dto.req.CollegeReq;
import com.demo.entity.College;
import com.demo.repo.CollegeRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import static com.demo.util.DateUtil.now;

/**
 * @Author fusheng
 */
@Service
public class CollegeService {

    @Autowired
    CollegeRepo repo;

    public College findCollegeByName(String name){
        return repo.findByName(name);
    }

    public College findCollegeById(String id){
        return repo.findOne(id);
    }

    public void saveCollege(CollegeReq req){
        College college = new College();
        college.setName(req.getName());
        college.setCollegeAddress(req.getAddress());
        college.setCollegePhone(req.getPhone());
        college.setCollegeWeb(req.getWeb());
        college.setCreatedTime(now());
        repo.save(college);
    }
}

Controller:

package com.demo.controller;

import com.demo.dto.req.CollegeReq;
import com.demo.entity.College;
import com.demo.service.CollegeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;

/**
 * @Author fusheng
 */
@RestController
public class MatchController {
    @Autowired
    CollegeService service;

    @GetMapping(value = "/college")
    public College getCollegeByName(@RequestParam(name = "name") String name){
        return service.findCollegeByName(name);
    }

    @GetMapping(value = "/college2")
    public College getCollegeById(@RequestParam(name = "id") String id){
        return service.findCollegeById(id);
    }

    @PostMapping(value = "/college/save")
    public String saveCollege(@RequestBody@Valid CollegeReq req){
        service.saveCollege(req);
        return "success";
    }
}

在上面的内容中，jpa节省了大量的sql语句，但是有些复杂的查询呢，这边在推荐一个类QueryDslPredicateExecutor。

public interface CollegeRepo extends JpaRepository<College,String>,QueryDslPredicateExecutor<College>

//依赖
    <dependencies>
        <!--query dsl -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

<build>
        <plugins>
            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

之后需要执行 maven compile命令，会生成一个QCollege.class文件。使用如下：

    public College findByNameAndTime(String name, String address){
        QCollege qCollege = QCollege.college;
        Predicate predicate = qCollege.name.eq(name).and(qCollege.collegeAddress.like("%" + address + "%"));
        return repo.findOne(predicate);
    }

后面发现不知道怎么回事QCollege找不到了，执行maven compile命令的时候出错，上面加上一个依赖，改为

            <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
                <dependencies>
                <dependency>
                    <groupId>com.querydsl</groupId>
                    <artifactId>querydsl-apt</artifactId>
                    <version>${querydsl.version}</version>
                </dependency>
                </dependencies>
            </plugin>

接下来简单介绍一下OneToMany和ManyToOne的使用。

@Data
@Entity
@Table(name = "compete_team")
@DynamicInsert
@DynamicUpdate
public class CompeteTeam {
    @Id
    @GeneratedValue(generator = "id")
    @GenericGenerator(name = "id", strategy = "uuid")
    private String id;

    private String collegeId;

    private String teamName;

    private String teamSlogan;
    private Date createdTime;

    private Date updatedTime;

    private Integer isDelete;

    @ManyToOne
    @JoinColumn(name = "collegeId",insertable = false,updatable = false)
    private College college;
}

College.java类增加代码（有使用的关联的类不能直接返回或者set进某个实体，不然会进入死循环报错）

    @OneToMany(mappedBy = "college")
    private List<CompeteTeam> teamList;

使用示例：

public CollegeTeamDto findCollegeTeam(String collegeName){
        QCollege qCollege = QCollege.college;
        Predicate predicate = qCollege.name.eq(collegeName)
                .and(qCollege.teamList.any().isDelete.eq(0));
        College college = repo.findOne(predicate);
        CollegeTeamDto teamDto = new CollegeTeamDto();
        teamDto.setCollegeName(college.getName());
        teamDto.setCollegeAddress(college.getCollegeAddress());
        teamDto.setTeamList(
             college.getTeamList().stream()
                    .map(this::convert)
                    .collect(Collectors.toList())
        );
        return teamDto;
    }

    private Team convert(CompeteTeam competeTeam){
        Team team = new Team();
        team.setTeamName(competeTeam.getTeamName());
        team.setTeamSlogan(competeTeam.getTeamSlogan());
        return team;

    }

在项目中使用java8的localdate和localdatetime类

一种方法是增加依赖，jpa底层其实还是hibernate。

 <dependency>
     <groupId>org.hibernate</groupId>
     <artifactId>hibernate-java8</artifactId>
     <version>5.0.12.Final</version>
 </dependency>

二是实现AttributeConverter类,LocalDate与Date对应，LocalDateTime和Timestamp对应，如果用到两种时间需要实现两次

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.sql.Date;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;

/**
 * @Date 2019-12-04 9:45
 * @Author fushenng
 */
@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime localDateTime) {
        return localDateTime == null ? null : Timestamp.valueOf(localDateTime);
    }

    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp timestamp) {
        return timestamp == null ? null : timestamp.toLocalDateTime();
    }
}

配置文件的读取

上面我们添加了一些配置文件，但是都是由spring读取的，我们并没有用到这些配置文件，接下来讲解我们怎么获取并使用这些配置文件。

province:
  name: 浙江省
  citys:
    - name: 杭州市
      remark: 省会
    - name: 温州市
      remark: 皮革厂倒闭了

我们根据上面的配置文件格式来建立一个实体类

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @Author fusheng
 */
@ConfigurationProperties(prefix = "province")
@Component
@Data
public class Province {

    private String name;
    private List<City> citys;

    @Data
    public static class City{
        private String name;
        private String remark;
    }
}

通过注解@ConfigurationProperties获取配置文件的值并注入到实体中，prefix主要作用是告知要注入的配置文件位置，然后会根据参数名和配置文件进行对应。配置文件中（- ）的作用是说明下面的配置是一个集合，所以在实体中我们用List来接收。

import com.demo.dto.Province;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

/**
 * @Author fusheng
 */
@Service
public class ProvinceService {
    @Autowired
    private Province province;

    @Value("${province.name}")
    private String name;

    public String name(){
        return name;
    }

    public Province detail(){
        return province;
    }
}

@RestController
public class ProvinceController {

    @Autowired
    ProvinceService service;

    @GetMapping("/province/name")
    public String name(){
        return service.name();
    }

    @GetMapping("/province/detail")
    public Province detail(){
        return service.detail();
    }
}

在需要用到的地方，我们直接用@Autowired直接获取province实体，例：调用http://localhost:8088/province/detail返回数据为

{"name":"浙江省","citys":[{"name":"杭州市","remark":"省会"},{"name":"温州市","remark":"皮革厂倒闭了"}]}

我们也可以在service中直接使用province的各种配置信息。

@Value注解是使用配置文件的另外一种方式，格式为@Value("${}")，这个只是读取单条配置信息。

关于配置文件更详细点的知识可以参考Spring Boot配置文件详解

以上就是该篇文章的全部内容，有些知识可能说的不够详细，不对之处欢迎指出。