熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> Java核心技術 >> 正文

根據JavaBean生成數據庫增刪改查的SQL語句

2022-06-13   來源: Java核心技術 

  根據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);

  }

  }


From:http://tw.wingwit.com/Article/program/Java/hx/201311/25899.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.