CODEWITHSUNDEEP
javamysqldatabase
Dhirendra Singh Bisht
Dhirendra Singh Bisht

Reputation: 41

How to use textfield input in mysql SELECT query

I am using Java netbeans and mysql. I want to check whether the value entered by the user in a textfield tf is already present in the mysql table or not.

 String query1="SELECT * FROM trytable WHERE name='8'";
 ResultSet rs=stmt.executeQuery(query1);
if(rs.isBeforeFirst()==true){JOptionPane.showMessageDialog(null,"already");}

In the above code in place of 8 I want to give the value that the user input in the form and then check whether that value already exist in form or not.

Please help me in the first line . Thanks

Upvotes: 1

Views: 817

Answers (3)

Zephyr
Zephyr

Reputation: 10253

You should use a PreparedStatement instead of a regular statement. This is more secure than a normal Statement and allows you to avoid SQL injection issues.

You would change your query like so:

String query = "SELECT * FROM trytable WHERE name='?';";

Note the ? at the end of the query. This can be replaced later in your code when setting up the PreparedStatement:

PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, userInput);

ResultSet rs = preparedStatement.executeQuery();
if (rs.next()) System.out.println("Record exists!");

Here, you are telling the prepared statement to replace the first ? in the query, with the value of userInput. So, if the user inputs a 3, the query that gets executed would be SELECT * FROM trytable WHERE name=3;.

Also note that rs.next() returns true if the query returns any results, so that would be the proper way to determine if the record exists.

Upvotes: 1

Dhirendra Singh Bisht
Dhirendra Singh Bisht

Reputation: 41

This is the final code will is working absolutely fine. 

try{
                    Class.forName("com.mysql.jdbc.Driver");
                    Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","");
                    String query = "SELECT * FROM table_name WHERE name=?;";
                    PreparedStatement preparedStatement = conn.prepareStatement(query);
                    preparedStatement.setString(1,jtf.getText());
                    ResultSet rs = preparedStatement.executeQuery();
                    if(rs.next()==true){
                        JOptionPane.showMessageDialog(null,"Value already exist");
                    }
                    else{
                        JOptionPane.showMessageDialog(null,"Value not present");
                        String query1="INSERT INTO table_name(col_name) VALUES (?)";
                        preparedStatement = conn.prepareStatement(query1);
                        preparedStatement.setString(1,jtf.getText());
                        preparedStatement.execute();
                        JOptionPane.showMessageDialog(null,"DONE");

                    }
                    rs.close();
                    preparedStatement.close();
                }
                catch(Exception e){
                    System.out.println("Exception:"+e.getMessage());
                }

Upvotes: 0

Roshana Pitigala
Roshana Pitigala

Reputation: 8786

ResultSet is like a table, it has a cursor. At the beginning the cursor is above the first row so isBeforeFirst() will always return true even there are no results in the ResultSet.

In order to retrieve results you need to move the cursor to the next row, to do that you can use,

rs.next()

If the cursor moved to the next row successfully (which means there are more results) it will return true otherwise false. As you only need the first result you can also use,

rs.first()

to confirm there are data available in the returned ResultSet.

Try,

if (rs.first()) {
  JOptionPane.showMessageDialog(null, "already");
}

Upvotes: 0

Related Questions