java - 使用具有字符串的额外列将数据从一个 Mysql 表传输到另一个表

Question

我有一个名为 register 的 Mysql 表，我想将所有详细信息从 register 传输到 login 表，并带有一个自动生成密码的额外列。

这是我的 Servlet，其中有自动生成的密码:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String passWord=Long.toHexString(Double.doubleToLongBits(Math.random()));
        System.out.println(passWord);
        String Id=request.getParameter("id");
        int id=Integer.parseInt(Id);
        AffiliateApproveService approve=new AffiliateApproveService();
        approve.affiliateApprove(id,passWord);

    }

这里我为 PassWord 列生成了一个字母数字密码作为字符串“passWord”如何将密码分配给 PassWord 列....

这是我的服务类别:

public class AffiliateApproveService {

    public void affiliateApprove(int id,String passWord) {
        AffiliateApproveDAO cad=new AffiliateApproveDAO();
        DataSource dataSource=new DataSource();
        cad.setDataSource(dataSource);
        cad.insertClient(id);
        cad.updateClient(id, passWord);
        cad.deleteClient(id);

    }

这是我的 DAO:

public class AffiliateApproveDAO {

        private DataSource dataSource;
        public void setDataSource(DataSource dataSource) {
       this.dataSource = dataSource;
    }
    Connection conn=null;
    PreparedStatement statement=null;
    ResultSet rs=null;


    public void insertClient(int id) {    

        try{

            conn=dataSource.createConnection();
            PreparedStatement ps=conn.prepareStatement("insert into login(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            ps.executeUpdate("insert into login ( id, FirstName, LastName, Gender, Category, DateOfBirth, Age, Address, Country,  State, city, PinCode, UserName, EmailId, ContactNo, MobileNo)select id, FirstName, LastName, Gender, Category, DateOfBirth, Age, Address, Country,  State, city, PinCode, UserName, EmailId, ContactNo, MobileNo  from  register where id="+id+" ");
            ps.setInt(1, id);

}
        catch (SQLException e) {
            throw new RuntimeException(e);

    } finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
    }


    }


    public void deleteClient(int id) {
        try{
            conn=dataSource.createConnection();
            PreparedStatement ps1=conn.prepareStatement("delete from register where id="+id+"");
            ps1.executeUpdate();
      }
          catch (SQLException e) {
              throw new RuntimeException(e);

      } finally {
              if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
          if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
          if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
      }
    }


public void updateClient(int id,String passWord){

    try{
        conn=dataSource.createConnection();
        PreparedStatement ps=conn.prepareStatement("UPDATE login set PassWord= "+passWord+" where id="+id+"");
        ps.executeUpdate();
  }
      catch (SQLException e) {
          throw new RuntimeException(e);

  } finally {
          if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
      if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
      if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
  }
}


}

这是我的用于登录的 DDl 表

create table login(
id int(100) not null AUTO_INCREMENT primary key,
FirstName varchar(300) default null,
LastName varchar(300) default null,
Gender varchar(200) default null,
Category varchar(200) default null,
DateOfBirth varchar(200) default null,
Age int(7)default null,
Address varchar(1000) default null,
Country varchar(500) default null,
State varchar (500) default null,
city varchar(500)default null,
PinCode int(10)default null,
UserName varchar(100)default null,
EmailId varchar(500)default null,
ContactNo varchar(20) default null,
MobileNo varchar(20) default null,
TimeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PassWord varchar(100)null default null
);

这是我的寄存器表 DDl:

create table register(
id int(100) not null AUTO_INCREMENT primary key,
FirstName varchar(300) default null,
LastName varchar(300) default null,
Gender varchar(200) default null,
Category varchar(200) default null,
DateOfBirth varchar(200) default null,
Age int(3)default null,
Address varchar(1000) default null,
Country varchar(500) default null,
State varchar (500) default null,
city varchar(500)default null,
PinCode int(10)default null,
UserName varchar(500)default null,
EmailId varchar(500)default null,
ContactNo varchar(20) default null,
MobileNo varchar(20) default null,
TimeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

现在我的问题是在 updateClient() 方法中如何使用变量 passWord 的值更新登录表中的列 PassWord 请帮我解决这个问题...提前致谢...

Answer 1

使用它会对您有所帮助，

"insert into login 
(id, FirtName, LastName, Gender, Category, DateOfBirth, Age, Address, Country, State, city, PinCode, UserName, EmailId, ContactNo, MobileNo, PassWord)
((select * from register where id="+id+" "),password);