MyBatis代码笔记--0.0

MyBatis-01基于使用

一、入门实例(普通Java工程)：完成简单的ORM映射

0.1环境搭建：

• 数据库建库建表：--mysql5.5   --mybatisdb库  --user表   --若干字段
• Java工程环境搭建：JDK1.8；JUnit4;
• 其他依赖

1.写一个pojo:User.java，此可理解为ORM--O

public class User {
	
	private int id;//id
	private String name;//用户名
	private String addr;//地址
	private int age;//年龄
    /** 省略setter and getter */
    /** 省略全字段toString() */
}

2.写个UserMapper.xml,此可以视为ORM--M

引入DTD约束

<?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是映射文件的唯一标识 -->     
<mapper namespace="userns">
	<!-- select标签,这里用于查询,
	id是该标签唯一标志，resultType是指结果集要封装哪个实体值是类的全路径 -->
	<select id="all" resultType="cn.tedu.pojo.User">
	select * from user
	</select>
</mapper>

3.引入log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

4.写一个测试类TestMyBatis.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import cn.tedu.pojo.User;

/**
 *测试mybatis入门案例 
 *1.创建会话工厂
 *2.创建sqlsession，执行sql
 *3.处理结果
 *4.释放资源
 */
public class TestMybatis {
	//单元测试
	@Test
	public void hello() throws IOException {
		//1.创建会话工厂
		InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
		//2.创建sqlsession，执行sql
		SqlSession session = ssf.openSession();
		//定位sql的位置namespace值+id值
		List<User> list = session.selectList("userns.all");
		//3.处理结果
		for (User user : list) {
			System.out.println(user);
		}
		//4.释放资源
		session.close();
	}
}

 

二、入门实例(maven项目)：完成简单的ORM映射<增加一个pom.xml即可>

1.maven项目的pom.xml配置

<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>cn.tedu</groupId>
	<artifactId>mybatisday01V2.0</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<build>
		<plugins>
			<!-- 添加JDK插件(版本1.8) -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.5.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
		</plugins>
	</build>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>
		<!--添加mysql驱动程序依赖 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.40</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId><!-- 组id -->
			<artifactId>junit</artifactId><!-- 项目id -->
			<version>4.11</version><!-- 版本号 -->
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.2.8</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
	</dependencies>
</project>

---------------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------------

三、MyBatis之Dao和*Mapper.xml

1.写一个User类<同上>和UserMapper.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="usernp">
	<!-- <sql>+<include> 查询user表中id是2的记录 -->
	<sql id="colums">
		id,name,addr,age
	</sql>
	<select id="findOne" resultType="User">
		select
		<include refid="colums" />
		from user
		<where>
			id=#{id}
		</where>
	</select>
	<!-- if标签 -->
	<select id="ifsql" resultType="User">
		select
		<include refid="colums" />
		from user
		<where>
			<if test="id=!null and id!=0">id=#{id}</if>
			<if test="name!=null">and name=#{name}</if>
			<if test="addr!=null">and addr=#{addr}</if>
		</where>
	</select>

	<update id="setsql" parameterType="User">
		update user
		<set>age=#{age}</set>
		<where>
			<if test="id!=null">id=#{id}</if>
		</where>
	</update>

	<select id="foreacharray" resultType="User">
		select * from user where id in
		(
		<foreach collection="array" item="o" separator=",">#{o}</foreach>
		)
	</select>

	<delete id="foreachlist">
		delete from user where id in
		<foreach collection="list" item="o" separator="," open="("
			close=")">#{o}</foreach>
	</delete>

	<update id="foreachupdate">
		update user set age=18 where id in(
		<!-- collection的值：是测试类中传递的map中的key -->
		<foreach collection="key" separator="," item="i">
			#{i}
		</foreach>
		)
	</update>
</mapper>

 

2.写一个Dao接口:UserMapperDao.java和InterfaceMapper.xml

UserMapperDao.java

public abstract interface UserMapperDao {
	public abstract List<User> findAll();
	int count();
	User findOne();
	User findOne2(int id);
	int save(User user);
	int update(User user);
	int del(String str);
}

