java - 如何根据主键的值更改外键？

Question

我现在有两张 table ， table talkview:

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(10)     | NO   | PRI | NULL    | auto_increment |
| titlename | varchar(64) | NO   |     | NULL    |                |
| postname  | varchar(64) | NO   |     | NULL    |                |
| counts    | varchar(11) | YES  |     | 0       |                |
+-----------+-------------+------+-----+---------+----------------+

和表主要讨论:

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| title    | varchar(64) | NO   |     | NULL    |                |
| text     | varchar(64) | NO   |     | NULL    |                |
| username | varchar(64) | NO   |     | NULL    |                |
| talk_id  | int(10)     | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

现在我想将数据一起插入这些表中，“talk_id”将根据表 talkview 中的“id”自动更改。

在Java中，我尝试了这段代码，但'talk_id'没有变化:

public void posttalk(String title, String con, String name){
        connection = DBConection.getConnection();
        Statement stmt = null;
        String SQL_1 = "insert into talkview(titlename,postname) values(? , ?)";
        String SQL_2 = "insert into maintalk(title,text,username) values(? , ? , ?)";
        try {
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement = connection.prepareStatement(SQL_1);
            preparedStatement.setString(1, title);
            preparedStatement.setString(2, name);
            Integer a = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(SQL_2);
            preparedStatement.setString(1, title);
            preparedStatement.setString(2, name);
            preparedStatement.setString(3, con);
            Integer b = preparedStatement.executeUpdate();
            connection.commit();
            connection.setAutoCommit(true);
            } catch (SQLException sqle) {
            try {
                connection.rollback();
                stmt.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            sqle.printStackTrace();
            } finally {
            DBConection.closeConnection(connection);
        }
    }

Answer 1

在第一个名为 talkview 的表中插入一些数据后。您可以使用最大主键检索插入的最后一条记录，因为您的PK是自动递增的，然后将其放在maintalk的talk_id列中 table 。您可以执行以下代码来检索它。

    String SQL_1 = "insert into talkview(titlename,postname) values(? , ?)";
    String SQL_2 = "insert into maintalk(title,text,username,talk_id) values(? , ? , ?, ?)";
    String SQL_3 = "SELECT id FROM talkview order by id DESC LIMIT 1;";
    try {
        connection.setAutoCommit(false);
        PreparedStatement preparedStatement = connection.prepareStatement(SQL_1);
        preparedStatement.setString(1, title);
        preparedStatement.setString(2, name);
        Integer a = preparedStatement.executeUpdate();

        // This is the query to retrieve the last ID you insert in talkveiw table
        PreparedStatement preparedStatement = connection.prepareStatement(SQL_3);
        ResultSet result = preparedStatement.executeQuery();
        Integer id = 0; // This will be the id you will add on your talk_id
        if(result.next()) {
            id = result.getInt(1);
        }

        preparedStatement = connection.prepareStatement(SQL_2);
        preparedStatement.setString(1, title);
        preparedStatement.setString(2, name);
        preparedStatement.setString(3, con);
        preparedStatement.setInt(4, id); // you will insert it here the 'id'
        Integer b = preparedStatement.executeUpdate();
        connection.commit();
        connection.setAutoCommit(true);