java - 在 mysql 中检查用户名...如果存在则插入到其他表中否则基表

Question

好吧，我在 MySQL 工作台中有下表 Table1--

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);

现在我想要的是，如果已经存在一个具有该值 (pnmae) 的用户名，我正在输入它不应该进入我的表 1 或更新，但它应该进入另一个表表 2，模式表2如下--

movie _id     int            not null 
movie_name    varchar        
user_name     varchar
genre         varchar

为此使用什么。如何使用 if -else 条件

下面是我在检查用户名是否存在时所做的

Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:XXXX/Recommendation1", "root",
                    "wXXXXX");
            Statement st = con.createStatement();
            //Statement st2 = con.createStatement();
            String SQL = "SELECT * from Table1 WHERE user_name ='" + pname
                    + "' ";
            ResultSet rs = st.executeQuery(SQL);

            --if (rs.) {   //What to put here

                int j = st
                        .executeUpdate("insert into Table2(movie_Id,movie_name,user_name,genre) values('"
                                + movieId
                                + "','"
                                + mname
                                + "','"
                                + pname
                                + "','" + genre + "') ");

            }

            int i = st
                    .executeUpdate("insert into Table1(movie_Id,movie_name,user_name,rating,genre) values('"
                            + movieId
                            + "','"
                            + mname
                            + "','"
                            + pname
                            + "','" + ratingr + "','" + genre + "') ");

            /*  ResultSet rs = st1.executeQuery();
                out.println("name is already there");
                out.println("Data is successfully inserted!"); */

        /*  final String Query = "SELECT t1.user_Id from Table1 t1 JOIN Table2 t2 on t2.movie_Id = t1.movie_id WHERE t2.user_name = 'vishal'";

            PreparedStatement st11 = con.prepareStatement(Query);
            ResultSet rw = st11.executeQuery();
            rw.last();

            int id = rw.getInt("user_Id");

            System.out.print("ID: " + id);*/

谢谢

Answer 1

试试这个:-

        boolean isUser=false;
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:XXXX/Recommendation1", "root",
                "wXXXXX");
        Statement st = con.createStatement();           
        String SQL = "SELECT 1 from Table1 WHERE user_name ='"+ pname
                + "' ";
        ResultSet rs = st.executeQuery(SQL);
        isUser=rs.next();
        Statement st2 = con.createStatement();
        if (isUser) {   //check the user is exist or not

            int j = st2.executeUpdate("insert into Table2(movie_Id,movie_name,user_name,genre) values('"
                            + movieId
                            + "','"
                            + mname
                            + "','"
                            + pname
                            + "','" + genre + "') ");

     }else{

     int i = st2.executeUpdate("insert into Table1(movie_Id,movie_name,user_name,rating,genre) values('"
                        + movieId
                        + "','"
                        + mname
                        + "','"
                        + pname
                        + "','" + ratingr + "','" + genre + "') ");

       }

希望对您有所帮助。