Java 如何使用jdbc连接将excel表中的数据存储到mysql数据库中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18721773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to store data from excel sheet into mysql database using jdbc connection
提问by Avinash Singh
I have written the code for access data from Excel sheet but i am not able to store into database using JDBC . my code for access data from excel sheet
我已经编写了从 Excel 工作表访问数据的代码，但我无法使用 JDBC 存储到数据库中。我从 Excel 表访问数据的代码
try{
List list=new ArrayList();
String fileName="d:\menu.xls";
File file=new File(fileName);
InputStream input = new BufferedInputStream(new FileInputStream(file));
POIFSFileSystem fs = new POIFSFileSystem( input );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
//int i=0;
Iterator rows=sheet.rowIterator();
while(rows.hasNext()){
HSSFRow row=(HSSFRow)rows.next();
System.out.println("\n");
Iterator cells=row.cellIterator();
while( cells.hasNext() ) {
HSSFCell cell = (HSSFCell) cells.next();
if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType()){
System.out.print(cell.getNumericCellValue()+" " );
// list.add(cell.getNumericCellValue());
}
else if (HSSFCell.CELL_TYPE_STRING==cell.getCellType()){
System.out.print(cell.getStringCellValue()+" " );
//list.add(cell.getStringCellValue());
}
else
if (HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType()){
System.out.print(cell.getBooleanCellValue()+" " );
//list.add(cell.getBooleanCellValue());
}
else
if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType()){
System.out.print( "BLANK " );}
else
System.out.print("Unknown cell type");
}
}
System.out.println(list);
}catch(Exception e){
e.printStackTrace();
}
I got all the data into myeclipse console bt i have no idea how to store in DB
我将所有数据都输入了 myeclipse 控制台 bt 我不知道如何存储在数据库中
please tell me how to store data into mysql database using JDBC
请告诉我如何使用JDBC将数据存储到mysql数据库中
Thanx in advance
提前谢谢
采纳答案by dic19
As you have an ArrayListin which you're adding cell values, you can proceed as follows:
当您ArrayList在其中添加单元格值时，您可以按以下步骤操作：
public void insertRowInDB(List cellValues){
Connection con = null;
PreparedStatement preparedStatement = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost/databaseName?"
+ "user=sqlUser&password=sqlPassword");
String sql = "INSERT INTO tableName(field1, field2, ..., fieldN) VALUES (?,?,...,?)";
preparedStatement = con.prepareStatement(sql);
int paramIndex = 1;
for(Object cell : cellValues){
preparedStatement.setObject(paramIndex, cell);
paramIndex++;
}
int status = preparedStatement.executeUpdate();
//DO something with the status if needed
} catch(SQLException ex) {
/* log the exception */
} finally {
try{
preparedStatemnt.close();
con.close();
} catch(SQLException ignored) {}
}
}
You'll need make this little change:
你需要做这个小小的改变：
while(rows.hasNext()){
List list = new ArrayList(); // initialize the list here
HSSFRow row=(HSSFRow)rows.next();
System.out.println("\n");
Iterator cells=row.cellIterator();
while( cells.hasNext() ) {
/* all your code seems fine here, just uncomment list.add(something) lines */
}
insertRowInDB(list); // insert the cells in your database
}
I also suggest you check out this MySQL and Java JDBC - Tutorial
我还建议您查看此MySQL 和 Java JDBC - 教程
