Excel导入数据库:Date类型数据插入mysql数据库时出错,求大神指导
数据库建表:
DROP TABLE IF EXISTS stu;
CREATE TABLE `stu` (
`id` int(4) NOT NULL auto_increment,
`name` varchar(10) NOT NULL,
`age` int(2) NOT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
bean://set get 方法都有
package net.company.bean;
import java.util.Date;
public class Student {
private int id;
private String name;
private int age;
private Date birthday;
}
DAO:
package net.company.dao;
import
java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import
java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import net.company.bean.Student;
import net.company.connect.ConnConnection;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class StuDateDao {
public void getStuDateDao() {
// 找到导入的文件
InputStream is = StuDateDao.class.getClassLoader().getResourceAsStream(
"student.xls");
try {
Workbook wb = Workbook.getWorkbook(is);// 创建工作簿
Sheet sheet = wb.getSheet(0);// 创建工作表
for (int i = 1; i < sheet.getRows(); i++) {
Student stu = new Student();
for (int j = 0; j < sheet.getColumns(); j++) {
if (j == 1) {
stu.setName(sheet.getCell(j, i).getContents());
continue;
}
if (j == 2) {
stu.setAge(Integer.parseInt(sheet.getCell(j, i)
.getContents()));
continue;
}
if (j == 3) {
Date date = net.company.utils.Utils.stringToDate(sheet
.getCell(j, i).getContents(),
"yyyy-MM-dd hh:mm:ss");// 把Excel表格中字符串数据转化为时间
Timestamp ts = new Timestamp(date.getTime());
stu.setTime(ts);
continue;
}
}
getStudentInfo(stu);
}
} catch (BiffException e) {
e.printStackTrace();
} catch (
IOException e) {
e.printStackTrace();
}
}
public void getStudentInfo(Student stu) {
String sql = "insert into stu values(" + stu.getId() + ",'" + stu.getName() + "'," + stu.getAge() +stu.getTime() + ")";
ConnConnection conn = new ConnConnection();
Connection con = conn.getConnect();
try {
Statement state = con.createStatement();
state.executeUpdate(sql);
} catch (
SQLException e) {
e.printStackTrace();
} finally {
try {