PreparedStatement is not matching a record on calling execute()

Question

I am trying to insert a record in the database if it does not exist already. The record exists if the 'description' field matches with the same field of record to be inserted.

In the code, executing the PreparedStatement has no effect on the variable 'created'. The method 'createIdea()' should return true if new record has been inserted otherwise return false (i.e. record already exists).

public static boolean createIdea(User userIns,String description,int categoryId) throws ClassNotFoundException, SQLException { PreparedStatement ps = null; Connection con = DBConnection.getConnection(); ps = con.prepareStatement("SELECT id FROM idea WHERE description = ?"); ps.setString(1, description); boolean found = ps.execute(); // If description exists ps.close(); if(found) { return false; } else { ps = con.prepareStatement("INSERT INTO idea (description, user_id, status, posted_date) VALUES (?, ?, ?, ?)"); ps.setString(1, description); ps.setInt(2, userIns.getId()); ps.setString(3, "Pending"); ps.setDate(4, new java.sql.Date(new java.util.Date().getTime())); int n = ps.executeUpdate(); ps.close(); if(n != 0) { // Other PreparedStatements } } con.close(); return true; }

Table Description:

mysql> desc idea; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | description | text | NO | | NULL | | | user_id | int(11) | NO | MUL | NULL | | | status | varchar(20) | NO | | NULL | | | posted_date | date | NO | | NULL | | +-------------+-------------+------+-----+---------+----------------+

The method createIdea() always returns flase. Where did I go wrong?

What is the boolean value returned by execute()? docs.oracle.com/javase/8/docs/api/java/sql/… — JB Nizet
– JB Nizet, Commented Aug 1, 2019 at 6:05
The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s). Returns: true if the first result is a ResultSet object; false if the first result is an update count or there is no result — Lalit Mehra
– Lalit Mehra, Commented Aug 1, 2019 at 6:12
Did you try running the query on the sql console itself and see what is the result. Since description is a text value even a single space can change the result from the prepared statement. — Lalit Mehra
– Lalit Mehra, Commented Aug 1, 2019 at 6:14
@umer_farooque you didn't answer my question. Read the javadoc I linked to, carefully, and ask yourself when the execute() method returns true and when it returns false. It's right there, in the documentation. Then ask yourself why you use execute() although you know that what you have is a select query, and thus that executQuery() would be much simpler and more appropriate. — JB Nizet
– JB Nizet, Commented Aug 1, 2019 at 6:31
Yes, and that is completely normal and expected if you read the documentation. Have you finally read it? What does it say? When does execute() return true? — JB Nizet
– JB Nizet, Commented Aug 1, 2019 at 6:50

Prince Vegeta · Accepted Answer · 2019-08-01 08:11:12Z

The PreparedStatement#execute from javadoc states:

Returns: true if the first result is a ResultSet object; false if the first result is an update count or there is no result.

It will always return true on a SELECT query.

As Mark Rotteveel pointed out, it will also return true even on an empty ResultSet (which is still a ResultSet). On the contrary, it will return false if there is no ResultSet or there is/is not an update count.

It would be better to use executeQuery() which will return a ResultSet (PreparedStatement#executeQuery).

ResultSet found = ps.executeQuery(); if (!found.isBeforeFirst() ) { return false; }

With that description, it is important to keep in mind that an empty result set is still a result. The "there is no result" in that documentation means that the query produced neither a - possibly empty - result set nor an update count.
@MarkRotteveel Thanks for pointing out that information. WIll update accordingly.
Yeah... Got it working now by using executeQuery() The condition looks like this.. if(found.next()) return false;
@umer_farooque While your approach might be correct, next() will move the cursor to check for other rows in ResultSet. Instead, you can just call isBeforeFirst() to test if there is any row returned without moving the cursor (check if empty or not).

user404 · Accepted Answer · 2019-08-01 07:14:23Z

As Prince Vegeta quoted the doc and said execute() for select query will return true, you can try like shown there or just simply try to check if your resultset has any value returned:

ResultSet rs= ps.executeQuery(); if (rs.next()) { return true; }

Collectives™ on Stack Overflow

PreparedStatement is not matching a record on calling execute()

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related