首页 > 代码库 > Sql控制反转小尝试
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(); } @Override public 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 handler return; } 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 validation if(errors.size()>0){ try { //validate error if(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 result distrubuteResult(); } } /** * 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 error request.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+", " or "); }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 */ @Override public 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 */ @Override public void closeConnection() { if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * Get a connection */ @Override public Connection getConnection() { return this.conn; } /** * Execute update */ @Override public int update(String sql) throws SQLException{ openConnection(); pstmt = conn.prepareStatement(sql); return pstmt.executeUpdate(); } /** * Execute select, return result set row number */ @Override public 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 */ @Override public 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 block e.printStackTrace(); } return null; } /** * Execute select, return a data list. * <p>Use row index and column index to retrieve items in data list</p> */ @Override public 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 block e.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> */ @Override public void rollback() { try { if(!conn.getAutoCommit()) conn.rollback(); } catch (SQLException e) { e.printStackTrace(); } } /** * Put an error to error list */ @Override public void setError(String error) { errors.add(error); } /** * Get error list return by this handler instance */ @Override public ArrayList<String> getErrors() { return errors; } }
Sql控制反转小尝试
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。