Mybatis逐步深入

（一）

1.单表查询

<?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> <environments default=""> <environment id=""> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/user"/> <property name="username" value="root"/> <property name="password" value="1"/> </dataSource> </environment> </environments> <mappers><mapper resource="com/web/mybatis/userSql.xml"/></mappers> </configuration> <?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="environment"> <resultMap type="com.web.entity.User" id="userMap"> <id column="uid" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="name" property="name" javaType="string" jdbcType="VARCHAR"/> <result column="password" property="password" javaType="string" jdbcType="VARCHAR"/> <result column="gender" property="gender" javaType="string" jdbcType="VARCHAR"/> <result column="interest" property="interest" javaType="string" jdbcType="VARCHAR"/> <result column="address" property="address" javaType="string" jdbcType="VARCHAR"/> <result column="area" property="area" javaType="string" jdbcType="VARCHAR"/> <result column="udid" property="udid" javaType="Integer" jdbcType="INTEGER"/> </resultMap> <select id="SelectUser" resultMap="userMap"> select * from user </select> </mapper> package com.web.mybatis; 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; public class MysqlMyabitsSessionFactory { private static SqlSessionFactory sqlSession = null; private static MysqlMyabitsSessionFactory mySession = null; public MysqlMyabitsSessionFactory(){ String resource = "com/web/mybatis/mysql-config.xml"; try { Reader read = Resources.getResourceAsReader(resource); sqlSession = new SqlSessionFactoryBuilder().build(read); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static MysqlMyabitsSessionFactory getInstance(){ if(mySession == null){ return new MysqlMyabitsSessionFactory(); } return mySession; } public static SqlSessionFactory getSqlSessionFactory(){ return sqlSession; } } package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.User; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } } public List<User> SelectUser(){ List<User> list = this.sqlSession.selectList("SelectUser"); return list; } public static void main(String[] args) { UserModel um = new UserModel(); List<User> list = um.SelectUser(); for(User user : list){ System.out.println(user.getName()+"-"+user.getGender()); } } }

2.多表查询

<?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="environment"> <resultMap type="com.web.entity.User" id="userMap"> <id column="uid" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="name" property="name" javaType="string" jdbcType="VARCHAR"/> <result column="password" property="password" javaType="string" jdbcType="VARCHAR"/> <result column="gender" property="gender" javaType="string" jdbcType="VARCHAR"/> <result column="interest" property="interest" javaType="string" jdbcType="VARCHAR"/> <result column="address" property="address" javaType="string" jdbcType="VARCHAR"/> <result column="area" property="area" javaType="string" jdbcType="VARCHAR"/> <result column="udid" property="udid" javaType="Integer" jdbcType="INTEGER"/> <association property="dept" javaType="com.web.entity.Dept" resultMap="deptMap"></association> </resultMap> <resultMap type="com.web.entity.Dept" id="deptMap" > <id column="id" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="d_name" property="name" javaType="string" jdbcType="VARCHAR"/> </resultMap> <select id="SelectUser" resultMap="userMap" parameterType="com.web.entity.User"> select u.*, d.* from user u , dept d where u.udid = d.id and u.name like #{name}; </select> </mapper> package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.User; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } } User user = new User(); public List<User> SelectUser(){ user.setName("%3%"); List<User> list = this.sqlSession.selectList("SelectUser",user); return list; } public static void main(String[] args) { UserModel um = new UserModel(); List<User> list = um.SelectUser(); for(User user : list){ System.out.println(user.getName()+"-"+user.getGender()+"-"+user.getDept().getName()); } } }

（二）Mybatis面向接口的编程

1.多对一查询 新建一个com.web.mapper的包并建一个接口

