内容简介：在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，影响性能
以上所述就是小编给大家介绍的《MyBatisSQLBuilder》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
ASP.NET 2.0入门经典
哈特 / 清华大学出版社 / 2006-8 / 78.00元
该书是Wrox红皮书中的畅销品种， 从初版、1.0版、1.1版到目前的2.0版，已经3次升级，不仅内容更加完善、实用，还展现了.NET 2.0的最新技术和ASP.NET 2.0最新编程知识，是各种初学者学习ASP.NET的优秀教程，也是Web开发人员了解ASP.NET 2.0新技术的优秀参考书。该书与《ASP.NET 2.0高级编程（第4版）》及其早期版本，曾影响到无数中国Web程序员。一起来看看 《ASP.NET 2.0入门经典》 这本书的介绍吧!
