我想同时更新数据库中的两个表。一个表用于组,另一个表则用于组成员:
CREATE TABLE IF NOT EXISTS groups (
group_id INTEGER UNSIGNED AUTO_INCREMENT,
group_name VARCHAR(150) NOT NULL DEFAULT '',
group_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (group_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
CREATE TABLE IF NOT EXISTS group_members (
group_mem_user_id INTEGER UNSIGNED NOT NULL,
group_mem_group_id INTEGER UNSIGNED NOT NULL,
group_mem_role TINYINT DEFAULT 1,
group_mem_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT group_mem_pk PRIMARY KEY (group_mem_user_id, group_mem_group_id),
FOREIGN KEY (group_mem_user_id) REFERENCES user (user_id),
FOREIGN KEY (group_mem_group_id) REFERENCES groups (group_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
我想使用存储过程在group
中创建一个条目,并在group_members
中使用刚刚为组创建的id创建一个条目的条目。
我知道如何在服务器上做到这一点(我有一个java服务器,我使用Spring的JdbcTemplate来调用数据库),但我认为在存储过程中这样做会更好、更高效。
两个单独的查询是(im使用准备好的语句):
INSERT INTO groups (group_name) VALUES (?)
和
INSERT INTO group_members (group_mem_user_id, group_mem_group_id, group_mem_role) VALUES (?,?,?)
但我不知道如何将这些合并到一个存储过程中。
DELIMITER //
DROP PROCEDURE IF EXISTS create_group //
CREATE PROCEDURE create_group(
#in/out here
)
BEGIN
#no idea
END //
DELIMITER ;
理想情况下,我希望它返回一些值来描述操作是否成功。
我使用以下过程:
DELIMITER //
DROP PROCEDURE IF EXISTS create_group //
CREATE PROCEDURE create_group(
IN create_group_group_name VARCHAR(150),
IN create_group_user_id INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO groups (group_name) VALUES (create_group_group_name);
INSERT INTO group_members (group_mem_user_id, group_mem_group_id, group_mem_role) VALUES (create_group_user_id, LAST_INSERT_ID(), 2);
COMMIT;
END //
DELIMITER ;
在我的服务器中,我像这样使用它:
JdbcTemplate jt = new JdbcTemplate(DB.getDataSource(DB_USER));
int i = jt.update("CALL create_group (?,?)", new Object[] {groupName, userId});
if (i != 1)
throw new SQLException("Error creating group with name=" + groupName + " for userid=" + userId);
i == 1
,如果一切顺利。如果用户没有被添加为成员,则永远不会创建组(在下面的第一次迭代中解决了这个问题)。
旧
(非事务性,如果第二次插入失败,则会导致问题,然后创建没有成员的组,在某些情况下它可能会起作用,这就是为什么我把它留在这里,但它对我不起作用)
以下程序有效。它不会返回任何东西,我只是利用程序完成时没有错误的事实来假设一切都好
DELIMITER //
DROP PROCEDURE IF EXISTS create_group //
CREATE PROCEDURE create_group(
IN create_group_group_name VARCHAR(150),
IN create_group_user_id INT
)
BEGIN
INSERT INTO groups (group_name) VALUES (create_group_group_name);
INSERT INTO group_members (group_mem_user_id, group_mem_group_id, group_mem_role) VALUES (create_group_user_id, LAST_INSERT_ID(), 2);
END //
DELIMITER ;