关于 Spring For All

关于 Spring For All

Spring For All 的一切
最新动态

最新动态

Spring 5 会是咋样呢
Spring Boot

Spring Boot

快速构建并运行 Spring 应用程序
Spring Cloud

Spring Cloud

分布式系统的一套工具,可用于构建微服务
Spring Framework

Spring Framework

提供依赖注入、事务、Web应用、数据访问等模块
Spring Data

Spring Data

提供一致性数据访问模块
Spring Security

Spring Security

提供应用身份验证和授权支持
Spring Batch

Spring Batch

提供高容批处理操作模块
Spring AMQP

Spring AMQP

基于AMQP消息解决方案
Micro Service Arch.

Micro Service Arch.

微服务架构相关
开源项目及视频教程

开源项目及视频教程

做中国最好的 Spring 开源项目及视频教程
小马哥专栏

小马哥专栏

阿里技术布道者,专注 Spring Boot 及微服务

Spring学习(一): 使用AbstractRoutingDataSource类进行多数据源配置

zane 发表了文章 • 0 个评论 • 169 次浏览 • 2017-09-12 12:33 • 来自相关话题

多数据源在项目中很常见,举几个需求,如页面展示的数据需要从不同的数据库中去查询,修改等,例如数据库的读写分离等

配置多数据源要考虑到如下的情况
单个数据源是如下这种情况:

如果是多个数据源的话,将会变成下面这种情况



这样,每增加一个数据源就要去配置一个sessionFactory,这样比较麻烦,于是spring的
AbstractRoutingDataSource类产生了


它的实现原理是扩展Spring的`AbstractRoutingDataSource`抽象类(该类充当了`DataSource`的路由中介, 能在运行时, 根据某种key值来动态切换到真正的`DataSource`上。)
 从AbstractRoutingDataSource的源码中:$(document).ready(function() {$('pre code').each(function(i, block) { hljs.highlightBlock( block); }); });public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean

我们可以看到,它继承了AbstractDataSource,而AbstractDataSource不就是javax.sql.DataSource的子类,So我们可以分析下它的getConnection方法:public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}

public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}

获取连接的方法中,重点是determineTargetDataSource()方法,看源码:protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}上面这段源码的重点在于determineCurrentLookupKey()方法,这是AbstractRoutingDataSource类中的一个抽象方法,而它的返回值是你所要用的数据源dataSource的key值,有了这个key值,resolvedDataSource(这是个map,由配置文件中设置好后存入的)就从中取出对应的DataSource,如果找不到,就用配置默认的数据源。
看完源码,应该有点启发了吧,没错!你要扩展AbstractRoutingDataSource类,并重写其中的determineCurrentLookupKey()方法,来实现数据源的切换:

接下来,将展示一个多数据源配置demo 

1.配置一个config类,配置3个数据源(下面是JAVA配置写法)/**
* Created by Youjie on 2017/7/23.
*/
@Configuration
@EnableTransactionManagement
public class DataSourceConfig {

@Bean
@ConfigurationProperties(prefix = "datasource.db_a")
@Qualifier("dataSourceDefault")
public DataSource dataSourceDefault(){
return new DruidDataSource();
}

@Bean
@ConfigurationProperties(prefix = "datasource.db_b")
@Qualifier("dataSourceB")
public DataSource dataSourceB(){
return new DruidDataSource();
}

@Bean
@ConfigurationProperties(prefix = "datasource.db_c")
@Qualifier("dataSourceC")
public DataSource dataSourceC(){
return new DruidDataSource();
}


@Bean
@Primary
public DataSource multipleDataSource(){
DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
dynamicDataSource.setDefaultTargetDataSource(dataSourceDefault());

Map<Object,Object> dataSourcesType = new HashMap();
dataSourcesType.put(DataSources.DATASOURCE_A,dataSourceDefault());
dataSourcesType.put(DataSources.DATASOURCE_B,dataSourceB());
dataSourcesType.put(DataSources.DATASOURCE_C,dataSourceC());

dynamicDataSource.setTargetDataSources(dataSourcesType);

return dynamicDataSource;
}



@Bean
public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(multipleDataSource());
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.may.model");
sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("configuration.xml"));
return sqlSessionFactoryBean.getObject();
}

@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(multipleDataSource());
}



}对应的 application.properties如下server.port=9002
server.context-path=/


#A user
datasource.db_a.driver-class-name=com.mysql.jdbc.Driver
datasource.db_a.url=jdbc:mysql://127.0.0.1:3306/test_A?autoReconnect=true&useUnicode=true&characterEncoding=utf8&logSlowQueries=false&slowQueryThresholdMillis=0&allowMultiQueries=true
datasource.db_a.username=root
datasource.db_a.password=123456

#B city
datasource.db_b.driver-class-name=com.mysql.jdbc.Driver
datasource.db_b.url=jdbc:mysql://127.0.0.1:3306/test_B?autoReconnect=true&useUnicode=true&characterEncoding=utf8&logSlowQueries=false&slowQueryThresholdMillis=0&allowMultiQueries=true
datasource.db_b.username=root
datasource.db_b.password=123456

#C role
datasource.db_c.driver-class-name=com.mysql.jdbc.Driver
datasource.db_c.url=jdbc:mysql://127.0.0.1:3306/test_C?autoReconnect=true&useUnicode=true&characterEncoding=utf8&logSlowQueries=false&slowQueryThresholdMillis=0&allowMultiQueries=true
datasource.db_c.username=root
datasource.db_c.password=123456

2、写一个ThreadLocalDynamicDataSource类继承AbstractRoutingDataSource,并实现determineCurrentLookupKey方法public class ThreadLocalRountingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceTypeManager.get();
}
}3、编写DataSourceTypeManager执行类,并利用ThreadLocal以空间换时间的方式解决线程安全问题public class DataSourceTypeManager {
private static final ThreadLocal<DataSources> dataSourceTypes = new ThreadLocal<DataSources>() {
@Override
protected DataSources initialValue() {
return DataSources.DATASOURCE_DEFAULT;
}
};

public static DataSources get() {
return dataSourceTypes.get();
}

public static void set(DataSources dataSourceType) {
dataSourceTypes.set(dataSourceType);
}

public static void reset() {
dataSourceTypes.set(DataSources.DATASOURCE_DEFAULT);
}

public static void clearDataSources () {
dataSourceTypes.remove();
}
}




上面代码利用initialValue方法,调用初始化时指定的DataSources 类型,这里DataSources 为枚举类型,里面定义三种数据源的名称,代码如下:public enum DataSources {
DATASOURCE,DATASOURCE_DEFAULT,DATASOURCE_ORACLE
}
4、multipleDataSource多数据源配置的xml配置<bean id="multipleDataSource"
class="com.hxqc.basic.dependency.datasource.ThreadLocalRountingDataSource">
<property name="defaultTargetDataSource" ref="ncDataSource"/>
<property name="targetDataSources">
<map key-type="com.hxqc.basic.dependency.datasource.DataSources">
<entry key="DATASOURCE" value-ref="erpDataSource"/>
<entry key="DATASOURCE_DEFAULT" value-ref="ncDataSource"/>
<entry key="DATASOURCE_ORACLE" value-ref="oracleDataSource"/>
</map>
</property>
</bean>



multipleDataSource多数据源配置的Java配置(上文已经贴出该配置,这里再强调一下)@Bean
@Primary
public DataSource multipleDataSource(){
DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
dynamicDataSource.setDefaultTargetDataSource(dataSourceDefault());

Map<Object,Object> dataSourcesType = new HashMap();
dataSourcesType.put(DataSources.DATASOURCE_A,dataSourceDefault());
dataSourcesType.put(DataSources.DATASOURCE_B,dataSourceB());
dataSourcesType.put(DataSources.DATASOURCE_C,dataSourceC());

dynamicDataSource.setTargetDataSources(dataSourcesType);

return dynamicDataSource;
}
5.在DAOImpl中切换数据源 或者配置aop切面根据不同的包下面的数据,采用不同的数据源



1,在Dao层直接切换数据源,如切换到默认数据源DataSourceTypeManager.set(DataSources.DATASOURCE_DEFAULT)
2.根据aop进行动态切换
aop xml配置<bean id="dataSourceInterceptor" class="com.hxqc.basic.dependency.interceptor.DataSourceInterceptor"/>
<aop:config>
<aop:aspect id="dataSourceAspect" ref="dataSourceInterceptor">
<aop:pointcut id="datasource" expression="execution(* com.hxqc.data.gather.core.erp.*.mapper..*.*(..))"/>
<aop:pointcut id="datasource_oracle" expression="execution(* com.hxqc.data.gather.core.oracle.*.mapper..*.*(..))"/>
<aop:pointcut id="datasource_default" expression="execution(* com.hxqc.data.gather.core.nc.*.mapper..*.*(..))"/>
<aop:before method="setDataSource" pointcut-ref="datasource"/>
<aop:before method="setDataSourceDefault" pointcut-ref="datasource_default"/>
<aop:before method="setDataSourceOracle" pointcut-ref="datasource_oracle"/>
</aop:aspect>
</aop:config>aop java 配置@Aspect
@Component
public class DataSourceAspect {

private Logger log = LoggerFactory.getLogger(getClass());

@Pointcut("execution(* com.may.dataA.mapper.*.*(..))")
public void pointCutA(){}

@Pointcut("execution(* com.may.dataB.mapper.*.*(..))")
public void pointCutB(){}

@Pointcut("execution(* com.may.dataC.mapper.*.*(..))")
public void pointCutC(){}
@Pointcut("execution(* com.may..*.*(..))")
public void annotation(){}



public void setDataSourceDefault(JoinPoint jp){
DataSourceManager.set(DataSources.DATASOURCE_A);
log.info("切换数据源","切换到默认数据源A");

}
public void setDataSourceB(JoinPoint jp){
DataSourceManager.set(DataSources.DATASOURCE_B);
log.info("切换数据源","切换到数据源B");
}

public void setDataSourceC(JoinPoint jp){
DataSourceManager.set(DataSources.DATASOURCE_C);
log.info("切换数据源","切换到数据源C");
}

@Before("@annotation(ds)")
public void setAnnotationDataSource(JoinPoint jp,DataSource ds){
// 获取当前的指定的数据源;
String dsId = ds.value();
// 如果不在我们注入的所有的数据源范围之内,那么输出警告信息,系统自动使用默认的数据源。
/*if (!DataSourceManager.containsDataSource(dsId)) {

log.error("数据源[{}]不存在,使用默认数据源 > {}", ds.value(), jp.getSignature());

} else {

log.debug("Use DataSource : {} > {}", ds.value(), jp.getSignature());

// 找到的话,那么设置到动态数据源上下文中。
DynamicDataSourceContextHolder.setDataSourceType(ds.name());
}*/

//将string值转换为enum
DataSourceManager.set(Enum.valueOf(DataSources.class,ds.value()));

log.info("切换数据源"+ds.value(),"切换到默认数据源"+ds.value());
}



}
解释,这里定义三个切面,分别对应三个数据源,如在oracle包下的,动态切换到oracle数据源,上面配置的最后部分是用到了自定义注解DataSource注解,该注解在方法上,通过切面来进行数据源的切换,具体看源码

然后配置切面类/**
*
*
* @author maple
*
*/
public class DataSourceInterceptor {

public void setDataSourceDefault(JoinPoint jp){
DataSourceTypeManager.set(DataSources.DATASOURCE_DEFAULT);
LogU.i("切换数据源","切换到NC数据源");
System.out.println(jp.getTarget().getClass().getSimpleName());
}

public void setDataSource(JoinPoint jp){
DataSourceTypeManager.set(DataSources.DATASOURCE);
LogU.i("切换数据源","切换到ERP数据源");
}

public void setDataSourceOracle(JoinPoint jp){
DataSourceTypeManager.set(DataSources.DATASOURCE_ORACLE);
LogU.i("切换数据源","切换到Oracle数据源");
}
}





`问题:`多数据源切换是成功了,但牵涉到事务呢?单数据源事务是ok的,但如果多数据源需要同时使用一个事务呢?这个问题有点头大,网络上有人提出用atomikos开源项目实现JTA分布式事务处理。你怎么看?

注意:

本文源码放在码云上,注意,源码将XML配置的内容都改成了JAVA配置,项目是基于Spring-boot,也提供了自定义注解来改变动态数据源!

本文源码:http://git.oschina.net/youjie1/may-dynamic-datasource
原文

  查看全部


多数据源在项目中很常见,举几个需求,如页面展示的数据需要从不同的数据库中去查询,修改等,例如数据库的读写分离等


配置多数据源要考虑到如下的情况
单个数据源是如下这种情况:

如果是多个数据源的话,将会变成下面这种情况



这样,每增加一个数据源就要去配置一个sessionFactory,这样比较麻烦,于是spring的
AbstractRoutingDataSource类产生了


它的实现原理是扩展Spring的`AbstractRoutingDataSource`抽象类(该类充当了`DataSource`的路由中介, 能在运行时, 根据某种key值来动态切换到真正的`DataSource`上。)
 从AbstractRoutingDataSource的源码中:
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean


我们可以看到,它继承了AbstractDataSource,而AbstractDataSource不就是javax.sql.DataSource的子类,So我们可以分析下它的getConnection方法:
public Connection getConnection() throws SQLException {  
return determineTargetDataSource().getConnection();
}

public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}


获取连接的方法中,重点是determineTargetDataSource()方法,看源码:
protected DataSource determineTargetDataSource() {  
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
上面这段源码的重点在于determineCurrentLookupKey()方法,这是AbstractRoutingDataSource类中的一个抽象方法,而它的返回值是你所要用的数据源dataSource的key值,有了这个key值,resolvedDataSource(这是个map,由配置文件中设置好后存入的)就从中取出对应的DataSource,如果找不到,就用配置默认的数据源。
看完源码,应该有点启发了吧,没错!你要扩展AbstractRoutingDataSource类,并重写其中的determineCurrentLookupKey()方法,来实现数据源的切换:

接下来,将展示一个多数据源配置demo 

1.配置一个config类,配置3个数据源(下面是JAVA配置写法)
/**
* Created by Youjie on 2017/7/23.
*/
@Configuration
@EnableTransactionManagement
public class DataSourceConfig {

@Bean
@ConfigurationProperties(prefix = "datasource.db_a")
@Qualifier("dataSourceDefault")
public DataSource dataSourceDefault(){
return new DruidDataSource();
}

@Bean
@ConfigurationProperties(prefix = "datasource.db_b")
@Qualifier("dataSourceB")
public DataSource dataSourceB(){
return new DruidDataSource();
}

@Bean
@ConfigurationProperties(prefix = "datasource.db_c")
@Qualifier("dataSourceC")
public DataSource dataSourceC(){
return new DruidDataSource();
}


@Bean
@Primary
public DataSource multipleDataSource(){
DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
dynamicDataSource.setDefaultTargetDataSource(dataSourceDefault());

Map<Object,Object> dataSourcesType = new HashMap();
dataSourcesType.put(DataSources.DATASOURCE_A,dataSourceDefault());
dataSourcesType.put(DataSources.DATASOURCE_B,dataSourceB());
dataSourcesType.put(DataSources.DATASOURCE_C,dataSourceC());

dynamicDataSource.setTargetDataSources(dataSourcesType);

return dynamicDataSource;
}



@Bean
public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(multipleDataSource());
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.may.model");
sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("configuration.xml"));
return sqlSessionFactoryBean.getObject();
}

@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(multipleDataSource());
}



}
对应的 application.properties如下
server.port=9002
server.context-path=/


#A user
datasource.db_a.driver-class-name=com.mysql.jdbc.Driver
datasource.db_a.url=jdbc:mysql://127.0.0.1:3306/test_A?autoReconnect=true&useUnicode=true&characterEncoding=utf8&logSlowQueries=false&slowQueryThresholdMillis=0&allowMultiQueries=true
datasource.db_a.username=root
datasource.db_a.password=123456

#B city
datasource.db_b.driver-class-name=com.mysql.jdbc.Driver
datasource.db_b.url=jdbc:mysql://127.0.0.1:3306/test_B?autoReconnect=true&useUnicode=true&characterEncoding=utf8&logSlowQueries=false&slowQueryThresholdMillis=0&allowMultiQueries=true
datasource.db_b.username=root
datasource.db_b.password=123456

#C role
datasource.db_c.driver-class-name=com.mysql.jdbc.Driver
datasource.db_c.url=jdbc:mysql://127.0.0.1:3306/test_C?autoReconnect=true&useUnicode=true&characterEncoding=utf8&logSlowQueries=false&slowQueryThresholdMillis=0&allowMultiQueries=true
datasource.db_c.username=root
datasource.db_c.password=123456


2、写一个ThreadLocalDynamicDataSource类继承AbstractRoutingDataSource,并实现determineCurrentLookupKey方法
public class ThreadLocalRountingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceTypeManager.get();
}
}
3、编写DataSourceTypeManager执行类,并利用ThreadLocal以空间换时间的方式解决线程安全问题
public class DataSourceTypeManager {
private static final ThreadLocal<DataSources> dataSourceTypes = new ThreadLocal<DataSources>() {
@Override
protected DataSources initialValue() {
return DataSources.DATASOURCE_DEFAULT;
}
};

public static DataSources get() {
return dataSourceTypes.get();
}

public static void set(DataSources dataSourceType) {
dataSourceTypes.set(dataSourceType);
}

public static void reset() {
dataSourceTypes.set(DataSources.DATASOURCE_DEFAULT);
}

public static void clearDataSources () {
dataSourceTypes.remove();
}
}




上面代码利用initialValue方法,调用初始化时指定的DataSources 类型,这里DataSources 为枚举类型,里面定义三种数据源的名称,代码如下:
public enum DataSources {
DATASOURCE,DATASOURCE_DEFAULT,DATASOURCE_ORACLE
}

4、multipleDataSource多数据源配置的xml配置
<bean id="multipleDataSource"
class="com.hxqc.basic.dependency.datasource.ThreadLocalRountingDataSource">
<property name="defaultTargetDataSource" ref="ncDataSource"/>
<property name="targetDataSources">
<map key-type="com.hxqc.basic.dependency.datasource.DataSources">
<entry key="DATASOURCE" value-ref="erpDataSource"/>
<entry key="DATASOURCE_DEFAULT" value-ref="ncDataSource"/>
<entry key="DATASOURCE_ORACLE" value-ref="oracleDataSource"/>
</map>
</property>
</bean>



multipleDataSource多数据源配置的Java配置(上文已经贴出该配置,这里再强调一下)
@Bean
@Primary
public DataSource multipleDataSource(){
DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
dynamicDataSource.setDefaultTargetDataSource(dataSourceDefault());

Map<Object,Object> dataSourcesType = new HashMap();
dataSourcesType.put(DataSources.DATASOURCE_A,dataSourceDefault());
dataSourcesType.put(DataSources.DATASOURCE_B,dataSourceB());
dataSourcesType.put(DataSources.DATASOURCE_C,dataSourceC());

dynamicDataSource.setTargetDataSources(dataSourcesType);

return dynamicDataSource;
}

