根据JavaBean生成数据库增删改查的SQL语句
genInsertSQL(Object bean);
genUpdateSQL(Object bean);
genDeleteSQL(Object bean);
genFindAllSQL(String tableName);
genFindAllSQL(String tableName)
import javaioSerializable;
import javalangreflectField;
import javatextSimpleDateFormat;
import javautilArrayList;
import javautilDate;
import javautilHashtable;
import javautilList;
import comdomainAG;
/**
* FOR JDBC RUID OperationThis class can generate SQL by parameter of
* Object(update/insert/delete)
* or Class(select)
*
*@category comutil
*@class SQLUtil
*@author
*@date 下午::
*@see
*/
public class SQLUtil
{
private final static String database_table_prefix=databasetableprefix;
private String pkColumnName=SN;
public SQLUtil()
{
//pkColumnName is vary of Tableso mothods which need pkColumn can not use static
thispkColumnName=SN;
}
public SQLUtil(String pkColumnName)
{
//pkColumnName is vary of Tableso mothods which need pkColumn can not use static
thispkColumnName=pkColumnName;
}
/**
* @param Object the object for insert
* @return an insert String like below:
* insert into vmm_ag(sndomainnamemodifydatetestDouble)
* values(domainnameto_date(YYYYMMDD))
*/
public static String genInsertSQL(Object bean)
{
List <String> fields=BeanUtilgetFields(bean);
List <String> values=BeanUtilgetValuesString(bean);
Hashtable fieldsTypes = BeanUtilgetFieldsAndTypes(bean);
//Systemoutprintln(fieldssize():+fieldssize()+valuessize():+valuessize());
if(fieldssize() !=valuessize())
{
String error=ERROR:genInsertSQL(bean)>fieldss size does not match values sizebean:+bean+
\n fieldssize():+fieldssize()+valuessize():+valuessize();
Systemerrprintln(error);
return error;
}
//get database table Name
String tableName=getTableName(bean);
String insertSQL=insert into +tableName+ (;
for (int i = ; i < fieldssize(); i++)
{
//if value of this field is nullthen ignore this field
if(valuesget(i) !=null)
{
insertSQL+=fieldsget(i);
//if not the last fieldthen add
if(i!=fieldssize())
{
insertSQL+=;
}
}
}
insertSQL+=);
insertSQL+=values (;
for (int j = ; j <fieldssize(); j++)
{
String value=;
String typeOfThisField=(String) fieldsTypesget(fieldsget(j));
if(typeOfThisFieldequalsIgnoreCase(String))
{ //when String add around the string like:tempString
value=+valuesget(j)+;
}
else if(typeOfThisFieldequalsIgnoreCase(Date))
{ // select to_date( ::YYYYMMDD HH:MI:SS) from dual
value=to_date(+(String) valuesget(j)+YYYYMMDD HH:MI:SS);
}
else
{ //other types like longdouble add nothing but their value
value=(String) valuesget(j);
}
insertSQL +=value;
if(j!=fieldssize())
{
insertSQL +=;
}
}
insertSQL +=);
//Systemoutprintln(SQL is:+insertSQL);
return insertSQL;
}
public String genUpdateSQL(Object bean)
{
List <String> fields=BeanUtilgetFields(bean);
List <String> values=BeanUtilgetValuesString(bean);
Hashtable fieldsTypes = BeanUtilgetFieldsAndTypes(bean);
if(fieldssize() !=valuessize())
{
String error=ERROR:genUpdateSQL(bean)>fieldss size does not match values sizebean:+bean+
\n fieldssize():+fieldssize()+valuessize():+valuessize();
Systemerrprintln(error);
return error;
}
//get database table Name
String tableName=getTableName(bean);
String updateSQL=update + tableName + set ;
String pkColumnValue=;
for (int i = ; i < fieldssize(); i++)
{
String setSQL=fieldsget(i)+=;
String typeOfThisField=(String) fieldsTypesget(fieldsget(i));
String value=;
if(typeOfThisFieldequalsIgnoreCase(String))
{ //when String add around the string like:tempString
value=+valuesget(i)+;
setSQL+=value;
if(pkColumnNameequalsIgnoreCase(fieldsget(i)))
{
pkColumnValue=value;
}
}
else if(typeOfThisFieldequalsIgnoreCase(Date))
{ // select to_date( ::YYYYMMDD HH:MI:SS) from dual
value=to_date(+(String) valuesget(i)+YYYYMMDD HH:MI:SS);
setSQL+=value;
if(pkColumnNameequalsIgnoreCase(fieldsget(i)))
{
pkColumnValue=value;
}
}
else
{ //other types like longdouble add nothing but their value
value=(String) valuesget(i);
setSQL+=value;
if(pkColumnNameequalsIgnoreCase(fieldsget(i)))
{
pkColumnValue=value;
}
}
if(i!=fieldssize())
{
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(sndomainnamemodifydatetestDouble)
* values(domainnameto_date(YYYYMMDD))
*/
public String genDeleteSQL(Object bean)
{
List <String> fields=BeanUtilgetFields(bean);
List <String> values=BeanUtilgetValuesString(bean);
Hashtable fieldsTypes = BeanUtilgetFieldsAndTypes(bean);
//get database table Name
String tableName=getTableName(bean);
String pkType=(String) fieldsTypesget(pkColumnName);
String pkValue=;
for (int i = ; i < fieldssize(); i++)
{ //find the pk columns value
if(pkColumnNametoUpperCase()equalsIgnoreCase(fieldsget(i)))
{
pkValue=valuesget(i);
}
}
//if the pkType is String then need to add around the value
//number
if (StringequalsIgnoreCase(pkType))
{
pkValue=+pkValue+;
}
//String deleteSQL =delete +tableName + where +pkColumnName+=+pkValue;
String deleteSQL=genRemoveByIdSQL(tableNamepkValue);
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=
*/
public String genFindByIdSQL(String tableNameSerializable 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=
*/
public String genRemoveByIdSQL(String tableNameSerializable 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
* sothe VMM_AGGroup is the tableName
* @param bean
* @return tableName
*/
public static String getTableName(Object bean)
{
String className=BeanUtilgetClassName(bean);
//get tablePrefix from config file
String tablePrefix=PropertiesUtilgetProperty(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=PropertiesUtilgetProperty(database_table_prefix);
//get database table Name
String tableName=tablePrefix+className;
return tableName;
}
/**
* 在生成Fields和values时就只生成value不为null的列
* 数据类型的处理
* ()String加
* ()long和double直接写
* ()date型在生成values的时候就生成字符串行 精确到秒
* @param args
*/
public static void main(String[] args)
{
Date aDate = new Date();
AG ag = new AG();
String sql=genInsertSQL(ag);
Systemoutprintln(sql);
}
}
