SSM框架动态SQL：INSERT语句

本文写的是编写动态SQL的INSERT语句时遇到的问题

第一次写文章，有什么不足请提出！欢迎交流
环境：idea2019.3，java8，tomcat9.0，mysql数据库

前端页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@include file="common/header.jsp"%>
<div>
    <a class="hiddenanchor" id="signup"></a>
    <a class="hiddenanchor" id="signin"></a>

    <div class="login_wrapper">
        <div class="animate form login_form">
            <section class="login_content">
                <form action="docreatebankcard" method="post">
                    <h1>办卡</h1>
                    <div>
                        <input type="text" class="form-control" name="idCard" placeholder="请输入身份证号" required="" />
                    </div>
                    <div>
                        <input type="password" class="form-control" name="password1" placeholder="请输入银行卡密码" required="" />
                    </div>
                    <div>
                        <input type="password" class="form-control" name="password2" placeholder="请再次输入银行卡密码" required="" />
                    </div>
                    <span>${error}</span>
                    <div>
                        <button type="submit" class="btn btn-success">申  请</button>
                        <button type="reset" class="btn btn-default">重  填</button>
                    </div>

                    <div class="clearfix"></div>
                </form>
            </section>
        </div>
    </div>
</div>
<%@include file="common/footer.jsp"%>
<script src="${pageContext.request.contextPath }/statics/localjs/rollpage.js"></script>
<script src="${pageContext.request.contextPath }/statics/localjs/applist.js"></script>

pojo

package com.pojo;

public class BankCard {
    private int id;
    private String bankcard;
    private int status;
    private String password;
    private int money;
    private int userId;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBankcard() {
        return bankcard;
    }

    public void setBankcard(String bankcard) {
        this.bankcard = bankcard;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }
}

Controller

package com.Controller.manager;

import com.Controller.user.UserBankController;
import com.pojo.BankCard;
import com.pojo.Manager;
import com.pojo.UserInfo;
import com.service.manager.ManagerService;
import com.service.user.UserService;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.List;
import java.util.Random;

@Controller
@RequestMapping(value = "/manager")
public class ManagerCreateBankCardController {
    private Logger logger = Logger.getLogger(UserBankController.class);
    @Resource
    private ManagerService managerService;

    @RequestMapping(value = "/platform/createbankcard")
    public String createbankcard(HttpServletRequest request, HttpSession session) {
        return "manager/createbankcard";
    }

