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

2024-11-03 技术教程

这篇文章将为大家详细讲解有关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 <T> 数据实体类型如 User */ public static <T> 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 <T> 数据实体类型,如 User */ public static <T> 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 <T> 数据实体类型如 User */ public static <T> 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 <T> 数据实体类型如 User */ public static <T> 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 <T> * @return * @throws IllegalArgumentException */ private static <T> 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 "); for (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() + " = '" + ReflectUtil.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<String> getBeanPropertyList(String bean){try {Class clz = Class.forName(bean);Field[] strs = clz.getDeclaredFields();List<String> propertyList = new ArrayList<String>();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<String> 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+")";}}

关于Java反射 JavaBean对象自动生成插入,更新,删除,查询sql语句操作就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。