BeetlSQL 3 功能预览2

本博客介绍了BeetlSQL3提供的高级扩展功能，以下问题可以在BeetlSQL3中轻易扩展实现

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

基础功能在预览介绍里有

PluginAnnotationSample

1/**
2 *  自定义注解演示
3 * @author xiandafu
4 * @see Jackson , json注解
5 * @see LoadOne, 加载更多数据
6 * @see Matcher, mapper扩展新注解
7 * @see Tenant,多租户注解
8 *
9 */
10public class PluginAnnotationSample {
11    SQLManager sqlManager;
12
13    public PluginAnnotationSample(SQLManager sqlManager) {
14        this.sqlManager = sqlManager;
15
16    }
17
18    public static void main(String[] args) throws Exception {
19        SQLManager sqlManager = SampleHelper.getSqlManager();
20        PluginAnnotationSample plugin = new PluginAnnotationSample(sqlManager);
21        plugin.testJacksonAnnotation();
22        plugin.testMatcherAnnotation();
23        plugin.loadMore();
24        plugin.tenant();
25
26    }
27
28    /**
29     * 自定义个Json注解 @Jackson，用于序列化到数据库，或者反序列化
30     */
31    public void testJacksonAnnotation(){
32        UserInfo userInfo = new UserInfo();
33        Name name = new Name();
34        name.setFirstName("joel");
35        name.setLastName("li");
36        userInfo.setName(name);
37        sqlManager.insert(userInfo);
38        Integer id = userInfo.getId();
39        userInfo = sqlManager.unique(UserInfo.class,id);
40        System.out.println(userInfo.getName());
41    }
42
43    /**
44     * 自定义一个mapper的扩展注解@Matcher，使得mapper参数中有一个Condition类，能构造查询条件。
45     * 也演示了如何自定义BaseMapper和重新实现内置方法
46     */
47    public void testMatcherAnnotation(){
48        MyMapper mapper = sqlManager.getMapper(MyMapper.class);
49        {
50            //@matcher注解
51            List<UserInfo> list = mapper.query(new Condition().append("name", Condition.Opt.equals)
52                    ,"lijz");
53            System.out.println(list.size());
54        }
55
56        {
57            // 重新实现的deleteById方法
58            try{
59                mapper.deleteById(1);
60            }catch(Exception ex){
61                System.out.println(ex.getMessage());
62            }
63        }
64
65
66    }
67
68    /**
69     * 查询数据后，在根据注解 @LoadOne定义加载更多数据
70     */
71    public void loadMore(){
72        UserDetail userInfo = sqlManager.unique(UserDetail.class,1);
73        System.out.println(userInfo.getDepartmentId());
74        System.out.println(userInfo.getDept().getName());
75
76    }
77
78
79    /**
80     * 查询前根据注解 @Tenant，提供一些必要的上下文参数
81     */
82    public void tenant(){
83        //设置当前操作的租户
84        TenantContext.tenantLocals.set(1);
85
86        String sql = "select * from user where department_id=#{tenantId}";
87        List<TenantUser> list = sqlManager.execute(sql,TenantUser.class,new HashMap());
88        System.out.println(list.get(0));
89
90    }
91
92
93
94
95    @Table(name="user")
96    @Data
97    public static class UserInfo{
98        @Column("id")
99        @AutoID
100        Integer id;
101
102        @Column("name")
103        @Jackson
104        Name name ;
105    }
106    @Data
107    public static class Name{
108        String firstName;
109        String lastName;
110    }
111
112
113    @Table(name="user")
114    @Data
115    @LoadOne(name="foo")
116    public static class UserDetail{
117        @Column("id")
118        @AutoID
119        Integer id;
120        String name;
121        Integer departmentId;
122        DepartmentInfo dept;
123    }
124
125    @Data
126    @Table(name="department")
127    public static class DepartmentInfo{
128        @Column("id")
129        @AutoID
130        Integer id;
131        String name;
132    }
133
134
135    @Data
136    @Table(name="user")
137    @Tenant
138    public static class TenantUser{
139        @Auto
140        private Integer id;
141        @Column("name")
142        private String name;
143    }
144
145
146
147    public  interface MyMapper extends  MyBaseMapper<UserInfo>{
148        /**
149         * Matcher是扩展注解，表示执行的sql，需要考虑Condition和后面的参数
150         * @param condition
151         * @param name
152         * @return
153         */
154        @Matcher
155        List<UserInfo> query(Condition condition,String name);
156    }
157
158    /**
159     * 构造一个自定的Mapper方法，继承所有BaseMapper,但deleteById会做一些特殊逻辑
160     * @param <T>
161     */
162    public static interface  MyBaseMapper<T> extends BaseMapper{
163        @AutoMapper(DeleteCheckByIdAMI.class)
164        int deleteById(Object key);
165    }
166
167    public static class DeleteCheckByIdAMI extends MapperInvoke {
168
169        @Override
170        public Object call(SQLManager sm, Class entityClass, Method m, Object[] args) {
171            if(entityClass==UserInfo.class){
172                throw new IllegalArgumentException("不允许调用 "+entityClass);
173            }
174            return sm.deleteById(entityClass, args[0]);
175        }
176
177    }
178
179
180}

