【Spring Boot&&Spring Cloud系列】Spring Boot中使用数据库之MySql-白红宇

【Spring Boot&&Spring Cloud系列】Spring Boot中使用数据库之MySql

阅读量：6284 次

发布时间：2019-06-22

本文共 15317 字，大约阅读时间需要 51 分钟。

对于传统关系型数据库来说，Spring Boot使用JPA(Java Persistence API)资源库提供持久化的标准规范，即将Java的普通对象通过对象关系映射（ORM）持久化到数据库中。

项目代码地址：

一、使用MySQL是maven中的依赖配置


                 
      
       mysql
                  
      
       mysql-connector-java
                  
      
       runtime
              
             
                 
      
       org.springframework.boot
                  
      
       spring-boot-starter-data-jpa

二、建立数据实体

1、假设有部门、用户、角色三个实体，且关系为一个用户只能隶属于一个部门，一个用户可以拥有多个角色

2、Java类

User.java

1 package com.slp.entity; 2  3 import com.fasterxml.jackson.annotation.JsonBackReference; 4 import org.springframework.format.annotation.DateTimeFormat; 5  6 import javax.persistence.*; 7 import java.util.Date; 8 import java.util.List; 9 10 11 /**12  * Created by sangliping on 2017/8/18.13  * @ManyToOne定义User与Department的多对一关系，并用中间表user_role来存储他们的ID14  * @DateTimeFormat日期进行格式化15  * @JsonBackReference用了方式关系对象的递归访问16  */17 18 @Entity19 @Table(name = "user")20 public class User implements java.io.Serializable{21     @Id22     @GeneratedValue(strategy = GenerationType.IDENTITY)23     private Long id;24     private String name;25     @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")26     private Date createdate;27 28     @ManyToOne29     @JoinColumn(name = "did")30     @JsonBackReference31     private Department deparment;32 33     @ManyToMany(cascade = {}, fetch = FetchType.EAGER)34     @JoinTable(name = "user_role",35             joinColumns = {@JoinColumn(name = "user_id")},36             inverseJoinColumns = {@JoinColumn(name = "roles_id")})37     private List
     
       roles;38 39     public User() {40     }41 42     public Long getId() {43         return id;44     }45 46     public void setId(Long id) {47         this.id = id;48     }49 50     public String getName() {51         return name;52     }53 54     public void setName(String name) {55         this.name = name;56     }57 58     public Date getCreatedate() {59         return createdate;60     }61 62     public void setCreatedate(Date createdate) {63         this.createdate = createdate;64     }65 66     public Department getDeparment() {67         return deparment;68     }69 70     public void setDeparment(Department deparment) {71         this.deparment = deparment;72     }73 74     public List
      
        getRoles() {75         return roles;76     }77 78     public void setRoles(List
       
         roles) {79         this.roles = roles;80     }81 }

Role.java

1 package com.slp.entity; 2  3 import javax.persistence.*; 4 import java.io.Serializable; 5  6 /** 7  * Created by sangliping on 2017/8/18. 8  */ 9 import javax.persistence.*;10 11 @Entity12 @Table(name = "role")13 public class Role implements java.io.Serializable{14     @Id15     @GeneratedValue(strategy = GenerationType.IDENTITY)16     private Long id;17     private String name;18 19     public Role() {20     }21 22     public Long getId() {23         return id;24     }25 26     public void setId(Long id) {27         this.id = id;28     }29 30     public String getName() {31         return name;32     }33 34     public void setName(String name) {35         this.name = name;36     }37 }

Department.java

1 package com.slp.entity; 2  3  4 import javax.persistence.*; 5  6 /** 7  * Created by sangliping on 2017/8/18. 8  * @Table指定关联的数据库的表名 9  * @Id定义一条记录的唯一标识10  * @GeneratedValue设置为自动增长11  */12 13 @Entity14 @Table(name = "department")15 public class Department {16     @Id17     @GeneratedValue(strategy = GenerationType.IDENTITY)18     private Long id;19     private String name;20 21     public Department() {22     }23 24     public Long getId() {25         return id;26     }27 28     public void setId(Long id) {29         this.id = id;30     }31 32     public String getName() {33         return name;34     }35 36     public void setName(String name) {37         this.name = name;38     }39 }

