BeetlSQL 3 功能预览2

• 定义一个注解@Jackson能把属性对象转化成json存入到数据库
• 定义@LoadOne, 当数据加载完毕后，可以加载更多数据
• 定义@Matcher注解，影响Mapper的执行
• 多租户问题，通过定义@Tenant解决
• BeetlSQL 不支持BigInteger，如何自己扩展一个
• BeetlSQL 不支持Jackson的JsonNode，如何自己扩展
• 不喜欢BeetlSQL提供的json配置映射，自己想搞一个XML的
• BeetlSQL的默认映射方式足够好，但我想扩展自己的
• 给每个发送的DB的SQL的语句自动增加有个sqlId标记的注释，以方便DBA和程序员沟通,或者历史代码方便维护
• BeetlSQL的想把执行的SQL和参数输出到日志框架里。或者记录每次执行参数和结果
• 当底层执行JDBC的时候，我想为一些特殊的SQL增加特殊的JDBC设置

以上代码可以在 BeetlSQL Samples 找到

基础功能在预览介绍里有

PluginAnnotationSample

/**
 *  自定义注解演示
 * @author xiandafu
 * @see Jackson , json注解
 * @see LoadOne, 加载更多数据
 * @see Matcher, mapper扩展新注解
 * @see Tenant,多租户注解
 *
 */
public class PluginAnnotationSample {
    SQLManager sqlManager;

    public PluginAnnotationSample(SQLManager sqlManager) {
        this.sqlManager = sqlManager;

    }

    public static void main(String[] args) throws Exception {
        SQLManager sqlManager = SampleHelper.getSqlManager();
        PluginAnnotationSample plugin = new PluginAnnotationSample(sqlManager);
        plugin.testJacksonAnnotation();
        plugin.testMatcherAnnotation();
        plugin.loadMore();
        plugin.tenant();

    }

    /**
     * 自定义个Json注解 @Jackson，用于序列化到数据库，或者反序列化
     */
    public void testJacksonAnnotation(){
        UserInfo userInfo = new UserInfo();
        Name name = new Name();
        name.setFirstName("joel");
        name.setLastName("li");
        userInfo.setName(name);
        sqlManager.insert(userInfo);
        Integer id = userInfo.getId();
        userInfo = sqlManager.unique(UserInfo.class,id);
        System.out.println(userInfo.getName());
    }

    /**
     * 自定义一个mapper的扩展注解@Matcher，使得mapper参数中有一个Condition类，能构造查询条件。
     * 也演示了如何自定义BaseMapper和重新实现内置方法
     */
    public void testMatcherAnnotation(){
        MyMapper mapper = sqlManager.getMapper(MyMapper.class);
        {
            //@matcher注解
            List<UserInfo> list = mapper.query(new Condition().append("name", Condition.Opt.equals)
                    ,"lijz");
            System.out.println(list.size());
        }

        {
            // 重新实现的deleteById方法
            try{
                mapper.deleteById(1);
            }catch(Exception ex){
                System.out.println(ex.getMessage());
            }
        }


    }

    /**
     * 查询数据后，在根据注解 @LoadOne定义加载更多数据
     */
    public void loadMore(){
        UserDetail userInfo = sqlManager.unique(UserDetail.class,1);
        System.out.println(userInfo.getDepartmentId());
        System.out.println(userInfo.getDept().getName());

    }


    /**
     * 查询前根据注解 @Tenant，提供一些必要的上下文参数
     */
    public void tenant(){
        //设置当前操作的租户
        TenantContext.tenantLocals.set(1);

        String sql = "select * from user where department_id=#{tenantId}";
        List<TenantUser> list = sqlManager.execute(sql,TenantUser.class,new HashMap());
        System.out.println(list.get(0));

    }




    @Table(name="user")
    @Data
    public static class UserInfo{
        @Column("id")
        @AutoID
        Integer id;

        @Column("name")
        @Jackson
        Name name ;
    }
    @Data
    public static class Name{
        String firstName;
        String lastName;
    }


