I'm using below code to do checking before data get inserted into MySQL. However, I get error as below
在数据插入到MySQL之前,我使用下面的代码进行检查。但是,我得到如下所示的错误
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 'WHERE NOT EXISTS (SELECT * FROM movie_title WHERE title = 'ABC')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
Here my code
在这里我的代码
public void checkAndAdd(String movieTitle) throws Exception {
// TODO Auto-generated method stub
DatabaseConnection db=new DatabaseConnection();
Connection connect=db.getConnection();
String sql="INSERT INTO movie_title(title) VALUES (?) WHERE NOT EXISTS (SELECT * FROM movie_title WHERE title = ?)";
PreparedStatement ps=connect.prepareStatement(sql);
ps.setString(1,movieTitle);
ps.setString(2,movieTitle);
ps.executeUpdate();
connect.close();
ps.close();
}
Edited
编辑
String sql=" IF NOT EXISTS(SELECT * FROM movie_title WHERE title = ?) INSERT INTO movie_title (title) VALUES (?) ";
1 个解决方案
#1
4
You can't put a WHERE clause in your SQL when you're using the VALUES keyword. Try something like this:
当使用值关键字时,不能在SQL中放置WHERE子句。试试这样:
IF NOT EXISTS(SELECT 1 FROM movie_title WHERE title = ?)
INSERT INTO movie_title (title)
VALUES (?)
I work with SQL Server mostly, so I'm not sure if that's completely valid for MySQL, but it will be something similar.
我主要使用SQL Server,所以我不确定这对MySQL是否完全有效,但它将是类似的。
Okay, for MySQL it has to be a little different:
对MySQL来说,它必须有点不同:
INSERT INTO movie_title (title)
SELECT mt.title
FROM
(SELECT ? title) mt
WHERE NOT EXISTS (
SELECT 1 FROM movie_title WHERE title = ?)
#1
4
You can't put a WHERE clause in your SQL when you're using the VALUES keyword. Try something like this:
当使用值关键字时,不能在SQL中放置WHERE子句。试试这样:
IF NOT EXISTS(SELECT 1 FROM movie_title WHERE title = ?)
INSERT INTO movie_title (title)
VALUES (?)
I work with SQL Server mostly, so I'm not sure if that's completely valid for MySQL, but it will be something similar.
我主要使用SQL Server,所以我不确定这对MySQL是否完全有效,但它将是类似的。
Okay, for MySQL it has to be a little different:
对MySQL来说,它必须有点不同:
INSERT INTO movie_title (title)
SELECT mt.title
FROM
(SELECT ? title) mt
WHERE NOT EXISTS (
SELECT 1 FROM movie_title WHERE title = ?)