Java读取数据库表（二） - IT评测·应用市场-qidao123.com

package com.easycrud.builder;
import com.easycrud.bean.Constants;
import com.easycrud.bean.FieldInfo;
import com.easycrud.bean.TableInfo;
import com.easycrud.utils.JsonUtils;
import com.easycrud.utils.PropertiesUtils;
import com.easycrud.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @BelongsProject: EasyCrud
* @BelongsPackage: com.easycrud.builder
* @Author: xpx
* @Email: 2436846019@qq.com
* @CreateTime: 2023-05-02 18:02
* @Description: 读Table
* @Version: 1.0
*/
public class BuildTable {
private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
private static Connection conn = null;
/**
* 查表信息，表名，表注释等
*/
private static String SQL_SHOW_TABLE_STATUS = "show table status";
/**
* 将表结构当作表读出字段的信息，如字段名(field),类型(type),自增(extra)...
*/
private static String SQL_SHOW_TABLE_FIELDS = "show full fields from %s";
/**
* 检索索引
*/
private static String SQL_SHOW_TABLE_INDEX = "show index from %s";
/**
* 读配置,连接数据库
*/
static {
String driverName = PropertiesUtils.getString("db.driver.name");
String url = PropertiesUtils.getString("db.url");
String user = PropertiesUtils.getString("db.username");
String password = PropertiesUtils.getString("db.password");
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,password);
} catch (Exception e) {
logger.error("数据库连接失败",e);
}
}
/**
* 读取表
*/
public static List<TableInfo> getTables() {
PreparedStatement ps = null;
ResultSet tableResult = null;
List<TableInfo> tableInfoList = new ArrayList();
try{
ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
tableResult = ps.executeQuery();
while(tableResult.next()) {
String tableName = tableResult.getString("name");
String comment = tableResult.getString("comment");
//logger.info("tableName:{},comment:{}",tableName,comment);
String beanName = tableName;
/**
* 去xx_前缀
*/
if (Constants.IGNORE_TABLE_PREFIX) {
beanName = tableName.substring(beanName.indexOf("_")+1);
}
beanName = processFiled(beanName,true);
// logger.info("bean:{}",beanName);
TableInfo tableInfo = new TableInfo();
tableInfo.setTableName(tableName);
tableInfo.setBeanName(beanName);
tableInfo.setComment(comment);
tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_PARAM);
/**
* 读字段信息
*/
readFieldInfo(tableInfo);
/**
* 读索引
*/
getKeyIndexInfo(tableInfo);
// logger.info("tableInfo:{}",JsonUtils.convertObj2Json(tableInfo));
tableInfoList.add(tableInfo);
// logger.info("表名：{},备注：{}，JavaBean:{},JavaParamBean:{}",tableInfo.getTableName(),tableInfo.getComment(),tableInfo.getBeanName(),tableInfo.getBeanParamName());
}
logger.info("tableInfoList:{}",JsonUtils.convertObj2Json(tableInfoList));
}catch (Exception e){
logger.error("读取表失败",e);
}finally {
if (tableResult != null) {
try {
tableResult.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return tableInfoList;
}
/**
* 将表结构当作表读出字段的信息，如字段名(field),类型(type),自增(extra)...
* @param tableInfo
* @return
*/
private static void readFieldInfo(TableInfo tableInfo) {
PreparedStatement ps = null;
ResultSet fieldResult = null;
List<FieldInfo> fieldInfoList = new ArrayList();
try{
ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableInfo.getTableName()));
fieldResult = ps.executeQuery();
while(fieldResult.next()) {
String field = fieldResult.getString("field");
String type = fieldResult.getString("type");
String extra = fieldResult.getString("extra");
String comment = fieldResult.getString("comment");
/**
* 类型例如varchar(50)我们只需要得到varchar
*/
if (type.indexOf("(") > 0) {
type = type.substring(0, type.indexOf("("));
}
/**
* 将aa_bb变为aaBb
*/
String propertyName = processFiled(field, false);
// logger.info("f:{},p:{},t:{},e:{},c:{},",field,propertyName,type,extra,comment);
FieldInfo fieldInfo = new FieldInfo();
fieldInfoList.add(fieldInfo);
fieldInfo.setFieldName(field);
fieldInfo.setComment(comment);
fieldInfo.setSqlType(type);
fieldInfo.setAutoIncrement("auto_increment".equals(extra) ? true : false);
fieldInfo.setPropertyName(propertyName);
fieldInfo.setJavaType(processJavaType(type));
// logger.info("JavaType:{}",fieldInfo.getJavaType());
if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, type)) {
tableInfo.setHaveDataTime(true);
}else {
tableInfo.setHaveDataTime(false);
}
if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
tableInfo.setHaveData(true);
}else {
tableInfo.setHaveData(false);
}
if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, type)) {
tableInfo.setHaveBigDecimal(true);
}else {
tableInfo.setHaveBigDecimal(false);
}
}
tableInfo.setFieldList(fieldInfoList);
}catch (Exception e){
logger.error("读取表失败",e);
}finally {
if (fieldResult != null) {
try {
fieldResult.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 检索唯一索引
* @param tableInfo
* @return
*/
private static List<FieldInfo> getKeyIndexInfo(TableInfo tableInfo) {
PreparedStatement ps = null;
ResultSet fieldResult = null;
List<FieldInfo> fieldInfoList = new ArrayList();
try{
/**
* 缓存Map
*/
Map<String,FieldInfo> tempMap = new HashMap();
/**
* 遍历表中字段
*/
for (FieldInfo fieldInfo : tableInfo.getFieldList()) {
tempMap.put(fieldInfo.getFieldName(),fieldInfo);
}
ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableInfo.getTableName()));
fieldResult = ps.executeQuery();
while(fieldResult.next()) {
String keyName = fieldResult.getString("key_name");
Integer nonUnique = fieldResult.getInt("non_unique");
String columnName = fieldResult.getString("column_name");
/**
* 0是唯一索引，1不唯一
*/
if (nonUnique == 1) {
continue;
}
List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
if (null == keyFieldList) {
keyFieldList = new ArrayList();
tableInfo.getKeyIndexMap().put(keyName,keyFieldList);
}
keyFieldList.add(tempMap.get(columnName));
}
}catch (Exception e){
logger.error("读取索引失败",e);
}finally {
if (fieldResult != null) {
try {
fieldResult.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return fieldInfoList;
}
/**
* aa_bb__cc==>AaBbCc || aa_bb_cc==>aaBbCc
* @param field
* @param uperCaseFirstLetter，首字母是否大写
* @return
*/
private static String processFiled(String field,Boolean uperCaseFirstLetter) {
StringBuffer sb = new StringBuffer();
String[] fields=field.split("_");
sb.append(uperCaseFirstLetter ? StringUtils.uperCaseFirstLetter(fields[0]):fields[0]);
for (int i = 1,len = fields.length; i < len; i++){
sb.append(StringUtils.uperCaseFirstLetter(fields[i]));
}
return sb.toString();
}
/**
* 为数据库字段类型匹配对应Java属性类型
* @param type
* @return
*/
private static String processJavaType(String type) {
if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPE,type)) {
return "Integer";
}else if (ArrayUtils.contains(Constants.SQL_LONG_TYPE,type)) {
return "Long";
}else if (ArrayUtils.contains(Constants.SQL_STRING_TYPE,type)) {
return "String";
}else if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)) {
return "Date";
}else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)) {
return "BigDecimal";
}else {
throw new RuntimeException("无法识别的类型:"+type);
}
}
}

复制代码