Spring-Spring Data

Spring
Spring
发布日期:   2020-04-21
更新日期:   2020-05-25
文章字数:   2.2k
阅读时长:   12 分
阅读次数:  

概述

Spring 对JDBC的支持有两种方式:

  1. JdbcTemplate
  2. JPA

使用Spring JdbcTemplate持久化数据

1. Maven依赖配置

  • JDBC

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

  • 数据库

    • H2 database
    <!-- H2 database -->
<dependency>
 <groupId>com.h2database</groupId>
 <artifactId>h2</artifactId>
 <scope>runtime</scope>
</dependency>
    • Sql Server
    <dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>8.3.0.jre11-preview</version>
</dependency>

2. 配置数据源

  • java配置方式

    @Configuration
public class JdbcConfiguration {
    /** java配置方式: SQL Server database **/
    //@Bean
    public DataSource dataSourceH2(){
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        dataSource.setUrl("jdbc:sqlserver://10.0.0.10;DatabaseName=test");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    /** java配置方式: H2 database **/
    //@Bean
    public DataSource dataSource(){
        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        builder.setType(EmbeddedDatabaseType.H2).setName("test");
        return builder.build();
    }
    /** 注册日期转换器 **/
    @Bean
    public Converter<String, LocalDate> dateConverter() {
        return new Converter<String, LocalDate>() {
            @Override
            public LocalDate convert(String source) {
                return LocalDate.parse(source, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            }
        };
    }
}

  • 配置文件方式: 在application.properties中配置

    # SQL Server
# spring.datasource.url=jdbc:sqlserver://10.0.0.10;DatabaseName=test
# spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
# spring.datasource.username=root
# spring.datasource.password=root

# H2
spring.datasource.platform=h2
spring.datasource.url=jdbc:h2:mem:test;DB_CLOSE_ON_EXIT=FALSE

3. JdbcTemplate实例

  • *domain: * User.java

    @Data
@AllArgsConstructor
public class User {
    @NotBlank(message = "用户名不能为空")
    private String name;
    @Size(min = 11,max = 11,message = "手机长度必须为11位")
    private String phone;
    @Size(min = 6,max = 12,message = "密码长度6~12")
    private String password;
}

  • thymeleaf : form.html

    <!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>表单</title>
</head>
<body>
    <form action="/form" method="post" th:object="${user}">
        <lable>name #: </lable><input type="text" th:field="*{name}" />
        <span class="fieldError" th:if="${#fields.hasErrors('*{name}')}" th:errors="*{name}"></span><br/>
        <lable>phone #: </lable><input type="text" name="phone" id="phone"/><br/>
        <lable>password #: </lable><input type="text" name="password" id="password"/><br/>
        <input type="submit" value="submit" />
    </form>
</body>
</html>

  • *Controller: * UserController.java

    @Controller
@RequestMapping("/form")
public class UserController {
    private JdbcUserRepository jdbcUserRepository;
    @Autowired
    public void setJdbcUserRepository(JdbcUserRepository jdbcUserRepository) {
        this.jdbcUserRepository = jdbcUserRepository;
    }

    @GetMapping
    public String form(User user){
        return "form";
    }

    @PostMapping
    public String submit(@Valid User user, Errors result){
        if (result.hasErrors()) {
            List<ObjectError> list = result.getAllErrors();
            for (ObjectError error : list) {
                System.out.println(error.getDefaultMessage());
            }
            return "form";
        }
        //业务逻辑处理
        User u = jdbcUserRepository.findOne(user.getName());

        //jdbcUserRepository.findAll();
        System.out.println(u.getName()+","+u.getPassword());
        return "form";
    }
}

  • *Repository: * JdbcUserRepository.java

    @Repository
public class JdbcUserRepository implements BaseRepository<User> {
    private JdbcTemplate template;
    @Autowired
    public JdbcUserRepository(JdbcTemplate template) {
        this.template = template;
    }

    @Override
    public Iterable<User> findAll() {
        String sql = "select top 5 Account,DisplayName,OfficePhone from BPMSysUsers";
        return template.query(sql, this::MapRowToUser);
    }

    @Override
    public User findOne(String id) {
        String sql = "select Account,DisplayName,OfficePhone from BPMSysUsers where Account = ?";
        return template.queryForObject(sql,this::MapRowToUser,id);
    }

    @Override
    public boolean save(User user) {
        return false;
    }

    private User MapRowToUser(ResultSet rs, int rows) throws SQLException{
        System.out.println(rows);
        return new User(
                rs.getString("Account"),
                rs.getString("OfficePhone"),
                rs.getString("DisplayName")
        );
    }

}

4. SimpleJdbcInsert实例

  • *domain: * PopForm.java PopApplySon.java

    @Data
public class PopForm {
    private Integer id;
    @NotBlank(message = "申请人不能为空")
    private String name;
    private String company;

    private LocalDate applyDate;
    @JsonProperty(value = "son")
    private List<PopApplySon> popApplySons;

    @JsonGetter(value = "APPLY_DATE") //序列化后
    @JsonSerialize(using = LocalDateSerializer.class) //注册jackson序列化日期
    public LocalDate getApplyDate() {
        return applyDate;
    }
    @JsonSetter(value = "applyDate")
    @JsonDeserialize(using = LocalDateDeSerializer.class) //注册jackson反序列化日期
    public void setApplyDate(LocalDate applyDate) {
        this.applyDate = applyDate;
    }
}

  • Jackson自定义序列化/反序列化日期

    //反序列化日期(json -> java)
public class LocalDateDeSerializer extends StdDeserializer<LocalDate> {
    public LocalDateDeSerializer() {
        this(null);
    }
    public LocalDateDeSerializer(Class<LocalDate> vc) {
        super(vc);
    }
    @Override
    public LocalDate deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException, JsonProcessingException {
        return LocalDate.parse(jsonParser.getText(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }
}
    //序列化日期(java -> json)
public class LocalDateSerializer extends StdSerializer<LocalDate> {
    public LocalDateSerializer() {
        this(null);
    }
    public LocalDateSerializer(Class<LocalDate> t) {
        super(t);
    }

    @Override
    public void serialize(LocalDate localDate, JsonGenerator jsonGenerator, SerializerProvider serializerProvider) throws IOException {
        jsonGenerator.writeString(localDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }
}

  • thymeleaf : popForm.html

    <!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>popApply</title>
    <script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
    <script>
        $(function(){
            $("#form_submit").on("click",function(){
                var data = $("#pop").serializeArray()
                var obj = {}
                obj.son = []
                console.log(data);
                for(let i = 0;i<data.length;++i){
                    let name = data[i].name;
                    if(name == "goodsCode"){
                        let sonobj = {};
                        sonobj[name] = data[i].value;
                        sonobj[data[++i].name] = data[i].value;
                        obj.son.push(sonobj);
                        continue;
                    }
                    obj[name] = data[i].value;
                }
                $.ajax({
                    type: "POST",
                    url: "/popForm",
                    dataType: "json",
                    traditional : true,
                    contentType : "application/json;charset=utf-8",
                    async: false,
                    data: JSON.stringify(obj),
                    success: function(data){
                        console.log(data);
                    },
                    error: function(msg){
                        alert("失败: " + msg);
                    }
                })
            })
        });
    </script>
</head>
<body>
<form id="pop">
    <span>applyName #: </span><input type="text" name="name" id="name"  /><br/>
    <span>company #: </span><input type="text" name="company" id="company" /><br/>
    <span>applyDate #: </span><input type="text" name="applyDate" id="applyDate" /><br/>
    <table>
        <tr>
            <td>品名</td>
            <td>数量</td>
        </tr>
        <tr>
            <td><input type="text" name="goodsCode" /></td>
            <td><input type="text" name="totalAmount" /></td>
        </tr>
        <tr>
            <td><input type="text" name="goodsCode" /></td>
            <td><input type="text" name="totalAmount" /></td>
        </tr>
    </table>
    <span id="form_submit">提交</span>
</form>
</body>
</html>

  • *Controller: * PopFormController.java

    @Controller
@RequestMapping(value = "/popForm")
public class PopFormController {
    private JdbcPopFormRepository jdbcPopFormRepository;
    @Autowired
    public void setJdbcPopFormRepository(JdbcPopFormRepository jdbcPopFormRepository) {
        this.jdbcPopFormRepository = jdbcPopFormRepository;
    }

    //get请求时展示表单申请页面
    @GetMapping
    public String show(){
        return "popForm";
    }
    //post提交时保存申请数据
    @PostMapping
    @ResponseBody
    public String process(@RequestBody PopForm popForm){
        jdbcPopFormRepository.save(popForm);
        return "{\"success\":\"true\"}"; //返回json字符串
    }
}

  • *Repository: * JdbcPopFormRepository.java

    @Repository
public class JdbcPopFormRepository implements BaseRepository<PopForm> {
    private JdbcTemplate template;
    private SimpleJdbcInsert popInsert;
    private SimpleJdbcInsert popSonInsert;
    private ObjectMapper objectMapper;

    //依赖注入
    @Autowired
    public JdbcPopFormRepository(JdbcTemplate template) {
        this.template = template;
        objectMapper = new ObjectMapper();
    }

    @Override
    public Iterable<PopForm> findAll() {
        return null;
    }
    @Override
    public PopForm findOne(String id) {
        return null;
    }

    @Override
    public boolean save(PopForm popForm) {
        System.out.println(popForm);
        popInsert = new SimpleJdbcInsert(template)
                .withTableName("POP_APPLY") //要插入的表
                .usingColumns("NAME","COMPANY","APPLY_DATE") //要插入的字段
                .usingGeneratedKeyColumns("ID"); //设置主键,执行executeAndReturnKey会返回此键
        popSonInsert = new SimpleJdbcInsert(template)
                .withTableName("POP_APPLY_SON")
                .usingColumns("POP_APPLY_ID","GOODS_CODE","AMOUNT");
        //先保存主表
        int id = savePop(popForm);
        //保存明细表
        popForm.getPopApplySons().stream().forEach(
                e -> {
                    e.setPopApplyId(id);
                    savePopSon(e);
                }
        );
        return true;
    }

    private int savePop(PopForm popForm){
        Map<String,Object> values = objectMapper.convertValue(popForm,Map.class);
        System.out.println(values);
        return popInsert.executeAndReturnKey(values).intValue();
    }
    private void savePopSon(PopApplySon son){
        Map<String,Object> values = objectMapper.convertValue(son,Map.class);
        System.out.println(values);
        popSonInsert.execute(values);
    }
}

使用Spring JPA持久化数据

1. Maven依赖配置

  • 该依赖项不仅包含Spring Data JPA,同时默认使用Hibernate作为 JPA实现

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

  • 如果想使用其他的JPA实现,例如使用EclipseLink替换Hibernate

    <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 <exclusions>
 <exclusion>
 <artifactId>hibernate-entitymanager</artifactId>
 <groupId>org.hibernate</groupId>
 </exclusion>
 </exclusions>
</dependency>
<dependency>
 <groupId>org.eclipse.persistence</groupId>
 <artifactId>eclipselink</artifactId>
 <version>2.5.2</version>
</dependency>

2. Spring data JPA实例

  • *domain: * PopForm.java / PopDetail.java

    @Data
@Entity
@Table(name = "POP_APPLY")
public class PopForm {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    @NotBlank(message = "申请人不能为空")
    private String name;
    private String company;
    //@JsonIgnore
    private LocalDate applyDate;
    @JsonProperty(value = "son")
    @OneToMany(targetEntity = PopDetail.class,cascade = CascadeType.PERSIST)
    @JoinColumn(name = "POP_APPLY_ID")
    private List<PopDetail> popDetails;
    @PrePersist
    void applyDateAt(){
        this.applyDate = LocalDate.now();
    }
}
    @Data
@Entity
@Table(name = "POP_APPLY_SON")
public class PopDetail {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String goodsCode;
    @JsonProperty(value = "totalAmount")
    @Column(name = "totalAmount")
    private Integer amount;
}

  • *Thymeleaf: * popForm.html

    <!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>popApply</title>
    <script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
    <script>
        $(function(){
            $("#form_submit").on("click",function(){
                var data = $("#pop").serializeArray()
                var obj = {}
                obj.son = []
                console.log(data);
                for(let i = 0;i<data.length;++i){
                    let name = data[i].name;
                    if(name == "goodsCode"){
                        let sonobj = {};
                        sonobj[name] = data[i].value;
                        sonobj[data[++i].name] = data[i].value;
                        obj.son.push(sonobj);
                        continue;
                    }
                    obj[name] = data[i].value;
                }
                $.ajax({
                    type: "POST",
                    url: "/popFormJpa",
                    dataType: "json",
                    traditional : true,
                    contentType : "application/json;charset=utf-8",
                    async: false,
                    data: JSON.stringify(obj),
                    success: function(data){
                        console.log(data);
                    },
                    error: function(msg){
                        alert("失败: " + msg);
                    }
                })
            })
        });
    </script>
</head>
<body>
<form id="pop">
    <span>applyName #: </span><input type="text" name="name" id="name"  /><br/>
    <span>company #: </span><input type="text" name="company" id="company" /><br/>
    <table>
        <tr>
            <td>品名</td>
            <td>数量</td>
        </tr>
        <tr>
            <td><input type="text" name="goodsCode" /></td>
            <td><input type="text" name="totalAmount" /></td>
        </tr>
        <tr>
            <td><input type="text" name="goodsCode" /></td>
            <td><input type="text" name="totalAmount" /></td>
        </tr>
    </table>
    <span id="form_submit">提交</span>
</form>
</body>
</html>

  • *Controller: * PopFormController.java

    @Controller(value = "jpaPopFormController")
@RequestMapping(value = "/popFormJpa")
public class PopFormController {
    private JpaPopFormRepository jpaPopFormRepository;
    @Autowired
    public void setJpaPopFormRepository(JpaPopFormRepository jpaPopFormRepository) {
        this.jpaPopFormRepository = jpaPopFormRepository;
    }

    @GetMapping
    public String show(){
        return "jpa/popForm";
    }
    @PostMapping
    @ResponseBody
    public String process(@RequestBody PopForm popForm){
        System.out.println(popForm);
        jpaPopFormRepository.save(popForm);
        return "{\"success\":\"true\"}";
    }
}

  • *Repository: * JpaPopFormRepository.java

    public interface JpaPopFormRepository extends CrudRepository<PopForm,Integer> {
}

3. 自定义Repository

  • Spring Data提供了一些命名约定可以通过相应的方法命名规则自动帮你实现查询功能

    • read,get,find方法名起始进行查询
    • By后面跟查询的字段
    • And多个查询条件
    • Between查询范围
    • IsAfter, After, IsGreaterThan, GreaterThan
    • IsGreaterThanEqual, GreaterThanEqual
    • IsBefore, Before, IsLessThan, LessThan
    • IsLessThanEqual, LessThanEqual
    • IsBetween, Between
    • IsNull, Null
    • IsNotNull, NotNull
    • IsIn, In
    • IsNotIn, NotIn
    • IsStartingWith, StartingWith, StartsWith
    • IsEndingWith, EndingWith, EndsWith
    • IsContaining, Containing, Contains
    • IsLike, Like
    • IsNotLike, NotLike
    • IsTrue, True
    • IsFalse, False
    • Is, Equals
    • IsNot, Not
    • IgnoringCase, IgnoresCase

  • Spring Data还提供了自定义查询语句通过@Query等注解

  • 实例

    public interface JpaPopFormRepository extends CrudRepository<PopForm,Integer> {
    //使用命名约定(naming convention)
    List<PopForm> findPopFormsByCompanyAndAndApplyDateBetween(String name, LocalDate start,LocalDate end);
    List<PopForm> readPopFormsByNameInOrderByApplyDate(String... name);

    //使用@Query
    @Query("from PopForm p where p.name = ?1")
    List<PopForm> getPopForms(String name);
    //使用@Param注入参数
    @Query("from PopForm p where p.id = :id")
    PopForm getPopForm(@Param("id") int id);
    //使用原生的SQL
    @Query(value = "SELECT * FROM POP_APPLY AS a WHERE a.id = ?1",nativeQuery = true)
    PopForm getNativePopForm(int id);
}
文章作者: Bryson
文章链接: https://brysonbs.github.io/2020/04/21/Spring-Spring%20Data/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Bryson !
Spring
评论
 上一篇
设计模式-组合模式 设计模式-组合模式
组合模式的定义 组合(Composite)模式: ** 又叫部分-整体模式** ,它是一种将对象组合成树状的层次结构的模式,用来表示”部分-整体”的关系,使用户对单个对象和组合对象具有一致的访问性 *通俗的说: *就是在对象内部维护另一个对
2020-04-21 设计模式
设计模式
下一篇 
Spring-开发web应用 Spring-开发web应用
信息展示实例 创建domain *domain层: *通常用于放置在系统中,与数据库中的表对应起来的JavaBean @Data //lombok注解: 自动生成constructor getter/setter tostring方法 @R
2020-04-20 Spring
Spring
  目录