MappingSample

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

1public class MappingSample {
2    SQLManager sqlManager;
3
4    public MappingSample(SQLManager sqlManager) {
5        this.sqlManager = sqlManager;
6
7    }
8
9    public static void main(String[] args) throws Exception {
10        SQLManager sqlManager = SampleHelper.getSqlManager();
11        MappingSample mappingSample = new MappingSample(sqlManager);
12        mappingSample.rowMapper();
13        mappingSample.typeHandler4BigInt();
14        mappingSample.typeHandler4JacksonNode();
15        mappingSample.xmlMapping();
16    }
17
18    /**
19     * 把查询结果集额外的列映射到属性上
20     */
21    public void rowMapper(){
22        String sql = "select u.*,'[1,2]' col from user u where id=1 ";
23        UserVo info = sqlManager.execute(new SQLReady(sql), UserVo.class).get(0);
24        System.out.println(info.getExtraAttribute());
25
26        info = sqlManager.rowMapper(MyRowMapper.class).execute(new SQLReady(sql), UserVo.class).get(0);
27        System.out.println(info.getExtraAttribute());
28        //更常见的方式是通过注解RowProvider完成
29        info = sqlManager.execute(new SQLReady(sql), UserVo2.class).get(0);
30        System.out.println(info.getExtraAttribute());
31    }
32
33    /**
34     * 实现BigInteger转化
35     */
36    public void typeHandler4BigInt(){
37
38        BigIntTypeHandler bigIntTypeHandler = new BigIntTypeHandler();
39        sqlManager.getDefaultBeanProcessors().addHandler(BigInteger.class,bigIntTypeHandler);
40
41        User2 user = new User2();
42        user.setName("lll");
43        user.setDepartmentId(new BigInteger("1"));
44        sqlManager.insert(user);
45
46        Integer id = user.getId();
47        User2 dbUser = sqlManager.unique(User2.class,1);
48        System.out.println(id+":"+dbUser.getDepartmentId());
49
50    }
51
52    /**
53     * 实现jackson node 转化
54     */
55    public void typeHandler4JacksonNode(){
56
57        JsonNodeTypeHandler typeHandler = new JsonNodeTypeHandler();
58        /*注册需要处理的所有JsonNode子类*/
59        sqlManager.getDefaultBeanProcessors().addAcceptType(
60                new BeanProcessor.InheritedAcceptType(
61                        JsonNode.class,typeHandler));
62
63        UserNode user = new UserNode();
64        JsonNode node = JsonNodeTypeHandler.objectMapper.createObjectNode().textNode("abcefg");
65        user.setName(node);
66        sqlManager.insert(user);
67
68        Integer id = user.getId();
69        UserNode dbUser = sqlManager.unique(UserNode.class,1);
70        System.out.println(id+":"+dbUser.getName());
71
72    }
73
74    /**
75     * 按照xml定义映射，SimpleXMLMapping实现了ResultSetMapper接口
76     */
77    public void xmlMapping(){
78        User3 user = sqlManager.unique(User3.class,1);
79        System.out.println(user.getUserName());
80    }
81
82
83
84
85
86
87    @Data
88    public static class UserVo {
89        Integer id;
90        String name;
91        String extraAttribute;
92
93
94    }
95    @Data
96    @EqualsAndHashCode(callSuper = false)
97    @RowProvider(MyRowMapper.class)
98    public static class UserVo2 extends UserVo{
99
100    }
101
102    /**
103     * 把jsonNode存放到数据库，或者读取出来，在PluginAnnotation演示了通过自定义注解Jackson实现
104     * 这次我们可以换一个更底层的，实现一个类型处理器
105     */
106    @Table(name="user")
107    @Data
108    public static class UserNode{
109        @Auto
110        Integer id;
111        JsonNode name;
112    }
113
114    @Table(name="user")
115    @Data
116    public static class User2{
117        @Auto
118        Integer id;
119        String name;
120        BigInteger departmentId;
121    }
122
123
124    /**
125     * 使用xml配置映射规则,参考user.xml,ResultProvider表示如何映射，XmlMapping是映射配置
126     */
127    @Table(name="user")
128    @Data
129    @ResultProvider(SimpleXMLMapping.class)
130    @XmlMapping(path="user.xml")
131    public static class User3{
132        Integer id;
133        String userName;
134        Integer deptId;
135    }
136
137    /**
138     * Jackson的JsonNode类型处理类,使得java属性可以是JsonNode。
139     * 另外一种更通用的是PluginAnnotation例子中的@Jackson注解
140     */
141    public static class JsonNodeTypeHandler extends JavaSqlTypeHandler{
142        static ObjectMapper objectMapper = new ObjectMapper();
143        @Override
144        public Object getValue(ReadTypeParameter typePara) throws SQLException {
145            String str = typePara.getRs().getString(typePara.getIndex());
146            JsonNode node = objectMapper.valueToTree(str);
147            return node;
148        }
149        @Override
150        public void setParameter(WriteTypeParameter writeTypeParameter, Object obj)throws SQLException {
151            JsonNode node = (JsonNode)obj;
152            try {
153                String text = objectMapper.writeValueAsString(node);
154                writeTypeParameter.getPs().setString(writeTypeParameter.getIndex(),text);
155            } catch (JsonProcessingException e) {
156                throw new IllegalArgumentException("不是json格式");
157            }
158
159        }
160    }
161
162    /**
163     * Beetlsql 并没有内置对BigInteger支持，这里可以扩展
164     */
165    public static class BigIntTypeHandler extends JavaSqlTypeHandler{
166        static ObjectMapper objectMapper = new ObjectMapper();
167        @Override
168        public Object getValue(ReadTypeParameter typePara) throws SQLException {
169            BigDecimal decimal = typePara.getRs().getBigDecimal(typePara.getIndex());
170            return decimal.toBigInteger();
171
172        }
173        @Override
174        public void setParameter(WriteTypeParameter writeTypeParameter, Object obj)throws SQLException {
175            BigInteger bigInteger = (BigInteger)obj;
176            BigDecimal bigDecimal = new BigDecimal(bigInteger);
177            writeTypeParameter.getPs().setBigDecimal(writeTypeParameter.getIndex(),bigDecimal);
178        }
179    }
180
181    /**
182     * 自定义一个行映射
183     */
184    public static class MyRowMapper implements RowMapper{
185
186        @Override
187        public Object mapRow(ExecuteContext ctx, Object obj, ResultSet rs, int rowNum, Annotation config) throws SQLException {
188            //内置的映射已经完成
189            UserVo vo = (UserVo)obj;
190            //额外取得结果集
191            String col = rs.getString("col");
192            vo.setExtraAttribute(col);
193            return vo;
194        }
195    }
196}

InterceptSample

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

1public class InterceptSample {
2
3    public InterceptSample() {
4
5    }
6
7    public static void main(String[] args) throws Exception {
8        InterceptSample interceptSample = new InterceptSample();
9        interceptSample.appendSqlId();
10        interceptSample.debug();
11    }
12
13    /**
14     *在给执行的jdbc sql 增加一个注释
15     */
16    public void appendSqlId(){
17        SQLManager sqlManager = SampleHelper.init();
18        //新增加一个Interceptor
19        List< Interceptor> interceptors = new ArrayList(Arrays.asList(sqlManager.getInters()));
20        SqlIdAppendInterceptor sqlIdAppendInterceptor = new SqlIdAppendInterceptor();
21        //放到第一个，这样后面的DebugInteceptor能打印出sql，实际项目应该放到最后
22        interceptors.add(0,sqlIdAppendInterceptor);
23        sqlManager.setInters(interceptors.toArray(new Interceptor[0]));
24
25
26        sqlManager.unique(UserEntity.class,1);
27        sqlManager.execute(new SQLReady("select * from user"), UserEntity.class);
28
29
30    }
31
32    /**
33     * 自带的DebugInterceptor输出详细信息到控制台，这里切换到用日志框架
34     */
35    public void debug(){
36        SQLManager sqlManager = SampleHelper.init();
37        LogDebugInterceptor logDebugInterceptor = new LogDebugInterceptor();
38        sqlManager.setInters(new Interceptor[]{logDebugInterceptor});
39        sqlManager.unique(UserEntity.class,1);
40
41    }
42
43
44
45
46    public static class SqlIdAppendInterceptor implements  Interceptor{
47
48        @Override
49        public void before(InterceptorContext ctx) {
50            ExecuteContext context = ctx.getExecuteContext();
51            String jdbcSql = context.sqlResult.jdbcSql;
52            String info  = context.sqlId.toString();
53            //为发送到数据库的sql增加一个注释说明，方便数据库dba能与开发人员沟通
54            jdbcSql = "/*"+info+"*/\n"+jdbcSql;
55            context.sqlResult.jdbcSql = jdbcSql;
56        }
57
58        @Override
59        public void after(InterceptorContext ctx) {
60            //dothing
61        }
62        @Override
63        public void exception(InterceptorContext ctx, Exception ex) {
64            //do nothing
65        }
66    }
67
68
69    public static class LogDebugInterceptor extends DebugInterceptor {
70        Logger logger = Logger.getLogger("simple");
71        @Override
72        protected void println(String str) {
73            logger.log(Level.INFO,str);
74        }
75        @Override
76        protected void error(String str) {
77            logger.log(Level.SEVERE,str);
78        }
79    }
80
81}
82

