How to extract Object[] and get it into query using preparedstatement setObject()?

Question

might be a silly question but I cant get the query running. This is my code:

ArrayList<Attraction> getAttraction(Object[] obj) throws SQLException
{

    Connection connection = facade.getConnection();

    ArrayList<Attraction> attractions = new ArrayList<>();

    // Check obj value
    System.out.println(java.util.Arrays.toString(obj));

    PreparedStatement ps = connection.prepareStatement("SELECT DISTINCT attra.attractid,attractname,x,y,z,age,weight,"
            + "height,duration,price,status FROM attraction attra "
            + "INNER JOIN attrib_bridge ab ON attra.attractId = ab.attractId "
            + "INNER JOIN attribute attri ON ab.attributeId = attri.attributeid"
            + "WHERE attri.attributename IN ?");

    ps.setString(1, java.util.Arrays.toString(obj));

    ResultSet rs = ps.executeQuery(); 

    while(rs.next()){

        // Initialize attraction object
        Attraction attraction = new Attraction();

        attraction.setAttractionId(rs.getInt(1));
        attraction.setAttractionName(rs.getString(2));
        attraction.setxLocation(rs.getInt(3));
        attraction.setyLocation(rs.getInt(4));
        attraction.setzLocation(rs.getInt(5));
        attraction.setMinAge(rs.getInt(6));
        attraction.setMaxWeight(rs.getInt(7));
        attraction.setMinHeight(rs.getInt(8)); 
        attraction.setRideDuration(rs.getInt(9));
        attraction.setPriceRide(rs.getInt(10));
        attraction.setStatus(rs.getString(11));

        // Add new object to to movies
        attractions.add(attraction);
    }

    return attractions;
}

And this is the error I get :

[Gentle rides, Thrill rides, Transport rides] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in >your SQL syntax; check the manual that corresponds to your MySQL server version >for the right syntax to use near 'attri.attributename IN '[Gentle rides, Thrill >rides, Transport rides]'' at line 1

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293) at com.ThemeParkG02.rs.control.manager.ManageAttraction.getAttraction(ManageAttraction.java:123) at com.ThemeParkG02.rs.control.manager.Facade.getAttraction(Facade.java:85) at com.ThemeParkG02.rs.control.listener.ViewAttractionListListener.run(ViewAttractionListListener.java:32)

I'm really dizzy right now. The values is out but I the query is not working. The '[' and ']' really blocking my goal. Any input guys?

Thanks in advance.

Andreas · Accepted Answer · 2015-08-19T16:40:48.103

1

Note: I missed the issue of the IN clause, but my answer is technically correct, as the first error in the SQL is the missing space. The link provided by Miljen Mikic explains how to fix the IN clause issue.

Your error says:

MySQLSyntaxErrorException: You have an error in >your SQL syntax

You are missing a space before WHERE.

Updated

For improved readability, I prefer writing it like this:

String sql = "SELECT DISTINCT attra.attractid, attractname, x, y, z, age" +
                           ", weight, height, duration, price, status" +
              " FROM attraction attra" +
              " JOIN attrib_bridge ab ON attra.attractId = ab.attractId" +
              " JOIN attribute attri ON ab.attributeId = attri.attributeid" +
             " WHERE attri.attributename IN ?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setString(1, Arrays.toString(obj));
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            // Process row here
        }
    }
}

edited Aug 19 '15 at 16:40

answered Aug 19 '15 at 16:28

Andreas

154,647
11
152
247

Thanks. One more thing, it should be : " WHERE attri.attributename IN (?) " – SkyvrawleR Aug 19 '15 at 16:53
@SkyvrawleR Actually, the `IN` clause issue is more complicated than that (see the link). You have to dynamically build the SQL with the correct number of markers `IN (?, ?, ?, ?)`, and then call `ps.setString(n, ...)` multiple times to set each value in the array. – Andreas Aug 19 '15 at 16:58
The code runs but return 0. I test this query in navicat and its working with 3 rows of data. This is the tested query. 'SELECT DISTINCT attra.attractid,attractname,x,y,z,age,weight,height,duration,price,status FROM attraction attra INNER JOIN attrib_bridge ab ON attra.attractId = ab.attractId INNER JOIN attribute attri ON ab.attributeId = attri.attributeid WHERE attri.attributename IN ('Transport rides');' – SkyvrawleR Aug 19 '15 at 17:16
@SkyvrawleR If your list contains 3 values ("A", "B", "C"), then `Arrays.toString` returns `[A, B, C]`, and `IN (?)` with `setString(1)` is equivalent to `IN ('[A, B, C]')`, which is same as `= '[A, B, C]'`, which is not what you want. What you want is `IN (?,?,?)` with `setString(1,"A"); setString(2,"B"); setString(3,"C");`. – Andreas Aug 19 '15 at 17:28
lets say we dont know the size of the object[] , how will you solve that kind of situation? – SkyvrawleR Aug 19 '15 at 17:35
See http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause, which was the second link in the answer provider by the "duplicate" message. – Andreas Aug 19 '15 at 17:37

How to extract Object[] and get it into query using preparedstatement setObject()?

1 Answers1