CODEWITHSUNDEEP
javamysqljdbcarraylist
carl saptarshi
carl saptarshi

Reputation: 355

Inserting data into database Java JDBC

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

Upvotes: 1

Views: 4400

Answers (1)

A.A
A.A

Reputation: 823

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

Upvotes: 6

Related Questions