Why is an error arising in updating info into SQL Database from Java code?

Question

I am writing the code for a sign-up page for a system. Once the button is clicked, the system will check if the user is above the age of 14. If true, the system should save all the inputted data into a SQL Database.

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
       // TODO add your handling code here:
       int age = Integer.parseInt(Age_Input.getText());
       String firstname = FirstName_Input.getText();
       String surname = Surname_Input.getText();
       String email = Email_Input.getText();
       String userid = UserID_InputSignUp.getText();
       char[] pass = Password_InputSignUp.getPassword();

       if (age<14) {
           JOptionPane.showMessageDialog(null,"Sorry, You need to be at least 14 years to use this software... ");
           new Login_Page().setVisible(true);
           this.setVisible(false);
       } else {
           try {
              Class.forName("com.mysql.cj.jdbc.Driver");
              Connection connect = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/user_info", "root", "nerdswonka");
              Statement stmt = connect.createStatement();

              String query1 = "INSERT INTO user_info(user_id, password, firstname, lastname, emailid, age) VALUES('"+userid+"', "+Arrays.toString(pass)+", '"+firstname+"', '"+surname+"', '"+email+"', "+age+");";
              stmt.executeUpdate(query1);

              JOptionPane.showMessageDialog(null, "Account Creation succesful!");
              stmt.close();
              connect.close();

          } catch (Exception e) {
              JOptionPane.showMessageDialog(null, "Error in connecting to SQL Database");
        }

          new Login_Page().setVisible(true);
          this.setVisible(false);
    }

}

The code isn't updating anything into the database and is simply showing JOptionPane after an exception (error ) comes. What edits can be done to the code so that values get stored into SQL?

first change: print the error message to know what it is about... better, also print the stack trace e.printStackTrace() - I suspect that Arrays.toString(pass) is not what the database accepts, probably you want new String(pass) — user85421
– user85421, Commented Sep 20, 2019 at 10:41

Tim Biegeleisen · Accepted Answer · 2019-09-20 11:02:46Z

2

The immediate cause of the failure is probably that your INSERT statement contains the following string which is not being single quoted:

Arrays.toString(pass)

However, you should completely abandon your current approach and instead use a prepared statement:

String sql = "INSERT INTO user_info (user_id, password, firstname, lastname, emailid, age) " +
    "VALUES (?, ?, ?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user_info", "root", "nerdswonka");
    PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, userid);
        ps.setString(2, Arrays.toString(pass));
        ps.setString(3, firstname);
        ps.setString(4, surname);
        ps.setString(5, email);
        ps.setInt(6, age);

        int row = ps.executeUpdate();
        System.out.println(row); // rows inserted (should be 1)

    }
    catch (SQLException e) {
        System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Prepared statements do many things, one of which is handling the messy details about how to properly escape your literal data in a SQL query. In this case, they free you from having to worry about placing single quotes around your interpolated Java strings. Statements also prevent bad things like SQL injection from happening.

edited Sep 20, 2019 at 11:02

answered Sep 20, 2019 at 10:41

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

9 Comments

user85421 Over a year ago

yes, beware of Little Bobby Tables [:-)

Vittal Kamath Over a year ago

I tried your prepared statement code. A red error line is appearing below 'user_id' in line 5 of your code. In line 2 of your code, should I also use single quotes and double quotes around the values?

Tim Biegeleisen Over a year ago

@VittalKamath I fixed a typo, I had user_id instead of userid. No, don't put single quotes anywhere. The VALUES are represented in the statement by ? placeholders. You don't need to worry about how to format anything.

Vittal Kamath Over a year ago

SQL State: S1009 Parameter index out of range (1 > number of parameters, which is 0). I am getting this error after running your code...

Tim Biegeleisen Over a year ago

I don't know what you are running, but my code should not be generating this error.

|

vaibhav shanker · Accepted Answer · 2019-09-20 10:45:43Z

0

You are not printing your exception in catch block. You can use e.printStackTrace(); to print exception first.

answered Sep 20, 2019 at 10:45

vaibhav shanker

1732 silver badges11 bronze badges

Collectives™ on Stack Overflow

Why is an error arising in updating info into SQL Database from Java code?

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related