Java导入Excel数据到MySQL关于编码

Question

这是我的来源!!

String url    =   "jdbc:mysql://localhost/dahan?characterEncoding=euckr";
String user    =   "root";
String password   =   "pass";

    try{
        Class.forName("com.mysql.jdbc.Driver");  
        Connection con = (Connection)                                                      DriverManager.getConnection(url,user,password);  
        con.setAutoCommit(false);  
        PreparedStatement pstm = null ; 
        PreparedStatement pstm1 = null ; 
        PreparedStatement pstm2 = null ; 
        FileInputStream input = new FileInputStream("C:/Users/hyunwoo/Downloads/Personal Contacts.xls");  
        POIFSFileSystem fs = new POIFSFileSystem( input );  
        HSSFWorkbook wb = new HSSFWorkbook(fs);  
        HSSFSheet sheet = wb.getSheetAt(0);
    Row row;
    String del = "DROP TABLE IF EXISTS `dahanMail`";
    String cre = "CREATE TABLE `dahanMail` (`전체이름`  varchar(50),`성`  varchar(50),`이름`  varchar(50) ,`닉네임`  varchar(50),"
            + "`회사`  varchar(500),`부서`  varchar(500),`직급`  varchar(500),`메일주소1`  varchar(50),"
            + "`메일주소2`  varchar(50),`전화번호1`  varchar(50),`전화번호2`  varchar(50),`전화번호3`  varchar(50),"
            + "`휴대폰1`  varchar(50),`휴대폰2`  varchar(50),`팩스1`  varchar(50),`팩스2`  varchar(50),"
            + "`주소`  varchar(50),`웹사이트`  varchar(50),`id`  int NOT NULL ,PRIMARY KEY (`id`))";
    pstm1 = (PreparedStatement) con.prepareStatement(del);
    pstm2 = (PreparedStatement) con.prepareStatement(cre);
    pstm1.execute();
    pstm2.execute();
for(int i=0; i<=sheet.getLastRowNum(); i++){
        row = sheet.getRow(i);
        String fullname = row.getCell(0).getStringCellValue();
        String lastname = row.getCell(1).getStringCellValue();
        String firstname = row.getCell(2).getStringCellValue();
        String nickname = row.getCell(3).getStringCellValue();
        String company = row.getCell(4).getStringCellValue();
        String sub = row.getCell(5).getStringCellValue();
        String hei = row.getCell(6).getStringCellValue();
        String mailaddress1 = row.getCell(7).getStringCellValue();
        String mailaddress2 = row.getCell(8).getStringCellValue();
        String cnumber1 = row.getCell(9).getStringCellValue();
        String cnumber2 = row.getCell(10).getStringCellValue();
        String cnumber3 = row.getCell(11).getStringCellValue();
        String pnumber1 = row.getCell(12).getStringCellValue();
        String pnumber2 = row.getCell(13).getStringCellValue();
        String fax1 = row.getCell(14).getStringCellValue();
        String fax2 = row.getCell(15).getStringCellValue();
        String address = row.getCell(16).getStringCellValue();
        String website = row.getCell(17).getStringCellValue();
        int id = (int) row.getCell(18).getNumericCellValue();

        String sql = "INSERT INTO dahanmail VALUES('"+fullname+"','"+lastname+"','"+firstname+"'"
                        + ",'"+nickname+"','"+company+"','"+sub+"','"+hei+"','"+mailaddress1+"','"+mailaddress2+"','"+cnumber1+"','"+cnumber2+"','"+cnumber3+"'"
                                        + ",'"+pnumber1+"','"+pnumber2+"','"+fax1+"','"+fax2+"',"
                                                + "'"+address+"','"+website+"','"+id+"')";

       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");

我已经使用Java中的POI将excel数据保存到mysql而且效果很好!所以我在cmd中检查了mysql表但控制台窗口仍然显示损坏的语言，例如“？？？”看来是编码问题...如何更改源来解决编码问题？

Answer 1

你所拥有的看起来已经接近工作，但有一些事情我会改变:

首先，在循环之前声明您的 SQL，并使用 ? 来表示值占位符:

String sql = "INSERT INTO dahanmail VALUES(?,?,?)";
PreparedStatement pstm = conn.prepareStatement(sql);

(我在上面的代码中没有放置足够的 ?，每个值都应该有一个)

然后，在循环中，可以设置参数:

pstm.setString(1,row.getCell(0).getStringCellValue());
pstm.setString(2,row.getCell(2).getStringCellValue());
//one for each value. You could use another loop.

然后(仍在循环中)，执行语句:

pstm.execute();

最后清除下一轮的参数:

pstm.clearParameters();