Java读取数据库表（二）
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();
Boolean haveDateTime = false;
Boolean haveDate = false;
Boolean haveBigDecimal = false;
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)) {
haveDateTime = true;
}
if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
haveDate = true;
}
if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, type)) {
haveBigDecimal = true;
}
}
tableInfo.setHaveDataTime(haveDateTime);
tableInfo.setHaveData(haveDate);
tableInfo.setHaveBigDecimal(haveBigDecimal);
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中出现过。
