CODEWITHSUNDEEP
javamysqlinsert
user1617807
user1617807

Reputation: 1

Java - sending variable Insert data to MySql database

I have a simple program where I want to take a user supplied first name and last name, then enter those into a MySql database. The two variables, call them first and last, will be simple command line prompts, so scanner first then scanner last. Then take first and last and add them to my MySql statement. Here is the code I have, but for some reason I cannot get the syntax correct. In this example I'm using statments, although I can and would use prepared statements. In the real world I would use prepared statements, but even a statement would work for this project.

Here is my code with one scanner line. Right now the code does work with the two constant values Cathy and Jones. I want those to be variables.

class AddStudent 
{ 

    public static void main (String[] args) 
    { 
        try
        { 

            Connection conn = DriverManager.getConnection(url,user,pass); 
            Statement st = conn.createStatement(); 
            Scanner first = new Scanner(System.in);
            System.out.println(first.nextLine());


            String SQL = "INSERT INTO test VALUES ('Cathy', 'Jones')";
            st.executeUpdate(SQL);



              conn.close(); 

        } catch (Exception e) { 
            System.err.println("Got an exception! "); 
            System.err.println(e.getMessage()); 
        } 

    }

    private static String url = "jdbc:mysql://localhost:3306/registrar";

Upvotes: 0

Views: 11206

Answers (3)

hvgotcodes
hvgotcodes

Reputation: 120168

So many potential errors.

  1. missing a closing } to close the class.
  2. are you sure your database is running?
  3. Did you create the table test?

4. Your scanner is amok. 

 Scanner scanner = new Scanner(System.in);
 String first = scanner.nextLine();
 String last = scanner.nextLine();

5.

String SQL = "INSERT INTO test VALUES ('Cathy', 'Jones')";
st.executeUpdate(SQL);

should be

String SQL = String.format("INSERT INTO test VALUES ('%s', '%s')", first, last);
st.executeUpdate(SQL);

Upvotes: 1

Tucker
Tucker

Reputation: 7362

The best way to do it is the following way. This is code below achieves what you want and prevents SQL injections and other problems caused by malicious or unexpected input by a user.

String firstName = "Cathy";
String lastName = "Jones";
String query= "INSERT INTO test VALUES (?,?)";
PreparedStatement stmt = mysqlConnection.prepareStatement(query);
stmt.setString(1,firstName);
stmt.setString(2,lastName);
stmt.executeUpdate(SQL);

Upvotes: 0

Harmeet Singh
Harmeet Singh

Reputation: 2616

Here you go, but its not recommended to go with Statement

Scanner first = new Scanner(System.in);
String f = first.nextLine();

Scanner last = new Scanner(System.in);
String l = last.nextLine();    

String SQL = "INSERT INTO test VALUES ('" + f + "','" + l + "')";
st.executeUpdate(SQL);

Its recommended like this :

PreparedStatement ps = conn.prepareStatement("INSERT INTO test VALUES (?, ?)");
ps.setString(1, f);
ps.setString(2, l);
ps.executeUpdate();

java reference

Upvotes: 2

Related Questions