Sql控制反转小尝试

假想用配置Sql语句的方式来完成一个处理逻辑，并且映射到一个Url，这样当请求这个url的时候，执行前面配置的sql。

下面的一段具体配置，例如 当请求pagerlistdept.do的时候，会传入参数Offset，并调用handler执行里面配置的SQL语句。

dept_sql_mapping.xml

<?xml version="1.0" encoding="UTF-8"?><!-- <!DOCTYPE sql-mappings SYSTEM "sql-parser.dtd"> --><sql-mappings><sql-mapping url="pagerlistdept.do" success="/deptlist.jsp" fail="/error.jsp" handler="org.sqlparser.handler.impl.SimpleSqlHandler"><sql result="deptlist" type="query" variables="offset">select d.id_f,d.name_f,count(e.id_f) emp_count from dept_t d left join emp_t e on d.id_f=e.dept_id_f group by d.id_f limit #offset#,6</sql><sql result="dept_count" type="count">select count(*) from dept_t</sql></sql-mapping><sql-mapping url="deptlist.do" success="/deptlist.jsp"><sql result="deptlist" type="query">select d.id_f,d.name_f,count(e.id_f) emp_count from dept_t d left join emp_t e on d.id_f=e.dept_id_f group by d.id_f limit 0,6</sql><sql result="dept_count" type="count">select count(*) from dept_t</sql></sql-mapping><sql-mapping url="jsondeptlist.do" type="json"><sql result="deptlist" type="query">select * from dept_t</sql></sql-mapping><sql-mapping url="deptedit.do" success="deptadd.jsp"><sql result="dept" type="find">select id_f,name_f from dept_t where id_f=#did#</sql></sql-mapping><sql-mapping url="deptadd.do" success="deptlist.do" fail="/error.jsp"><sql result="added_rows" type="update">insert into dept_t(name_f) values('#name#')</sql><validate><parameter name="name" validator="org.sqlparser.validator.impl.AccountValidator"/></validate></sql-mapping><sql-mapping url="deptdelete.do" success="deptlist.do" fail="/error.jsp"><transactional><sql type="update">delete from dept_t where id_f=#did#</sql></transactional></sql-mapping><sql-mapping url="deptupdate.do" success="deptlist.do"><sql type="update">update dept_t set name_f='#name#' where id_f=#did#</sql></sql-mapping></sql-mappings>

下面看看怎么实现。。。

首先，在classpath下面定义一个总的配置文件，暂时命名为sqlparser.xml，定义好默认的handler和数据库连接信息(db.properties)

<?xml version="1.0" encoding="UTF-8"?><sqlparser><mapping name="dept_mapping" location="mappings/dept_sql_mapping.xml"/><default-sql-handler class="org.sqlparser.handler.impl.SimpleSqlHandler"/><database-config-file file="db.properties"/></sqlparser>

创建总的控制器，用一个Servlet来完成。主要用于加载配置信息，拦截请求并解析

/** * Dispacher servlet for sqlparser * You should configure this servlet as normal servlet int web.xml * and set <load-on-startup>1</load-on-startup> to make  * it starts with web container * @author john.liu * */public class SqlParserServlet extends HttpServlet {private static final long serialVersionUID = 1L;protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {try {request.setCharacterEncoding("UTF-8");request.removeAttribute("VALIDATION_ERRORS");request.getSession().removeAttribute("ERRORS");} catch (UnsupportedEncodingException e) {e.printStackTrace();}RequestParser rp = new RequestParser(request,response);rp.parse();}@Overridepublic void init(ServletConfig config) throws ServletException {long st = System.currentTimeMillis();ConfigurationParser.loadConfiguration();long ed = System.currentTimeMillis();System.out.println("load configurations in "+(ed-st)+" ms.");}}

加载配置信息由RequestParser完成，这里面主要是根据请求的uri获取到处理的handler和sql语句，执行并分发视图.

