java - 如何提取 Object[] 并使用准备好的语句 setObject() 将其放入查询中？

Question

这个问题已经有答案了:

PreparedStatement IN clause alternatives? (33 个回答)

已关闭 8 年前。

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

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