CODEWITHSUNDEEP
javamysqljdbc
umer_farooque
umer_farooque

Reputation: 3

PreparedStatement is not matching a record on calling execute()

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?

Upvotes: 0

Views: 690

Answers (2)

Prince Vegeta
Prince Vegeta

Reputation: 719

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

Upvotes: 2

user404
user404

Reputation: 2028

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

Upvotes: 0

Related Questions