Mybatis中使用xml进行增删改查实践

【1】概要

MyBatis是支持普通sql查询、存储过程和高级映射的半自动持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。

MyBatis可以使用 简单的XML或注解用于配置和原始映射，将接口和Java的POJO(Plain Old Java Objects 普通的Java对象)映射成数据库中的记录。

每一个Mybatis应用程序都以一个sqlSessionFactory对象的实例为核心。

sqlSessionFactory对象的实例可以通过sqlSessionFactoryBuilder对象来获得。sqlSessionFactoryBuilder对象可以通过xml配置文件，或从以往使用管理中准备好的Configuration类实例中来构建sqlSessionFactory对象。

使用配置类获取sqlSessionFactory

BlogMapper是一个含有SQL语句注解的mapper接口。

DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();
TransactionFactory transactionFactory = new JdbcTransactionFactory();
//环境
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration configuration = new Configuration(environment);
//映射器类
configuration.addMapper(BlogMapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

注意这种情况下配置是添加映射器类。映射器类是Java类，这些类包含SQL映射语句的注解从而避免了xml文件的依赖，但是xml映射仍然在大多数高级映射(比如：嵌套join映射)时需要。

出于这样的原因，如果存在xml配置文件的话，MyBatis将会自动查找和加载一个对等的XML文件(这种情况下，基于类路径下的BlogMapper.class类的类名，那么BlogMapper.xml将会被加载–即class 与 XML在同一个文件目录下。否则需要手动配置加载xml)。

其他获取sqlsessionFactory的方法如：

SqlSessionFactory build(Reader reader)
SqlSessionFactory build(Reader reader, String environment) 
SqlSessionFactory build(Reader reader, Properties properties) 
SqlSessionFactory build(Reader reader, String environment, Properties properties)
SqlSessionFactory build(InputStream inputStream)
SqlSessionFactory build(InputStream inputStream, String environment)
SqlSessionFactory build(InputStream inputStream, Properties properties)
SqlSessionFactory build(InputStream inputStream, String environment, Properties properties)
SqlSessionFactory build(Configuration config)

---

【2】基本增删改查xml配置

① 以userMapper实例

<?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.web.mapper.userMapper">
	
	 <!-- 可以解决model属性名与数据表中column列名不一致问题 jdbcType一定要大写 -->	 
	 <resultMap type="User" id="UserMap">
	 	<id property="id" column="id" javaType="int" jdbcType="INTEGER"/>
	 	<result property="name" column="username" javaType="string" jdbcType="VARCHAR"/>
	 	<result property="age" column="age" javaType="int" jdbcType="INTEGER"/>
	 </resultMap>
	 
	 <!--
	 注意这里的result，如果column == property 则可以直接返回Java object。
	 如果属性名与列名不一致，解决方法如下：
	 1. 使用resultMap; 
	 2.返回hashmap ; 
	 3.查询语句使用别名
	 -->
	 <select id="getUser" parameterType="int" resultMap="UserMap">
	 	select * from t_user where id=#{id}
	 </select>
	 
	 <delete id="deleteUser" parameterType="int" >
	 	delete from t_user where id=#{id}
	 </delete>

	 
	 <update id="updateUser" parameterType="User" >
	 	update t_user set username=#{name},age=#{age} where id=#{id}
	 </update>
	 
	 <insert id="insertUser" parameterType="User" >
	 	insert into t_user(username,age) values(#{name},#{age})
	 </insert>
	 
	 <!-- model's attr(name) different from column(username), so the result use UserMap -->
	 
	 <select id="getUsers"  resultMap="UserMap">
	 	select * from t_user
	 </select>
</mapper>

---

② 注册到全局配置文件mybatis.xml

当与spring结合时，将不需要这个配置文件。

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>

	<properties resource="jdbc.properties"/>
	
	<!-- 配置实体类的别名 -->
	<typeAliases>
		<!-- <typeAlias type="com.web.model.User" alias="User"/> -->
		<package name="com.web.model"/>
	</typeAliases>
<!-- 
	development : 开发模式
	work : 工作模式
 -->
	<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>
	<!--xml格式-->
		<mapper resource="com/web/mapper/userMapper.xml"/>
		<mapper resource="com/web/mapper/orderMapper.xml"/>
	<!--sql 注解格式-->		
		<mapper class="com.web.mapperClass.UserMapper"/>
	</mappers>
</configuration>

---

【3】通过SqlSessionFactory拿到session

这里使用xml文件获取sqlSessionFactory和sqlSession。

public static SqlSessionFactory getFactory(){
	String resource = "mybatis.xml";
	InputStream inputStream = MybatisUtils.class.getClassLoader().getResourceAsStream(resource);
	SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
	return factory;
}

SqlSession session = factory.openSession(true);
//默认手动提交；
/*
两种解决方式：
1.factory.opensession(true);
2.session.commit();
*/

其他获取sqlsession的方式如下：

SqlSession openSession()
SqlSession openSession(boolean autoCommit)
SqlSession openSession(Connection connection)
SqlSession openSession(TransactionIsolationLevel level)
SqlSession openSession(ExecutorType execType)
SqlSession openSession(ExecutorType execType, boolean autoCommit)
SqlSession openSession(ExecutorType execType,TransactionIsolationLevel level)
SqlSession openSession(ExecutorType execType, Connection connection)

---

【4】增删改查后台测试代码

① 基本增删改查测试

/*use  sql  xml not annotation*/
	@Test
	public void testAdd(){
		//直接使用sqlsession的方法进行操作，不对userMapper进行代理
		SqlSession session = MybatisUtils.getFactory().openSession();
		String statement = "com.web.mapper.userMapper.insertUser";
		/*return the effect rows*/
		int insert= session.insert(statement, new User("tom5", 15));
		
		/*default is not auto commit*/
		session.commit(true);
		session.close();
		
		System.out.println("effect rows.."+insert);
	}
	@Test
	public void testSelect(){
		/*set auto commit ,which equals to the above*/
		SqlSession session = MybatisUtils.getFactory().openSession(true);
		
		String statement = "com.web.mapper.userMapper.getUser";
		/*return the effect rows*/
		User user =  session.selectOne(statement, 3);
		System.out.println("effect rows.."+user);
	}
	@Test
	public void testUpdate(){
		
		SqlSession session = MybatisUtils.getFactory().openSession(true);
		String statement = "com.web.mapper.userMapper.updateUser";
		/*return the effect rows*/
		int update= session.update(statement, new User(3,"tom4", 13));
		System.out.println("effect rows.."+update);
	}
	@Test
	public void testDelete(){
		
		SqlSession session = MybatisUtils.getFactory().openSession();
		String statement = "com.web.mapper.userMapper.deleteUser";
		/*return the effect rows*/
		int delete= session.delete(statement, 6);
		/* commit by yourself*/
		session.commit();
		System.out.println("effect rows.."+delete);
		session.close();
	}
	
	@Test
	public void testGetUsers(){
		
		SqlSession session = MybatisUtils.getFactory().openSession();
		String statement = "com.web.mapper.userMapper.getUsers";
		/*return the List<User>*/
		List<User> users= session.selectList(statement);
		
		session.commit();
		System.out.println("effect rows.."+users);
		session.close();
	}
	

② 参数类型和返回类型为map

修改userMapper.xml :

<select id="getUserForMap" parameterType="hashmap" resultType="hashmap">
		select * from c_user where id=#{id};
	</select>

测试代码

@Test
public void getUserForMap(){
	
	SqlSession session = MybatisUtils.getFactory().openSession();
	String statement = "com.web.mapper.userMapper.getUserForMap";
	HashMap<String, Object> map = new HashMap<String, Object>();
	map.put("id", 1);
	/*return the effect rows*/
	Object selectOne = session.selectOne(statement, map);
	
	/*default is not auto commit*/
	session.commit(true);
	session.close();
	
	System.out.println("effect rows.."+selectOne+" ,class :"+selectOne.getClass());
}

测试结果

effect rows..{id=1, age=12, name=luli} ,class :class java.util.HashMap