All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.tmsps.ne4spring.orm.MySQLUtil Maven / Gradle / Ivy

Go to download

NE For Spring is good :)

There is a newer version: 999.0.0.0
Show newest version
package com.tmsps.ne4spring.orm;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tmsps.ne4spring.annotation.Column;
import com.tmsps.ne4spring.annotation.PK;
import com.tmsps.ne4spring.annotation.UpdateSkip;
import com.tmsps.ne4spring.orm.model.DataModel;
import com.tmsps.ne4spring.utils.StrUtil;

/**
 * @author zhangwei [email protected]
 */
public class MySQLUtil {
	static Logger log = LoggerFactory.getLogger(MySQLUtil.class);

	public static String getInsSQL(final Class clazz) {
		int i = 0;
		StringBuilder sb = new StringBuilder("insert into").append(StringUtils.SPACE);
		// 获取表名称
		sb.append(ClassUtil.getTableName(clazz)).append("(");
		// 获取class中需要映射的属性名
		List names = ClassUtil.getPropertyName(clazz);
		for (i = 0; i < names.size() - 1; i++) {
			// 生成映射的insert 语句
			sb.append('`').append(names.get(i)).append("`,");
		}
		sb.append('`').append(names.get(names.size() - 1)).append('`');
		sb.append(") values (");
		for (i = 0; i < names.size() - 1; i++) {
			// 生成映射的占位符
			sb.append("? , ");
		}
		sb.append("?)");
		log.debug(sb.toString());
		return sb.toString();
	}
	
	public static String getTemplateInsSQL(final String tableName,final List propertys) {
		StringBuilder sb = new StringBuilder("insert into ").append(tableName).append("(");
		
		// 需要映射的属性名
		propertys.forEach(name -> {
			sb.append("`").append(name).append("`,");
		});
		sb.deleteCharAt(sb.length() - 1);
		sb.append(") values (");
		for (int i = 0; i < propertys.size() - 1; i++) {
			sb.append("?").append(",");
		}
		sb.append("?)");

		log.debug(sb.toString());
		return sb.toString();
	}

	public static String getUpdateSQL(final Class clazz) {
		StringBuilder sb = new StringBuilder("update").append(StringUtils.SPACE);
		// 获取表名称
		sb.append(ClassUtil.getClassName(clazz)).append(StringUtils.SPACE).append("set").append(StringUtils.SPACE);
		// 获取字段名称
		List names = ClassUtil.getClassFields(clazz);
		for (Field name : names) {
			if (name.isAnnotationPresent((Class) PK.class)) {
				continue;
			}
			// 如果注解为UpdateSkip，则该字段不进入更新语句
			if (name.isAnnotationPresent((Class) UpdateSkip.class)) {
				continue;
			}
			// 判断是否含有Column注解
			if (name.isAnnotationPresent((Class) Column.class)) {
				// 如果注解中的updateable为false，则该字段不进入更新语句
				if (!name.getAnnotation(Column.class).updateable()) {
					continue;
				} else {
					sb.append('`').append(name.getAnnotation(Column.class).name()).append("`= ?,");
					continue;
				}
			}
			sb.append('`').append(name.getName()).append("`= ?,");
		}
		// 去掉 最后的逗号
		sb.deleteCharAt(sb.length() - 1);
		sb.append(StringUtils.SPACE).append("where").append(StringUtils.SPACE);
		sb.append(ClassUtil.getIdField(clazz).getName()).append(StringUtils.SPACE).append("= ?");
		log.debug(sb.toString());
		return sb.toString();
	}

	/**
	 * 生成变动的update SQL 语句，方便处理变动的字段，不变动的字段不处理，节省工作量
	 * @author zhangwei 2015年10月12日 下午7:24:09
	 */
	public static String getChangeUpdateSQL(final Object obj) {
		StringBuilder sb = new StringBuilder("update ");
		String idFileld = ClassUtil.getIdField(obj.getClass()).getName();
		// 获取表名称
		sb.append(ClassUtil.getClassName(obj.getClass())).append(" set ");
		Map beanKeyVals = ClassUtil.getClassKeyValNotNull(obj);
		for (Map.Entry entry : beanKeyVals.entrySet()) {
			if (idFileld.equals(entry.getKey())) {
				continue;
			}
			sb.append('`').append(entry.getKey() + "`= ?,");
		}
		// 去掉 最后的逗号
		sb.deleteCharAt(sb.length() - 1);
		sb.append(" where ");
		sb.append(ClassUtil.getIdField(obj.getClass()).getName()).append(" = ?");
		log.debug(sb.toString());
		return sb.toString();
	}

	public static String getDelRealSQL(final Class clazz) {
		StringBuilder sb = new StringBuilder("delete from  ");
		// 获取表名称
		sb.append(ClassUtil.getClassName(clazz)).append(" where ");
		Field id = ClassUtil.getIdField(clazz);
		sb.append(id.getName()).append(" = ?");
		log.debug(sb.toString());
		return sb.toString();
	}

	public static String getDelRealSQL(final String tableName, final String pkColumn) {
		StringBuilder sb = new StringBuilder("delete from ");
		// 获取表名称
		sb.append(tableName).append(" where ");
		sb.append(pkColumn).append(" = ?");
		log.debug(sb.toString());
		return sb.toString();
	}

	public static String getSelectSQL(final Class clazz, boolean status) {
		StringBuilder sb = new StringBuilder("select * from  ");
		// 获取表名
		sb.append(ClassUtil.getClassName(clazz)).append(" where ");
		Field id = ClassUtil.getIdField(clazz);
		sb.append(id.getName()).append(" = ?");
		if (status) {// 是否需要标识位 status
			sb.append(" and status = 0");
		}
		log.debug(sb.toString());
		return sb.toString();
	}

	public static String getSelectByIdSQL(String tableName, String pkName) {
		if (StrUtil.isBlank(pkName)) {// 主键为空则使用约定主键 _id
			return getSelectByIdSQL(tableName);
		} else if (StrUtil.notBlank(tableName)) {
			StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" t");
			sb.append(" where t.").append(pkName).append(" = ?");
			log.debug(sb.toString());
			return sb.toString();
		} else {
			log.error("tableName and pkName is not null !!!");
		}
		return null;
	}

	public static String getSelectByIdSQL(String tableName) {
		if (StrUtil.notBlank(tableName)) {
			StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" t");
			sb.append(" where t._id = ?");
			log.debug(sb.toString());
			return sb.toString();
		} else {
			log.error("tableName is not null !!!");
		}
		return null;
	}

	public static String getCntSql(String sql) {
		if (sql == null) {
			return null;
		}
		StringBuilder sizeSql = new StringBuilder("SELECT COUNT(*) ");
		sizeSql.append(sql.substring(sql.toUpperCase().lastIndexOf("FROM")));
		log.debug(sizeSql.toString());
		return sizeSql.toString();
	}

	public static  T fillPojoByResultSet(ResultSet rs, final Class clazz) {
		try {
			T obj = (T) clazz.getDeclaredConstructor().newInstance();
			List fs = ClassUtil.getClassFields(clazz);
			for (Field field : fs) {
				Object val = rs.getObject(field.getName());
				ClassUtil.setClassVal(field, obj, val);
			}
			return obj;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static String getPageSQL(String select, String sqlExceptSelect) {
		StringBuilder sb = new StringBuilder(select);
		sb.append(" ").append(sqlExceptSelect);
		sb.append(" ").append("LIMIT ?,?");
		return sb.toString();
	}
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy