我想使用一个准备好的语句,如下所示:
acl = conn.prepareStatement("INSERT INTO acls_t(user,topic,rw) values((SELECT id from users_t where username=?),'?/#',1);");
mqtt_acl.setString(1, name);
mqtt_acl.setString(2, uuid);
我的数据库是MariaDB,不接受这个。它返回以下错误:
java.sql.SQLException: Could not set parameter at position 2 (values was '3aa4ea54-3105-47a3-9bfb-c17c4348d84a') Query - conn:56(M) - "INSERT INTO acls_t(user,topic,rw) values((SELECT id from users_t where username=?),'?/#',1);"
如何处理此问题?
按如下操作:
acl = conn.prepareStatement("INSERT INTO acls_t(user,topic,rw) values((SELECT id from users_t where username=?),?,1);");
mqtt_acl.setString(1, name);
mqtt_acl.setString(2, uuid + "/#");
您的sql语句可以简化为:
INSERT INTO acls_t(user,topic,rw) SELECT id, CONCAT(?, '/#'), 1 FROM users_t WHERE username = ?;
而不使用必须嵌套SELECT
语句的VALUES
所以将您的代码更改为:
acl = conn.prepareStatement("INSERT INTO acls_t(user,topic,rw) SELECT id, CONCAT(?, '/#'), 1 FROM users_t WHERE username = ?;");
mqtt_acl.setString(1, uuid);
mqtt_acl.setString(2, name);
使用此代码,如果表users_t
中没有用户的username
等于变量name
的值,则不会插入任何行,但您的代码将尝试向表acls_t
插入一行,并将列user
设置为null
。