MyBatis
步骤一
建立实体类
public class User {
private int id;
private String name;
private String pwd;
编写对应的get、set方法 、有参构造和无参构造、toString方法。
建立UserMapper接口
public interface UserMapper {
// 查询全部用户
List<User> getUserList();
// 根据id的查询用户
User getUserById(int id);
// 增加用户insert
int addUser(User user);
// 修改用户
int updateUser(User user);
// 删除用户
int deleteUser(int id);
**引入MyBatis核心配置文件
<?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核心配置文件-->
<configuration>
<!--引入外部配置文件-->
<properties resource="db.properties"/>
<!--给实体类取别名-->
<typeAliases>
<package name="com.jie.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/jie/dao/UserMapper.xml"/>
<!--<mapper class="com.jie.dao.UserMapper"/>-->
<!--<package name="com.jie.dao"/>-->
</mappers>
</configuration>
编写UserMap.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">
<!--namespace 绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.jie.dao.UserMapper">
<select id="getUserList" resultType="user">
select * from mybatis.user
</select>
<select id="getUserById" parameterType="int" resultType="user">
select * from mybatis.user where id = #{id}
</select>
<!-- 对象中的属性,可以直接取出来-->
<insert id="addUser" parameterType="User">
insert into mybatis.user (id , name , pwd) values (#{id},#{name},#{pwd});
</insert>
<update id="updateUser" parameterType="User">
UPDATE mybatis.user set name =#{name}, pwd=#{pwd} WHERE id = #{id};
</update>
<delete id="deleteUser" parameterType="int">
delete FROM mybatis.user where id = #{id}
</delete>
编写MyBatis工具类
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
// 使用MyBatis第一步:获取sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
}
测试类
@Test
public void test(){
// 第一步: 获得SqlSession 对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//方式一: 执行SQL
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserList();
for (User user : userList) {
System.out.println(user);
}
//关闭SqlSession
sqlSession.close();
结果集映射
当字段名不一样时
<!--结果集映射-->
<resultMap id="UserMap" type="User">
<!--字段名一样可以不写-->
<!--<result column="id" property="id"/>-->
<!--<result column="name" property="name"/>-->
<result column="pwd" property="password"/>
</resultMap>
开启日志
log4j
在MyBatis核心配置文件中开启log4j日志
默认是STDOUT_LOGGING
<settings>
<!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
<setting name="logImpl" value="LOG4J"/>
</settings>
编写log4j.properties 对log4j进行配置
最后在测试类中加入日志
static Logger logger = Logger.getLogger(UserMapperTest.class);
@Test
public void getUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
@Test
public void testlog4j(){
logger.info("info:进入了log4j");
logger.debug("debug:进入了log4j");
logger.error("error:进入了log4j");
}
使用注解
在UserMapper接口中使用注解
省去了xml文件,但是功能不如使用xml文件强大
@Select("select * from user")
List<User> getUsers();
// 方法存在多个参数,所有的参数前面必须加上@Param("id")注解
@Select("select * from user where id= #{id}")
User getUserById(@Param("id") int id);
@Insert("insert into user (id , name, pwd) values (#{id},#{name},#{pwd})")
int addUser(User user);
@Update("update user set name=#{name},pwd=#{pwd} where id=#{id}")
int updateUser(User user);
@Delete("delete from user where id=#{uid}")
int deleteUser(@Param("uid") int id);
嵌套查询和子查询
查询所有的学生信息,以及老师的信息
准备阶段:1.建立Teacher和Student实体类
2.编写两个实体类的Mapper接口
3.编写MyBatis核心配置文件,绑定接口
4.建立对应接口的xml文件
5.创建工具类
5.测试
方式一:子查询
只有步骤四和上面不同,所以这里只写步骤四的代码
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jie.dao.StudentMapper">
<select id="getStudent" resultMap="StudentTeacher">
SELECT * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性我们需要单独处理 对象:association 集合:collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
嵌套查询
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jie.dao.StudentMapper">
<select id="getStudent2" resultMap="StudentTeacher">
SELECT s.id sid, s.name sname,t.name tname
FROM student s,teacher t
WHERE s.tid = t.id;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" column="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
测试类
@Test
public void testStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
注意点
返回一个对象时使用:association
<association property="teacher" column="Teacher">
<result property="name" column="tname"/>
</association>
返回一个集合时使用:collection
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
动态SQL
if、where、set用法
if:可以使用 if 进行判断,对SQL语句进行拼接
set:在update语句中代替set使用,可以自动去掉最后一个 author = #{author}, 中的逗号。
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title != null">
title=#{title},
</if>
<if test="author !=null">
author = #{author},
</if>
</set>
where id = #{id}
</update>
where:用法与set大致相同,在需要使用where时加上,可以自动去掉where后面的and
when:判断与if差不多不同点在于后面接otherwise
otherwise:否则,如果when条件不成立,则otherwise成立
choose:when和otherwise都需要在choose标签内
<select id="queryBlogChoose" resultType="blog" parameterType="map">
select * FROM blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
AND author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
动态SQL片段:把可以重复使用的SQL语句拿出来,建立动态SQL片段。
<!--动态SQL片段-->
<sql id="if-title-author">
<if test="title != null">
title=#{title}
</if>
<if test="author !=null">
and author = #{author}
</if>
</sql>
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * FROM blog
<where>
<include refid="if-title-author"/>
</where>
</select>
一级缓存和二级缓存
一级缓存:一级缓存为默认开启的缓存,只能储存上一次查询的数据,下一次查询后刷新。
二级缓存:FIFO先进先出。
<!--开启二级缓存-->
<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
配置二级缓存ehcache.xml
<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
updateCheck="false">
<diskStore path="./tmpdir/Tmp_EhCache"/>
<defaultCache
eternal="false"
maxElementsInMemory="10000"
overflowToDisk="false"
diskPersistent="false"
timeToIdleSeconds="1800"
timeToLiveSeconds="259200"
memoryStoreEvictionPolicy="LRU"/>
<cache
name="cloud_user"
eternal="false"
maxElementsInMemory="5000"
overflowToDisk="false"
diskPersistent="false"
timeToIdleSeconds="1800"
timeToLiveSeconds="1800"
memoryStoreEvictionPolicy="LRU"/>
</ehcache>
小知识
数据的查询路径为:二级缓存 - - -》一级缓存 - - -》数据库