PreparedStatement's setInt() not working on PostgreSQL

Question

I'm using PostgreSQL 9.1 with Tomcat 7 on Eclipse. The connection is made through a connection pool. When I use the setInt() method for the PreparedStatement, nothing is returned. When printing the prepared statement, the '?' is replaced by some weird String I suppose is a reference to the object. When I use the method setObject() with the appropriate Type.INTEGER, the printed statement contains the correct value, but as a String, and forcefully an Int is required.

Here's the code:

public User_DTO[] select(User_DTO params, int order, int limit, int offset)
{
    User_DTO result = null;
    ArrayList<User_DTO> result_arr = new ArrayList<User_DTO>(0);

    String query = "SELECT id, name, role FROM Usuario WHERE name LIKE ? AND role LIKE ? AND id != ? ORDER BY ? LIMIT ? OFFSET ?";

    String name = params.getName();
    String role = params.getRole();
    String id = params.getId();

    try
    {
        conn = Pool.getConnection();
        stmt = conn.prepareStatement(query);
    
        stmt.setString(1, "%" + name + "%");
        stmt.setString(2, "%" + role + "%");
        stmt.setString(3, id);

                // Using setInt()
                // stmt.setInt(4, order);

                // Using setObject
        stmt.setObject(4, order, Types.INTEGER);
        System.out.println(order);
    
        if(limit == -1)
        {
            stmt.setNull(5, Types.NULL);
        }
        else
        {
            stmt.setInt(5, limit);
        }
    
        stmt.setInt(6, offset);

        //
        System.out.println(stmt);
    
        rs = stmt.executeQuery();
    
        while(rs.next())
        {
            result = new User_DTO();
        
            result.setId(rs.getString("id"));
            result.setName(rs.getString("name"));
            result.setRole(rs.getString("role"));
        
            result_arr.add(result);
        }
    }
    catch(SQLException sql_e)
    {
        sql_e.printStackTrace();
    }
    finally
    {
        Pool.close(conn, stmt, rs);
    }

    return (result != null) ? result_arr.toArray(new User_DTO[result_arr.size()]) : null;
}

The printed statement using setInt() is this:

SELECT id, name, role FROM Usuario WHERE name LIKE '%%' AND role LIKE '%%' AND id != '13253cc9' ORDER BY '[B@65adcbab' LIMIT '[B@75167bb3' OFFSET '[B@171360d3'

And the one printed using setObject():

SELECT id, name, role FROM Usuario WHERE name LIKE '%%' AND role LIKE '%%' AND id != '13253cc9' ORDER BY '1' LIMIT '[B@75167bb3' OFFSET '[B@171360d3'

Any idea why this might be happening?

Answer 1

Two quick thoughts on this.

1. The string you are seeing is the Integer object converted to a string.
2. Why are you trying to order the result by an Integer?

I think you have the number of parameters wrong. You have to order by a field of the tables included in the query, therefore you should not be giving the 4th parameter as an integer. In fact, you should probably have a fixed ordering.