InterfaceMapper.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">
<!-- 写sql -->
<mapper namespace="cn.tedu.dao.UserMapperDao">
	<!-- 查询所有记录 -->
	<select id="findAll" resultType="User"><!-- /mybatisday01V2.0/src/main/java/ -->
		select * from user
	</select>
	<select id="count" resultType="int">
		select count(*) from user
	</select>
	<select id="findOne" resultType="cn.tedu.pojo.User">
		select * from user where id=1
	</select>
	<select id="findOne2" resultType="cn.tedu.pojo.User">
		select * from user where id=#{id}
	</select>
	<insert id="save" parameterType="cn.tedu.pojo.User">
		insert into user values(null,#{name},#{addr},#{age})
	</insert>
	<insert id="update" parameterType="cn.tedu.pojo.User">
		update user set age=#{age} where name=#{name}
	</insert>
	<delete id="del" parameterType="String">
		delete from user where name=#{name}
	</delete>
</mapper>

3.写一个database.properties，sqlMapConfig.xml

database.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatisdb
username=root
password=root

sqlMapConfig.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>
	<!-- properties -->
	<properties resource="database.properties"></properties>
	<!-- 设置别名 -->
	<typeAliases>
		<typeAlias type="cn.tedu.pojo.User" alias="User" />
	</typeAliases>
	<!-- 配置开发环境,全局配置文件 -->
	<environments default="test">
		<!-- 测试环境 -->
		<environment id="test">
			<!-- mybatis事务交给jdbc管理 -->
			<transactionManager type="jdbc"></transactionManager>
			<!-- 配置数据源（连接池），池化的 -->
			<dataSource type="pooled">
				<!-- 聚焦<configuration>查看各种标签位置 -->
				<property name="driver" value="${driverClass}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- 引入映射文件 -->
	<mappers>
		<mapper resource="mapper/UserMapper.xml" /><!-- /mybatisday01/src/ -->
		<mapper resource="mapper/InterfaceMapper.xml"/><!-- /mybatisday02/src/main/resources/ -->
	</mappers>
</configuration>

4.测试类DynamicSqlTest.java和InterfaceTest.java

DynamicSqlTest.java

/**
 * 动态sql的测试
 */
public class DynamicSqlTest {
	SqlSessionFactory ssf;//线程安全
	@Before
	public void init() {
		try {
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			ssf = new SqlSessionFactoryBuilder().build(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void sqlInclude() {
		SqlSession session = ssf.openSession();
		User user = session.selectOne("usernp.findOne",2);
		System.out.println(user);
		session.close();
	}
	@Test
	public void ifsql() {
		SqlSession session = ssf.openSession();
		User user = new User();
		user.setId(1);
		//user.setName("guangtouqiang");
		user.setAddr("北京");
		User select = session.selectOne("usernp.ifsql",user);
		System.out.println("这个select "+select);
		session.close();
	}
	@Test
	public void setsql() {
		SqlSession session = ssf.openSession(true);
		User user = new User();
		user.setAge(1000);
		user.setId(2);
		int update = session.update("usernp.setsql",user);
		System.out.println("修改了"+update+"条");
		session.close();
	}
	@Test
	public void foreacharray() {
		SqlSession session = ssf.openSession();
		List<User> list = session.selectList("usernp.foreacharray",new int[] {1,2,3} );
		for (User user : list) {
			System.out.println(user);
		}
		session.close();
	}
	@Test
	public void foreachlist() {
		SqlSession session = ssf.openSession(true);
		List<Integer> list = new ArrayList<>();
		list.add(1);
		list.add(2);
		session.delete("usernp.foreachlist",list);
		session.close();
	}
	@Test
	public void foreachupdate() {
		SqlSession session = ssf.openSession(true);
		Map<String,Object> map = new HashMap<>();
		map.put("key", new int[] {1,2,3});
		session.update("usernp.foreachupdate",map);
		session.close();
	}
}

InterfaceTest.java

/**
 * 测试mapper接口 
 */
public class InterfaceTest {
	SqlSessionFactory ssf;
	@Before
	public void init() {
		try {
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			ssf = new SqlSessionFactoryBuilder().build(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	@Test
	public void all() {
		//1.获取sqlsession，执行sql
		SqlSession session = ssf.openSession();
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		List<User> list = dao.findAll();
		for (User user : list) {
			System.out.println(user);
		}
		session.close();
	}
	@Test
	public void count() {
		SqlSession session = ssf.openSession();
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		int count = dao.count();
		System.out.println(count);
		session.close();
	}
	@Test
	public void findOne() {
		SqlSession session = ssf.openSession();
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		User user = dao.findOne();
		System.out.println(user);
		session.close();
	}
	@Test
	public void findOne2() {
		SqlSession session = ssf.openSession();
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		User user = dao.findOne2(2);
		System.out.println(user);
		session.close();
	}
	@Test
	public void save() {
		SqlSession session = ssf.openSession(true);
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		User user = new User();
		user.setName("预知未来");
		user.setAddr("在今天");
		user.setAge(18);
		int save = dao.save(user);
		System.out.println(save);
		session.close();
	}
	@Test
	public void update() {
		SqlSession session = ssf.openSession(true);
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		User user = new User();
		user.setAge(26);
		user.setName("念你");
		int update = dao.update(user);
		System.out.println(update);
		session.close();
	}
	@Test
	public void del() {
		SqlSession session = ssf.openSession(true);
		UserMapperDao dao = session.getMapper(UserMapperDao.class);
		String str = "ok";
		int del = dao.del(str);
		System.out.println(del);
		session.close();
	}
}

四、多表联查和一二级缓存

UserInfoMapper.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="cn.tedu.dao.UserInfoDao">
	<!-- 开启当前mapper级别的二级缓存 -->
	<cache></cache>
	<!-- namespace值是mapper的唯一限定名，写UserInfoMapper接口完全限定名，
在UserInfoMapper接口中定义相关方法，在Test类中实现并利用测试单元进行测试 -->
	<!-- resultMap封装不规则的字段 type:结果集要封装在哪个实体对象上就是这个
对象的全路径（别名） id:唯一标志 -->
	<resultMap id="userRM" type="UserInfo">
		<!-- 配置主键信息，column="" properties="" -->
		<id column="id" property="id" />
		<result column="user_name" property="userName" />
		<result column="user_addr" property="userAddr" />
		<result column="user_age" property="userAge" />
	</resultMap>
	<!-- 绑定一对一的userextra的关系 type:结果集要封装在哪个实体对象上就是这个
对象的全路径（别名） id:唯一标志 -->
	<resultMap type="UserInfo" id="userExtraRM" extends="userRM">
		<!-- 一对一关联关系 -->
		<association property="userExtra"
			javaType="cn.tedu.pojo.UserExtra">
			<id column="id" property="id" />
			<result column="user_id" property="userId" />
			<result column="work" property="work" />
			<result column="salary" property="salary" />
		</association>
	</resultMap>
	<!-- 增加关联查询sql -->
	<select id="findExtraByUser" resultMap="userExtraRM">
		<!-- 内连接/外连接（左/右/全） -->
		select * from user_info t1,user_extra t2
		where t1.id = t2.user_id and
		t1.id=#{id}
	</select>
	<!-- 绑定一对多的对象关系的order -->
	<resultMap type="UserInfo" id="orderRM" extends="userRM">
		<collection property="orders" ofType="cn.tedu.pojo.Orders">
			<id column="oid" property="id" />
			<result column="user_id" property="userId"></result>
			<result column="order_no" property="orderNo"></result>
			<result column="order_desc" property="orderDesc"></result>
			<result column="price" property="price"></result>
		</collection>
	</resultMap>
	<!-- 根据用户id查询所有订单信息 -->
	<select id="findOrdersByUser" resultMap="orderRM">
		select
		t1.id,t1.user_name,t1.user_addr,t1.user_age,
		t2.id as oid,t2.user_id,t2.order_no,t2.order_desc,t2.price
		from user_info
		t1,orders t2
		where t1.id = t2.user_id and t1.id=#{id}
	</select>
</mapper>

sqlMapConfig.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>
	<!-- properties -->
	<properties resource="db.properties"></properties>
	<!-- 开启二级缓存 -->
	<settings>
		<setting name="cacheEnabled" value="true"/>
	</settings>
	<!-- 设置别名 -->
	<typeAliases>
		<typeAlias type="cn.tedu.pojo.UserInfo" alias="UserInfo" />
	</typeAliases>
	<!-- 配置开发环境,全局配置文件 -->
	<environments default="test">
		<!-- 测试环境 -->
		<environment id="test">
			<!-- mybatis事务交给jdbc管理 -->
			<transactionManager type="jdbc"></transactionManager>
			<!-- 配置数据源（连接池），池化的 -->
			<dataSource type="pooled">
				<!-- 聚焦<configuration>查看各种标签位置 -->
				<property name="driver" value="${driverClass}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- 引入映射文件 -->
	<mappers>
		<mapper resource="mapper/UserInfoMapper.xml"/>
	</mappers>
</configuration>