三、使用JPA实体进行持久化

1、持久化实体

UserRepository.java

1 package com.slp.repository; 2  3 import com.slp.entity.User; 4 import org.springframework.data.jpa.repository.JpaRepository; 5 import org.springframework.stereotype.Repository; 6  7  8 /** 9  * Created by sangliping on 2017/8/18.10  * @Repository将该接口定义为一个资源库，使它能被其他程序引用，为其他程序提供存储数据的功能11  */12 @Repository13 public interface UserRepository extends JpaRepository
     
       {14 15 }

RoleRepository.java

1 package com.slp.repository; 2  3 import com.slp.entity.Role; 4 import org.springframework.data.jpa.repository.JpaRepository; 5 import org.springframework.stereotype.Repository; 6  7  8 /** 9  * Created by sangliping on 2017/8/18.10  */11 @Repository12 public interface RoleRepository extends JpaRepository
     
       {13 }

DepartmentRepository.java

1 package com.slp.repository; 2  3 import com.slp.entity.Department; 4 import org.springframework.data.jpa.repository.JpaRepository; 5 import org.springframework.stereotype.Repository; 6  7 /** 8  * Created by sangliping on 2017/8/18. 9  */10 @Repository11 public interface DepartmentRepository extends JpaRepository
     
       {12 }

使用以上的实现我们不用写任何一句Sql语句就可以执行一些基本的操作，这是因为JpaRepository实现了一些方法，JpaRepository继承与PagingAndSortingRepository它提供了扥也和排序的功能，PageAndSortingRepository又继承与CurdRepository它提供了最简单的增删改查操作。

比如JpaRepository的方法包括：

1 package org.springframework.data.jpa.repository; 2  3 import java.io.Serializable; 4 import java.util.List; 5 import org.springframework.data.domain.Example; 6 import org.springframework.data.domain.Sort; 7 import org.springframework.data.repository.NoRepositoryBean; 8 import org.springframework.data.repository.PagingAndSortingRepository; 9 import org.springframework.data.repository.query.QueryByExampleExecutor;10 11 @NoRepositoryBean12 public interface JpaRepository
     
       extends PagingAndSortingRepository
      
       , QueryByExampleExecutor
       
         {13     List
        
          findAll();14 15     List
         
           findAll(Sort var1);16 17     List
          
            findAll(Iterable
           
             var1);18 19 
             List save(Iterable var1);20 21 void flush();22 23  S saveAndFlush(S var1);24 25 void deleteInBatch(Iterable
                
                  var1);26 27 void deleteAllInBatch();28 29 T getOne(ID var1);30 31 
                  List findAll(Example var1);32 33  List findAll(Example var1, Sort var2);34 }

JPA定义声明方法的规则：

在接口中使用findBy、readBy、getBy作为方法名的前缀，拼接实体中类的属性字段（首字母大写）并可选择拼接一些SQL查询关键字来合成一个查询方法

1 And           findByIdAndName(Long id,String name); 2 Or            findByIdOrName(Long id,String name); 3 Between       findByCreatedateBetween(Date start,Date end); 4 LessThan      findByCreatedateLessThan(Date start); 5 GreaterThan   findByCreatedateGreaterThan(Date start); 6 IsNull        findByNameIsNull(); 7 IsNotNull     findByNameIsNotNull(); 8 NotNull       findByNameNotNull(); 9 Like          findByNameLike(String name);10 NotLike       findByNameNotLike(String name);11 orderBy       findByNameOrderByIdAsc(String name);12 Not           findByNameNot(String name);13 In            findByNameIn(Collection
     
       nameList);14 NotIn         findByNameNotIn(Collection
      
        nameList);

四、Spring Boot参数配置

