一个动态数据库路由实现
需求:
业务库按机构+应用进行了分库,业务系统要求能根据机构+应用切换数据库连接.
实现:
系统将所有业务库的数据连接信息保存到一个路由数据库中的路由表里.
程序在需要连接具体的业务库时,可以查询路由表得到连接信息,并建立连接.
知识点:
spring JdbcTemplate JavaConfig
首先定义一个助手接口JDBCRouteHelper,提供给业务类使用.
下面是一个业务类使用JDBCRouteHelper查询业务库的方式:
1 @Autowired 2 JDBCRouteHelper jdbcRouteHelper; 3 4 @Override 5 public Map<String, Object> getItem(Map<String, Object> inParam) { 6 if (MapUtil.getValue("id", inParam) == "") 7 throw new InParamCheckException("id不能为空:" + inParam.get("id")); 8 String orgId = MapUtil.getValue("org_id", inParam); 9 String appId = IDConstants.APP_ID_PIS; 10 NamedParameterJdbcTemplate jdbc = jdbcRouteHelper.getJDBCTemplate(orgId, appId); 11 12 return new MyMap().put("simple_info", jdbc.query("select * from simple_info where id=:id", inParam, new HashMapRowMapper())) 13 .put("simple_test", 14 jdbc.query("select * from simple_test where simple_info_id=:id", inParam, new HashMapRowMapper())) 15 .getMap(); 16 }
1 public interface JDBCRouteHelper { 2 3 /** 4 * @param jdbcRouteTemplate 5 * 设置路由配置数据库的连接 6 */ 7 void setRouteJdbc(NamedParameterJdbcTemplate jdbcRouteTemplate); 8 9 /** 10 * 11 * @param orgId 12 * @param appId 13 * @return 14 * 得到指定机构与指定应用对应的数据库操作模板类 15 */ 16 NamedParameterJdbcTemplate getJDBCTemplate(String orgId, String appId); 17 18 /** 19 * @param orgId 20 * @param appId 21 * @return 22 * 得到指定机构与指定应用对应的数据库事务模版类 23 */ 24 public TransactionTemplate getTransactionTemplate(String orgId, String appId); 25 26 }
接口实现类
public class JDBCRouteHelperImp implements JDBCRouteHelper { //缓存所有业务数据库的jdbc模版 private final static Map<String, NamedParameterJdbcTemplate> mapJdbc = new ConcurrentHashMap<>(); //缓存所有业务数据库的datasource private final static Map<String, DataSource> mapDataSource = new ConcurrentHashMap<>(); //缓存所有业务数据库的事务模版 private final static Map<String, TransactionTemplate> mapTx = new ConcurrentHashMap<>(); private final static Object lockDS = new Object(); private final static Object lockJDBC = new Object(); private final static Object lockTX = new Object(); //路由配置库的连接 NamedParameterJdbcTemplate jdbcRouteTemplate; //查询路由配置库的连接属性 static String sqlGetDS = "select * from bas_datasource where " + "org_id=:orgId and app_id=:appId and dbs_type=1"; JDBCRouteHelperImp(NamedParameterJdbcTemplate jdbcRouteTemplate) { this.jdbcRouteTemplate = jdbcRouteTemplate; } @Override public void setRouteJdbc(NamedParameterJdbcTemplate jdbcRouteTemplate) { this.jdbcRouteTemplate = jdbcRouteTemplate; } @Override public NamedParameterJdbcTemplate getJDBCTemplate(String orgId, String appId) { String dbKey = orgId + "_" + appId; if (mapJdbc.get(dbKey) == null) { synchronized (lockJDBC) { if (mapJdbc.get(dbKey) == null) { DataSource dataSource = getDataSource(orgId, appId); NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource); mapJdbc.put(dbKey, jdbcTemplate); } } } return mapJdbc.get(dbKey); } @Override public TransactionTemplate getTransactionTemplate(String orgId, String appId) { String dbKey = orgId + "_" + appId; if (mapTx.get(dbKey) == null) { synchronized (lockTX) { if (mapTx.get(dbKey) == null) { DataSource dataSource = getDataSource(orgId, appId); DataSourceTransactionManager txManager = new DataSourceTransactionManager(dataSource); TransactionTemplate transactionTemplate = new TransactionTemplate(txManager); mapTx.put(dbKey, transactionTemplate); } } } return mapTx.get(dbKey); } /** * @param orgId * @param appId * @return * 得到指定机构与指定应用对应的DataSource */ private DataSource getDataSource(String orgId, String appId) { String dbKey = orgId + "_" + appId; Map<String, String> paramMap = new HashMap<>(); paramMap.put("orgId", orgId); paramMap.put("appId", appId); List<Map<String, Object>> lst = jdbcRouteTemplate.query(sqlGetDS, paramMap, new ColumnMapRowMapper()); if (!lst.isEmpty()) { Map<String, Object> dsItem = lst.get(0); BasicDataSource dataSource = new BasicDataSource(); dataSource.setDriverClassName((String) dsItem.get("DBS_DriverClassName")); dataSource.setUrl((String) dsItem.get("DBS_URL")); dataSource.setUsername((String) dsItem.get("DBS_UserName")); dataSource.setPassword((String) dsItem.get("DBS_Password")); mapDataSource.put(dbKey, dataSource); } else { throw new RuntimeException("在配置库中没有发现数据库连接配置:orgId:[" + orgId + "] appId:" + appId); } return mapDataSource.get(dbKey); } }
通过spring 的JavaConfig将实现类放入容器
@Configuration @PropertySource("classpath:mysql_route.properties") public class JDBCRouteConfig { @Autowired Environment env; @Bean("jdbcRouteHelper") JDBCRouteHelper routeHelper() { BasicDataSource dsRoute = new BasicDataSource(); dsRoute.setDriverClassName(env.getProperty("mysql.driverClassName")); dsRoute.setUrl(env.getProperty("mysql.url")); dsRoute.setUsername(env.getProperty("mysql.username")); dsRoute.setPassword(env.getProperty("mysql.password")); NamedParameterJdbcTemplate jdbcRouteTemplate = new NamedParameterJdbcTemplate(dsRoute); return new JDBCRouteHelperImp(jdbcRouteTemplate); } }
业务系统加载helper的bean
1 public class App 2 { 3 public static void main( String[] args ) throws IOException 4 { 5 @SuppressWarnings("resource") 6 AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(); 7 ctx.register(JDBCRouteConfig.class); 8 ctx.refresh(); 9 System.out.println("任意键退出"); 10 System.in.read(); 11 } 12 }
如果是web容器,也可以在web.xml中配置加载:
<listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <context-param> <param-name>contextConfigLocation</param-name> <param-value>com.linsen.jbdc.JdbcRouteConfig </context-param> <context-param> <param-name>contextClass</param-name> <param-value>org.springframework.web.context.support.AnnotationConfigWebApplicationContext</param-value> </context-param>
这里只是展示实现机制,如果是用于开发框架,进一步封装可以不用业务方法直接调用获取jdbc模版类方法,而是有框架封装好,业务类直接注入框架组装好的jdbc模版类.实现思路是在每个请求的filter中获取具体业务库的jdbc模板类,并刷到spring容器中.
