利用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类型的.......