select中的返回值
1. resultType 实体包全路径,返回值可以是List也可以是对象
2. resultMap :resultMap 映射器id,实体根据映射装配,返回值可以是List也可以是对象
实体和sql列映射4种方法
1.xml resultMap
2.使用as别名,例如: select create_time as createTime where id = 1
3.接口上使用@Results和@Result等同于xml resultMap
4.设置全局驼峰下换线自动转换开关 <setting name="mapUnderscoreToCamelCase" value="true"/>
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zsk</groupId>
<artifactId>mybatis-normal-demo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.apache.lucene</groupId>
<artifactId>lucene-core</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.13.3</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.13.3</version>
</dependency>
</dependencies>
</project>
jdbc.properties
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://127.0.0.1:3306/test_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
db.username=root
db.password=root
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--引入jdbc.propeties文件-->
<properties resource="jdbc.properties"/>
<settings>
<!-- 驼峰映射 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 日志 -->
<setting name="logImpl" value="log4j2"/>
</settings>
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 使用下面的方式配置参数,后面会有所有的参数介绍 -->
<property name="helperDialect" value="mysql"/>
<property name="reasonable" value="true"/>
</plugin>
</plugins>
<environments default="development">
<!-- environment:某一个环境 id:就是这个环境的名称-->
<environment id="development">
<!--
transactionManager:事务管理(ACID)
type="JDBC|MANAGED" jdbc:简单jdbc事务 MANAGED:啥都不做
-->
<transactionManager type="MANAGED"/>
<!-- 数据源(连接池) POOLED:mybatis内置的连接池 -->
<dataSource type="POOLED">
<!--驱动,地址,用户名,密码-->
<property name="driver" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--配置映射xml-->
<mapper resource="com/zsk/IAttrInfoIDao.xml"/>
<mapper resource="com/zsk/AclUserDao.xml"/>
</mappers>
</configuration>
dao/mapper.java
package com.zsk.dao;
import com.zsk.entity.AttrInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface IAttrInfoIDao {
int deleteByPrimaryKey(Long id);
int insert(AttrInfo record);
int insertSelective(AttrInfo record);
AttrInfo selectByPrimaryKey(Long id);
int updateByPrimaryKeySelective(AttrInfo record);
int updateByPrimaryKey(AttrInfo record);
int batchInsert(List<AttrInfo> list);
List<AttrInfo> findByName(@Param("name") String name);
List<AttrInfo> findAll(@Param("maxId") long maxId);
int deleteByName(@Param("name") String name);
boolean existByName(@Param("name") String name);
List<AttrInfo> findByColNameAndValue(@Param("colName") String colName, @Param("colValue") String colValue);
}
dao/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.zsk.dao.IAttrInfoIDao">
<resultMap id="BaseResultMap" type="com.zsk.entity.AttrInfo">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="value" jdbcType="BIGINT" property="value"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<sql id="Base_Column_List">
id, `name`, `value`, create_time, update_time
</sql>
<sql id="common_col">
id, create_time, update_time
</sql>
<sql id="common_col_class">
#{item.id}, #{item.createTime}, #{item.updateTime}
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from attr_info
where id = #{id,jdbcType=BIGINT}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from attr_info
where id = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.zsk.entity.AttrInfo" useGeneratedKeys="true">
insert into attr_info(
`name`,
`value`,
`create_time`,
`update_time`
)
values(
#{name,jdbcType=VARCHAR},
#{value,jdbcType=BIGINT},
#{createTime,jdbcType=TIMESTAMP},
#{updateTime,jdbcType=TIMESTAMP}
)
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.zsk.entity.AttrInfo"
useGeneratedKeys="true">
insert into attr_info
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null">
`name`,
</if>
<if test="value != null">
`value`,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="updateTime != null">
update_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="value != null">
#{value,jdbcType=BIGINT},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.zsk.entity.AttrInfo">
update attr_info
<set>
<if test="name != null">
`name` = #{name,jdbcType=VARCHAR},
</if>
<if test="value != null">
`value` = #{value,jdbcType=BIGINT},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
update_time = #{updateTime,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKey" parameterType="com.zsk.entity.AttrInfo">
update attr_info
set
`name` = #{name,jdbcType=VARCHAR},
`value` = #{value,jdbcType=BIGINT},
`create_time` = #{createTime,jdbcType=TIMESTAMP},
`update_time` = #{updateTime,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=BIGINT}
</update>
<insert id="batchInsert" parameterType="java.util.List">
insert into attr_info (`name`, `value`,`id`, create_time, update_time)
values
<foreach collection="list" item="item" separator=",">
(
#{item.name},
#{item.value},
<include refid="common_col_class"/>
)
</foreach>
</insert>
<select id="findByName" resultType="com.zsk.entity.AttrInfo">
select
name,value,
<include refid="common_col"/>
from attr_info where `name` = #{name}
</select>
<select id="findAll" resultType="com.zsk.entity.AttrInfo">
SELECT
name,value,
<include refid="common_col"/>
FROM attr_info WHERE id > #{maxId} order by id
</select>
<delete id="deleteByName">
DELETE FROM attr_info WHERE `name` = #{name}
</delete>
<select id="existByName" resultType="boolean">
SELECT (SELECT COUNT(id) FROM attr_info WHERE name = #{name})>0 as res
</select>
<!-- 动态传入列名称 -->
<select id="findByColNameAndValue" resultType="com.zsk.entity.AttrInfo">
SELECT * FROM attr_info WHERE `${colName}` = #{colValue}
</select>
</mapper>
使用
// 加载 mybatis 全局配置文件
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("mybatis-config.xml");
// 构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 根据 sqlSessionFactory 产生 session
SqlSession session = sessionFactory.openSession();
IAttrInfoIDao attrDao = session.getMapper(IAttrInfoIDao.class);
// 如果是insert/update需要提交
session.commit();
session.close();