数据库连接:
package javacore1;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import com.foxx.app.NewDataToDB;
import com.foxx.model.FoxxCode;
public class DB {
//批量插入的速度要比普通处理的速度快
public static int[] Insert(Connection conn,List<FoxxCode> list) {
int[] a=null;
try {
String sql = "insert into index_and_stk (index_code,index_name,constituent_code,constituent_name,weight)values(?,?,?,?,?)";
// 插入数据的sql语句
PreparedStatement pstmt=conn.prepareStatement(sql);
long startTime=System.currentTimeMillis();
for(FoxxCode foxx :NewDataToDB.list){
pstmt.setString(1, foxx.getIndex_code());
pstmt.setString(2, foxx.getIndex_name());
pstmt.setString(3, foxx.getConstituent_code());
pstmt.setString(4, foxx.getConstituent_name());
pstmt.setDouble(5, foxx.getWeight());
pstmt.addBatch();//添加到批量处理
}
int[] result=pstmt.executeBatch();
a=result;
System.out.println("总共耗时:"+(System.currentTimeMillis() - startTime));
pstmt.close(); //关闭数据库连接
} catch (SQLException e) {
e.printStackTrace();
}
return a;
}
//创建数据库连接
public static Connection getConnection(String user, String pass) {
Connection conn = null;//声明连接对象
String driver = "com.mysql.jdbc.Driver";// 驱动程序类名
String url = "jdbc:mysql://localhost:3306/test?" // 数据库URL
+ "useUnicode=true&characterEncoding=UTF8";// 防止乱码
try {
Class.forName(driver);// 注册(加载)驱动程序
conn = DriverManager.getConnection(url, user, pass);// 获取数据库连接
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//回收数据库连接
public static void closeConnection(Connection conn) {
try {
if (conn != null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
NewDataToDB mNewDataToDB = new NewDataToDB();
mNewDataToDB.main(args);
// 账号密码
Connection conn = getConnection("root", "");
//方法名调用数据库连接,把须保存的数据拿过来
Insert(conn,NewDataToDB.list);
// 保存完之后关闭数据库连接
closeConnection(conn);
}
}
读取excel文档数据:
package com.foxx.app;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
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;
import com.foxx.model.FoxxCode;
import javacore1.DB;
/**
* 用apache poi 包提供的接口来读取excel文档
* @author jiangwanchang
* @deprecated 读取本地文件并存库
* @Date:2017年11月21日 11:30
*
*/
public class NewDataToDB {
public static List<FoxxCode> list = new ArrayList<FoxxCode>();
public static void main(String args[]) {
DB mDBHelper = new DB();
FoxxCode mFoxxcode =null;
try {
String fileName = "f:/tt.xls"; //定义本地的读取文件位置
int count =0; //我这里用来去除标题各种
HSSFWorkbook workXsl = new HSSFWorkbook(new FileInputStream(fileName)); // 得到这个excel表格对象,并以workXsl来保存
// wb.getSheetAt(0);
// 循环遍历表sheet.getLastRowNum()是获取一个表最后一条记录的记录号,
HSSFSheet sheet = workXsl.getSheetAt(0);
//存放list
for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
mFoxxcode= new FoxxCode();
count++;
if(count>1){
// 创建一个行对象
HSSFRow row = sheet.getRow(j);
HSSFCell cell0 = row.getCell((short) 0);
HSSFCell cell1 = row.getCell((short) 1);
HSSFCell cell2 = row.getCell((short) 2);
HSSFCell cell3 = row.getCell((short) 3);
HSSFCell cell4 = row.getCell((short) 4);
System.out.println(cell0+"\t"+cell1+"\t"+cell2+"\t"+cell3+"\t"+cell4);
mFoxxcode.setIndex_code(cell0.getStringCellValue());
mFoxxcode.setIndex_name(cell1.getStringCellValue());
mFoxxcode.setConstituent_code(cell2.getStringCellValue());
mFoxxcode.setConstituent_name(cell3.getStringCellValue());
mFoxxcode.setWeight((double)cell4.getCellType());
list.add(mFoxxcode);
}
System.out.println("=================================================");
}
} catch (FileNotFoundException e) {
System.out.println(e.toString());
} catch (IOException ex) {
System.out.println(ex.toString());
}
}
}
、、、这里是另一个遍历
// 把一行里的每一个字段遍历出来
//for (int i = 0; i < row.getLastCellNum(); i++) {
// // 创建一个行里的一个字段的对象,也就是获取到的一个单元格中的值
// HSSFCell cell = row.getCell(i);
//
//
//
//
// // 在这里我们就可以做很多自己想做的操作了,比如往数据库中添加数据等
// System.out.println(cell.getRichStringCellValue());
//}