简介

文档

<dependency>
 <groupId>com.ibeetl</groupId>
 <artifactId>beetlsql</artifactId>
 <version>3.${version}</version>
</dependency>

<dependency>
 <groupId>com.ibeetl</groupId>
 <artifactId>beetlsql</artifactId>
 <version>2.13.3.RELEASE</version>
</dependency>

适合用户

编译源码

git clone https://gitee.com/xiandafu/beetlsql
mvn clean package
# 如果想修改源码
mvn clean install 

<modules>
<!--核心功能 -->
<module>sql-core</module>
<module>sql-mapper</module>
<module>sql-util</module>
<module>sql-fetch</module>
<!-- 打包到一起 -->
<module>beetlsql</module>
<module>sql-gen</module>
<module>sql-test</module>
<module>sql-samples</module>
<module>sql-xml</module>	
<module>sql-saga</module>		
<!-- 集成和扩展太多的数据库,可以被屏蔽,以加速项目下载jar -->
<!--	<module>sql-integration</module>-->
<!-- <module>sql-jmh</module>-->
<!--	<module>sql-db-support</module>-->
</modules>

阅读源码例子

代码示例

例子1,内置方法,无需写SQL完成常用操作

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);

┏━━━━━ Debug [user.selectUserAndDepartment] ━━━
┣ SQL: select * from user where 1 = 1 and id=?
┣ 参数: [1]
┣ 位置: org.beetl.sql.test.QuickTest.main(QuickTest.java:47)
┣ 时间: 23ms
┣ 结果: [1]
┗━━━━━ Debug [user.selectUserAndDepartment] ━━━

例子2 使用SQL

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);
//Map 也可以作为输入输出参数
List<Map> listMap = sqlManager.execute(sqlReady,Map.class);

例子3 使用模板SQL

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);
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);

例子4 使用Query类

LambdaQuery<UserEntity> query = sqlManager.lambdaQuery(UserEntity.class);
List<UserEntity> entities = query.andEq(UserEntity::getDepartmentId,1)
 .andIsNotNull(UserEntity::getName).select();

例子5 把数十行SQL放到sql文件里维护,默认支持markdown格式,也支持XML的mybatis语法

//访问user.md#select
SqlId id = SqlId.of("user","select");
Map map = new HashMap();
map.put("name","n");
List<UserEntity> list = sqlManager.select(id,UserEntity.class,map);

例子6 复杂映射支持

@Data
@ResultProvider(AutoJsonMapper.class)
 public static class MyUserView {
 Integer id;
 String name;
 DepartmentEntity dept;
 }

{
	"id": "id",
	"name": "name",
	"dept": {
		"id": "dept_id",
		"name": "dept_name"
	},
	"roles": {
		"id": "r_id",
		"name": "r_name"
	}
}

例子7 最好使用mapper来作为数据库访问类

@SqlResource("user") /*sql文件在user.md里*/
public interface UserMapper extends BaseMapper<UserEntity> {
 @Sql("select * from user where id = ?")
 UserEntity queryUserById(Integer id);
 @Sql("update user set name=? where id = ?")
 @Update
 int updateName(String name,Integer id);
 @Template("select * from user where id = #{id}")
 UserEntity getUserById(Integer id);
 @SpringData/*Spring Data风格*/
 List<UserEntity> queryByNameOrderById(String name);
 /**
 * 可以定义一个default接口
 * @return
 */
 default List<DepartmentEntity> findAllDepartment(){
 Map paras = new HashMap();
 paras.put("exlcudeId",1);
 List<DepartmentEntity> list = getSQLManager().execute("select * from department where id != #{exlcudeId}",DepartmentEntity.class,paras);
 return list;
 }
 /**
 * 调用sql文件user.md#select,方法名即markdown片段名字
 * @param name
 * @return
 */
 List<UserEntity> select(String name);
 /**
 * 翻页查询,调用user.md#pageQuery
 * @param deptId
 * @param pageRequest
 * @return
 */
 PageResult<UserEntity> pageQuery(Integer deptId, PageRequest pageRequest);
	
 @SqlProvider(provider= S01MapperSelectSample.SelectUserProvider.class)
 List<UserEntity> queryUserByCondition(String name);
 @SqlTemplateProvider(provider= S01MapperSelectSample.SelectUs
 List<UserEntity> queryUserByTemplateCondition(String name);
 @Matcher /*自己定义个Matcher注解也很容易*/
 List<UserEntity> query(Condition condition,String name);
}

例子8 使用Fetch 注解

 @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;
 }

例子9 不同数据库切换

 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);

 @Data
 @Table(name = "department")
 @TargetSQLManager("b")
 public static class DepartmentData {
 @Auto
 private Integer id;
 private String name;
 }

例子10 如果想给每个sql语句增加一个sqlId标识

 public static class SqlIdAppendInterceptor implements Interceptor{
 @Override
 public void before(InterceptorContext ctx) {
 ExecuteContext context = ctx.getExecuteContext();
 String jdbcSql = context.sqlResult.jdbcSql;
 String info = context.sqlId.toString();
 //为发送到数据库的sql增加一个注释说明,方便数据库dba能与开发人员沟通
 jdbcSql = "/*"+info+"*/\n"+jdbcSql;
 context.sqlResult.jdbcSql = jdbcSql;
 }
 }

例子11 代码生成框架

	List<SourceBuilder> sourceBuilder = new ArrayList<>();
	SourceBuilder entityBuilder = new EntitySourceBuilder();
	SourceBuilder mapperBuilder = new MapperSourceBuilder();
	SourceBuilder mdBuilder = new MDSourceBuilder();
	//数据库markdown文档
	SourceBuilder docBuilder = new MDDocBuilder();
	sourceBuilder.add(entityBuilder);
	sourceBuilder.add(mapperBuilder);
	sourceBuilder.add(mdBuilder);
	sourceBuilder.add(docBuilder);
 SourceConfig config = new SourceConfig(sqlManager,sourceBuilder);
 //只输出到控制台
	ConsoleOnlyProject project = new ConsoleOnlyProject();
	String tableName = "USER";
	config.gen(tableName,project);

例子13 定义一个Beetl函数

 GroupTemplate groupTemplate = groupTemplate();
 groupTemplate.registerFunction("nextDay",new NextDayFunction());
 Map map = new HashMap();
 map.put("date",new Date());
 String sql = "select * from user where create_time is not null and create_time<#{nextDay(date)}";
 List<UserEntity> count = sqlManager.execute(sql,UserEntity.class,map);

 public static class NextDayFunction implements Function {
 @Override
 public Object call(Object[] paras, Context ctx) {
 Date date = (Date) paras[0];
 Calendar c = Calendar.getInstance();
 c.setTime(date);
 c.add(Calendar.DAY_OF_YEAR, 1); // 今天+1天
 return c.getTime();
 }
 }

例子14 更多可扩展的例子

 static final String USER_TABLE="${toTable('user',id)}";
 @Data
 @Table(name = USER_TABLE)
 public static class MyUser {
 @AssignID
 private Integer id;
 private String name;
 }

@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.METHOD, ElementType.FIELD})
@Builder(JacksonConvert.class)
public @interface Jackson {
}

/**
 * 组合注解,给相关操作添加额外的租户信息,从而实现根据租户分表或者分库
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE})
@Builder(TenantContext.class)
public @interface Tenant {
}

@Retention(RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE})
@Builder(ProviderConfig.class)
public @interface XmlMapping {
 String path() default "";
}

例子15 微服务事务

String orderAddUrl = "http://127.0.0.1:8081/order/item/{orderId}/{userId}/{fee}";
String userBalanceUpdateUrl = "http://127.0.0.1:8082/user/fee/{orderId}/{userId}/{fee}";
..........
SagaContext sagaContext = SagaContext.sagaContextFactory.current();
try {
 sagaContext.start(gid);
 //模拟调用俩个微服务,订单和用户
 rest.postForEntity(orderAddUrl, null,String.class, paras);
 rest.postForEntity(userBalanceUpdateUrl, null,String.class, paras);
 if (1 == 1) {
 throw new RuntimeException("模拟失败,查询saga-server 看效果");
 }
} catch (Exception e) {
 log.info("error " + e.getMessage());
 log.info("start rollback " + e.getMessage());
 sagaContext.rollback();
 return e.getMessage();
}

@Autowired
UserMapper userMapper;
@Transactional(propagation= Propagation.NEVER)
public void update(String orderId,String userId,Integer fee){
 SagaContext sagaContext = SagaContext.sagaContextFactory.current();
 try{
 sagaContext.start(orderId);
 UserEntity user = userMapper.unique(userId);
 user.setBalance(user.getBalance()-fee);
 userMapper.updateById(user);
 sagaContext.commit();
 }catch (Exception e){
 sagaContext.rollback();
 }
}

public interface SagaMapper<T> {
	/** sega 改造的接口**/
	@AutoMapper(SagaInsertAMI.class)
	void insert(T entity);
	@AutoMapper(SagaUpdateByIdAMI.class)
	int updateById(T entity);
	@AutoMapper(SagaDeleteByIdAMI.class)
	int deleteById(Object key);
}

BeetlSQL的架构