博客

升级到SpringBoot2.0以上版本后，配置Jpa多数据源遇到的问题

  • Post author:
  • Post category:其他


最近在整理一个SpringBoot+有多数据源的Demo，方言有SQLite和Mysql，SpringBoot使用的版本是

<version>1.5.10.RELEASE</version>

，想升级2.0以上的版本，于是出现了一系列版本冲突问题，小问题就不贴了，只贴处理最久的一个Jpa多数据源配置问题，在网上记录了该问题解决方法的鲜有几人。


<version>1.5.10.RELEASE</version>

原代码

DataSourceConfiguration 类 

package com.xxxx.core.sqlite.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.sqlite.SQLiteDataSource;

/**
 * 配置sqlite数据库的DataSource
 * 
 */
@Configuration
public class DataSourceConfiguration {
	
	@Value("${sqlite.dbName}")
	private String dbName;

	@Bean(destroyMethod = "", name = "EmbeddeddataSource")
	public DataSource dataSource() {
		DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
		dataSourceBuilder.driverClassName("org.sqlite.JDBC");
		dataSourceBuilder.url("jdbc:sqlite:" + dbName);
		dataSourceBuilder.type(SQLiteDataSource.class);
		return dataSourceBuilder.build();
	}

}

JpaConfiguration 类 

package com.xxxx.core.sqlite.config;

import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.EntityManagerFactory;
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.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.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaRepositories(basePackages = "com.xxxx.core.repository", transactionManagerRef = "jpaTransactionManager", entityManagerFactoryRef = "localContainerEntityManagerFactoryBean")
@EnableTransactionManagement
public class JpaConfiguration {

	@Resource
	private JpaProperties jpaProperties;

	@Autowired
	@Bean
	public JpaTransactionManager jpaTransactionManager(@Qualifier(value = "EmbeddeddataSource") DataSource dataSource, EntityManagerFactory entityManagerFactory) {
		JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
		jpaTransactionManager.setEntityManagerFactory(entityManagerFactory);
		jpaTransactionManager.setDataSource(dataSource);

		return jpaTransactionManager;
	}

	@Autowired
	@Bean
	LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(@Qualifier(value = "EmbeddeddataSource") DataSource dataSource, EntityManagerFactoryBuilder builder) {
		return builder.dataSource(dataSource).packages("com.xxxx.core.model").properties(getVendorProperties(dataSource)).build();
	}

	private Map<String, String> getVendorProperties(DataSource dataSource) {
		return jpaProperties.getHibernateProperties(dataSource);
	}

}

ModelMetaData 类 

package com.xxxx.core.sqlite.config;

import java.util.HashMap;
import java.util.Map;

import javax.persistence.EntityManagerFactory;

import org.hibernate.SessionFactory;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.persister.entity.AbstractEntityPersister;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 构建一个存放表名和model实体class的对应关系，如member_entity:MemberEntity.class
 *
 */
@Configuration
@AutoConfigureAfter(JpaConfiguration.class)
public class ModelMetaData {

	@Bean(name = "metaMap")
	public Map<String, Class> metaMap(EntityManagerFactory factory) throws ClassNotFoundException {
		if (factory.unwrap(SessionFactory.class) == null) {
			throw new NullPointerException("factory is not a hibernate factory");
		}
		SessionFactory sessionFactory = factory.unwrap(SessionFactory.class);
		Map<String, ClassMetadata> metaMap = sessionFactory.getAllClassMetadata();
		Map<String, Class> map = new HashMap<>(metaMap.size());
		for (String key : metaMap.keySet()) {
			AbstractEntityPersister classMetadata = (AbstractEntityPersister) metaMap.get(key);
			String tableName = classMetadata.getTableName().toLowerCase();
			int index = tableName.indexOf(".");
			if (index >= 0) {
				tableName = tableName.substring(index + 1);
			}
			map.put(tableName, Class.forName(key));
		}
		return map;
	}

}

SQLiteDialect 类 

package com.xxxx.core.sqlite.config;

import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.JDBCException;
import org.hibernate.ScrollMode;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.AbstractAnsiTrimEmulationFunction;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.dialect.identity.IdentityColumnSupport;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitHelper;
import org.hibernate.dialect.unique.DefaultUniqueDelegate;
import org.hibernate.dialect.unique.UniqueDelegate;
import org.hibernate.engine.spi.RowSelection;
import org.hibernate.exception.DataException;
import org.hibernate.exception.JDBCConnectionException;
import org.hibernate.exception.LockAcquisitionException;
import org.hibernate.exception.spi.SQLExceptionConversionDelegate;
import org.hibernate.exception.spi.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.spi.ViolatedConstraintNameExtracter;
import org.hibernate.internal.util.JdbcExceptionHelper;
import org.hibernate.mapping.Column;
import org.hibernate.type.StandardBasicTypes;

