mybatis入门示例传智燕青讲解一

示例一：增删改查
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">
<!-- 
	1、properties属性
	注意：mybatis属性加载顺序
		（1）、在properties元素体内定义的属性首先被读取
		（2）、读取properties元素中resource或url加载的配置文件，它会覆盖已读取的同名属性
		（3）、最后读取parameterType传递的属性，它会覆盖已读取的同名属性
	建议：不要在properties元素体内添加任何属性值
		  在properties文件中定义属性名要有一定的特殊性

	2、settings mybatis全局参数设置
	3、typeAliases别名设置

-->
<configuration>
	<!-- 加载属性文件 -->
	<properties resource="dbSources.properties">
		<!-- 还可以配置属性名和属性值 -->
		<!-- <property name="jdbc.password" value="root"/>  -->
		<!-- <property name="value" value="c"/> -->
	</properties>
	<typeAliases>
		<typeAlias type="com.chen.pojo.User" alias="_User"></typeAlias>
	</typeAliases>
	<environments default="development">
	  <environment id="development">
		<transactionManager type="JDBC" />
		<dataSource type="POOLED">
			<property name="driver" value="${jdbc.driver}"/>
			<property name="url" value="${jdbc.url}"/>
			<property name="username" value="${jdbc.username}"/>
			<property name="password" value="${jdbc.password}"/>
		</dataSource>
	  </environment>
	</environments>
	<mappers>
		<mapper resource="sqlMapper/UserMapper.xml" ></mapper>
		<mapper resource="sqlMapper/UserMapper2.xml"/>

		<!-- 通过mapper接口加载单个映射文件
			需遵循一些规范：需要将mapper接口类名和mapper.xml映射文件名保持一致
			且在一个目录中；前提：使用的是mapper代理方法
		 -->
		 <!-- <mapper class="com.chen.mapper2.UserMapper"></mapper> -->

		 <!-- 批量加载mapper
		 	指定mapper接口的包名，mybatis自动扫描包下所有mapper接口进行加载
		 	需遵循一些规范：需要将mapper接口类名和mapper.xml映射文件名保持一致
			且在一个目录中；前提：使用的是mapper代理方法
		  -->
		 <mapper class="com.chen.mapper3.UserMapper"></mapper>
		 <package name="com.chen.mapper2"/>

	</mappers>
</configuration>

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="com.chen.pojo.UserMapper">
	<!-- 
		id:标识映射文件中的sql
		将sql语句封装到mappedStatement对象中，所以将id称为statement的id
		parameterType:指定输入参数的类型
		#{}表示一个占位符号
		resultType:指定sql输出结果
	 -->
	<select id="findUserById" parameterType="Long" resultType="_User">
		select * from t_user where id=#{id}
	</select>

	<!-- 
		resultType:指定就是单条记录所映射的java对象类型
		${}：表示拼接sql串，将接收到参数的内容不加任何修饰拼接到sql中
		使用${}拼接sql,引起sql注入,存在安全隐患不建议使用
		${value}：接收输入的参数的内容，如果传入的类型是简单类型，只能用value

	 -->
	<select id="findUserByName" parameterType="String" resultType="com.chen.pojo.User">
		select * from t_user where username like '%${value}%'
	</select>

	<insert id="insertUser" parameterType="com.chen.pojo.User" useGeneratedKeys="true" keyProperty="id">
		insert into t_user(username,birthday) values(#{username},#{birthday})
	</insert>
	<!-- 
		插入记录返回自增长主键方法二
		SELECT LAST_INSERT_ID()：得到刚insert进去记录的主键值，只适用于自增长
		keyProperty:将查询到主键值设置到parameterType指定的对象中的属性
		order :SELECT LAST_INSERT_ID()执行顺序，相对于insert语句
		resultType:SELECT LAST_INSERT_ID()返回结果类型
	 -->
	<insert id="insertUser2" parameterType="com.chen.pojo.User" >
		<selectKey keyProperty="id" order="AFTER" resultType="Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into t_user(username,birthday) values(#{username},#{birthday})
	</insert>

	<delete id="deleteUserById" parameterType="Long">
		delete from t_user where id=#{id}
	</delete>

	<update id="updateUserById" parameterType="com.chen.pojo.User">
		update t_user set username=#{username},birthday=#{birthday} where id=#{id}
	</update>
</mapper>

Demo.java

package com.chen.demo;

import java.io.IOException;
import java.io.Reader;
import java.util.Date;
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 com.chen.pojo.User;

public class Demo1 {
	private static SqlSessionFactory sqlSessionFactory= null;
	static{
		String resources = "sqlMapConfig.xml";
		try {
			Reader reader = Resources.getResourceAsReader(resources);
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		test2();
	}
	public static void test1(){
		SqlSession session = sqlSessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.findUserById";
		User user = (User) session.selectOne(sql, 1L);
		System.out.println(user);
		session.close();
	}

	public static void test2(){
		SqlSession session = sqlSessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.findUserByName";
		List<User> users =  session.selectList(sql, "c");
		System.out.println(users);
		session.close();
	}

	public static void test3(){
		SqlSession session = sqlSessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.insertUser";
		User u = new User();
		u.setUsername("冬天");u.setBirthday(new Date());
		session.insert(sql, u);
		session.commit();
		System.out.println(u);
		session.close();
	}
	public static void test4(){
		SqlSession session = sqlSessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.insertUser2";
		User u = new User();
		u.setUsername("春天");u.setBirthday(new Date());
		session.insert(sql, u);
		session.commit();
		System.out.println(u);
		session.close();
	}

	public static void test5(){
		SqlSession session = sqlSessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.deleteUserById";
		session.delete(sql, 25L);
		session.commit();
		session.close();
	}
	public static void test6(){
		SqlSession session = sqlSessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.updateUserById";
		String sql2="com.chen.pojo.UserMapper.findUserById";
		User u = session.selectOne(sql2, 24L);
		u.setUsername("冬天的忧伤");
		session.update(sql, u);
		session.commit();
		session.close();
	}

}

示例二：原始Dao开发方法
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="com.chen.pojo.UserMapper">
	<!-- 
		id:标识映射文件中的sql
		将sql语句封装到mappedStatement对象中，所以将id称为statement的id
		parameterType:指定输入参数的类型
		#{}表示一个占位符号
		resultType:指定sql输出结果
	 -->
	<select id="findUserById" parameterType="Long" resultType="_User">
		select * from t_user where id=#{id}
	</select>

	<!-- 
		resultType:指定就是单条记录所映射的java对象类型
		${}：表示拼接sql串，将接收到参数的内容不加任何修饰拼接到sql中
		使用${}拼接sql,引起sql注入,存在安全隐患不建议使用
		${value}：接收输入的参数的内容，如果传入的类型是简单类型，只能用value

	 -->
	<select id="findUserByName" parameterType="String" resultType="com.chen.pojo.User">
		select * from t_user where username like '%${value}%'
	</select>

	<insert id="insertUser" parameterType="com.chen.pojo.User" useGeneratedKeys="true" keyProperty="id">
		insert into t_user(username,birthday) values(#{username},#{birthday})
	</insert>
	<!-- 
		插入记录返回自增长主键方法二
		SELECT LAST_INSERT_ID()：得到刚insert进去记录的主键值，只适用于自增长
		keyProperty:将查询到主键值设置到parameterType指定的对象中的属性
		order :SELECT LAST_INSERT_ID()执行顺序，相对于insert语句
		resultType:SELECT LAST_INSERT_ID()返回结果类型
	 -->
	<insert id="insertUser2" parameterType="com.chen.pojo.User" >
		<selectKey keyProperty="id" order="AFTER" resultType="Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into t_user(username,birthday) values(#{username},#{birthday})
	</insert>

	<delete id="deleteUserById" parameterType="Long">
		delete from t_user where id=#{id}
	</delete>

	<update id="updateUserById" parameterType="com.chen.pojo.User">
		update t_user set username=#{username},birthday=#{birthday} where id=#{id}
	</update>
</mapper>

UserDao.java
UserDaoImpl.java

package com.chen.dao;

import com.chen.pojo.User;

public interface UserDao {
	public User findUserById(Long id);

	public void updateUserById(User u);

	public void deleteUserById(Long id);
}


package com.chen.dao;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.chen.pojo.User;
/*
 * （1）原始Dao开发方法
 * 问题：1、dao接口实现类方法存在大量模板方法，设想将模板方法提取出来
 *   2、sqlsession调用方法时将statement的id硬编码了（ sql="com.chen.pojo.UserMapper.findUserById"）
 *   3、sqlsession调用方法时传入参数类型错误，编译期不会报错
 * 	
 * 问题：接口方法输入参数只能为一个
 * 解决：通过包装类型包含多个参数
 * 注意：持久层可以使用包装类型，service层建议不使用包装类型
 */
public class UserDaoImpl implements UserDao {
	private SqlSessionFactory sessionFactory ;
	public UserDaoImpl() {
	}
	public UserDaoImpl(SqlSessionFactory factory){
		this.sessionFactory=factory;
	}

	public User findUserById(Long id) {
		SqlSession session = sessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.findUserById";
		User u =session.selectOne(sql, id);
		session.close();
		return u;
	}

	public void updateUserById(User u) {
		SqlSession session = sessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.updateUserById";
		session.update(sql, u);
		session.commit();
		session.close();
	}

	public void deleteUserById(Long id) {
		SqlSession session = sessionFactory.openSession();
		String sql="com.chen.pojo.UserMapper.deleteUserById";
		session.delete(sql, id);
		session.commit();
		session.close();
	}

}

Demo.java

package com.chen.demo;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.chen.dao.UserDao;
import com.chen.dao.UserDaoImpl;
import com.chen.pojo.User;

public class Demo2_UserDao {
	private static SqlSessionFactory sqlSessionFactory= null;
	static{
		String resources = "sqlMapConfig.xml";
		try {
			Reader reader = Resources.getResourceAsReader(resources);
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		test1();
	}

	public static void test1(){
		UserDao userDao = new UserDaoImpl(sqlSessionFactory);
		User u = userDao.findUserById(24L);
		System.out.println(u);
	}

}

示例三：mapper代理方法
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="com.chen.mapper.UserMapper">
	<select id="findUserById" parameterType="Long" resultType="com.chen.pojo.User">
		select * from t_user where id=#{id}
	</select>
	<select id="findUserByName" parameterType="String" resultType="com.chen.pojo.User">
		select * from t_user where username like '%${value}%'
	</select>
	<insert id="insertUser" parameterType="com.chen.pojo.User" useGeneratedKeys="true" keyProperty="id">
		insert into t_user(username,birthday) values(#{username},#{birthday})
	</insert>
	<insert id="insertUser2" parameterType="com.chen.pojo.User" >
		<selectKey keyProperty="id" order="AFTER" resultType="Long">
			SELECT LAST_INSERT_ID()
		</selectKey>
		insert into t_user(username,birthday) values(#{username},#{birthday})
	</insert>

	<delete id="deleteUserById" parameterType="Long">
		delete from t_user where id=#{id}
	</delete>

	<update id="updateUserById" parameterType="com.chen.pojo.User">
		update t_user set username=#{username},birthday=#{birthday} where id=#{id}
	</update>
</mapper>

UserMapper.java

package com.chen.mapper;

import java.util.List;

import com.chen.pojo.User;
/*
 * (2)、mapper代理方法
 *  1、需要编写mapper.xml映射文件
 *	mapper接口编写需要遵循一些规范，mybatis就可以自动生成mapper接口实现类代理对象
 *    （1）、在mapper.xml中namespace等于接口mapper.java地址(com.chen.mapper。UserMapper)
 *    （2）、mapper.java接口中的方法名和mapper.xml中statement的id一致
 *    （3）、mapper.java接口中的方法输入参数类型和mapper.xml中statement的parameterType一致
 *    （4）、mapper.java接口中的方法返回值类型和mapper.xml中statement的resultType一致
 */
public interface UserMapper {
	public User findUserById(Long id);

	public List<User> findUserByName(String name);

	public void insertUser(User u);

	public void insertUser2(User u);

	public void updateUserById(User u);

	public void deleteUserById(Long id);
}

Demo.java

package com.chen.demo;

import java.io.IOException;
import java.io.Reader;

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 com.chen.mapper.UserMapper;
import com.chen.pojo.User;

public class Demo3_UserMapper {
	private static SqlSessionFactory sqlSessionFactory= null;
	static{
		String resources = "sqlMapConfig.xml";
		try {
			Reader reader = Resources.getResourceAsReader(resources);
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		test1();
	}

	private static void test1() {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User u = userMapper.findUserById(24L);
		System.out.println(u);
	}
}

示例四：动态sql
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="com.chen.mapper3.UserMapper">
	<!-- 定义sql片段
		经验：基于单表来定义sql片段，可重用性高
		在sql片段中不要包括where
	 -->
	<sql id="query_user">
		<if test="userCust != null">
			<if test="userCust.username !=null and userCust.username!=''">
				and username like #{userCust.username}
			</if>
			<if test="userCust.sex !=null and userCust.sex!=''">
				and sex=#{userCust.sex}
			</if>
		</if>
	</sql>


 	<select id="findUserList" parameterType="com.chen.pojo.UserQueryVO" resultType="_User">
 		select * from t_user  
 		<where>
 			<if test="userCust != null">
 				<if test="userCust.username !=null and userCust.username!=''">
 					and username like #{userCust.username}
 				</if>
 				<if test="userCust.sex !=null and userCust.sex!=''">
 					and sex=#{userCust.sex}
 				</if>
 			</if>
 		</where>

 	</select>

 	<select id="findUserCount" parameterType="com.chen.pojo.UserQueryVO" resultType="int">
 		select count(*) from t_user
 		<where>
 			<include refid="query_user"></include>
 		</where>
 	</select>

 	<select id="findUserByIds" parameterType="com.chen.pojo.UserQueryVO" resultType="_User">
 		select * from t_user
 		<where>
 			<include refid="query_user"></include>
 			<foreach collection="ids" item="id" open="and (" close=")" separator="or">
 				id =#{id}
 			</foreach>
 		</where>
 	</select>
</mapper>

UserMapper.java

package com.chen.mapper3;

import java.util.List;

import com.chen.pojo.User;
import com.chen.pojo.UserQueryVO;

public interface UserMapper {
	public List<User> findUserList(UserQueryVO vo);

	public int findUserCount(UserQueryVO vo);

	public List<User>findUserByIds(UserQueryVO vo);
}

Demo.java

package com.chen.demo;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
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 com.chen.mapper3.UserMapper;
import com.chen.pojo.User;
import com.chen.pojo.UserCustmer;
import com.chen.pojo.UserQueryVO;

public class Demo5_SQL {
	private static SqlSessionFactory sqlSessionFactory= null;
	static{
		String resources = "sqlMapConfig.xml";
		try {
			Reader reader = Resources.getResourceAsReader(resources);
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		test3();
	}

	private static void test1() {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		UserQueryVO vo = new UserQueryVO();
		UserCustmer userCust = new UserCustmer();
		userCust.setUsername("%C%");
		userCust.setSex("m");
		vo.setUserCust(userCust);
		List<User> list =userMapper.findUserList(vo);
		System.out.println(list);
	}

	private static void test2() {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		UserQueryVO vo = new UserQueryVO();
		UserCustmer userCust = new UserCustmer();
		userCust.setUsername("%C%");
		userCust.setSex("m");
		vo.setUserCust(userCust);
		int count =userMapper.findUserCount(vo);
		System.out.println(count);
	}

	private static void test3() {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		UserQueryVO vo = new UserQueryVO();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(10);
		ids.add(12);
		ids.add(15);
		UserCustmer userCust = new UserCustmer();
		userCust.setUsername("%C%");
		vo.setIds(ids);
		vo.setUserCust(userCust);
		List<User> list =userMapper.findUserByIds(vo);
		System.out.println(list);
	}
}