How to use textfield input in mysql SELECT query

Question

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

So you have two issues: How to declare a variable in Java netbeans, and how to query a database? For the second point, what if someone inserts '8' while you're in the middle of checking? — Strawberry
– Strawberry, Commented Jul 29, 2018 at 5:02

Zephyr · Accepted Answer · 2018-07-29 19:51:24Z

1

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.

edited Jul 29, 2018 at 19:51

answered Jul 29, 2018 at 4:59

Zephyr

10.3k5 gold badges37 silver badges76 bronze badges

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

6 Comments

Strawberry Over a year ago

Note that will also return usernames like '3alpha'

Zephyr Over a year ago

@Strawberry - How would it return that?

Zephyr Over a year ago

I see. I made the assumption that name would be an integer type. Probably a poor assumption, so I updated my answer to include the '. Thank you :)

Strawberry Over a year ago

Hm, you think ;-)

Strawberry Over a year ago

But I don't think that's the right way to prepare strings

|

Roshana Pitigala · Accepted Answer · 2018-07-29 05:49:08Z

0

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

edited Jul 29, 2018 at 5:49

answered Jul 29, 2018 at 5:43

Roshana Pitigala

8,8569 gold badges53 silver badges87 bronze badges

6 Comments

Dhirendra Singh Bisht Over a year ago

I tried but its not fixed yet. Please check: Code: imgur.com/a/McRnHb3 Error: imgur.com/a/FkjYanN

Roshana Pitigala Over a year ago

@DhirendraSinghBisht Which SQL statement gives the error?

Dhirendra Singh Bisht Over a year ago

@Zephyr Thanks to you your codes and explanation helped me a lot.

Dhirendra Singh Bisht Over a year ago

Thanks to you your codes and explanation helped me a lot.

Roshana Pitigala Over a year ago

@DhirendraSinghBisht My pleasure :)

|

Dhirendra Singh Bisht · Accepted Answer · 2018-07-29 15:38:38Z

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

Collectives™ on Stack Overflow

How to use textfield input in mysql SELECT query

3 Answers 3

6 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related