sql 转java_简单数据访问类，生成简单SQL，自动转换成java对象

importjava.lang.reflect.Field;importjava.util.Collection;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;public classSqlTemplate {private static Logger logger = LoggerFactory.getLogger(SqlTemplate.class);public final static String TABLE_PREFIX = "t";private String tableName = "";/*** 如果使用多Schema模式可以使用此参数*/

private String schemaPrefix = "";/*** 实体类对应的字段名*/

privateString[] fieldNames;/*** 数据库表对应的字段名称*/

privateString[] dbFieldNames;/*** 构造函数,解析类名,字段名,生成对应数据库中的表名和字段名

*@paramclazz*/

public SqlTemplate(Class>clazz) {//获取有get方法的字段

Field[] fields =ObjectUtil.getObjectFields(clazz);int fieldsLength =fields.length;

fieldNames= newString[fieldsLength];

dbFieldNames= newString[fieldsLength];for (int i = 0; i < fieldsLength; i++) {

Field f=fields[i];

String fieldName=f.getName();

fieldNames[i]=fieldName;

dbFieldNames[i]=StringUtil.camelToUnderline(fieldName);

}

String clazzName=clazz.getSimpleName();//数据库表名

tableName = schemaPrefix + StringUtil.camelToUnderline(TABLE_PREFIX +clazzName);

}publicString getInsertSQL() {//拼SQL语句

StringBuffer sql = newStringBuffer();

sql.append("INSERT INTO ");

sql.append("" + tableName + "");

sql.append(" (");for (int i = 0; i < dbFieldNames.length; i++) {

sql.append("" + dbFieldNames[i] + "");if (i < dbFieldNames.length - 1) {

sql.append(",");

}

}

sql.append(") ");

sql.append(" VALUES(");for (int i = 0; i < fieldNames.length; i++) {

String fieldName=fieldNames[i];

sql.append(":" +fieldName);if (i < fieldNames.length - 1) {

sql.append(",");

}

}

sql.append(") ");returnsql.toString();

}publicString getUpdateSQL() {//拼SQL语句

StringBuffer sql = newStringBuffer();

sql.append("UPDATE ");

sql.append("" + tableName + "");

sql.append(" SET ");for (int i = 1; i < dbFieldNames.length; i++) {

String dbFieldName=dbFieldNames[i];

String fieldName=fieldNames[i];

sql.append(dbFieldName);

sql.append("=:" +fieldName);if (i < dbFieldNames.length - 1) {

sql.append(",");

}

}

sql.append(" WHERE ");

sql.append(" id ");

sql.append("=:id");returnsql.toString();

}public String getCountSQL(Collectionwhere){

String[] whereArray=toStringArray(where);returngetCountSQL(whereArray);

}publicString getCountSQL(String[] where){

StringBuffer sql= newStringBuffer();

sql.append("SELECT count(0) FROM ");

sql.append("" + tableName + "");

sql.append(toWhereSQL(where));returnsql.toString();

}publicString getSelectSQL() {//拼SQL语句

StringBuffer sql = newStringBuffer();

sql.append("SELECT * FROM ");

sql.append("" + tableName + "");returnsql.toString();

}public String getSelectSQL(Collectionwhere) {if (where != null && !where.isEmpty()) {

String[] whereArray=toStringArray(where);returngetSelectSQL(whereArray);

}else{returngetSelectSQL();

}

}publicString getSelectSQL(String[] where) {//拼SQL语句

StringBuffer sql = newStringBuffer();

sql.append("SELECT * FROM ");

sql.append("" + tableName + "");//如果有where条件

sql.append(toWhereSQL(where));returnsql.toString();

}publicString getDeleteSQL() {//拼SQL语句

StringBuffer sql = newStringBuffer();

sql.append("DELETE FROM ");

sql.append("" + tableName + "");

sql.append(" WHERE ");

sql.append(" id ");

sql.append("=:id");returnsql.toString();

}publicString getDeleteSQL(String[] where) {//拼SQL语句

StringBuffer sql = newStringBuffer();

sql.append("DELETE FROM ");

sql.append("" + tableName + "");if (where != null && where.length > 0) {

sql.append(toWhereSQL(where));

}else{

sql.append(" WHERE ");

sql.append(" id ");

sql.append("=:id");

}returnsql.toString();

}privateString getDbFieldName(String fieldName) {for (int i = 0; i < fieldNames.length; i++) {

String fName=fieldNames[i];if(fieldName.equals(fName)) {returndbFieldNames[i];

}

}return null;

}publicString toWhereSQL(String[] where) {

StringBuffer sql= newStringBuffer();if (where != null && where.length > 0) {

sql.append(" WHERE ");for (int i = 0; i < where.length; i++) {

String w=where[i];

String dbFieldName=getDbFieldName(w);if (dbFieldName == null) {

logger.error("can not get the dbFieldName of {}", w);return null;

}

sql.append(" " + dbFieldName + " ");

sql.append("=:" +w);if (i < where.length - 1) {

sql.append(" and ");

}

}

}returnsql.toString();

}private String[] toStringArray(Collectionwhere) {if (where != null && !where.isEmpty()) {

String[] whereArray= newString[where.size()];int i = 0;for(String s : where) {

whereArray[i]=s;

i++;

}returnwhereArray;

}return null;

}public voidsetSchemaPrefix(String schemaPrefix) {this.schemaPrefix =schemaPrefix;

}

}