1、添加 MySQL-connector-java.1.7-bin.jar和jxl.jar包到项目的lib目录下
2.数据库名为wiki
3.表名为stu
4.Excel文件目录:e://stu.xls
5.编写类:连接mysql的字符串方法、插入的方法、实体类
表结构如下:
链接数据库
package com.javen.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBhepler {
/*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://192.168.1.211;DatabaseName=wiki";*/
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://192.168.1.211:3306/wiki?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&autoReconnectForPools=true";
Connection con = null;
ResultSet res = null;
public void DataBase() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, "root", "123456");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.err.println("装载 JDBC/ODBC 驱动程序失败。" );
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.err.println("无法连接数据库" );
e.printStackTrace();
}
}
// 查询
public ResultSet Search(String sql, String str[]) {
DataBase();
try {
PreparedStatement pst =con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
res = pst.executeQuery();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
}
// 增删修改
public int AddU(String sql, String str[]) {
int a = 0;
DataBase();
try {
PreparedStatement pst = con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
a = pst.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
}
表的实体如下:
package com.javen.entity;
/**
* @author
* @Email
*
*/
public class StuEntity {
private int id;
private String name;
private String sex;
private int num;
public StuEntity() {
}
public StuEntity(int id, String name, String sex, int num) {
this.id = id;
this.name = name;
this.sex = sex;
this.num = num;
}
@Override
public String toString() {
return "StuEntity [id=" + id + ", name=" + name + ", sex=" + sex
+ ", num=" + num + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
}
数据库里的数据导入到Excel
package com.javen.excel;
import java.util.List;
import com.javen.db.DBhepler;
import com.javen.entity.StuEntity;
import com.javen.service.StuService;
/**
* @author
* @Email
*
*/
public class TestExcelToDb {
public static void main(String[] args) {
//得到表格中所有的数据
List<StuEntity> listExcel=StuService.getAllByExcel("e://stu.xls");
/*//得到数据库表中所有的数据
List<StuEntity> listDb=StuService.getAllByDb();*/
DBhepler db=new DBhepler();
// System.out.println(listExcel.get(0).getName());
for (StuEntity stuEntity : listExcel) {
int id=stuEntity.getId();
if (!StuService.isExist(id)) {
//不存在就添加
String sql="insert into stu (name,sex,num) values(?,?,?)";
String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};
db.AddU(sql, str);
}else {
//存在就更新
String sql="update stu set name=?,sex=?,num=? where id=?";
String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""};
db.AddU(sql, str);
}
}
}
}
效果演示: