有时我们会需要对多张表进行关联,今天我需要用到多表添加。不知道怎么弄但是想了下可以获取到第一张表最后一次进行添加的数据,我只会两种方法:一是获取到最后一次进行添加的时间,二是获取到最后进行添加的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>
<!-- 对orders表进行插入操作 --> <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> <!-- 这里就是需要使用多表进行插入的ID --><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<!-- 获取到user最后一次插入的数据,并返回一个int型的值 --> <select id="selectLastId" resultType="int"> select LAST_INSERT_ID() </select>
Service层:OrdersService.javapackage 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(); }
Controller层:OrdersControllerpackage 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); } }
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); } }