package com.web.mapper; import java.util.List; import com.web.entity.User; public interface IUserMappler { public List<User> SearchUser(User user); } <?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.IUserMappler"> <resultMap type="com.web.entity.User" id="userMap"> <id column="uid" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="name" property="name" javaType="string" jdbcType="VARCHAR"/> <result column="password" property="password" javaType="string" jdbcType="VARCHAR"/> <result column="gender" property="gender" javaType="string" jdbcType="VARCHAR"/> <result column="interest" property="interest" javaType="string" jdbcType="VARCHAR"/> <result column="address" property="address" javaType="string" jdbcType="VARCHAR"/> <result column="area" property="area" javaType="string" jdbcType="VARCHAR"/> <result column="udid" property="udid" javaType="Integer" jdbcType="INTEGER"/> <association property="dept" javaType="com.web.entity.Dept" resultMap="deptMap"></association> </resultMap> <resultMap type="com.web.entity.Dept" id="deptMap" > <id column="id" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="d_name" property="name" javaType="string" jdbcType="VARCHAR"/> </resultMap> <select id="SearchUser" resultMap="userMap" parameterType="com.web.entity.User"> select u.*, d.* from user u , dept d where u.udid = d.id and u.name like #{name}; </select> </mapper> package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.User; import com.web.mapper.IUserMappler; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; IUserMappler userMapper = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } if(userMapper == null){ userMapper = sqlSession.getMapper(IUserMappler.class); } } User user = new User(); public List<User> SelectUser(){ user.setName("%1%"); // IUserMappler userMapper = sqlSession.getMapper(IUserMappler.class); List<User> list = userMapper.SearchUser(user); return list; } public static void main(String[] args) { UserModel um = new UserModel(); List<User> list = um.SelectUser(); for(User user : list){ System.out.println(user.getName()+"-"+user.getGender()+"-"+user.getDept().getName()); } } }

2.一对多查询

package com.web.mapper; import com.web.entity.Dept; public interface IUserMappler { public Dept SearchDeptById(int id); } <?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.IUserMappler"> <resultMap type="com.web.entity.User" id="userMap"> <id column="uid" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="name" property="name" javaType="string" jdbcType="VARCHAR"/> <result column="password" property="password" javaType="string" jdbcType="VARCHAR"/> <result column="gender" property="gender" javaType="string" jdbcType="VARCHAR"/> <result column="interest" property="interest" javaType="string" jdbcType="VARCHAR"/> <result column="address" property="address" javaType="string" jdbcType="VARCHAR"/> <result column="area" property="area" javaType="string" jdbcType="VARCHAR"/> <result column="udid" property="udid" javaType="Integer" jdbcType="INTEGER"/> <!-- <association property="dept" javaType="com.web.entity.Dept" resultMap="deptMap"></association> --> </resultMap> <resultMap type="com.web.entity.Dept" id="deptMap" > <id column="id" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="d_name" property="name" javaType="string" jdbcType="VARCHAR"/> <collection property="listUser" javaType="java.util.List" ofType="com.web.entity.User" resultMap="userMap"></collection> <!-- 或<collection property="listUser" javaType="java.util.List" ofType="com.web.entity.User" > <id column="uid" property="id" javaType="Integer" jdbcType="INTEGER"/> <result column="name" property="name" javaType="string" jdbcType="VARCHAR"/> <result column="password" property="password" javaType="string" jdbcType="VARCHAR"/> <result column="gender" property="gender" javaType="string" jdbcType="VARCHAR"/> <result column="interest" property="interest" javaType="string" jdbcType="VARCHAR"/> <result column="address" property="address" javaType="string" jdbcType="VARCHAR"/> <result column="area" property="area" javaType="string" jdbcType="VARCHAR"/> <result column="udid" property="udid" javaType="Integer" jdbcType="INTEGER"/> </collection> --> </resultMap> <!-- <select id="SearchUser" resultMap="userMap" parameterType="com.web.entity.User"> select u.*, d.* from user u , dept d where u.udid = d.id and u.name like #{name}; </select> --> <select id="SearchDeptById" resultMap="deptMap" parameterType="com.web.entity.Dept"> select u.*, d.* from user u , dept d where u.udid = d.id and d.id = #{0}; </select> </mapper> package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.Dept; import com.web.entity.User; import com.web.mapper.IUserMappler; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; IUserMappler userMapper = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } if(userMapper == null){ userMapper = sqlSession.getMapper(IUserMappler.class); } } Dept dept = new Dept(); public Dept SearchDeptById(){ Dept dept = userMapper.SearchDeptById(1); return dept; } public static void main(String[] args) { UserModel um = new UserModel(); Dept dept = um.SearchDeptById(); System.out.println(dept.getName()); for (User user : dept.getListUser()) { System.out.println(user.getName()+"-"+user.getGender()); } } }

（三）Mybatis注解开发，userSql被架空

1.