    //处理表单请求
    @PostMapping(value = "/platform/docreatebankcard")
    public String docreatebankcard(@RequestParam("idCard") String idCard,
                                   @RequestParam("password1") String password1,
                                   @RequestParam("password2") String password2,
                                   HttpServletRequest request,
                                   HttpSession session,
                                   Model model) {
        logger.debug("docreatebankcard====================================");
        int id = 0;
        int i = 0;
        BankCard bk = new BankCard();
        //验证两次密码输入是否一致
        if(!password1.equals(password2)) {
            request.setAttribute("error", "两次输入密码不一致!");
            return "/manager/createbankcard";
        }
        //根据身份证号来查询用户,并且获得用户的id,作为外键
        try {
            id = managerService.findUserByIdCard(idCard);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //id为0,说明找不到对应的用户
        if(id == 0) {
            System.out.println("id" + id);
            request.setAttribute("error", "身份证号不正确");
            return "/manager/createbankcard";
        } else {
        //id不为0,则用实体类将数据传到Service进行处理
            System.out.println("id" + id);
            String str="0123456789";
            char[] cs = new char[10];
            for(int j = 0; j < cs.length; j++) {
                    int index = (int) (Math.random() * 10);
                    cs[j] = str.charAt(index);
            }
            String result = new String(cs);

            bk.setBankcard(result);
            bk.setPassword(password1);
            bk.setUserId(id);
            bk.setMoney(0);
            bk.setStatus(1);

            try {
                i = managerService.createBankCard(bk);
            } catch (Exception e) {
                e.printStackTrace();
            }
            //i不为0,跳转到成功页面
            if (i != 0) {
                return "/manager/createsuccess";
            } else {
            //否则提示创建失败
                System.out.println("-------------------" + i + "------------------------");
                request.setAttribute("error", "创建失败，请重新输入");
                return "manager/createbankcard";
            }
        }
    }

}

Service接口

package com.service.manager;

import com.pojo.BankCard;
import com.pojo.Manager;
import com.pojo.User;
import com.pojo.UserInfo;

public interface ManagerService {
    /**
     * 创建银行卡
     * @param bk
     * @return
     */
    public int createBankCard(BankCard bk);
}

ServiceImpl

package com.service.manager;

import com.dao.manager.ManagerMapper;
import com.pojo.BankCard;
import com.pojo.Manager;
import com.pojo.User;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class ManagerServiceImpl implements ManagerService{
    @Resource
    private ManagerMapper mapper;

    @Override
    public int createBankCard(BankCard bk) {
        System.out.println("=======================ManagerServiceImpl========================"+bk);
        int i = 0;
        i = mapper.createBankCard(bk);
        return i;
    }
}

Mapper接口

package com.dao.manager;

import com.pojo.BankCard;
import com.pojo.Manager;
import com.pojo.User;
import org.apache.ibatis.annotations.Param;

public interface ManagerMapper {
/**
 * 创建银行卡
 * 这里只有一个参数也能用@param
 * @param bk
 * @return
 */
	public int createBankCard(@Param("bk") BankCard bk);
}

Mapper.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.dao.manager.ManagerMapper">
    <insert id="createBankCard" parameterType="com.pojo.BankCard">
        <!-- 用于排错 -->
        <!--        insert into bankcardinfo
        (id, bankcard, status, password, money, userId)
        values (3, #{bankcard.bankcard}, 1, #{bankcard.password}, 0, #{bankcard.userId});-->

        <!-- 解决主键自增问题，
        keyProperty：自增键名，
        order：可设置为 BEFORE 或 AFTER，
            如果设置为 BEFORE,那么它会首先选择主键,设置 keyProperty 然后执行插入语句。
            如果设置为 AFTER,那么先执行插入语句,然后是 selectKey 元素，一般用AFTER
        resultType：结果的类型-->
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into bankcardinfo
        <!-- prefix：添加前缀
             suffix：添加后缀
             prefixOverrides：删除前缀
             suffixOverrides：删除后缀 -->
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <!-- 自增键不需要写 -->
<!--            <if test="id != null">-->
<!--                id,-->
<!--            </if>-->

            <!--前端只输入了密码，所以只需要判断密码即可-->
            <!-- Caused by: org.apache.ibatis.binding.BindingException: Parameter 'password' not found. Available parameters are [bk, param1]
                出现这个错误的话可能是没有写上传入的实体类.属性-->
            <if test="bk.password != null">
                bankCard, status, password, money, userId,
            </if>
        </trim>

        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <!-- 自增键不需要写 -->
<!--            <if test="id != null">-->
<!--                #{bankCard.id,jdbcType=Integer},-->
<!--            </if>-->

            <!-- 这里的jdbcType都要大写,否则会报错:
            org.mybatis.spring.MyBatisSystemException:
            nested exception is org.apache.ibatis.builder.BuilderException:
            Error resolving JdbcType. Cause: java.lang.IllegalArgumentException:
            No enum constant org.apache.ibatis.type.JdbcType.没大写的Type名 -->
            <if test="bk.bankcard != null">
                #{bk.bankcard,jdbcType=VARCHAR},
            </if>
            <if test="bk.status != null">
                #{bk.status,jdbcType=INTEGER},
            </if>
            <if test="bk.password != null">
                #{bk.password,jdbcType=VARCHAR},
            </if>
            <if test="bk.money != null">
                #{bk.money,jdbcType=INTEGER},
            </if>
            <if test="bk.userId != null">
                #{bk.userId,jdbcType=INTEGER},
            </if>
        </trim>
    </insert>
</mapper>

创建前

数据库

创建后

数据库