mybaits笔记

发表于 2019-03-29 更新于 2022-05-28 分类于 java 阅读次数： Waline：本文字数： 9.9k 阅读时长 ≈ 9 分钟

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dataserver?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&allowMultiQueries=true
username=admin
password=admin

Java中使用

CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_delete` tinyint(11) DEFAULT '0' COMMENT '0:未删除 1:已删除',
  `user_name` varchar(32) DEFAULT '' COMMENT '用户名',
  `user_age` varchar(3) DEFAULT '' COMMENT '用户年龄',
  `user_address` varchar(128) DEFAULT '' COMMENT '用户地址',
  `user_tel` varchar(16) DEFAULT '' COMMENT '用户电话',
  `user_type` tinyint(4) DEFAULT '0' COMMENT '用户等级 0:普通 1:高级',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='用户信息表';

增

<insert id="insert" parameterType="cn.wkq.domain.UsrInfoDo">
  <!--
    WARNING - @mbggenerated
    This element is automatically generated by MyBatis Generator, do not modify.
  -->
  insert into user_info (id, create_time, update_time, 
    is_delete, user_name, user_age, 
    user_address, user_tel, user_type
    )
  values (#{id,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}, 
    #{isDelete,jdbcType=TINYINT}, #{userName,jdbcType=VARCHAR}, #{userAge,jdbcType=VARCHAR}, 
    #{userAddress,jdbcType=VARCHAR}, #{userTel,jdbcType=VARCHAR}, #{userType,jdbcType=TINYINT}
    )
</insert>

<insert id="batchInsert">
    insert into user_info (id, create_time, update_time,
    is_delete, user_name, user_age,
    user_address, user_tel, user_type
    )
    <foreach collection="list" separator="," item="item">
        (
        #{item.id,jdbcType=INTEGER}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP},
        #{item.isDelete,jdbcType=TINYINT}, #{item.userName,jdbcType=VARCHAR}, #{item.userAge,jdbcType=VARCHAR},
        #{item.userAddress,jdbcType=VARCHAR}, #{item.userTel,jdbcType=VARCHAR}, #{item.userType,jdbcType=TINYINT}
        )
    </foreach>
</insert>

注意：foreach 里配置 separator=","，不需要在sql结尾加 ,

删

<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    delete from user_info
    where id = #{id,jdbcType=INTEGER}
</delete>

改

<update id="updateByPrimaryKey" parameterType="cn.wkq.domain.UsrInfoDo">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update user_info
    set create_time = #{createTime,jdbcType=TIMESTAMP},
    update_time = #{updateTime,jdbcType=TIMESTAMP},
    is_delete = #{isDelete,jdbcType=TINYINT},
    user_name = #{userName,jdbcType=VARCHAR},
    user_age = #{userAge,jdbcType=VARCHAR},
    user_address = #{userAddress,jdbcType=VARCHAR},
    user_tel = #{userTel,jdbcType=VARCHAR},
    user_type = #{userType,jdbcType=TINYINT}
    where id = #{id,jdbcType=INTEGER}
</update>

<update id="updateByPrimaryKeySelective" parameterType="cn.wkq.domain.UsrInfoDo">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update user_info
    <set>
        <if test="createTime != null">
            create_time = #{createTime,jdbcType=TIMESTAMP},
        </if>
        <if test="updateTime != null">
            update_time = #{updateTime,jdbcType=TIMESTAMP},
        </if>
        <if test="isDelete != null">
            is_delete = #{isDelete,jdbcType=TINYINT},
        </if>
        <if test="userName != null">
            user_name = #{userName,jdbcType=VARCHAR},
        </if>
        <if test="userAge != null">
            user_age = #{userAge,jdbcType=VARCHAR},
        </if>
        <if test="userAddress != null">
            user_address = #{userAddress,jdbcType=VARCHAR},
        </if>
        <if test="userTel != null">
            user_tel = #{userTel,jdbcType=VARCHAR},
        </if>
        <if test="userType != null">
            user_type = #{userType,jdbcType=TINYINT},
        </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
</update>

<update id="batchUpdateAllColumns">
    <foreach collection="list" separator=";" item="item">
        update user_info
        set create_time = #{createTime,jdbcType=TIMESTAMP},
        update_time = #{updateTime,jdbcType=TIMESTAMP},
        user_name = #{userName,jdbcType=VARCHAR},
        user_age = #{userAge,jdbcType=VARCHAR},
        user_address = #{userAddress,jdbcType=VARCHAR},
        user_tel = #{userTel,jdbcType=VARCHAR},
        user_type = #{userType,jdbcType=TINYINT}
        where id = #{id,jdbcType=INTEGER}
    </foreach>
</update>

注意，foreach里配置了 separator=";" ，不要的update语句后加 ; ，否则会报错
此种写法需要在 jdbc url 里配置 &allowMultiQueries=true

<update id="batchDelete">
    UPDATE car_category
    SET is_delete = 1 
    WHERE id in
    <foreach collection="ids" item="item" separator="," open="(" close=")">
        #{item}
    </foreach>
</update>

查

<select id="getDataById" resultType="cn.wkq.domain.UsrInfoDo">
  SELECT id, create_time, update_time, user_name
  FROM user_info
  WHERE id >= #{startId}
  <![CDATA[
  AND id <= #{endId}
  ]]>
</select>

<sql id="Base_Column_List">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    id, create_time, update_time, user_name, user_age, user_address, user_tel, user_type
</sql>

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select
    <include refid="Base_Column_List"/>
    from user_info
    where id = #{id,jdbcType=INTEGER}
</select>

<select id="getPageable" resultType="cn.wkq.domain.UsrInfoDo">
    select
    <include refid="Base_Column_List"/>
    from user_info
    <if test="limit != null">
        limit
        <if test="offset != null">
            ${offset},
        </if>
        ${limit}
    </if>
</select>

遇到的问题

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘;

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; 
      update tablexxx
      set control_type = 'C',
      manufacturers ' at line 1
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; 
      update tablexxx
      set control_type = 'C',
      manufacturers ' at line 1
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:230) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]

被队友坑了，jdbc里配置的 &allowMultiQueries=true 导致 &allowMultiQueries=true 没有生效

mybatis批量更新报错问题解决

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘update

org.springframework.jdbc.BadSqlGrammarException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update tablexxx
      set control_type = 'C',
      manufacturers = '克莱' at line 210
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL:  报错的SQL有546664字符，大概有547KB
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update tablexxx
      set control_type = 'C',
      manufacturers = '克莱' at line 210
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update tablexxx
      set control_type = 'C',
      manufacturers = '克莱' at line 210
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:230) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
    ...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update tablexxx
      set control_type = 'C',
      manufacturers = '克莱' at line 210
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_20]
	...

检查 jdbc url配置里是否有 &allowMultiQueries=true
检查 mysql xml 里是否在 foreach里配置了 separator=";" sql语句里又写了 ;
检查 mysql 版本

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

limit 1

Cause: java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp

org.springframework.dao.TransientDataAccessResourceException: Error attempting to get column 'gmt_create' from result set.  Cause: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
; SQL []; Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp; nested exception is java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108) ~[spring-jdbc-4.2.4.RELEASE.jar:4.2.4.RELEASE]

Caused by: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

数据问题

代码里可以用String (不推荐)

Caused by: org.apache.ibatis.builder.BuilderException: Error creating document instance. Cause: org.xml.sax.SAXParseException

Caused by: org.apache.ibatis.builder.BuilderException: Error creating document instance.  Cause: org.xml.sax.SAXParseException; lineNumber: 39; columnNumber: 17; The content of elements must consist of well-formed character data or markup.

sql语句里有特殊符号，比如 <

<![CDATA[
AND id <= #{endId}
]]>

References

[1] mybatis简介-官方中文文档
[2] mybatis批量更新报错问题解决
[3] 自动生成mapper-xml和对应数据库实体类