自己学习使用的工具类
import com.google.common.base.CaseFormat;
import java.lang.reflect.Field;
/**
* @author JQC
* @date 2021/12/24 9:03
*/
public class SqlBuilder {
/**
* 生成插入语句
*
* @param tablename 表名
* @param clazz 与数据库中字段一一对应的类
* @param t 有数据的实体
* @param <T> 数据实体类型 如 User
*/
public static <T> String getInsertSql(String tablename, Class<T> clazz, T t) throws Exception {
String sql = "";
Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
StringBuffer topHalf = new StringBuffer("insert into " + tablename + " (");
StringBuffer afterAalf = new StringBuffer("values (");
for (Field field : fields) {
String fieldName = field.getName();
Object fieldValue = ReflectUtil.getFieldValue(t, fieldName);
topHalf.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName) + ",");
if (fieldValue instanceof String) {
afterAalf.append("'" + fieldValue + "',");
} else {
afterAalf.append(fieldValue + ",");
}
}
topHalf.deleteCharAt(topHalf.lastIndexOf(","));
afterAalf.deleteCharAt(afterAalf.lastIndexOf(","));
topHalf.append(") ");
afterAalf.append(") ");
sql = topHalf + afterAalf.toString();
return sql;
}
/**
* 生成更新语句
* 必须含有id
* 数据实体中 null 与 空字段不参与更新
*
* @param tablename 数据库中的表明
* @param clazz 与数据库中字段一一对应的类
* @param t 有数据的实体
* @param <T> 数据实体类型,如 User
*/
public static <T> String getUpdateSql(String tablename, Class<T> clazz, T t) throws Exception {
//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
String sql = "";
String id = ""; //保存id名:ID or id
Field[] fields = ReflectUtil.getFieldsDirectly(clazz, true);
sql = "update " + tablename + " set ";
for (Field field : fields) {
StringBuffer tmp = new StringBuffer();
String fieldName = field.getName();
if ("ID".equals(fieldName) || "id".equals(fieldName)) {
id = fieldName;
continue;//更新的时候无需set id=xxx
}
Object value = ReflectUtil.getFieldValue(t, fieldName);
if (value != null && value != "") {
tmp.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName) + "=");
if (value instanceof String) {
tmp.append("'" + value + "',");
} else {
tmp.append(value + ",");
}
sql += tmp;
}
}
sql = new StringBuilder(sql).deleteCharAt(sql.lastIndexOf(",")) + " 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 Exception {
//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 Exception {
//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 Exception {
//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) {
Object fieldValue = ReflectUtil.getFieldValue(t, field.getName());
String fieldName = field.getName();
if ("ID".equals(fieldName) || "id".equals(fieldName)) {
if (fieldValue != null && (int) fieldValue != 0) {
//id 不为空
topHalf.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName) + " = " + fieldValue);
flag = true;
break;
}
} else {
if (fieldValue != null && (String) fieldValue != "") {
topHalf.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName) + " = '" + fieldValue + "'");
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;
}
}