5.在DAOImpl中切换数据源 或者配置aop切面根据不同的包下面的数据,采用不同的数据源



1,在Dao层直接切换数据源,如切换到默认数据源
DataSourceTypeManager.set(DataSources.DATASOURCE_DEFAULT)

2.根据aop进行动态切换
aop xml配置
<bean id="dataSourceInterceptor" class="com.hxqc.basic.dependency.interceptor.DataSourceInterceptor"/>
<aop:config>
<aop:aspect id="dataSourceAspect" ref="dataSourceInterceptor">
<aop:pointcut id="datasource" expression="execution(* com.hxqc.data.gather.core.erp.*.mapper..*.*(..))"/>
<aop:pointcut id="datasource_oracle" expression="execution(* com.hxqc.data.gather.core.oracle.*.mapper..*.*(..))"/>
<aop:pointcut id="datasource_default" expression="execution(* com.hxqc.data.gather.core.nc.*.mapper..*.*(..))"/>
<aop:before method="setDataSource" pointcut-ref="datasource"/>
<aop:before method="setDataSourceDefault" pointcut-ref="datasource_default"/>
<aop:before method="setDataSourceOracle" pointcut-ref="datasource_oracle"/>
</aop:aspect>
</aop:config>
aop java 配置
@Aspect
@Component
public class DataSourceAspect {

private Logger log = LoggerFactory.getLogger(getClass());

@Pointcut("execution(* com.may.dataA.mapper.*.*(..))")
public void pointCutA(){}

@Pointcut("execution(* com.may.dataB.mapper.*.*(..))")
public void pointCutB(){}

@Pointcut("execution(* com.may.dataC.mapper.*.*(..))")
public void pointCutC(){}
@Pointcut("execution(* com.may..*.*(..))")
public void annotation(){}



public void setDataSourceDefault(JoinPoint jp){
DataSourceManager.set(DataSources.DATASOURCE_A);
log.info("切换数据源","切换到默认数据源A");

}
public void setDataSourceB(JoinPoint jp){
DataSourceManager.set(DataSources.DATASOURCE_B);
log.info("切换数据源","切换到数据源B");
}

public void setDataSourceC(JoinPoint jp){
DataSourceManager.set(DataSources.DATASOURCE_C);
log.info("切换数据源","切换到数据源C");
}

@Before("@annotation(ds)")
public void setAnnotationDataSource(JoinPoint jp,DataSource ds){
// 获取当前的指定的数据源;
String dsId = ds.value();
// 如果不在我们注入的所有的数据源范围之内,那么输出警告信息,系统自动使用默认的数据源。
/*if (!DataSourceManager.containsDataSource(dsId)) {

log.error("数据源[{}]不存在,使用默认数据源 > {}", ds.value(), jp.getSignature());

} else {

log.debug("Use DataSource : {} > {}", ds.value(), jp.getSignature());

// 找到的话,那么设置到动态数据源上下文中。
DynamicDataSourceContextHolder.setDataSourceType(ds.name());
}*/

//将string值转换为enum
DataSourceManager.set(Enum.valueOf(DataSources.class,ds.value()));

log.info("切换数据源"+ds.value(),"切换到默认数据源"+ds.value());
}



}

解释,这里定义三个切面,分别对应三个数据源,如在oracle包下的,动态切换到oracle数据源,上面配置的最后部分是用到了自定义注解DataSource注解,该注解在方法上,通过切面来进行数据源的切换,具体看源码

然后配置切面类
/**
*
*
* @author maple
*
*/
public class DataSourceInterceptor {

public void setDataSourceDefault(JoinPoint jp){
DataSourceTypeManager.set(DataSources.DATASOURCE_DEFAULT);
LogU.i("切换数据源","切换到NC数据源");
System.out.println(jp.getTarget().getClass().getSimpleName());
}

public void setDataSource(JoinPoint jp){
DataSourceTypeManager.set(DataSources.DATASOURCE);
LogU.i("切换数据源","切换到ERP数据源");
}

public void setDataSourceOracle(JoinPoint jp){
DataSourceTypeManager.set(DataSources.DATASOURCE_ORACLE);
LogU.i("切换数据源","切换到Oracle数据源");
}
}





`问题:`多数据源切换是成功了,但牵涉到事务呢?单数据源事务是ok的,但如果多数据源需要同时使用一个事务呢?这个问题有点头大,网络上有人提出用atomikos开源项目实现JTA分布式事务处理。你怎么看?

注意:

本文源码放在码云上,注意,源码将XML配置的内容都改成了JAVA配置,项目是基于Spring-boot,也提供了自定义注解来改变动态数据源!

本文源码:http://git.oschina.net/youjie1/may-dynamic-datasource
原文

 

Spring Data Redis - Could not safely identify store assignment for repository

回复

wayne 发起了问题 • 1 人关注 • 0 个回复 • 225 次浏览 • 2017-09-04 13:45 • 来自相关话题

Spring Data JPA系列:数据查询(Specification)(二)

wayne 发表了文章 • 0 个评论 • 510 次浏览 • 2017-07-18 16:36 • 来自相关话题

写了一系列入门文章之后,博客也有了一些访问量,按照计划,对数据查询进行深入一些的探究,包括
- inner join查询
- 连接对象的属性值查询
- in条件查询
- left join查询
还是入门级的示例,更深入的用法需要在实际场景中深化。

1、更改Customer类

增加@OneToMany注解的订单对象
需要注意的是,这次增加了Lombok依赖,一个简化对象类定义的插件,详见:https://projectlombok.org/
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.NamedQuery;
import javax.persistence.*;
import java.util.List;
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@NamedQuery(name="Customer.findByFirstName",query = "select c from Customer c where c.firstName = ?1")
public class Customer {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
private String firstName;
private String lastName;

//一对多,一个客户对应多个订单,关联的字段是订单里的cId字段
@OneToMany
@JoinColumn(name = "cId")
private List<MyOrder> myOrders;
public Customer(String firstName, String lastName) {
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return String.format(
"Customer[id=%d, firstName='%s', lastName='%s']",
id, firstName, lastName);
}
}
2、增加MyOrder类

我的订单对象
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.math.BigDecimal;
/**
* Created by Administrator on 2017/7/17 0017.
*/
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MyOrder {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String code;
private Long cId;
private BigDecimal total;
//实体映射重复列必须设置:insertable = false,updatable = false
@OneToOne
@JoinColumn(name = "cId",insertable = false,updatable = false)
private Customer customer;
@Override
public String toString() {
return "MyOrder{" +
"id=" + id +
", code='" + code + '\'' +
", cId=" + cId +
", total=" + total +
", customer=" + customer +
'}';
}
}3、新增MyOrderRepository类

这里主要是继承JpaSpecificationExecutor接口,进行Specification查询
import com.example.demo.dto.MyOrder;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.CrudRepository;
/**
* Created by Administrator on 2017/7/17 0017.
*/
public interface MyOrderRepository extends JpaSpecificationExecutor<MyOrder>,CrudRepository<MyOrder,Long> {
}4、新增ShoppingController类
import com.example.demo.dto.Customer;
import com.example.demo.dto.MyOrder;
import com.example.demo.repositories.MyOrderRepository;
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.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.persistence.criteria.*;

@Controller
@RequestMapping("/shop")
public class ShoppingController {
@Autowired
private MyOrderRepository myOrderRepository;

/**
* 内连接查询
*/
@RequestMapping("/q1")
public void specification1() {
//根据查询结果,声明返回值对象,这里要查询用户的订单列表,所以声明返回对象为MyOrder
Specification<MyOrder> spec = new Specification<MyOrder>() {
//Root<X> 根查询,默认与声明相同
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
//声明并创建MyOrder的CriteriaQuery对象
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
//连接的时候,要以声明的根查询对象(这里是root,也可以自己创建)进行join
//Join<Z,X>是Join生成的对象,这里的Z是被连接的对象,X是目标对象,
// 连接的属性字段是被连接的对象在目标对象的属性,这里是我们在MyOrder内声明的customer
//join的第二个参数是可选的,默认是JoinType.INNER(内连接 inner join),也可以是JoinType.LEFT(左外连接 left join)
Join<Customer, MyOrder> myOrderJoin = root.join("customer", JoinType.INNER);
//用CriteriaQuery对象拼接查询条件,这里只增加了一个查询条件,cId=1
q1.select(myOrderJoin).where(cb.equal(root.get("cId"), 1));
//通过getRestriction获得Predicate对象
Predicate p1 = q1.getRestriction();
//返回对象
return p1;
}
};
resultPrint(spec);
}


/**
* 增加查询条件,关联的对象Customer的对象值
*/
@RequestMapping("/q2")
public void specification2() {
Specification<MyOrder> spec = new Specification<MyOrder>() {
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
Join<Customer, MyOrder> myOrderJoin = root.join("customer");
q1.select(myOrderJoin)
.where(
cb.equal(root.get("cId"), 1),//cId=1
cb.equal(root.get("customer").get("firstName"), "Jack")//对象customer的firstName=Jack
);
Predicate p1 = q1.getRestriction();
return p1;
}
};
resultPrint(spec);
}

