java把excel表的数据导入到mysql数据库

package com.cn.wjq.util; import java.io.FileOutputStream; import java.lang.annotation.Annotation; import java.lang.reflect.Field; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.cn.wjq.model.BeanInfo; import com.cn.wjq.model.ClassType; import com.cn.wjq.model.ColumnInfo; public class Generator { private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class); private static final String SQL = "SELECT * FROM "; public static final String packagePath = "com.cn.wjq.beam"; // 生成文件路径 public static final String nextLine = "\r\n"; public static final String tab = "\t"; public static final String towtab = "\t\t"; public static final String ent = "\r\n\t"; public static final String pub = "public "; public static final String pri = "private "; public static final String imp = "import "; public static final String filePath = System.getProperty("user.dir"); public static final String dirPath = filePath + "\\src\\"; public static Pattern linePattern = Pattern.compile("_(\\w)"); public static Pattern linePattern2 = Pattern.compile("^[a-z]"); public static int createNum = 0; // 获取数据库下的表字段信息 public static BeanInfo getColumnInfos(String tableName) throws SQLException { BeanInfo beanInfo = new BeanInfo(); // 存入表名 beanInfo.setTableName(tableName); // 存入表对应的类名 String tableN = formChangeToLower(tableName); StringBuilder className = upCaseFirstWord(tableN); beanInfo.setClassName(className.toString()); // 获取列的信息 List<ColumnInfo> columnInfos = new ArrayList<>(); // 获取列名 List<String> columnNames = new ArrayList<>(); // 获取列的类型种类 List<String> columnTypes = new ArrayList<>(); // 连接数据库 Connection connection = DBConnectUtil.getConnection(); PreparedStatement pStatement = null; String tableSql = SQL + tableName; try { pStatement = connection.prepareStatement(tableSql); // 结果集元数据 ResultSetMetaData resultSetMetaData = pStatement.getMetaData(); // 得到表的列数 int columnCount = resultSetMetaData.getColumnCount(); for (int i = 0; i < columnCount; i++) { ColumnInfo columnInfo = new ColumnInfo(); // 获取列名 String columnName = resultSetMetaData.getColumnName(i + 1); // 获取列的类型 String columnType = resultSetMetaData.getColumnTypeName(i + 1); columnInfo.setColumnName(columnName); columnInfo.setColumnType(columnType); columnNames.add(columnName); columnInfos.add(columnInfo); // 获取列的类型种类 String columnTypeString = columnType.toString(); if (!columnTypes.contains(columnTypeString)) { columnTypes.add(columnType); } } } catch (SQLException e) { LOGGER.error("getColumnNames failure", e); } finally { DBConnectUtil.release(pStatement, connection); } beanInfo.setColumnNames(columnNames); beanInfo.setColumnInfos(columnInfos); beanInfo.setColumnTypes(columnTypes); return beanInfo; } // 生成javaBean public static void generateInJava(BeanInfo beanInfo) throws Exception { StringBuilder javaBuilder = new StringBuilder(); FileOutputStream fos = null; javaBuilder.append("package " + packagePath + ";"); javaBuilder.append(nextLine); // 拼接导入的jar String importJar = pieceImportJar(beanInfo); javaBuilder.append(importJar); // 拼接类名 javaBuilder.append(nextLine); javaBuilder.append(pub + "class " + beanInfo.getClassName() + "{"); javaBuilder.append(nextLine); // 开始拼接属性 List<ColumnInfo> columnInfos = beanInfo.getColumnInfos(); String propertyString = pieceProperty(columnInfos); javaBuilder.append(propertyString); // 拼接get与set方法 String methodString = pieceGetAndSetMethod(columnInfos); javaBuilder.append(methodString); javaBuilder.append(nextLine); javaBuilder.append("}"); // 获取路径，打印 String sendPath = dotToSlash(packagePath); String javaFilePath = dirPath + dotToSlash2(sendPath) + "\\" + beanInfo.getClassName() + ".java"; fos = new FileOutputStream(javaFilePath, true); fos.write(javaBuilder.toString().getBytes()); createNum++; System.out.println("create " + beanInfo.getClassName() + ".java"); fos.close(); } // 对import的jar包进行拼接 private static String pieceImportJar(BeanInfo beanInfo) { StringBuilder javaBuilder = new StringBuilder(); // 获取字段的类型 List<String> columnTypes = beanInfo.getColumnTypes(); // List columnTypes = getColumnTypes(beanInfo); // 拼接导入的jar for (Object type : columnTypes) { if ("DATE".equals(type.toString()) || "DATETIME".equals(type.toString())) { javaBuilder.append("import java.util.Date;"); javaBuilder.append(nextLine); } } return javaBuilder.toString(); } // 对表属性进行拼接 private static String pieceProperty(List<ColumnInfo> columnInfos) { StringBuilder javaBuilder = new StringBuilder(); // 开始拼接属性 for (ColumnInfo columnInfo : columnInfos) { if ("INT".equals(columnInfo.getColumnType().toString())) { javaBuilder.append(tab + pri + " Integer " + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); } else if ("VARCHAR".equals(columnInfo.getColumnType().toString())) { javaBuilder.append(tab + pri + " String " + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); } else if ("DECIMAL".equals(columnInfo.getColumnType().toString())) { javaBuilder.append(tab + pri + " Long " + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); } else if ("DATE".equals(columnInfo.getColumnType().toString()) || "DATETIME".equals(columnInfo.getColumnType().toString())) { javaBuilder.append(tab + pri + " Date " + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); } else if ("DOUBLE".equals(columnInfo.getColumnType().toString())) { javaBuilder.append(tab + pri + " Double " + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); } javaBuilder.append(nextLine); } return javaBuilder.toString(); } // 拼接get与set方法 private static String pieceGetAndSetMethod(List<ColumnInfo> columnInfos) { StringBuilder javaBuilder = new StringBuilder(); // 拼接get与set方法 for (ColumnInfo columnInfo : columnInfos) { if ("INT".equals(columnInfo.getColumnType().toString())) { // 单词首字母要大写 get javaBuilder.append(tab + pub + " Integer get" + upCaseFirstWord(formChangeToLower(columnInfo.getColumnName())) + "(){"); javaBuilder.append(nextLine); javaBuilder.append(towtab + "return " + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); javaBuilder.append(tab + "}"); javaBuilder.append(nextLine); // 单词首字母要大写 set javaBuilder .append(tab + pub + " void set" + upCaseFirstWord(formChangeToLower(columnInfo.getColumnName())) + "(Integer " + formChangeToLower(columnInfo.getColumnName()) + "){"); javaBuilder.append(nextLine); javaBuilder.append(towtab + "this." + formChangeToLower(columnInfo.getColumnName()) + "=" + formChangeToLower(columnInfo.getColumnName()) + ";"); javaBuilder.append(nextLine); javaBuilder.append(tab + "}"); javaBuilder.append(