在MySQL中插入时检查数据库中是否存在用户名(Check for given username existence in database at time of insertion in MySQL

在MySQL中插入时检查数据库中是否存在用户名(Check for given username existence in database at time of insertion in MySQL)

如何在Java中插入MySQL时检查数据库中给定的用户名是否存在？

我在MySQL工作台中有下表 -

user_id int not null,autoincrement movie _id int not null movie_name varchar user_name varchar rating int genre varchar

以下是插入数据库 -

try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:XXXX/Recommendation1", "root", "XXXXXXXXXXX"); Statement st = con.createStatement(); int i = st .executeUpdate("insert into Table1(movie_Id,movie_name,user_name,rating,genre) values('" + movieId + "','" + mname + "','" + pname + "','" + ratingr + "','" + genre + "')"); out.println("Data is successfully inserted!"); } catch (Exception e) { System.out.print(e);

所以这里是我只插入电影ID，nmovie名称，用户名，评级类型然后它进入数据库并提供用户ID（自动增量）

我想要的是，如果我在user_name选项中给出相同的名称，它不应该提供唯一的autoincremente id，而是将所有细节插入现有的User_id

换句话说，如何检查数据库中给定的用户名是否存在？

How to check for given username existence in database at time of insertion in MySQL in Java?

I have the following table in MySQL workbench--

user_id int not null,autoincrement movie _id int not null movie_name varchar user_name varchar rating int genre varchar

Now following is the insertion into database--

try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:XXXX/Recommendation1", "root", "XXXXXXXXXXX"); Statement st = con.createStatement(); int i = st .executeUpdate("insert into Table1(movie_Id,movie_name,user_name,rating,genre) values('" + movieId + "','" + mname + "','" + pname + "','" + ratingr + "','" + genre + "')"); out.println("Data is successfully inserted!"); } catch (Exception e) { System.out.print(e);

So here is I am only inserting movie id,nmovie name,user name,rating genre and then it gets into database and a user id is provided to it(autoincrement)

What i want that if i give the same name in my user_name option it should not provide the unique autoincremente id but insert all the details with the existing User_id

In other words how to check for given username existence in database?

最满意答案

你要找的是在主键存在时更新值的SQL，如果不存在则插入。

在MySQL中，仅当插入不存在时才具有该形式

INSERT INTO Table11(...) VALUES(...) ON DUPLICATE KEY UPDATE col11=value1, col2=value2,...

要指定您希望值来自VALUES（）部分，您需要进行以下操作。

INSERT INTO Table11(...) VALUES(...) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2),...

使用表和列名称的完整示例

"insert into Table1(movie_Id,movie_name,user_name,rating,genre) values('" + movieId+ "','"+ mname+ "','"+ pname + "','" + ratingr + "','" + genre + "') " + "ON DUPLICATE KEY UPDATE " + "movie_name=VALUES(movie_name), user_name=VALUES(user_name), " + "rating=VALUES(rating), genre=VALUES(genre) "

MySQL ON DUPLICATE KEY参考

作为一个注释，使用prepare语句避免SQL注入是更好的形式。

String stmt = "insert into Table1(movie_Id,movie_name,user_name,rating,genre) values(?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE " + "movie_name=VALUES(movie_name), user_name=VALUES(user_name), " + "rating=VALUES(rating), genre=VALUES(genre) " PreparedStatement ps = connection.prepareStatement(stmt); ps.setObject(1, movie_id); ps.setObject(2, movie_name); ps.setObject(3, user_name); ps.setObject(4, rating); ps.setObject(5, genre); ResultSet rs = ps.executeQuery();

What you are looking for is the SQL to either update the values if the primary key exists, or insert if it doesnt.

In MySQL the insert only if it doesn't exist has the form

INSERT INTO Table11(...) VALUES(...) ON DUPLICATE KEY UPDATE col11=value1, col2=value2,...

To specify that you want the values to come from the VALUES() section you need to make it the following.

INSERT INTO Table11(...) VALUES(...) ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2),...

Full example using your table and column names

"insert into Table1(movie_Id,movie_name,user_name,rating,genre) values('" + movieId+ "','"+ mname+ "','"+ pname + "','" + ratingr + "','" + genre + "') " + "ON DUPLICATE KEY UPDATE " + "movie_name=VALUES(movie_name), user_name=VALUES(user_name), " + "rating=VALUES(rating), genre=VALUES(genre) "

MySQL ON DUPLICATE KEY reference

As a note, it's better form to use the prepare statement to avoid SQL injection.

String stmt = "insert into Table1(movie_Id,movie_name,user_name,rating,genre) values(?,?,?,?,?) " + "ON DUPLICATE KEY UPDATE " + "movie_name=VALUES(movie_name), user_name=VALUES(user_name), " + "rating=VALUES(rating), genre=VALUES(genre) " PreparedStatement ps = connection.prepareStatement(stmt); ps.setObject(1, movie_id); ps.setObject(2, movie_name); ps.setObject(3, user_name); ps.setObject(4, rating); ps.setObject(5, genre); ResultSet rs = ps.executeQuery();