/**
* in的条件查询
* 需要将对应的结果集以root.get("attributeName").in(Object.. values)的方式传入
* values支持多个参数,支持对象(Object),表达式Expression<?>,集合Collection以及Expression<Collection<?>>
*/
@RequestMapping("/q3")
public void specification3() {
Specification<MyOrder> spec = new Specification<MyOrder>() {
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
Join<Customer, MyOrder> myOrderJoin = root.join("customer");
q1.select(myOrderJoin)
.where(
cb.equal(root.get("cId"), 1)
, root.get("id").in(1, 2, 4)
);

Predicate p1 = q1.getRestriction();
return p1;
}
};
resultPrint(spec);
}


/**
* 左外链接查询,对比inner join,
* 这里只是改了一个参数,将JoinType.INNER改成JoinType.LEFT
* <p>
* 注意,当前示例不支持JoinType.RIGHT,用的比较少,没有探究
*/
@RequestMapping("/q4")
public void specification4() {
Specification<MyOrder> spec = new Specification<MyOrder>() {
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
Join<Customer, MyOrder> myOrderJoin = root.join("customer", JoinType.LEFT);
q1.select(myOrderJoin).where(cb.equal(root.get("cId"), 1));
Predicate p1 = q1.getRestriction();
return p1;
}
};
resultPrint(spec);
}


private void resultPrint(Specification<MyOrder> spec) {
//分页查询
Pageable pageable = new PageRequest(0, 10, Sort.Direction.DESC, "id");
//查询的分页结果
Page<MyOrder> page = myOrderRepository.findAll(spec, pageable);
System.out.println(page);
System.out.println(page.getTotalElements());
System.out.println(page.getTotalPages());
for (MyOrder c : page.getContent()) {
System.out.println(c.toString());
}
}

}
5、测试
1)、内连接查询及结果:
URL:http://localhost:8080/shop/q1 
2)、关联对象条件查询及结果:
URL:http://localhost:8080/shop/q2
3)、in条件查询测试及结果:
URL:http://localhost:8080/shop/q3
4)、左外连接测试及结果:
URL:http://localhost:8080/shop/q4
 
参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com
本文链接:http://www.icnws.com/?p=578

版权归作者所有,转载请注明! 查看全部
写了一系列入门文章之后,博客也有了一些访问量,按照计划,对数据查询进行深入一些的探究,包括
- inner join查询
- 连接对象的属性值查询
- in条件查询
- left join查询
还是入门级的示例,更深入的用法需要在实际场景中深化。

1、更改Customer类

增加@OneToMany注解的订单对象
需要注意的是,这次增加了Lombok依赖,一个简化对象类定义的插件,详见:https://projectlombok.org/
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.NamedQuery;
import javax.persistence.*;
import java.util.List;
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@NamedQuery(name="Customer.findByFirstName",query = "select c from Customer c where c.firstName = ?1")
public class Customer {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
private String firstName;
private String lastName;

//一对多,一个客户对应多个订单,关联的字段是订单里的cId字段
@OneToMany
@JoinColumn(name = "cId")
private List<MyOrder> myOrders;
public Customer(String firstName, String lastName) {
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return String.format(
"Customer[id=%d, firstName='%s', lastName='%s']",
id, firstName, lastName);
}
}

2、增加MyOrder类

我的订单对象
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.math.BigDecimal;
/**
* Created by Administrator on 2017/7/17 0017.
*/
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class MyOrder {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String code;
private Long cId;
private BigDecimal total;
//实体映射重复列必须设置:insertable = false,updatable = false
@OneToOne
@JoinColumn(name = "cId",insertable = false,updatable = false)
private Customer customer;
@Override
public String toString() {
return "MyOrder{" +
"id=" + id +
", code='" + code + '\'' +
", cId=" + cId +
", total=" + total +
", customer=" + customer +
'}';
}
}
3、新增MyOrderRepository类

这里主要是继承JpaSpecificationExecutor接口,进行Specification查询
import com.example.demo.dto.MyOrder;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.CrudRepository;
/**
* Created by Administrator on 2017/7/17 0017.
*/
public interface MyOrderRepository extends JpaSpecificationExecutor<MyOrder>,CrudRepository<MyOrder,Long> {
}
4、新增ShoppingController类
import com.example.demo.dto.Customer;
import com.example.demo.dto.MyOrder;
import com.example.demo.repositories.MyOrderRepository;
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.data.jpa.domain.Specification;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.persistence.criteria.*;

@Controller
@RequestMapping("/shop")
public class ShoppingController {
@Autowired
private MyOrderRepository myOrderRepository;

/**
* 内连接查询
*/
@RequestMapping("/q1")
public void specification1() {
//根据查询结果,声明返回值对象,这里要查询用户的订单列表,所以声明返回对象为MyOrder
Specification<MyOrder> spec = new Specification<MyOrder>() {
//Root<X> 根查询,默认与声明相同
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
//声明并创建MyOrder的CriteriaQuery对象
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
//连接的时候,要以声明的根查询对象(这里是root,也可以自己创建)进行join
//Join<Z,X>是Join生成的对象,这里的Z是被连接的对象,X是目标对象,
// 连接的属性字段是被连接的对象在目标对象的属性,这里是我们在MyOrder内声明的customer
//join的第二个参数是可选的,默认是JoinType.INNER(内连接 inner join),也可以是JoinType.LEFT(左外连接 left join)
Join<Customer, MyOrder> myOrderJoin = root.join("customer", JoinType.INNER);
//用CriteriaQuery对象拼接查询条件,这里只增加了一个查询条件,cId=1
q1.select(myOrderJoin).where(cb.equal(root.get("cId"), 1));
//通过getRestriction获得Predicate对象
Predicate p1 = q1.getRestriction();
//返回对象
return p1;
}
};
resultPrint(spec);
}


/**
* 增加查询条件,关联的对象Customer的对象值
*/
@RequestMapping("/q2")
public void specification2() {
Specification<MyOrder> spec = new Specification<MyOrder>() {
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
Join<Customer, MyOrder> myOrderJoin = root.join("customer");
q1.select(myOrderJoin)
.where(
cb.equal(root.get("cId"), 1),//cId=1
cb.equal(root.get("customer").get("firstName"), "Jack")//对象customer的firstName=Jack
);
Predicate p1 = q1.getRestriction();
return p1;
}
};
resultPrint(spec);
}

/**
* in的条件查询
* 需要将对应的结果集以root.get("attributeName").in(Object.. values)的方式传入
* values支持多个参数,支持对象(Object),表达式Expression<?>,集合Collection以及Expression<Collection<?>>
*/
@RequestMapping("/q3")
public void specification3() {
Specification<MyOrder> spec = new Specification<MyOrder>() {
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
Join<Customer, MyOrder> myOrderJoin = root.join("customer");
q1.select(myOrderJoin)
.where(
cb.equal(root.get("cId"), 1)
, root.get("id").in(1, 2, 4)
);

Predicate p1 = q1.getRestriction();
return p1;
}
};
resultPrint(spec);
}


/**
* 左外链接查询,对比inner join,
* 这里只是改了一个参数,将JoinType.INNER改成JoinType.LEFT
* <p>
* 注意,当前示例不支持JoinType.RIGHT,用的比较少,没有探究
*/
@RequestMapping("/q4")
public void specification4() {
Specification<MyOrder> spec = new Specification<MyOrder>() {
@Override
public Predicate toPredicate(Root<MyOrder> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
CriteriaQuery<MyOrder> q1 = cb.createQuery(MyOrder.class);
Join<Customer, MyOrder> myOrderJoin = root.join("customer", JoinType.LEFT);
q1.select(myOrderJoin).where(cb.equal(root.get("cId"), 1));
Predicate p1 = q1.getRestriction();
return p1;
}
};
resultPrint(spec);
}


private void resultPrint(Specification<MyOrder> spec) {
//分页查询
Pageable pageable = new PageRequest(0, 10, Sort.Direction.DESC, "id");
//查询的分页结果
Page<MyOrder> page = myOrderRepository.findAll(spec, pageable);
System.out.println(page);
System.out.println(page.getTotalElements());
System.out.println(page.getTotalPages());
for (MyOrder c : page.getContent()) {
System.out.println(c.toString());
}
}

}
5、测试
1)、内连接查询及结果:
URL:http://localhost:8080/shop/q1 
2)、关联对象条件查询及结果:
URL:http://localhost:8080/shop/q2
3)、in条件查询测试及结果:
URL:http://localhost:8080/shop/q3
4)、左外连接测试及结果:
URL:http://localhost:8080/shop/q4
 
参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com
本文链接:http://www.icnws.com/?p=578


版权归作者所有,转载请注明!


Spring Data JPA系列:数据更新(Update)

wayne 发表了文章 • 0 个评论 • 481 次浏览 • 2017-07-11 11:15 • 来自相关话题

上次通过《Spring Data JPA系列:使用@Modifying修改(Modifying queries)》介绍了数据更新的方式,这种更新方式会很不方便,写的时候也比较麻烦,可以为更新密码、更新用户名等一些特殊的更新单独定义,但是对大多数数据操作是不方便的,比如我要更新一条有一百个字段的数据,这时候如果要通过Modifying方式就非常的不方便,因此,我们需要一种新的方式来解救。
通过阅读Spring-Data-JPA相关的文档和博客,找到了对应的解决方案,就是使用`save()`方法,经过测试,可用。
我们平时对`save()`方法的理解,大多是等同于`insert()`,主要是指新增一条数据,而JPA的`save()`方法包含了`merge()`的概念,就是说,如果save的对象不存在primary key或者primary key值在database内不存在的时候会新添加一条数据,如果primary key 存在并且primary key已经在database中存在,那就会依据primary key对该条数据进行更新,这是我们乐意见到的。

参考的文章:```java
https://stackoverflow.com/ques ... a-jpa
```

相关描述如下:

