# BeetlSQL3 特点 * 派别:SQL为中心 * 内置常见增删改查功能,节省项目50%工作量 * 强化SQL管理,通过md文件管理sql,使用Beetl模板编写复杂sql * 简单SQL可以通过Query类链式API完成 * 全面支持跨数据库平台 * 支持NOSQL,如ClickhHouse,Elastic,Hive等 * 支持SQL查询引擎,如Apache Drill,Presto等 * 支持一对一,一对多等常见的映射。 * 可以使用约定习俗映射,复杂查询结果支持通过json配置映射到POJO * 提供idea插件 * 其他 * 具备代码生成功能,提供代码生成框架 * 最大程度减少数据库重构对项目造成的影响 * 最大程度减少数据库切换对项目造成的影响 * 支持多数据源,数据源包含传统数据库,NOSQL,SQL查询引擎,且可以根据规则使用数据源 * 内置主从支持 * 提供丰富的扩展功能,80%的功能都可以自行扩展,打造自己个性化的数据库发访问框架,扩展适应新的数据库&NOSQL&查询引擎 * # 数据库工具的痛点 * 开发效率低,如mybatis,还需要搭配plus工具才能提高开发效率,而JOOQ这样的又不适合复杂访问 * 无SQL管理,遇到复杂的sql特别难维护,比如在Java里拼写sql,遇到调整就麻烦 * 跨数据库平台,即使Hibernate,也完全做不到跨数据库 * 缺少数据库和NOSQL无缝切换很难,比如一部分业务要无缝切换到NOSQL上 * 数据库重构对代码影响非常大,数据库列修改,增加要改很多代码 * 难以调试数据库访问代码 BeetlSQL不仅仅知道所有这些痛点,而且能很好的解决这些痛点 # BeetlSQL3 例子 所有例子都可以从 https://gitee.com/xiandafu/beetlsql/tree/3.0/sql-samples/sql-sample-quickstart 看到和运行 * S1QuickStart:SQLMananger API,Query类,Mapper使用,基本的CRUD映射 * S2MappingSample:如何把结果集映射到Java对象,通过注解,通过json配置,或者约定习俗进行复杂映射,通过自定义注解来扩展映射方式 * S3PageSample:翻页和范围查询 * S4Other: 其他常用操作示例,一些常见的like,in,batch操作 * S5Fetch:自动fetch功能 ,在查询对象后,还可以自动fetch其他对象,类似JPA的ORM,但ORM对CRUD影响过大,fetch功能则简单很多 * S6MoreSource: 非常方便的实现多数据源,每个实体可以标记自己的数据源;或者简单一个主从数据库的例子;或者分表例子;或者分库+分表。 * S7CodeGen: 使用BeetlSQL生成代码,SQL语句和数据库文档 用户能在2小时内浏览完所有例子并基本掌握BeetlSQL的用法,如果想运行这些例子,需要参考章节《快速开始》,例子仅仅做了解BeetlSQL基本用法 ## 基础例子 ```java /** * 入门 演示内置SQLManager用法和BaseMapper用法,项目中更推荐使用BaseMapper,而不是较为底层的SQLManager * @author xiandafu * */ public class S1QuickStart { SQLManager sqlManager; UserMapper mapper = null; public S1QuickStart(SQLManager sqlManager) { this.sqlManager = sqlManager; mapper = sqlManager.getMapper(UserMapper.class); } public static void main(String[] args) throws Exception { SQLManager sqlManager = SampleHelper.getSqlManager(); S1QuickStart quickStart = new S1QuickStart(sqlManager); quickStart.baseSqlManager(); quickStart.executeSql(); quickStart.executeTemplate(); quickStart.query(); quickStart.mapper(); quickStart.sqlResource(); } /** * 使用内置sqlManager方法 */ public void baseSqlManager(){ UserEntity user = sqlManager.unique(UserEntity.class,1); user.setName("ok123"); sqlManager.updateById(user); UserEntity newUser = new UserEntity(); newUser.setName("newUser"); newUser.setDepartmentId(1); sqlManager.insert(newUser); UserEntity template = new UserEntity(); template.setDepartmentId(1); List<UserEntity> list = sqlManager.template(template); } //执行sql语句方法 public void executeSql(){ String sql = "select * from user where id=?"; Integer id = 1; SQLReady sqlReady = new SQLReady(sql,new Object[id]); List<UserEntity> userEntities = sqlManager.execute(sqlReady,UserEntity.class); String updateSql = "update department set name=? where id =?"; String name="lijz"; SQLReady updateSqlReady = new SQLReady(updateSql,new Object[]{name,id}); sqlManager.executeUpdate(updateSqlReady); } //执行sql模板语句 public void executeTemplate(){ { String sql = "select * from user where department_id=#{id} and name=#{name}"; UserEntity paras = new UserEntity(); paras.setDepartmentId(1); paras.setName("lijz"); List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras); } { //或者使用Map作为参数 String sql = "select * from user where department_id=#{myDeptId} and name=#{myName}"; Map paras = new HashMap(); paras.put("myDeptId",1); paras.put("myName","lijz"); List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras); } { //使用Beetl模板语句 String sql = "select * from user where 1=1 \n" + "-- @if(isNotEmpty(myDeptId)){\n" + " and department_id=#{myDeptId}\t\n" + "-- @}\n" + "and name=#{myName}"; Map paras = new HashMap(); paras.put("myDeptId",1); paras.put("myName","lijz"); List<UserEntity> list = sqlManager.execute(sql,UserEntity.class,paras); } } public void query(){ { Query<UserEntity> query = sqlManager.query(UserEntity.class); List<UserEntity> entities = query.andEq("department_id",1) .andIsNotNull("name").select(); } { //使用LambdaQuery,能很好的支持数据库重构 LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class); List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1) .andIsNotNull(UserEntity::getName).select(); } } /** * 最常用的方式,编写一个Mapper类,mapper方法提供数据库访问接口,beetlsql提供丰富的beetlsql实现 */ public void mapper(){ // 内置BaseMapper方法调用 List<UserEntity> list = mapper.all(); boolean isExist = mapper.exist(2); UserEntity me = mapper.unique(1); me.setName("newName"); mapper.updateById(me); //调用其他方法 UserEntity user = mapper.getUserById(1); UserEntity user2 = mapper.queryUserById(2); mapper.updateName("newName2",2); List<UserEntity> users = mapper.queryByNameOrderById("newName2"); List<DepartmentEntity> depts = mapper.findAllDepartment(); } /** * 对于复杂sql语句,比如几十行,甚至几百行的sql模板语句,放到markdown文件里是个不错的想法 * 参考sql/user.md#select */ public void sqlResource(){ SqlId id = SqlId.of("user","select"); //or SqlId id = SqlId.of("user.select"); Map map = new HashMap(); map.put("name","n"); List<UserEntity> list = sqlManager.select(id,UserEntity.class,map); UserMapper mapper = sqlManager.getMapper(UserMapper.class); mapper.select("n"); } } ``` ## 结果集映射 ```java /** * 演示如何将数据库查询结果映射到java对象上 * * @author xiandafu */ public class S2MappingSample { SQLManager sqlManager; UserMapper mapper =null; public S2MappingSample(SQLManager sqlManager) { this.sqlManager = sqlManager; mapper = sqlManager.getMapper(UserMapper.class); } public static void main(String[] args) throws Exception { SQLManager sqlManager = SampleHelper.getSqlManager(); S2MappingSample mappingSample = new S2MappingSample(sqlManager); mappingSample.column(); mappingSample.toMap(); mappingSample.view(); mappingSample.mappingProvider(); mappingSample.jsonConfig(); mappingSample.autoMapping(); mappingSample.myAttributeAnnotation(); } /** * 使用@Column注解,或者按照NameConversion来自动映射 */ public void column() { MyUser user = sqlManager.unique(MyUser.class, 1); } /** * 可以把查询结果转化成Map,在java中,注意,滥用map作为也业务对象是非常糟糕设计 */ public void toMap() { SQLReady sqlReady = new SQLReady("select id,name from user"); List<Map> list = sqlManager.execute(sqlReady, Map.class); } /** * */ public void view() { //映射所有列 TestUser user = sqlManager.unique(TestUser.class, 1); //映射只有一个KeyInfo标注的属性,本例子中department属性不在查询结果范围里 TestUser keyInfo = sqlManager.viewType(TestUser.KeyInfo.class).unique(TestUser.class, 1); } /** * 使用额外的映射类来映射 */ public void mappingProvider() { //运行时刻指定一个映射类 TestUser testUser = sqlManager.rowMapper(MyRowMapper.class).unique(TestUser2.class, 1); //使用@RowProvider注解为类指定一个Mapper,这个更常用 TestUser2 testUser2 = sqlManager.unique(TestUser2.class, 1); } /** * 使用json 配置来映射,类似mybatis的xml配置 */ public void jsonConfig() { String sql = "select d.id id,d.name name ,u.id u_id,u.name u_name " + " from department d join user u on d.id=u.department_id where d.id=?"; Integer deptId = 1; SQLReady ready = new SQLReady(sql,new Object[]{deptId}); List<DepartmentInfo> list = sqlManager.execute(ready,DepartmentInfo.class); System.out.println(list.toString()); } /** * 使用json 配置来映射,类似mybatis的xml配置 */ public void autoMapping() { List<MyUserView> list = mapper.allUserView(); System.out.println(list); } /** * 自定义一个属性注解Base64,用于编码和解码属性字段 */ public void myAttributeAnnotation(){ UserData userData = new UserData(); userData.setName("123456"); sqlManager.insert(userData); UserData data = sqlManager.unique(UserData.class,userData.getId()); System.out.println("user name "+data.getName()); UserEntity entity = sqlManager.unique(UserEntity.class,userData.getId()); System.out.println("db value "+entity.getName()); } /** * 演示使用Column 注解映射java属性与表列名, */ @Data @Table(name="user") public static class MyUser { @Column("id") @AutoID Integer myId; @Column("name") String myName; } @Data @Table(name="user") public static class TestUser { public static interface KeyInfo { } @Column("id") @AutoID @View(KeyInfo.class) Integer myId; @Column("name") @View(KeyInfo.class) String myName; Integer departmentId; } @RowProvider(MyRowMapper.class) public static class TestUser2 extends TestUser { } /** * 使用json配置来映射,如果映射配置过长,建议放到文件中,使用resource说明配置路径 * */ @Data @ResultProvider(JsonConfigMapper.class) // @JsonMapper( // "{'id':'id','name':'name','users':{'id':'u_id','name':'u_name'}}") @org.beetl.sql.annotation.entity.JsonMapper(resource ="user.departmentJsonMapping") public static class DepartmentInfo { Integer id; String name; List<UserInfo> users; } @Data public static class UserInfo { Integer id; String name; } /** * 如果数据库查询的结果与类定义一致,也可以使用AutoJsonMapper */ @Data @ResultProvider(AutoJsonMapper.class) public static class MyUserView { Integer id; String name; DepartmentEntity dept; } public static class MyRowMapper implements RowMapper<TestUser> { @Override public TestUser mapRow(ExecuteContext ctx, Object obj, ResultSet rs, int rowNum, Annotation config) throws SQLException { TestUser testUser = (TestUser) obj; testUser.setMyName(testUser.getMyName() + "-" + System.currentTimeMillis()); return testUser; } } @Table(name="user") @Data public static class UserData{ @AutoID Integer id; @Base64 String name; } @Retention(RetentionPolicy.RUNTIME) @Target(value = {ElementType.METHOD, ElementType.FIELD}) @Builder(Base64Convert.class) public static @interface Base64 { } /** * 自定义一个注解,实现把属性字段加密存入数据库,取出的时候解密 */ public static class Base64Convert implements AttributeConvert { Charset utf8 = Charset.forName("UTF-8"); public Object toDb(ExecuteContext ctx, Class cls, String name, Object dbValue) { String value= (String) BeanKit.getBeanProperty(dbValue,name); byte[] bs = java.util.Base64.getEncoder().encode(value.getBytes(utf8)); return new String(bs,utf8); } public Object toAttr(ExecuteContext ctx, Class cls, String name, ResultSet rs, int index) throws SQLException { String value = rs.getString(index); return new String(java.util.Base64.getDecoder().decode(value),utf8); } } } ``` ## 翻页查询 ```java public class S3PageSample { SQLManager sqlManager; UserMapper mapper =null; public S3PageSample(SQLManager sqlManager) { this.sqlManager = sqlManager; mapper = sqlManager.getMapper(UserMapper.class); } public static void main(String[] args) throws Exception { SQLManager sqlManager = SampleHelper.getSqlManager(); S3PageSample page = new S3PageSample(sqlManager); page.baseRange(); page.page(); page.jdbcPage(); page.resourceSqlPage(); page.resourceGroupSqlPage(); } /** * 范围查询 */ public void baseRange(){ List<UserEntity> all = mapper.all(); long count = mapper.allCount(); UserEntity template = new UserEntity(); template.setDepartmentId(1); UserEntity user1 = mapper.templateOne(template); } /** * 翻页查询,使用模板sql */ public void page(){ /** * sql模板语句的page函数能自动把sql模板语句转为为求总数语句 */ String sql = "select #{page('*')} from user where department_id=#{id}"; PageRequest request = DefaultPageRequest.of(1,10); Map map = new HashMap<>(); map.put("id",1); PageResult pr = sqlManager.executePageQuery(sql,UserEntity.class,map,request); //强制转化为DefaultPageResult, DefaultPageResult pageResult = (DefaultPageResult)pr; printPageResult(pageResult); } /** * 直接使用jdbc sql */ public void jdbcPage(){ /** * 解析jdbc sql语句,生成求总数语句 */ String sql = "select * from user where department_id=?"; PageRequest request = DefaultPageRequest.of(1,10); SQLReady sqlReady = new SQLReady(sql,new Object[]{1}); PageResult pr = sqlManager.execute(sqlReady,UserEntity.class,request); DefaultPageResult pageResult = (DefaultPageResult)pr; printPageResult(pageResult); } /** * 翻页查询通常很复杂,SQL很长,把sql语句放到sql文件里是个好办法,也是最常用的办法 */ public void resourceSqlPage(){ PageRequest request = DefaultPageRequest.of(1,10); PageResult pr = mapper.pageQuery(1,request); DefaultPageResult pageResult = (DefaultPageResult)pr; printPageResult(pageResult); } /** * 对分组语句进行翻页查询,需要嵌套在子查询里,比如 * <pre> * select count(1),name from user group by name * </pre> * 如上分组提供给beetlsql的时候,应该编写成 * <pre> * select #{page()} from ( select count(1),name from user group by name ) a * </pre> * */ public void resourceGroupSqlPage(){ PageRequest request = DefaultPageRequest.of(1,10); PageResult pr = mapper.pageQuery2(1,request); DefaultPageResult pageResult = (DefaultPageResult)pr; printPageResult(pageResult); } public void printPageResult(DefaultPageResult pageResult){ System.out.println(pageResult.getPage()); System.out.println(pageResult.getPageSize()); System.out.println(pageResult.getTotal()); System.out.println(pageResult.getTotalPage()); System.out.println(pageResult.getResult()); } } ``` ## 演示like,batchUpdate,in 操作 ```java public class S4Other { SQLManager sqlManager; UserMapper mapper = null; public S4Other(SQLManager sqlManager) { this.sqlManager = sqlManager; mapper = sqlManager.getMapper(UserMapper.class); } public static void main(String[] args) throws Exception { SQLManager sqlManager = SampleHelper.getSqlManager(); S4Other others = new S4Other(sqlManager); others.like(); others.in(); others.batch(); others.sqlResult(); } /** * like */ public void like() { String sql = "select * from user where name like #{name}"; Map paras = new HashMap(); String name = "%li%"; paras.put("name", name); List<UserEntity> users = sqlManager.execute(sql, UserEntity.class, paras); //同样效果 sql = "select * from user where name like #{'%'+name+'%'}"; paras = new HashMap(); name = "li"; paras.put("name", name); users = sqlManager.execute(sql, UserEntity.class, paras); //同样效果 SQLReady sqlReady = new SQLReady("select * from user where name like ?" ,new Object[]{"%"+name+"%"}); users = sqlManager.execute(sqlReady,UserEntity.class); } /** * in */ public void in() { //使用beetlsql提供的join函数,接受一个list变量 String sql = "select * from user where id in ( #{join(ids)} )"; List list = Arrays.asList(1,2,3,4,5); Map paras = new HashMap(); paras.put("ids", list); List<UserEntity> users = sqlManager.execute(sql, UserEntity.class, paras); } /** * batch */ public void batch() { //批量插入 UserEntity user1 = new UserEntity(); user1.setName("b1"); user1.setDepartmentId(1); UserEntity user2 = new UserEntity(); user2.setName("b2"); user2.setDepartmentId(1); //根据组件批量更新 List<UserEntity> data = Arrays.asList(user1,user2); sqlManager.insertBatch(UserEntity.class,data); data.get(1).setName("bb11"); sqlManager.updateByIdBatch(data); //循环删除,执行多次 data.stream().forEach(userEntity -> mapper.deleteById(userEntity.getId())); } /** * 不执行,只得到sql语句和参数 */ public void sqlResult(){ Map map = new HashMap(); map.put("name","li"); SQLResult sqlResult = sqlManager.getSQLResult(SqlId.of("user","select"),map); String targetJdbc = sqlResult.jdbcSql; Object[] paras = sqlResult.toObjectArray(); System.out.println(targetJdbc); System.out.println(Arrays.asList(paras)); } } ``` ## 自动fetch ```java /** * 演示自动fetch,类似orm,但不同于orm,CRUD在ORM概念下过于复杂, * BeetlSQL的fetch没有那么多复杂概念,仅仅是加载对象后看看还有没有需要再加载的对象 * * * @author xiandafu */ public class S5Fetch { SQLManager sqlManager; public S5Fetch(SQLManager sqlManager) { this.sqlManager = sqlManager; } public static void main(String[] args) throws Exception { //为了简单起见,俩个sqlManager都来自同一个数据源,实际是不同数据库,甚至是NOSQL SQLManager sqlManager = SampleHelper.init(); S5Fetch fetch = new S5Fetch(sqlManager); fetch.fetchOne(); fetch.fetchMany(); } /** * */ public void fetchOne(){ UserData user = sqlManager.unique(UserData.class,1); System.out.println(user.getDept()); //fetchOne 会合并查询提高性能 List<UserData> users = sqlManager.all(UserData.class); System.out.println(users.get(0).getDept()); } public void fetchMany(){ DepartmentData dept = sqlManager.unique(DepartmentData.class,1); System.out.println(dept.getUsers()); } /** * 用户数据使用"a" sqlmanager */ @Data @Table(name="user") @Fetch public static class UserData { @Auto private Integer id; private String name; private Integer departmentId; @FetchOne("departmentId") private DepartmentData dept; } /** * 部门数据使用"b" sqlmanager */ @Data @Table(name="department") @Fetch public static class DepartmentData { @Auto private Integer id; private String name; @FetchMany("departmentId") private List<UserData> users; } } ``` ## 多数据库 可能是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; } } ``` ## 代码生成框架 演示代码生成框架,以及生成代码和数据库文档 ```java /** * 演示beetlsql 代码生成框架 * * @author xiandafu */ public class S7CodeGen { SQLManager sqlManager; public S7CodeGen(SQLManager sqlManager) { this.sqlManager = sqlManager; initGroupTemplate(); } protected void initGroupTemplate(){ //指定模板文件路径,正常情况下,不需要要指定,默认在classpath:templates,但idea的环境读取不到 GroupTemplate groupTemplate = BaseTemplateSourceBuilder.getGroupTemplate(); String root = System.getProperty("user.dir"); //代码模板在sql-gen,你可以指定自己的模板路径 String templatePath = root+"/sql-gen/src/main/resources/templates/"; FileResourceLoader resourceLoader = new FileResourceLoader(templatePath); groupTemplate.setResourceLoader(resourceLoader); } public static void main(String[] args) throws Exception { //为了简单起见,俩个sqlManager都来自同一个数据源,实际是不同数据库,甚至是NOSQL SQLManager sqlManager = SampleHelper.init(); S7CodeGen gen = new S7CodeGen(sqlManager); gen.genCode(); gen.genDoc(); gen.genAllDoc(); } /** * 代码生成,生成实体,mapper代码 */ public void genCode(){ List<SourceBuilder> sourceBuilder = new ArrayList<>(); SourceBuilder entityBuilder = new EntitySourceBuilder(); SourceBuilder mapperBuilder = new MapperSourceBuilder(); SourceBuilder mdBuilder = new MDSourceBuilder(); sourceBuilder.add(entityBuilder); sourceBuilder.add(mapperBuilder); sourceBuilder.add(mdBuilder); SourceConfig config = new SourceConfig(sqlManager,sourceBuilder); //如果有错误,抛出异常而不是继续运行1 EntitySourceBuilder.getGroupTemplate().setErrorHandler(new ReThrowConsoleErrorHandler() ); ConsoleOnlyProject project = new ConsoleOnlyProject(); String tableName = "USER"; config.gen(tableName,project); } /** * 生成数据库文档 */ public void genDoc(){ List<SourceBuilder> sourceBuilder = new ArrayList<>(); SourceBuilder docBuilder = new MDDocBuilder(); sourceBuilder.add(docBuilder); SourceConfig config = new SourceConfig(sqlManager,sourceBuilder); //如果有错误,抛出异常而不是继续运行1 EntitySourceBuilder.getGroupTemplate().setErrorHandler(new ReThrowConsoleErrorHandler() ); ConsoleOnlyProject project = new ConsoleOnlyProject(); String tableName = "USER"; config.gen(tableName,project); } /** * 生成数据库文档 */ public void genAllDoc(){ List<SourceBuilder> sourceBuilder = new ArrayList<>(); SourceBuilder docBuilder = new MDDocBuilder(); sourceBuilder.add(docBuilder); SourceConfig config = new SourceConfig(sqlManager,sourceBuilder); //如果有错误,抛出异常而不是继续运行1 EntitySourceBuilder.getGroupTemplate().setErrorHandler(new ReThrowConsoleErrorHandler() ); StringOnlyProject project = new StringOnlyProject(); config.genAll(project); String output = project.getContent(); System.out.println(output); } } ```