Java反射 JavaBean对象自动生成插入,更新,删除,查询sql语句操作

Java反射 JavaBean对象自动生成插入,更新,删除,查询sql语句操作

通过反射根据提供的表名、POJO类型、数据对象自动生成sql语句。

如名为 User 的javaBean与名为 user 的数据库表对应，可以提供一个封装有数据的User对象user，根据user中含有的数据自动生成sql语句。

1、生成插入语句（插入user中包含的非空数据的语句）：

String insertSql = getInsertSql("user", User.class, user);

2、生成更新语句（user中id不能为空）：

String updateSql = getUpdateSql("user", User.class, user);

3、生成删除语句（根据user中第一个非空属性值作为查找条件删除）：

//生成删除id为1的语句

User user = new User();

user.setId(1);

String deleteSql = getDeleteSql("user", User.class, user);

4、生成查询语句（根据user中第一个非空属性值为查找条件）：

//生成查询id为1的语句

User user = new User();

user.setId(1);

String selectSql = getSelectSql("user", User.class, user);

package com.hims.util;

import cn.hutool.core.util.ReflectUtil;

import cn.hutool.core.util.StrUtil;

import com.hims.bean.User;

import java.lang.reflect.Field;

public class ProduceSql {

//String insertSql = getInsertSql("user", User.class, user);

/**

* 生成插入语句

* @param tablename 表明

* @param t 有数据的实体

* @param 数据实体类型 如 User

*/

public static String getInsertSql(String tablename, T t) throws IllegalArgumentException {

//insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...)

boolean flag = false;

String sql = "";

Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);

StringBuffer topHalf = new StringBuffer("insert into "+tablename+" (");

StringBuffer afterAalf = new StringBuffer("values (");

for (Field field : fields) {

if ("ID".equals(field.getName()) || "id".equals(field.getName())){

continue; //id 自动生成无需手动插入

}

topHalf.append(field.getName() + ",");

if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {

afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");

flag = true;

} else {

afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");

flag = true;

}

}

if (!flag) {

throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty.You must provide an object with at least one attribute.");

}

topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));

afterAalf = new StringBuffer(StrUtil.removeSuffix(afterAalf.toString(), ","));

topHalf.append(") ");

afterAalf.append(") ");

sql = topHalf.toString() + afterAalf.toString();

return sql;

}

/**

* 生成更新语句

* 必须含有id

* 数据实体中 null 与 空字段不参与更新

* @param tablename 数据库中的表明

* @param t 有数据的实体

* @param 数据实体类型,如 User

*/

public static String getUpdateSql(String tablename, T t) throws IllegalArgumentException {

//UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where ID=xxx

//or

//UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where id=xxx

boolean flag = false;

String sql = "";

String id = ""; //保存id列名：ID or id

Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);

sql = "update "+tablename+" set ";

for (Field field : fields) {

StringBuffer tmp = new StringBuffer();

if ("ID".equals(field.getName()) || "id".equals(field.getName())){

id = field.getName();

continue;//更新的时候无需set id=xxx

}

if (ReflectUtil.getFieldValue(t, field.getName()) != null && (String)ReflectUtil.getFieldValue(t, field.getName()) != "") {

tmp.append( field.getName() + "=");

if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {

tmp.append( "'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");

flag = true;

} else {

tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");

flag = true;

}

sql += tmp;

}

}

if (!flag) {

throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty except for ID.You must provide an object with at least one attribute exclude ID.");

}

sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id)+"'";

return sql;

}

/**

* 生成删除语句

* 根据 user 中第一个不为空的字段删除,应该尽量使用 id,提供至少一个非空属性

* @param tablename 表明

* @param t 有数据的实体

* @param 数据实体类型 如 User

*/

public static String getDeleteSql(String tablename, T t) throws IllegalArgumentException {

//delete from table_name where column_name = value

return getSelectOrDeleteSql(tablename, t, "delete");

}

/**

* 生成查询语句

* 根据 user 中第一个不为空的字段查询

* @param tablename 表名

* @param t 有数据的实体

* @param 数据实体类型 如 User

*/

public static String getSelectSql(String tablename, T t) throws IllegalArgumentException {

//delete from table_name where column_name = value

return getSelectOrDeleteSql(tablename, t, "select *");

}

/**

* 根据 operation 生成一个如：operation from table_name where column_name = value 的sql语句

* @param tablename

* @param t

* @param operation "select *" or "delete"

* @param

* @return

* @throws IllegalArgumentException

*/

private static String getSelectOrDeleteSql(String tablename, T t, String operation) throws IllegalArgumentException {

//operation from table_name where column_name = value

boolean flag = false;

String sql = "";

Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);

StringBuffer topHalf = new StringBuffer(operation + " from " + tablename + " where ");

fhttp://or (Field field : fields) {

if ("ID".equals(field.getName()) || "id".equals(field.getName())) {

if (ReflectUtil.getFieldValue(t, field.getName()) != null && (int)ReflectUtil.getFieldValue(t, field.getName()) != 0) {

//id 不为空

topHalf.append(field.getName() + " = " + ReflectUtil.getFieldValue(t, field.getName()));

flag = true;

break;

}

}

else {

if (ReflectUtil.getFieldValue(t, field.getName()) != null && (String)ReflectUtil.getFieldValue(t, field.getName()) != "") {

topHalf.append(field.getName() + " = '" + YlXJyAMoTSReflectUtil.getFieldValue(t, field.getName()) + "'");

flag = true;

break;

}

}

}

if (!flag) {

throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty.You must provide an object with at least one attribute.");

}

sql = topHalf.toString();

return sql;

}

}

补充知识：通过java反射实现对javabean生成各种sql语句

通过java反射实现对javabean生成各种sql语句，有请大家评论，更改

package com.pdt.util;

import java.lang.reflect.Field;

import java.util.ArrayList;

import java.util.List;

public class BeanUtil {

/**

* @param args

*/

public static void main(String[] args) {

System.out.println(getBeanFilesList("com.pdt.bean.Dictionary"));

System.out.println(genCreateTableSql("com.pdt.bean.Dictionary"));

System.out.println(genInsertSql("com.pdt.bean.Dictionary"));

}

public static String getBeanName(String bean){

try {

Class clz = Class.forName(bean);

String clzStr = clz.toString();

//得到类名

String beanName = clzStr.substring(clzStr.lastIndexOf(".")+1).toLowerCase();

return beanName;

} catch (ClassNotFoundException e) {

e.printStackTrace();

return "";

}

}

public static List getBeanPropertyList(String bean){

try {

Class clz = Class.forName(bean);

Field[] strs = clz.getDeclaredFields();

List propertyList = new ArrayList();

for (int i = 0; i < strs.length; i++) {

String protype = strs[i].getType().toString();

propertyList.add(protype.substring(protype.lastIndexOf(".")+1)+"`"+strs[i].getName());

}

return propertyList;

} catch (ClassNotFoundException e) {

e.printStackTrace();

return null;

}

}

public static String getBeanFilesList(String bean){

try {

Class clz = Class.forName(bean);

Field[] strs = clz.getDeclaredFields();

StringBuffer sb = new StringBuffer();

for (int i = 0; i < strs.length; i++) {

String protype = strs[i].getType().toString();

if (!strs[i].getName().equals("tableName")&&!strs[i].getType().equals("List")) {

sb.append(strs[i].getName()+",");

}

}

sb.deleteCharAt(sb.toString().lastIndexOf(","));

return sb.toString();

} catch (ClassNotFoundException e) {

e.printStackTrace();

return null;

}

}

/**

* 生成建表語句

* @param bean

* @return

*/

public static String genCreateTableSql(String bean){

List beanPropertyList = getBeanPropertyList(bean);

StringBuffer sb = new StringBuffer("create table wnk_pdt_"+getBeanName(bean)+"(\n");

for (String string : beanPropertyList) {

String[] propertys = string.split("`");

if (!propertys[1].equals("tableName")&&!propertys[1].equals("param")&&!propertys[0].equals("List")) {

if (propertys[1].equals("id")) {

sb.append(" id bigint primary key auto_increment,\n");

} else {

if (propertys[0].equals("int")) {

sb.append(" " + propertys[1] + " int default 0 comment '',\n");

} else if (propertys[0].equals("String")) {

sb.append(" " + propertys[1] + " varchar(2000) default '' comment '',\n");

} else if (propertys[0].equals("double")) {

sb.append(" " + propertys[1] + " double(10,2) default 0.0 comment '',\n");

} else if (propertys[0].equals("Date")) {

sb.append(" " + propertys[1] + " datetime comment '',\n");

}

}

}

}

sb.append(")");

sb.deleteCharAt(sb.lastIndexOf(","));

return sb.toString();

}

/**

* 生成查询语句

* @param bean

* @return

*/

public static String genSelectAllSql(String bean){

String filesList = getBeanFilesList(bean);

return "select \n "+filesList+" \n from \n wnk_pdt_"+getBeanName(bean)+"";

}

/**

* 生成插入语句

* @param bean

* @return

*/

public static String genInsertSql(String bean){

String filesList = getBeanFilesList(bean);

int fl = DataUtil.getCountSonStr(filesList,",")+1;

String wenhao = "";

for (int i = 0; i < fl; i++) {

if(i==fl-1){

wenhao = wenhao+"?";

}else{

wenhao = wenhao+"?,";

}

}

return "insert into wnk_pdt_"+getBeanName(bean)+"("+filesList+") values("+wenhao+")";

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。