Java - sending variable Insert data to MySql database

Question

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

Harmeet Singh · Accepted Answer · 2012-08-22 18:23:29Z

2

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

answered Aug 22, 2012 at 18:23

Harmeet Singh

2,61619 silver badges21 bronze badges

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

1 Comment

user1617807 Over a year ago

Thank you!.. Great help from everyone, yes, I figured, I would have to get my head around the syntax for the prepared statements. And working in the security field, but I really don't write much code. I know how easy it is for Sql Injection. This is mostly a test project, for a simple school class, that offers, well not that much in the way of help.

hvgotcodes · Accepted Answer · 2012-08-22 23:51:12Z

1

So many potential errors.

missing a closing } to close the class.
are you sure your database is running?
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);

edited Aug 22, 2012 at 23:51

answered Aug 22, 2012 at 18:17

hvgotcodes

121k33 gold badges209 silver badges237 bronze badges

1 Comment

user1617807 Over a year ago

Sorry, yes as far as the code, it works, the sql table gets the updates. I cut a bit of the code off. The main question is how to change those constant values to variables. And from all the answers so far, and thanks everyone, the first step I guess will be to change prepared statements

Tucker · Accepted Answer · 2012-08-22 18:31:08Z

0

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

answered Aug 22, 2012 at 18:31

Tucker

7,38210 gold badges40 silver badges61 bronze badges

2 Comments

user1617807 Over a year ago

Thank you. Yes that is the direction it seems I need to use. So the first part you have where string firstName is set to Cathy, I can then change Cathy to a variable I get from some chosen input?

Tucker Over a year ago

Yes, you can change the value to firstName or lastName to whatever you want and rerun the code. In the example above, i have firstName equal Cathy, and lastName equal to Jones.... not sure I understand your comment

Collectives™ on Stack Overflow

Java - sending variable Insert data to MySql database

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related