利用xml做配置文件,可以动态生成sql:
- package com.landray.kmss.util;
- import java.io.IOException;
- import java.lang.reflect.InvocationTargetException;
- import java.lang.reflect.Method;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.Iterator;
- import java.util.List;
- import java.util.Map;
- import java.util.Set;
- import org.jdom.Document;
- import org.jdom.Element;
- import org.jdom.JDOMException;
- import org.jdom.input.SAXBuilder;
- import org.mira.lucene.analysis.c;
- import org.springframework.jdbc.core.JdbcTemplate;
- import com.landray.kmss.sys.transport.model.Exam;
- /**
- * createBy Zhang Yanan
- *
- * createTime 2012-6-14 下午03:53:19
- *
- * desc 类和表的映射工具
- *
- */
- public class MappingUtil {
- private JdbcTemplate jdbcTemplate;
- private JdbcTemplate jdbcTemplateAssist;
- public MappingUtil(JdbcTemplate jdbcTemplate,JdbcTemplate Assist){
- this.jdbcTemplate = jdbcTemplate;
- this.jdbcTemplateAssist = jdbcTemplateAssist;
- }
- public MappingUtil(){
- }
- public JdbcTemplate getJdbcTemplate() {
- return jdbcTemplate;
- }
- public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
- this.jdbcTemplate = jdbcTemplate;
- }
- public JdbcTemplate getJdbcTemplateAssist() {
- return jdbcTemplateAssist;
- }
- public void setJdbcTemplateAssist(JdbcTemplate jdbcTemplateAssist) {
- this.jdbcTemplateAssist = jdbcTemplateAssist;
- }
- //从Object[] 转为Bean
- public Object fromObject2Bean(Class c ,Object[] o) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
- Object ob = c.newInstance();
- Method[] f = c.getMethods();
- String path = this.getXmlFileName(c);
- Map map = this.readXml(path);
- for(int i=0;i<f.length;i++){
- Method method = f[i];
- String methodName = method.getName();
- if(methodName.startsWith("set")){
- String fieldName = methodName.replace("set", "");
- String upChar = fieldName.charAt(0)+"";
- fieldName=fieldName.replace(upChar,upChar.toLowerCase());
- String order = (String)map.get(fieldName);
- method.invoke(ob, o[Integer.valueOf(order)-1]);
- }
- }
- return ob;
- }
- //获取class获取映射文件位置
- private String getXmlFileName(Class c){
- String path = "";
- String xml = c.getName().substring((c.getName().lastIndexOf("."))+1)+".xml";
- path = c.getResource(xml).toString();
- return path;
- }
- //读取xml,把属性,顺序放入map
- private Map readXml(String path){
- Map map = new HashMap();
- SAXBuilder sb = new SAXBuilder();
- try {
- Document myDocument = sb.build(path);
- Element root = myDocument.getRootElement();
- Element table = root.getChild("table");
- List<Element> list = table.getChildren("property");
- for(Element e:list){
- String order = e.getAttributeValue("order");
- String name = e.getAttributeValue("name");
- map.put(name, order);
- }
- } catch (JDOMException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- return map;
- }
- //读取xml,把属性名,表字段放入map
- private Map readXmlForsql(String path){
- Map map = new HashMap();
- SAXBuilder sb = new SAXBuilder();
- try {
- Document myDocument = sb.build(path);
- if(myDocument==null){
- throw new RuntimeException("请检查xml的路径");
- }
- Element root = myDocument.getRootElement();
- Element table = root.getChild("table");
- List<Element> list = table.getChildren("property");
- String tableName = table.getAttributeValue("name");
- map.put("table_name", tableName);
- for(Element e:list){
- String column = e.getAttributeValue("column");
- String name = e.getAttributeValue("name");
- map.put(name, column);
- }
- } catch (JDOMException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- return map;
- }
- //根据对象转换插入sql语句
- public String fromBean2InsertSql(Object o){
- StringBuilder sb = new StringBuilder();
- // System.out.println(o.getClass());
- String path = this.getXmlFileName(o.getClass());
- Map map = this.readXmlForsql(path);
- sb.append("insert into "+map.get("table_name"));
- map.remove("table_name");
- Set set = map.entrySet();
- Iterator it = set.iterator();
- StringBuilder columns = new StringBuilder();
- StringBuilder values = new StringBuilder();
- columns.append("(");
- values.append("(");
- while(it.hasNext()){
- Map.Entry e = (Map.Entry)it.next();
- columns.append(e.getValue()+",");
- Object ob = this.getValueOfObject(o, e.getKey().toString());
- if(null == ob){
- values.append("null,");
- }else{
- values.append("'"+ob+"',");
- }
- }
- String cStr = columns.toString().substring(0, columns.toString().length()-1);
- String vStr = values.toString().substring(0,values.toString().length()-1);
- sb.append(cStr+")");
- sb.append(" values ");
- sb.append(vStr+")");
- return sb.toString();
- }
- //根据属性名称获取对象该属性的值
- public Object getValueOfObject(Object ob,String field){
- Object o = new Object();
- String s = field.substring(0,1);
- String fieldNew = s.toUpperCase()+field.substring(1,field.length());
- String method = "get"+fieldNew;
- try {
- try {
- Method m = ob.getClass().getMethod(method);
- o = m.invoke(ob);
- } catch (IllegalArgumentException e) {
- e.printStackTrace();
- } catch (IllegalAccessException e) {
- e.printStackTrace();
- } catch (InvocationTargetException e) {
- e.printStackTrace();
- }
- } catch (SecurityException e) {
- e.printStackTrace();
- } catch (NoSuchMethodException e) {
- e.printStackTrace();
- }
- return o;
- }
- //根据id和class生成删除sql
- public String getDeleteSql(Class c,String id){
- String path = this.getXmlFileName(c);
- Map map = this.readXmlForsql(path);
- String sql = "delete from "+map.get("table_name")+" where fd_id='"+id+"'";
- return sql;
- }
- //根据对象生成更新sql
- public String getUpdateSql(Object o){
- StringBuilder sb = new StringBuilder();
- String path = this.getXmlFileName(o.getClass());
- Map map = this.readXmlForsql(path);
- sb.append("update "+map.get("table_name"));
- //把主键和表名移除,全剩属性
- map.remove("fdId");
- map.remove("table_name");
- Set set = map.entrySet();
- Iterator it = set.iterator();
- StringBuilder values = new StringBuilder();
- values.append(" set ");
- while(it.hasNext()){
- Map.Entry e = (Map.Entry)it.next();
- Object ob = this.getValueOfObject(o, e.getKey().toString());
- if(null == ob){
- values.append(e.getValue()+"=null");
- }else{
- values.append(e.getValue()+"='"+ob+"',");
- }
- }
- String vStr = values.toString().substring(0,values.toString().length()-1);
- sb.append(vStr);
- //主键地方需要改进
- sb.append(" where fd_id= '"+getValueOfObject(o,"fdId")+"'");
- return sb.toString();
- }
- //把一个List<Object[]> 转为List<Bean>
- public List<?> getBeanListFromObjectArrList(List<Object[]> list, Class c ){
- List newList = new ArrayList ();
- for(Object[] o:list){
- try{
- Object ob = this.fromObject2Bean(c, o);
- newList.add(ob);
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- return newList;
- }
- //根据对象生成查询sql
- public String getQuerySqlFromBean(Object o){
- StringBuilder sb = new StringBuilder();
- String path = this.getXmlFileName(c.class);
- Map map = this.readXmlForsql(path);
- sb.append("select * from "+map.get("table_name")+" where 1 = 1");
- map.remove("table_name");
- map.remove("fdId");
- Set set = map.entrySet();
- sb.append(this.getFilterSql(set, o));
- return sb.toString();
- }
- //生成过滤条件语句
- public String getFilterSql(Set set,Object o){
- StringBuilder sb = new StringBuilder();
- Iterator it = set.iterator();
- while(it.hasNext()){
- Map.Entry e = (Map.Entry)it.next();
- Object ob = this.getValueOfObject(o, e.getKey().toString());
- if(null != ob && !ob.toString().equals("")){
- sb.append(" and "+e.getValue()+"='"+ob+"'");
- }
- }
- return sb.toString();
- }
- //查询数目语句
- public String getCountSqlFromBean(Object o){
- StringBuilder sb = new StringBuilder();
- String path = this.getXmlFileName(c.class);
- Map map = this.readXmlForsql(path);
- sb.append("select count(0) from "+map.get("table_name")+" where 1 = 1");
- map.remove("table_name");
- map.remove("fdId");
- Set set = map.entrySet();
- sb.append(this.getFilterSql(set, o));
- return sb.toString();
- }
- //生成查询分页语句
- public String getQuerySqlFromBean(Object o,int pageNo,int pageSize){
- String sql = this.getQuerySqlFromBean(o);
- sql += " limit "+(pageNo-1)*pageSize+","+pageSize;
- return sql;
- }
- //根据id和class返回对象
- public Object getBeanById(Class c,String id){
- String path = this.getXmlFileName(c);
- Map map = this.readXmlForsql(path);
- String table = map.get("table_name").toString();
- //此处主键需要改进
- String sql = "select * from "+table+" where fd_id='"+id+"'";
- List<Object[]> list = JdbcTemplateUtil.getObjectList(jdbcTemplateAssist, sql);
- Object[] o = list.get(0);
- Object ob = new Object();
- try{
- ob = this.fromObject2Bean(c, o);
- }catch(Exception e){
- e.printStackTrace();
- }
- return ob;
- }
- //根据过滤条件生成查询sql
- public String getQuerySqlFromFilters(Class c,Map equilFilter,Map likeFilter){
- StringBuilder sb = new StringBuilder();
- String path = this.getXmlFileName(c);
- Map map = this.readXmlForsql(path);
- sb.append("select * from "+map.get("table_name")+" where 1 = 1");
- sb.append(this.createrSqlByFilters(equilFilter, likeFilter));
- return sb.toString();
- }
- //生成过滤条件的sql
- public String createrSqlByFilters(Map equilFilter,Map likeFilter){
- StringBuilder sb = new StringBuilder();
- Set eSet = equilFilter.entrySet();
- Iterator eIt = eSet.iterator();
- while(eIt.hasNext()){
- Map.Entry e = (Map.Entry)eIt.next();
- if(null != e.getValue() && !"".equals(e.getValue().toString())){
- sb.append(" and "+e.getKey()+"='"+e.getValue()+"'");
- }
- }
- Set lSet = likeFilter.entrySet();
- Iterator lIt = lSet.iterator();
- while(lIt.hasNext()){
- Map.Entry e = (Map.Entry)lIt.next();
- if(null != e.getValue() && !"".equals(e.getValue().toString())){
- sb.append(" and "+e.getKey()+" like '%"+e.getValue()+"%'");
- }
- }
- return sb.toString();
- }
- //根据过滤条件生成分页
- public String getQuerySqlFromFilters(Class c,Map equilFilter,Map likeFilter,int pageNo,int pageSize){
- String sql = this.getQuerySqlFromFilters(c, equilFilter, likeFilter);
- sql += " limit "+(pageNo-1)*pageSize+","+pageSize;
- return sql;
- }
- //根据过滤条件查询数目
- public String getCountSqlFromFilters(Class c,Map equilFilter,Map likeFilter){
- String path = this.getXmlFileName(c);
- Map map = this.readXmlForsql(path);
- StringBuilder sb = new StringBuilder();
- sb.append("select count(0) from "+map.get("table_name")+" where 1 = 1");
- sb.append(this.createrSqlByFilters(equilFilter, likeFilter));
- return sb.toString();
- }
- /**
- * @param args
- * @throws IllegalAccessException
- * @throws InstantiationException
- * @throws InvocationTargetException
- * @throws IllegalArgumentException
- */
- public static void main(String[] args) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
- MappingUtil u = new MappingUtil();
- Class c = Exam.class;
- Object[] o = new Object[4];
- o[0]="1";
- o[1]="2";
- o[2]="3";
- o[3]="4";
- u.fromObject2Bean(c, o);
- Exam e = new Exam();
- e.setFdAddress("地址");
- e.setFdId("1");
- e.setFdContent("备注");
- e.setFdTime("2012-06-14");
- System.out.println(u.fromBean2InsertSql(e));
- System.out.println(u.getUpdateSql(e));
- System.out.println(u.getDeleteSql(c, e.getFdId()));
- }
- }
Exam.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <root>
- <table name="exam_info">
- <property name="fdId" column="fd_id" order="1"/>
- <property name="fdAddress" column="fd_address" order="2"/>
- <property name="fdTime" column="fd_time" order ="3"/>
- <property name="fdContent" column="fd_content" order="4"/>
- <property name="fdOperatorId" column="fd_operator_id" order="5"/>
- <property name="fdOparatorName" column="fd_operator_name" order="6"/>
- <property name="fdCreateTime" column="fd_create_time" order="7"/>
- <property name="fdAlterTime" column="fd_alter_time" order="8"/>
- <property name="fdIsAbandon" column="fd_is_abandon" order="9"/>
- <property name="fdName" column="fd_name" order="10"/>
- </table>
- </root>
Exam.java
- package com.landray.kmss.sys.transport.model;
- public class Exam {
- private String fdId;
- private String fdAddress;
- private String fdTime;
- private String fdContent;
- private String fdOperatorId;
- private String fdOparatorName;
- private String fdCreateTime;
- private String fdAlterTime;
- private String fdIsAbandon;
- private String fdName;
- public String getFdName() {
- return fdName;
- }
- public void setFdName(String fdName) {
- this.fdName = fdName;
- }
- public String getFdId() {
- return fdId;
- }
- public void setFdId(String fdId) {
- this.fdId = fdId;
- }
- public String getFdAddress() {
- return fdAddress;
- }
- public void setFdAddress(String fdAddress) {
- this.fdAddress = fdAddress;
- }
- public String getFdTime() {
- return fdTime;
- }
- public void setFdTime(String fdTime) {
- this.fdTime = fdTime;
- }
- public String getFdContent() {
- return fdContent;
- }
- public void setFdContent(String fdContent) {
- this.fdContent = fdContent;
- }
- public String getFdOperatorId() {
- return fdOperatorId;
- }
- public void setFdOperatorId(String fdOperatorId) {
- this.fdOperatorId = fdOperatorId;
- }
- public String getFdOparatorName() {
- return fdOparatorName;
- }
- public void setFdOparatorName(String fdOparatorName) {
- this.fdOparatorName = fdOparatorName;
- }
- public String getFdCreateTime() {
- return fdCreateTime;
- }
- public void setFdCreateTime(String fdCreateTime) {
- this.fdCreateTime = fdCreateTime;
- }
- public String getFdIsAbandon() {
- return fdIsAbandon;
- }
- public void setFdIsAbandon(String fdIsAbandon) {
- this.fdIsAbandon = fdIsAbandon;
- }
- public String getFdAlterTime() {
- return fdAlterTime;
- }
- public void setFdAlterTime(String fdAlterTime) {
- this.fdAlterTime = fdAlterTime;
- }
- }
该表的sql
- /*
- Navicat MySQL Data Transfer
- Source Server :
- Source Server Version : 50145
- Source Host :
- Source Database : china
- Target Server Type : MYSQL
- Target Server Version : 50145
- File Encoding : 65001
- Date: 2012-06-16 16:48:20
- */
- SET FOREIGN_KEY_CHECKS=0;
- -- ----------------------------
- -- Table structure for `exam_info`
- -- ----------------------------
- DROP TABLE IF EXISTS `exam_info`;
- CREATE TABLE `exam_info` (
- `fd_id` varchar(36) NOT NULL DEFAULT '',
- `fd_address` varchar(100) DEFAULT NULL,
- `fd_time` varchar(100) DEFAULT NULL,
- `fd_content` varchar(500) DEFAULT NULL,
- `fd_operator_id` varchar(36) DEFAULT NULL,
- `fd_operator_name` varchar(50) DEFAULT NULL,
- `fd_create_time` varchar(20) DEFAULT NULL,
- `fd_alter_time` varchar(20) DEFAULT NULL,
- `fd_is_abandon` varchar(5) DEFAULT NULL,
- `fd_name` varchar(50) DEFAULT NULL,
- PRIMARY KEY (`fd_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- -- ----------------------------
- -- Records of exam_info
- -- ----------------------------
- INSERT INTO `exam_info` VALUES ('137ef3d1a4d9b4c5d88591a43a78ccc7', '天津第一高级中学', '2012-03-30 09:00:00', '考试说明', '12f67c5cfba228b501b8f464308b5cd5', '培训主管', '2012-06-15 04:23:22', '2012-06-15 04:46:22', '0', '2012第一季度考试');
- INSERT INTO `exam_info` VALUES ('137ef531c4680441617a0dd4741ba97c', '第一实验小学', '2012-06-30 16:46:00', '22', '12f67c5cfba228b501b8f464308b5cd5', '培训主管', '2012-06-15 04:47:10', '2012-06-15 04:47:14', '0', '天津第二季度考试');
- INSERT INTO `exam_info` VALUES ('137f3f66306e18d3d79ba7f46c69efa8', '天津第一高级中学', '2012-09-29 14:23:00', '', '12f67c5cfba228b501b8f464308b5cd5', '培训主管', '2012-06-16 02:23:59', '2012-06-16 02:23:59', '0', '2012第三季度考试');
- INSERT INTO `exam_info` VALUES ('137f3f7df45447143f7130148fba42ad', '天津实验小学', '2012-12-16 14:25:00', '', '12f67c5cfba228b501b8f464308b5cd5', '培训主管', '2012-06-16 02:25:37', '2012-06-16 02:25:37', '0', '2012天津第四季度');
目前只支持String类型的.......