import com.xxxx.core.sqlite.config.identity.SQLiteDialectIdentityColumnSupport;

/**
 * sqlite方言，目的是使用Hibernate jpa操作SQLite
 * 
 */
public class SQLiteDialect extends Dialect {

	private final UniqueDelegate uniqueDelegate;

	public SQLiteDialect() {
		registerColumnType(Types.BIT, "boolean");
		registerColumnType(Types.DECIMAL, "decimal");
		registerColumnType(Types.CHAR, "char");
		registerColumnType(Types.LONGVARCHAR, "longvarchar");
		registerColumnType(Types.TIMESTAMP, "datetime");
		registerColumnType(Types.BINARY, "blob");
		registerColumnType(Types.VARBINARY, "blob");
		registerColumnType(Types.LONGVARBINARY, "blob");

		registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", ""));
		registerFunction("mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2"));
		registerFunction("quote", new StandardSQLFunction("quote", StandardBasicTypes.STRING));
		registerFunction("random", new NoArgSQLFunction("random", StandardBasicTypes.INTEGER));
		registerFunction("round", new StandardSQLFunction("round"));
		registerFunction("substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
		registerFunction("trim", new AbstractAnsiTrimEmulationFunction() {
			@Override
			protected SQLFunction resolveBothSpaceTrimFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1)");
			}

			@Override
			protected SQLFunction resolveBothSpaceTrimFromFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?2)");
			}

			@Override
			protected SQLFunction resolveLeadingSpaceTrimFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1)");
			}

			@Override
			protected SQLFunction resolveTrailingSpaceTrimFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1)");
			}

			@Override
			protected SQLFunction resolveBothTrimFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1, ?2)");
			}

			@Override
			protected SQLFunction resolveLeadingTrimFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1, ?2)");
			}

			@Override
			protected SQLFunction resolveTrailingTrimFunction() {
				return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1, ?2)");
			}
		});
		uniqueDelegate = new SQLiteUniqueDelegate(this);
	}

	private static final SQLiteDialectIdentityColumnSupport IDENTITY_COLUMN_SUPPORT = new SQLiteDialectIdentityColumnSupport(new SQLiteDialect());

	@Override
	public IdentityColumnSupport getIdentityColumnSupport() {
		return IDENTITY_COLUMN_SUPPORT;
	}

	private static final AbstractLimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
		@Override
		public String processSql(String sql, RowSelection selection) {
			final boolean hasOffset = LimitHelper.hasFirstRow(selection);
			return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
		}

		@Override
		public boolean supportsLimit() {
			return true;
		}

		@Override
		public boolean bindLimitParametersInReverseOrder() {
			return true;
		}
	};

	@Override
	public LimitHandler getLimitHandler() {
		return LIMIT_HANDLER;
	}

	@Override
	public boolean supportsLockTimeouts() {
		return false;
	}

	@Override
	public String getForUpdateString() {
		return "";
	}

	@Override
	public boolean supportsOuterJoinForUpdate() {
		return false;
	}

	@Override
	public boolean supportsCurrentTimestampSelection() {
		return true;
	}

	@Override
	public boolean isCurrentTimestampSelectStringCallable() {
		return false;
	}

	@Override
	public String getCurrentTimestampSelectString() {
		return "select current_timestamp";
	}

	private static final int SQLITE_BUSY = 5;
	private static final int SQLITE_LOCKED = 6;
	private static final int SQLITE_IOERR = 10;
	private static final int SQLITE_CORRUPT = 11;
	private static final int SQLITE_NOTFOUND = 12;
	private static final int SQLITE_FULL = 13;
	private static final int SQLITE_CANTOPEN = 14;
	private static final int SQLITE_PROTOCOL = 15;
	private static final int SQLITE_TOOBIG = 18;
	private static final int SQLITE_CONSTRAINT = 19;
	private static final int SQLITE_MISMATCH = 20;
	private static final int SQLITE_NOTADB = 26;

	@Override
	public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
		return new SQLExceptionConversionDelegate() {
			@Override
			public JDBCException convert(SQLException sqlException, String message, String sql) {
				final int errorCode = JdbcExceptionHelper.extractErrorCode(sqlException) & 0xFF;
				if (errorCode == SQLITE_TOOBIG || errorCode == SQLITE_MISMATCH) {
					return new DataException(message, sqlException, sql);
				} else if (errorCode == SQLITE_BUSY || errorCode == SQLITE_LOCKED) {
					return new LockAcquisitionException(message, sqlException, sql);
				} else if ((errorCode >= SQLITE_IOERR && errorCode <= SQLITE_PROTOCOL) || errorCode == SQLITE_NOTADB) {
					return new JDBCConnectionException(message, sqlException, sql);
				}
				return null;
			}
		};
	}

	@Override
	public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
		return EXTRACTER;
	}

	private static final ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
		@Override
		protected String doExtractConstraintName(SQLException sqle) throws NumberFormatException {
			final int errorCode = JdbcExceptionHelper.extractErrorCode(sqle) & 0xFF;
			if (errorCode == SQLITE_CONSTRAINT) {
				return extractUsingTemplate("constraint ", " failed", sqle.getMessage());
			}
			return null;
		}
	};

	@Override
	public boolean supportsUnionAll() {
		return true;
	}

	@Override
	public boolean canCreateSchema() {
		return false;
	}

	@Override
	public boolean hasAlterTable() {
		return false;
	}

	@Override
	public boolean dropConstraints() {
		return false;
	}

	@Override
	public boolean qualifyIndexName() {
		return false;
	}

	@Override
	public String getAddColumnString() {
		return "add column";
	}

	@Override
	public String getDropForeignKeyString() {
		throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
	}

	@Override
	public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable, String[] primaryKey, boolean referencesPrimaryKey) {
		throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
	}

	@Override
	public String getAddPrimaryKeyConstraintString(String constraintName) {
		throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
	}

	@Override
	public boolean supportsCommentOn() {
		return true;
	}

	@Override
	public boolean supportsIfExistsBeforeTableName() {
		return true;
	}

	@Override
	public boolean doesReadCommittedCauseWritersToBlockReaders() {
		return true;
	}

	@Override
	public boolean doesRepeatableReadCauseReadersToBlockWriters() {
		return true;
	}

	@Override
	public boolean supportsTupleDistinctCounts() {
		return false;
	}

	@Override
	public int getInExpressionCountLimit() {
		return 1000;
	}

	@Override
	public UniqueDelegate getUniqueDelegate() {
		return uniqueDelegate;
	}

	private static class SQLiteUniqueDelegate extends DefaultUniqueDelegate {
		public SQLiteUniqueDelegate(Dialect dialect) {
			super(dialect);
		}

		@Override
		public String getColumnDefinitionUniquenessFragment(Column column) {
			return " unique";
		}
	}

	@Override
	public String getSelectGUIDString() {
		return "select hex(randomblob(16))";
	}

	@Override
	public ScrollMode defaultScrollMode() {
		return ScrollMode.FORWARD_ONLY;
	}
	
}

SQLite工具类 

package com.xxxx.core.sqlite.config;

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.spi.MetadataBuilderInitializer;
import org.hibernate.engine.jdbc.dialect.internal.DialectResolverSet;
import org.hibernate.engine.jdbc.dialect.spi.DialectResolver;
import org.jboss.logging.Logger;

/**
 * SQLite工具
 * 
 */
public class SQLiteMetadataBuilderInitializer implements MetadataBuilderInitializer {

	private final static Logger logger = Logger.getLogger(SQLiteMetadataBuilderInitializer.class);

	@Override
	public void contribute(MetadataBuilder metadataBuilder, StandardServiceRegistry serviceRegistry) {
		DialectResolver dialectResolver = serviceRegistry.getService(DialectResolver.class);

		if (!(dialectResolver instanceof DialectResolverSet)) {
			logger.warnf("DialectResolver '%s' is not an instance of DialectResolverSet, not registering SQLiteDialect", dialectResolver);
			return;
		}

		((DialectResolverSet) dialectResolver).addResolver(resolver);
	}

	static private final SQLiteDialect dialect = new SQLiteDialect();

	static private final DialectResolver resolver = (DialectResolver) info -> {
		if (info.getDatabaseName().equals("SQLite")) {
			return dialect;
		}
		return null;
	};
	
}

SQLiteDialectIdentityColumnSupport类 

package com.xxxx.core.sqlite.config.identity;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.identity.IdentityColumnSupportImpl;

public class SQLiteDialectIdentityColumnSupport extends IdentityColumnSupportImpl {
	
	public SQLiteDialectIdentityColumnSupport(Dialect dialect) {
		super(dialect);
	}

	@Override
	public boolean supportsIdentityColumns() {
		return true;
	}

	@Override
	public boolean hasDataTypeInIdentityColumn() {
		return false;
	}

	@Override
	public String getIdentitySelectString(String table, String column, int type) {
		return "select last_insert_rowid()";
	}

	@Override
	public String getIdentityColumnString(int type) {
		return "integer";
	}
	
}