/** * Class for parsing request * This is almost heart of sqlparser,which parses request,executes sql,dispatches result,returns error. * @author john.liu * */public class RequestParser {private HttpServletRequest request;private HttpServletResponse response;private String request_do;/** * 404 request target */private String success = "404error.do";/** * error request target  */private String fail = "error.do";/** * specify type='json' to make an ajax request  */private String type; /** * by default ,redirect is false */private boolean redirect = false;private SqlBean[] sql_array;private SqlBean[] tran_sql_array;private HashMap<String,String> parameters;private SqlHandler default_sql_handler;public RequestParser(HttpServletRequest request,HttpServletResponse response){this.request = request;this.response = response;init();}/** * initiate some variables by request */private void init(){String uri = request.getRequestURI();String context = request.getContextPath();this.request_do = uri.substring(uri.indexOf(context)+context.length()+1);if(request_do.indexOf("?")!=-1)this.request_do = request_do.substring(0, request_do.indexOf("?"));HashMap url_map = ConfigurationParser.sqlMap.get(request_do);if(url_map == null) {this.request_do = "404error.do";}boolean isError = handleErrorRequest();if(isError) return;type = url_map.get("TYPE")!=null?(String)url_map.get("TYPE"):null;success = url_map.get("SUCCESS")!=null?(String)url_map.get("SUCCESS"):success;fail = url_map.get("FAIL")!=null?(String)url_map.get("FAIL"):fail;redirect = url_map.get("REDIRECT")!=null?Boolean.valueOf((String)url_map.get("REDIRECT")):false;sql_array = url_map.get("SQL_ARRAY")!=null?(SqlBean[])url_map.get("SQL_ARRAY"):null;tran_sql_array = url_map.get("TRAN_SQL_ARRAY")!=null?(SqlBean[])url_map.get("TRAN_SQL_ARRAY"):null;parameters = url_map.get("VALIDATE_PARAM")!=null?(HashMap<String,String>)url_map.get("VALIDATE_PARAM"):null;String handler_class = url_map.get("SQL_HANDLER")!=null?url_map.get("SQL_HANDLER").toString():null;initHandlerClass(handler_class); //initiate handler class}private void initHandlerClass(String handler_class) {try {long st = System.currentTimeMillis();if(default_sql_handler != null && default_sql_handler.getClass().getCanonicalName().equals(handler_class)){//dont initialize the same handlerreturn;}if(handler_class!=null){Class<SqlHandler> clazz = (Class<SqlHandler>)Class.forName(handler_class);default_sql_handler = clazz.newInstance();}else if(ConfigurationParser.default_sql_handler_class!=null){Class<SqlHandler> clazz = (Class<SqlHandler>)Class.forName(ConfigurationParser.default_sql_handler_class);default_sql_handler = clazz.newInstance();}else{default_sql_handler = new SimpleSqlHandler(ConfigurationParser.db_config_file);}long ed = System.currentTimeMillis();System.out.println("["+new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date())+"]"+default_sql_handler.toString()+" cost: "+(ed-st)+" ms");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}/** * pasrse request */public void parse() {if(default_sql_handler==null) return;if(default_sql_handler.getConnection()==null) default_sql_handler.openConnection();if(ConfigurationParser.sqlMap.get(success)!=null){redirect = true; //redirect to another request in the url-map}List<String> errors = ReuqestParameterValidator.doValidate(request,parameters); //do validationif(errors.size()>0){try {//validate errorif(type!=null&&(type.equals("json")||type.equals("xml"))){PrintWriter pw = response.getWriter();pw.write("false");pw.close();}else{request.setAttribute("VALIDATION_ERRORS", errors);request.getRequestDispatcher(fail).forward(request, response);}} catch (Exception e) {e.printStackTrace();} }else{//no error with validation,dispatch resultdistrubuteResult();}}/** * handle errors * @return */private boolean handleErrorRequest() {if(!request_do.equals("error.do")&&!request_do.equals("404error.do")) return false;String url = "";if(request_do.equals("error.do")){url = "/WEB-INF/classes/web/error.jsp";}else if(request_do.equals("404error.do")){url = "/WEB-INF/classes/web/404.jsp";}try {request.getRequestDispatcher(url).forward(request, response);} catch (Exception e) {e.printStackTrace();}return true;}/** * dispatche result */private void distrubuteResult() {try{response.setCharacterEncoding("UTF-8");default_sql_handler.getErrors().clear();HashMap<String,Object> resultMap = getSqlResult();if(type!=null&&(type.equals("json"))){PrintWriter pw = response.getWriter();JSONObject jo = JSONObject.fromObject(resultMap);pw.write(jo.toString());pw.close();}else{if(default_sql_handler.getErrors().size()>0){//sql execute errorrequest.getSession().setAttribute("ERRORS", default_sql_handler.getErrors());//response.sendRedirect(request.getContextPath()+""+fail);response.sendRedirect(request.getContextPath()+"/"+fail);}else{if(redirect){response.sendRedirect(request.getContextPath()+"/"+success);}else{request.getRequestDispatcher(success).forward(request, response);}}}} catch (Exception e) {e.printStackTrace();} finally{default_sql_handler.closeConnection(); //close current connection }}/** * execute sql, and return result map * @return result map * @throws SQLException */private HashMap<String,Object> getSqlResult() throws SQLException {HashMap<String,Object> resultMap = new HashMap<String, Object>(0);if(sql_array!=null){for(SqlBean sql:sql_array){Object res = executeSql(sql);if(type!=null&&(type.equals("json"))){resultMap.put(sql.getResult(), res);}}}if(tran_sql_array!=null){if(default_sql_handler.getConnection()==null) default_sql_handler.openConnection();default_sql_handler.getConnection().setAutoCommit(false);for(SqlBean tran_sql:tran_sql_array){Object res = executeSql(tran_sql);if(type!=null&&(type.equals("json"))){resultMap.put(tran_sql.getResult(), res);}}default_sql_handler.getConnection().commit();}return resultMap;}/** * execute single sql * @param sqlbean * @return mixed type object probably are int,object[] or list<object[]> * @throws SQLException */private Object executeSql(SqlBean sqlbean) throws SQLException{String sql = sqlbean.getSql();sql = setSqlParameters(sql); //set parameter String result = sqlbean.getResult();String type = sqlbean.getType();String[] variables = sqlbean.getVariables();Object res = null;if("update".equals(type)){int rows = 0;try {rows = default_sql_handler.update(sql);} catch (SQLException e) {default_sql_handler.rollback();System.err.println("[sql execute error]"+sql);default_sql_handler.setError("[sql execute error]");}res = rows;}else if("query".equals(type)){if(result==null) return null;res = default_sql_handler.query(sql);}else if("find".equals(type)){if(result==null) return null;res = default_sql_handler.find(sql);}else if("count".equals(type)){if(result==null) return 0;res = default_sql_handler.count(sql);}HttpSession session = request.getSession();if(result != null){if(redirect){session.setAttribute(result, res);}else{request.setAttribute(result, res);}}if(variables != null){for(String var:variables){if(redirect){session.setAttribute(var, request.getParameter(var));}else{request.setAttribute(var, request.getParameter(var));}}}return res;}private String setSqlParameters(String sql){Pattern p = Pattern.compile("#(\\w|\\d)+#");Matcher m = p.matcher(sql);while(m.find()){String g = m.group();String param = g.replace("#", "");sql = sql.replace(g, escapeString(request.getParameter(param)));}return sql;}private static String escapeString(String str){if(str==null) return "null";return str.replace("\'", "\\'").replace("\"", "\\\"").replaceAll("\\s+or\\s+", " ｏｒ ");}

SimpleSqlHandler类定义增改删查之类的方法

package org.sqlparser.handler.impl;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Properties;import org.sqlparser.handler.SqlHandler;/** * <p>Simple implementation of sql handler</p> * <p>This class defined the simplest jdbc operations,which are query,count,find,update</p> * <p>You can make your owner sql handler by implementing interface SqlHandler</p> * <p>The best way is defining a default sql handler by implementing interface SqlHandler , *  and implementing all methods.Then if needed, you can define other handlers by  *  extending the default sql handler you defined before, and override one or more methods  *  according to your detail logic.And these handlers can be sepecified in 'handler' attribute  *  of sql-mapping to make this sql-mapping request handled by your owner sql handler.</p> * @author john.liu * */public class SimpleSqlHandler implements SqlHandler {private String configFile = "db.properties";/** * Connection */private Connection conn;/** * PreparedStatement */private PreparedStatement pstmt;/** * Database driver class * <p>It is suggested that u make this property configured in a file  * and configure 'database-config-file' attribute in sqlparser.xml</p> */private static String db_driver;/** * Database connection url * <p>It is suggested that u make this property configured in a file  * and configure 'database-config-file' attribute in sqlparser.xml</p> */private static String db_url;/** * Database user name * <p>It is suggested that u make this property configured in a file  * and configure 'database-config-file' attribute in sqlparser.xml</p> */private static String db_user;/** * database connect password * <p>It is suggested that u make this property configured in a file  * and configure 'database-config-file' attribute in sqlparser.xml</p> */private static String db_password;/** * Default constructor method */public SimpleSqlHandler(){init();}/** * Constructor method * <p>Initiate an instance by specified database configure file * @param config_file */public SimpleSqlHandler(String config_file){if(config_file != null && !"".equals(configFile)) {this.configFile = config_file;}init();}/** * Load database configure file * @param config_file database configure file */private void init() {Properties props = new Properties();try {props.load(this.getClass().getClassLoader().getResourceAsStream(this.configFile));db_driver = props.getProperty("db_driver");db_url = props.getProperty("db_url");db_user = props.getProperty("db_user");db_password = props.getProperty("db_password");} catch (IOException e) {e.printStackTrace();setError("can not load database config file");}}/** * Open a new connection if connection is null */@Overridepublic void openConnection(){if(conn != null) return;try {Class.forName(db_driver);conn = DriverManager.getConnection(db_url,db_user,db_password);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}/** * Close connection if connection is not null */@Overridepublic void closeConnection() {if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}/** * Get a connection  */@Overridepublic Connection getConnection() {return this.conn;}/** * Execute update */@Overridepublic int update(String sql) throws SQLException{openConnection();pstmt = conn.prepareStatement(sql);return pstmt.executeUpdate();}/** * Execute select, return result set row number */@Overridepublic int count(String sql) {try {openConnection();pstmt = conn.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();if(rs.next()){return rs.getInt(1);}} catch (SQLException e) {e.printStackTrace();}return 0;}/** * Execute select, return one row data  */@Overridepublic Object[] find(String sql) {try {openConnection();pstmt = conn.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();int cols = rs.getMetaData().getColumnCount();Object[] row = new Object[cols];if(rs.next()){for(int loop=0; loop<cols; loop++){row[loop] = rs.getObject(loop+1);}}return row;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return null;}/** * Execute select, return a data list. * <p>Use row index and column index to retrieve items in data list</p> */@Overridepublic List<Object[]> query(String sql) {try {openConnection();pstmt = conn.prepareStatement(sql);ResultSet rs = pstmt.executeQuery();int cols = rs.getMetaData().getColumnCount();ArrayList<Object[]> list = new ArrayList<Object[]>(0);while(rs.next()){Object[] row = new Object[cols];for(int loop=0; loop<cols; loop++){row[loop] = rs.getObject(loop+1);}list.add(row);}return list;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return null;}/** * Roll back current transaction * <p>You can put some <sql> tags in <transactional> to make these sql executed  * within a transaction,either of these sql 's failure will cause this method 's invoke</p> */@Overridepublic void rollback() {try {if(!conn.getAutoCommit())conn.rollback();} catch (SQLException e) {e.printStackTrace();}}/** * Put an error to error list */@Overridepublic void setError(String error) {errors.add(error);}/** * Get error list return by this handler instance */@Overridepublic ArrayList<String> getErrors() {return errors;}}