org.jeecgframework.minidao.util.MiniDaoUtil Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of minidao-pe Show documentation
Show all versions of minidao-pe Show documentation
An powerful enhanced toolkit of SpringJdbc for simplify development
The newest version!
package org.jeecgframework.minidao.util;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jeecgframework.minidao.pagehelper.PageException;
import org.jeecgframework.minidao.pagehelper.dialect.AbstractHelperDialect;
import org.jeecgframework.minidao.pagehelper.dialect.PageAutoDialect;
import org.jeecgframework.minidao.pojo.MiniDaoPage;
import org.jeecgframework.minidao.sqlparser.AbstractSqlProcessor;
import org.jeecgframework.minidao.sqlparser.impl.JsqlparserSqlProcessor;
import org.jeecgframework.minidao.sqlparser.impl.SimpleSqlProcessor;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
*
* @Title:JdkLocalUtil
* @description:JdkLocalUtil
* @author 张代浩
* @date Jul 5, 2013 2:58:29 PM
* @version V1.0
*/
public class MiniDaoUtil {
private static final Log logger = LogFactory.getLog(MiniDaoUtil.class);
/**
* 缓存有没有问题
*/
static PageAutoDialect pageAutoDialect = new PageAutoDialect();
/**
* 数据库类型别名
*/
public static final String DATABSE_TYPE_HSQLDB = "hsqldb";
public static final String DATABSE_TYPE_H2 = "h2";
public static final String DATABSE_TYPE_PHOENIX = "phoenix";
public static final String DATABSE_TYPE_POSTGRE = "postgresql";
public static final String DATABSE_TYPE_MYSQL = "mysql";
public static final String DATABSE_TYPE_MARIADB = "mariadb";
public static final String DATABSE_TYPE_SQLITE = "sqlite";
public static final String DATABSE_TYPE_HERDDB = "herddb";
public static final String DATABSE_TYPE_ORACLE = "oracle";
public static final String DATABSE_TYPE_ORACLE9I= "oracle9i";
public static final String DATABSE_TYPE_DM = "dm"; //达梦数据库
public static final String DATABSE_TYPE_DB2 = "db2";
public static final String DATABSE_TYPE_INFORMIX = "informix";
public static final String DATABSE_TYPE_INFORMIX_SQLI = "informix-sqli";
public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";
public static final String DATABSE_TYPE_SQLSERVER2012 = "sqlserver2012";
public static final String DATABSE_TYPE_DERBY= "derby"; //Derby
public static final String DATABSE_TYPE_EDB= "edb";
public static final String DATABSE_TYPE_OSCAR = "oscar"; //神通
public static final String DATABSE_TYPE_KINGBASE = "kingbase";//人大金仓
public static final String DATABSE_TYPE_CLICKHOUSE = "clickhouse";
public static final String DATABSE_TYPE_HIGHGO = "highgo";//瀚高数据库
public static final String DATABSE_TYPE_XUGU = "xugu";//瀚高数据库
public static final String DATABSE_TYPE_ZENITH = "zenith"; //华为高斯 GaussDB
public static final String DATABSE_TYPE_POLARDB = "polardb"; //PolarDB
//update-begin---author:scott ---date:2024-07-04 for:SQL解析引擎改造支持普通和jsqlparser切换----
private static final boolean JSQLPARSER_AVAILABLE = checkJSqlParserAvailability();
protected static AbstractSqlProcessor abstractSqlProcessor;
static {
if (MiniDaoUtil.isJSqlParserAvailable()) {
abstractSqlProcessor = new JsqlparserSqlProcessor();
} else {
abstractSqlProcessor = new SimpleSqlProcessor();
}
}
//update-end---author:scott ---date::2024-07-04 for:SQL解析引擎改造支持普通和jsqlparser切换----
// /**
// * 分页SQL
// */
// public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}"; // mysql
// public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";// postgresql
// public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}"; // oracle
// public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}"; // sqlserver
/**
* 获取url
*
* @param dataSource
* @return
*/
private static String getUrl(DataSource dataSource) {
Connection conn = null;
try {
conn = dataSource.getConnection();
return conn.getMetaData().getURL();
} catch (SQLException e) {
throw new PageException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
//ignore
}
}
}
}
/**
* 自动获取DB类型
* @param dbUrl 数据库连接
* @return
*/
public static String getDbType(String dbUrl){
String dbType = pageAutoDialect.getDialectKeyByJdbcUrl(dbUrl);
if(dbType!=null){
dbType = dbType.toLowerCase();
}
return dbType;
}
// /**
// * 自动获取DB类型
// * @param dbUrl 数据库连接
// * @return
// */
// public static String getDbType(String dbUrl){
// String dbType = "";
// dbUrl = dbUrl.toLowerCase();
// if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_MYSQL)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_MYSQL;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_ORACLE)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_ORACLE;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_SQLSERVER)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_SQLSERVER;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_POSTGRE)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_POSTGRE;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_DM)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_DM;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_MARIADB)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_MARIADB;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_DB2)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_DB2;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_SQLITE)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_SQLITE;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_KINGBASE8)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_KINGBASE8;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_POLARDB)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_POLARDB;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_OSCAR)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_OSCAR;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_ZENITH)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_ZENITH;
// }else if(dbUrl.contains(MiniDaoUtil.DATABSE_TYPE_DERBY)) {
// dbType = MiniDaoUtil.DATABSE_TYPE_DERBY;
// }else {
// dbType = MiniDaoUtil.DATABSE_TYPE_OTHER;
// }
// return dbType;
// }
/**
* 自动获取DB类型
* @param dataSource
* @return
*/
public static String getDbType(DataSource dataSource){
long startTime=System.currentTimeMillis();
//获取数据库连接url
String dbUrl = getUrl(dataSource);
String dbType = getDbType(dbUrl);
long endTime=System.currentTimeMillis();
logger.info("获取DB类型:"+ dbType+ ",耗时:"+ (endTime-startTime) +"ms");
return dbType;
}
/**
* 按照数据库类型,封装SQL
*
* @param dbUrl
* 数据库类型
* @param sql
* @param page
* @param rows
* @return
*/
public static String createPageSql(String dbUrl, String sql, int page,int rows) {
AbstractHelperDialect dialect = pageAutoDialect.getDialect(dbUrl);
MiniDaoPage pageSetting = new MiniDaoPage();
pageSetting.setPage(page);
pageSetting.setRows(rows);
String executePageSql = dialect.getPageSql(sql,pageSetting);
return executePageSql;
}
/**
* 获取SQL查询记录数SQL
*
* @param sql
* @return
*/
public static String getCountSql(String sql) {
try {
sql = abstractSqlProcessor.getCountSql(sql);
} catch (Exception e) {
logger.warn("getCountSql error:" + e.getMessage());
}
return sql;
}
/**
* 去除SQL中的order by (为了兼容SQLServer)
*
* @param sql
* @return
*/
public static String removeOrderBy(String sql) {
try {
sql = abstractSqlProcessor.removeOrderBy(sql);
} catch (Exception e) {
logger.warn("removeOrderBy error:" + e.getMessage());
}
return sql;
}
/**
* 在SQL的最外层增加或修改ORDER BY子句
* @for TV360X-2551
* @param sql 原始SQL
* @param field 新的ORDER BY字段
* @param isAsc 是否正序
* @return
* @author chenrui
* @date 2024/9/27 17:25
*/
public static String addOrderBy(String sql, String field, boolean isAsc){
try {
sql = abstractSqlProcessor.addOrderBy(sql,field,isAsc);
} catch (Exception e) {
logger.warn("addOrderBy error:" + e.getMessage());
}
return sql;
}
/**
* 解析SQL查询字段
*
* @param parsedSql
* @return
*/
public static List