根据JavaBean生成数据库增删改查的SQL语句。1,genInsertSQL(Object bean);2,genUpdateSQL(Object bean);3,genDeleteSQL(Object bean);4,genFindAllSQL(String tableName);5,genFindAllSQL(String tableName)
import java.io.Serializable;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;
import com.domain.AG;
/**
* FOR JDBC RUID Operation,This class can generate SQL by parameter of
* Object(update/insert/delete)
* or Class(select)
*
*@category com.util
*@class SQLUtil
*@author zhusheng3@126.com
*@date 2008-6-7 下午04:08:40
*@see
*/
public class SQLUtil
{
private final static String database_table_prefix="database.table.prefix";
private String pkColumnName="SN";
public SQLUtil()
{
//pkColumnName is vary of Table,so mothods which need pkColumn can not use static
this.pkColumnName="SN";
}
public SQLUtil(String pkColumnName)
{
//pkColumnName is vary of Table,so mothods which need pkColumn can not use static
this.pkColumnName=pkColumnName;
}
/**
* @param Object the object for insert
* @return an insert String like below:
* insert into vmm_ag(sn,domainname,modifydate,testDouble)
* values(5,'domainname5',to_date('20080121','YYYYMMDD'),22.4)
*/
public static String genInsertSQL(Object bean)
{
List <String> fields=BeanUtil.getFields(bean);
List <String> values=BeanUtil.getValues2String(bean);
Hashtable fieldsTypes = BeanUtil.getFieldsAndTypes(bean);
//System.out.println("fields.size():"+fields.size()+"--values.size():"+values.size());
if(fields.size() !=values.size())
{
String error="ERROR:genInsertSQL(bean)->fields's size does not match values' size,bean:"+bean+
"/n fields.size():"+fields.size()+"--values.size():"+values.size();
System.err.println(error);
return error;
}
//get database table Name
String tableName=getTableName(bean);
String insertSQL="insert into "+tableName+" (";
for (int i = 0; i < fields.size(); i++)
{
//if value of this field is null,then ignore this field
if(values.get(i) !=null)
{
insertSQL+=fields.get(i);
//if not the last field,then add ","
if(i!=fields.size()-1)
{
insertSQL+=",";
}
}
}
insertSQL+=")";
insertSQL+="values (";
for (int j = 0; j <fields.size(); j++)
{
String value="";
String typeOfThisField=(String) fieldsTypes.get(fields.get(j));
if(typeOfThisField.equalsIgnoreCase("String"))
{ //when String ,add '' around the string ,like:'tempString'
value="'"+values.get(j)+"'";
}
else if(typeOfThisField.equalsIgnoreCase("Date"))
{ // select to_date('2008-01-01 14:26:38','YYYY-MM-DD HH24:MI:SS') from dual
value="to_date('"+(String) values.get(j)+"','YYYY-MM-DD HH24:MI:SS')";
}
else
{ //other types like long,double ,add nothing but their value
value=(String) values.get(j);
}
insertSQL +=value;
if(j!=fields.size()-1)
{
insertSQL +=",";
}
}
insertSQL +=")";
//System.out.println("SQL is:"+insertSQL);
return insertSQL;
}
public String genUpdateSQL(Object bean)
{
List <String> fields=BeanUtil.getFields(bean);
List <String> values=BeanUtil.getValues2String(bean);
Hashtable fieldsTypes = BeanUtil.getFieldsAndTypes(bean);
if(fields.size() !=values.size())
{
String error="ERROR:genUpdateSQL(bean)->fields's size does not match values' size,bean:"+bean+
"/n fields.size():"+fields.size()+"--values.size():"+values.size();
System.err.println(error);
return error;
}
//get database table Name
String tableName=getTableName(bean);
String updateSQL="update "+ tableName +" set ";
String pkColumnValue="";
for (int i = 0; i < fields.size(); i++)
{
String setSQL=fields.get(i)+"=";
String typeOfThisField=(String) fieldsTypes.get(fields.get(i));
String value="";
if(typeOfThisField.equalsIgnoreCase("String"))
{ //when String ,add '' around the string ,like:'tempString'
value="'"+values.get(i)+"'";
setSQL+=value;
if(pkColumnName.equalsIgnoreCase(fields.get(i)))
{
pkColumnValue=value;
}
}
else if(typeOfThisField.equalsIgnoreCase("Date"))
{ // select to_date('2008-01-01 14:26:38','YYYY-MM-DD HH24:MI:SS') from dual
value="to_date('"+(String) values.get(i)+"','YYYY-MM-DD HH24:MI:SS')";
setSQL+=value;
if(pkColumnName.equalsIgnoreCase(fields.get(i)))
{
pkColumnValue=value;
}
}
else
{ //other types like long,double ,add nothing but their value
value=(String) values.get(i);
setSQL+=value;
if(pkColumnName.equalsIgnoreCase(fields.get(i)))
{
pkColumnValue=value;
}
}
if(i!=fields.size()-1)
{
setSQL +=",";
}
updateSQL +=setSQL;
}
updateSQL +=" where "+pkColumnName +"="+pkColumnValue;
return updateSQL;
}
/**
* @param Object the object for insert
* @return an insert String like below:
* insert into vmm_ag(sn,domainname,modifydate,testDouble)
* values(5,'domainname5',to_date('20080121','YYYYMMDD'),22.4)
*/
public String genDeleteSQL(Object bean)
{
List <String> fields=BeanUtil.getFields(bean);
List <String> values=BeanUtil.getValues2String(bean);
Hashtable fieldsTypes = BeanUtil.getFieldsAndTypes(bean);
//get database table Name
String tableName=getTableName(bean);
String pkType=(String) fieldsTypes.get(pkColumnName);
String pkValue="";
for (int i = 0; i < fields.size(); i++)
{ //find the pk column's value
if(pkColumnName.toUpperCase().equalsIgnoreCase(fields.get(i)))
{
pkValue=values.get(i);
}
}
//if the pkType is String ,then need to add '' around the value
//number
if ("String".equalsIgnoreCase(pkType))
{
pkValue="'"+pkValue+"'";
}
//String deleteSQL ="delete "+tableName +" where "+pkColumnName+"="+pkValue;
String deleteSQL=genRemoveByIdSQL(tableName,pkValue);
return deleteSQL;
}
/**
* @param Object the object for select
* @return an select String like below:
* select * from vmm_ag
*/
public static String genFindAllSQL(String tableName)
{
String findAllSQL ="select * from "+tableName;
return findAllSQL;
}
/**
* @param Object the object for select
* @return an select String like below:
* select * from vmm_ag where sn=1
*/
public String genFindByIdSQL(String tableName,Serializable id)
{
String findByIdSQL ="select * from "+tableName +" where "+pkColumnName+"="+id;
return findByIdSQL;
}
/**
* @param Object the object for select
* @return an select String like below:
* select * from vmm_ag where sn=1
*/
public String genRemoveByIdSQL(String tableName,Serializable id)
{
String findByIdSQL ="delete from "+tableName +" where "+pkColumnName+"="+id;
return findByIdSQL;
}
/**
* TableName=tablePrefix+ClassName
* eg. VMM_AGGroup="VMM_"+"AGGroup"
* the "VMM_" is the prefix
* the "AGGroup" is the ClassName
* so,the "VMM_AGGroup" is the tableName
* @param bean
* @return tableName
*/
public static String getTableName(Object bean)
{
String className=BeanUtil.getClassName(bean);
//get tablePrefix from config file
String tablePrefix=PropertiesUtil.getProperty(database_table_prefix);
//get database table Name
String tableName=tablePrefix+className;
return tableName;
}
public static String getTableName(String className)
{
//get tablePrefix from config file
String tablePrefix=PropertiesUtil.getProperty(database_table_prefix);
//get database table Name
String tableName=tablePrefix+className;
return tableName;
}
/**
* 1、在生成Fields和values时就只生成value不为null的列
* 2、数据类型的处理:
* (1)String加''
* (2)long和double直接写
* (3)date型在生成values的时候就生成字符串行20080218 235632,精确到秒
* @param args
*/
public static void main(String[] args)
{
Date aDate = new Date();
AG ag = new AG();
String sql=genInsertSQL(ag);
System.out.println(sql);
}
}