java - 从表中选择并插入到另一个表中

Question

我尝试从 MYSQL 数据库选项卡中选择一些数据并将它们插入到另一个 MYSQL 数据库表中。

这是我的 servlet 代码

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);

    PrintWriter out = response.getWriter();

    String name = request.getParameter("name");

    ArrayList al = null;

    int size = 0;
    size = AcceptDao.getData(name);

    if (size>0) {
        out.println("<script type=\"text/javascript\">");
        out.println("alert('Successfully Employee Added');");
        out.println("</script>");
    } else {
        out.println("<script type=\"text/javascript\">");
        out.println("alert('Try Again');");
        out.println("</script>");
    }
}

这是我用于选择和插入数据的 java 代码。

 public static int getData(String Uname) {
    ArrayList al = null;
    int status = 0;
    String name = null;
    String role = null;
    String pass = null;
    try {
        Connection con = ConnectionProvider.getCon();
        String query = "SELECT noty_name,noty_user_role,noty_pass FROM notification WHERE noty_name='" + Uname + "'";

        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(query);
        al = new ArrayList();

        while (rs.next()) {
            name = rs.getString("noty_name");
            role = rs.getString("noty_user_role");
            pass = rs.getString("noty_pass");

        }

            PreparedStatement ps = con.prepareStatement("INSERT INTO employee (name,user_role,pass) values(?,?,?)");
            ps.setString(1, name);
            ps.setString(2, role);
            ps.setString(3, pass);
            status = ps.executeUpdate();
    } catch (Exception e) {
    }
    return status;
}

这不能正常工作。此代码不执行选择和插入操作。这段代码有什么问题。

Answer 1

请注意，“插入”的代码部分位于循环之外。因此，如果有的话，只会插入循环中的最后一个数据。此外，不需要 while(rs.next) 循环。如果您确定在请求中传递了唯一的名称，则可以使用 if(rs.next) 条件。

while (rs.next()) {
   name = rs.getString("noty_name");
   role = rs.getString("noty_user_role");
   pass = rs.getString("noty_pass");    
}
/* The Below part is wrong */
PreparedStatement ps = con.prepareStatement("INSERT INTO employee (name,user_role,pass) values(?,?,?)");
ps.setString(1, name);
ps.setString(2, role);
ps.setString(3, pass);
status = ps.executeUpdate();

请使用下面的代码

public static int getData(String Uname) {
ArrayList al = null;
int status = 0;
String name = null;
String role = null;
String pass = null;
try {
    Connection con = ConnectionProvider.getCon();
    String query = "SELECT noty_name,noty_user_role,noty_pass FROM notification WHERE name='" + Uname + "'";

    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery(query);
    al = new ArrayList();

    PreparedStatement ps = con.prepareStatement("INSERT INTO employee (name,user_role,pass) values(?,?,?)");

    while (rs.next()) {
        name = rs.getString("noty_name");
        role = rs.getString("noty_user_role");
        pass = rs.getString("noty_pass");

        ps.setString(1, name);
        ps.setString(2, role);
        ps.setString(3, pass);
        status = ps.executeUpdate();
    }


} catch (Exception e) {
}
return status;

}