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)
自定义方法,用于将表名或字段名转换为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中出现过。
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |