@Override
public String buildQuerySql(Class<?> entityClass, Connection conn) throws SQLException {
if (!SqlCache.VOSQL.containsKey(entityClass)) {
String orderBy = "";
AnnotationUtil annotationUtil = AnnotationUtil.getInstance();
// 获取表名
String tableName = annotationUtil.getAnnotationTableName(entityClass);
List<ColumnMeta> list = DaoOptemplate.getInstance().refresh(tableName, conn);
StringBuilder sb = new StringBuilder("select ");
for (int i = 0; i < list.size(); i++) {
ColumnMeta cm = list.get(i);
String columnName = cm.getColumnName();
sb.append(columnName + ",");
// 如果是自动增长,或者是日期,则设置自动降序排列
if (orderBy.equalsIgnoreCase("")) {
if (cm.isAutoIncrement() || cm.getColumnType().indexOf("date") != -1) {
orderBy = " order by " + columnName + " desc";
}
}
}
sb.deleteCharAt(sb.lastIndexOf(","));
sb.append(" from " + tableName);
sb.append(SqlConstant.ORDERBY_SPLIT + orderBy);
SqlCache.VOSQL.put(entityClass, sb.toString());
}
return SqlCache.VOSQL.get(entityClass);
}
@Override
public String buildPrimaryConditionSql(Object entity) throws SQLException {
AnnotationUtil annotationUtil = AnnotationUtil.getInstance();
Map<String, Object> primary = annotationUtil.getPrimaryValue(entity);
String sql = "";
Map<String, PropertyDescriptor> beanMap = null;
try {
beanMap = annotationUtil.getBeanInfo(entity.getClass());
} catch (IntrospectionException e1) {
throw new SQLException("查询失败:" + e1.getMessage());
}
Set<String> primaryNames = primary.keySet();
String joinWord = " where ";
for (Iterator<String> it = primaryNames.iterator(); it.hasNext(); ) {
String primaryName = it.next();
if (primary.get(primaryName) == null) {
continue;
}
if (sql.indexOf("where") != -1) {
joinWord = " and ";
}
// 获取主键的数据类型
String typeName = beanMap.get(primaryName).getPropertyType().getSimpleName().toLowerCase();
// 若为字符类型
sql += joinWord;
if (typeName.equals("string")) {
sql += primaryName + "='" + primary.get(primaryName) + "'";
} else {
sql += primaryName + "=" + primary.get(primaryName);
}
}
return sql;
}
@Override
public String buildUpdateSql(Object entity, Connection conn) throws SQLException {
AnnotationUtil annotationUtil = AnnotationUtil.getInstance();
// 获取表名
String tableName = annotationUtil.getAnnotationTableName(entity.getClass());
// 获取主键名
String[] primaryKeyNames = annotationUtil.getPrimaryKey(entity.getClass());
// 获取实体类存放的属性名和值
Map<String, Object> beanMap = annotationUtil.getBeanInfo(entity);
/**
* select * from tablename where 1=0的意义 此语句的作用是: 打开此记录集,但并不从记录集中读取任何记录
* 直观点说就是,为保护记录集数据,仅做打开,是只做插入记录时用到
*/
List<ColumnMeta> list = DaoOptemplate.getInstance().refresh(tableName, conn);
int columnCount = list.size();
StringBuffer valueBuffer = new StringBuffer("update " + tableName + " set ");
String whereCondition = "";
/** 此处用于判断实体的存在性 若存在则SQL语句组装完成，将where条件中的id补入 */
String word = " where ";
boolean flag = false;
for (int i = 0; i < columnCount; i++) {
ColumnMeta cm = list.get(i);
String columnName = cm.getColumnName();
String columnType = cm.getColumnType();
Object columnValue = beanMap.get(columnName);
if (columnValue == null) {
continue;
}
if (isPrimaryKey(columnName, primaryKeyNames)) {
whereCondition += word + getConditionSyntax(columnName, columnType, columnValue);
if (primaryKeyNames.length > 1) {
word = " and ";
}
} else {
if (columnValue != null) {
flag = true;
// 若为最后一个
if (i == columnCount) {
valueBuffer.append(getConditionSyntax(columnName, columnType, columnValue));
} else {
valueBuffer.append(getConditionSyntax(columnName, columnType, columnValue) + ",");
}
}
}
}
if (!flag) {
return null;
}
// 获取最后一个逗号到总长度的大小
int quoteApartLength = valueBuffer.length() - valueBuffer.toString().lastIndexOf(",");
if (quoteApartLength == 1) {
valueBuffer.deleteCharAt(valueBuffer.toString().lastIndexOf(","));
}
String sql = valueBuffer.toString() + whereCondition;
return sql;
}
@Override
public String buildDeleteSql(Object entity, Connection conn) throws SQLException, Exception {
AnnotationUtil annotationUtil = AnnotationUtil.getInstance();
// 获取表名
String tableName = annotationUtil.getAnnotationTableName(entity.getClass());
// 获取主键名
String[] primaryKeyNames = annotationUtil.getPrimaryKey(entity.getClass());
// 获取实体类存放的属性名和值
Map<String, Object> beanMap = annotationUtil.getBeanInfo(entity);
String sql = "delete from " + tableName;
String whereCondition = "";
boolean flag = false;
/** 此处用于判断实体的存在性 若存在则SQL语句组装完成，将where条件中的id补入 */
String word = " where ";
for (String columnName : primaryKeyNames) {
String columnType = entity.getClass().getDeclaredField(columnName).getType().getSimpleName();
Object columnValue = beanMap.get(columnName);
if (columnValue == null) {
flag = true;
continue;
}
whereCondition += word + getConditionSyntax(columnName, columnType, columnValue);
if (primaryKeyNames.length > 1) {
word = " and ";
}
}
// 如果主键的值为null，则通过普通条件查询
if (flag) {
List<ColumnMeta> list = DaoOptemplate.getInstance().refresh(tableName, conn);
int columnCount = list.size();
for (int i = 0; i < columnCount; i++) {
ColumnMeta cm = list.get(i);
String columnName = cm.getColumnName();
if (isPrimaryKey(columnName, primaryKeyNames)) {
continue;
}
String columnType = cm.getColumnType();
Object columnValue = beanMap.get(columnName);
if (columnValue == null) {
continue;
}
whereCondition += word + getConditionSyntax(columnName, columnType, columnValue);
if (whereCondition.contains("where")) {
word = " and ";
}
}
}
if (!whereCondition.contains("where")) {
throw new SQLException("查询的主键的值不能为空！");
}
sql += whereCondition;
return sql;
}
@Override
public String buildConditionSql(
Class<?> entityClass,
int firstIndex,
int maxResult,
Map<String, String> OrderBy,
String where_sql,
String whereValue,
boolean flag)
throws SQLException {
String sql = null;
AnnotationUtil annotationUtil = AnnotationUtil.getInstance();
Map<String, PropertyDescriptor> beanMap = null;
try {
beanMap = annotationUtil.getBeanInfo(entityClass);
} catch (IntrospectionException e1) {
throw new SQLException("查询失败：" + e1.getMessage());
}
// "select * from " + tableName
// 拼接查询sql语句
StringBuffer sqlBuffer = new StringBuffer();
// 如果有条件查询
if (where_sql != null) {
sqlBuffer.append(" where ");
String[] splitWhereSql = where_sql.split(SqlConstant.CONDITION_SPLIT);
String[] splitWhereValue = whereValue.split(SqlConstant.CONDITION_SPLIT);
// 如果支持模糊查询
if (flag) {
for (int i = 0; i < splitWhereSql.length; i++) {
// 获取此参数的类型名
String typeName =
beanMap.get(splitWhereSql[i]).getPropertyType().getSimpleName().toLowerCase();
if (typeName.equals("string") || typeName.indexOf("date") != -1) {
sqlBuffer
.append(splitWhereSql[i] + " like '%" + splitWhereValue[i] + "%'")
.append(" and ");
} else {
sqlBuffer.append(splitWhereSql[i] + "=" + splitWhereValue[i]).append(" and ");
}
}
} else {
for (int i = 0; i < splitWhereSql.length; i++) {
// 获取此参数的类型名
String typeName =
beanMap.get(splitWhereSql[i]).getPropertyType().getSimpleName().toLowerCase();
if (typeName.equals("string") || typeName.indexOf("date") != -1) {
sqlBuffer.append(splitWhereSql[i] + "='" + splitWhereValue[i] + "'").append(" and ");
} else {
sqlBuffer.append(splitWhereSql[i] + "=" + splitWhereValue[i]).append(" and ");
}
}
}
// 将多余的and删除
sqlBuffer.delete(sqlBuffer.lastIndexOf("and"), sqlBuffer.length());
}
// 设置按主键降序排序
// sqlBuffer.append(" order by " + primaryKeyName + " desc ");
/*
* if (firstIndex == 0 && maxResult == 0) {
* sqlBuffer.append(" order by " + primaryKeyName + " desc "); } else {
* sqlBuffer.append(" order by " + primaryKeyName + " desc limit " +
* firstIndex + "," + maxResult); }
*/
sql = sqlBuffer.toString();
return sql;
}