MyBatisSQLBuilder

栏目: 数据库 · 发布时间: 5年前

内容简介:在MyBatis中,可以使用SQLBuilder用Java代码动态构建SQL语句注意:文章首发:

在MyBatis中,可以使用SQLBuilder用 Java 代码动态构建 SQL 语句

示例代码

package bj.mybatis;

import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.WebApplicationType;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.quartz.QuartzAutoConfiguration;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/12/11 上午9:57
 */
@SpringBootApplication(exclude = QuartzAutoConfiguration.class)
public class App implements ApplicationListener<ApplicationReadyEvent> {

    public static void main(String[] args) {
        new SpringApplication(App.class) {{
            setWebApplicationType(WebApplicationType.NONE);
        }}.run(args);
    }

    @Resource
    private UserMapper userMapper;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Bean
    @Primary
    public MybatisProperties mybatisProperties() {
        return new MybatisProperties() {{
            this.setConfiguration(new Configuration() {{
                this.setMapUnderscoreToCamelCase(true);
            }});
        }};
    }

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        initDatabase();
        User one = new User("sky", "九天");
        User two = new User("thunder", "韵雷");
        User three = new User("rain", "暴雨");
        userMapper.create(one);
        userMapper.create(two);
        userMapper.create(three);
        System.out.println(one);
        System.out.println(two);
        System.out.println(three);
        System.out.println(userMapper.findAllByIdList(Arrays.asList(1, 2)));
    }

    @Data
    @NoArgsConstructor
    private static class User {
        private Integer id;
        private String username;
        private String realName;

        User(String username, String realName) {
            this.username = username;
            this.realName = realName;
        }
    }

    @SuppressWarnings("UnusedReturnValue")
    @Mapper
    interface UserMapper {
        @SuppressWarnings("unused")
        class Provider {
            public String findAllByIdList(Map<String, Object> params) {
                List<?> idList = (List<?>) params.get("idList");
                return new SQL() {{
                    SELECT("*");
                    FROM("user");
                    WHERE("id IN " + idList.stream().map(Object::toString).collect(Collectors.joining(",", "(", ")")));
                }}.toString();
            }

            public String create(Map<String, Object> params) {
                User user = (User) params.get("user");
                return new SQL() {{
                    INSERT_INTO("user");
                    VALUES("username", String.format("'%s'", user.username));
                    VALUES("real_name", "#{user.realName}");
                }}.toString();
            }
        }

        @SelectProvider(type = Provider.class, method = "findAllByIdList")
        List<User> findAllByIdList(@Param("idList") List<Integer> idList);

        @InsertProvider(type = Provider.class, method = "create")
        @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "user.id")
        int create(@Param("user") User user);
    }

    private void initDatabase() {
        jdbcTemplate.execute("DROP TABLE IF EXISTS user");
        jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(16) UNIQUE ,real_name VARCHAR(32) CHARSET 'utf8')");
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource() {{
            this.setJdbcUrl("jdbc:mysql://localhost/foo?characterEncoding=utf-8");
            this.setUsername("root");
            this.setPassword("root");
        }};
    }
}

控制台输出

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.0.RELEASE)

2018-12-11 11:12:33.049  INFO 17749 --- [           main] bj.mybatis.App                           : Starting App on MacBook-Air-2.local with PID 17749 (/Users/yuchao/temp/java/hellomaven/target/classes started by yuchao in /Users/yuchao/temp/java/hellomaven)
2018-12-11 11:12:33.103  INFO 17749 --- [           main] bj.mybatis.App                           : No active profile set, falling back to default profiles: default
2018-12-11 11:12:37.851  WARN 17749 --- [           main] reactor.netty.tcp.TcpResources           : [http] resources will use the default LoopResources: DefaultLoopResources {prefix=reactor-http, daemon=true, selectCount=4, workerCount=4}
2018-12-11 11:12:37.853  WARN 17749 --- [           main] reactor.netty.tcp.TcpResources           : [http] resources will use the default ConnectionProvider: PooledConnectionProvider {name=http, poolFactory=reactor.netty.resources.ConnectionProvider$$Lambda$263/708214419@806996}
2018-12-11 11:12:38.057  INFO 17749 --- [           main] bj.mybatis.App                           : Started App in 6.862 seconds (JVM running for 9.432)
2018-12-11 11:12:38.073  INFO 17749 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-12-11 11:12:38.538  INFO 17749 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
App.User(id=1, username=sky, realName=九天)
App.User(id=2, username=thunder, realName=韵雷)
App.User(id=3, username=rain, realName=暴雨)
[App.User(id=1, username=sky, realName=九天), App.User(id=2, username=thunder, realName=韵雷)]
2018-12-11 11:12:38.996  INFO 17749 --- [      Thread-20] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2018-12-11 11:12:39.009  INFO 17749 --- [      Thread-20] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

要点

  • SelectProvider 用来做Select语句生成器
  • InsertProvider 用来做Insert语句生成器。不能跟 SelectProvider 混用,返回类型不一致
  • Provider 不能是接口,只能是类,提供SQL语句的方法必须有访问权限
  • org.apache.ibatis.annotations.Options 注解可以配置自动生成列
  • Map<String, Object> params 可以获取到Mapper方法的所有参数
  • 自定义 org.mybatis.spring.boot.autoconfigure.MybatisProperties 类型的Bean可以动态配置MyBatis
  • Insert方法不能返回对象,只能返回改动的行数
  • 生成的SQL跟XML生成的SQL格式完全一致

MyBatisSQLBuilder结合jOOQ使用

Java代码

package bj.mybatis;

import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.Logger;
import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.session.Configuration;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamType;
import org.jooq.impl.DSL;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.WebApplicationType;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.quartz.QuartzAutoConfiguration;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import static jooq.tables.User.USER;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/12/11 上午9:57
 */
