【總結内容總結自 尚矽谷 資料】
發博備查。
1. mybatis 快速入門:
1). 添加 jar 包
mybatis-3.1.1.jar
mysql-connector-java-5.1.7-bin.jar
2). 建庫建表
create database mybatis;
use mybatis;
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);
INSERT INTO users(NAME, age) VALUES('Tom', 12);
INSERT INTO users(NAME, age) VALUES('Jack', 11);
3). 添加 Mybatis 的配置檔案 conf.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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
</configuration>
4). 定義表所對應的實體類
public class User {
private int id;
private String name;
private int age;
//get,set 方法
}
5). 定義操作 users 表的 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.irvin.mybatis_test.test1.userMapper">
<select id="getUser" parameterType="int"
resultType="com.irvin.mybatis_test.test1.User">
select * from users where id=#{id}
</select>
</mapper>
6). 在 conf.xml 檔案中注冊 userMapper.xml 檔案
<mappers>
<mapper resource="com/irvin/mybatis_test/test1/userMapper.xml"/>
</mappers>
7). 編寫測試代碼:執行定義的 select 語句
public class Test {
public static void main(String[] args) throws IOException {
String resource = "conf.xml";
//加載 mybatis 的配置檔案(它也加載關聯的映射檔案)
Reader reader = Resources.getResourceAsReader(resource);
//建構 sqlSession 的工廠
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//建立能執行映射檔案中 sql 的 sqlSession
SqlSession session = sessionFactory.openSession();
//映射 sql 的辨別字元串
String statement = "com.atguigu.mybatis.bean.userMapper"+".selectUser";
//執行查詢傳回一個唯一user 對象的sql
User user = session.selectOne(statement, 1);
System.out.println(user);
}
}
2. 一對一關聯:
<!--
方式一:嵌套結果:使用嵌套結果映射來處理重複的聯合結果的子集
封裝聯表查詢的資料(去除重複的資料)
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1
-->
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
方式二:嵌套查詢:通過執行另外一個SQL 映射語句來傳回預期的複雜類型
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一個查詢得到的teacher_id 的值
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
select * from class where c_id=#{id}
<resultMap type="_Classes" id="ClassResultMap2">
<association property="teacher" column="teacher_id" javaType="_Teacher"
select="getTeacher">
<select id="getTeacher" parameterType="int" resultType="_Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
association 用于一對一關聯查詢
property : 對象屬性的名稱
javaType ;對象屬性的類型
column : 所對應的外鍵字段名稱
select : 使用另一個查詢封裝的結果
3. 一對多關聯:
方式一: 嵌套結果: 使用嵌套結果映射來處理重複的聯合結果的子集
SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1
<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and c.c_id=#{id}
<resultMap type="_Classes" id="ClassResultMap3">
<!-- ofType 指定students 集合中的對象類型 -->
<collection property="students" ofType="_Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
SELECT * FROM student WHERE class_id=1 //1 是第一個查詢得到的c_id 字段的值
<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
<resultMap type="_Classes" id="ClassResultMap4">
<association property="teacher" column="teacher_id" javaType="_Teacher"
select="getTeacher2"></association>
<collection property="students" ofType="_Student" column="c_id" select="getStudent"></collection>
<select id="getTeacher2" parameterType="int" resultType="_Teacher">
<select id="getStudent" parameterType="int" resultType="_Student">
SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
<!--
collection : 做一對多關聯查詢
ofType : 指定集合中元素對象的類型
4. 動态 SQL 與模糊查詢
<?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.irvin.day03_mybatis.test6.userMapper">
<select id="getUser" parameterType="com.irvin.day03_mybatis.test6.ConditionUser"
resultType="com.irvin.day03_mybatis.test6.User">
select * from d_user where age>=#{minAge} and age<=#{maxAge}
<if test='name!="%null%"'>and name like #{name}</if>
</select>
</mapper>
MyBatis 中可用的動态SQL 标簽:
if
choose(when, otherwise)
trim(where, set)
foreach
5. 調用存儲過程
<mapper namespace="com.atguigu.day03_mybatis.test7.userMapper">
<select id="getCount" resultType="java.util.Map" statementType="CALLABLE">
{call
ges_user_count(#{sex_id,mode=IN,jdbcType=INTEGER},#{result,mode=OUT,jdbcType=INTEGER})
}
<select>
parameterMap : 引用 <parameterMap>
statementType : 指定 statement 的真是類型 : CALLABLE 執行調用存儲過程的語句
<parameterMap> : 定義多個參數的鍵值對
type : 需要傳遞的參數的真實類型 java.util.Map
<parameter> : 指定一個參數 key - value
6. 緩存機制 :
1). 一級緩存:
基于PerpetualCache 的 HashMap 本地緩存,其存儲作用域為 Session,當 Session flush 或 close 之後,該Session 中的所有 Cache 就将清空。
/*
* 一級緩存: 也就Session 級的緩存(預設開啟)
*/
@Test
public void testCache1() {
SqlSession session = MybatisUtils.getSession();
String statement = "com.atguigu.mybatis.test8.userMapper.getUser";
User user = session.selectOne(statement, 1);
System.out.println(user);
/*
* 一級緩存預設就會被使用
*/
user = session.selectOne(statement, 1);
System.out.println(user);
*/
1. 必須是同一個Session,如果session 對象已經close()過了就不可能用了
session = MybatisUtils.getSession();
2. 查詢條件是一樣的
user = session.selectOne(statement, 2);
3. 沒有執行過session.clearCache()清理緩存
session.clearCache();
4. 沒有執行過增删改的操作(這些操作都會清理緩存)
session.update("com.atguigu.mybatis.test8.userMapper.updateUser",
new User(2, "user", 23));
}
2). 二級緩存與一級緩存其機制相同,預設也是采用 PerpetualCache,HashMap 存儲,不同在于其存儲作用域為 Mapper(Namespace),并且可自定義存儲源,如 Ehcache。
3). 對于緩存資料更新機制,當某一個作用域(一級緩存Session/二級緩存Namespaces)的進行了C/U/D 操作後,預設該作用域下所有 select 中的緩存将被clear。
添加一個<cache>在userMapper.xml 中
<mapper namespace="com.atguigu.mybatis.test8.userMapper">
<cache/>
<!--
補充說明:
1. 映射語句檔案中的所有select 語句将會被緩存。
2. 映射語句檔案中的所有insert,update 和delete 語句會重新整理緩存。
3. 緩存會使用Least Recently Used(LRU,最近最少使用的)算法來收回。
4. 緩存會根據指定的時間間隔來重新整理。
5. 緩存會存儲1024 個對象
-->
<!--
<cache
eviction="FIFO" //回收政策為先進先出
flushInterval="60000" //自動重新整理時間60s
size="512" //最多緩存512 個引用對象
readOnly="true"/> //隻讀
7. spring 內建 mybatis
1). 添加Jar 包
[mybatis]
mybatis-3.2.0.jar
mybatis-spring-1.1.1.jar
log4j-1.2.17.jar
[spring]
spring-aop-3.2.0.RELEASE.jar
spring-beans-3.2.0.RELEASE.jar
spring-context-3.2.0.RELEASE.jar
spring-core-3.2.0.RELEASE.jar
spring-expression-3.2.0.RELEASE.jar
spring-jdbc-3.2.0.RELEASE.jar
spring-test-3.2.4.RELEASE.jar
spring-tx-3.2.0.RELEASE.jar
aopalliance-1.0.jar
cglib-nodep-2.2.3.jar
commons-logging-1.1.1.jar
[MYSQL 驅動包]
mysql-connector-java-5.0.4-bin.jar
2). 資料庫表
CREATE TABLE s_user(
user_id INT AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(30),
user_birthday DATE,
user_salary DOUBLE
)
3). 實體類: User
public class User {
private int id;
private String name;
private Date birthday;
private double salary;
//set,get 方法
4). DAO 接口: UserMapper (XXXMapper)
public interface UserMapper {
void save(User user);
void update(User user);
void delete(int id);
User findById(int id);
List<User> findAll();
}
5). 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.atguigu.mybatis.test9.UserMapper">
<resultMap type="User" id="userResult">
<result column="user_id" property="id"/>
<result column="user_name" property="name"/>
<result column="user_birthday" property="birthday"/>
<result column="user_salary" property="salary"/>
</resultMap>
<!-- 取得插入資料後的id -->
<insert id="save" keyColumn="user_id" keyProperty="id" useGeneratedKeys="true">
insert into s_user(user_name,user_birthday,user_salary)
values(#{name},#{birthday},#{salary})
</insert>
<update id="update">
update s_user
set user_name = #{name},
user_birthday = #{birthday},
user_salary = #{salary}
where user_id = #{id}
</update>
<delete id="delete">
delete from s_user
</delete>
<select id="findById" resultMap="userResult">
select * from s_user
</select>
<select id="findAll" resultMap="userResult">
select *
from s_user
</mapper>
6). spring 的配置檔案: beans.xml
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
<!-- 1. 資料源 : DriverManagerDataSource -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<!-- 2. mybatis 的SqlSession 的工廠: SqlSessionFactoryBean -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="typeAliasesPackage" value="com.irvin.spring_mybatis2.domain"/>
<!-- 3. mybatis 自動掃描加載Sql 映射檔案 : MapperScannerConfigurer -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.irvin.spring_mybatis2.mapper"/>
<property name="sqlSessionFactory" ref="sqlSessionFactory"/>
<!-- 4. 事務管理 : DataSourceTransactionManager -->
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- 5. 使用聲明式事務 -->
<tx:annotation-driven transaction-manager="txManager" />
</beans>
7). mybatis 的配置檔案: mybatis-config.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>
<!-- Spring 整合myBatis 後,這個配置檔案基本可以不要了-->
<!-- 設定外部配置檔案 -->
<!-- 設定類别名 -->
<!-- 設定資料庫連接配接環境 -->
<!-- 映射檔案 -->
</configuration>
8). 測試
@RunWith(SpringJUnit4ClassRunner.class) //使用Springtest架構
@ContextConfiguration("/beans.xml") //加載配置
public class SMTest {
@Autowired //注入
private UserMapper userMapper;
@Test
public void save() {
User user = new User();
user.setBirthday(new Date());
user.setName("marry");
user.setSalary(300);
userMapper.save(user);
System.out.println(user.getId());
}
public void update() {
User user = userMapper.findById(2);
user.setSalary(2000);
userMapper.update(user);
public void delete() {
userMapper.delete(3);
public void findById() {
User user = userMapper.findById(1);
System.out.println(user);