普通项目使用mybatis
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();
