目录
多数据源@PersistenceContext VS @Autowired
JPA 使用AbstractBaseEntity并设置修改时间修改人等自动填充
JPA查询方式
JPA返回没有数据时，List为空[]，不是null；
方法约定形式查询：
List<Student> findByDescription(String description);
或者@Query查询，
（@Query(value = "select t1 from Student t1 where t1.description = ?1 ")）
List<Student> findStudentInfo(String description)
或者原生@Query(value = "xxxxx", nativeQuery = true)；
@Query(value = "select t1.* from student t1 where t1.description = ?1 ", nativeQuery = true)
List<Student> queryForDescription(String description);
Page查询，参数in，查询返回指定DTO demo
@Query("select new com.test.dto.StudentDTO(s.id, s.name, s.endTime) from com.test.entity.StudentEntity s where s.status = :status and s.startTime >= :searchDate and s.id in (:ids)")
Page<ScheduleDTO> findDemoDTO(Pageable page, @Param(value = "status") int status , @Param(value = "searchDate") Date searchDate ,@Param(value = "ids") List<String> ids);
Example查询实例
Group group = new Group();
group.setGroupName(groupName);
group.setRuleName(ruleName);
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("groupName", match -> match.contains()) // Fuzzy query match start: {groupName}%
.withMatcher("ruleName", match -> match.startsWith());
return groupRepository.findAll(Example.of(group, matcher));
JPA传入参数null和""区别
如果传入参数：
null时，返回结果[]；
""时，会当成空条件处理，返回结果将查询数据库中description是空的数据行；
JPA Specification
关联查询
// 暂时没有试过
aRepository.findAll((Root<A> aRoot, CriteriaQuery<?> cq, CriteriaBuilder cb) -> {
Root<B> bRoot = cq.from(B.class);
// Root<C> cRoot = cq.from(C.class); 也可以加C表，D表…… 然后再cb中加关联条件就可以了。
// Join<A, B> join = A.join("b"); 可以用join方式联合查询，但是需要A中有B的实体。
List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(aRoot.get("id"), bRoot.get("aId"))); // AB两表的关联条件，就是sql join 中的on条件
predicates.add(cb.notEqual(aRoot.get("XXX"), XXX));
cq.distinct(true); // 去重 如果多对多
return cb.and(predicates.toArray(new Predicate[predicates.size()]));
} , pageable);使用函数查询
Specification<AuditLog> specification = new Specification<AuditLog>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<AuditLog> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<Predicate>();
if (StringUtils.isNotBlank(ruleSetVersion)) {
predicates.add(cb.equal(root.<String>get("ruleSetVersion"), ruleSetVersion));
}
if (StringUtils.isNotBlank(submitDate)) {
// 调用Oracle方法，第一个参数是方法名称，第二个是返回数据类型,后续的是参数,可以多个
Expression<String> function =
cb.function("DATE_FORMAT", String.class, root.get("submitDate"), cb.literal("%Y-%m-%d"));
predicates.add(cb.equal(function, submitDate));
}
return cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
};使用like
Specification<Group> specification = new Specification<Group>() {
private static final long serialVersionUID = 1L;
@Override
public Predicate toPredicate(Root<Group> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(cb.like(root.<String>get("groupName"), "%" + groupName + "%"));
predicates.add(cb.like(root.<String>get("ruleName"), "%" + ruleName + "%"));
return cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
};
return groupRepository.findAll(specification);JPA createNativeQuery
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.PageRequest;
public abstract class AbstractCreateQueryService {
private final Logger log = LoggerFactory.getLogger(this.getClass());
protected Query createNativeQueryWithParameters(EntityManager entityManager, String sql,
Map<String, Object> parameters) {
return createNativeQueryWithParameters(entityManager, sql, "", parameters, null);
}
protected Query createNativeQueryWithParameters(EntityManager entityManager, String sql, String customMapping,
Map<String, Object> parameters, PageRequest page) {
// Create native query.
Query query = StringUtils.isNotBlank(customMapping) ? entityManager.createNativeQuery(sql, customMapping)
: entityManager.createNativeQuery(sql.toString());
// Set parameters
for (Map.Entry<String, Object> parameter : parameters.entrySet()) {
query.setParameter(parameter.getKey(), parameter.getValue());
}
if (page != null) {
int pageNum = page.getPageNumber();
int size = page.getPageSize();
query.setFirstResult(pageNum * size);
query.setMaxResults(size);
log.debug("Page no: {}, page size: {}", pageNum, size);
}
return query;
}
protected Query createNativeQueryWithParameters(EntityManager entityManager, String sql, Class<?> resultClass,
Map<String, Object> parameters, PageRequest page) {
Query query = resultClass != null ? entityManager.createNativeQuery(sql, resultClass)
: entityManager.createNativeQuery(sql);
// Set parameters
for (Map.Entry<String, Object> parameter : parameters.entrySet()) {
query.setParameter(parameter.getKey(), parameter.getValue());
}
if (page != null) {
int pageNum = page.getPageNumber();
int size = page.getPageSize();
query.setFirstResult(pageNum * size);
query.setMaxResults(size);
log.debug("Page no: {}, page size: {}", pageNum, size);
}
return query;
}
}JPA SqlResultSetMapping
@SqlResultSetMapping(
name="studentRespMapping",
classes={
@ConstructorResult(
targetClass=StudentDTO.class,
columns={
@ColumnResult(name="id", type = Long.class),
@ColumnResult(name="name"),
@ColumnResult(name="age", type = Integer.class),
@ColumnResult(name="create_by"),
@ColumnResult(name="create_time", type = LocalDateTime.class)
}
)
}
)
@Data
@Entity
@Table(name = "ums_student")
public class Student extends BaseEntity implements Serializable {
private String name;
private Integer age;
}Java Plain JDBC数据库操作实例
有些情况下，我们并不需要更多的数据源交由spring data jpa等这样的多数据源配置管理，但我们又需要在另外的库中进行很少的操作，这种情况为了避免浪费CPU资源浪费（多数据源配置是需要线程池等资源管理的），可以使用JDBC DriverManager实现：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import com.leosong.simulate.Student;
@Component
public class StudentSchemaSupport {
private final Logger log = LoggerFactory.getLogger(this.getClass());
// batch insert per records execute
private static final int BATCH_PER_COUNT = 1000;
@Value("${spring.datasource.third.url}")
private String dbUrl;
@Value("${spring.datasource.third.username}")
private String username;
@Value("${spring.datasource.third.password}")
private String password;
/**
* Create DB using the given databaseName.
*
* @param databaseName
* @throws SQLException
*/
public void createDatabase(String databaseName) throws SQLException {
// Open a connection
try (Connection conn = DriverManager.getConnection(dbUrl, username, password);
Statement stmt = conn.createStatement();) {
StringBuilder sql = new StringBuilder("CREATE DATABASE IF NOT EXISTS `").append(databaseName)
.append("` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;");
stmt.executeUpdate(sql.toString());
log.info("Created DATABASE: [{}]...", databaseName);
} catch (SQLException e) {
throw e;
}
}
/**
* create table: ums_student in specified DB
*
* @param databaseName
* @throws SQLException
*/
public void createTableStudent(String databaseName) throws SQLException {
// enable batch
String dbUrlNew = dbUrl.replace("old_database", databaseName);
try (Connection conn = DriverManager.getConnection(dbUrlNew, username, password);
Statement stmt = conn.createStatement();) {
stmt.executeUpdate(studentTableSql().toString());
log.info("Created TABLE: {}.[ums_student]...", databaseName);
} catch (SQLException e) {
throw e;
}
}
/**
* Insert records
*
* @param databaseName
* @param records
* @throws SQLException
*/
public void insertRecordsStudentData(String databaseName, List<Student> studentDataList)
throws SQLException {
String dbUrlNew = dbUrl.replace("old_database", databaseName) + "&rewriteBatchedStatements=true";
String sql =
"INSERT INTO ums_student (id,name,age,create_date) VALUES(?, ?, ?, ?, )";
try (Connection conn = DriverManager.getConnection(dbUrlNew, username, password);
PreparedStatement preStmt = conn.prepareStatement(sql);) {
int count = 0;
for (Student data : studentDataList) {
count++;
preStmt.setLong(1, data.getId());
preStmt.setString(2, data.getName());
preStmt.setString(3, data.getAge());
preStmt.setObject(7, data.getCreateDate());
preStmt.addBatch();
if (count % BATCH_PER_COUNT == 0) {
preStmt.executeBatch();
log.info("Batch insert record, count: [{}]", count);
}
}
// insert remaining records
preStmt.executeBatch();
log.info("Batch insert record, count: [{}]", count);
log.info("Inserted into TABLE: {}.[ums_student]...", databaseName);
} catch (SQLException e) {
throw e;
}
}
protected static String studentTableSql() {
StringBuilder sql = new StringBuilder();
sql.append("CREATE TABLE `ums_student` (\n");
sql.append(" `id` bigint unsigned NOT NULL AUTO_INCREMENT,\n");
sql.append(" `name` varchar(100) NOT NULL,\n");
sql.append(" `age` int NOT NULL,\n");
sql.append(" `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,\n");
sql.append(" PRIMARY KEY (`id`)\n");
sql.append(") ENGINE=InnoDB AUTO_INCREMENT=27937 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n");
return sql.toString();
}
}
多数据源实体到数据库命名自动映射失效配置
多数据源配置，解决entity实体对象驼峰命名自动映射数据库蛇形命名失效配置：
import java.util.HashMap;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy;
import org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactorySecond",
transactionManagerRef = "transactionManagerSecond",
basePackages = {"com.leosong.simulate.repository"})
public class SecondDataSourceConfig {
@Autowired
@Qualifier("secondDataSource")
private DataSource secondDataSource;
@Autowired
private JpaProperties jpaProperties;
@Bean(name = "entityManagerSecond")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactoryBean(builder).getObject().createEntityManager();
}
@Bean(name = "entityManagerFactorySecond")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(secondDataSource)
.properties(getProperties())
.packages("com.leosong.simulate.domain")
.persistenceUnit("secondPersistentUnit")
.build();
}
public Map<String, Object> getProperties() {
Map<String, Object> props = new HashMap<>();
props.put("format_sql", "true");
props.put("max_fetch_depth", "1");
// 重点在这里，加上解决命名自动映射问题
props.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
return props;
}
@Bean(name = "transactionManagerSecond")
public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryBean(builder).getObject());
}
}多数据源@PersistenceContext VS @Autowired
@PersistenceContext(unitName = "secondPersistentUnit")
private EntityManager entityManager;VS
@Autowired
private EntityManager entityManager;
You shouldn't use @Autowired. @PersistenceContext takes care to create a unique EntityManager for every thread.
In a production application you can have multiple clients calling your application in the same time.
For each call, the application creates a thread. Each thread should use its own EntityManager.
Imagine what would happen if they share the same EntityManager: different users would access the same entities.
EntityManager不是线程安全的，当多个请求进来的时候，spring会创建多个线程，而@PersistenceContext就是用来为每个线程创建一个EntityManager的，而@Autowired就只创建了一个。JPA
使用jpa做数据持久化，我们需要实体管理器EntityManager，而EntityManager需要实体管理器工厂类EntityManagerFactory，而实体管理器工厂类EntityManagerFactory需要数据源DataSource，
另外要做事务管理，需要JpaTransactionManager，它也需要数据源，整个配置还是我们在做xml配置的思路一样。然后使用创建原生sql：
entityManager.createNativeQuery(sql, customMapping);
多数据源开启指定事务
在多数据源的环境下，如果需要开启事务，需要指定事务管理器的名称，不然系统会自动使用
@Primary注解的事务管理器。
@Transactional(transactionManager="transactionManagerSec")JPA 使用AbstractBaseEntity并设置修改时间修改人等自动填充
在开发中一般实体都有相同的字段，比如ID，创建时间，修改时间，修改人等，不用每个实体都写上，我们可以定一个公共实体，然后具体类继承这个公共实体：
公共实体
import java.time.LocalDateTime;
import javax.persistence.Column;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import com.leosong.simulate.consts.DrsConstant;
@Data
@MappedSuperclass
// 设置自动插入
@EntityListeners(value = AuditingEntityListener.class)
public abstract class AbstractBaseEntity {
/**
* primary key
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", insertable = false, nullable = false)
private Long id;
/**
* create time
*/
@JsonFormat(pattern = DrsConstant.SDT_DATETIME)
@CreatedDate
@Column(nullable = false, updatable = false)
private LocalDateTime createTime;
/**
* update time
*/
@JsonFormat(pattern = DrsConstant.SDT_DATETIME)
@LastModifiedDate
private LocalDateTime updateTime;
}自动审计设置
AuditingEntityListener对实体的创建时间、更新时间进行自动审计
1. 启动类启动@EnableJpaAuditing
2. AbstractBaseEntity 加上注解@EntityListeners(value = AuditingEntityListener.class)
3. 对应的字段使用 @CreatedDate 或者 @LastModifiedDate
@EntityListeners(AuditingEntityListener.class)这个注解才能使@CreatedDate@LastModifiedDate生效
设置createBy：
1. @CreatedBy
2. 配置CustomAuditorAware
@CreatedBy@LastModifiedBy
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.domain.AuditorAware;
import com.leosong.util.UserContext;
@Configuration
public class CustomAuditorAware implements AuditorAware<String> {
@Autowired
protected UserContext userContext;
@Override
public Optional<String> getCurrentAuditor() {
String currentUserName = userContext.getUsername();
return Optional.ofNullable(currentUserName);
}
}###############
记住, JDBC的性能最大的增进是减少JDBC驱动与数据库之间的网络通讯次数;
end.
752
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