    @Table(name="user")
    @Data
    @LoadOne(name="foo")
    public static class UserDetail{
        @Column("id")
        @AutoID
        Integer id;
        String name;
        Integer departmentId;
        DepartmentInfo dept;
    }

    @Data
    @Table(name="department")
    public static class DepartmentInfo{
        @Column("id")
        @AutoID
        Integer id;
        String name;
    }


    @Data
    @Table(name="user")
    @Tenant
    public static class TenantUser{
        @Auto
        private Integer id;
        @Column("name")
        private String name;
    }



    public  interface MyMapper extends  MyBaseMapper<UserInfo>{
        /**
         * Matcher是扩展注解，表示执行的sql，需要考虑Condition和后面的参数
         * @param condition
         * @param name
         * @return
         */
        @Matcher
        List<UserInfo> query(Condition condition,String name);
    }

    /**
     * 构造一个自定的Mapper方法，继承所有BaseMapper,但deleteById会做一些特殊逻辑
     * @param <T>
     */
    public static interface  MyBaseMapper<T> extends BaseMapper{
        @AutoMapper(DeleteCheckByIdAMI.class)
        int deleteById(Object key);
    }

    public static class DeleteCheckByIdAMI extends MapperInvoke {

        @Override
        public Object call(SQLManager sm, Class entityClass, Method m, Object[] args) {
            if(entityClass==UserInfo.class){
                throw new IllegalArgumentException("不允许调用 "+entityClass);
            }
            return sm.deleteById(entityClass, args[0]);
        }

    }


}

MappingSample

演示如实如何实现TypeHandler转化BigInteger，和JsonNode，以及RowMapper，xml定义的ResultSetMapper，这些提供了灵活的映射

public class MappingSample {
    SQLManager sqlManager;

    public MappingSample(SQLManager sqlManager) {
        this.sqlManager = sqlManager;

    }

    public static void main(String[] args) throws Exception {
        SQLManager sqlManager = SampleHelper.getSqlManager();
        MappingSample mappingSample = new MappingSample(sqlManager);
        mappingSample.rowMapper();
        mappingSample.typeHandler4BigInt();
        mappingSample.typeHandler4JacksonNode();
        mappingSample.xmlMapping();
    }

    /**
     * 把查询结果集额外的列映射到属性上
     */
    public void rowMapper(){
        String sql = "select u.*,"[1,2]" col from user u where id=1 ";
        UserVo info = sqlManager.execute(new SQLReady(sql), UserVo.class).get(0);
        System.out.println(info.getExtraAttribute());

        info = sqlManager.rowMapper(MyRowMapper.class).execute(new SQLReady(sql), UserVo.class).get(0);
        System.out.println(info.getExtraAttribute());
        //更常见的方式是通过注解RowProvider完成
        info = sqlManager.execute(new SQLReady(sql), UserVo2.class).get(0);
        System.out.println(info.getExtraAttribute());
    }

    /**
     * 实现BigInteger转化
     */
    public void typeHandler4BigInt(){

        BigIntTypeHandler bigIntTypeHandler = new BigIntTypeHandler();
        sqlManager.getDefaultBeanProcessors().addHandler(BigInteger.class,bigIntTypeHandler);

        User2 user = new User2();
        user.setName("lll");
        user.setDepartmentId(new BigInteger("1"));
        sqlManager.insert(user);

        Integer id = user.getId();
        User2 dbUser = sqlManager.unique(User2.class,1);
        System.out.println(id+":"+dbUser.getDepartmentId());

    }

    /**
     * 实现jackson node 转化
     */
    public void typeHandler4JacksonNode(){

        JsonNodeTypeHandler typeHandler = new JsonNodeTypeHandler();
        /*注册需要处理的所有JsonNode子类*/
        sqlManager.getDefaultBeanProcessors().addAcceptType(
                new BeanProcessor.InheritedAcceptType(
                        JsonNode.class,typeHandler));

        UserNode user = new UserNode();
        JsonNode node = JsonNodeTypeHandler.objectMapper.createObjectNode().textNode("abcefg");
        user.setName(node);
        sqlManager.insert(user);

        Integer id = user.getId();
        UserNode dbUser = sqlManager.unique(UserNode.class,1);
        System.out.println(id+":"+dbUser.getName());

    }

