MyBatis是支持普通SQL查询,存储过程和高级映射的优秀持久层框架,其使用简单的xml或注解用于配置和原始映射,将接口和Java的POJO映射成数据库中的记录;
所用的jar以及xml:
链接:https://pan.baidu.com/s/11eoHPbvS8mNzkAgolFYqoQ
提取码:ze4i
命令行自动生成映射文件:java -jar mybatis1.jar -configfile generator.xml -overwrite
MyBatis持久化步骤:
- 持久化类PO和编写持久化操作的Mapper.xml(对应的Mapper接口),在其中定义要执行的SQL语句;
- 获取SqlSessionFactory;
- 获取SqlSession;
- 用面向对象的方式操作数据库;
- 关闭事务,关闭SqlSession;
SqlSessionFactory是单个数据库映射关系经过编译后的内存镜像,且线程安全;
sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));
SqlSession是执行持久化的操作的对象,类似于JDBC的Connection,是应用程序与持久存储层之间执行交互操作的一个单线程对象,其方法有:
语句执行方法: |
这些方法被用来执行定义在 SQL 映射的 XML 文件中的 SELECT,INSERT,UPDA E T 和 DELETE 语句。它们都会自行解释,每一句都使用语句的 ID 属性和参数对象,参数可以 是原生类型(自动装箱或包装类) ,JavaBean,POJO 或 Map。 namespace + id : 即:namespace 定位到唯一的mapper映射文件,id 定位到这个mapper映射文件的指定的sql语句: |
<T> T selectOne(String statement, Object parameter) | |
<E> List<E> selectList(String statement, Object parameter) | |
<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey) | |
int insert(String statement, Object parameter) | |
int update(String statement, Object parameter) | |
int delete(String statement, Object parameter) | |
<T> T selectOne(String statement) |
selectOne 和 selectList 的不同仅仅是 selectOne 必须返回一个对象。 如果多余一个, 或者 没有返回 (或返回了 null) 那么就会抛出异常。 , 如果你不知道需要多少对象, 使用 selectList。 如果你想检查一个对象是否存在,那么最好返回统计数(0 或 1) 。因为并不是所有语句都需 要参数,这些方法都是有不同重载版本的,它们可以不需要参数对象。 |
<E> List<E> selectList(String statement) | |
<K,V> Map<K,V> selectMap(String statement, String mapKey) | |
int insert(String statement) | |
int update(String statement) | |
int delete(String statement) | |
<E> List<E> selectList (String statement, Object parameter, RowBounds rowBounds) | 它们允许你限制返回行数的范围,或者提供自定 义结果控制逻辑,这通常用于大量的数据集合。 |
<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowbounds) | |
void select (String statement, Object parameter, ResultHandler handler) | |
void select (String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) | |
事务控制方法: |
控制事务范围有四个方法。如果是自动提交或正在使用外部事务管理器,则不需要这四个方法。若使用 JDBC 事务管理员,由 Connection 实例来控制,则需要这四个方法; 默认情况下 MyBatis 不会自动提交事务, 除非它侦测到有插入, 更新或删除操作改变了 数据库。如果你已经做出了一些改变而没有使用这些方法,那么你可以传递 true 到 commit 和 rollback 方法来保证它会被提交(注意,你不能在自动提交模式下强制 session,或者使用 了外部事务管理器时) 。很多时候你不用调用 rollback(),因为如果你没有调用 commit 时 MyBatis 会替你完成。然而,如果你需要更多对多提交和回滚都可能的 session 的细粒度控 制,你可以使用回滚选择来使它成为可能。 |
void commit() | |
void commit(boolean force) | |
void rollback() | |
void rollback(boolean force) | |
void clearCache() | 清理 Session 级的缓存 |
void close() | 确保 SqlSession 被关闭 |
<T> T getMapper(Class<T> type) | 使用映射器,返回Mapper接口的代理对象 |
Connection getConnection() | 获得JDBC的数据库连接对象 |
MyBatis初始化基本过程:
sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));
SqlSessionFactoryBuilder根据传入的数据流生成Configuration对象,然后根据Configuration对象创建默认的SqlSessionFactory实例;
简单的xml-CRUD:
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.answer.mapper.UserMapper">
<select id="selectUserById" parameterType="int" resultType="com.answer.model.User" resultMap="userResultMap">
select * from tb_user where id= #{id}
</select>
<insert id="insert" parameterType="com.answer.model.User">
insert into tb_user() values (#{id},#{username},#{loginname},
#{pword},#{phone},#{address})
</insert>
<delete id="delete" parameterType="int">
delete from tb_user where id=#{id}
</delete>
<update id="update" parameterType="com.answer.model.User">
update tb_user
set username =#{username},loginname=#{loginname},
pword=#{pword},phone=#{phone},address=#{address}
where id=#{id}
</update>
</mapper>
UserMapper接口:
public interface UserMapper {
User selectUserById(int id);
List<Order> selectOrderByUserId(int id);
int insert(User user);
int delete(int id);
int update(User user);
User selectUserByIdDy(int id);
}
测试:
private SqlSessionFactory sqlSessionFactory;
public UserImpl(){
sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));
}
@Test
public void query(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
User user=sqlSession.selectOne("selectUserById",1);
System.out.println(user.getOrders());
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
@Test
public void insert(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
User user=new User();
user.setId(2);
user.setUsername("刘");
user.setLoginname("liu");
user.setPword("123123");
user.setPhone("98474929");
user.setAddress("兰州");
sqlSession.selectOne("insert",user);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
@Test
public void del(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
sqlSession.selectOne("delete",2);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
@Test
public void update(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
User user=new User();
user.setId(2);
user.setUsername("刘");
user.setLoginname("liuyf");
user.setPword("123123000");
user.setPhone("9847492999");
user.setAddress("兰州安宁");
sqlSession.selectOne("update",user);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
xml关联映射:
一对一:Card和Person
<?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.answer.mapper.CardMapper">
<select id="selectCardById" parameterType="int" resultType="com.answer.model.Card">
select * from tb_card where id= #{id}
</select>
</mapper>
<mapper namespace="com.answer.mapper.PersonMapper">
<select id="selectPersonById" parameterType="int" resultMap="personMapper">
select * from tb_person where id= #{id}
</select>
<resultMap id="personMapper" type="com.answer.model.Person">
<id property="id" column="id"/>
<result property="name" column="name"></result>
<result property="sex" column="sex"></result>
<result property="age" column="age"></result>
<!-- 一对一关联映射-->
<association property="card" column="card_id"
select="com.answer.mapper.CardMapper.selectCardById"
javaType="com.answer.model.Card">
</association>
</resultMap>
</mapper>
测试:
private SqlSessionFactory sqlSessionFactory;
public PersonImpl(){
sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));
}
@Test
public void query(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
/*Card card=sqlSession.selectOne("selectCardById",1);
System.out.println(card.getCard());*/
Person person=sqlSession.selectOne("selectPersonById",1);
System.out.println(person.getName()+":"+person.getCard().getCard());
/*PersonMapper personMapper=sqlSession.getMapper(PersonMapper.class);
Person person=personMapper.selectPersonById(1);*/
//System.out.println(person.getName()+":"+person.getCard().getCard());
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
一对多:Class和Student
<mapper namespace="com.answer.mapper.ClassMapper">
<select id="selectClassById" parameterType="int" resultMap="classResultMapper">
select * from tb_class where id= #{id}
</select>
<resultMap id="classResultMapper" type="com.answer.model.Class">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
<!-- 一对多关联映射-->
<collection property="student" javaType="ArrayList"
column="id" ofType="com.answer.model.Student"
select="com.answer.mapper.StudentMapper.selectStudentByClassId"
fetchType="lazy">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
</collection>
</resultMap>
</mapper>
<mapper namespace="com.answer.mapper.StudentMapper">
<select id="selectStudentById" parameterType="int" resultMap="studentResultMapper">
SELECT * FROM tb_clazz c,tb_student s
WHERE c.id = s.clazz_id
AND s.id = #{id}
</select>
<select id="selectStudentByClassId" parameterType="int" resultMap="studentResultMapper">
select * from tb_student where class_id= #{id}
</select>
<resultMap id="studentResultMapper" type="com.answer.model.Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<!-- 多对一关联映射-->
<association property="aClass" column="class_id"
select="com.answer.mapper.ClassMapper.selectClassById"
javaType="com.answer.model.Class">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
</association>
</resultMap>
</mapper>
测试:
private SqlSessionFactory sqlSessionFactory;
public ClassImpl(){
sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));
}
@Test
public void query(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
Class c=sqlSession.selectOne("selectClassById",1);
System.out.println(c.getName());
List<Student> list=c.getStudent();
list.forEach(student -> System.out.println(student.getName()+":"+student.getaClass().getName()));
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
多对多:Order和Article(需建立中间表item)
<mapper namespace="com.answer.mapper.OrderMapper">
<resultMap id="orderResultMap" type="com.answer.model.Order">
<id property="id" column="oid"/>
<result property="code" column="code"/>
<result property="total" column="total"/>
<association property="user" column="user_id"
select="com.answer.mapper.UserMapper.selectUserById"
javaType="com.answer.model.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="loginname" column="loginname"/>
<result property="pword" column="pword"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
</association>
<collection property="articles" javaType="ArrayList"
column="id" ofType="com.answer.model.Article"
select="com.answer.mapper.ArticleMapper.selectArticleByOrderId"
fetchType="lazy">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</collection>
</resultMap>
<!-- 注意,如果查询出来的列同名,例如tb_user表的id和tb_order表的id都是id,同名,需要使用别名区分 -->
<select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
SELECT u.*,o.id AS oid,CODE,total,user_id
FROM tb_user u,tb_order o
WHERE u.id = o.user_id
AND o.id = #{id}
</select>
<!-- 根据userid查询订单 -->
<select id="selectOrderByUserId" parameterType="int" resultType="com.answer.model.Order">
SELECT * FROM tb_order WHERE user_id = #{id}
</select>
</mapper>
<mapper namespace="com.answer.mapper.ArticleMapper">
<select id="selectArticleByOrderId" parameterType="int" resultMap="articleResultMapper">
SELECT * FROM tb_article WHERE id IN (
SELECT article_id FROM tb_item WHERE order_id = #{id}
)
</select>
<select id="selectArticleById" parameterType="int" resultMap="articleResultMapper">
select * from tb_article where id= #{id}
</select>
<resultMap id="articleResultMapper" type="com.answer.model.Article">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</resultMap>
</mapper>
测试:
private SqlSessionFactory sqlSessionFactory;
public OrderImpl(){
sqlSessionFactory=new SqlSessionFactoryBuilder().build(PersonImpl.class.getClassLoader().getResourceAsStream("mybatis.cfg.xml"));
}
@Test
public void query(){
SqlSession sqlSession=sqlSessionFactory.openSession();
try{
/*List<Order> orders=sqlSession.selectList("selectOrderByUserId",1);
orders.forEach(order -> System.out.println(order.getArticles()));
for(Order order:orders){
System.out.println(order.getArticles());
}*/
Order order=sqlSession.selectOne("selectOrderById",1);
System.out.println(order.getUser().getUsername());
List<Article> articles=order.getArticles();
articles.forEach(article -> System.out.println(article.getName()));
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
简单的注解-CRUD:
UserMapper接口:
@Select("select * from tb_user where id=#{id}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property ="username",column = "username"),
@Result(property ="loginname",column = "loginname"),
@Result(property ="pword",column = "pword"),
@Result(property ="phone",column = "phone"),
@Result(property ="address",column = "address"),
})
User selectUserById(int id);
@Insert("insert into tb_user() values(#{id},#{username},#{loginname},#{pword},#{phone},#{address})")
int insert(User user);
@Delete("delete from tb_user where id=#{id}")
int delete(int id);
@Update("update tb_user set username =#{username},loginname=#{loginname}," +
" pword=#{pword},phone=#{phone},address=#{address}" +
" where id=#{id}")
int update(User user);
测试同xml;
一对一:Card和Person
CardMapper接口:
@Select("select * from tb_card where id=#{id}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "card",column = "card"),
})
Card selectCardById(int id);
PersonMapper接口:
@Select("select * from tb_person where id=#{id} ")
@Results({
@Result(id=true,property = "id",column = "id"),
@Result(property = "name",column = "name"),
@Result(property = "sex",column = "sex"),
@Result(property = "age",column = "age"),
@Result(property = "card",column = "card_id",
one = @One(select = "com.answer.mapper.CardMapper.selectCardById"))
})
Person selectPersonById(int id);
一对多:Class和Student
public interface ClassMapper {
@Select("select * from tb_class where id=#{id}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property ="code",column = "code"),
@Result(property ="name",column = "name"),
@Result(property = "student",column = "id",
many = @Many(select = "com.answer.mapper.StudentMapper.selectStudentByClassId"))
})
Class selectClassById(int id);
}
public interface StudentMapper {
@Select("select * from tb_student where id=#{id}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property ="name",column = "name"),
@Result(property ="sex",column = "sex"),
@Result(property ="age",column = "age"),
@Result(property = "aClass",column = "class_id",
one = @One(select = "com.answer.mapper.ClassMapper.selectClassById"))
})
Student selectStudentById(int id);
@Select("select * from tb_student where class_id=#{id}")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property ="name",column = "name"),
@Result(property ="sex",column = "sex"),
@Result(property ="age",column = "age")
})
List<Student> selectStudentByClassId(int id);
}
多对多:Order和Article(需建立中间表item)
public interface OrderMapper {
@Select("select * from tb_order where id=#{id}")
@Results({
@Result(id=true,property = "id",column = "id"),
@Result(property = "code",column = "code"),
@Result(property = "total",column = "total"),
@Result(property = "articles",column = "id",
many = @Many(select = "com.answer.mapper.ArticleMapper.selectArticleByOrderId"))
})
Order selectOrderById(int id);
}
public interface ArticleMapper {
@Select("select * from tb_article where id in (select article_id from tb_item where order_id=#{id})")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "name",column = "name"),
@Result(property = "price",column = "price"),
@Result(property = "remark",column = "remark")
})
List<Article> selectArticleByOrderId(int id);
}