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

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

package com.slp.entity; import com.fasterxml.jackson.annotation.JsonBackReference;
import org.springframework.format.annotation.DateTimeFormat; import javax.persistence.*;
import java.util.Date;
import java.util.List; /**
* Created by sangliping on 2017/8/18.
* @ManyToOne定义User与Department的多对一关系，并用中间表user_role来存储他们的ID
* @DateTimeFormat日期进行格式化
* @JsonBackReference用了方式关系对象的递归访问
*/ @Entity
@Table(name = "user")
public class User implements java.io.Serializable{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createdate; @ManyToOne
@JoinColumn(name = "did")
@JsonBackReference
private Department deparment; @ManyToMany(cascade = {}, fetch = FetchType.EAGER)
@JoinTable(name = "user_role",
joinColumns = {@JoinColumn(name = "user_id")},
inverseJoinColumns = {@JoinColumn(name = "roles_id")})
private List<Role> roles; public User() {
} public Long getId() {
return id;
} public void setId(Long id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public Date getCreatedate() {
return createdate;
} public void setCreatedate(Date createdate) {
this.createdate = createdate;
} public Department getDeparment() {
return deparment;
} public void setDeparment(Department deparment) {
this.deparment = deparment;
} public List<Role> getRoles() {
return roles;
} public void setRoles(List<Role> roles) {
this.roles = roles;
}
}

package com.slp.entity; import javax.persistence.*;
import java.io.Serializable; /**
* Created by sangliping on 2017/8/18.
*/
import javax.persistence.*; @Entity
@Table(name = "role")
public class Role implements java.io.Serializable{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name; public Role() {
} public Long getId() {
return id;
} public void setId(Long id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
}
}

package com.slp.entity; import javax.persistence.*; /**
* Created by sangliping on 2017/8/18.
* @Table指定关联的数据库的表名
* @Id定义一条记录的唯一标识
* @GeneratedValue设置为自动增长
*/ @Entity
@Table(name = "department")
public class Department {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name; public Department() {
} public Long getId() {
return id;
} public void setId(Long id) {
this.id = id;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
}
}

package com.slp.repository; import com.slp.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository; /**
* Created by sangliping on 2017/8/18.
* @Repository将该接口定义为一个资源库，使它能被其他程序引用，为其他程序提供存储数据的功能
*/
@Repository
public interface UserRepository extends JpaRepository<User,Long> { }

package com.slp.repository; import com.slp.entity.Role;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository; /**
* Created by sangliping on 2017/8/18.
*/
@Repository
public interface RoleRepository extends JpaRepository<Role,Long> {
}

package com.slp.repository; import com.slp.entity.Department;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository; /**
* Created by sangliping on 2017/8/18.
*/
@Repository
public interface DepartmentRepository extends JpaRepository<Department,Long> {
}

package org.springframework.data.jpa.repository; import java.io.Serializable;
import java.util.List;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.QueryByExampleExecutor; @NoRepositoryBean
public interface JpaRepository<T, ID extends Serializable> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
List<T> findAll(); List<T> findAll(Sort var1); List<T> findAll(Iterable<ID> var1); <S extends T> List<S> save(Iterable<S> var1); void flush(); <S extends T> S saveAndFlush(S var1); void deleteInBatch(Iterable<T> var1); void deleteAllInBatch(); T getOne(ID var1); <S extends T> List<S> findAll(Example<S> var1); <S extends T> List<S> findAll(Example<S> var1, Sort var2);
}

And findByIdAndName(Long id,String name);
Or findByIdOrName(Long id,String name);
Between findByCreatedateBetween(Date start,Date end);
LessThan findByCreatedateLessThan(Date start);
GreaterThan findByCreatedateGreaterThan(Date start);
IsNull findByNameIsNull();
IsNotNull findByNameIsNotNull();
NotNull findByNameNotNull();
Like findByNameLike(String name);
NotLike findByNameNotLike(String name);
orderBy findByNameOrderByIdAsc(String name);
Not findByNameNot(String name);
In findByNameIn(Collection<String> nameList);
NotIn findByNameNotIn(Collection<String> nameList);

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

