上一篇写了从数据库导出excel。今天继上一篇写出从excel导入数据库。数据库表有这些字段：ID,BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISH,BOOK_DATE,BOOK_ISBN,BOOK_PAGE,BOOK_PRICE。
连接数据库类：
import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;
public class DBConnection {private String classString="oracle.jdbc.driver.OracleDriver";private String username="benz";private String password="benz";private String url="java:oracle:thin:@192.168.1.17:1521:bhdba";private Connection con=null;public Connection getConnection(){ try { Class.forName(classString); con=DriverManager.getConnection(url,username,password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return con;}
}
具体操纵类：
import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.SQLException;import java.text.ParseException;import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelToDB {private Connection con;private DBConnection db;private PreparedStatement pst;private String filePath="f:\\test.xls";public boolean insertDB(){ boolean flag=true; db=new DBConnection(); con=db.getConnection(); try { //文件流指向excel文件 FileInputStream fin=new FileInputStream(filePath); HSSFWorkbook workbook=new HSSFWorkbook(fin);//创建工作薄 HSSFSheet sheet=workbook.getSheetAt(0);//得到工作表 HSSFRow row=null;//对应excel的行 HSSFCell cell=null;//对应excel的列 int totalRow=sheet.getLastRowNum();//得到excel的总记录条数 //以下的字段一一对应数据库表的字段 String bookName=""; String bookAuthor=""; String bookPublish=""; Date bookDate=null; String bookIsbn=""; int bookPage=0; float bookPrice=0.0f; String sql="insert into book(ID,BOOK_NAME,BOOK_AUTHOR,BOOK_PUBLISH," + "BOOK_DATE,BOOK_ISBN,BOOK_PAGE,BOOK_PRICE) " + "values(SEQ_BOOK.NEXTVAL,?,?,?,?,?,?,?)"; //SEQ_BOOK.NEXTVAL为数据库表序列 for(int i=1;i<=totalRow;i++){ row=sheet.getRow(i); cell=row.getCell(1); bookName=cell.getRichStringCellValue().toString(); cell=row.getCell(2); bookAuthor=cell.getRichStringCellValue().toString(); cell=row.getCell(3); bookPublish=cell.getRichStringCellValue().toString(); cell=row.getCell(4); //格式化字符串时间 SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd"); bookDate=new Date((format.parse(cell.getRichStringCellValue().toString())).getTime()); cell=row.getCell(5); bookIsbn=cell.getRichStringCellValue().toString(); cell=row.getCell(6); bookPage=Integer.parseInt(cell.getRichStringCellValue().toString()); cell=row.getCell(7); bookPrice=Float.parseFloat(cell.getRichStringCellValue().toString()); pst=con.prepareStatement(sql); pst.setString(1,bookName); pst.setString(2,bookAuthor); pst.setString(3,bookPublish); pst.setDate(4,bookDate); pst.setString(5,bookIsbn); pst.setInt(6,bookPage); pst.setFloat(7,bookPrice); pst.execute(); } } catch (FileNotFoundException e) { flag=false; e.printStackTrace(); } catch(IOException ex){ flag=false; ex.printStackTrace(); } catch(SQLException exx){ flag=false; exx.printStackTrace(); } catch(ParseException exxx){ exxx.printStackTrace(); }finally{ try { pst.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } return flag; }public static void main(String args[]){ ExcelToDB toDB=new ExcelToDB(); toDB.insertDB();}
}
相关资源：poi作excel导入数据库