1、依赖包
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.5</version>
</dependency>
2、生成insert
Users users = new Users();
users.setId(1);
users.setName("tqf");
users.setSex("男");
String sql = getInsertSql("users.users",Users.class,users);
System.out.println(sql);
3、生成update
Users users = new Users();
users.setId(1);
users.setName("tqf");
users.setSex("男");
String sql_update = getUpdateSql("users.users",Users.class,users);
System.out.println(sql_update);
4、生成select
Users users = new Users();
users.setId(1);
users.setName("tqf");
users.setSex("男");
String sql_select = getSelectSql("users.users",users);
System.out.println(sql_select);
5、生成delete
Users users = new Users();
users.setId(1);
users.setName("tqf");
users.setSex("男");
String sql_delete = getDeleteSql("users.users",users);
System.out.println(sql_delete);
6、工具类
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.shucha.deveiface.biz.model.Users;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlSentence {
public static void main(String[] args) {
Users users = new Users();
users.setId(1);
users.setName("tqf");
users.setSex("男");
String sql = getInsertSql("users.users",Users.class,users);
System.out.println(sql);
String sql_update = getUpdateSql("users.users",Users.class,users);
System.out.println(sql_update);
String sql_select = getSelectSql("users.users",users);
System.out.println(sql_select);
String sql_delete = getDeleteSql("users.users",users);
System.out.println(sql_delete);
}
public static <T> String getInsertSql(String tablename, Class<T> clazz, T t){
String sql = "";
Field[] fields = ReflectUtil.getFieldsDirectly(clazz, 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;
}
topHalf.append(field.getName() + ",");
if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
} else {
afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
}
}
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;
}
public static <T> String getUpdateSql(String tablename, Class<T> clazz, T t){
String sql = "";
String id = "";
Field[] fields = ReflectUtil.getFieldsDirectly(clazz, 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;
}
if (ReflectUtil.getFieldValue(t, field.getName()) != null && ReflectUtil.getFieldValue(t, field.getName()) != "") {
tmp.append( field.getName() + "=");
if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
tmp.append( "'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
} else {
tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
}
sql += tmp;
}
}
sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id)+"'";
return sql;
}
public static <T> String getDeleteSql(String tablename, T t) throws IllegalArgumentException {
return getSelectOrDeleteSql(tablename, t, "delete");
}
public static <T> String getSelectSql(String tablename, T t) throws IllegalArgumentException {
return getSelectOrDeleteSql(tablename, t, "select *");
}
private static <T> String getSelectOrDeleteSql(String tablename, T t, String operation) throws IllegalArgumentException {
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) {
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;
}
/*获取你的数据库连接*
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select COLUMN_NAME,COLUMN_TYPE from information_schema.COLUMNS where table_name = '"+tablename+"' and table_schema = '"+dbname+"'");
StringBuffer sb=new StringBuffer();
while (rs.next()) {
sb.append("private Object "+rs.getObject(1)+";\n");
}
System.out.print(sb.toString());
rs.close();
stat.close();
conn.close();
return sb.toString();
}*/
}