引发MySQLExceptionError的MBRContains语句

Question

String setBox = "SET @box = 
                                'Polygon((" + lx + " " + ry + ","
                                            + rx + " " + ry + ","
                                            + lx + " " + ly + ","
                                            + rx + " " + ly + ","
                                            + lx + " " + ry + "))';\n";

ResultSet regionResult = stmt.executeQuery(setBox + 
  "SELECT ItemID FROM ItemPoint WHERE MBRContains(GeomFromText(@box), Coords);\n");

我不知道为什么会有语法错误，因为我是基于以下原因的:http://dev.mysql.com/doc/refman/5.5/en/using-spatial-indexes.html。

我的MySQL服务器版本是:5.5.40-0Ubuntu0.14.04.1(Ubuntu)

Answer 1

您的问题不是MySql是PreparedStatement。不能用它来运行两个语句，必须使用CallableStatement

对于您的问题，它将类似于：

String callString = "{ call SET @box = 'Polygon(( ? ?,
                                       ? ?,
                                       ? ?,
                                       ? ?,
                                       ? ?))'; 
                      SELECT ItemID 
                        FROM ItemPoint 
                       WHERE MBRContains(GeomFromText(@box), Coords); }";

CallableStatement callableStatement = con.prepareCall(callString);
callableStatement.setInt(1, lx);
callableStatement.setInt(2, ry);
callableStatement.setInt(3, rx);
callableStatement.setInt(4, ry);
callableStatement.setInt(5, lx);
callableStatement.setInt(6, ly);
callableStatement.setInt(7, rx);
callableStatement.setInt(8, ly);
callableStatement.setInt(9, lx);
callableStatement.setInt(10, ry);
ResultSet regionResult = callableStatement.executeQuery();

注意，这可能不是它的工作方式。你可能需要改变一些东西，这是基本的想法。我是凭记忆这么做的，所以...