Inserting data into database Java JDBC

Question

I am learning Java JDBC, and am using a loop to store my data into a database (mySQL db). When I am storing the first name and last name of the individual, it seems to work fine, but when I try to insert email addresses, I get the following Error:

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)'

To the best of my knowledge, unless I am missing something INCREDIBLY obvious, I can't see what syntax error I have made?

My code is as follows:

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("[email protected]", "[email protected]","[email protected]"));

            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();
       }
    }
}

When I tried inserting data one at a time e.g.

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

That was working fine, so I am confused as to why the data is not being passed in correctly. Thanks!

EDIT: Thanks to feedback from @scaisEdge and @A.A, although the fix that I was looking for did work, using string literals is a BAD idea as this you open for SQL injections. In turn, I have now modified my code, using Prepared statements (@A.A 's answer) , which has worked, and is a lot less problematic!

the new code is as follows:

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("[email protected]", "[email protected]","[email protected]"));
            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();
        }
    }
}

Use PreparedStatement (docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) to avid problems like this. It will add quotes for you automatically and escape things which could brake statement. — Pshemo
– Pshemo, Commented Nov 25, 2017 at 17:09
I just came stumbled up on that doc page right now whilst looking for an answer and was going to try that next :) thanks! — carl saptarshi
– carl saptarshi, Commented Nov 25, 2017 at 17:10

A.A · Accepted Answer · 2020-02-10 14:14:59Z

6

I would also recommend using prepared statements, not only is it more readable but also will prevent you from SQL injection attacks. More info here

Example:

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

edited Feb 10, 2020 at 14:14

answered Nov 25, 2017 at 17:10

A.A

8331 gold badge10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kinaeh Over a year ago

there is a typo, you set String 2 twice. I can't edit it unfortunately.

Collectives™ on Stack Overflow

Inserting data into database Java JDBC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related