用java把excel表的数据导入到数据库（可能有些类型还没考虑完全，抽空写的）
————————————————————————————代码下载————————————————————————————————
简单的思路
一、先获取数据库表的字段名、主键名，
二、读取excel文件的数据（注意：excel文件的数据表字段的顺序与该表的顺序一致，有空再做导出）
三、拼接sql
上代码：
一、先连接数据库
package com.cn.wjq.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class DBConnectUtil {
private static String driver;
private static String username;
private static String pwd;
private static String url;
static {
try {
ClassLoader classLoader = DBConnectUtil.class.getClassLoader();
InputStream resourceAsStream = classLoader.getResourceAsStream("config/props/db.properties");
Properties properties=new Properties();
properties.load(resourceAsStream);
url = properties.getProperty("url");
username = properties.getProperty("user");
pwd = properties.getProperty("password");
driver = properties.getProperty("driver");
Class.forName(driver);
}catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection conn = (Connection) DriverManager.getConnection(url, username, pwd);
if (conn == null) {
System.out.println("Failed to connect database...");
} else {
System.out.println("database connected successful...");
}
return conn;
}
public static void release(ResultSet rs, PreparedStatement sta, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("Resource release successful...");
}
public static void release(PreparedStatement sta, Connection conn) {
if (sta != null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("Resource release successful...");
}
}
二、获取表的信息（当时没想好，写了个生成实体类代码（也就拼接））
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(nextLine);
} else if ("VARCHAR".equals(columnInfo.getColumnType().toString())) {
// 单词首字母要大写 get
javaBuilder.append(tab + pub + " String 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()))
+ "(String " + formChangeToLower(columnInfo.getColumnName()) + "){");
javaBuilder.append(nextLine);
javaBuilder.append(towtab + "this." + formChangeToLower(columnInfo.getColumnName()) + "="
+ formChangeToLower(columnInfo.getColumnName()) + ";");
javaBuilder.append(nextLine);
javaBuilder.append(tab + "}");
javaBuilder.append(nextLine);
} else if ("DECIMAL".equals(columnInfo.getColumnType().toString())) {
// 单词首字母要大写 get
javaBuilder.append(tab + pub + " Long 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()))
+ "(Long " + formChangeToLower(columnInfo.getColumnName()) + "){");
javaBuilder.append(nextLine);
javaBuilder.append(towtab + "this." + formChangeToLower(columnInfo.getColumnName()) + "="
+ formChangeToLower(columnInfo.getColumnName()) + ";");
javaBuilder.append(nextLine);
javaBuilder.append(tab + "}");
javaBuilder.append(nextLine);
} else if ("DATE".equals(columnInfo.getColumnType().toString())
|| "DATETIME".equals(columnInfo.getColumnType().toString())) {
// 单词首字母要大写 get
javaBuilder.append(tab + pub + " Date 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()))
+ "(Date " + formChangeToLower(columnInfo.getColumnName()) + "){");
javaBuilder.append(nextLine);
javaBuilder.append(towtab + "this." + formChangeToLower(columnInfo.getColumnName()) + "="
+ formChangeToLower(columnInfo.getColumnName()) + ";");
javaBuilder.append(nextLine);
javaBuilder.append(tab + "}");
javaBuilder.append(nextLine);
} else if ("DOUBLE".equals(columnInfo.getColumnType().toString())) {
// 单词首字母要大写 get
javaBuilder.append(tab + pub + " Double 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()))
+ "(Double " + formChangeToLower(columnInfo.getColumnName()) + "){");
javaBuilder.append(nextLine);
javaBuilder.append(towtab + "this." + formChangeToLower(columnInfo.getColumnName()) + "="
+ formChangeToLower(columnInfo.getColumnName()) + ";");
javaBuilder.append(nextLine);
javaBuilder.append(tab + "}");
javaBuilder.append(nextLine);
}
javaBuilder.append(nextLine);
}
return javaBuilder.toString();
}
// 转换路径格式
// 例如：com/apache/convertor --> com\apache\convertor
private static String dotToSlash2(String path) {
String newPath = path.replace("/", "\\");
return newPath;
}
// 转换路径格式
// 例如：com.apache.convertor --> com/apache/convertor
private static String dotToSlash(String name) {
String newName = name.replace(".", "/");
return newName;
}
// 字符串转小写
private static String formChangeToLower(String name) {
return name.toLowerCase().replace("_", "");
}
// 属性首字母改成大写
private static StringBuilder upCaseFirstWord(String columnName) {
StringBuilder stringBuilder = new StringBuilder();
String firstWord = columnName.substring(0, 1).toUpperCase();
String afertWord = columnName.substring(1);
stringBuilder.append(firstWord + afertWord);
return stringBuilder;
}
// 转换为驼峰命名
private static String formChangeToHump(String name) {
name = name.toLowerCase();
Matcher m = linePattern.matcher(name);
StringBuffer sb = new StringBuffer();
while (m.find()) {
m.appendReplacement(sb, m.group(1).toUpperCase());
}
m.appendTail(sb);
StringBuffer sb1 = new StringBuffer();
Matcher m1 = linePattern2.matcher(sb.toString());
if (m1.find()) {
m1.appendReplacement(sb1, m1.group(0).toUpperCase());
}
m1.appendTail(sb1);
return sb1.toString();
}
public static void main(String[] args) throws Exception {
String tableName = "gjjdepositeinfo";
BeanInfo beanInfo = getColumnInfos(tableName);
List<ColumnInfo> columnInfos = beanInfo.getColumnInfos();
for (int i = 0; i < columnInfos.size(); i++) {
System.out.println(columnInfos.get(i).getColumnName() + "..." + columnInfos.get(i).getColumnType());
}
generateInJava(beanInfo);
System.out.println(beanInfo.getColumnNames().toString());
}
}
三、记录表格的信息实体类
package com.cn.wjq.model;
import java.util.List;
public class BeanInfo {
//表名称
private String tableName;
//表对应的类名
private String className;
//列的信息
private List<ColumnInfo> ColumnInfos;
//列的类型种类
private List<String> columnTypes;
//列的类名
private List<String> columnNames;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public List<ColumnInfo> getColumnInfos() {
return ColumnInfos;
}
public void setColumnInfos(List<ColumnInfo> columnInfos) {
ColumnInfos = columnInfos;
}
public List<String> getColumnTypes() {
return columnTypes;
}
public void setColumnTypes(List<String> columnTypes) {
this.columnTypes = columnTypes;
}
public List<String> getColumnNames() {
return columnNames;
}
public void setColumnNames(List<String> columnNames) {
this.columnNames = columnNames;
}
@Override
public String toString() {
return "BeanInfo [tableName=" + tableName + ", className=" + className + ", ColumnInfos=" + ColumnInfos
+ ", columnTypes=" + columnTypes + ", columnNames=" + columnNames + "]";
}
}
四、读取excel
package com.cn.wjq.util;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.cn.wjq.dao.MysqlImpl;
import com.cn.wjq.model.BeanInfo;
public class ExcelInsertPiece {
public ExcelInsertPiece() {
}
public static void start(InputStream in, String path,BeanInfo beanInfo) throws Exception {
Workbook book = getWorkBook(in, path); // 1.获取工作簿
List<Sheet> sheets = getSheets(book); // 2.获取所有工作表
SheetIterator(sheets,beanInfo); // 3.对所有工作表进行操作
}
// 1.获取工作簿
public static Workbook getWorkBook(InputStream in, String path) throws FileNotFoundException, IOException {
return path.endsWith(".xls") ? (new HSSFWorkbook(in))
: (path.endsWith(".xlsx") ? (new XSSFWorkbook(in)) : (null));
}
// 2.获取所有工作表
private static List<Sheet> getSheets(Workbook book) {
int numberOfSheets = book.getNumberOfSheets();
System.out.println("numberOfSheets:" + numberOfSheets);
List<Sheet> sheets = new ArrayList<Sheet>();
for (int i = 0; i < numberOfSheets; i++) {
sheets.add(book.getSheetAt(i));
}
return sheets;
}
// 3.对所有工作表进行操作
private static void SheetIterator(List<Sheet> sheets,BeanInfo beanInfo) {
for (int i = 0; i < sheets.size(); i++) { //循环每一张工作表
Sheet sheet = sheets.get(i);
if (sheet.getLastRowNum() > 1) { //判断是否为空表，获取有数据的最后一行的行数。如果为零则为空表
System.out.println(sheet.getSheetName() + "============="); //打印不为空的工作表名字
}
Iterator<Row> iterator = sheet.iterator(); //迭代器
//用两个while循环遍历所有单元格
while (iterator.hasNext()) { //遍历每一行
Row nextRow = iterator.next();
if (nextRow.getRowNum() < 1) {
continue;
//nextRow.getRowNum()就是获取行数，由表中看出第一行(getRowNum()=0)为表头，直接跳过
}
//从第二行开始是有用的数据，要保存早数据库，第二行：nextRow.getRowNum()=1
Iterator<Cell> cellIterator = nextRow.cellIterator();
int size = beanInfo.getColumnNames().size();
List<Object> columnValues=new ArrayList<>();
while (cellIterator.hasNext()) { //遍历每一行的每一列
Cell cell = cellIterator.next();
if (cell.getColumnIndex()>=0&&cell.getColumnIndex()<size) {
String cellValue = "";
DecimalFormat df = new DecimalFormat("#");
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
cellValue =cell.getRichStringCellValue().getString().trim();
columnValues.add(cellValue);
break;
case HSSFCell.CELL_TYPE_NUMERIC:
//将单元格内容设置为number类型，也可以这样写cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellType(0);
SimpleDateFormat ftm = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
System.out.println(value+"...");
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
//判断是否属于时间类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
columnValues.add(ftm.format(date));
//如果这里打印的话，还是会打印出数字，但是保存到数据库会变成日期格式
System.out.println(ftm.format(date).toString());
}else {
//基本类型
columnValues.add(value);
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue =String.valueOf(cell.getBooleanCellValue()).trim();
columnValues.add(cellValue);
break;
case HSSFCell.CELL_TYPE_FORMULA:
cellValue =cell.getCellFormula();
columnValues.add(cellValue);
break;
default:
cell.setCellType(1);
columnValues.add(cell.getStringCellValue().toString());
break;
}
}
}
try {
//到这里已经遍历完一行Execl的所有单元格，并存储到model里面了，现在调用方法保存到数据库
MysqlImpl mysqlImpl = new MysqlImpl();
System.out.println(columnValues.toString());
if (columnValues.size()==1&&columnValues.get(0)==null||columnValues.get(0)=="") {
//判断excel表中的这条数据是否为空
continue;
}else if (mysqlImpl.exist(beanInfo, columnValues)) {
System.out.println("此对象已存在于数据库,不可重复添加");
continue;
}
else {
mysqlImpl.save(beanInfo, columnValues);
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(" ");
}
System.out.println(" ");
}
}
}
五、开始拼接sql先查询是否存在该表主键存在，不存在在读取excel的代码中再执行插入
package com.cn.wjq.dao;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.cn.wjq.model.BeanInfo;
import com.cn.wjq.util.DBConnectUtil;
public class MysqlImpl {
private static final String sql_findById = " SELECT * FROM ";
private static final String sql_save = " INSERT INTO ";
@SuppressWarnings("resource")
public Integer save(BeanInfo beanInfo,List<Object> columnValus) throws Exception {
Connection conn = null;
PreparedStatement prep = null;
int i = 0;
try {
conn = DBConnectUtil.getConnection();
StringBuilder sqlBuilder=new StringBuilder();
//拼接sql
//" INSERT INTO t_reader(id,dept,course,credit,book_num,publish_date,book_name) VALUES(?,?,?,?,?,?,?)";
sqlBuilder.append(sql_save);
sqlBuilder.append(beanInfo.getTableName()+"(");
//拼接sql的属性
List<String> columnNames = beanInfo.getColumnNames();
int prepCount=0;
ok:
for(int j=0;j<columnNames.size();j++) {
if (j==columnNames.size()-1) {
prepCount++;
sqlBuilder.append(columnNames.get(j)+") VALUES(");
for (int j2 = 0; j2 < prepCount; j2++) {
if (j2==prepCount-1) {
sqlBuilder.append("?)");
break ok;
}
sqlBuilder.append("?,");
}
}
sqlBuilder.append(columnNames.get(j)+",");
prepCount++;
}
String sqlString = sqlBuilder.toString();
prep = conn.prepareStatement(sqlString);
for(int k=0;k<columnValus.size();k++) {
prep.setObject(k+1, columnValus.get(k));
}
i = prep.executeUpdate();
if(i>0){
System.out.println("1 row affected...");
}else if(i==0){
System.out.println("insert failed...");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBConnectUtil.release( prep, conn);
}
return i;
}
//判断该主键是否存在
//SELECT * FROM t_reader WHERE id = ?
public boolean exist(BeanInfo beanInfo,List<Object> columnValus) {
Connection connection=null;
PreparedStatement prepareStatement=null;
ResultSet resultSet=null;
DatabaseMetaData dbMetaData = null;
boolean exist=false;
ResultSet primaryKeyResultSet =null;
String primaryKeyColumnName =null;
StringBuilder sqlBuilder=null;
try {
connection = DBConnectUtil.getConnection();
dbMetaData = connection.getMetaData();
String tableName = beanInfo.getTableName();
List<String> columnNames = beanInfo.getColumnNames();
primaryKeyResultSet = dbMetaData.getPrimaryKeys(null,null,tableName);
while(primaryKeyResultSet.next()){
primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");
}
if (primaryKeyColumnName!=null) {
sqlBuilder=new StringBuilder();
sqlBuilder.append(sql_findById);
sqlBuilder.append(beanInfo.getTableName()+" WHERE "+primaryKeyColumnName+"=?");
prepareStatement = connection.prepareStatement(sqlBuilder.toString());
ok:
for(int i=0;i<columnValus.size();i++) {
if (primaryKeyColumnName.equals(columnNames.get(i))) {
prepareStatement.setObject(1, columnValus.get(i));
break ok;
}
}
resultSet = prepareStatement.executeQuery();
if (resultSet.next()==true) {
exist=true;
}else {
exist=false;
}
}
} catch (Exception e) {
// TODO: handle exception
}
return exist;
}
}
六、main方法
package com.cn.wjq.main;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import com.cn.wjq.model.BeanInfo;
import com.cn.wjq.model.ColumnInfo;
import com.cn.wjq.util.ExcelInsertPiece;
import com.cn.wjq.util.Generator;
public class MyMain {
public static void main(String[] args) throws Exception {
String tableName1="t_cousre";
//输入表名
BeanInfo beanInfo = Generator.getColumnInfos(tableName1);
List<ColumnInfo> columnInfos = beanInfo.getColumnInfos();
for (int i = 0; i < columnInfos.size(); i++) {
System.out.println(columnInfos.get(i).getColumnName() + "..." + columnInfos.get(i).getColumnType());
}
Generator.generateInJava(beanInfo);
//String path = "c:/t_reader工作表.xls";
String path = "c:/couse工作表.xls";
InputStream in = new FileInputStream(new File(path));
ExcelInsertPiece.start(in,path,beanInfo);
}
}
估计还有不少漏洞，只是基本可用，新手小白鼠一只，欢迎大佬们点评；有需要修改的地方还请不吝赐教