Identity of entities is defined by their primary keys. Since firstname and lastname are not parts of the primary key, you cannot tell JPA to treat Users with the same firstnames and lastnames as equal if they have different userIds.

So, if you want to update a User identified by its firstname and lastname, you need to find that User by a query, and then change appropriate fields of the object your found. These changes will be flushed to the database automatically at the end of transaction, so that you don't need to do anything to save these changes explicitly.

##### EDIT:

Perhaps I should elaborate on overall semantics of JPA. There are two main approaches to design of persistence APIs:

- insert/update approach. When you need to modify the database you should call methods of persistence API explicitly: you call insert to insert an object, or update to save new state of the object to the database.
- Unit of Work approach. In this case you have a set of objects managed by persistence library. All changes you make to these objects will be flushed to the database automatically at the end of Unit of Work (i.e. at the end of the current transaction in typical case). When you need to insert new record to the database, you make the corresponding object managed. Managed objects are identified by their primary keys, so that if you make an object with predefined primary key managed, it will be associated with the database record of the same id, and state of this object will be propagated to that record automatically.

JPA follows the later approach. save() in Spring Data JPA is backed by merge() in plain JPA, therefore it makes your entity managed as described above. It means that calling save() on an object with predefined id will update the corresponding database record rather than insert a new one, and also explains why save() is not called create().
 
 
参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com

版权归作者所有,如需转载,请与作者联系! 查看全部
上次通过《Spring Data JPA系列:使用@Modifying修改(Modifying queries)》介绍了数据更新的方式,这种更新方式会很不方便,写的时候也比较麻烦,可以为更新密码、更新用户名等一些特殊的更新单独定义,但是对大多数数据操作是不方便的,比如我要更新一条有一百个字段的数据,这时候如果要通过Modifying方式就非常的不方便,因此,我们需要一种新的方式来解救。
通过阅读Spring-Data-JPA相关的文档和博客,找到了对应的解决方案,就是使用`save()`方法,经过测试,可用。
我们平时对`save()`方法的理解,大多是等同于`insert()`,主要是指新增一条数据,而JPA的`save()`方法包含了`merge()`的概念,就是说,如果save的对象不存在primary key或者primary key值在database内不存在的时候会新添加一条数据,如果primary key 存在并且primary key已经在database中存在,那就会依据primary key对该条数据进行更新,这是我们乐意见到的。

参考的文章:```java
https://stackoverflow.com/ques ... a-jpa
```

相关描述如下:

Identity of entities is defined by their primary keys. Since firstname and lastname are not parts of the primary key, you cannot tell JPA to treat Users with the same firstnames and lastnames as equal if they have different userIds.

So, if you want to update a User identified by its firstname and lastname, you need to find that User by a query, and then change appropriate fields of the object your found. These changes will be flushed to the database automatically at the end of transaction, so that you don't need to do anything to save these changes explicitly.

##### EDIT:

Perhaps I should elaborate on overall semantics of JPA. There are two main approaches to design of persistence APIs:

- insert/update approach. When you need to modify the database you should call methods of persistence API explicitly: you call insert to insert an object, or update to save new state of the object to the database.
- Unit of Work approach. In this case you have a set of objects managed by persistence library. All changes you make to these objects will be flushed to the database automatically at the end of Unit of Work (i.e. at the end of the current transaction in typical case). When you need to insert new record to the database, you make the corresponding object managed. Managed objects are identified by their primary keys, so that if you make an object with predefined primary key managed, it will be associated with the database record of the same id, and state of this object will be propagated to that record automatically.

JPA follows the later approach. save() in Spring Data JPA is backed by merge() in plain JPA, therefore it makes your entity managed as described above. It means that calling save() on an object with predefined id will update the corresponding database record rather than insert a new one, and also explains why save() is not called create().
 
 
参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com


版权归作者所有,如需转载,请与作者联系!


Spring Data JPA系列:投影(Projection)的用法

wayne 发表了文章 • 0 个评论 • 356 次浏览 • 2017-07-09 23:58 • 来自相关话题

在JPA的查询中,有一个不方便的地方,@Query注解,如果查询直接是Select C from Customer c,这时候,查询的返回对象就是Customer这个完整的对象,包含所有字段,对于我们的示例并没有什么问题,但是对于比较庞大的domain类,这个查询时就比较要命,并不是所有的字段都能用到,比较头疼。另外,如果定义select c.firstName as firstName,c.lastName as lastName from Customer c这个查询结果,返回的对象是Object类型,而且无法直接转换成Customer对象,这样用起来就不是很方便。
对于这种情况,JPA提供了一种声明方式来解决,即声明一个接口类,然后直接使用这个接口类接受返回的数据即可。下面奉上代码:

1、增加CustomerProjection接口类
package com.example.demo.dto;

import org.springframework.beans.factory.annotation.Value;

/**
* Created by Administrator on 2017/7/9 0009.
*/
public interface CustomerProjection {
@Value("#{target.firstName + ' ' + target.lastName}")
String getFullName();

String getFirstName();

String getLastName();
}
这里声明的方式是可以直接通过get+属性名,这是普通的,另外也可以通过@Value注解来实现指定字段,除了指定字段也可以做聚合展示,比如有些地方需要展示客户的全名,这里定义的getFullName()方法及注解@Value即完成这一操作。需要注意这里的@Value中的target表达式写法及拼接方法。
 
2、增加CustomerRepository方法
@Query("SELECT c.firstName as firstName,c.lastName as lastName from Customer c")
Collection<CustomerProjection> findAllProjectedBy();3、增加CustomerController方法
/**
* find by projections
*/
@RequestMapping("/findAllProjections")
public void findAllProjections(){
Collection<CustomerProjection> projections = repository.findAllProjectedBy();
System.out.println(projections);
System.out.println(projections.size());
for (CustomerProjection projection:projections){
System.out.println("FullName:"+projection.getFullName());
System.out.println("FirstName:"+projection.getFirstName());
System.out.println("LastName:"+projection.getLastName());
}
}这里只是做了简单示意,深入的内容需要自己去挖掘探索。不过关于Projection的资料比较少,我也是扒了不少资料才理解的差不多了,还需要多多实践。

另外spring-data-examples项目中有一些JPA的例子,可以用来学习,梳理思路。https://github.com/spring-projects/spring-data-examples/tree/master/jpa
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com
 

版权归作者所有,如需转载,请与作者联系! 查看全部
在JPA的查询中,有一个不方便的地方,@Query注解,如果查询直接是
Select C from Customer c
,这时候,查询的返回对象就是Customer这个完整的对象,包含所有字段,对于我们的示例并没有什么问题,但是对于比较庞大的domain类,这个查询时就比较要命,并不是所有的字段都能用到,比较头疼。另外,如果定义
select c.firstName as firstName,c.lastName as lastName from Customer c
这个查询结果,返回的对象是Object类型,而且无法直接转换成Customer对象,这样用起来就不是很方便。
对于这种情况,JPA提供了一种声明方式来解决,即声明一个接口类,然后直接使用这个接口类接受返回的数据即可。下面奉上代码:

1、增加CustomerProjection接口类
package com.example.demo.dto;

import org.springframework.beans.factory.annotation.Value;

/**
* Created by Administrator on 2017/7/9 0009.
*/
public interface CustomerProjection {
@Value("#{target.firstName + ' ' + target.lastName}")
String getFullName();

String getFirstName();

String getLastName();
}
这里声明的方式是可以直接通过get+属性名,这是普通的,另外也可以通过@Value注解来实现指定字段,除了指定字段也可以做聚合展示,比如有些地方需要展示客户的全名,这里定义的getFullName()方法及注解@Value即完成这一操作。需要注意这里的@Value中的target表达式写法及拼接方法。
 
2、增加CustomerRepository方法
    @Query("SELECT c.firstName as firstName,c.lastName as lastName from Customer  c")
Collection<CustomerProjection> findAllProjectedBy();
3、增加CustomerController方法
    /**
* find by projections
*/
@RequestMapping("/findAllProjections")
public void findAllProjections(){
Collection<CustomerProjection> projections = repository.findAllProjectedBy();
System.out.println(projections);
System.out.println(projections.size());
for (CustomerProjection projection:projections){
System.out.println("FullName:"+projection.getFullName());
System.out.println("FirstName:"+projection.getFirstName());
System.out.println("LastName:"+projection.getLastName());
}
}
这里只是做了简单示意,深入的内容需要自己去挖掘探索。不过关于Projection的资料比较少,我也是扒了不少资料才理解的差不多了,还需要多多实践。

另外spring-data-examples项目中有一些JPA的例子,可以用来学习,梳理思路。https://github.com/spring-projects/spring-data-examples/tree/master/jpa
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com
 


版权归作者所有,如需转载,请与作者联系!


Spring Data JPA系列:分页(Pageable)

wayne 发表了文章 • 0 个评论 • 337 次浏览 • 2017-07-09 23:54 • 来自相关话题

