Resultset has error for Java Jframe Swing

Question

// TODO add your handling code here:
PreparedStatement st = null;
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xaramat","root","");
    Statement state = con.createStatement();
    Statement stat = con.createStatement();
    String Username=Username_txt.getText();
    String Password=Password_txt.getText();
    String Role=this.Role.getSelectedItem().toString();
    String sql2="SELECT * FROM admin WHERE Username = '" + Username + "'";
    PreparedStatement pstmt = con.prepareStatement(sql2);
    ResultSet res=pstmt.executeQuery(sql2);
    if(res.next()){
        String sql="INSERT INTO admin(Username,Password,Type)"
                + "VALUES ('"+Username+"','"+Password+"','"+Role+"') ";
        state.executeUpdate(sql);
        JOptionPane.showMessageDialog(this, "Account has been added Succsesfully!");
        Username_txt.setText("");
        Password_txt.setText("");
        this.Role.setSelectedIndex(0);
    }
} catch (Exception e) {
    System.out.println(e);
}

I want to check if the username is taken. The username field is unique and if it's not taken then I want to insert it.

The is not how to use a PreparedStatement. The point of using a PreparedStatement is to build the statement so you can pass parameter values to the statement. Check out: https://stackoverflow.com/a/24083888/131872 for a basic example. For the insert statement you would also use paramenter instead of hard coding variable values in the string. Also you basic logic is wrong. If you initial select returns a value that means the id already exists, so you don't want to add it again. — camickr, Mar 01 '23 at 17:43
sounds unrelated to swing: implement the database problem in isolation (f.i. with hard-coded values) before using in a UI context. and stick to java naming conventions please — kleopatra, Mar 02 '23 at 00:40

score 0 · Accepted Answer · answered Mar 01 '23 at 19:30

For your information, the following line of your code is no longer needed:

Class.forName("com.mysql.cj.jdbc.Driver");

You want to avoid entering the same USERNAME twice. I assume that you have defined USERNAME as the primary key for [database] table ADMIN. If you have then simply perform the insert and catch the duplicate entry error.

try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xaramat","root","");
     PreparedStatement ps = con.prepareStatement("insert into ADMIN (USERNAME, PASSWORD, TYPE) values (?, ?, ?)")) {
    ps.setString(1, username);
    ps.setString(2, password);
    ps.setString(3, role);
    ps.executeUpdate();
}
catch (SQLException xSql) {
    int code = xSql.getErrorCode();
    if (code == 1062) {
        System.out.println(xSql.getMessage());
    }
    else {
        xSql.printStackTrace();
    }
}

The above code uses try-with-resources. You may also want to refer to the JDBC trail which is part of Oracle's Java tutorials.

However, if you want to first check whether a certain USERNAME already exists in [database] table ADMIN, do a count query – which is guaranteed to return a result. If the count is zero, then perform the insert.

try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xaramat","root","");
     PreparedStatement ps = con.prepareStatement("select count(*) from ADMIN where USERNAME = ?")) {
    ps.setString(1, username);
    ResultSet rs = ps.executeQuery();
    rs.next();
    int count = rs.getInt(1);
    if (count == 0) {
        try (PreparedStatement ps2 = con.prepareStatement("insert into ADMIN (USERNAME, PASSWORD, TYPE) values (?, ?, ?)")) {
            ps2.setString(1, username);
            ps2.setString(2, password);
            ps2.setString(3, role);
            ps2.executeUpdate();
        }
    }
}
catch (SQLException xSql) {
    xSql.printStackTrace();
}

Resultset has error for Java Jframe Swing

1 Answers1