DbStyleSample

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

1public class DbStyleSample {
2
3    public DbStyleSample() {
4
5    }
6
7    public static void main(String[] args) throws Exception {
8        DbStyleSample dbStyleSample = new DbStyleSample();
9        dbStyleSample.preparedStatement();
10    }
11
12
13    public void preparedStatement(){
14        DataSource dataSource = SampleHelper.mysqlDatasource();
15        ConnectionSource source = ConnectionSourceHelper.getSingle(dataSource);
16        SQLManagerBuilder builder = new SQLManagerBuilder(source);
17        builder.setNc(new UnderlinedNameConversion());
18        builder.setInters(new Interceptor[]{new DebugInterceptor()});
19        //实现某些特殊功能
20        builder.setDbStyle(new MySqlDbStyle2());
21        SQLManager sqlManager = builder.build();
22
23
24        sqlManager.all(UserEntity.class);
25
26    }
27
28    public static class MySqlDbStyle2  extends MySqlStyle {
29        @Override
30        public SQLExecutor buildExecutor(ExecuteContext executeContext){
31            return  new MyExecutor(executeContext);
32        }
33
34        /**
35         * 考虑jdbc fetch size
36         */
37        public static class MyExecutor extends  BaseSQLExecutor{
38
39            public MyExecutor(ExecuteContext executeContext) {
40                super(executeContext);
41            }
42            @Override
43            protected ResultSetHolder dbQuery(Connection conn, String sql, List<SQLParameter> jdbcPara) throws SQLException {
44                if(this.getExecuteContext().target!= UserEntity.class){
45                    return super.dbQuery(conn,sql,jdbcPara);
46                }
47                //对于UserEntity对象查询，考虑使用特殊设置
48                PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
49                ps.setFetchSize(100000);
50                this.setPreparedStatementPara(ps, jdbcPara);
51                ResultSet rs = ps.executeQuery();
52                return new ResultSetHolder(ps, rs);
53            }
54
55        }
56    }
57
58}