java - 将数据插入数据库 Java JDBC

Question

我正在学习 Java JDBC，并且正在使用循环将我的数据存储到数据库 (mySQL db) 中。当我存储个人的名字和姓氏时，它似乎工作正常，但当我尝试插入电子邮件地址时，出现以下错误:

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 '@123.com)'

据我所知，除非我遗漏了一些非常明显的东西，否则我看不出我犯了什么语法错误？

我的代码如下:

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;

public class Driver {
    public static void main(String[] args) {
        try{
            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:8889/demo","root","root");
            Statement myStmt = myConn.createStatement();
            ArrayList<String> firstNames = new ArrayList<String>(Arrays.asList("James", "John","Mark"));
            ArrayList<String> lastNames = new ArrayList<String>(Arrays.asList("Handly", "licks","manford"));
            ArrayList<String> emails = new ArrayList<String>(Arrays.asList("james@123.com", "John@45.co.uk","Markus@marc.com"));

            for (int i = 0; i < firstNames.size(); i++){
                String sql = "INSERT INTO EMPLOYEES (first_name,last_name,email) VALUES (" + firstNames.get(i)+ ", " + lastNames.get(i) + ", " + emails.get(i) + ");";
                myStmt.executeUpdate(sql);
            }
            ResultSet myRes = myStmt.executeQuery("select * from Employees");
            while(myRes.next()){
                System.out.println(myRes.getString("first_name")+", "+ myRes.getString("last_name"));
            }
        }catch(Exception e){
             e.printStackTrace();
       }
    }
}

当我尝试一次插入一个数据时，例如

String sql = "INSERT INTO EMPLOYEES (first_name,last_name,email) VALUES ('John','test','test@test.com')";
myStmt.executeUpdate(sql);

那工作正常，所以我很困惑为什么数据没有被正确传递。谢谢!

编辑:感谢@scaisEdge 和@A.A 的反馈，虽然我正在寻找的修复确实有效，但使用字符串文字是一个BAD 的想法，因为您打开它用于 SQL 注入(inject)。反过来，我现在已经修改了我的代码，使用准备好的语句(@A.A 的回答)，这很有效，而且问题少了很多!

新代码如下:

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;

public class Driver {
    public static void main(String[] args) {

        Connection myConn = null;
        PreparedStatement myStmt = null;
        ResultSet myRs = null;
        try{
            //1.get connection to db
            myConn = DriverManager.getConnection("jdbc:mysql://localhost:8889/demo","root","root");

            ArrayList<String> firstNames = new ArrayList<String>(Arrays.asList("James", "John","Mark"));
            ArrayList<String> lastNames = new ArrayList<String>(Arrays.asList("Handly", "licks","manford"));
            ArrayList<String> emails = new ArrayList<String>(Arrays.asList("james@123.com", "John@45.co.uk","Markus@marc.com"));
            for (int i = 0; i < firstNames.size(); i++){

                //Insert Query
                myStmt = myConn.prepareStatement("INSERT INTO EMPLOYEES (first_name,last_name,email) VALUES (?,?,?)");

                myStmt.setString(1,firstNames.get(i));
                myStmt.setString(2,lastNames.get(i));
                myStmt.setString(3,emails.get(i));
                myStmt.execute();
            } 
            ResultSet myRes = myStmt.executeQuery("select * from Employees");
            while(myRes.next()){
                System.out.println(myRes.getString("first_name")+", "+ myRes.getString("last_name"));
            }
            myConn.close();
        }catch(Exception e) {
             e.printStackTrace();
        }
    }
}

Answer 1

我还建议使用准备好的语句，它不仅更具可读性，而且还能防止您遭受 SQL 注入(inject)攻击。更多信息 here

例子:

preparedStatement = connection.prepareStatement("INSERT INTO EMPLOYEES (fname, lname, email) VALUES (?, ?, ?)");
preparedStatement.setString(1, person.getFName());
preparedStatement.setString(2, person.getLName());
preparedStatement.setString(3, person.getEmail());