其实将数据表中内容导入数据库可以在可视化界面上直接操作,方便快捷,但是介于工作得原因,需要每天定时入库啥的,这时程序得方便性就可以体现出来了。
假设我们要将长成这个样子的excel数据表中的内容导入MySQL数据库
1. 数据库连接
package JavaToExcel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBC_database {
String driver = "com.mysql.cj.jdbc.Driver";
/**
* test1:数据库名称
* ?serverTimezone=UTC:时区设置
* &autoReconnect=true&useSSL=false:解决establishing SSL Connection问题
* characterEncoding=utf8:解决数据库入库时中文出现乱码问题
*/
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC&autoReconnect=true&useSSL=false&characterEncoding=utf8";//添加?serverTimezone=UTC解决时区报错异常
Connection con = null;
ResultSet res = null;
public void DataBase() {
try {
Class.forName(driver);
System.out.println("创建驱动成功");
con = DriverManager.getConnection(url, "root", "123456");
System.out.println("连接数据库成功");
} catch (ClassNotFoundException e) {
System.err.println("装载 JDBC驱动程序失败。" );
e.printStackTrace();
} catch (SQLException e) {
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) {
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) {
e.printStackTrace();
}
return a;
}
public static void main(String[] args) {
// 调用该类的JDBC_database方法,测试数据库连接是否成功
JDBC_database db5=new JDBC_database();
db5.DataBase();
}
}
2. 数据实体类定义
package JavaToExcel;
/**
*
* @author liq 将数据表中的字段进行封装
*
*/
public class ObjectEntity {
private String zonename;
private int incre_customers;
private int active_num;
private int task_num;
private float complete_rate;
private int ranking;
private int task_gap;
public ObjectEntity() {
super();
}
public ObjectEntity(String zonename, int incre_customers, int active_num, int task_num, float complete_rate, int ranking,
int task_gap) {
super();
this.zonename = zonename;
this.incre_customers = incre_customers;
this.active_num = active_num;
this.task_num = task_num;
this.complete_rate = complete_rate;
this.ranking = ranking;
this.task_gap = task_gap;
}
@Override
public String toString() {
return "StuEntity [zonename=" + zonename + ", incre_customers=" + incre_customers + ", active_num=" + active_num
+ ", task_num=" + task_num + ", complete_rate=" + complete_rate + ", ranking=" + ranking + ", task_gap="
+ task_gap + "]";
}
public String getZonename() {
return zonename;
}
public void setZonename(String zonename) {
this.zonename = zonename;
}
public int getIncre_customers() {
return incre_customers;
}
public void setIncre_customers(int incre_customers) {
this.incre_customers = incre_customers;
}
public int getActive_num() {
return active_num;
}
public void setActive_num(int active_num) {
this.active_num = active_num;
}
public int getTask_num() {
return task_num;
}
public void setTask_num(int task_num) {
this.task_num = task_num;
}
public float getComplete_rate() {
return complete_rate;
}
public void setComplete_rate(