Mybatis异常

2020年12月15日 21点热度 0条评论 来源: 尽力漂亮

文章目录

Mybatis异常合集

1.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘id’ in order clause is ambiguous

;Column ‘id’ in order clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘id’ in order clause is ambiguous] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘id’ in order clause is ambiguou

SQL: select
a.name,b.name,a.protocol_type,a.mn,a.type,a.is_started,a.transfer_type
from cus_point a,cus_enterprise b where a.enterprise_id=b.id
and enterprise_id = ? order by id desc LIMIT ?

尝试解决

发现是后面拼接 order by id模棱两可

2.mybatis多表查询未成功

我的这里需要联查的企业名称未得到

2.1 sql语句两表查询成功,说明是mybatis映射问题

2.2 两表查询 自定义vo类来接收结果 用AS 来实现 别名映射

<?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.shbykj.springboot.common.repository.EnterprisePointVoMapper">

  <resultMap id="EnterprisePointVo" type="com.shbykj.springboot.common.pojo.EnterprisePointVo">
    <result column="id" property="id"/>
    <result column="enterpriseId" property="enterpriseId"/>
    <result column="userId" property="userId"/>
    <result column="name" property="name"/>
    <result column="mn" property="mn"/>
    <result column="type" property="type"/>
    <result column="pass" property="pass"/>
    <result column="num" property="num"/>
    <result column="protocolType" property="protocolType"/>
    <result column="transferType" property="transferType"/>
    <result column="address" property="address"/>
    <result column="longitude" property="longitude"/>
    <result column="latitude" property="latitude"/>
    <result column="remark" property="remark"/>
    <result column="divisorCount" property="divisorCount"/>
    <result column="isDeleted" property="isDeleted"/>
    <result column="gmtCreate" property="gmtCreate"/>
    <result column="gmtModified" property="gmtModified"/>
    <result column="gmtStop" property="gmtStop"/>
    <result column="stopReason" property="stopReason"/>
    <result column="isStarted" property="isStarted"/>

    <result column="enterpriseName" property="enterpriseName" ></result>
  </resultMap>
  <select id="enterprisePointSearch" parameterType="com.shbykj.springboot.query.EnterprisePointQuery" resultType="com.shbykj.springboot.common.pojo.EnterprisePointVo">
      select a.`id,a.`name`,
      b.`name` AS enterpriseName,
      a.protocol_type AS protocolType,
      a.mn,a.type,
      a.is_started AS isStarted,
      a.transfer_type AS transferType from cus_point a,cus_enterprise b where a.enterprise_id=b.id
    <include refid="whereSql"></include>
    order by a.id desc
  </select>

  <sql id="whereSql">
    <if test="name!=null and name!=''">
      AND a.name LIKE CONCAT("%",#{name},"%")
    </if>
   <!-- <if test="ename!=null and ename!=''">
      AND b.name LIKE CONCAT("%",#{ename},"%")
    </if>-->
 <!--   <if test="control_level !=null and control_level !='' ">
      <![CDATA[ and control_level  =  #{control_level}  ]]>
    </if>
    <if test="industry_id !=null and industry_id!='' ">
      <![CDATA[ and industry_id =  #{industry_id}  ]]>
    </if>-->
    <if test="enterpriseId !=null and enterpriseId!='' ">
      <![CDATA[ and enterprise_id =  #{enterpriseId}  ]]>
    </if>
  </sql>

    <!--高级查询-->
    <!--<select id="advancedQuery" parameterType="com.shbykj.springboot.query.CustEnterpriseQuery" resultType="com.shbykj.springboot.query.CustEnterpriseQuery">
        SELECT *
        FROM cus_enterprise
        <include refid="whereSql"></include>
        order by id desc

    </select>
    &lt;!&ndash; sql:放的一些代码片断  &ndash;&gt;
    <sql id="whereSql">
            <if test="name!=null and name!=''">
                AND name LIKE CONCAT("%",#{name},"%")
            </if>
            <if test="control_level !=null and control_level !='' ">
                <![CDATA[ and control_level  =  #{control_level}  ]]>
            </if>
            <if test="industry_id !=null and industry_id!='' ">
                <![CDATA[ and industry_id =  #{industry_id}  ]]>
            </if>
            <if test="id !=null and id!='' ">
                <![CDATA[ and id =  #{id}  ]]>
            </if>
            AND is_deleted = 0
    </sql>-->
</mapper>
package com.shbykj.springboot.common.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author
 * @date 2020-10-16 11:05
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class EnterprisePointVo extends EnterprisePoint{
    /**
     * 企业名称
     */
    private String enterpriseName;

}

package com.shbykj.springboot.common.pojo;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @author
 * @date 2020-10-12 22:37
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "cus_enterprise")
public class EnterprisePoint implements Serializable {
    /**
     * 站点主键
     */
    @Id
    //@GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    /**
     * 企业编号
     */
    @Column(name = "enterprise_id")
    private Long enterpriseId;
    /**
     * 负责人编号
     */
    @Column(name = "user_id")
    private Long userId;
    /**
     * 站点名称
     */
    private String name;
    /**
     * 站点mn名称
     */
    private String mn;
    /**
     * 监测类型
     */
    private Byte type;
    /**
     * 站点密码
     */
    private String pass;
    /**
     * 站点编号
     */
    private String num;
    /**
     * 协议类型
     */
    @Column(name = "protocol_type")
    private String protocolType;
    /**
     * 传输类型
     */
    @Column(name = "transfer_type")
    private Byte transferType;
    /**
     * 站点编号
     */
    private String address;
    /**
     * 经度
     */
    private BigDecimal longitude;
    /**
     * 维度
     */
    private BigDecimal latitude;
    /**
     * 站点说明
     */
    private String remark;
    /**
     * 监测因子数量
     */
    @Column(name = "divisor_count")
    private String divisorCount;
    /**
     * 监测因子数量
     */
    @Column(name = "is_deleted")
    private Byte isDeleted ;

    /**
     * 创建时间
     */
    @Column(name = "gmt_create")
    private Date gmtCreate ;
    /**
     * 更新时间
     */
    @Column(name = "gmt_modified")
    private Date gmtModified ;
    /**
     * 停止时间
     */
    @Column(name = "gmt_stop")
    private Date gmtStop ;

    /**
     * 停止原因
     */
    @Column(name = "stop_reason")
    private String stopReason ;


    /**
     * 是否启用
     */
    @Column(name = "is_started")
    private Byte isStarted ;



    //hh为12小时制,HH为24小时制;GMT+8 东八区
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    public Date getGmtCreate() {
        return gmtCreate;
    }

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    public void setGmtCreate(Date gmtCreate) {
        this.gmtCreate = gmtCreate;
    }

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    public Date getGmtModified() {
        return gmtModified;
    }

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    public void setGmtModified(Date gmtModified) {
        this.gmtModified = gmtModified;
    }


}

3.Could not set parameters for mapping: ParameterMapping{property=‘enterpriseId’, mode=IN, javaType=class java.lang.Long

.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

sql语句写错了少添加了字段的’xxxxx’

4.下拉框能显示值,选中后得到underfined

注意id是否写正确、对应

5. onclick传参是中文报错

正确写法a:

onclick="sysDivisorChooseButton(' + data.data[i].id + ',\'' + data.data[i].name + '\');"  
 如果参数两边不是单引号而是双引号,那么转义符就是\"

6.org.thymeleaf.exceptions.TemplateInputException: Error resolving template [selCity],

6.1 解决:加上注解 @ResponseBody

重点6.mybatis映射错误

1.结构

2.mapper多个实体类别名配置

# mybatis基本信息
mybatis:
  type-aliases-package: com.shbykj.springboot.common.pojo,com.shbykj.springboot.sys.pojo,com.shbykj.springboot.user.pojo
  mapper-locations: /static/mapper/**

3.SpringbootApplication 的@MapperScan配置多个路径写法

@SpringBootApplication
@MapperScan({"com.shbykj.springboot.common.repository","com.shbykj.springboot.sys.dao","com.shbykj.springboot.user.dao"})
public class   SpringbootApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootApplication.class, args);
    }
}

使用tk.mybatis异常

Cause: java.lang.InstantiationException: tk.mybatis.mapper.provider.base.BaseSelectProvider

解决方法:之前导包错误,要导入tk.mapper包的mapperScan

    原文作者:尽力漂亮
    原文地址: https://blog.csdn.net/weixin_44106334/article/details/109159899
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。