在JPA中提供了很方便的分页功能,那就是Pageable(org.springframework.data.domain.Pageable)以及它的实现类PageRequest(org.springframework.data.domain.PageRequest),详细的可以见示例代码。
1、改变CustomerRepository方法​ /**
* 一个参数,匹配两个字段
* @param name2
* @Param pageable 分页参数
* @return
* 这里Param的值和=:后面的参数匹配,但不需要和方法名对应的参数值对应
* 这里增加了@QueryHints注解,是给查询添加一些额外的提示
* 比如当前的name值为HINT_COMMENT是在查询的时候带上一些备注信息
*/
@QueryHints(value = { @QueryHint(name = HINT_COMMENT, value = "a query for pageable")})
@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
Page<Customer> findByName(@Param("name") String name2,Pageable pageable);
2、增加CustomerController方法pageable
/**
* 分页
* 应用查询提示@QueryHints,这里是在查询的适合增加了一个comment
* 查询结果是lastName和firstName都是bauer这个值的数据
*/
@RequestMapping("/pageable")
public void pageable(){
//Pageable是接口,PageRequest是接口实现
//PageRequest的对象构造函数有多个,page是页数,初始值是0,size是查询结果的条数,后两个参数参考Sort对象的构造方法
Pageable pageable = new PageRequest(0,3, Sort.Direction.DESC,"id");
Page<Customer> page = repository.findByName("bauer",pageable);
//查询结果总行数
System.out.println(page.getTotalElements());
//按照当前分页大小,总页数
System.out.println(page.getTotalPages());
//按照当前页数、分页大小,查出的分页结果集合
for (Customer customer: page.getContent()) {
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
}从示例代码的注释当中可以看到Page对象的相关参数及值的说明,更详细的用法,参考PageRequest源码。

小结:怎么样,是不是很简单很方便?!
另:PageRequest.java源码在上一篇文章已经有了。
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com
 

版权归作者所有,如需转载,请与作者联系! 查看全部
在JPA中提供了很方便的分页功能,那就是Pageable(org.springframework.data.domain.Pageable)以及它的实现类PageRequest(org.springframework.data.domain.PageRequest),详细的可以见示例代码。
1、改变CustomerRepository方法​
    /**
* 一个参数,匹配两个字段
* @param name2
* @Param pageable 分页参数
* @return
* 这里Param的值和=:后面的参数匹配,但不需要和方法名对应的参数值对应
* 这里增加了@QueryHints注解,是给查询添加一些额外的提示
* 比如当前的name值为HINT_COMMENT是在查询的时候带上一些备注信息
*/
@QueryHints(value = { @QueryHint(name = HINT_COMMENT, value = "a query for pageable")})
@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
Page<Customer> findByName(@Param("name") String name2,Pageable pageable);
2、增加CustomerController方法pageable
    /**
* 分页
* 应用查询提示@QueryHints,这里是在查询的适合增加了一个comment
* 查询结果是lastName和firstName都是bauer这个值的数据
*/
@RequestMapping("/pageable")
public void pageable(){
//Pageable是接口,PageRequest是接口实现
//PageRequest的对象构造函数有多个,page是页数,初始值是0,size是查询结果的条数,后两个参数参考Sort对象的构造方法
Pageable pageable = new PageRequest(0,3, Sort.Direction.DESC,"id");
Page<Customer> page = repository.findByName("bauer",pageable);
//查询结果总行数
System.out.println(page.getTotalElements());
//按照当前分页大小,总页数
System.out.println(page.getTotalPages());
//按照当前页数、分页大小,查出的分页结果集合
for (Customer customer: page.getContent()) {
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
}
从示例代码的注释当中可以看到Page对象的相关参数及值的说明,更详细的用法,参考PageRequest源码。

小结:怎么样,是不是很简单很方便?!
另:PageRequest.java源码在上一篇文章已经有了。
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com
 


版权归作者所有,如需转载,请与作者联系!


Spring Data Jpa遇到mysql关键字

采蘑菇的大叔 发表了文章 • 0 个评论 • 306 次浏览 • 2017-07-08 16:09 • 来自相关话题

今天来加班,同事发来一张图:

分析知道,这是因为spring data jpa遇到了mysql的关键字,导致update失败了。
查看了model中,是这样写的。[code]/**
* 参数名
*/
@Column(name = "name")
private String name;
[/code]
寻求了一会儿方案,突发奇想,改成下面的样子[code]/**
* 参数名
*/
@Column(name = "`name`")
private String name;
[/code]
运行,解决。

没有用过hibernate,bibernate里面以前应该有这种问题的方案吧。

查看全部
今天来加班,同事发来一张图:

分析知道,这是因为spring data jpa遇到了mysql的关键字,导致update失败了。
查看了model中,是这样写的。
[code]/**
* 参数名
*/
@Column(name = "name")
private String name;
[/code]
寻求了一会儿方案,突发奇想,改成下面的样子
[code]/**
* 参数名
*/
@Column(name = "`name`")
private String name;
[/code]
运行,解决。


没有用过hibernate,bibernate里面以前应该有这种问题的方案吧。


Spring Data JPA系列:应用查询提示(Applying query hints)

wayne 发表了文章 • 0 个评论 • 581 次浏览 • 2017-07-06 10:54 • 来自相关话题

这一节讲应用查询提示,学这一节比较波折,文档上的介绍太简单了,而且面对示例不知道如何下手,所以拖了一下,才有点头绪。
public interface UserRepository extends Repository<User, Long> {

@QueryHints(value = { @QueryHint(name = "name", value = "value")},
forCounting = false)
Page<User> findByLastname(String lastname, Pageable pageable);
}对name和value的值我一直以为是自定义的,最后发现不完全是,是有一组定义好的值供我们选择。
1、QueryHints源码如下:
/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* License: GNU Lesser General Public License (LGPL), version 2.1 or later.
* See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
*/
package org.hibernate.jpa;

import java.util.HashSet;
import java.util.Set;

import static org.hibernate.annotations.QueryHints.CACHEABLE;
import static org.hibernate.annotations.QueryHints.CACHE_MODE;
import static org.hibernate.annotations.QueryHints.CACHE_REGION;
import static org.hibernate.annotations.QueryHints.COMMENT;
import static org.hibernate.annotations.QueryHints.FETCHGRAPH;
import static org.hibernate.annotations.QueryHints.FETCH_SIZE;
import static org.hibernate.annotations.QueryHints.FLUSH_MODE;
import static org.hibernate.annotations.QueryHints.LOADGRAPH;
import static org.hibernate.annotations.QueryHints.NATIVE_LOCKMODE;
import static org.hibernate.annotations.QueryHints.READ_ONLY;
import static org.hibernate.annotations.QueryHints.TIMEOUT_HIBERNATE;
import static org.hibernate.annotations.QueryHints.TIMEOUT_JPA;

/**
* Defines the supported JPA query hints
*
* @author Steve Ebersole
*/
public class QueryHints {
/**
* The hint key for specifying a query timeout per Hibernate O/RM, which defines the timeout in seconds.
*
* @deprecated use {@link #SPEC_HINT_TIMEOUT} instead
*/
@Deprecated
public static final String HINT_TIMEOUT = TIMEOUT_HIBERNATE;

/**
* The hint key for specifying a query timeout per JPA, which defines the timeout in milliseconds
*/
public static final String SPEC_HINT_TIMEOUT = TIMEOUT_JPA;

/**
* The hint key for specifying a comment which is to be embedded into the SQL sent to the database.
*/
public static final String HINT_COMMENT = COMMENT;

/**
* The hint key for specifying a JDBC fetch size, used when executing the resulting SQL.
*/
public static final String HINT_FETCH_SIZE = FETCH_SIZE;

/**
* The hint key for specifying whether the query results should be cached for the next (cached) execution of the
* "same query".
*/
public static final String HINT_CACHEABLE = CACHEABLE;

/**
* The hint key for specifying the name of the cache region (within Hibernate's query result cache region)
* to use for storing the query results.
*/
public static final String HINT_CACHE_REGION = CACHE_REGION;

/**
* The hint key for specifying that objects loaded into the persistence context as a result of this query execution
* should be associated with the persistence context as read-only.
*/
public static final String HINT_READONLY = READ_ONLY;

/**
* The hint key for specifying the cache mode ({@link org.hibernate.CacheMode}) to be in effect for the
* execution of the hinted query.
*/
public static final String HINT_CACHE_MODE = CACHE_MODE;

/**
* The hint key for specifying the flush mode ({@link org.hibernate.FlushMode}) to be in effect for the
* execution of the hinted query.
*/
public static final String HINT_FLUSH_MODE = FLUSH_MODE;

public static final String HINT_NATIVE_LOCKMODE = NATIVE_LOCKMODE;

/**
* Hint providing a "fetchgraph" EntityGraph. Attributes explicitly specified as AttributeNodes are treated as
* FetchType.EAGER (via join fetch or subsequent select).
*
* Note: Currently, attributes that are not specified are treated as FetchType.LAZY or FetchType.EAGER depending
* on the attribute's definition in metadata, rather than forcing FetchType.LAZY.
*/
public static final String HINT_FETCHGRAPH = FETCHGRAPH;

/**
* Hint providing a "loadgraph" EntityGraph. Attributes explicitly specified as AttributeNodes are treated as
* FetchType.EAGER (via join fetch or subsequent select). Attributes that are not specified are treated as
* FetchType.LAZY or FetchType.EAGER depending on the attribute's definition in metadata
*/
public static final String HINT_LOADGRAPH = LOADGRAPH;

private static final Set<String> HINTS = buildHintsSet();

private static Set<String> buildHintsSet() {
HashSet<String> hints = new HashSet<String>();
hints.add( HINT_TIMEOUT );
hints.add( SPEC_HINT_TIMEOUT );
hints.add( HINT_COMMENT );
hints.add( HINT_FETCH_SIZE );
hints.add( HINT_CACHE_REGION );
hints.add( HINT_CACHEABLE );
hints.add( HINT_READONLY );
hints.add( HINT_CACHE_MODE );
hints.add( HINT_FLUSH_MODE );
hints.add( HINT_NATIVE_LOCKMODE );
hints.add( HINT_FETCHGRAPH );
hints.add( HINT_LOADGRAPH );
return java.util.Collections.unmodifiableSet( hints );
}

public static Set<String> getDefinedHints() {
return HINTS;
}

protected QueryHints() {
}
}对应值的介绍,除了注释,我在网上也找到了一些资料,讲的很详细:
https://www.thoughts-on-java.org/11-jpa-hibernate-query-hints-every-developer-know/
https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/jpa/QueryHints.html
 
2、在CustomerRepository中添加示例:

导入QueryHint的name的对应的值import static org.hibernate.jpa.QueryHints.HINT_COMMENT; /**
* 一个参数,匹配两个字段
* @param name2
* @Param pageable 分页参数
* @return
* 这里Param的值和=:后面的参数匹配,但不需要和方法名对应的参数值对应
* 这里增加了@QueryHints注解,是给查询添加一些额外的提示
* 比如当前的name值为HINT_COMMENT是在查询的时候带上一些备注信息
*/
@QueryHints(value = { @QueryHint(name = HINT_COMMENT, value = "a query for pageable")})
@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
Page<Customer> findByName(@Param("name") String name2,Pageable pageable);
3、在CustomerController中添加示例:
/**
* 分页
* 应用查询提示@QueryHints,这里是在查询的适合增加了一个comment
* 查询结果是lastName和firstName都是bauer这个值的数据
*/
@RequestMapping("/pageable")
public void pageable(){
//Pageable是接口,PageRequest是接口实现
//PageRequest的对象构造函数有多个,page是页数,初始值是0,size是查询结果的条数,后两个参数参考Sort对象的构造方法
Pageable pageable = new PageRequest(0,3, Sort.Direction.DESC,"id");
Page<Customer> page = repository.findByName("bauer",pageable);
//查询结果总行数
System.out.println(page.getTotalElements());
//按照当前分页大小,总页数
System.out.println(page.getTotalPages());
//按照当前页数、分页大小,查出的分页结果集合
for (Customer customer: page.getContent()) {
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
}注意到,这里除了方法调用了带有查询提示的方法以外,还对方法的Pageable参数进行了简单实现——PageRequest,这个类包含了多个构造函数,可以根据自己的需求自由定制,对于排序不分参考Sort那一篇。
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com 查看全部

这一节讲应用查询提示,学这一节比较波折,文档上的介绍太简单了,而且面对示例不知道如何下手,所以拖了一下,才有点头绪。
public interface UserRepository extends Repository<User, Long> {

@QueryHints(value = { @QueryHint(name = "name", value = "value")},
forCounting = false)
Page<User> findByLastname(String lastname, Pageable pageable);
}
对name和value的值我一直以为是自定义的,最后发现不完全是,是有一组定义好的值供我们选择。
1、QueryHints源码如下:
/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* License: GNU Lesser General Public License (LGPL), version 2.1 or later.
* See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
*/
package org.hibernate.jpa;

import java.util.HashSet;
import java.util.Set;

import static org.hibernate.annotations.QueryHints.CACHEABLE;
import static org.hibernate.annotations.QueryHints.CACHE_MODE;
import static org.hibernate.annotations.QueryHints.CACHE_REGION;
import static org.hibernate.annotations.QueryHints.COMMENT;
import static org.hibernate.annotations.QueryHints.FETCHGRAPH;
import static org.hibernate.annotations.QueryHints.FETCH_SIZE;
import static org.hibernate.annotations.QueryHints.FLUSH_MODE;
import static org.hibernate.annotations.QueryHints.LOADGRAPH;
import static org.hibernate.annotations.QueryHints.NATIVE_LOCKMODE;
import static org.hibernate.annotations.QueryHints.READ_ONLY;
import static org.hibernate.annotations.QueryHints.TIMEOUT_HIBERNATE;
import static org.hibernate.annotations.QueryHints.TIMEOUT_JPA;

/**
* Defines the supported JPA query hints
*
* @author Steve Ebersole
*/
public class QueryHints {
/**
* The hint key for specifying a query timeout per Hibernate O/RM, which defines the timeout in seconds.
*
* @deprecated use {@link #SPEC_HINT_TIMEOUT} instead
*/
@Deprecated
public static final String HINT_TIMEOUT = TIMEOUT_HIBERNATE;

/**
* The hint key for specifying a query timeout per JPA, which defines the timeout in milliseconds
*/
public static final String SPEC_HINT_TIMEOUT = TIMEOUT_JPA;

/**
* The hint key for specifying a comment which is to be embedded into the SQL sent to the database.
*/
public static final String HINT_COMMENT = COMMENT;

/**
* The hint key for specifying a JDBC fetch size, used when executing the resulting SQL.
*/
public static final String HINT_FETCH_SIZE = FETCH_SIZE;

/**
* The hint key for specifying whether the query results should be cached for the next (cached) execution of the
* "same query".
*/
public static final String HINT_CACHEABLE = CACHEABLE;

/**
* The hint key for specifying the name of the cache region (within Hibernate's query result cache region)
* to use for storing the query results.
*/
public static final String HINT_CACHE_REGION = CACHE_REGION;

/**
* The hint key for specifying that objects loaded into the persistence context as a result of this query execution
* should be associated with the persistence context as read-only.
*/
public static final String HINT_READONLY = READ_ONLY;

/**
* The hint key for specifying the cache mode ({@link org.hibernate.CacheMode}) to be in effect for the
* execution of the hinted query.
*/
public static final String HINT_CACHE_MODE = CACHE_MODE;

/**
* The hint key for specifying the flush mode ({@link org.hibernate.FlushMode}) to be in effect for the
* execution of the hinted query.
*/
public static final String HINT_FLUSH_MODE = FLUSH_MODE;

public static final String HINT_NATIVE_LOCKMODE = NATIVE_LOCKMODE;

/**
* Hint providing a "fetchgraph" EntityGraph. Attributes explicitly specified as AttributeNodes are treated as
* FetchType.EAGER (via join fetch or subsequent select).
*
* Note: Currently, attributes that are not specified are treated as FetchType.LAZY or FetchType.EAGER depending
* on the attribute's definition in metadata, rather than forcing FetchType.LAZY.
*/
public static final String HINT_FETCHGRAPH = FETCHGRAPH;

/**
* Hint providing a "loadgraph" EntityGraph. Attributes explicitly specified as AttributeNodes are treated as
* FetchType.EAGER (via join fetch or subsequent select). Attributes that are not specified are treated as
* FetchType.LAZY or FetchType.EAGER depending on the attribute's definition in metadata
*/
public static final String HINT_LOADGRAPH = LOADGRAPH;

private static final Set<String> HINTS = buildHintsSet();

private static Set<String> buildHintsSet() {
HashSet<String> hints = new HashSet<String>();
hints.add( HINT_TIMEOUT );
hints.add( SPEC_HINT_TIMEOUT );
hints.add( HINT_COMMENT );
hints.add( HINT_FETCH_SIZE );
hints.add( HINT_CACHE_REGION );
hints.add( HINT_CACHEABLE );
hints.add( HINT_READONLY );
hints.add( HINT_CACHE_MODE );
hints.add( HINT_FLUSH_MODE );
hints.add( HINT_NATIVE_LOCKMODE );
hints.add( HINT_FETCHGRAPH );
hints.add( HINT_LOADGRAPH );
return java.util.Collections.unmodifiableSet( hints );
}

public static Set<String> getDefinedHints() {
return HINTS;
}

protected QueryHints() {
}
}
对应值的介绍,除了注释,我在网上也找到了一些资料,讲的很详细:
https://www.thoughts-on-java.org/11-jpa-hibernate-query-hints-every-developer-know/
https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/jpa/QueryHints.html
 
2、在CustomerRepository中添加示例:

导入QueryHint的name的对应的值
import static org.hibernate.jpa.QueryHints.HINT_COMMENT;
    /**
* 一个参数,匹配两个字段
* @param name2
* @Param pageable 分页参数
* @return
* 这里Param的值和=:后面的参数匹配,但不需要和方法名对应的参数值对应
* 这里增加了@QueryHints注解,是给查询添加一些额外的提示
* 比如当前的name值为HINT_COMMENT是在查询的时候带上一些备注信息
*/
@QueryHints(value = { @QueryHint(name = HINT_COMMENT, value = "a query for pageable")})
@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
Page<Customer> findByName(@Param("name") String name2,Pageable pageable);
3、在CustomerController中添加示例:
    /**
* 分页
* 应用查询提示@QueryHints,这里是在查询的适合增加了一个comment
* 查询结果是lastName和firstName都是bauer这个值的数据
*/
@RequestMapping("/pageable")
public void pageable(){
//Pageable是接口,PageRequest是接口实现
//PageRequest的对象构造函数有多个,page是页数,初始值是0,size是查询结果的条数,后两个参数参考Sort对象的构造方法
Pageable pageable = new PageRequest(0,3, Sort.Direction.DESC,"id");
Page<Customer> page = repository.findByName("bauer",pageable);
//查询结果总行数
System.out.println(page.getTotalElements());
//按照当前分页大小,总页数
System.out.println(page.getTotalPages());
//按照当前页数、分页大小,查出的分页结果集合
for (Customer customer: page.getContent()) {
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
}
注意到,这里除了方法调用了带有查询提示的方法以外,还对方法的Pageable参数进行了简单实现——PageRequest,这个类包含了多个构造函数,可以根据自己的需求自由定制,对于排序不分参考Sort那一篇。
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com

Spring Data JPA系列:使用@Modifying修改(Modifying queries)

wayne 发表了文章 • 0 个评论 • 365 次浏览 • 2017-07-04 15:59 • 来自相关话题

通过之前的讲解和示例,我们掌握了基本的JPA使用方法,大多数是一些查询数据的方法,这一节我们学习通过@Modifying去做数据更新的方法示例。

1、在CustomerRepository上增加新方法
/**
* 根据lastName去更新firstName,返回结果是更改数据的行数
* @param firstName
* @param lastName
* @return
*/
@Modifying//更新查询
@Transactional//开启事务
@Query("update Customer c set c.firstName = ?1 where c.lastName = ?2")
int setFixedFirstnameFor(String firstName, String lastName);这里需要注意,在使用@Modifying注解的时候,一定要加上事务注解@Transactional,如果你忘了或者加错了,那很可能报如下错误:
2017-07-01 01:03:24.844 ERROR 8072 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]
: Servlet.service() for servlet [dispatcherServlet] in context with path []
threw exception [Request processing failed; nested exception is org.springframework
dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is
javax.persistence.TransactionRequiredException: Executing an update/delete query] with root cause
javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:54)
~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]如果你发现了上述错误就去加上事务吧。
 
2、在CustomerController中测试:
/**
* 根据FirstName进行修改
*/
@RequestMapping("/modifying")
public void modifying(){
Integer result = repository.setFixedFirstnameFor("Bauorx","Bauer");
if(result!=null){
System.out.println("modifying result:"+result);
}
System.out.println("-------------------------------------------");

}
这个示例是update的,还有delete也是同样的,参考上述示例。
 
到此,@Modifying的介绍几本就这样了。
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com 查看全部
通过之前的讲解和示例,我们掌握了基本的JPA使用方法,大多数是一些查询数据的方法,这一节我们学习通过@Modifying去做数据更新的方法示例。

1、在CustomerRepository上增加新方法
    /**
* 根据lastName去更新firstName,返回结果是更改数据的行数
* @param firstName
* @param lastName
* @return
*/
@Modifying//更新查询
@Transactional//开启事务
@Query("update Customer c set c.firstName = ?1 where c.lastName = ?2")
int setFixedFirstnameFor(String firstName, String lastName);
这里需要注意,在使用@Modifying注解的时候,一定要加上事务注解@Transactional,如果你忘了或者加错了,那很可能报如下错误:
2017-07-01 01:03:24.844 ERROR 8072 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    
: Servlet.service() for servlet [dispatcherServlet] in context with path []
threw exception [Request processing failed; nested exception is org.springframework
dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is
javax.persistence.TransactionRequiredException: Executing an update/delete query] with root cause
javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:54)
~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
如果你发现了上述错误就去加上事务吧。
 
