Java讀取資料庫表（二） application.properties db.driver.name=com.mysql.cj.jdbc.Driver db.url=jdbc:mysql://localhost:3306/easycrud?useUnicode=true&characterEnco ...
Java讀取資料庫表（二）
application.properties
db.driver.name=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/easycrud?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
db.username=root
db.password=xpx24167830
#是否忽略表首碼
ignore.table.prefix=true
#參數bean尾碼
suffix.bean.param=Query
輔助閱讀
配置文件中部分信息被讀取到之前文檔說到的Constants.java中以常量的形式存儲，BuildTable.java中會用到，常量命名和上面類似。
StringUtils.java
package com.easycrud.utils;
/**
* @BelongsProject: EasyCrud
* @BelongsPackage: com.easycrud.utils
* @Author: xpx
* @Email: [email protected]
* @CreateTime: 2023-05-03 13:30
* @Description: 字元串大小寫轉換工具類
* @Version: 1.0
*/
public class StringUtils {
/**
* 首字母轉大寫
* @param field
* @return
*/
public static String uperCaseFirstLetter(String field) {
if (org.apache.commons.lang3.StringUtils.isEmpty(field)) {
return field;
}
return field.substring(0, 1).toUpperCase() + field.substring(1);
}
/**
* 首字母轉小寫
* @param field
* @return
*/
public static String lowerCaseFirstLetter(String field) {
if (org.apache.commons.lang3.StringUtils.isEmpty(field)) {
return field;
}
return field.substring(0, 1).toLowerCase() + field.substring(1);
}
/**
* 測試
* @param args
*/
public static void main(String[] args) {
System.out.println(lowerCaseFirstLetter("Abcdef"));
System.out.println(uperCaseFirstLetter("abcdef"));
}
}
輔助閱讀
org.apache.commons.lang3.StringUtils.isEmpty()
只能判斷String類型是否為空（org.springframework.util包下的Empty可判斷其他類型），源碼如下
public static boolean isEmpty(final CharSequence cs) {
return cs == null || cs.length() == 0;
}
xx.toUpperCase()
字母轉大寫
xx.toLowerCase()
字母轉小寫
xx.substring()
返回字元串的子字元串
索引從0開始
public String substring(int beginIndex) //起始索引，閉
public String substring(int beginIndex, int endIndex) //起始索引到結束索引，左閉右開
BuildTable.java完整代碼
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: [email protected]
* @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);
}
}
}
輔助閱讀
去表名首碼，如tb_test-->test
beanName = tableName.substring(beanName.indexOf("_")+1);
indexOf("_")定位第一次出現下劃線的索引位置，substring截取後面的字元串。
processFiled(String,Boolean)
自定義方法，用於將表名或欄位名轉換為Java中的類名或屬性名，如aa_bb__cc-->AaBbCc || aa_bb_cc-->aaBbCc
processFiled(String,Boolean)中的String[] fields=field.split("_")
xx.split("_")是將xx字元串按照下劃線進行分割。
processFiled(String,Boolean)中的append()
StringBuffer類包含append()方法，相當於“+”，將指定的字元串追加到此字元序列。
processJavaType(String)
自定義方法，用於做資料庫欄位類型與Java屬性類型之間的匹配。
processJavaType(String)中的ArrayUtils.contains(A,B)
判斷B是否在A中出現過。