    /**
     * 按照xml定义映射，SimpleXMLMapping实现了ResultSetMapper接口
     */
    public void xmlMapping(){
        User3 user = sqlManager.unique(User3.class,1);
        System.out.println(user.getUserName());
    }






    @Data
    public static class UserVo {
        Integer id;
        String name;
        String extraAttribute;


    }
    @Data
    @EqualsAndHashCode(callSuper = false)
    @RowProvider(MyRowMapper.class)
    public static class UserVo2 extends UserVo{

    }

    /**
     * 把jsonNode存放到数据库，或者读取出来，在PluginAnnotation演示了通过自定义注解Jackson实现
     * 这次我们可以换一个更底层的，实现一个类型处理器
     */
    @Table(name="user")
    @Data
    public static class UserNode{
        @Auto
        Integer id;
        JsonNode name;
    }

    @Table(name="user")
    @Data
    public static class User2{
        @Auto
        Integer id;
        String name;
        BigInteger departmentId;
    }


    /**
     * 使用xml配置映射规则,参考user.xml,ResultProvider表示如何映射，XmlMapping是映射配置
     */
    @Table(name="user")
    @Data
    @ResultProvider(SimpleXMLMapping.class)
    @XmlMapping(path="user.xml")
    public static class User3{
        Integer id;
        String userName;
        Integer deptId;
    }

    /**
     * Jackson的JsonNode类型处理类,使得java属性可以是JsonNode。
     * 另外一种更通用的是PluginAnnotation例子中的@Jackson注解
     */
    public static class JsonNodeTypeHandler extends JavaSqlTypeHandler{
        static ObjectMapper objectMapper = new ObjectMapper();
        @Override
        public Object getValue(ReadTypeParameter typePara) throws SQLException {
            String str = typePara.getRs().getString(typePara.getIndex());
            JsonNode node = objectMapper.valueToTree(str);
            return node;
        }
        @Override
        public void setParameter(WriteTypeParameter writeTypeParameter, Object obj)throws SQLException {
            JsonNode node = (JsonNode)obj;
            try {
                String text = objectMapper.writeValueAsString(node);
                writeTypeParameter.getPs().setString(writeTypeParameter.getIndex(),text);
            } catch (JsonProcessingException e) {
                throw new IllegalArgumentException("不是json格式");
            }

        }
    }

    /**
     * Beetlsql 并没有内置对BigInteger支持，这里可以扩展
     */
    public static class BigIntTypeHandler extends JavaSqlTypeHandler{
        static ObjectMapper objectMapper = new ObjectMapper();
        @Override
        public Object getValue(ReadTypeParameter typePara) throws SQLException {
            BigDecimal decimal = typePara.getRs().getBigDecimal(typePara.getIndex());
            return decimal.toBigInteger();

        }
        @Override
        public void setParameter(WriteTypeParameter writeTypeParameter, Object obj)throws SQLException {
            BigInteger bigInteger = (BigInteger)obj;
            BigDecimal bigDecimal = new BigDecimal(bigInteger);
            writeTypeParameter.getPs().setBigDecimal(writeTypeParameter.getIndex(),bigDecimal);
        }
    }

    /**
     * 自定义一个行映射
     */
    public static class MyRowMapper implements RowMapper{

        @Override
        public Object mapRow(ExecuteContext ctx, Object obj, ResultSet rs, int rowNum, Annotation config) throws SQLException {
            //内置的映射已经完成
            UserVo vo = (UserVo)obj;
            //额外取得结果集
            String col = rs.getString("col");
            vo.setExtraAttribute(col);
            return vo;
        }
    }
}

InterceptSample