2、在CustomerController中测试:
    /**
* 根据FirstName进行修改
*/
@RequestMapping("/modifying")
public void modifying(){
Integer result = repository.setFixedFirstnameFor("Bauorx","Bauer");
if(result!=null){
System.out.println("modifying result:"+result);
}
System.out.println("-------------------------------------------");

}

这个示例是update的,还有delete也是同样的,参考上述示例。
 
到此,@Modifying的介绍几本就这样了。
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com

Spring Data JPA系列:使用Sort进行排序(Using Sort)

wayne 发表了文章 • 0 个评论 • 327 次浏览 • 2017-07-01 12:45 • 来自相关话题

通过上一节的学习,我们知道了如何用@Query注解来实现灵活的查询。在上一节的示例中,我也尝试给出简单的排序,通过JPQL语句以及原生SQL来实现的。这样的实现,虽然在一定程度上可以应用,但是灵活度不够,因此结合@Query注解,我们可以使用Sort来对结果进行排序。

1、在CustomerRepository内添加方法
/**
* 一个参数,匹配两个字段
* @param name2
* @param sort 指定排序的参数,可以根据需要进行调整
* @return
* 这里Param的值和=:后面的参数匹配,但不需要和方法名对应的参数值对应
*
*/
@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
List<Customer> findByName4(@Param("name") String name2,Sort sort);方法一如既往,是声明式的,只是在原有方法的基础上,加上Sort(org.springframework.data.domain.Sort)作为参数即可。
 
