超级简单的sqlmap工具
以前也写过一个，
这一把加入了动态sql,全部重写了
与ibatis类似
#param_name# 使用参数 ？（暂时不支持）
$param_name$ 直接替换
本工具特点：
提供了对ibatis常见功能的支持
核心类只有一个SqlMap.java
本工具适用与综合查询 （insert与update不推荐使用）
动态sql语句只支持 条件 （这个最常用了）
//根据 content 动态生成 sql
//参数xxx不为空时，才输出 content,实现动态sql
//目前只支持常见的notempty判断
//可使用模板来动态生成sql语句，这样将非常强大，常见的模板工具有 freemark,velocity等
//只是一个简单的查询工具，就不使用第三方的模板工具了
//{xxx} xxx 参数名
配置文件使用纯文本格式，而非xml格式（有些特殊字符XML里搞来搞去很无趣）
配置文件格式 ajf_sql_map(xxx)=select * from x ,xxx表示sql name
传入参数直接替换，参数封装在map中
查询返回结果，记录行映射为map,key值从resultset的元数据中获取，且全部为小写，可参考作后的 rs2list部分代码
其他代码不一一给出，稍微搞一下即能整出来
sql-map配置文件只提供一个（统一配置管理，分散配置，各有所长了）
/WEB-INF/classes/ajf_sql_map.config
/**
* zju ee && cs
* 国产偶像top3 毛泽东，邓小平，周恩来
* NBA偶像NO.1 Tim Duncan
* 头号足球偶像 罗纳尔多
* 小牛踏青 giscat@163.com
**/
一个简单的例子
ajf_sql_map.config配置文件
支持//开头的单行注释
ajf_sql_map(q1)=select * from t_user
ajf_sql_map(q2)=select * from t_user where name like '%{name}%'
ajf_sql_map(q3)=select * from t_user where 2>1
and name like '%{name}%'
////ajf_sql_map(q1)=select * from t_user
///
/////ajf_sql_map(q1)=select * from t_user
///
ajf_sql_map(q4)=select * from t_user
ajf_sql_map(q5)=select * from t_user
测试代码
Map map = JspUtil.getRequestModel(request);//把请求参数封装到map中
List list = null;
try{
list = SqlMap.query("q1",map);
out.println(list.size());
}catch(Exception e){
out.println(e);
}
try{
list = SqlMap.query("q2",map);
out.println(list.size());
}catch(Exception e){
out.println(e);
}
try{
list = SqlMap.query("q3",map);
out.println(list.size());
}catch(Exception e){
out.println(e);
}
try{
list = SqlMap.query("q4",map);
out.println(list.size());
}catch(Exception e){
out.println(e);
}
SqlMap.java原代码
package com.zjuee;
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;
//import org.apache.commons.beanutils.*;
import javax.servlet.*;
import javax.servlet.http.*;
/**
* zju ee && cs
* 国产偶像top3 毛泽东，邓小平，周恩来
* NBA偶像NO.1 Tim Duncan
* 头号足球偶像 罗纳尔多
* 小牛踏青 giscat@163.com
**/
public class SqlMap
{
static Properties config = null;
//static String sql_config_file = "/ajf_sql.properties";
static String sql_config_file = "/ajf_sql_map.config";
static Class c = SqlMap.class;
static String SEP = "ajf_sql_map\\(";
static String sql_config_string=null;
//------------
private SqlMap()
{
}
//clear the note,start with // ,single line
//去掉注释，只支持以//开头的单行注释
public static String getConfigString(String s)
throws Exception{
if(s==null){return "";}
s=s.replaceAll("//[^\\r\\n]{0,2000}[\\r\\n]{1,100}","");
return s;
}
//参数替换，指定参数名
public static String setParamSingle(String sql, String param, String v)
throws Exception {
if (v == null)
v = "";
if (param == null)
param = "";
param = param.trim();
if (param.length() == 0)
throw new Exception("\u53C2\u6570\u540D\u4E0D\u80FD\u4E3A\u7A7A");
String s = "{" + param + "}";
int ipos = 0;
int len = 0;
len = s.length();
String head = null;
String tail = null;
StringBuffer sql2 = new StringBuffer();
ipos = sql.indexOf(s);
tail = sql;
for (; ipos >= 0; ipos = sql.indexOf(s)) {
head = sql.substring(0, ipos);
tail = sql.substring(ipos + len);
sql2.append(head).append(v);
sql = tail;
}
sql2.append(tail);
return sql2.toString();
//return StringUtil.setParam(sql,param,v);
}
//--------------
public static Properties getSqlConfigProp()throws Exception{
if(config!=null){return config;}
return getSqlConfigPropInternal();
}
//读取配置文件
//配置文件格式 ajf_sql_map(xxx)=select * from x ,xxx表示sql name
public synchronized static Properties getSqlConfigPropInternal()throws Exception{
if(config!=null){return config;}
InputStream is = null;
InputStreamReader ir = null;
StringBuffer sb = new StringBuffer();
Properties prop = null;
String msg = null;
msg = "error when read ajf sql map config file [ WEB-INF/classes"+sql_config_file;
msg=msg+" ],please check is it exist";
//System.out.println("1");
is = c.getResourceAsStream(sql_config_file);
// System.out.println("2");
if(is==null){
throw new Exception(msg);
}
// if(is==null){System.out.println("is is null");}
ir = new InputStreamReader(is);
int ch = 0;
if(ir==null){
throw new Exception(msg);
}
ch = ir.read();
// if(ir==null){System.out.println("ir is null");}
while(ch>0){
// System.out.println(ch);
sb.append((char)ch);
ch = ir.read();
}
String s = sb.toString();
s=getConfigString(s);
sql_config_string = s+"";
//System.out.println(sql_config_string);
int i =0;
int num =0;
String name = null;
int pos = 0;
int start = 0;
int end = 0;
String[]arr=null;
String ss = null;
String sql = null;
arr=s.split(SEP);
num=arr.length;
prop = new Properties();
for(i=0;i
ss=arr[i];
pos = ss.indexOf(")=");
if(pos<=0){continue;}
name=ss.substring(0,pos);
if(name==null){continue;}
name=name.trim();
if(name.length()<1){continue;}
sql=ss.substring(pos+2);
if(StringUtil.isempty(sql)){
continue;
}
sql=sql.trim();
if(!StringUtil.isempty(prop.getProperty(name))){
throw new Exception("duplicate sql name["+name+"]");
}
prop.setProperty(name,sql);
}
return prop;
}
//获取所有参数 {xxx} xxx为参数名
public static List getParamNames(String s) throws Exception {
//List list = new ArrayList();
List list = null;
Map map = new HashMap();
if (s == null) {
return new ArrayList();
}
Pattern p = Pattern.compile("\\{([0-9A-Za-z._-]{0,50})\\}");
Matcher m = p.matcher(s);
String paramName = null;
while (m.find()) {
paramName = m.group(1);
//list.add(paramName);
map.put(paramName,"1");
}
//return list;
return StringUtil.getMapKey(map);
}
//替换参数，全部替换
public static String setParam(String s, Map map)
throws Exception {
if (map == null) {
return s;
}
List list = getParamNames(s);
if (list == null) {
return s;
}
int i = 0;
int num = 0;
String paramName = null;
String v = null;
Object obj = null;
num = list.size();
for (i = 0; i < num; i++) {
paramName = (String) list.get(i);
//System.out.println(paramName);
obj = map.get(paramName);
if(obj==null){
v="";
}else{
v = obj+"";
}
//s = setParamSingle(s, paramName, (String)map.get(paramName));
//System.out.println(v);
//s = StringUtil.setParam(s, paramName, v);
s = setParamSingle(s, paramName, v);
//System.out.println(s);
}
return s;
}
//根据 content 动态生成 sql
//参数xxx不为空时，才输出 content,实现动态sql
//目前只支持常见的notempty判断
//可使用模板来动态生成sql语句，这样将非常强大，常见的模板工具有 freemark,velocity等
//还是比较喜欢简单，就不使用第三方的模板工具了
public static String getParamSql(String s,Map map)throws Exception{
if(s==null){return "null";}
if(map==null){return s;}
String ps = "([^/]{1,500})";
Pattern p = Pattern.compile(ps);
Matcher m = p.matcher(s);
StringBuffer sb = new StringBuffer();
String v = null;
String rs = null;
String name = null;
while (m.find()) {
name=m.group(1);
rs=m.group(2);
v = (String)map.get(name);
if(StringUtil.isempty(v)){
m.appendReplacement(sb, "");
}else{
m.appendReplacement(sb, rs);
}
}
m.appendTail(sb);
return sb.toString();
}
//根据名称获取原始的sql语句
public static String getSqlByName(String name)
throws Exception{
String s = null;
if(StringUtil.isempty(name)){
throw new Exception("sql name is empty");
}
s = getSqlConfigProp().getProperty(name);
if(StringUtil.isempty(s)){
throw new Exception("no sql names ["+name+"]");
}
return s;
}
//根据名称获取sql语句，动态生成，参数替换后的sql,可直接执行
public static String getSqlByName(String name,Map map)
throws Exception{
String sql = null;
sql = getSqlByName(name);
sql=getParamSql(sql,map);
if(map==null){return sql;}
return getSql(sql,map);
}
public static String getSql(String s,Map map)throws Exception{
if(map==null){return s;}
if(map.isEmpty()){return s;}
List list = getParamNames(s);
return setParam(s,map);
}
//读取配置内容，文件内容 和 sql map 语句
public static String getSqlConfigContent()throws Exception{
String s = null;
Properties prop = getSqlConfigProp();
s=PropUtil.dump(prop)+"\n\n\n"+sql_config_string;
return s;
}
//--------sql map jdbc
// executeUpdate不推荐使用
public static int executeUpdate(Connection cn,
String name,Map map)
throws Exception{
if(cn==null){
throw new Exception("cn is null");
}
int num = 0;
String sql = null;
Statement stmt = null;
sql = getSqlByName(name,map);
//sql = getParamSql(sql,map);
//getSql(sql,map);
try{
stmt = cn.createStatement();
num = DBUtil.executeUpdate(stmt,sql);
return num;
}catch(Exception e){
throw new Exception(e+","+sql);
}finally{DBUtil.close(stmt);}
}
public static int executeUpdate(
String name,Map map)
throws Exception{
Connection cn = null;
try{
cn = DBUtil.getConn();
return executeUpdate(cn,name,map);
}catch(Exception e){
throw e;
}finally{
DBUtil.close(cn);
}
}
public static List query(Connection cn,
String name,Map map)
throws Exception{
if(cn==null){
throw new Exception("cn is null");
}
String sql = null;
Statement stmt = null;
ResultSet rs = null;
sql = getSqlByName(name,map);
//sql = getParamSql(sql,map);
//getSql(sql,map);
try{
stmt = cn.createStatement();
rs = DBUtil.executeQuery( stmt,sql);
return DBUtil.rs2list(rs);//把resultSet 映射成map
//rs2list代码见最后
//支持结果集分页，参考 rs2list代码
}catch(Exception e){
throw new Exception(e+","+sql);
}finally{
DBUtil.close(rs);
DBUtil.close(stmt);
}
}
public static List query(
String name,Map map)
throws Exception{
Connection cn = null;
try{
cn = DBUtil.getConn();
return query(cn,name,map);
}catch(Exception e){
throw e;
}finally{
DBUtil.close(cn);
}
}
public static Map queryOne(Connection cn,String name,Map map)
throws Exception{
if(cn==null){
throw new Exception("cn is null");
}
String sql = null;
sql = getSqlByName(name,map);
//sql = getParamSql(sql,map);
//getSql(sql,map);
return queryOne(cn, sql,null);
}
public static Map queryOne(String name,Map map)
throws Exception{
Connection cn = null;
try{
cn = DBUtil.getConn();
return queryOne(cn,name,map);
}catch(Exception e){
throw e;
}finally{
DBUtil.close(cn);
}
}
}//end class
rs2list 相关代码
public static List rs2list(ResultSet rs) throws Exception {
List list = new ArrayList();
if (rs == null) {
return list;
}
String[] arr = null;
Map map = null;
arr = getColName(rs);
int i = 0;
int num = 0;
String v = null;
if (arr != null) {
num = arr.length;
while (rs.next()) {
map = new HashMap();
for (i = 1; i <= num; i++) {
v = rs.getString(i);
if (v == null) {
v = "";
}
map.put(arr[i - 1], v);
}// end for
list.add(map);
}// end while
}// end if
return list;
}
//支持分页，基于结果集的通用"假"分页
//把结果集全部装载到内存，（数据量不大时完全可以这么做）
//对于大数据量查询，请设置statementset的setMaxRows参数，限制resultSet大小即可
//要不然会很慢且吃内存
public static List rs2list(ResultSet rs, int skip, int page_size)
throws Exception {
List list = new ArrayList();
if (rs == null) {
return list;
}
String[] arrColName = null;
String colName = null;
int colNum = 0;
int pos = 0;
int i = 0;
int j = 0;
Map map = null;
String v = null;
ResultSetMetaData rsmd = null;
rsmd = rs.getMetaData();
colNum = rsmd.getColumnCount();
arrColName = new String[colNum];
for (i = 1; i <= colNum; i++) {
arrColName[i - 1] = rsmd.getColumnName(i).toLowerCase();
}
if (page_size == 0) {
page_size = 10000000;
}
while ((rs.next()) && (j < page_size)) {
if (pos < skip) {
pos++;
continue;
}
map = new HashMap();
for (i = 0; i < colNum; i++) {
v = rs.getString(i + 1);
if (v == null) {
v = "";
}
map.put(arrColName[i], v);
}
j++;
list.add(map);
}// end while
return list;
}
public static String[] getColName(ResultSet rs) throws Exception {
if (rs == null) {
return null;
}
int num = 0;
int i = 0;
String colName = null;
String[] arr = null;
ResultSetMetaData rsmd = null;
rsmd = rs.getMetaData();
num = rsmd.getColumnCount();
arr = new String[num];
for (i = 1; i <= num; i++) {
colName = rsmd.getColumnName(i);
colName = colName.toLowerCase();
arr[i - 1] = colName;
}
return arr;
}
(责任编辑：包春林)
|