package com.slp.config; import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement; /**
* Created by sangliping on 2017/8/18.
* @EnableTransactionManagement启用了JPA的事务管理
* @EnableJpaRepositories启用了JPA资源库并指定了定义的接口资源库的位置
* @EntityScan指定了定义实体的位置
*/
@Order(Ordered.HIGHEST_PRECEDENCE)
@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@EnableJpaRepositories(basePackages = "com.slp.repository")
@EntityScan(basePackages = "com.slp.entity")
public class JpaConfiguration { PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor(){
return new PersistenceExceptionTranslationPostProcessor();
}
}

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

package com.slp; /**
* Created by sangliping on 2017/8/18.
*/
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.support.TransactionTemplate; import javax.sql.DataSource;
import java.util.Properties; @Configuration
@EnableJpaRepositories(basePackages = "com.slp.repository")
public class JpaConfiguration { @Bean
PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor() {
return new PersistenceExceptionTranslationPostProcessor();
} @Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/dev?characterEncoding=utf8");
dataSource.setUsername("root");
dataSource.setPassword("123456"); return dataSource;
} @Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
entityManagerFactoryBean.setDataSource(dataSource());
entityManagerFactoryBean.setPackagesToScan("com.slp.entity");
entityManagerFactoryBean.setJpaProperties(buildHibernateProperties());
entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter() {{
setDatabase(Database.MYSQL);
}});
return entityManagerFactoryBean;
} protected Properties buildHibernateProperties() {
Properties hibernateProperties = new Properties(); hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
hibernateProperties.setProperty("hibernate.show_sql", "true");
hibernateProperties.setProperty("hibernate.use_sql_comments", "false");
hibernateProperties.setProperty("hibernate.format_sql", "true");
hibernateProperties.setProperty("hibernate.hbm2ddl.auto", "update");
hibernateProperties.setProperty("hibernate.generate_statistics", "false");
hibernateProperties.setProperty("javax.persistence.validation.mode", "none");
hibernateProperties.setProperty(" spring.jpa.hibernate.ddl-auto", "update");
//Audit History flags
hibernateProperties.setProperty("org.hibernate.envers.store_data_at_delete", "true");
hibernateProperties.setProperty("org.hibernate.envers.global_with_modified_flag", "true"); return hibernateProperties;
} @Bean
public PlatformTransactionManager transactionManager() {
return new JpaTransactionManager();
} @Bean
public TransactionTemplate transactionTemplate() {
return new TransactionTemplate(transactionManager());
} }

package com.slp;
import com.slp.entity.Department;
import com.slp.entity.Role;
import com.slp.entity.User;
import com.slp.repository.DepartmentRepository;
import com.slp.repository.RoleRepository;
import com.slp.repository.UserRepository;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.util.Assert; import java.util.*; @RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {JpaConfiguration.class})
public class MySqlTest {
private static Logger logger = LoggerFactory.getLogger(MySqlTest.class); @Autowired
UserRepository userRepository;
@Autowired
DepartmentRepository departmentRepository;
@Autowired
RoleRepository roleRepository; @Before
public void initData(){
userRepository.deleteAll();
roleRepository.deleteAll();
departmentRepository.deleteAll(); Department department = new Department();
department.setName("开发部");
departmentRepository.save(department);
Assert.notNull(department.getId()); Role role = new Role();
role.setName("slp");
roleRepository.save(role);
Assert.notNull(role.getId()); User user = new User();
user.setName("user");
user.setCreatedate(new Date());
user.setDeparment(department); List<Role> roles = roleRepository.findAll();
Assert.notNull(roles);
user.setRoles(roles); userRepository.save(user);
Assert.notNull(user.getId());
} @Test
public void findPage(){
Pageable pageable = new PageRequest(0, 10, new Sort(Sort.Direction.ASC, "id"));
Page<User> page = userRepository.findAll(pageable);
Assert.notNull(page);
for(User user : page.getContent()) {
logger.info("====user==== user name:{}, department name:{}, role name:{}",
user.getName(), user.getDeparment().getName(), user.getRoles().get(0).getName());
}
} }