1、Mybatis的優勢
輕量級、半自動化、開源
2、最佳執行個體
- 1、添加Mybatis坐标
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.encoding>UTF-8</maven.compiler.encoding>
<java.version>1.8</java.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<!--mybatisࣖ坐标-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!--mysql驅動坐标-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<!--單元測試坐标-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
- 2、建立user資料表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
- 3、編寫User實體類
public class User {
private Integer id;
private String username;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
'}';
}
}
- 4、編寫映射檔案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.hdl.dao.UserDao">
<!--
namespace:命名空間 與id組成sql唯一标志
resultType: 表明傳回值類型
-->
<!--抽取sql片段-->
<sql id="selectUser">
select * from user
</sql>
<!--查詢所有使用者-->
<select id="findAll" resultType="uSeR">
<include refid="selectUser"/>
</select>
</mapper>
- 5、編寫核心配置檔案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="jdbc.properties"></properties>
<!--給實體的全限定類名給别名-->
<typeAliases>
<!--給單獨的實體起别名-->
<!--<typeAlias type="com.hdl.pojo.User" alias="User"/>-->
<!--批量起别名:該包下所有的類的本身的類名,别名不區分大小寫-->
<package name="com.hdl.pojo"/>
</typeAliases>
<!--運作環境-->
<environments default="development">
<environment id="development">
<!--目前事務交由jdbc管理-->
<transactionManager type="JDBC"/>
<!--表示使用mybatis連接配接池-->
<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>
<!--mapper映射-->
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
- 6、編寫測試類
@Test
public void test1() throws IOException {
//1.Resources工具類,配置檔案加載,吧配置檔案加載成位元組輸入流
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//2.解析配置檔案,并建立SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//3.生産SqlSession,預設開啟一個事務,該事務不會自動送出;在進行增删改時,要手動送出事務
SqlSession sqlSession = sqlSessionFactory.openSession();
List<User> list = sqlSession.selectList("user.findAll");
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}
3、Mybatis的CRUD操作
- 注解方式
/**
* 查詢所有使用者
*/
@Select("select * from user")
List<User> findAll();
/**
* 新增使用者
*/
@Insert("insert into user(id, username, password, birthday) values(#{id}, #{username}, #{password}, #{birthday})")
void insertUser(User user);
/**
* 修改使用者
*/
@Update("update user set username = #{username} where id = #{id}")
void updateUser(User user);
/**
* 删除使用者
*/
@Delete("delete from user where id = #{id}")
void deleteUser(Integer id);
- XML方式
跟注解裡面的sql一樣,改成UserMapper.xml方式即可
4、一對一查詢
/**
* 查詢訂單的同時還查詢該訂單所屬使用者
*/
@Results({
@Result(property = "id", column = "id"),
@Result(property = "orderTime", column = "orderTime"),
@Result(property = "total", column = "total"),
@Result(property = "user", column = "uid", javaType = User.class,
one = @One(select = "com.hdl.mapper.IUserMapper.findUserById"))
})
@Select("select * from orders")
List<Order> findOrderAndUser();
/**
* 根據id查詢使用者資訊
*/
@Select("select * from user where id = #{id}")
User findUserById(Integer id);
<!--resultMap: 手動配置實體屬性與表字段的映射關系-->
<resultMap id="orderMap" type="Order" >
<id property="id" column="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result property="orderTime" column="orderTime" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result property="total" column="total" javaType="java.lang.Double" jdbcType="DOUBLE"/>
<!--關聯查詢-->
<association property="user" javaType="User">
<id property="id" column="uid" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result property="username" column="username" javaType="java.lang.String" jdbcType="VARCHAR"/>
</association>
</resultMap>
5、一對多/多對多查詢
/**
* 根據使用者查詢使用者關聯的訂單(一對多查詢)
*/
@Results({
@Result(property = "id", column = "id"),
@Result(property = "username", column = "username"),
@Result(property = "password", column = "password"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "orders", column = "id",
many = @Many(select = "com.hdl.mapper.IOrderMapper.findOrderByUserId")),
})
@Select("select * from user where id = #{id}")
User findUserAndOrder(Integer id);
/**
* 根據使用者查詢角色,多對多查詢
*/
/**
* 根據使用者查詢使用者關聯的訂單(一對多查詢)
*/
@Results({
@Result(property = "id", column = "id"),
@Result(property = "username", column = "username"),
@Result(property = "password", column = "password"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "orders", column = "id",
many = @Many(select = "com.hdl.mapper.IOrderMapper.findOrderByUserId")),
@Result(property = "roles", column = "id",
many = @Many(select = "com.hdl.mapper.IRoleMapper.findRoleByUserId")),
})
@Select("select * from user where id = #{id}")
User findUserAndRole(Integer id);
=================================
@Select("select * from sys_role where id in (select roleid from sys_user_role where userid = #{#userId})")
List<Role> findRoleByUserId(Integer userId);
=================================
@Select("select * from orders where uid = #{userId}")
List<Order> findOrderByUserId(Integer userId);
<!--resultMap: 手動配置實體屬性與表字段的映射關系-->
<resultMap id="userMap" type="User" >
<id property="id" column="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result property="username" column="username" javaType="java.lang.String" jdbcType="VARCHAR"/>
<!--關聯查詢-->
<collection property="orders" ofType="com.hdl.pojo.Order">
<id property="id" column="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result property="orderTime" column="orderTime" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result property="total" column="total" javaType="java.lang.Double" jdbcType="DOUBLE"/>
</collection>
</resultMap>
<!--resultMap: 手動配置實體屬性與表字段的映射關系-->
<resultMap id="userRoleMap" type="User" >
<id property="id" column="id" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result property="username" column="username" javaType="java.lang.String" jdbcType="VARCHAR"/>
<!--關聯查詢-->
<collection property="roles" ofType="com.hdl.pojo.Role">
<id property="id" column="roleid" javaType="java.lang.Integer" jdbcType="INTEGER"/>
<result property="rolename" column="rolename" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result property="roleDesc" column="roleDesc" javaType="java.lang.String" jdbcType="VARCHAR"/>
</collection>
</resultMap>
6、Mybatis傳統Mapper和代理Mapper
- 傳統方式
傳統方式SqlSession直接使用statementId和參數查詢
- 代理方式
mapper代理使用JDK動态代理生成代理類查詢(主流)
7、Mybatis加載外部properties
在sqlMapConfig.xml中,可以配置外部properties加載
<!--加載外部properties檔案-->
<properties resource="jdbc.properties"></properties>
8、Mybatis配置别名typeAlias
在sqlMapConfig.xml中,可以配置實體類的别名
單個實體類配置
<!--給實體的全限定類名給别名-->
<typeAliases>
<!--給單獨的實體起别名-->
<typeAlias type="com.hdl.pojo.User" alias="User"/>
</typeAliases>
某包下批量取别名
<!--給實體的全限定類名給别名-->
<typeAliases>
<!--批量起别名:該包下所有的類的本身的類名,别名不區分大小寫-->
<package name="com.hdl.pojo"/>
</typeAliases>
9、Mybatis的Mapper映射方式
- 1、使用resource指定XML配置檔案
- 2、使用class指定某個類
- 3、使用package指定某個包, 配置檔案和mapper接口需要同包同名