业务表对应主键为varChar类型且无自增策略的新增与修改问题

业务表对应主键为varChar类型且无自增策略的新增与修改问题

上周领到一个简单的小需求,但在开发过程中遇到一点小插曲,在此记录下,希望小小的问题和踩坑可以帮助到大家。话不多说,直接看表结构:
在这里插入图片描述
这里机构id为varchar类型(对应实体属性为String类型)是主键且无自增策略,也就是说新增时机构id这个字段是通过手动输入保存的;还有这个以数字开头的机构DES密钥字段也在后续过程中也遇到问题。下面直接看代码(使用SpringBoot+Vue+Mybatis / Mybatis plus):
在这里插入图片描述
这段代码可以看到修改或新增提交from表单时都是通过branchId(主键)来判断具体走新增还是修改逻辑,所以此套逻辑必定不行(新增、修改操作均需要传入branchId)
于是做了以下修改(通过给新增、修改绑定flag标识进而判断触发的是哪个操作),下面为修改后的代码:
在这里插入图片描述

/** 新增按钮操作 */
    handleAdd() {
      this.reset();
      this.open = true;
      this.title = "添加机构信息管理";
      this.flag=true;       //新增绑定true标识
    },
    /** 修改按钮操作 */
    handleUpdate(row) {
      this.flag=false;     //修改绑定true标识
      this.reset();
      const branchId = row.branchId || this.ids
      getWalletInfo(branchId).then(response => {
        this.form = response.data;
        this.open = true;
        this.title = "修改机构信息管理";
      });
    },

修改提交form表单逻辑(此时通过绑定的flag标识进行逻辑判断):

/** 提交按钮 */
submitForm() {
  this.$refs["form"].validate(valid => {
    if (valid) {
      if (!this.flag) {
        updateWalletInfo(this.form).then(response => {
          this.$modal.msgSuccess("修改成功");
          this.open = false;
          this.getList();
        });
      } else {
        addWalletInfo(this.form).then(response => {
          this.$modal.msgSuccess("新增成功");
          this.open = false;
          this.getList();
        });
      }
    }
  });
},

解决了功能问题还有一些细节需要处理。修改时需要将branchId(主键)置为不可编辑状态:
在这里插入图片描述
效果:
在这里插入图片描述

做完以上操作后,尝试对数据进行修改时,结果又产生报错,以下为报错信息:

	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
	at com.sun.proxy.$Proxy164.update(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy164.update(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	... 146 common frames omitted
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "3" <S_LONG>
    at line 9, column 13.

Was expecting one of:

    "ACTION"
    "ANY"
    "BYTE"
    "CASCADE"
    "CAST"
    "CHANGE"
    "CHAR"
    "CHARACTER"
    "COLUMN"
    "COLUMNS"
    "COMMENT"
    "COMMIT"
    "CREATE"
    "CYCLE"
    "DESC"
    "DESCRIBE"
    "DISABLE"
    "DIV"
    "DO"
    "DOUBLE"
    "DUPLICATE"
    "ENABLE"
    "END"
    "EXCLUDE"
    "EXTRACT"
    "FALSE"
    "FIRST"
    "FN"
    "FOLLOWING"
    "FORMAT"
    "GROUP"
    "IF"
    "INDEX"
    "INSERT"
    "INTERVAL"
    "ISNULL"
    "KEY"
    "LAST"
    "LEFT"
    "LIMIT"
    "MATERIALIZED"
    "NEXTVAL"
    "NO"
    "NOLOCK"
    "NULLS"
    "OF"
    "OFFSET"
    "ON"
    "OPEN"
    "OPTIMIZE"
    "ORDER"
    "OVER"
    "PARTITION"
    "PATH"
    "PERCENT"
    "PRECISION"
    "PRIMARY"
    "PRIOR"
    "PROCEDURE"
    "PUBLIC"
    "RANGE"
    "READ"
    "REPLACE"
    "RIGHT"
    "ROW"
    "ROWS"
    "SCHEMA"
    "SEPARATOR"
    "SEQUENCE"
    "SESSION"
    "SET"
    "SIBLINGS"
    "SIZE"
    "START"
    "TABLE"
    "TABLES"
    "TEMP"
    "TEMPORARY"
    "TO"
    "TOP"
    "TRUE"
    "TRUNCATE"
    "TYPE"
    "UNSIGNED"
    "VALIDATE"
    "VALUE"
    "VALUES"
    "VIEW"
    "XML"
    "ZONE"
    <K_DATETIMELITERAL>
    <K_DATE_LITERAL>
    <S_IDENTIFIER>
    <S_QUOTED_IDENTIFIER>

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:26538)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:26377)
	at net.sf.jsqlparser.parser.CCJSqlParser.RelObjectNameExt(CCJSqlParser.java:3882)
	at net.sf.jsqlparser.parser.CCJSqlParser.RelObjectNameList(CCJSqlParser.java:3135)
	at net.sf.jsqlparser.parser.CCJSqlParser.Column(CCJSqlParser.java:3173)
	at net.sf.jsqlparser.parser.CCJSqlParser.Update(CCJSqlParser.java:1138)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:138)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:502)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:186)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:176)
	at com.baomidou.mybatisplus.extension.parser.JsqlParserSupport.parserMulti(JsqlParserSupport.java:63)
	... 168 common frames omitted