@SpringBootApplication(exclude = QuartzAutoConfiguration.class)
public class App implements ApplicationListener<ApplicationReadyEvent> {

    public static void main(String[] args) {
        new SpringApplication(App.class) {{
            setWebApplicationType(WebApplicationType.NONE);
        }}.run(args);
    }

    @Resource
    private UserMapper userMapper;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Bean
    @Primary
    public MybatisProperties mybatisProperties() {
        return new MybatisProperties() {{
            this.setConfiguration(new Configuration() {{
                this.setMapUnderscoreToCamelCase(true);
            }});
        }};
    }

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        initLogger();
        initDatabase();
        User one = new User("sky", "九天");
        User two = new User("thunder", "韵雷");
        User three = new User("rain", "暴雨");
        userMapper.create(one);
        userMapper.create(two);
        userMapper.create(three);
        System.out.println(one);
        System.out.println(two);
        System.out.println(three);
        System.out.println("Users(id in (1,2)):");
        System.out.println(userMapper.findAllByIdList(Arrays.asList(1, 2)));
    }

    @Data
    @NoArgsConstructor
    private static class User {
        private Integer id;
        private String username;
        private String realName;

        User(String username, String realName) {
            this.username = username;
            this.realName = realName;
        }
    }

    @SuppressWarnings("UnusedReturnValue")
    @Mapper
    interface UserMapper {
        @SuppressWarnings("unused")
        class Provider {
            private DSLContext dslContext = DSL.using(SQLDialect.MYSQL_8_0);

            public String findAllByIdList(Map<String, Object> params) {
                List<?> idList = (List<?>) params.get("idList");
                return dslContext.selectFrom(USER).where(USER.ID.in(idList)).getSQL(ParamType.INLINED);
            }

            public String create(Map<String, Object> params) {
                User user = (User) params.get("user");
                return dslContext.insertInto(USER)
                        .set(USER.USERNAME, user.username)
                        .set(USER.REAL_NAME, user.realName)
                        .getSQL(ParamType.INLINED);
            }
        }

        @SelectProvider(type = Provider.class, method = "findAllByIdList")
        List<User> findAllByIdList(@Param("idList") List<Integer> idList);

        @InsertProvider(type = Provider.class, method = "create")
        @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "user.id")
        int create(@Param("user") User user);
    }

    private void initLogger() {
        ((Logger) LoggerFactory.getLogger(org.jooq.Constants.class)).setLevel(Level.WARN);
    }

    private void initDatabase() {
        jdbcTemplate.execute("DROP TABLE IF EXISTS user");
        jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(16) UNIQUE ,real_name VARCHAR(32) CHARSET 'utf8')");
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource() {{
            this.setJdbcUrl("jdbc:mysql://localhost/foo?characterEncoding=utf-8");
            this.setUsername("root");
            this.setPassword("root");
        }};
    }
}

控制台输出

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.0.RELEASE)

2018-12-11 15:18:09.320  INFO 18826 --- [           main] bj.mybatis.App                           : Starting App on MacBook-Air-2.local with PID 18826 (/Users/yuchao/temp/java/hellomaven/target/classes started by yuchao in /Users/yuchao/temp/java/hellomaven)
2018-12-11 15:18:09.328  INFO 18826 --- [           main] bj.mybatis.App                           : No active profile set, falling back to default profiles: default
2018-12-11 15:18:11.540  INFO 18826 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-12-11 15:18:11.668  INFO 18826 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-12-11 15:18:12.281  WARN 18826 --- [           main] reactor.netty.tcp.TcpResources           : [http] resources will use the default LoopResources: DefaultLoopResources {prefix=reactor-http, daemon=true, selectCount=4, workerCount=4}
2018-12-11 15:18:12.281  WARN 18826 --- [           main] reactor.netty.tcp.TcpResources           : [http] resources will use the default ConnectionProvider: PooledConnectionProvider {name=http, poolFactory=reactor.netty.resources.ConnectionProvider$$Lambda$281/609825180@ffaaaf0}
2018-12-11 15:18:12.451  INFO 18826 --- [           main] bj.mybatis.App                           : Started App in 4.13 seconds (JVM running for 6.377)
App.User(id=1, username=sky, realName=九天)
App.User(id=2, username=thunder, realName=韵雷)
App.User(id=3, username=rain, realName=暴雨)
Users(id in (1,2)):
[App.User(id=1, username=sky, realName=九天), App.User(id=2, username=thunder, realName=韵雷)]
2018-12-11 15:18:13.038  INFO 18826 --- [      Thread-14] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2018-12-11 15:18:13.042  INFO 18826 --- [      Thread-14] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

注意:

  • 需要配置好jOOQ
  • 没有用到PreparedStatement,影响性能

文章首发: https://baijifeilong.github.io


以上所述就是小编给大家介绍的《MyBatisSQLBuilder》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

图解密码技术

图解密码技术

[日] 结城浩 / 周自恒 / 人民邮电出版社 / 2014-12 / 79.00元

本书以图配文的形式,详细讲解了6种最重要的密码技术:对称密码、公钥密码、单向散列函数、消息认证码、数字签名和伪随机数生成器。 第一部分讲述了密码技术的历史沿革、对称密码、分组密码模式(包括ECB、CBC、CFB、OFB、CTR)、公钥、混合密码系统。第二部分重点介绍了认证方面的内容,涉及单向散列函数、消息认证码、数字签名、证书等。第三部分讲述了密钥、随机数、PGP、SSL/TLS 以及密码技......一起来看看 《图解密码技术》 这本书的介绍吧!

图片转BASE64编码
图片转BASE64编码

在线图片转Base64编码工具

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具