如何自定义Interceptor,定义了一个SqlIdAppendInterceptor，总是在sql语句最前面增加一个注释,注释内容为sqlId标识符，方便数据库dba与程序员沟通

public class InterceptSample {

    public InterceptSample() {

    }

    public static void main(String[] args) throws Exception {
        InterceptSample interceptSample = new InterceptSample();
        interceptSample.appendSqlId();
        interceptSample.debug();
    }

    /**
     *在给执行的jdbc sql 增加一个注释
     */
    public void appendSqlId(){
        SQLManager sqlManager = SampleHelper.init();
        //新增加一个Interceptor
        List< Interceptor> interceptors = new ArrayList(Arrays.asList(sqlManager.getInters()));
        SqlIdAppendInterceptor sqlIdAppendInterceptor = new SqlIdAppendInterceptor();
        //放到第一个，这样后面的DebugInteceptor能打印出sql，实际项目应该放到最后
        interceptors.add(0,sqlIdAppendInterceptor);
        sqlManager.setInters(interceptors.toArray(new Interceptor[0]));


        sqlManager.unique(UserEntity.class,1);
        sqlManager.execute(new SQLReady("select * from user"), UserEntity.class);


    }

    /**
     * 自带的DebugInterceptor输出详细信息到控制台，这里切换到用日志框架
     */
    public void debug(){
        SQLManager sqlManager = SampleHelper.init();
        LogDebugInterceptor logDebugInterceptor = new LogDebugInterceptor();
        sqlManager.setInters(new Interceptor[]{logDebugInterceptor});
        sqlManager.unique(UserEntity.class,1);

    }




    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+"*/
"+jdbcSql;
            context.sqlResult.jdbcSql = jdbcSql;
        }

        @Override
        public void after(InterceptorContext ctx) {
            //dothing
        }
        @Override
        public void exception(InterceptorContext ctx, Exception ex) {
            //do nothing
        }
    }


    public static class LogDebugInterceptor extends DebugInterceptor {
        Logger logger = Logger.getLogger("simple");
        @Override
        protected void println(String str) {
            logger.log(Level.INFO,str);
        }
        @Override
        protected void error(String str) {
            logger.log(Level.SEVERE,str);
        }
    }

}

DbStyleSample

DbStyle 是Beetlsql的核心之一，这里演示如何扩展

public class DbStyleSample {

    public DbStyleSample() {

    }

    public static void main(String[] args) throws Exception {
        DbStyleSample dbStyleSample = new DbStyleSample();
        dbStyleSample.preparedStatement();
    }


    public void preparedStatement(){
        DataSource dataSource = SampleHelper.mysqlDatasource();
        ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
        SQLManagerBuilder builder = new SQLManagerBuilder(source);
        builder.setNc(new UnderlinedNameConversion());
        builder.setInters(new Interceptor[]{new DebugInterceptor()});
        //实现某些特殊功能
        builder.setDbStyle(new MySqlDbStyle2());
        SQLManager sqlManager = builder.build();


        sqlManager.all(UserEntity.class);

    }

    public static class MySqlDbStyle2  extends MySqlStyle {
        @Override
        public SQLExecutor buildExecutor(ExecuteContext executeContext){
            return  new MyExecutor(executeContext);
        }

        /**
         * 考虑jdbc fetch size
         */
        public static class MyExecutor extends  BaseSQLExecutor{

            public MyExecutor(ExecuteContext executeContext) {
                super(executeContext);
            }
            @Override
            protected ResultSetHolder dbQuery(Connection conn, String sql, List<SQLParameter> jdbcPara) throws SQLException {
                if(this.getExecuteContext().target!= UserEntity.class){
                    return super.dbQuery(conn,sql,jdbcPara);
                }
                //对于UserEntity对象查询，考虑使用特殊设置
                PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                ps.setFetchSize(100000);
                this.setPreparedStatementPara(ps, jdbcPara);
                ResultSet rs = ps.executeQuery();
                return new ResultSetHolder(ps, rs);
            }

        }
    }

}