# sqlhelper

**Repository Path**: quickthinking/sqlhelper

## Basic Information

- **Project Name**: sqlhelper
- **Description**: SQL开发、测试工具套件（分页、批处理、DDL等）其中分页支持MyBatis，SpringJDBC，commons-dbutils, jfinal, ebean, mango等
- **Primary Language**: Java
- **License**: LGPL-3.0
- **Default Branch**: master
- **Homepage**: https://gitee.com/fangjinuo/sqlhelper
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 16
- **Created**: 2020-03-02
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

[![License](https://img.shields.io/badge/license-LGPL3.0-green.svg)](https://github.com/fangjinuo/sqlhelper/blob/master/LICENSE)

[![Build Status](https://www.travis-ci.org/fangjinuo/sqlhelper.svg?branch=master)](https://travis-ci.org/fangjinuo/sqlhelper)
[![Codacy Badge](https://api.codacy.com/project/badge/Grade/9c27e94ffa3a4ee58279c36236b2b075)](https://www.codacy.com/manual/fs1194361820/sqlhelper?utm_source=github.com&utm_medium=referral&utm_content=fangjinuo/sqlhelper&utm_campaign=Badge_Grade)

[![JDK](https://img.shields.io/badge/JDK-1.6+-green.svg)](https://www.oracle.com/technetwork/java/javase/downloads/index.html)

[![MyBatis](https://img.shields.io/badge/MyBatis-3.x-green.svg)](http://www.mybatis.org/mybatis-3/index.html)
[![MyBatis](https://img.shields.io/badge/MyBatisPlus-3.x-green.svg)](http://www.mybatis.org/mybatisplus-3/index.html)
[![jFinal](https://img.shields.io/badge/jFinal-3.x-green.svg)](https://github.com/jfinal/jfinal)
[![jFinal](https://img.shields.io/badge/jFinal-4.x-green.svg)](https://github.com/jfinal/jfinal)
[![EBean](https://img.shields.io/badge/Ebean-11.x-green.svg)](https://ebean.io/docs/query/sqlquery)
[![Mango](https://img.shields.io/badge/Mango-1.6.x-green.svg)](https://github.com/jfaster/mango)


[![Spring-Jdbc](https://img.shields.io/badge/SpringJdbc-2.x-green.svg)](https://spring.io/projects/spring-framework)
[![Spring-Jdbc](https://img.shields.io/badge/SpringJdbc-3.x-green.svg)](https://spring.io/projects/spring-framework)
[![Spring-Jdbc](https://img.shields.io/badge/SpringJdbc-4.x-green.svg)](https://spring.io/projects/spring-framework)
[![Spring-Jdbc](https://img.shields.io/badge/SpringJdbc-5.x-green.svg)](https://spring.io/projects/spring-framework)

[![SpringBoot](https://img.shields.io/badge/SpringBoot-1.x-green.svg)](https://spring.io/projects/spring-boot/)
[![SpringBoot](https://img.shields.io/badge/SpringBoot-2.x-green.svg)](https://spring.io/projects/spring-boot/)

[![maven](https://img.shields.io/badge/maven-v3.0.2-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:3.0.2)
[![maven](https://img.shields.io/badge/maven-v3.0.1-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:3.0.1)
[![maven](https://img.shields.io/badge/maven-v3.0.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:3.0.0)
[![maven](https://img.shields.io/badge/maven-v2.4.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.4.0)
[![maven](https://img.shields.io/badge/maven-v2.3.1-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.3.1)
[![maven](https://img.shields.io/badge/maven-v2.3.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.3.0)
[![maven](https://img.shields.io/badge/maven-v2.2.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.2.0)
[![maven](https://img.shields.io/badge/maven-v2.1.2-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.1.2)
[![maven](https://img.shields.io/badge/maven-v2.1.1-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.1.1)
[![maven](https://img.shields.io/badge/maven-v2.1.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.1.0)
[![maven](https://img.shields.io/badge/maven-v2.0.7-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.7)
[![maven](https://img.shields.io/badge/maven-v2.0.6-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.6)
[![maven](https://img.shields.io/badge/maven-v2.0.4-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.4)
[![maven](https://img.shields.io/badge/maven-v2.0.3-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.3)
[![maven](https://img.shields.io/badge/maven-v2.0.2-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.2)
[![maven](https://img.shields.io/badge/maven-v2.0.1-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.1)
[![maven](https://img.shields.io/badge/maven-v2.0.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:2.0.0)
[![maven](https://img.shields.io/badge/maven-v1.2.3-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:1.2.3)
[![maven](https://img.shields.io/badge/maven-v1.2.1-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:1.2.1)
[![maven](https://img.shields.io/badge/maven-v1.2.0-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:1.2.0)
[![maven](https://img.shields.io/badge/maven-v1.1.1-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo.sqlhelper%20AND%20v:1.1.1)
[![maven](https://img.shields.io/badge/maven-v1.1.RELEASE-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo%20AND%20v:1.1-RELEASE)
[![maven](https://img.shields.io/badge/maven-v1.0.RELEASE-green.svg)](https://search.maven.org/search?q=g:com.github.fangjinuo%20AND%20v:1.0-RELEASE)


Tutorial : https://fangjinuo.gitee.io/docs/index.html


# sqlhelper
SQL Tools ( **Dialect**, **Pagination**, **DDL Dump**, **UrlParser**, **SqlStatementParser**, **WallFilter**, **BatchExecutor for Test**) based Java

## modules
|   module         | sqlhelper version | JDK |  Description      |
|------------------|-------------------|-----|-------------------|
|sqlhelper-dialect | 1.0+              |1.6+ |  the core         |
|sqlhelper-mybatis | 1.0+              |1.6+ |  the mybatis tools (MyBatis Pagination Plugin) |
|sqlhelper-mybatis-spring-boot | 1.0+  |1.6+ |  spring boot autoconfigure, starter for sqlhelper-mybatis|
|sqlhelper-mybatis-over-pagehelper|1.0+|1.6+ |  migrate your application from ***mybatis-pagehelper*** |
|sqlhelper-jfinal  | 1.2+              |1.6+ |  supports jfinal  |
|sqlhelper-ebean   | 1.2+              |1.6+ |  supports ebean   |
|sqlhelper-hibernate| 1.2+             |1.6+ |  supports hibernate|
|sqlhelper-mango   | 1.2+              |1.6+ |  supports mango   |
|sqlhelper-batchinsert|1.2+            |1.6+ |  Batch insert data to database, use it for performance |
|sqlhelper-springjdbc | 2.0.2+         |1.6+ |  supports spring-jdbc |
|sqlhelper-springjdbc-spring-boot| 2.0.2+|1.8+| spring boot autoconfigure, starter for sqlhelper-springjdbc |
|sqlhelper-mybatisplus|2.0.7+         |1.6+ |  supports mybatis-plus |
|sqlhelper-mybatisplus-spring-boot|2.0.7+ |1.8+ |  supports mybatis-plus in spring boot application |
|sqlhelper-dbutils | 2.2.0            |1.6+| supports Apache Commons-DBUtils |
|sqlhelper-examples|1.0+               |1.8+ |  some examples for there tools |


## tools usage
### pagination plugin
#### Key Features
1. Supports MyBatis, MyBatis-Plus, SpringJdbc, Apache Commons-DBUtils, JFinal, EBean, Mango, Hibernate
2. Supported 110+ databases, you can find them ***[here](https://github.com/f1194361820/sqlhelper/wiki/Pagination_Database)***. Conatins almost all chinese database:
  + AliSQL (阿里 MySQL)
  + AntDB (亚信)
  + CirroDB (东方国信 行云)
  + CynosDB (腾讯云数据库)
  + Doris (Apache Doris，百度研发)
  + DM (达梦)
  + EsgynDB (易鲸捷)
  + GaussDB (华为 高斯)
  + GBase (南大通用)
  + GoldenDB (中兴)
  + HHDB (恒辉数据库)
  + HighGo (瀚高)
  + HybridDB (阿里巴巴 分布式PostgreSQL)
  + K-DB (浪潮)
  + KingBase (金仓)
  + MaxCompute (阿里巴巴)
  + OBase (上海丛云信息科技)
  + OceanBase (阿里巴巴/蚂蚁金服 兼容mysql)
  + OSCAR (神州通用)
  + OpenBase (东软)
  + RadonDB (青云)
  + SequoiaDB (巨杉)
  + SinoDB (星瑞格)
  + TDSQL (腾讯 分布式MySQL)
  + TiDB (北京平凯星辰科技))
  + Trafodion (易鲸捷 EsgynDB的开源版)
  + UxDB (优炫数据库)
    
    
    If you want know all RDMBS ranking, you can find them in [DB Engines](https://db-engines.com/en/ranking/relational+dbms).
    
3. Supports multiple databases in one application 
4. Supports auto detect dialect, so the dialect option is optional (current supports this mode in mybatis environment)
5. **The performance is higher than Mybatis-PageHelper**, because the limit、offset will be setted with PrepareStatement placeholder '?'
6. Supports plugin use Java SPI
7. Supports spring boot 1.x , 2.x
8. Supports mybatis 3.x 
9. Supports mybatis-plus 
10. Supports JDK6+
11. Supports **Memory Pagination**
12. Supports SqlSymbolMapper, use it you can mapping any symbol to SQL symbol
13. Supports **Dump DDL**
14. Supports **subquery pagination** for mybatis, spring-jdbc, apache commons-dbutils
15. Supports **like parameter escape** for mybatis

#### sqlhelper vs mybatis-pagehelper
|  metric                  | mybatis-pagehelper |      sqlhelper    |
|--------------------------|:------------------:|:-----------------:|
|  databases               |         13         |         100+      |
|  multiple databases in runtime |   √          |         √         |
|  auto detect dialect     |         √          |         √         |
|  plugin                  |         √          |         √         |
|  PrepareStatement with '?'|         X         |         √         |                             
|  mybatis                 |         3.x        |         3.x       |
|  spring boot             |         1.x, 2.x   |         1.x, 2.x  |
|  JDK                     |         1.6+       |         1.6+      |
|  jFinal                  |         X          |         √         |
|  Mango                   |         X          |         √         |
|  EBean                   |         X          |         √         | 
|  国产数据库               |         X          | √ （参见上述列表）  |
|  Spring JDBC             |         X          | 2.x,3.x,4.x,5.x   | 
|  SqlSymbol Mapping       |         X          |         √         |
|  MyBatis-Plus            |         X          |         √         |   
|  Apache Commons-DBUtils  |         X          |         √         |
|  Subquery pagination     |         X          |         √         |  
                     


# Pagination
* [Quick Start](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart)
  + [MyBatis application](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_MyBatis)
    - [Installation](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_MyBatis)
    - [How To](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_MyBatis)
    - [Migrate from mybatis-pagehelper application](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_MyBatis)
  + [SpringJDBC application](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_SpringJDBC)
    - [Installation](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_SpringJDBC)
    - [How To](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_SpringJDBC)  
  + [jFinal application](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_jFinal)
    - [Installation](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_jFinal)
    - [How To](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_jFinal)
  + [MyBatis-Plus application](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_MyBatis_Plus)
  + [EBean application](https://github.com/fangjinuo/sqlhelper/wiki/Pagination_QuickStart_ebean)
* Advanced Usage
# UrlParser
Parse jdbc url.
## usage:
<pre>
String url = "jdbc:mysql://${localhost}:${port}....";
DatabaseInfo dbinfo = new JdbcUrlParser().parse(url)
...
</pre> 

# Batch Insert Tool
 If you want to optimize SQL performance, it is essential to create a large amount of sample data. This tool can meet your needs.

# [FAQ](https://github.com/fangjinuo/sqlhelper/wiki/FAQ)

# Contact
QQ Group: 750929088   
![QQ Group](https://github.com/fangjinuo/sqlhelper/blob/master/_images/qq_group.png)