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

怼烎@ 2022-10-26 12:55 50阅读 0赞

对于传统关系型数据库来说，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

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 Listroles;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 ListgetRoles() \{75 return roles;76 \}77

78 public void setRoles(Listroles) \{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

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

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

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 ListfindAll();14

15 ListfindAll(Sort var1);16

17 List findAll(Iterablevar1);18

19 List save(Iterablevar1);20

21 void flush();22

23 S saveAndFlush(S var1);24

25 void deleteInBatch(Iterablevar1);26

27 void deleteAllInBatch();28

29 T getOne(ID var1);30

31 List findAll(Examplevar1);32

33 List findAll(Examplevar1, 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(CollectionnameList);14 NotIn findByNameNotIn(Collection nameList);

四、Spring Boot参数配置

1 spring.datasource.url=jdbc:mysql://localhost:3306/dev?characterEncoding=utf-82 spring.datasource.username=root3 spring.datasource.driver-class-name=com.mysql.jdbc.Driver4 spring.datasource.password=1234565 spring.jpa.database=mysql6 spring.jpa.show-sql=true7 \#配置此选项会自动检查表是否创建，如果没有创建则创建，如果已经创建则更新8 spring.jpa.hibernate.ddl-auto=update9 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文件中加入测试依赖

2 org.springframework.boot

3 spring-boot-starter-test

4 test

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

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 Listroles = 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 Pagepage = 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、如果之前创建了表之后名字变更会新建一个表这样再次执行的之后如果有主外键也会有冲突报错