java 使用java将excel电子表格数据导入MYSQL数据库

import excel spreadsheet data into MYSQL database using java

提问by meghna

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.hssf.usermodel.HSSFSheet;

public class assessmentdatabase_3 {
    public static void main(String[] args) throws Exception {

        try {

　　　     Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/database", "root",
                    "pass");
            con.setAutoCommit(false);
            PreparedStatement pstm = null;
            FileInputStream input = new FileInputStream("filepath");
            POIFSFileSystem fs = new POIFSFileSystem(input);
            HSSFWorkbook wb = new HSSFWorkbook(fs);

            HSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {

                row = sheet.getRow(i);
                String Process = row.getCell(0).getStringCellValue();
                double Level = row.getCell(2).getNumericCellValue();
                double A = row.getCell(3).getNumericCellValue();
                double B = row.getCell(4).getNumericCellValue();
                double C = row.getCell(5).getNumericCellValue();


                String sql = "INSERT INTO description_process_level VALUES('" + Process + "','" + Level + "','" + A + "',`" + B + "`,`" + C +)";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
                System.out.println("Import rows " + i);
            }
            con.commit();
            pstm.close();
            con.close();
            input.close();
            System.out.println("Success import excel to mysql table");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

I have debug the entire code and it is compiling till line 47 and showing the error at line 48. I want to import this data to mysql.

我已经调试了整个代码，它一直编译到第 47 行并在第 48 行显示错误。我想将此数据导入 mysql。

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '61.166666666666664`,`75.25`,'61.166666666666664',`61.166666666666664`,`75.25`)' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
    at fibb.assessmentdatabase_3.main(assessmentdatabase_3.java:48)

回答by YCF_L

Change your Query with this:

用这个改变你的查询：

String sql = "INSERT INTO description_process_level (process, level, a, b, c) VALUES('" + Process + "'," + Level + "," + A + "," + B + "," + C +")";

1. You missed " in the end,
2. You don't need to use ' for Double, just for String

1. 你最后错过了“
2. 您不需要将 ' 用于 Double，仅用于 String

I advice to use :

我建议使用：

String sql = "INSERT INTO description_process_level (process, level, a, b, c) VALUES(?, ?, ?, ?, ?)";
pstm = (PreparedStatement) con.prepareStatement(sql);

pstm.setString(1, Process);
pstm.setDouble(2, Level);
pstm.setDouble(3, A);
pstm.setDouble(4, B);
pstm.setDouble(5, C);

Here is an exemple how to use prepared statement:

以下是如何使用准备好的语句的示例：

http://www.mkyong.com/jdbc/jdbc-preparestatement-example-select-list-of-the-records/

http://www.mkyong.com/jdbc/jdbc-preparestatement-example-select-list-of-the-records/