单数据源的配置很简单,就是在yml或者properties文件中配置一下就行,以下示例是yml文件单数据源mysql的配置(配置了阿里的druid数据库连接池):
spring
datasource
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/test
username: admin
password: 123456
driver-class-name: com.mysql.jdbc.Driver
第一种方式
在yml或者properties文件配置一下以及创建两个java配置文件分别配置defaultDb和secondDb,以下是示例代码:
yml中
spring
datasource
defaultDb:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/test
username: admin
password: 123456
driver-class-name: com.mysql.jdbc.Driver
secondDb:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:oracle.thin:@localhost:1521/test1
username: admin
password: 123456
driver-class-name: oracle.jdbc.driver.OracleDriver
一个数据源对应一个配置文件。
DefaultDBConfigurer
@Data //lombok插件
@Configuration
//mapper的包扫描以及SqlSessionFactory映射(这个不配置的话从数据源使用不了)
@MapperScan(basePackages = "com.study.mapper.default", sqlSessionFactoryRef = "defaultSqlSessionFactory")
@ConfigurationProperties(prefix = "spring.datasource.defaultDb")
public class DefaultMysqlConfig {
private String url;
private String username;
private String password;
private String driverClass;
private String mapperLocations;
@Bean("defaultDataSource")
@Primary
public DataSource defaultDataSource(){
return DataSourceBuilder.create().url(url)
.username(username)
.password(password)
.driverClassName(driverClass)
.build();
}
@Bean("defaultSqlSessionFactory")
@Primary
public SqlSessionFactory defaultSqlSessionFactory(@Qualifier("defaultDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations();
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
return sqlSessionFactoryBean.getObject();
}
@Bean("defaultTransactionManager")
@Primary
public DataSourceTransactionManager defaultTransactionManager(@Qualifier("defaultDataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean("defaultSqlSessionTemplate")
@Primary
public SqlSessionTemplate defaultSqlSessionTemplate(@Qualifier("defaultSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
SecondDbConfigurer.java
@Data
@Configuration
@MapperScan(basePackages = "com.study.mapper.second", sqlSessionFactoryRef = "secondSqlSessionFactory")
@ConfigurationProperties(prefix = "spring.datasource.secondDb")
public class SecondMysqlConfig {
private String url;
private String username;
private String password;
private String driverClass;
private String mapperLocations;
@Bean("secondDataSource")
public DataSource secondDataSource(){
return DataSourceBuilder.create().url(url)
.username(username)
.password(password)
.driverClassName(driverClass)
.build();
}
@Bean("secondSqlSessionFactory")
public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations();
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
return sqlSessionFactoryBean.getObject();
}
@Bean("secondTransactionManager")
public DataSourceTransactionManager secondTransactionManager(@Qualifier("secondDataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean("secondSqlSessionTemplate")
public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
之后你就可以当作一个数据源一样正常地通过mybatsi映射来操作数据库了,加了@Primary注解的数据源是默认数据源。
第二种方式
配置多个JdbcTemplate。在yml文件中把变量值设置了:
spring
datasource
firstDb:
url: jdbc:mysql://localhost:3306/test
username: admin
password: 123456
driver-class-name: com.mysql.jdbc.Driver
secondDb:
url: jdbc:oracle.thin:@localhost:1521/test1
username: admin
password: 123456
driver-class-name: oracle.jdbc.driver.OracleDriver
然后创建一个JdbcLinkConfig.java文件:
@Configuration
public class JdbcLinkConfig {
//创建两个数据源
@Bean(name="firstDataSource")
@Qualifier("firstDataSource")
@ConfigurationProperties(prefix="spring.datasource.firstDb")
public DataSource firstDataSource() {
return DatasourceBuilder.create().build();
}
@Bean(name="secondDataSource")
@Qualifier("secondDataSource")
@Primary
@ConfigurationProperties(prefix="spring.datasource.secondDb")
public DataSource secondDataSource() {
return DatasourceBuilder.create().build();
}
//根据两个数据源创建两个jdbcTemplate
@Bean(name="firstJdbcTemplate")
public JdbcTemplate firstJdbcTemplate(@Qualifier("firstDataSource")DataSource
datasource){
return new JdbcTemplate(datasource);
}
@Bean(name="secondJdbcTemplate")
public JdbcTemplate firstJdbcTemplate(@Qualifier("secondJdbcTemplate")DataSource
datasource){
return new JdbcTemplate(datasource);
}
}
之后你需要操作哪个数据源你就注入哪个数据源对应的jdbcTemplate操作就行。
第三种方式
几个数据源就创建几个连接工厂和对应的操作类。这里就创建一个为例,其他的照猫画葫芦就行。
创建FirstFactory.java
public class FirstFactory {
private static final Logger logger = Logger.getLogger(FirstFactory.class);
private static final FirstFactory factory = new FirstFactory();
private static DataSource datasource = null;
public static FirstFactory getInstance() {
return factory;
}
//获取数据库连接
public Connection getConnection() throw SQLException {
if(null == dataSource) {
configDataSource();
}
return dataSource.getConnection();
}
//配置数据源
private static void configDataSource() {
try{
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUsername("admin");
druidDataSource.setPassword("123456");
druidDataSource.setUrl("jdbc:mysql://localhost:3306/test");
datasource = druidDataSource;
} catch (Exception e) {
logger.error("First数据库配置异常:" + e.getMessage);
e.printStackTrace();
}
}
}
创建FirstQuery.java
这个类就是包装了一些对数据库的一些增删改查的操作。这里就简单写几个常用的。
public class FirstQuery {
private static final Logger logger = Logger.getLogger(FirstQuery.class);
private FirstFactory fst = null;
public FirstQuery() {
fst = FirstFactory.getInstance();
}
//基于apache dbutils封装查询方法获取查询实例
private QueryRunner getRunner() {
return new QueryRunner(true);
}
//设置java原始类型
private static List<Class<?>> primitiveClasses = new ArrayList<Class<?>>();
static {
primitiveClasses.add(Long.class);
primitiveClasses.add(Integer.class);
primitiveClasses.add(String.class);
primitiveClasses.add(java.util.Date.class);
primitiveClasses.add(java.sql.Date.class);
primitiveClasses.add(java.sql.Timestamp.class);
}
//获取数据库连接
public Connection getConnection() throws SQLException {
return this.fst.getConnection();
}
//创建表、删除数据、插入数据、修改数据--不需要额外参数的
public void executeUpdate(String sql) throws SQLException {
Connection conn = null;
Statement stmt = null;
try {
conn = this.getConnection();
stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catach (SQLException e) {
throw e;
} finally {
if(stmt != null){
this.close(conn, stmt, null);
}
}
}
//创建表、删除数据、插入数据、修改数据--需要额外参数的
public void executeUpdate(String sql, Object... params) throws SQLException {
Connection conn = null;
try{
conn = this.getConnection();
this.getRunner().update(conn, sql, params);
} catch (SQLException e) {
this.rollbackt(conn);
throw e;
} finally {
this.close(conn);
}
}
//根据参数查询单条数据里面的一些字段,无参的话params为new Object[]{}
public Map<String, Object> queryMap(String sql, Object... params) {
Map<String, Object> ret = null;
Connection conn = null;
try{
conn = this.getConnection();
ret = this.getRunner().query(conn, sql, new MapHandler(), params);
} catach (SQLException e) {
logger.info("DBUtil execute sql: " + sql);
} finally {
this.close(conn);
}
return ret;
}
//根据参数查询多条数据里面的一些字段,无参的话params为new Object[]{}
public Map<String, Object> queryMapList(String sql, Object... params) {
List<Map<String, Object>> ret = null;
Connection conn = null;
try{
conn = this.getConnection();
ret = this.getRunner().query(conn, sql, new MapHandler(), params);
} catach (SQLException e) {
logger.info("DBUtil execute sql: " + sql);
} finally {
this.close(conn);
}
return ret;
}
//根据参数查询单条数据(多条数据参考上面把ret的类型改成List<T>就行)并转换成指定对象,无参的
//话params为new Object[]{}.
public <T> T queryBean(Class<T> beanClass, String sql, Object... params) {
T ret = null;
Connection conn = null;
try{
conn = this.getConnection();
ret = this.getRunner().query(conn, sql, isPrimitive(beanClass)?new
ScalarHandler<T>(1): new BeanHandler<T>(beanClass), params);
} catch (SQLException e){
logger.info("DBUtil execute sql: " + sql);
rollback(conn);
} finally {
close(conn);
}
}
//根据参数查询个数,无参的话params为new Object[]{}
public Long queryCount(String sql, Object... params) {
return this.queryBean(Long.class, sql, params);
}
//关闭数据库连接
public void close(Connection conn){
DbUtils.closeQuietly(conn);
}
//关闭所有链接
public void close(Connection conn, Statement stmt, Result rs) {
DbUtils.closeQuietly(conn, stmt, rs);
}
//数据库操作回滚
public void rollback(Connection conn) {
try{
DbUtils.rollback(conn);
} catch(SQLException e) {
e.printStackTrace();
}
}
private boolean isPrimitive(Class<?> cla) {
return cla.isPrimitive() || primitiveClasses.contains(cla);
}
}
关于这两个java类,我们可以写两个他们的父接口,这样再配置数据源的时候 ,直接实现父接口就行。
总结
第一种方法可以让所有的数据源使用mybatis映射,而第二第三种就得需要在java文件里面写sql语句,关闭连接啥的。个人比较中意第一种方法~~
有几点要注意一下:
1.上面关于数据库连接的配置,只是一个样例,具体得看你自己的数据库信息。
2.第一种方法中java配置文件中有两个路径的配置,第一个是xml映射文件的路径,第二个是对应的java文件的路径。
3.这也是我第一次写多数据源,如果有啥说的不对的或者遇到啥问题,欢迎点评建议询问~