1 spring.datasource.url=jdbc:mysql://localhost:3306/dev?characterEncoding=utf-8 2 spring.datasource.username=root 3 spring.datasource.driver-class-name=com.mysql.jdbc.Driver 4 spring.datasource.password=123456 5 spring.jpa.database=mysql 6 spring.jpa.show-sql=true 7 #配置此选项会自动检查表是否创建，如果没有创建则创建，如果已经创建则更新 8 spring.jpa.hibernate.ddl-auto=update 9 spring.jpa.hibernate.naming.strategy=org.hibernate.cfg.ImprovedNamingStrategy10 spring.jpa.propertie.hibernate.dialect=org.hibernate.dialect.MySQLDialect

五、创建JPA配置类

1 package com.slp.config; 2  3 import org.springframework.boot.autoconfigure.domain.EntityScan; 4 import org.springframework.context.annotation.Configuration; 5 import org.springframework.core.Ordered; 6 import org.springframework.core.annotation.Order; 7 import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor; 8 import org.springframework.data.jpa.repository.config.EnableJpaRepositories; 9 import org.springframework.transaction.annotation.EnableTransactionManagement;10 11 /**12  * Created by sangliping on 2017/8/18.13  * @EnableTransactionManagement启用了JPA的事务管理14  * @EnableJpaRepositories启用了JPA资源库并指定了定义的接口资源库的位置15  * @EntityScan指定了定义实体的位置16  */17 @Order(Ordered.HIGHEST_PRECEDENCE)18 @Configuration19 @EnableTransactionManagement(proxyTargetClass = true)20 @EnableJpaRepositories(basePackages = "com.slp.repository")21 @EntityScan(basePackages = "com.slp.entity")22 public class JpaConfiguration {23 24     PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor(){25         return new PersistenceExceptionTranslationPostProcessor();26     }27 }

六、测试准备

1、在pom.xml文件中加入测试依赖

1 
     
      2     
      
       org.springframework.boot
      3     
      
       spring-boot-starter-test
      4     
      
       test
      5

2、编写测试JPA配置类

