对多表进行添加数据（获取最后一条插入的数据的ID）

最新推荐文章于 2023-02-22 15:03:58 发布

原创最新推荐文章于 2023-02-22 15:03:58 发布 · 9k 阅读

15 ·

CC 4.0 BY-SA版权

数据库操作同时被 2 个专栏收录

6 篇文章

订阅专栏

mybatis

5 篇文章

订阅专栏

本文介绍如何在MyBatis中实现多表关联插入，包括通过动态SQL插入数据并获取最后一次插入的ID，用于关联另一张表的插入操作。

有时我们会需要对多张表进行关联，今天我需要用到多表添加。不知道怎么弄但是想了下可以获取到第一张表最后一次进行添加的数据，我只会两种方法：一是获取到最后一次进行添加的时间，二是获取到最后进行添加的ID。我用的是第二种获取到最后进行操作添加的ID。

先将一张表进行添加数据的操作，写上查询语句“select LAST_INSERT_ID()”获取到最后一次添加的ID。获取到ID后返回一个int型的值，

映射文件：ordersMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.car.dao.OrdersMapper">
	<!-- 对user表进行插入，我这里用的是动态sql，也可以用普通的sql语句 -->
	<insert
		id="insertUser"
		parameterType="com.car.bean.User">
		insert into user
		<trim
			prefix="("
			suffix=")"
			suffixOverrides=",">
			<if test="uName != null"> u_name,
			</if>
			<if test="uPhone != null"> u_phone,
			</if>
		</trim>
		<trim
			prefix="values ("
			suffix=")"
			suffixOverrides=",">
			<if test="uName != null"> #{uName,jdbcType=VARCHAR},
			</if>
			<if test="uPhone != null"> #{uPhone,jdbcType=VARCHAR},
			</if>
		</trim>
		
		<!-- 获取到user最后一次插入的数据 -->
		<selectKey keyProperty="uId" resultType="int"> 
		select LAST_INSERT_ID()
		</selectKey>
	</insert>
	<!-- 获取到user最后一次插入的数据，并返回一个int型的值 -->
<select id="selectLastId" resultType="int">
	select LAST_INSERT_ID()
</select>

<insertid="insertOrders"parameterType="com.car.bean.Orders">insert into orders<trimprefix="("suffix=")"suffixOverrides=","><if test="oMoney != null"> o_money,</if><if test="oPaytype != null"> o_payType,</if><if test="oStatus != null"> o_status,</if><if test="oDate != null"> o_date,</if><if test="oOther != null"> o_other,</if><if test="oServicetype != null"> o_serviceType,</if><if test="oServiceproto != null"> o_serviceProto,</if><if test="oShigong != null"> o_shigong,</if><if test="oXiaoshou != null"> o_xiaoshou,</if><if test="oShoping != null"> o_shoping,</if> <if test="uId != null"> u_id,</if><if test="commentId != null"> comment_id,</if></trim><trimprefix="values ("suffix=")"suffixOverrides=","><if test="oMoney != null"> #{oMoney,jdbcType=FLOAT},</if><if test="oPaytype != null"> #{oPaytype,jdbcType=VARCHAR},</if><if test="oStatus != null"> #{oStatus,jdbcType=VARCHAR},</if><if test="oDate != null"> #{oDate,jdbcType=FLOAT},</if><if test="oOther != null"> #{oOther,jdbcType=VARCHAR},</if><if test="oServicetype != null"> #{oServicetype,jdbcType=VARCHAR},</if><if test="oServiceproto != null"> #{oServiceproto,jdbcType=VARCHAR},</if><if test="oShigong != null"> #{oShigong,jdbcType=VARCHAR},</if><if test="oXiaoshou != null"> #{oXiaoshou,jdbcType=VARCHAR},</if><if test="oShoping != null"> #{oShoping,jdbcType=VARCHAR},</if><if test="uId != null"> #{uId,jdbcType=INTEGER},</if><if test="commentId != null"> #{commentId,jdbcType=INTEGER},</if></trim></insert></mapper>

dao层（接口层）：ordersMapper.java

package com.car.dao;


import com.car.bean.Orders;
import com.car.bean.User;

public interface OrdersMapper {

    //对orders表进行插入的方法
    int insertOrders(Orders record); 
    
    //对user表进行插入的方法
    int insertUser(User user);

    //获取最后一次对user表进行插入后的ID
    int selectLastId();
}

Service层：OrdersService.java

package com.car.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.car.bean.Orders;
import com.car.bean.User;
import com.car.dao.OrdersMapper;

@Service
public class OrdersService {
	@Autowired
	OrdersMapper ordersMapper;

	//获取user表最后一次进行插入的ID，返回一个int型
	public int selectLastId(){
		int id=ordersMapper.selectLastId();
		return id;
	}
	
	//对user表进行添加
	public void insertUser(User user){
		ordersMapper.insertUser(user);
	}
	
	//对order表进行添加
	public void insertOrders(Orders order){
		ordersMapper.insertOrders(order);
	}
	
}

Controller层：OrdersController

package com.car.controller;

import java.util.List;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.car.bean.Orders;
import com.car.bean.User;
import com.car.service.OrdersService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

@Controller
@RequestMapping("/orders")
public class OrdersController
{
	@Autowired
	OrdersService ordersService;

	@RequestMapping("/select")
	public String addTest(@RequestParam(value = "pn", defaultValue = "1") Integer pn, Model model)
	{
		PageHelper.startPage(pn, 4);
		List<Orders> list = ordersService.select();
		PageInfo page = new PageInfo(list, 4);
		model.addAttribute("pageInfo", page);
		return "orders";
	}
	
	//先对user表进行插入
	@RequestMapping("/insertUser")
	public String insertUser(User user,Orders order,Model model){
		ordersService.insertUser(user);
		//上面对user表进行插入后获取ID，返回int型
		int id=ordersService.selectLastId();
		order.setuId(id);
		return insertOrders(order,model);
	}
	
	//最后对orders表进行插入
	@RequestMapping("/insertOrders")
	public String insertOrders(Orders orders,Model model){
		ordersService.insertOrders(orders);
		return addTest(1,model);
	}
}