一、使用poi包读取excel的cell时，整数会被转换为浮点数，即表格中数据为1的cell，读取的HSSFCell.toString()是1.0的格式，需要使用replaceAll(".0","")将.0去掉，或者使用(int)HSSFCell.getNumericCellValue()将得到的数据强行转换成整数。
二、对于使用PreparedStatement时，对？位置进行赋值时，赋值为null需要使用setNull函数而不是setInt()或者setString()这种类型的函数。SetNull(Index,type)，index填写赋值为null的？的位置，type填写对应位置的数据类型，例如Types.Integer。
三、mysql对于没有主键（索引？）的表，在workbrench上面是readonly的，要自己写sql语句导入数据。
四、mysql里面的Timestamp类型的数据，只能自动获取当前时间，不能按照long类型转换过去，所以mysql使用Datetime，用java里面的Date类型导入。
五、csv格式的文件可以直接用Reader读取，属性之间用“，”隔开。
下面的代码是导入movielens数据所用的，因为是一个一个表导入，所以格式比较乱，仅作为代码备份使用。
package excel.data;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import org.apache.poi.hssf.usermodel.*;
public class Link {
public static Connection getConnection() throws SQLException, java.lang.ClassNotFoundException
{
//第一步：加载MySQL的JDBC的驱动
Class.forName("com.mysql.jdbc.Driver");
//取得连接的url,能访问MySQL数据库的用户名,密码；moviesystem：数据库名
String url = "jdbc:mysql://localhost:3306/moviesystem";
String username = "admin";
String password = "960820";
//第二步：创建与MySQL数据库的连接类的实例
Connection con = DriverManager.getConnection(url, username, password);
return con;
}
public static void main(String args[]) {
//循环导入用户表
/*try
{
String sql = "insert into user(userId,username,password) values(?,?,?)";
//第三步：获取连接类实例con，用con创建Statement对象类实例 sql_statement
Connection con = getConnection();
PreparedStatement ps = null;
for(int i=2;i<=671;i++)
{
try{
ps = con.prepareStatement(sql);
ps.setInt(1, i);
ps.setString(2, String.valueOf(i));
ps.setString(3, String.valueOf(i));
boolean flag = ps.execute();
if(!flag){
System.out.println("Save data : No. = " + i);
}
}catch(Exception e)
{
e.printStackTrace();
}
}
System.out.println("Success");
//关闭连接和声明
ps.close();
con.close();
} catch(java.lang.ClassNotFoundException e) {
//加载JDBC错误,所要用的驱动没有找到
System.err.print("ClassNotFoundException");
//其他错误
System.err.println(e.getMessage());
} catch (SQLException ex) {
//显示数据库连接错误或查询错误
System.err.println("SQLException: " + ex.getMessage());
} */
//import movie
try
{
String sql = "insert into movie(movieId,title,genre) values(?,?,?)";
//第三步：获取连接类实例con，用con创建Statement对象类实例 sql_statement
Connection con = getConnection();
PreparedStatement ps = null;
InputStream is;
try {
is = new FileInputStream("movies.xls");
HSSFWorkbook hssfWorkbook;
int rowNum=1;
try {
hssfWorkbook = new HSSFWorkbook(is);
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
// 循环行Row
for (rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
HSSFCell movieId = hssfRow.getCell(0);
HSSFCell title = hssfRow.getCell(1);
HSSFCell genre = hssfRow.getCell(2);
String t = genre.toString();
if(t.indexOf(".") > 0)
{
//正则表达
t = t.replaceAll("0+?$", "");//去掉后面无用的零
t = t.replaceAll("[.]$", "");//如小数点后面全是零则去掉小数点
}
ps = con.prepareStatement(sql);
ps.setInt(1, (int)movieId.getNumericCellValue());
ps.setString(2, title.toString());
ps.setString(3, t);
//System.out.println((int)movieId.getNumericCellValue()+","+title.toString()+","+t);
boolean flag = ps.execute();
if(!flag)
{
System.out.println("No:"+rowNum);
}
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
System.out.println("error:"+rowNum);
}
System.out.println("Success");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//关闭连接和声明
ps.close();
con.close();
} catch(java.lang.ClassNotFoundException e) {
//加载JDBC错误,所要用的驱动没有找到
System.err.print("ClassNotFoundException");
//其他错误
System.err.println(e.getMessage());
} catch (SQLException ex) {
//显示数据库连接错误或查询错误
System.err.println("SQLException: " + ex.getMessage());
}
//import link
/*try
{
String sql = "insert into link(movieId,imdbId,tmdbId) values(?,?,?)";
//第三步：获取连接类实例con，用con创建Statement对象类实例 sql_statement
Connection con = getConnection();
PreparedStatement ps = null;
InputStream is;
try {
is = new FileInputStream("links.xls");
HSSFWorkbook hssfWorkbook;
int rowNum=1;
try {
hssfWorkbook = new HSSFWorkbook(is);
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
// 循环行Row
for (rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
HSSFCell movieId = hssfRow.getCell(0);
HSSFCell imdbId = hssfRow.getCell(1);
HSSFCell tmdbId = hssfRow.getCell(2);
ps = con.prepareStatement(sql);
ps.setInt(1, (int)movieId.getNumericCellValue());
if(imdbId == null)
ps.setNull(2, Types.INTEGER);
else
ps.setInt(2, (int)imdbId.getNumericCellValue());
if(tmdbId == null)
ps.setNull(3, Types.INTEGER);
else
ps.setInt(3, (int)tmdbId.getNumericCellValue());
/*System.out.print((int)movieId.getNumericCellValue()+",");
if(imdbId == null)
System.out.print("null,");
else
System.out.print((int)imdbId.getNumericCellValue()+",");
if(tmdbId == null)
System.out.println("null");
else
System.out.println((int)tmdbId.getNumericCellValue()+"");
boolean flag = ps.execute();
if(!flag)
{
System.out.println("No:"+rowNum);
}
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
System.out.println("error:"+rowNum);
}
System.out.println("Success");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//关闭连接和声明
ps.close();
con.close();
} catch(java.lang.ClassNotFoundException e) {
//加载JDBC错误,所要用的驱动没有找到
System.err.print("ClassNotFoundException");
//其他错误
System.err.println(e.getMessage());
} catch (SQLException ex) {
//显示数据库连接错误或查询错误
System.err.println("SQLException: " + ex.getMessage());
}*/
//import rating
/*try
{
String sql = "insert into rating(userId,movieId,rating,timestamp) values(?,?,?,?)";
//第三步：获取连接类实例con，用con创建Statement对象类实例 sql_statement
Connection con = getConnection();
PreparedStatement ps = null;
File is = new File("ratings.csv");
BufferedReader br = null;
try {
br = new BufferedReader(new FileReader(is));
String line = "";
String sub = "";
try {
line=br.readLine();
for(line = br.readLine(); line!=null; line = br.readLine())
{
int userId,movieId;
double rating;
Date timestamp;
int index = line.indexOf(",");
sub = line.substring(0, index);
line = line.substring(index+1);
//System.out.println(sub+"\n"+line);
userId = Integer.valueOf(sub).intValue();
//System.out.println(userId);
index = line.indexOf(",");
sub = line.substring(0, index);
line = line.substring(index+1);
movieId = Integer.valueOf(sub).intValue();
//System.out.println(sub+"\n"+line);
//System.out.println(movieId);
index = line.indexOf(",");
sub = line.substring(0, index);
line = line.substring(index+1);
//System.out.println(sub+"\n"+line);
rating = Double.valueOf(sub).doubleValue();
//System.out.println(rating);
//SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
long time = Long.valueOf(line).longValue();
timestamp = new Date(time*1000);
//String t = formatter.format(timestamp);
//System.out.println(userId+","+movieId+","+rating+","+t);
ps = con.prepareStatement(sql);
ps.setInt(1, userId);
ps.setInt(2, movieId);
ps.setDouble(3,rating);
ps.setDate(4, timestamp);
boolean flag = ps.execute();
if(!flag)
System.out.println(userId+","+movieId+","+rating+","+timestamp);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Can't create the bufferedreader");
}
ps.close();
con.close();
} catch(java.lang.ClassNotFoundException e) {
//加载JDBC错误,所要用的驱动没有找到
System.err.print("ClassNotFoundException");
//其他错误
System.err.println(e.getMessage());
} catch (SQLException ex) {
//显示数据库连接错误或查询错误
System.err.println("SQLException: " + ex.getMessage());
}*/
/*String s = "10";
if(s.indexOf(".") > 0){
//正则表达
s = s.replaceAll("0+?$", "");//去掉后面无用的零
s = s.replaceAll("[.]$", "");//如小数点后面全是零则去掉小数点
}
System.out.println(s);*/
}
}
122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
