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: 2436846019@qq.com
* @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: 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;
}
}
}
辅助阅读
去表名前缀，如tb_test-->test
beanName = tableName.substring(beanName.indexOf("_"+1;
indexOf("_"定位第一次出现下划线的索引位置，substring截取后面的字符串。
processFiled(String,Boolean
processFiled(String,Boolean中的String[] fields=field.split("_"
xx.split("_"是将xx字符串按照下划线进行分割。
processFiled(String,Boolean中的append(
processJavaType(String
自定义方法，用于做数据库字段类型与Java属性类型之间的匹配。
processJavaType(String中的ArrayUtils.contains(A,B
判断B是否在A中出现过。