<?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> <environments default=""> <environment id=""> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/user"/> <property name="username" value="root"/> <property name="password" value="1"/> </dataSource> </environment> </environments> <mappers> <!-- <mapper resource="com/web/mybatis/userSql.xml"/> --> <mapper class="com.web.mapper.IDeptMappler"/> </mappers> </configuration> 建立一个新的接口 package com.web.mapper; import java.util.List; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.web.entity.User; public interface IDeptMappler { @Select("select u.*, d.* from user u , dept d where u.udid = d.id and d.d_name like #{dept.name}") @Results({@Result(id=true,column="uid",property="id"), @Result(column="name",property="name"), @Result(column="password",property="password"), @Result(column="gender",property="gender"), @Result(column="interest",property="interest"), @Result(column="address",property="address"), @Result(column="area",property="area"), @Result(column="udid",property="udid"), @Result(column="id",property="dept.id"), @Result(column="d_name",property="dept.name")}) public List<User> SearchUser(User user); } package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.Dept; import com.web.entity.User; import com.web.mapper.IDeptMappler; import com.web.mapper.IUserMappler; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; IDeptMappler deptMapper = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } if(deptMapper == null){ deptMapper = sqlSession.getMapper(IDeptMappler.class); } } User user = new User(); public List<User> SearchUser(){ Dept dept = new Dept(); dept.setName("市场部"); user.setDept(dept); List<User> list = deptMapper.SearchUser(user); return list; } public static void main(String[] args) { UserModel um = new UserModel(); List<User> list = um.SearchUser(); for (User user : list) { System.out.println(user.getName()+"-"+user.getDept().getName()); } } }

2.如果Mybatis视User，Dept为两个对象，那么就需要一对多，or多对一配置，而且sql是查询一个表

1). 多对一查询 package com.web.mapper; import java.util.List; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.web.entity.Dept; import com.web.entity.User; public interface IDeptMappler { @Select("select * from user where name like #{name}") @Results({@Result(id=true,column="uid",property="id"), @Result(column="name",property="name"), @Result(column="password",property="password"), @Result(column="gender",property="gender"), @Result(column="interest",property="interest"), @Result(column="address",property="address"), @Result(column="area",property="area"), @Result(column="udid",property="dept",one=@One(select="com.web.mapper.IDeptMappler.SearchDeptById"))}) public List<User> SearchUser(User user); @Select("select * from dept where id = #{0}") @Results({@Result(id=true,column="id",property="id"), @Result(column="d_name",property="name")}) public Dept SearchDeptById(int id); } package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.Dept; import com.web.entity.User; import com.web.mapper.IDeptMappler; import com.web.mapper.IUserMappler; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; IDeptMappler deptMapper = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } if(deptMapper == null){ deptMapper = sqlSession.getMapper(IDeptMappler.class); } } User user = new User(); public List<User> SearchUser(){ user.setName("%1%"); List<User> list = deptMapper.SearchUser(user); return list; } public static void main(String[] args) { UserModel um = new UserModel(); List<User> list = um.SearchUser(); for (User user : list) { System.out.println(user.getName()+"-"+user.getDept().getName()); } } } 2).一对多查询 package com.web.mapper; import java.util.List; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.web.entity.Dept; import com.web.entity.User; public interface IDeptMappler { @Select("select * from dept where id = #{0}") @Results({@Result(id=true,column="id",property="id"), @Result(column="d_name",property="name"), @Result(column="id",property="listUser",many=@Many(select="com.web.mapper.IDeptMappler.SearchUser"))}) public Dept SearchDeptById(int id); @Select("select * from user where udid = #{0}") @Results({@Result(id=true,column="uid",property="id"), @Result(column="name",property="name"), @Result(column="password",property="password"), @Result(column="gender",property="gender"), @Result(column="interest",property="interest"), @Result(column="address",property="address"), @Result(column="area",property="area"), @Result(column="udid",property="udid")}) public List<User> SearchUser(int udid); } package com.web.control; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.web.entity.Dept; import com.web.entity.User; import com.web.mapper.IDeptMappler; import com.web.mapper.IUserMappler; import com.web.mybatis.MysqlMyabitsSessionFactory; public class UserModel { private SqlSession sqlSession = null; IDeptMappler deptMapper = null; public UserModel(){ if(sqlSession == null){ sqlSession = MysqlMyabitsSessionFactory.getInstance().getSqlSessionFactory().openSession(); } if(deptMapper == null){ deptMapper = sqlSession.getMapper(IDeptMappler.class); } } public Dept SearchDeptById(){ Dept dept = deptMapper.SearchDeptById(3); return dept; } public static void main(String[] args) { UserModel um = new UserModel(); Dept dept = um.SearchDeptById(); System.out.println(dept.getName()); for (User user : dept.getListUser()) { System.out.println(user.getName()+"-"+user.getGender()); } } }