java如何提取对象[]并使用preparedstatement setObject（）将其放入查询？

Question

可能是个愚蠢的问题，但我无法运行查询。这是我的代码：

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

这就是我得到的错误：

[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)

---

我现在真的头晕。值已输出，但查询不起作用。“[”和“]”真的阻碍了我的进球。有什么意见吗

提前谢谢

Answer 1

注意：我错过了IN子句的问题，但我的回答在技术上是正确的，因为SQL中的第一个错误是缺少空格。Miljen Mikic提供的链接解释了如何解决IN子句问题

你的错误是：

MySQLSyntaxErrorException: You have an error in >your SQL syntax

在WHERE之前缺少一个空格

更新

为了提高可读性，我更喜欢这样写：

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