14:42:32.410 [http-nio-8899-exec-6] WARN  o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver - [logException,208] - Resolved [org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: <EOL><EOL>### Error updating database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_info<EOL>         SET CUST_NO = ?,<EOL>            BRANCH_NAME = ?,<EOL>            PARENT_BRANCH_ID = ?,<EOL>            STATUS = ?,<EOL>            OPEN_DATE = ?,<EOL>            OPEN_TIME = ?,<EOL>            MD5_KEY = ?,<EOL>            3DES_KEY = ?,<EOL>            LINK_NAME = ?,<EOL>            MOBILE_PHONE = ?,<EOL>            E_MAIL = ? <EOL>        where BRANCH_ID = ?<EOL><EOL>### The error may exist in file [D:\hzsr\platform-business\target\classes\mapper\business\WalletBranchInfoMapper.xml]<EOL><EOL>### The error may involve com.horzits.business.mapper.WalletBranchInfoMapper.updateWalletBranchInfo<EOL><EOL>### The error occurred while executing an update<EOL><EOL>### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_info<EOL>         SET CUST_NO = ?,<EOL>            BRANCH_NAME = ?,<EOL>            PARENT_BRANCH_ID = ?,<EOL>            STATUS = ?,<EOL>            OPEN_DATE = ?,<EOL>            OPEN_TIME = ?,<EOL>            MD5_KEY = ?,<EOL>            3DES_KEY = ?,<EOL>            LINK_NAME = ?,<EOL>            MOBILE_PHONE = ?,<EOL>            E_MAIL = ? <EOL>        where BRANCH_ID = ?]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3bf20f8d] was not registered for synchronization because synchronization is not active
JDBC Connection [ConnectionProxyImpl{connectedTime=2024-06-17 14:39:22.55, closeCount=0, lastValidateTimeMillis=2024-06-17 14:42:21.375}] will not be managed by Spring
==>  Preparing: insert into sys_oper_log(title, business_type, method, request_method, operator_type, oper_name, dept_name, oper_url, oper_ip, oper_location, oper_param, json_result, status, error_msg, cost_time, oper_time) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate())
==> Parameters: 机构信息管理(String), 2(Integer), com.horzits.business.controller.WalletBranchInfoController.edit()(String), PUT(String), 1(Integer), admin(String), 研发部门(String), /business/walletInfo(String), 127.0.0.1(String), 内网IP(String), {"_3desKey":"2","branchId":"2","branchName":"2","custNo":"21111","email":"2","linkName":"2","md5Key":"2","mobilePhone":"2","openDate":"2个人","openTime":"2","parentBranchId":"2","status":"0"}(String), null, 1(Integer), nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_info
         SET CUST_NO = ?,
            BRANCH_NAME = ?,
            PARENT_BRANCH_ID = ?,
            STATUS = ?,
            OPEN_DATE = ?,
            OPEN_TIME = ?,
            MD5_KEY = ?,
            3DES_KEY = ?,
            LINK_NAME = ?,
            MOBILE_PHONE = ?,
            E_MAIL = ? 
        where BRANCH_ID = ?
### The error may exist in file [D:\hzsr\platform-business\target\classes\mapper\business\WalletBranchInfoMapper.xml]
### The error may involve com.horzits.business.mapper.WalletBranchInfoMapper.updateWalletBranchInfo
### The error occurred while executing an update
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: update wallet_branch_info
         SET CUST_NO = ?,
            BRANCH_NAME = ?,
            PARENT_BRANCH_ID = ?,
            STATUS = ?,
            OPEN_DATE = ?,
            OPEN_TIME = ?,
            MD5_KEY = ?,
            3DES_KEY = ?,
            LINK_NAME = ?,
            MOBILE_PHONE = ?,
            E_MAIL = ? 
        where BRANCH_ID = ?(String), 12(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3bf20f8d]

以下为xml映射:

<update id="updateWalletBranchInfo" parameterType="WalletBranchInfo">
    update wallet_branch_info
    <trim prefix="SET" suffixOverrides=",">
        <if test="custNo != null and custNo != ''">CUST_NO = #{custNo},</if>
        <if test="branchName != null and branchName != ''">BRANCH_NAME = #{branchName},</if>
        <if test="parentBranchId != null and parentBranchId != ''">PARENT_BRANCH_ID = #{parentBranchId},</if>
        <if test="status != null and status != ''">STATUS = #{status},</if>
        <if test="openDate != null and openDate != ''">OPEN_DATE = #{openDate},</if>
        <if test="openTime != null and openTime != ''">OPEN_TIME = #{openTime},</if>
        <if test="md5Key != null and md5Key != ''">MD5_KEY = #{md5Key},</if>
        <if test="_3desKey != null and _3desKey != ''">3DES_KEY = #{_3desKey},</if>
        <if test="linkName != null and linkName != ''">LINK_NAME = #{linkName},</if>
        <if test="mobilePhone != null and mobilePhone != ''">MOBILE_PHONE = #{mobilePhone},</if>
        <if test="email != null and email != ''">E_MAIL = #{email},</if>
    </trim>
    where BRANCH_ID = #{branchId}
</update>

根据报错检查映射以及参数占位等等问题均无果。后来,无可奈何,尝试将这一行删掉:

 <if test="_3desKey != null and _3desKey != ''">3DES_KEY = #{_3desKey},</if>

结果便可以修改成功。至此原因找到,得出以下总结: 使用Mybatis / Mybatis plus 在编译解析xml中sql语句时,无法编译解析数字开头的字段。

解决方法:(使用``将特殊字段包裹起来)
在这里插入图片描述

  • 4
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

new一个对象_

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值