package easy.db.util;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
//import com.meizu.bs.Env;
//import com.meizu.csc.keyword.Keyword;
//import com.meizu.framework.impl.dataAccess.DaoOperations;
//import com.meizu.framework.service.log.ILog;
//import com.meizu.framework.service.log.LogFactory;
/**
* 描述： 服务端与客户端存取数据库简易通信类
* 作者： 张军强
* 创建时间：2010-10-10
* 这个类主要用于与客户端交互,因为客户端提交过来的数据都是字符串
* 为了免去类型转换的麻烦,在本类中使用的HasMap几乎全部是
* HashMap<String,String> 而不使用更通用的HashMap<String,Object>
* 或者HashMap<Ojbect,Object>
*/
public class EasyDB {
// private static final Log logger = LogFactory.getLog(EasyDB.class);
// private static final ILog logger = LogFactory.getLog(EasyDB.class);
private Connection con=null;
private boolean debug=false;
private String lastSQL="";
private String dbType="";
private String slowSQL="";
private int slowSQLTime=1000;
private long startPoint=0L;
private long endPoint=0L;
private String errorMsg="";
private boolean isError=false;
private boolean isThrowError=true;
private int queryRowCount=0;
private LinkedList<String> sqlList=new LinkedList<String>();
private int logSqlCount=10;
private boolean isForDebug=false;
// private DaoOperations daoOperation;
/**
* mssql
*/
private String url = "jdbc:jtds:sqlserver://172.16.10.215:1433;SelectMethod=cursor;DatabaseName=meizu_db";
private String driverClassName = "net.sourceforge.jtds.jdbc.Driver"; //net.sourceforge.jtds.jdbc.Driver
private String username = "jqzhang";
private String password = "jqzhang";
/**
* mysql
*/
// private String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8";
// private String driverClassName = "com.mysql.jdbc.Driver"; //com.microsoft.jdbc.sqlserver.SQLServerDriver
// private String username = "root";
// private String password = "1016";
public EasyDB()
{
//con=getConnection();
}
public EasyDB(String driverClassName, String url,String username,String password)
{
this.driverClassName=driverClassName;
this.url=url;
this.username=username;
this.password=password;
con=getConnection(driverClassName, url, username, password);
}
public EasyDB(boolean isDebug)
{
isForDebug=true;
con=getConnection(driverClassName, url, username, password);
}
/**
* 最后执行的SQL
* @return
*/
public String getLastSQL()
{
return this.lastSQL;
}
public int getQueryRowCount() {
return queryRowCount;
}
public void setDebug(boolean debug) {
this.debug = debug;
}
public void setLogSqlCount(int logSqlCount) {
this.logSqlCount = logSqlCount;
}
public int getLogSqlCount() {
return logSqlCount;
}
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
public String getErrorMsg() {
return errorMsg;
}
public void setError(boolean isError) {
this.isError = isError;
}
public boolean isError() {
return isError;
}
public boolean isNotError() {
return !isError;
}
public void setThrowError(boolean isThrowError) {
this.isThrowError = isThrowError;
}
public boolean isThrowError() {
return isThrowError;
}
public boolean isDebug() {
return debug;
}
public void setSlowSQLTime(int slowSQLTime) {
this.slowSQLTime = slowSQLTime;
}
public int getSlowSQLTime() {
return slowSQLTime;
}
// public void setDaoOperation(DaoOperations daoOperation) {
// this.daoOperation = daoOperation;
// }
// public DaoOperations getDaoOperation() {
// return daoOperation;
// }
public String getSlowSQL() {
return slowSQL;
}
public void setDbType(String dbType) {
this.dbType = dbType;
}
public String getDbType() {
return dbType;
}
@SuppressWarnings("unused")
private boolean isEmpty(Object obj) {
if (obj == null) {
return true;
} else {
return false;
}
}
/**判断字符串是否为空
*
* @param str
* @return
*/
private boolean isEmpty(String str) {
if (str == null || str.trim().equalsIgnoreCase("")) {
return true;
} else {
return false;
}
}
private String getTrimStr(String str) {
if (isEmpty(str)) {
return "";
} else {
return str.trim();
}
}
/**合成参数sql Statement ,返回一个HashMap 里面包含SQL key:sql ,和参数形式的SQL key:sql_param
* 还有参数化好的 Statement key:stmt
* @param sql
* @param sql_para
* @param valueMap
* @return
*/
@SuppressWarnings("unchecked")
private Object makeSQL(String sql,String sql_para,HashMap<String, String> valueMap)
{
HashMap<String, Object> rtnMap = new HashMap<String, Object>();
rtnMap.put("sql", sql);
rtnMap.put("sql_param", sql_para);
HashMap<String, Object> jdbcMap=( HashMap<String, Object>) makeJdbcParams(sql_para);
PreparedStatement ps=(PreparedStatement)bindParams(jdbcMap.get("sql").toString(),(HashMap<Integer, String>)jdbcMap.get("sql_map"), valueMap);
rtnMap.put("stmt", ps);
if(sqlList.size()>logSqlCount)
{
sqlList.removeFirst();
}
sqlList.addLast(sql);
this.lastSQL="非参数SQL:\t"+sql+"\r\n"+"参数SQL_PARAM:\t"+sql_para;
this.slowSQL="慢SQL(slowSQL)\r\n"+this.lastSQL;
if(debug)
{
// logger.debug(this.lastSQL);
}
return rtnMap;
}
/**
* 利用查询的参数,返回首行首列
* @param tableName 表名
* @param resultFileds 字段列
* @param whereMap 条件
* @param valueMap 条件值
* @return
*/
@SuppressWarnings("unchecked")
public Object scalar(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap)
{
HashMap<String, Object> rtnMap= (HashMap<String, Object>) select( tableName, resultFileds,whereMap, valueMap);
return scalar( rtnMap.get("stmt"));
}
/**
* 利用查询的参数,返回首行
* @param tableName 表名
* @param resultFileds 字段列
* @param whereMap 条件
* @param valueMap 条件值
* @return
*/
@SuppressWarnings("unchecked")
public HashMap<String, Object> scalarRow(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap)
{
HashMap<String, Object> rtnMap= (HashMap<String, Object>) select( tableName, resultFileds,whereMap, valueMap);
return scalarRow( rtnMap.get("stmt"));
}
/**
* 利用查询的参数,返回数据集
* @param tableName 表名
* @param resultFileds 字段列
* @param whereMap 条件
* @param valueMap 条件值
* @return
*/
@SuppressWarnings("unchecked")
public List<HashMap<String,Object>> query(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap)
{
HashMap<String, Object> rtnMap= (HashMap<String, Object>) select( tableName, resultFileds,whereMap, valueMap);
return query( rtnMap.get("stmt"));
}
@SuppressWarnings("unchecked")
public List<HashMap<String,Object>> query(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderFields)
{
HashMap<String, Object> rtnMap= (HashMap<String, Object>) select( tableName, resultFileds,whereMap, valueMap,orderFields);
return query( rtnMap.get("stmt"));
}
public List<HashMap<String,Object>> query(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderFields,int top )
{
return query(tableName, resultFileds, whereMap, valueMap, orderFields, String.valueOf(top));
}
@SuppressWarnings("unchecked")
public List<HashMap<String,Object>> query(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderFields,String top )
{
HashMap<String, Object> rtnMap=null;
if (this.dbType.equalsIgnoreCase("mysql")) {
rtnMap=(HashMap<String, Object>) select_top_mysql( tableName, resultFileds,whereMap, valueMap,orderFields, top);
} else
{
rtnMap=(HashMap<String, Object>) select_top_mssql( tableName, resultFileds,whereMap, valueMap,orderFields,top);
}
return query( rtnMap.get("stmt"));
}
/**
* 返回分页的HashMap
* @param tableName 表名
* @param resultFileds 显示的列
* @param whereMap 条件Map
* @param valueMap 条件值 Map
* @param orderField 排序健
* @param start 参考 mysql limit start,limit
* @param limit 参考 mysql limit start,limit
* @return
*/
public List<HashMap<String,Object>> query(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField,int start,int limit)
{
return query(tableName, resultFileds, whereMap, valueMap, orderField, String.valueOf( start), String.valueOf(limit));
}
/**
* 返回分页的HashMap
* @param tableName 表名
* @param resultFileds 显示的列
* @param whereMap 条件Map
* @param valueMap 条件值 Map
* @param orderField 排序健
* @param start 参考 mysql limit start,limit
* @param limit 参考 mysql limit start,limit
* @return
*/
@SuppressWarnings("unchecked")
public List<HashMap<String,Object>> query(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField,String start,String limit)
{
HashMap<String, Object> rtnMap=null;
if(this.dbType.equalsIgnoreCase("mysql"))
{
rtnMap= (HashMap<String, Object>) select_mysql_grid(tableName, resultFileds, whereMap, valueMap, orderField, start, limit);
}
else
{
rtnMap= (HashMap<String, Object>) select_mssql_grid(tableName, resultFileds, whereMap, valueMap, orderField, start, limit);
}
return query( rtnMap.get("stmt"));
}
/**
* 合成查询SQL
*
* @param tableName
* @param resultFileds
* @param whereMap
* @param valueMap
* @return
*/
@SuppressWarnings("unchecked")
private Object select(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField)
{
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
String select="select "+resultFileds+" from "+ tableName+ mapWhere.get("where")+" order by "+ orderField;
String select_para="select "+ resultFileds+" from "+ tableName+mapWhere.get("where_param")+" order by "+ orderField;
return makeSQL(select, select_para, valueMap);
}
@SuppressWarnings("unchecked")
private Object select_top_mysql(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField,String top)
{
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
String select="select "+resultFileds+" from "+ tableName+ mapWhere.get("where")+" order by "+ orderField+ "limit "+ top;
String select_para="select "+ resultFileds+" from "+ tableName+mapWhere.get("where_param")+" order by "+ orderField+" limit "+ top;
return makeSQL(select, select_para, valueMap);
}
@SuppressWarnings("unchecked")
private Object select_top_mssql(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField,String top)
{
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
String select="select top "+ top+" "+resultFileds+" from "+ tableName+ mapWhere.get("where")+" order by "+ orderField;
String select_para="select top "+top+" "+ resultFileds+" from "+ tableName+mapWhere.get("where_param")+" order by "+ orderField;
return makeSQL(select, select_para, valueMap);
}
private String tplReplace(String str,HashMap<String, String> map)
{
String key="";
for (Iterator<String> it = map.keySet().iterator(); it.hasNext();) {
key=it.next().toString();
str=str.replaceAll("\\{"+key+"\\}", map.get(key));
}
return str;
}
/**
* mssql分页
* @param tableName
* @param resultFileds
* @param whereMap
* @param valueMap
* @param orderField
* @param start
* @param limit
* @return
*/
@SuppressWarnings("unchecked")
private Object select_mssql_grid(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField,String start,String limit)
{
//为好好看这样写,但性能不好
String temp=" SELECT count(1) FROM {tableName} {where} ";
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
HashMap<String, String> tplMap=new HashMap<String, String>();
tplMap.put("limit", limit);
tplMap.put("start", start);
tplMap.put("tableName", tableName);
tplMap.put("resultFileds",resultFileds );
tplMap.put("orderField", orderField);
tplMap.put("where", mapWhere.get("where"));
tplMap.put("where2", mapWhere.get("where").replaceFirst("where", " and "));
String select= tplReplace(temp, tplMap);
tplMap.put("where", mapWhere.get("where_param"));
tplMap.put("where2", mapWhere.get("where_param").replaceFirst("where", " and "));
String select_para= tplReplace(temp, tplMap);
HashMap<String, Object> rtnMap=null;
rtnMap=(HashMap<String, Object> )makeSQL(select, select_para, valueMap);
queryRowCount=Integer.valueOf( scalar(rtnMap.get("stmt")).toString());
//为好好看这样写,但性能不好
temp="SELECT TOP {limit} {resultFileds} "+
" FROM {tableName} "+
" WHERE {orderField} NOT IN "+
" ( "+
" SELECT TOP {start} {orderField} FROM {tableName} {where} ORDER BY {orderField} "+
" ) "+
" {where2} ORDER BY {orderField}";
// HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
// HashMap<String, String> tplMap=new HashMap<String, String>();
tplMap.put("limit", limit);
tplMap.put("start", start);
tplMap.put("tableName", tableName);
tplMap.put("resultFileds",resultFileds );
tplMap.put("orderField", orderField);
tplMap.put("where", mapWhere.get("where"));
tplMap.put("where2", mapWhere.get("where").replaceFirst("where", " and "));
select= tplReplace(temp, tplMap);
tplMap.put("where", mapWhere.get("where_param"));
tplMap.put("where2", mapWhere.get("where_param").replaceFirst("where", " and "));
select_para= tplReplace(temp, tplMap);
return makeSQL(select, select_para, valueMap);
}
/**
*mysql 分页
* @param tableName
* @param resultFileds
* @param whereMap
* @param valueMap
* @param orderField
* @param start
* @param limit
* @return
*/
@SuppressWarnings("unchecked")
private Object select_mysql_grid(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap,String orderField,String start,String limit)
{//为好好看这样写,但性能不好
String temp=" SELECT count(1) FROM {tableName} {where} ";
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
HashMap<String, String> tplMap=new HashMap<String, String>();
tplMap.put("limit", limit);
tplMap.put("start", start);
tplMap.put("tableName", tableName);
tplMap.put("resultFileds",resultFileds );
tplMap.put("orderField", orderField);
tplMap.put("where", mapWhere.get("where"));
tplMap.put("where2", mapWhere.get("where").replaceFirst("where", " and "));
String select= tplReplace(temp, tplMap);
tplMap.put("where", mapWhere.get("where_param"));
tplMap.put("where2", mapWhere.get("where_param").replaceFirst("where", " and "));
String select_para= tplReplace(temp, tplMap);
HashMap<String, Object> rtnMap=null;
rtnMap=(HashMap<String, Object> )makeSQL(select, select_para, valueMap);
queryRowCount=Integer.valueOf( scalar(rtnMap.get("stmt")).toString());
temp="SELECT {resultFileds} "+
" FROM {tableName} "+
" {where} order by {orderField} limit {start},{limit} ";
// HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
// HashMap<String, String> tplMap=new HashMap<String, String>();
tplMap.put("limit", limit);
tplMap.put("start", start);
tplMap.put("tableName", tableName);
tplMap.put("resultFileds",resultFileds );
tplMap.put("orderField", orderField);
tplMap.put("where", mapWhere.get("where"));
select= tplReplace(temp, tplMap);
tplMap.put("where", mapWhere.get("where_param"));
select_para= tplReplace(temp, tplMap);
return makeSQL(select, select_para, valueMap);
}
@SuppressWarnings("unchecked")
private Object select(String tableName,String resultFileds,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap)
{
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
String select="select "+resultFileds+" from "+ tableName+ mapWhere.get("where");
String select_para="select "+ resultFileds+" from "+ tableName+mapWhere.get("where_param");
return makeSQL(select, select_para, valueMap);
}
private void setConnection()
{
try
{
/**
* 当不用现在的框架时可以将以下信息去掉注
*/
if(isForDebug)
{
Class.forName(this.driverClassName);
con = DriverManager.getConnection(this.url,this.username, this.password);
if(url.toLowerCase().indexOf(":mysql:")>0)
{
this.dbType="mysql";
} else if(url.toLowerCase().indexOf(":sqlserver:")>0)
{
this.dbType="sqlserver";
}
}
else
{
/*
* 与现在的系统集成
*/
// con=this.daoOperation.getDataSource().getConnection();
this.url= con.getMetaData().getURL();
if(url.toLowerCase().indexOf(":mysql:")>0)
{
this.dbType="mysql";
} else if(url.toLowerCase().indexOf(":sqlserver:")>0)
{
this.dbType="sqlserver";
}
}
}
catch(Exception e)
{
error(e);
}
}
/**
* 取得连接
* @return
*/
private Connection getConnection()
{
// try
// {
// this.url=(String)Env.getProperty("app.database.url");
// this.driverClassName=(String)Env.getProperty("app.database.driver");
// this.username=(String)Env.getProperty("app.database.username");
// this.password=(String)Env.getProperty("app.database.password");
// }
// catch (Exception e) {
// error("初始化连接出错");
// }
if(con!=null)
{
try
{
if(this.con.isClosed())
{
setConnection();
}
}
catch(Exception e)
{
}
return con;
}
setConnection();
return con;
}
public void close()
{
try
{
if(con!=null&&!this.con.isClosed())
{
con.close();
}
}catch(Exception e)
{
error(e);
}
}
private Connection getConnection(String driverClassName, String url,String username,String password)
{
Connection con=this.con;
if(con!=null)
{
return this.con;
}
try
{
Class.forName(driverClassName);
con = DriverManager.getConnection(url, username, password);
if(url.toLowerCase().indexOf(":mysql:")>0)
{
this.dbType="mysql";
} else if(url.toLowerCase().indexOf(":sqlserver:")>0)
{
this.dbType="sqlserver";
}
this.con=con;
}
catch(Exception e)
{
error(e);
}
return con;
}
/**
* 设置连接
* @param conn
*/
public void setConnection(Connection conn)
{
this.con=conn;
}
/**
* 开启事条
*/
public void beginTran()
{
try
{
if(con==null) con=getConnection();
con.setAutoCommit(false);
}
catch (Exception e) {
this.error(e);
}
}
/**
* 回滚事务
*/
public void rollback()
{
try
{
this.con.rollback();
}
catch (Exception e) {
this.error(e);
}
}
/**
* 提交事务
*/
public void commit()
{
try
{
this.con.commit();
this.con.setAutoCommit(true);
}
catch (Exception e) {
try
{
this.con.rollback();
}
catch (Exception ee) {
this.error(ee);
}
this.error(e);
}
}
/**
* 是显导常
* @param e
*/
public void error(Exception e)
{
isError=true;
errorMsg=e.getMessage();
// logger.error(e.getMessage());
if(isThrowError())
{
throw new RuntimeException(e);
}
}
public void error(String s)
{
//logger.error(s);
try
{
throw new Exception(s);
}
catch (Exception e) {
error(e);
}
}
@SuppressWarnings("deprecation")
private String getStrFromHashMap(String key,HashMap<String, String> map)
{
String value=map.get(key);
if(value==null)
{
return "";
} else
{
return URLDecoder.decode(value).replaceAll("'", "''");
}
}
/**
* 绑定参数
* @param sql
* @param parasMap
* @param valueMap
* @return
* @throws SQLException
*/
private Object bindParams( String sql,HashMap<Integer, String> parasMap,HashMap<String, String> valueMap)
{
// Connection con=null;
PreparedStatement stmt=null;
try {
if(this.con==null||con.isClosed())
con=getConnection();
stmt=con.prepareStatement(sql);
if(stmt==null)
{
throw new Exception("获取prepareStatement出错!!!");
}
if(stmt.getParameterMetaData().getParameterCount()!=parasMap.size())
{
error("参数不一致,请检查参数的一致性!");
return null;
}
for (int i = 0; i < parasMap.size(); i++) {
if(valueMap.get(parasMap.get(i))==null)
{
throw new Exception("取不到参数名称为:"+parasMap.get(i)+",的参数!!!");
}else
{
stmt.setString(i+1, valueMap.get(parasMap.get(i)));
}
}
}
catch (Exception e) {
try
{
stmt.close();
}
catch (Exception err) {
}
error(e);
}
return stmt;
}
/**
* 构造参数
* @param sql
* @return
*/
private Object makeJdbcParams(String sql)
{
String regExp="\\:(\\w+)";
Pattern p=Pattern.compile(regExp);
Matcher m=p.matcher(sql);
HashMap<Integer, String> map=new HashMap<Integer, String>();
Integer i=0;
while (m.find()) {
map.put(i, m.group(1));
i++;
}
sql=sql.replaceAll(regExp, "?");
HashMap<String, Object> rtnMap = new HashMap<String, Object>();
rtnMap.put("sql", sql);
rtnMap.put("sql_map", map);
return rtnMap;
}
/**
* 执行SQL
* @param stmt
* @return
*/
public int execute(Object stmt)
{ initError();
PreparedStatement ps=(PreparedStatement)(stmt);
String sql="";
try
{
int rtn=0;
startPoint=System.nanoTime();
sql=ps.toString().toLowerCase();
if((sql.indexOf("update")>0)||(sql.indexOf("delete")>0))
{
if(!(sql.indexOf(" where ")>0))
{
ps.close();
this.error("此类不支持不带条件的删除与更新操作");
return -1;
}
}
ps.execute();
endPoint=System.nanoTime();
if(!isTimeOut())
{
this.slowSQL="";
}
rtn=ps.getUpdateCount();
return rtn;
}
catch (Exception e) {
error(e);
if(this.debug)
printSQL(lastSQL);
return -1;
} finally
{ try
{
ps.close();
}
catch (Exception e) {
// logger.info("回收资源时有问题");
}
}
}
/**
* 返回首行首列
* @param stmt
* @return
*/
public Object scalar(Object stmt){
Object rtn=null;
PreparedStatement ps=(PreparedStatement)(stmt);
ResultSet rs=null;
try{
rs=ps.executeQuery();
if(rs.next())
{
rtn= rs.getString(1);
}
rs.close();
ps.close();
}
catch (Exception e) {
error(e);
}
return rtn;
}
/**
* 返回首行
* @param stmt
* @return
*/
public HashMap<String,Object> scalarRow(Object stmt){
PreparedStatement ps=(PreparedStatement)(stmt);
ResultSet rs=null;
HashMap<String, Object> tempHash = new HashMap<String, Object>();
try{
rs=ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if(rs.next())
{
for (int i = 0; i < rsmd.getColumnCount(); i++) {
tempHash.put(rsmd.getColumnName(i + 1), rs
.getString(rsmd.getColumnName(i + 1)));
}
}
rs.close();
}
catch (Exception e) {
error(e);
}
return tempHash;
}
private boolean isTimeOut()
{
long st=(endPoint-startPoint)/(1000*1000);
if(st>slowSQLTime)
{
// logger.info(this.slowSQL);
// logger.info("慢查询时间:"+String.valueOf( st)+"毫秒\r\n");
return true;
}else
{
return false;
}
}
private void initError()
{
isError=false;
errorMsg="";
}
@SuppressWarnings("unchecked")
public List<HashMap<String,Object>> query(String sql){
HashMap<String, Object> rtnMap= (HashMap<String, Object>) makeSQL(sql, sql, null);
return query(rtnMap.get("stmt"));
}
public String safeStr(String str)
{
str=str.replaceAll("\\\\", "\\\\\\\\");
str=str.replaceAll("'", "\\'");
return str;
}
@SuppressWarnings("unchecked")
public List<HashMap<String,Object>> query(String sql,HashMap<String, String> valueMap){
String sql_param=sql;
Object[] keys=valueMap.keySet().toArray();
Comparator<Object> c = new Comparator<Object>(){
public int compare(Object o1, Object o2) {
int a1 = o1.toString().length();
int a2 = o2.toString().length();
if(a1<a2)return 1;
if(a1==a2)return 0;
else return -1;
}
};
Arrays.sort(keys, c);
for(Object s:keys){
sql=sql.replaceAll("\\:"+s, "'"+ safeStr( valueMap.get(s).toString())+"'");
}
HashMap<String, Object> rtnMap= (HashMap<String, Object>) makeSQL(sql, sql_param, valueMap);
return query(rtnMap.get("stmt"));
}
public Object scalar(String sql,HashMap<String, String> valueMap)
{
List<HashMap<String,Object>> obj= query(sql, valueMap);
if(obj==null||(obj!=null&&obj.size()==0))
{
return null;
}
else
{
Iterator<String> it = obj.get(0).keySet().iterator();
return obj.get(0).get( it.next());
}
}
public int execute(String sql,HashMap<String, String> valueMap){
String key="";
String sql_param=sql;
for (Iterator<String> it = valueMap.keySet().iterator(); it.hasNext();) {
key=it.next().toString();
sql=sql.replaceAll("\\:"+key, "'"+ safeStr( valueMap.get(key).toString())+"'");
}
HashMap<String, Object> rtnMap= (HashMap<String, Object>) makeSQL(sql, sql_param, valueMap);
return execute(rtnMap.get("stmt"));
}
/**
* 返回HashMap数据集
* @param stmt
* @return
*/
public List<HashMap<String,Object>> query(Object stmt){
initError();
PreparedStatement ps=(PreparedStatement)(stmt);
List<HashMap<String, Object>> tempList = new ArrayList<HashMap<String, Object>>();
ResultSet rs=null;
try{
if(ps==null)
{
error("PreparedStatement为空,查询直接返回");
return null;
}
startPoint=System.nanoTime();
rs=ps.executeQuery();
endPoint=System.nanoTime();
if(!isTimeOut())
{
this.slowSQL="";
}
ResultSetMetaData rsmd = rs.getMetaData();
HashMap<String, Object> tempHash = null;
while (rs.next()) {
tempHash = new HashMap<String, Object>();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
tempHash.put(rsmd.getColumnName(i + 1), rs
.getString(rsmd.getColumnName(i + 1)));
// tempHash.put(rsmd.getColumnName(i + 1), rs
// .getString(rsmd.getColumnName(i + 1)));
}
tempList.add(tempHash);
}
}catch(SQLException e){
this.error(e);
if(debug)
printSQL(lastSQL);
}
finally
{
try
{
rs.close();
ps.close();
}catch(Exception e)
{
// logger.info("回收资源时有问题");
}
}
return tempList;
}
/**
* 利用post 过来的HashMap
* @param fieldMap
* @param valueMap
* @return
*/
public HashMap<String, String> makeValueMap(HashMap<String, String> fieldMap,HashMap<String, String> valueMap)
{
String key="";
HashMap<String, String> rtnMap=new HashMap<String, String>();
for (Iterator<String> it = fieldMap.keySet().iterator(); it.hasNext();) {
key=it.next().toString();
if(valueMap.get(fieldMap.get(key))==null)
{
}
else
{
rtnMap.put(key,valueMap.get(fieldMap.get(key)));
}
}
return rtnMap;
}
public LinkedHashMap<String, String> getWhereMap()
{
return new LinkedHashMap<String, String>();
}
public HashMap<String, String> getHashMap()
{
return new LinkedHashMap<String, String>();
}
public void printSQL()
{
System.out.println(this.lastSQL);
}
public void printSQLs()
{
for (int i = 0; i < sqlList.size(); i++) {
System.out.println(this.sqlList.get(i));
}
}
public void printSQL(String sql)
{
if(sql==null)
{
System.out.println(this.lastSQL);
}
else
{
System.out.println(sql);
}
}
/**
* 合成插入语句
* @param tableName
* @param insertMap
* @return
*/
@SuppressWarnings("unchecked")
public Object insert(String tableName,HashMap<String, String> insertMap)
{
String key="";
String fileds="";
String filedsValue="";
String paraFields="";
String value="";
String insert="";
String param_insert="";
for (Iterator<String> it = insertMap.keySet().iterator(); it.hasNext();) {
key=it.next().toString();
fileds+=key+",";
paraFields+=":"+ key+",";
value=getStrFromHashMap(key, insertMap);
filedsValue+="'"+ value+"',";
}
fileds=fileds.substring(0, fileds.length()-1);
paraFields=paraFields.substring(0, paraFields.length()-1);
filedsValue=filedsValue.substring(0, filedsValue.length()-1);
insert="insert into "+ tableName+"("+ fileds+") values ("+ filedsValue+")";
param_insert="insert into "+ tableName+"("+ fileds+") values ("+ paraFields+")";
HashMap<String, Object> rtnMap= (HashMap<String, Object>) makeSQL(insert, param_insert, insertMap);
return execute( rtnMap.get("stmt"));
}
/**
* 合成更新语句并执行
* @param tableName
* @param updateMap
* @param whereMap
* @param valueMap
* @return
*/
@SuppressWarnings("unchecked")
public Object update(String tableName,HashMap<String, String> updateMap,LinkedHashMap<String, String> whereMap,HashMap<String, String> valueMap)
{
String key="";
String fileds="";
String filedsValue="";
String value="";
String update="";
String param_update="";
if(valueMap==null)
valueMap=updateMap;
for (Iterator<String> it = updateMap.keySet().iterator(); it.hasNext();) {
key=it.next().toString();
fileds+=key+"=:"+key+",";
value=getStrFromHashMap(key, updateMap);
filedsValue+=key+"='"+value+"',";
}
fileds=fileds.substring(0, fileds.length()-1);
filedsValue=filedsValue.substring(0, filedsValue.length()-1);
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, valueMap));
param_update="update "+ tableName+ " set "+ fileds+mapWhere.get("where_param");
update="update "+ tableName+ " set "+ filedsValue+ mapWhere.get("where");
HashMap<String, Object> rtnMap= (HashMap<String, Object>) makeSQL(update, param_update, mergeHashMap(updateMap, valueMap));
return execute( rtnMap.get("stmt"));
}
/**
* 克隆HashMap
* @param map
* @return
*/
public HashMap<String, String> cloneHashMap(HashMap<String, String> map)
{
HashMap<String, String> rtn=new HashMap<String, String>();
String key="";
for (Iterator<String> it = map.keySet().iterator(); it.hasNext();) {
key = it.next().toString();
rtn.put(key, map.get(key));
}
return rtn;
}
public boolean test(String regular,String value)
{
String patten="";
boolean rtn=false;
if(regular.equalsIgnoreCase("uncheck"))
{
return true;
} else
if(regular.equalsIgnoreCase("int"))
{
patten="^\\d+$";
} else if(regular.equalsIgnoreCase("float"))
{
patten="^(-?\\d+)(\\.\\d+)?$";
}else if(regular.equalsIgnoreCase("email"))
{
patten="^[\\w-]+(\\.[\\w-]+)*@[\\w-]+(\\.[\\w-]+)+$";
}else if(regular.equalsIgnoreCase("cn"))
{
patten="^[\u4e00-\u9fa5]+$";
}else if(regular.equalsIgnoreCase("url"))
{
patten="^(https|ftp|http)(://)[^\\s]*[.][^\\s]*|(www)[^\\s]*[.][^\\s]*$";
}else if(regular.equalsIgnoreCase("qq"))
{
patten="^[1-9][0-9]{4,}$";
}else if(regular.equalsIgnoreCase("en"))
{
patten="^[a-zA-Z]+$";
}else if(regular.equalsIgnoreCase("date"))
{
// String[] fmt={"yyyy-MM-dd","dd/MM/yyyy"};
// for (int i = 0; i < fmt.length; i++) {
// SimpleDateFormat sdf=new SimpleDateFormat(fmt[i]);
// try
// {
// sdf.parse(value);
// rtn= true;
// break;
// }
// catch(Exception e)
// {
// rtn=false;
// }
// }
// return rtn;
patten="^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$";
} else
{
patten=regular;
}
Pattern ptn=Pattern.compile(patten,Pattern.CASE_INSENSITIVE);
Matcher m = ptn.matcher(value);
if(m!=null&&m.find())
{
rtn=true;
}
return rtn;
}
public boolean valueValid(HashMap<String, String> regularMap,HashMap<String, String> valueMap)
{
String key="";
String op="";
String value="";
String[] ops=null;
boolean rtn=true;
for (Iterator<String> it = regularMap.keySet().iterator(); it.hasNext();) {
key = it.next().toString();
op=regularMap.get(key);
value=valueMap.get(key);
if(op!=null)
ops=op.split(",");
for (int i = 0; i < ops.length; i++) {
if(!test(ops[i],value))
{
rtn=false;
break;
}
}
}
return rtn;
}
/**
* 合成HashMap
* @param map
* @param map2
* @return
*/
public HashMap<String, String> mergeHashMap(HashMap<String, String> map,HashMap<String, String> map2)
{
HashMap<String, String> rtn=new HashMap<String, String>();
String key="";
for (Iterator<String> it = map.keySet().iterator(); it.hasNext();) {
key = it.next().toString();
rtn.put(key, map.get(key));
}
for (Iterator<String> it = map2.keySet().iterator(); it.hasNext();) {
key = it.next().toString();
rtn.put(key, map2.get(key));
}
return rtn;
}
/**
* 合成删除SQL 并执行
* @param tableName
* @param deleteMap
* @param whereMap
* @return
*/
@SuppressWarnings("unchecked")
public Object delete(String tableName,HashMap<String, String> deleteMap,LinkedHashMap<String, String> whereMap)
{
String param_delete="";
String delete="";
HashMap<String, String> mapWhere= (HashMap<String, String>)(where(whereMap, deleteMap));
param_delete="delete from "+ tableName+mapWhere.get("where_param");
delete="delete from "+ tableName+ mapWhere.get("where");
HashMap<String, Object> rtnMap= (HashMap<String, Object>)makeSQL(delete, param_delete, deleteMap);
return execute( rtnMap.get("stmt"));
}
/**
* 合成where 条件
* @param whereMap
* @param valueMap
* @return
*/
@SuppressWarnings({ "deprecation" })
public Object where(LinkedHashMap<String, String> whereMap,
HashMap<String, String> valueMap) {
// String whereName = "";
String op = "";
String w = "";
String pw = "";
String[] whereNames = null;
String value = "";
String key = "";
String postName = "";
HashMap<String, String> rtnMap = new HashMap<String, String>();
if(whereMap==null||valueMap==null)
{
rtnMap.put("where",w);
rtnMap.put("where_param",pw);
return rtnMap;
}
for (Iterator<String> it = whereMap.keySet().iterator(); it.hasNext();) {
key = it.next().toString();
op = whereMap.get(key).trim();
whereNames = key.split(",");
key = whereNames[0];
postName = (whereNames.length == 2) ? whereNames[1] : whereNames[0];
value = valueMap.get(postName);
value = getTrimStr(value);
value = URLDecoder.decode(value);
value = value.replaceAll("'", "''");
if(key.matches("^\\d+$"))
value=op;
if(!isEmpty(value))
{
if (op.equalsIgnoreCase("eq")) {
w += key + "='" + value + "'";
pw += key + "=:" + postName;
} else if (op.equalsIgnoreCase("li")) {
w += key + " like '%" + value + "%'";
pw += key + " like :" + postName + "";
} else if (op.equalsIgnoreCase("lil")) {
w += key + " like '%" + value + "'";
pw += key + " like :" + postName;
} else if (op.equalsIgnoreCase("lir")) {
w += key + " like '" + value + "%'";
pw += key + " like :" + postName + "";
} else if (op.equalsIgnoreCase("gt")) {
w += key + ">'" + value + "'";
pw += key + ">:" + postName;
} else if (op.equalsIgnoreCase("lt")) {
w += key + "<'" + value + "'";
pw += key + "<:" + postName;
} else if (op.equalsIgnoreCase("in")) {
String values[]=value.split(",");
String tv="";
for (int i = 0; i < values.length; i++) {
tv+="'"+ URLDecoder.decode( values[i].replaceAll("'", "''"))+"',";
}
tv=tv.substring(0, tv.length()-1);
w += key + " in (" + tv + ")";
pw += key + " in (" + tv + ")";
} else if (op.equalsIgnoreCase("ge")) {
w += key + ">='" + value + "'";
pw += key + ">=:" + postName;
} else if (op.equalsIgnoreCase("le")) {
w += key + "<='" + value + "'";
pw += key + "<=:" + postName;
} else if (op.equalsIgnoreCase("neq")) {
w += key + "<>'" + value + "'";
pw += key + "<>:" + postName;
} else {
pw += " " + op + " ";
w += " " + op + " ";
}
}
}
w = w.replaceAll("/\\(\\s*\\)/", "");
do {
int len = w.length();
w = w.replaceAll("^\\s*or\\s*|\\s*or\\s*$", " ");
w = w.replaceAll("^\\s*and\\s*|\\s*and\\s*$", " ");
w = w.replaceAll("^\\s*like\\s*|\\s*like\\s*$", " ");
w = w.replaceAll("\\s+like\\s+or\\s+", " or ");
w = w.replaceAll("\\s+like\\s+and\\s+", " and ");
w = w.replaceAll("\\s+like\\s+like\\s+", " like ");
w = w.replaceAll("\\s+or\\s+and\\s+", " and ");
w = w.replaceAll("\\s+or\\s+like\\s+", " like ");
w = w.replaceAll("\\s+or\\s+or\\s+", " or ");
w = w.replaceAll("\\s+and\\s+or\\s+", " and ");
w = w.replaceAll("\\s+and\\s+like\\s+", " like ");
w = w.replaceAll("\\s+and\\s+and\\s+", " and ");
w = w.replaceAll("\\s+\\(\\s+or\\s+\\)\\s+", " and ");
w = w.replaceAll("\\s+\\(\\s+and\\s+\\)\\s+", " and ");
w = w.replaceAll("\\s+\\(\\s+like\\s+\\)\\s+", " and ");
w = w.replaceAll("/\\(\\s*\\)/", "");
int len2 = w.length();
if (len == len2)
break;
} while (true);
pw = pw.replaceAll("/\\(\\s*\\)/", "");
do {
int len = pw.length();
pw = pw.replaceAll("^\\s*or\\s*|\\s*or\\s*$", " ");
pw = pw.replaceAll("^\\s*and\\s*|\\s*and\\s*$", " ");
pw = pw.replaceAll("^\\s*like\\s*|\\s*like\\s*$", " ");
pw = pw.replaceAll("\\s+like\\s+or\\s+", " or ");
pw = pw.replaceAll("\\s+like\\s+and\\s+", " and ");
pw = pw.replaceAll("\\s+like\\s+like\\s+", " like ");
pw = pw.replaceAll("\\s+or\\s+and\\s+", " and ");
pw = pw.replaceAll("\\s+or\\s+like\\s+", " like ");
pw = pw.replaceAll("\\s+or\\s+or\\s+", " or ");
pw = pw.replaceAll("\\s+and\\s+or\\s+", " and ");
pw = pw.replaceAll("\\s+and\\s+like\\s+", " like ");
pw = pw.replaceAll("\\s+and\\s+and\\s+", " and ");
pw = pw.replaceAll("\\s+\\(\\s+or\\s+\\)\\s+", " and ");
pw = pw.replaceAll("\\s+\\(\\s+and\\s+\\)\\s+", " and ");
pw = pw.replaceAll("\\s+\\(\\s+like\\s+\\)\\s+", " and ");
pw = pw.replaceAll("/\\(\\s*\\)/", "");
int len2 = pw.length();
if (len == len2)
break;
} while (true);
w=!w.trim().equalsIgnoreCase("")?" where "+ w:w;
pw=!pw.trim().equalsIgnoreCase("")?" where "+ pw:pw;
rtnMap.put("where",w);
rtnMap.put("where_param",pw);
return rtnMap;
}
public static void main(String[] args) throws SQLException {
}
}
张生工作室