2、在CustomerController中测试
/**
* @Query注解方式查询,
* 用@Param指定参数,匹配firstName和lastName
*/
@RequestMapping("/findByName")
public void findByName4(){
//按照ID倒序排列
System.out.println("直接创建sort对象,通过排序方法和属性名");
Sort sort = new Sort(Sort.Direction.DESC,"id");
List<Customer> result = repository.findByName4("Bauer",sort);
for (Customer customer:result){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
//按照ID倒序排列
System.out.println("通过Sort.Order对象创建sort对象");
Sort sortx = new Sort(new Sort.Order(Sort.Direction.DESC,"id"));
List<Customer> resultx = repository.findByName4("Bauer",sort);
for (Customer customer:result){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");

System.out.println("通过排序方法和属性List创建sort对象");
List<String> sortProperties = new ArrayList<>();
sortProperties.add("id");
sortProperties.add("firstName");
Sort sort2 = new Sort(Sort.Direction.DESC,sortProperties);
List<Customer> result2 = repository.findByName4("Bauer",sort2);
for (Customer customer:result2){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");

System.out.println("通过创建Sort.Order对象的集合创建sort对象");
List<Sort.Order> orders = new ArrayList<>();
orders.add(new Sort.Order(Sort.Direction.DESC,"id"));
orders.add(new Sort.Order(Sort.Direction.ASC,"firstName"));
List<Customer> result3 = repository.findByName4("Bauer",new Sort(orders));
for (Customer customer:result3){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
}
这里总共列举了四种排序方式:

1)直接创建Sort对象,适合对单一属性做排序
2)通过Sort.Order对象创建Sort对象,适合对单一属性做排序
3)通过属性的List集合创建Sort对象,适合对多个属性,采取同一种排序方式的排序
4)通过Sort.Order对象的List集合创建Sort对象,适合所有情况,比较容易设置排序方式

对应着我们的使用场景来进行选择创建Sort对象的方式。

注意,这里并没有列举所有的Sort使用方式,还有忽略大小写,使用JpaSort.unsafe、聚合函数等进行排序,查询的属性值是Entity的属性名,不是数据库的字段,要注意到!!
 
更多用法,请参考源码:http://www.icnws.com/wp-content/uploads/2017/06/Sort.java_.txt
 
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com 查看全部
通过上一节的学习,我们知道了如何用@Query注解来实现灵活的查询。在上一节的示例中,我也尝试给出简单的排序,通过JPQL语句以及原生SQL来实现的。这样的实现,虽然在一定程度上可以应用,但是灵活度不够,因此结合@Query注解,我们可以使用Sort来对结果进行排序。

1、在CustomerRepository内添加方法
    /**
* 一个参数,匹配两个字段
* @param name2
* @param sort 指定排序的参数,可以根据需要进行调整
* @return
* 这里Param的值和=:后面的参数匹配,但不需要和方法名对应的参数值对应
*
*/
@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
List<Customer> findByName4(@Param("name") String name2,Sort sort);
方法一如既往,是声明式的,只是在原有方法的基础上,加上Sort(org.springframework.data.domain.Sort)作为参数即可。
 
2、在CustomerController中测试
    /**
* @Query注解方式查询,
* 用@Param指定参数,匹配firstName和lastName
*/
@RequestMapping("/findByName")
public void findByName4(){
//按照ID倒序排列
System.out.println("直接创建sort对象,通过排序方法和属性名");
Sort sort = new Sort(Sort.Direction.DESC,"id");
List<Customer> result = repository.findByName4("Bauer",sort);
for (Customer customer:result){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
//按照ID倒序排列
System.out.println("通过Sort.Order对象创建sort对象");
Sort sortx = new Sort(new Sort.Order(Sort.Direction.DESC,"id"));
List<Customer> resultx = repository.findByName4("Bauer",sort);
for (Customer customer:result){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");

System.out.println("通过排序方法和属性List创建sort对象");
List<String> sortProperties = new ArrayList<>();
sortProperties.add("id");
sortProperties.add("firstName");
Sort sort2 = new Sort(Sort.Direction.DESC,sortProperties);
List<Customer> result2 = repository.findByName4("Bauer",sort2);
for (Customer customer:result2){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");

System.out.println("通过创建Sort.Order对象的集合创建sort对象");
List<Sort.Order> orders = new ArrayList<>();
orders.add(new Sort.Order(Sort.Direction.DESC,"id"));
orders.add(new Sort.Order(Sort.Direction.ASC,"firstName"));
List<Customer> result3 = repository.findByName4("Bauer",new Sort(orders));
for (Customer customer:result3){
System.out.println(customer.toString());
}
System.out.println("-------------------------------------------");
}

这里总共列举了四种排序方式:

1)直接创建Sort对象,适合对单一属性做排序
2)通过Sort.Order对象创建Sort对象,适合对单一属性做排序
3)通过属性的List集合创建Sort对象,适合对多个属性,采取同一种排序方式的排序
4)通过Sort.Order对象的List集合创建Sort对象,适合所有情况,比较容易设置排序方式


对应着我们的使用场景来进行选择创建Sort对象的方式。

注意,这里并没有列举所有的Sort使用方式,还有忽略大小写,使用JpaSort.unsafe、聚合函数等进行排序,查询的属性值是Entity的属性名,不是数据库的字段,要注意到!!
 
更多用法,请参考源码:http://www.icnws.com/wp-content/uploads/2017/06/Sort.java_.txt
 
 
 参考:
官方文档,https://docs.spring.io/spring- ... /html 
DEMO,https://github.com/icnws/spring-data-jpa-demo
个人博客,http://www.icnws.com