1 package com.slp; 2  3 /** 4  * Created by sangliping on 2017/8/18. 5  */ 6 import org.springframework.context.annotation.Bean; 7 import org.springframework.context.annotation.Configuration; 8 import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor; 9 import org.springframework.data.jpa.repository.config.EnableJpaRepositories;10 import org.springframework.jdbc.datasource.DriverManagerDataSource;11 import org.springframework.orm.jpa.JpaTransactionManager;12 import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;13 import org.springframework.orm.jpa.vendor.Database;14 import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;15 import org.springframework.transaction.PlatformTransactionManager;16 import org.springframework.transaction.support.TransactionTemplate;17 18 import javax.sql.DataSource;19 import java.util.Properties;20 21 22 @Configuration23 @EnableJpaRepositories(basePackages = "com.slp.repository")24 public class JpaConfiguration {25 26     @Bean27     PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor() {28         return new PersistenceExceptionTranslationPostProcessor();29     }30 31     @Bean32     public DataSource dataSource() {33         DriverManagerDataSource dataSource = new DriverManagerDataSource();34         dataSource.setDriverClassName("com.mysql.jdbc.Driver");35         dataSource.setUrl("jdbc:mysql://localhost:3306/dev?characterEncoding=utf8");36         dataSource.setUsername("root");37         dataSource.setPassword("123456");38 39         return dataSource;40     }41 42     @Bean43     public LocalContainerEntityManagerFactoryBean entityManagerFactory() {44         LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();45         entityManagerFactoryBean.setDataSource(dataSource());46         entityManagerFactoryBean.setPackagesToScan("com.slp.entity");47         entityManagerFactoryBean.setJpaProperties(buildHibernateProperties());48         entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter() {
    {49             setDatabase(Database.MYSQL);50         }});51         return entityManagerFactoryBean;52     }53 54     protected Properties buildHibernateProperties() {55         Properties hibernateProperties = new Properties();56 57         hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");58         hibernateProperties.setProperty("hibernate.show_sql", "true");59         hibernateProperties.setProperty("hibernate.use_sql_comments", "false");60         hibernateProperties.setProperty("hibernate.format_sql", "true");61         hibernateProperties.setProperty("hibernate.hbm2ddl.auto", "update");62         hibernateProperties.setProperty("hibernate.generate_statistics", "false");63         hibernateProperties.setProperty("javax.persistence.validation.mode", "none");64         hibernateProperties.setProperty(" spring.jpa.hibernate.ddl-auto", "update");65         //Audit History flags66         hibernateProperties.setProperty("org.hibernate.envers.store_data_at_delete", "true");67         hibernateProperties.setProperty("org.hibernate.envers.global_with_modified_flag", "true");68 69         return hibernateProperties;70     }71 72     @Bean73     public PlatformTransactionManager transactionManager() {74         return new JpaTransactionManager();75     }76 77     @Bean78     public TransactionTemplate transactionTemplate() {79         return new TransactionTemplate(transactionManager());80     }81 82 }

3、编写测试类

1 package com.slp; 2 import com.slp.entity.Department; 3 import com.slp.entity.Role; 4 import com.slp.entity.User; 5 import com.slp.repository.DepartmentRepository; 6 import com.slp.repository.RoleRepository; 7 import com.slp.repository.UserRepository; 8 import org.junit.Before; 9 import org.junit.Test;10 import org.junit.runner.RunWith;11 import org.slf4j.Logger;12 import org.slf4j.LoggerFactory;13 import org.springframework.beans.factory.annotation.Autowired;14 import org.springframework.data.domain.Page;15 import org.springframework.data.domain.PageRequest;16 import org.springframework.data.domain.Pageable;17 import org.springframework.data.domain.Sort;18 import org.springframework.test.context.ContextConfiguration;19 import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;20 import org.springframework.util.Assert;21 22 import java.util.*;23 24 @RunWith(SpringJUnit4ClassRunner.class)25 @ContextConfiguration(classes = {JpaConfiguration.class})26 public class MySqlTest {27     private static Logger logger = LoggerFactory.getLogger(MySqlTest.class);28 29     @Autowired30     UserRepository userRepository;31     @Autowired32     DepartmentRepository departmentRepository;33     @Autowired34     RoleRepository roleRepository;35 36     @Before37     public void initData(){38         userRepository.deleteAll();39         roleRepository.deleteAll();40         departmentRepository.deleteAll();41 42         Department department = new Department();43         department.setName("开发部");44         departmentRepository.save(department);45         Assert.notNull(department.getId());46 47         Role role = new Role();48         role.setName("slp");49         roleRepository.save(role);50         Assert.notNull(role.getId());51 52         User user = new User();53         user.setName("user");54         user.setCreatedate(new Date());55         user.setDeparment(department);56 57         List
     
       roles = roleRepository.findAll();58         Assert.notNull(roles);59         user.setRoles(roles);60 61         userRepository.save(user);62         Assert.notNull(user.getId());63     }64 65     @Test66     public void findPage(){67         Pageable pageable = new PageRequest(0, 10, new Sort(Sort.Direction.ASC, "id"));68         Page
      
        page = userRepository.findAll(pageable);69         Assert.notNull(page);70         for(User user : page.getContent()) {71             logger.info("====user==== user name:{}, department name:{}, role name:{}",72                     user.getName(), user.getDeparment().getName(), user.getRoles().get(0).getName());73         }74     }75 76 }

注意：

1、不要忘记实体类上的Entity注解

2、不要忘记Repository上的Repository注解

3、测试Jpa配置文件位于test包下

4、如果之前创建了表之后名字变更会新建一个表这样再次执行的之后如果有主外键也会有冲突报错

转载于:https://www.cnblogs.com/dream-to-pku/p/7389882.html

你可能感兴趣的文章

FrameWork中SQLServer数据源使用宏函数出错解决办法