升级SpringBoot

<version>2.0.5.RELEASE</version>

后，主要出现三个问题：

1.SQLiteDialectIdentityColumnSupport类的构造方法 

public SQLiteDialectIdentityColumnSupport(Dialect dialect) {
	super(dialect);
}

问题出现原因：


SpringBoot2.0后将父类IdentityColumnSupportImpl对方言初始化的的构造方法移除

因此该段直接改为 

public SQLiteDialectIdentityColumnSupport() {
	super();
}

2.JPA配置类JpaConfiguration 

private Map<String, String> getVendorProperties(DataSource dataSource) {
    return jpaProperties.getHibernateProperties(dataSource);
}

问题出现原因：

不明，貌似SpringBoot2.0对JpaProperties类做了重要改动，我也打不开底层jar，网上没有找到好的解释。

直接贴修改后的JpaConfiguration类 

package com.xxxx.core.sqlite.config;

import java.util.Map;

import javax.annotation.Resource;
import javax.persistence.EntityManagerFactory;
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.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaRepositories(basePackages = "com.xxxx.core.repository", transactionManagerRef = "jpaTransactionManager", entityManagerFactoryRef = "localContainerEntityManagerFactoryBean")
@EnableTransactionManagement
public class JpaConfiguration {
	
    /**
     * 注入 sqlite数据源
     */
    @Resource(name = "EmbeddeddataSource")
    private DataSource sqliteDataSource;


	@Resource
	private JpaProperties jpaProperties;

	@Autowired
	@Bean
	public JpaTransactionManager jpaTransactionManager(@Qualifier(value = "EmbeddeddataSource") DataSource dataSource, EntityManagerFactory entityManagerFactory) {
		JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
		jpaTransactionManager.setEntityManagerFactory(entityManagerFactory);
		jpaTransactionManager.setDataSource(dataSource);

		return jpaTransactionManager;
	}
	
	private Map<String, Object> getVendorProperties() {
		HibernateSettings hibernateSettings = new HibernateSettings();
		return jpaProperties.getHibernateProperties(hibernateSettings);
	}

	/**
	 * 配置EntityManagerFactory实体
	 *
	 * @param builder
	 * @return 实体管理工厂 packages 扫描@Entity注释的软件包名称 persistenceUnit 持久性单元的名称。
	 *         如果只建立一个EntityManagerFactory，你可以省略这个，但是如果在同一个应用程序中有多个，你应该给它们不同的名字
	 *         properties 标准JPA或供应商特定配置的通用属性。 这些属性覆盖构造函数中提供的任何值。
	 */
	@Autowired
	@Bean
	LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
		return builder.dataSource(sqliteDataSource).packages("com.xxxx.core.model").properties(getVendorProperties()).build();
	}

}

3.构建一个存放表名和model实体class的对应关系，ModelMetaData类

出现问题：


SessionFactoryImpl的getAllClassMetadata()被移除


修改后的ModelMetaData类: 

package com.xxxx.core.sqlite.config;

import java.util.HashMap;
import java.util.Map;

import javax.persistence.EntityManagerFactory;

import org.hibernate.SessionFactory;
import org.hibernate.internal.SessionFactoryImpl;
import org.hibernate.persister.entity.EntityPersister;
import org.hibernate.persister.entity.SingleTableEntityPersister;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 构建一个存放表名和model实体class的对应关系，如member_entity:MemberEntity.class
 *
 */
@Configuration
@AutoConfigureAfter(JpaConfiguration.class)
public class ModelMetaData {

	@SuppressWarnings({ "rawtypes", "deprecation" })
	@Bean(name = "metaMap")
	public Map<String, Class> metaMap(EntityManagerFactory factory) throws ClassNotFoundException {
		if (factory.unwrap(SessionFactory.class) == null) {
			throw new NullPointerException("factory is not a hibernate factory");
		}
		SessionFactoryImpl sessionFactory = (SessionFactoryImpl) factory.unwrap(SessionFactory.class);
		Map<String, EntityPersister> persisterMap = sessionFactory.getEntityPersisters();
        Map<String, Class> map = new HashMap<>();
		for (Map.Entry<String, EntityPersister> entity : persisterMap.entrySet()) {
			Class targetClass = entity.getValue().getMappedClass();
			SingleTableEntityPersister persister = (SingleTableEntityPersister) entity.getValue();
			String tableName = persister.getTableName();// Entity对应的表的英文名
			map.put(tableName, targetClass);
		}
		return map;
	}

}



注：

SessionFactoryImpl的getAllClassMetadata()方法目前也被注解@Deprecated，但仍可以使用。


版权声明：本文为weixin_43868594原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。