多數據庫 · BeetlSQL3 官方使用手冊

## 多數據庫可能是BeetlSQL最不好理解的部分，然而，應該還是比其他DAO工具更容易實現和理解 ```java /** * <ui> * <li> * 演示多數據源操作中的ConditionalSQLManager，按照條件決定使用哪個SQLManager * ConditionalSQLManager.decide方法決定使用哪個SQLManager， * decide默認會讀取目標對象的TargetSQLManager注解來決定，SQLManager的有些api參數沒有目標對象，則使用默認SQLManager * </li> * <li> * 演示user分表操作，動態表名實現分表 * </li> * <li> * 演示user分庫操作，根據條件決定數據訪問哪個數據庫，使用了{@link ConditionalConnectionSource} * </li> * </ui> * * * 注意：分庫分表最好使用中間件 * @author xiandafu */ public class S6MoreDatabase { public S6MoreDatabase() { } public static void main(String[] args) throws Exception { S6MoreDatabase moreSource = new S6MoreDatabase(); moreSource.conditional(); moreSource.masterSlave(); moreSource.multipleTables(); moreSource.multipleDataBaseAndTables(); } /** * 多數據源協作 */ public void conditional() { SQLManager a = SampleHelper.init(); SQLManager b = SampleHelper.init(); Map<String, SQLManager> map = new HashMap<>(); map.put("a", a); map.put("b", b); SQLManager sqlManager = new ConditionalSQLManager(a, map); //不同實體，用不同sqlManager操作，存入不同的數據庫 UserData user = new UserData(); user.setName("hello"); user.setDepartmentId(2); sqlManager.insert(user); DepartmentData dept = new DepartmentData(); dept.setName("dept"); sqlManager.insert(dept); } /** * 普通一主多從 */ public void masterSlave(){ //為了簡單起見，主從庫都走同一個數據庫 DataSource master = SampleHelper.mysqlDatasource(); DataSource slave1 = SampleHelper.mysqlDatasource(); DataSource slave2 = SampleHelper.mysqlDatasource(); ConnectionSource source = ConnectionSourceHelper.getMasterSlave(master,new DataSource[]{slave1,slave2}); SQLManagerBuilder builder = new SQLManagerBuilder(source); builder.setNc(new UnderlinedNameConversion()); builder.setInters(new Interceptor[]{new DebugInterceptor()}); builder.setDbStyle(new MySqlStyle()); SQLManager sqlManager = builder.build(); //更新操作走主庫 UserData user = new UserData(); user.setName("a"); user.setDepartmentId(1); sqlManager.insert(user); //查詢走從庫 sqlManager.unique(UserData.class,1); } /** * 單庫分表操作,user對象的{@code @Table}注解是邏輯表達式 * <pre>{@code * @Table(name="${toTable('user',id)}" * public class User{ * * } * }</pre> * toTable方法是一個自定義注冊的beetl方法，在運行的時候會根據id換算出真實表 * * 對于beetlsql所有內置方法，都可以自動分表，但你自己的sql，也要類似使用 * {@code ${toTable('user',id)}} * @see TableChoice */ public void multipleTables(){ SQLManager sqlManager = getSQLManager4MultipleTables(); //使用user表 sqlManager.deleteById(MyUser.class,199); MyUser user = new MyUser(); user.setName("abc"); user.setId(199); sqlManager.insert(user); //使用user_1表. 為了簡單起見，分表邏輯返回的目標表還是user表 MyUser user2 = new MyUser(); user2.setName("abc"); user2.setId(1500); sqlManager.insert(user2); } /** * 分庫分布表操作，同{@link #multipleTables()} 方法，但增加如果id超過一定限額，走另外一個數據庫 * 核心還是需要定義一個分庫分表邏輯 * @see TableAndDataBaseChoice */ public void multipleDataBaseAndTables(){ SQLManager sqlManager = getSQLManager4MultipleDatBase(); sqlManager.deleteById(MyUser.class,199); MyUser user = new MyUser(); user.setName("abc"); user.setId(199); sqlManager.insert(user); //這條記錄使用第二個庫的user表 sqlManager.deleteById(MyUser.class,2900); MyUser user2 = new MyUser(); user2.setName("abc"); user2.setId(2900); sqlManager.insert(user2); } protected SQLManager getSQLManager4MultipleTables(){ SQLManager sqlManager = SampleHelper.getSqlManager(); //告訴sqlManager遇到USER_TABLE這個不存在的表不慌，他是個虛表，真實表是user sqlManager.addVirtualTable("user",USER_TABLE); BeetlTemplateEngine templateEngine = (BeetlTemplateEngine)sqlManager.getSqlTemplateEngine(); // 注冊一個方法來實現映射到多表的邏輯 templateEngine.getBeetl().getGroupTemplate().registerFunction("toTable", new Function(){ @Override public Object call(Object[] paras, Context ctx) { String tableName = (String)paras[0]; Integer id = (Integer)paras[1]; //使用分表邏輯 TableChoice tableChoice = new TableChoice(); return tableChoice.getTableName(tableName,id); } }); return sqlManager; } /** * 分表選擇邏輯 */ public static class TableChoice{ public String getTableName(String tableName,Integer id){ if(id<1000){ return tableName; }else{ //根據需要返回另外一個表，比如tableName+"_1" return tableName; // return tableName+"_1"; } } } /** * 分庫選擇邏輯，用戶自由實現分表分庫邏輯， */ public static class TableAndDataBaseChoice{ public String getTableName(ExecuteContext executeContext,String tableName,Integer id){ if(id<1000){ return tableName; }else if(id<2000){ return tableName+"_1"; }else{ //如果繼續大，設置一個標記，進入另外一個數據庫cs2庫的user表 executeContext.setContextPara(FLAG,"cs2"); if(id<3000){ return tableName; }else{ return tableName+"_1"; } } } } private static final String FLAG ="connectionSource"; protected SQLManager getSQLManager4MultipleDatBase(){ //為了測試方便，假設指向同一個數據庫 DataSource db1 = SampleHelper.mysqlDatasource(); ConnectionSource cs1 = ConnectionSourceHelper.getSingle(db1); DataSource db2 = SampleHelper.mysqlDatasource(); ConnectionSource cs2 = ConnectionSourceHelper.getSingle(db2); Map<String,ConnectionSource> datas = new HashMap<>(); datas.put("cs1",cs1); datas.put("cs2",cs2); // 配置策略 ConditionalConnectionSource.Policy policy = new ConditionalConnectionSource.Policy() { @Override public String getConnectionSourceName(ExecuteContext ctx, boolean isUpdate) { String name = (String)ctx.getContextPara(FLAG); if(name!=null){ return name; }else{ // 如果沒有設置，則返回一個默認庫 return "cs1"; } } @Override public String getMasterName() { return "cs1"; } }; ConditionalConnectionSource ds = new ConditionalConnectionSource(policy,datas); // 初始化sqlManager，使用ConditionalConnectionSource SQLManagerBuilder builder = new SQLManagerBuilder(ds); builder.setNc(new UnderlinedNameConversion()); builder.setInters(new Interceptor[]{new DebugInterceptor()}); builder.setDbStyle(new MySqlStyle()); SQLManager sqlManager = builder.build(); // 申明一個虛表 "${toTable('user',id)}"，實際上是user表 sqlManager.addVirtualTable("user",USER_TABLE); BeetlTemplateEngine templateEngine = (BeetlTemplateEngine)sqlManager.getSqlTemplateEngine(); // 注冊一個方法來實現映射到多表的邏輯 templateEngine.getBeetl().getGroupTemplate().registerFunction("toTable", new Function(){ @Override public Object call(Object[] paras, Context ctx) { String tableName = (String)paras[0]; Integer id = (Integer)paras[1]; ExecuteContext executeContext = (ExecuteContext)ctx.getGlobal(ExecuteContext.NAME); //使用分庫邏輯 TableAndDataBaseChoice choice = new TableAndDataBaseChoice(); return choice.getTableName(executeContext,tableName,id); } }); return sqlManager; } /** * 用戶數據使用"a" sqlmanager */ @Data @Table(name = "user") @TargetSQLManager("a") public static class UserData { @Auto private Integer id; private String name; private Integer departmentId; } /** * 部門數據使用"b" sqlmanager */ @Data @Table(name = "department") @TargetSQLManager("b") public static class DepartmentData { @Auto private Integer id; private String name; } static final String USER_TABLE="${toTable('user',id)}"; @Data @Table(name = USER_TABLE) public static class MyUser { @AssignID private Integer id; private String name; } } ```