package dbtools; import java.util.List; import java.util.ListIterator; import org.dom4j.Document; import org.dom4j.Element; import org.dom4j.io.SAXReader; import com.thoughtworks.xstream.*; import com.thoughtworks.xstream.io.xml.DomDriver; /** * XStreamReflect * @author wardenx * */ public class XStreamReflect { /** * SQL文件位置 */ private static String path = "D://works//POS2011//DBTool//Java//DBToolsTest//dbtools//sql.xml"; /** * section定位 */ private static String TYPE_NODE = "section"; /** * ID */ private static String ID = "name"; /** * TYPE */ private static String TYPE = "type"; private static SAXReader xmlReader = new SAXReader(); private static Document doc = null; private static void init() { try { doc = xmlReader.read(path); } catch (Exception e) { e.printStackTrace(); } } /** * 得到反射类类型 * @param id * @return */ private static String getClassType(String id) { init(); String type = ""; List list = doc.selectNodes("//" + TYPE_NODE + "[@" + ID + "='" + id + "']"); for (ListIterator iter = list.listIterator(); iter.hasNext();) { Element element = (Element) iter.next(); type = element.attributeValue(TYPE); if (type != null && !"".equals(type)) { return type; } } return type; } /** * 得到相应xml节点下SQL信息 * @param id * @return * @throws ClassNotFoundException */ public static Object getCommandInfo(String id) throws ClassNotFoundException{ String type = getClassType(id); XStream xstream = new XStream(new DomDriver()); List list = doc.selectNodes("//" + id); for (ListIterator iter = list.listIterator(); iter.hasNext();) { Element element = (Element) iter.next(); if (element != null) { String str = element.asXML().replaceAll("[/r/t/n]", ""); xstream.alias(id, Class.forName(type)); return xstream.fromXML(str); } } return null; } } package dbtools; import java.lang.reflect.Field; /** * SqlXmlNodes * @author wardenx * */ public class SqlXmlNodes { private String commandText; private String commandType; private SqlXmlSubnodes[] params; private static String SQL_TYPE_HEADER = "java.sql.Types"; public String getCommandText() { return commandText; } public void setCommandText(String commandText) { this.commandText = commandText; } public String getCommandType() { return commandType; } public void setCommandType(String commandType) { this.commandType = commandType; } public SqlXmlSubnodes[] getParams() { return params; } public void setParams(SqlXmlSubnodes[] params) { this.params = params; } /** * 得到SQL参数下标 * @param name * @return */ public int getParamsIndex(String name) { if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { if (name.equalsIgnoreCase(params[i].getName())) { return Integer.parseInt(params[i].getParamIndex()); } } } return 0; } /** * 得到SQL参数类型 * @param name * @return */ public int getParamsType(String name) { if (params != null && params.length > 0) { for (int i = 0; i < params.length; i++) { if (name.equalsIgnoreCase(params[i].getName())) { try { Class a = Class.forName(SQL_TYPE_HEADER); Field f = a.getDeclaredField(params[i].getDbType()); return f.getInt(f); } catch (Exception e) { e.printStackTrace(); } } } } return 0; } } package dbtools; /** * SqlXmlSubnodes * @author wardenx * */ public class SqlXmlSubnodes { private String name; private String paramIndex; private String dbType; public String getDbType() { return dbType; } public void setDbType(String dbType) { this.dbType = dbType; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getParamIndex() { return paramIndex; } public void setParamIndex(String paramIndex) { this.paramIndex = paramIndex; } } package dbtools; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class XStreamReflectTest { public static void main(String args[]) { try { Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection( "jdbc:postgresql://localhost:5433/DBToolsTest", "postgres", "Password01!"); int sign = 5; switch (sign) { // select case 1: sqlSelect(conn); break; // add case 2: sqlAdd(conn); break; // update case 3: sqlUpdate(conn); break; // delete case 4: sqlDelete(conn); break; // function case 5: sqlFunction(conn); break; // default default: break; } } catch (Exception e) { e.printStackTrace(); } } private static void sqlSelect(Connection conn) throws Exception { SqlXmlNodes sqlXmlNodes = (SqlXmlNodes) XStreamReflect .getCommandInfo("S001"); // sql PreparedStatement stmt = conn.prepareCall(sqlXmlNodes.getCommandText()); stmt.setObject(sqlXmlNodes.getParamsIndex("age"), "18", sqlXmlNodes .getParamsType("age")); ResultSet rs = stmt.executeQuery(); while (rs.next()) { System.out.print("id:" + rs.getObject("id") + "-"); System.out.println("name:" + rs.getObject("name")); } } private static void sqlAdd(Connection conn) throws Exception { SqlXmlNodes sqlXmlNodes = (SqlXmlNodes) XStreamReflect .getCommandInfo("S002"); // sql PreparedStatement stmt = conn.prepareCall(sqlXmlNodes.getCommandText()); stmt.setObject(sqlXmlNodes.getParamsIndex("id"), "0007", sqlXmlNodes .getParamsType("id")); stmt.setObject(sqlXmlNodes.getParamsIndex("name"), "g", sqlXmlNodes .getParamsType("name")); stmt.setObject(sqlXmlNodes.getParamsIndex("age"), "25", sqlXmlNodes .getParamsType("age")); int rs = stmt.executeUpdate(); System.out.print(rs); } private static void sqlUpdate(Connection conn) throws Exception { SqlXmlNodes sqlXmlNodes = (SqlXmlNodes) XStreamReflect .getCommandInfo("S003"); // sql PreparedStatement stmt = conn.prepareCall(sqlXmlNodes.getCommandText()); stmt.setObject(sqlXmlNodes.getParamsIndex("age"), "100", sqlXmlNodes .getParamsType("age")); stmt.setObject(sqlXmlNodes.getParamsIndex("id"), "0003", sqlXmlNodes .getParamsType("id")); int rs = stmt.executeUpdate(); System.out.print(rs); } private static void sqlDelete(Connection conn) throws Exception { SqlXmlNodes sqlXmlNodes = (SqlXmlNodes) XStreamReflect .getCommandInfo("S004"); // sql PreparedStatement stmt = conn.prepareCall(sqlXmlNodes.getCommandText()); stmt.setObject(sqlXmlNodes.getParamsIndex("id"), "0007", sqlXmlNodes .getParamsType("id")); int rs = stmt.executeUpdate(); System.out.print(rs); } private static void sqlFunction(Connection conn) throws Exception { SqlXmlNodes sqlXmlNodes = (SqlXmlNodes) XStreamReflect .getCommandInfo("S005"); // sql PreparedStatement stmt = conn.prepareCall(sqlXmlNodes.getCommandText()); stmt.setObject(sqlXmlNodes.getParamsIndex("id"), "0001", sqlXmlNodes .getParamsType("id")); ResultSet rs = stmt.executeQuery(); while (rs.next()) { System.out.print("name:" + rs.getObject("name") + "-"); System.out.println("age:" + rs.getObject("age")); } } } <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <sectionGroup name="DbAccessCommandsInfo"> <section name="S001" type="dbtools.SqlXmlNodes" /> <section name="S002" type="dbtools.SqlXmlNodes" /> <section name="S003" type="dbtools.SqlXmlNodes" /> <section name="S004" type="dbtools.SqlXmlNodes" /> <section name="S005" type="dbtools.SqlXmlNodes" /> </sectionGroup> </configSections> <DbAccessCommandsInfo> <S001> <commandText> SELECT id,name FROM table1 where age = ? </commandText> <commandType>Text</commandType> <params> <dbtools.SqlXmlSubnodes> <name>age</name> <paramIndex>1</paramIndex> <dbType>INTEGER</dbType> </dbtools.SqlXmlSubnodes> </params> </S001> <S002> <commandText> INSERT INTO table1 VALUES(?,?,?) </commandText> <commandType>Text</commandType> <params> <dbtools.SqlXmlSubnodes> <name>id</name> <paramIndex>1</paramIndex> <dbType>VARCHAR</dbType> </dbtools.SqlXmlSubnodes> <dbtools.SqlXmlSubnodes> <name>name</name> <paramIndex>2</paramIndex> <dbType>VARCHAR</dbType> </dbtools.SqlXmlSubnodes> <dbtools.SqlXmlSubnodes> <name>age</name> <paramIndex>3</paramIndex> <dbType>INTEGER</dbType> </dbtools.SqlXmlSubnodes> </params> </S002> <S003> <commandText> UPDATE table1 SET age = ? WHERE id = ? </commandText> <commandType>Text</commandType> <params> <dbtools.SqlXmlSubnodes> <name>age</name> <paramIndex>1</paramIndex> <dbType>INTEGER</dbType> </dbtools.SqlXmlSubnodes> <dbtools.SqlXmlSubnodes> <name>id</name> <paramIndex>2</paramIndex> <dbType>VARCHAR</dbType> </dbtools.SqlXmlSubnodes> </params> </S003> <S004> <commandText> DELETE FROM table1 WHERE id = ? </commandText> <commandType>Text</commandType> <params> <dbtools.SqlXmlSubnodes> <name>id</name> <paramIndex>1</paramIndex> <dbType>VARCHAR</dbType> </dbtools.SqlXmlSubnodes> </params> </S004> <S005> <commandText> {call func(?)} </commandText> <commandType>Function</commandType> <params> <dbtools.SqlXmlSubnodes> <name>id</name> <paramIndex>1</paramIndex> <dbType>VARCHAR</dbType> </dbtools.SqlXmlSubnodes> </params> </S005> </DbAccessCommandsInfo> </configuration>