您好,登錄后才能下訂單哦!
本文源碼: GitHub·點這里 || GitEE·點這里
如果經常接觸數(shù)據開發(fā),會有這樣一個場景,服務A提供一個數(shù)據源,假設稱為動態(tài)數(shù)據源A,需要讀取該數(shù)據源下的數(shù)據;服務B提供一個數(shù)據源,假設稱為動態(tài)數(shù)據源B,需要寫入數(shù)據到該數(shù)據源。這個場景通常描述為數(shù)據同步,或者數(shù)據搬運。
基于上述流程圖,整體步驟如下:
Java中JDBC下執(zhí)行數(shù)據庫操作的一個重要接口,在已經建立數(shù)據庫連接的基礎上,向數(shù)據庫發(fā)送要執(zhí)行的SQL語句。
繼承Statement接口,且實現(xiàn)SQL預編譯,可以提高批量處理效率。常應用于批量數(shù)據寫入場景。
存儲JDBC查詢結果集的對象,ResultSet接口提供從當前行檢索列值的方法。
提供一個數(shù)據源管理的Factory,當前場景下主要管理一個讀庫即數(shù)據源A,和一個寫庫即數(shù)據源B,數(shù)據源連接驗證通過,放入容器中。
@Component
public class ConnectionFactory {
private volatile Map<String, Connection> connectionMap = new HashMap<>();
@Resource
private JdbcConfig jdbcConfig ;
@PostConstruct
public void init (){
ConnectionEntity read = new ConnectionEntity(
"MySql","jdbc:mysql://localhost:3306/data_read","user01","123");
if (jdbcConfig.getConnection(read) != null){
connectionMap.put(JdbcConstant.READ,jdbcConfig.getConnection(read));
}
ConnectionEntity write = new ConnectionEntity(
"MySql","jdbc:mysql://localhost:3306/data_write","user01","123");
if (jdbcConfig.getConnection(write) != null){
connectionMap.put(JdbcConstant.WRITE,jdbcConfig.getConnection(write));
}
}
public Connection getByKey (final String key){
return connectionMap.get(key) ;
}
}
基礎SQL管理
主要提供SQL的基礎模板,例如全表查,分頁查,表結構查詢。
public class BaseSql {
public static String READ_SQL = "SELECT * FROM %s LIMIT 1";
public static String WRITE_SQL = "INSERT INTO %s (SELECT * FROM %s WHERE 1=0)" ;
public static String CREATE_SQL = "SHOW CREATE TABLE %s" ;
public static String SELECT_SQL = "SELECT * FROM %s" ;
public static String COUNT_SQL = "SELECT COUNT(1) countNum FROM %s" ;
public static String PAGE_SQL = "SELECT * FROM %s LIMIT %s,%s" ;
public static String STRUCT_SQL (){
StringBuffer sql = new StringBuffer() ;
sql.append(" SELECT ");
sql.append(" COLUMN_NAME, ");
sql.append(" IS_NULLABLE, ");
sql.append(" COLUMN_TYPE, ");
sql.append(" COLUMN_KEY, ");
sql.append(" COLUMN_COMMENT ");
sql.append(" FROM ");
sql.append(" information_schema.COLUMNS ");
sql.append(" WHERE ");
sql.append(" table_schema = '%s' ");
sql.append(" AND table_name = '%s' ");
return String.valueOf(sql) ;
}
}
SQL參數(shù)拼接
根據SQL模板中缺失的參數(shù),進行動態(tài)補全,生成完成SQL語句。
public class BuildSql {
/**
* 讀權限SQL
*/
public static String buildReadSql(String table) {
String readSql = null ;
if (StringUtils.isNotEmpty(table)){
readSql = String.format(BaseSql.READ_SQL, table);
}
return readSql;
}
/**
* 讀權限SQL
*/
public static String buildWriteSql(String table){
String writeSql = null ;
if (StringUtils.isNotEmpty(table)){
writeSql = String.format(BaseSql.WRITE_SQL, table,table);
}
return writeSql ;
}
/**
* 表創(chuàng)建SQL
*/
public static String buildStructSql (String table){
String structSql = null ;
if (StringUtils.isNotEmpty(table)){
structSql = String.format(BaseSql.CREATE_SQL, table);
}
return structSql ;
}
/**
* 表結構SQL
*/
public static String buildTableSql (String schema,String table){
String structSql = null ;
if (StringUtils.isNotEmpty(table)){
structSql = String.format(BaseSql.STRUCT_SQL(), schema,table);
}
return structSql ;
}
/**
* 全表查詢SQL
*/
public static String buildSelectSql (String table){
String selectSql = null ;
if (StringUtils.isNotEmpty(table)){
selectSql = String.format(BaseSql.SELECT_SQL,table);
}
return selectSql ;
}
/**
* 總數(shù)查詢SQL
*/
public static String buildCountSql (String table){
String countSql = null ;
if (StringUtils.isNotEmpty(table)){
countSql = String.format(BaseSql.COUNT_SQL,table);
}
return countSql ;
}
/**
* 分頁查詢SQL
*/
public static String buildPageSql (String table,int offset,int size){
String pageSql = null ;
if (StringUtils.isNotEmpty(table)){
pageSql = String.format(BaseSql.PAGE_SQL,table,offset,size);
}
return pageSql ;
}
}
讀庫嘗試一次單條數(shù)據讀取,寫庫嘗試一次不成立條件的寫入,如果沒有權限,會拋出相應異常。
@RestController
public class CheckController {
@Resource
private ConnectionFactory connectionFactory ;
// MySQLSyntaxErrorException: SELECT command denied to user
@GetMapping("/checkRead")
public String checkRead (){
try {
String sql = BuildSql.buildReadSql("rw_read") ;
ExecuteSqlUtil.query(connectionFactory.getByKey(JdbcConstant.READ),sql) ;
return "success" ;
} catch (SQLException e) {
e.printStackTrace();
}
return "fail" ;
}
// MySQLSyntaxErrorException: INSERT command denied to user
@GetMapping("/checkWrite")
public String checkWrite (){
try {
String sql = BuildSql.buildWriteSql("rw_read") ;
ExecuteSqlUtil.update(connectionFactory.getByKey(JdbcConstant.WRITE),sql) ;
return "success" ;
} catch (SQLException e) {
e.printStackTrace();
}
return "fail" ;
}
}
這里執(zhí)行最簡單操作,把讀庫表創(chuàng)建語句查詢出來,丟到寫庫中執(zhí)行。
@RestController
public class StructController {
@Resource
private ConnectionFactory connectionFactory ;
@GetMapping("/syncStruct")
public String syncStruct (){
try {
String sql = BuildSql.buildStructSql("rw_read") ;
ResultSet resultSet = ExecuteSqlUtil.query(connectionFactory.getByKey(JdbcConstant.READ),sql) ;
String createTableSql = null ;
while (resultSet.next()){
createTableSql = resultSet.getString("Create Table") ;
}
if (StringUtils.isNotEmpty(createTableSql)){
ExecuteSqlUtil.update(connectionFactory.getByKey(JdbcConstant.WRITE),createTableSql) ;
}
return "success" ;
} catch (SQLException e) {
e.printStackTrace();
}
return "fail" ;
}
}
讀庫的表數(shù)據讀取,批量放入寫庫中。這里特別說一個方法:statement.setObject();在不知道參數(shù)個數(shù)和類型時,自動適配數(shù)據類型。
@RestController
public class DataSyncController {
@Resource
private ConnectionFactory connectionFactory ;
@GetMapping("/dataSync")
public List<RwReadEntity> dataSync (){
List<RwReadEntity> rwReadEntities = new ArrayList<>() ;
try {
Connection readConnection = connectionFactory.getByKey(JdbcConstant.READ) ;
String sql = BuildSql.buildSelectSql("rw_read") ;
ResultSet resultSet = ExecuteSqlUtil.query(readConnection,sql) ;
while (resultSet.next()){
RwReadEntity rwReadEntity = new RwReadEntity() ;
rwReadEntity.setId(resultSet.getInt("id"));
rwReadEntity.setSign(resultSet.getString("sign"));
rwReadEntities.add(rwReadEntity) ;
}
if (rwReadEntities.size() > 0){
Connection writeConnection = connectionFactory.getByKey(JdbcConstant.WRITE) ;
writeConnection.setAutoCommit(false);
PreparedStatement statement = writeConnection.prepareStatement("INSERT INTO rw_read VALUES(?,?)");
// 基于動態(tài)獲取列,和statement.setObject();自動適配數(shù)據類型
for (int i = 0 ; i < rwReadEntities.size() ; i++){
RwReadEntity rwReadEntity = rwReadEntities.get(i) ;
statement.setInt(1,rwReadEntity.getId()) ;
statement.setString(2,rwReadEntity.getSign()) ;
statement.addBatch();
if (i>0 && i%2==0){
statement.executeBatch() ;
}
}
// 處理最后一批數(shù)據
statement.executeBatch();
writeConnection.commit();
}
return rwReadEntities ;
} catch (SQLException e) {
e.printStackTrace();
}
return null ;
}
}
提供一個分頁查詢工具,在數(shù)據量大的情況下不能一次性讀取大量的數(shù)據,避免資源占用過高。
public class PageUtilEntity {
/**
* 分頁生成方法
*/
public static PageHelperEntity<Object> pageResult (int total, int pageSize,int currentPage, List dataList){
PageHelperEntity<Object> pageBean = new PageHelperEntity<Object>();
// 總頁數(shù)
int totalPage = PageHelperEntity.countTotalPage(pageSize,total) ;
// 分頁列表
List<Integer> pageList = PageHelperEntity.pageList(currentPage,pageSize,total) ;
// 上一頁
int prevPage = 0 ;
if (currentPage==1){
prevPage = currentPage ;
} else if (currentPage>1&¤tPage<=totalPage){
prevPage = currentPage -1 ;
}
// 下一頁
int nextPage =0 ;
if (totalPage==1){
nextPage = currentPage ;
} else if (currentPage<=totalPage-1){
nextPage = currentPage+1 ;
}
pageBean.setDataList(dataList);
pageBean.setTotal(total);
pageBean.setPageSize(pageSize);
pageBean.setCurrentPage(currentPage);
pageBean.setTotalPage(totalPage);
pageBean.setPageList(pageList);
pageBean.setPrevPage(prevPage);
pageBean.setNextPage(nextPage);
pageBean.initjudge();
return pageBean ;
}
}
很多復雜度偏高的業(yè)務,越是需要借助基礎API解決,因為復雜度高,不容易抽象化統(tǒng)一封裝,如果數(shù)據同步這塊業(yè)務,可以適配多種數(shù)據庫,完全可以獨立封裝為中間件,開源項目中關于多方數(shù)據同步或計算的中間件也有好多,可以自行了解下,增長眼界開闊思路。
GitHub·地址
https://github.com/cicadasmile/data-manage-parent
GitEE·地址
https://gitee.com/cicadasmile/data-manage-parent
推薦相關閱讀 |
---|
數(shù)據源管理:主從庫動態(tài)路由,AOP模式讀寫分離 |
數(shù)據源管理:基于JDBC模式,適配和管理動態(tài)數(shù)據源 |
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。