servlet层：
package servlet;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.swing.*;
import dao.Import;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.json.JSONObject;
import org.junit.Test;
@WebServlet("/UploadServlet")
public class UploadServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
JSONObject jsonObject=new JSONObject();
response.setContentType("text/html");
PrintWriter out = response.getWriter();
DiskFileItemFactory sf= new DiskFileItemFactory();//实例化磁盘被文件列表工厂
String path = request.getRealPath("/upload");//得到上传文件的存放目录
System.out.println(path);
sf.setRepository(new File(path));//设置文件存放目录
sf.setSizeThreshold(1024*1024);//设置文件上传小于1M放在内存中
String rename = "";//文件新生成的文件名
String fileName = "";//文件原名称
String name = "";//普通field字段
//从工厂得到servletupload文件上传类
ServletFileUpload sfu = new ServletFileUpload(sf);
try {
List<FileItem> lst = sfu.parseRequest(request);//得到request中所有的元素
for (FileItem fileItem : lst) {
if(fileItem.isFormField()){
if("name".equals(fileItem.getFieldName())){
name = fileItem.getString("UTF-8");
}
}else{
//获得文件名称
fileName = fileItem.getName();
System.out.println(fileName);
fileName = fileName.substring(fileName.lastIndexOf("\\")+1);
String houzhui = fileName.substring(fileName.lastIndexOf("."));
System.out.println(houzhui);
rename = UUID.randomUUID()+houzhui;
System.out.println(rename);
fileItem.write(new File(path, rename));
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
path = path + "\\" + rename;
System.out.println(path);
//调用dao层函数
Import excelImport = new Import();
int i = excelImport.readExcel(path);
System.out.println(i);
if (i == 1) {
jsonObject.put("code", 0);
response.getWriter().write(jsonObject.toString());
response.getWriter().close();
}
else if (i == 0){
jsonObject.put("code", 1);
response.getWriter().write(jsonObject.toString());
response.getWriter().close();
}
}
}
dao层：
package dao;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.*;
public class Import {
public void importSql(String importPath) throws IOException {
String username = "root";//用户名
String password = "123456";//密码
String host = "127.0.0.1";//导入的目标数据库所在的主机
String port = "3306";//使用的端口号
String importDatabaseName = "book";//导入的目标数据库的名称
String MysqlPath = "E:/mysql/mysql-8.0.25-winx64/bin/";
String loginCommand = new StringBuffer().append(MysqlPath).append("mysql -h").append(host).append(" -u").append(username).append(" -p").append(password)
.append(" -P").append(port).toString();
//第二步，获取切换数据库到目标数据库的命令语句
String switchCommand = new StringBuffer().append("use ").append(importDatabaseName).toString();
//第三步，获取导入的命令语句
String importCommand = new StringBuffer(" source ").append(importPath).toString();
//需要返回的命令语句数组
String[] commands = new String[] {loginCommand, switchCommand, importCommand};
Runtime runtime = Runtime.getRuntime();
Process process = runtime.exec(commands[0]);
//执行了第一条命令以后已经登录到mysql了，所以之后就是利用mysql的命令窗口
java.io.OutputStream os = process.getOutputStream();
OutputStreamWriter writer = new OutputStreamWriter(os);
//命令1和命令2要放在一起执行
// 这里会执行后面的代码， 将命令输出到mysql的命令窗口，进行执行
writer.write(commands[1] + "\r\n" + commands[2]);
System.out.println(commands[1]+commands[2]+commands[0]);
writer.flush();
writer.close();
os.close();
}
//导入excel
public int readExcel(String path) {
File file = new File(path);
FileInputStream fis = null;
Workbook workBook = null;
Statement statement=null;
PreparedStatement pStemt = null;
if (file.exists()) {
try {
/**获取msyql连接类型*/
Connection conn =sql();
if (!conn.isClosed()){statement = conn.createStatement();}
workBook = WorkbookFactory.create(new FileInputStream(new File(path)));
int numberOfSheets = workBook.getNumberOfSheets();
// sheet工作表
for (int s = 0; s < numberOfSheets; s++) {
Sheet sheetAt = workBook.getSheetAt(s);
//获取工作表名称
String sheetName = sheetAt.getSheetName();
//获取当前Sheet的总行数
int rowsOfSheet = sheetAt.getPhysicalNumberOfRows();
System.out.println("当前表格的总行数:" + rowsOfSheet);
int coloumNum=sheetAt.getRow(0).getPhysicalNumberOfCells();
System.out.println("总列数为"+coloumNum);
Row cellrow = sheetAt.getRow(2);
String sqladd1="create table "+sheetName;
for (int i =0;i <coloumNum;i++)
{
Cell cell = cellrow.getCell(i);
if (i==0){
// sqladd1 = sqladd1 + "(" + cell + " text " + "not null primary key,";
sqladd1 = sqladd1 + "(" + cell + " text,";
}
else if (i==coloumNum-1){
sqladd1 = sqladd1 + cell + " text)";
}
else {
sqladd1 = sqladd1 + cell +" text,";
}
}
System.out.println(sqladd1);
statement.execute(sqladd1);
//插入数据
DatabaseMetaData metaData = conn.getMetaData();
ResultSet tableInfo = metaData.getColumns("book","%", sheetName, "%");
while (tableInfo.next()) {
String insertclumn = "insert into col values ('" + sheetName+"'"
+ ",'" + tableInfo.getString("COLUMN_NAME")+"'"
+ ",'" + tableInfo.getString("COLUMN_DEF")+"'"
+ ",'" + tableInfo.getString("TYPE_NAME")+"'"
+ ",'" + tableInfo.getString("IS_NULLABLE")+"'"
+ ",'" + tableInfo.getString("IS_AUTOINCREMENT")+"'"
+ "," + null
+ ",'" + tableInfo.getString("COLUMN_SIZE")+"'"
+ "," + null
+ ")";
System.out.println(insertclumn);
PreparedStatement ps=conn.prepareStatement(insertclumn);
ps.execute();
}
//修改col的明细
for (int i =0; i<coloumNum;i++) {
String updatecol = "update col set ";
Row row1 = sheetAt.getRow(0);
Row row2 = sheetAt.getRow(1);
Row row3 = sheetAt.getRow(2);
Cell cell1 = row1.getCell(i);
Cell cell2 = row2.getCell(i);
Cell cell3 = row3.getCell(i);
updatecol = updatecol + "remarks = '" + cell1 + "',"
+ "danwei = '" + cell2 + "' where column_name = '" + cell3+"'";
System.out.println(updatecol);
PreparedStatement ps=conn.prepareStatement(updatecol);
ps.execute();
}
// String insertsql = "insert into " + sheetName + " values(";
for (int r = 3; r <rowsOfSheet; r++){
String insertsql = "insert into " + sheetName + " values(";
Row row = sheetAt.getRow(r);
for (int i =0; i<coloumNum;i++){
Cell cell = row.getCell(i);
if (i==0){
insertsql = insertsql +"'"+ cell +"',";
}
else {
if (i == coloumNum - 1) {
insertsql = insertsql + "'"+ cell +"'" + ")";
} else {
insertsql = insertsql + "'"+ cell +"'" + ",";
}
}
}
System.out.println(insertsql);
PreparedStatement ps=conn.prepareStatement(insertsql);
ps.execute();
}
String insertcol = "insert into cols(colname) values('"+sheetName+"')";
System.out.println(insertcol);
PreparedStatement ps1=conn.prepareStatement(insertcol);
ps1.execute();
}
return 1;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
} else {
System.out.println("文件不存在!");
return 0;
}
}
public static Connection sql() {
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/book?useUnicode=yes&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=UTC&useSSL=false";
String user = "root";
String password = "123456";
Connection conn=null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}