※別名
上個例子的Dept.xml裡面的resultType，有可能有很多支都回傳一樣的物件，所以可以用別名的方式來對應，據說2.x是在Dept.xml裡設裡，3.x方法是在mybatis-config.xml裡設定，集中起來感覺比較好找，可參考這裡如下：<properties resource="jdbc.properties"></properties> <typeAliases> <typeAlias type="org.mybatis.model.Dept" alias="Dept" /> <package name="org.mybatis.model" /> </typeAliases>
注意如果有「The content of element type "configuration" must match
"(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,plugins?,enviro
nments?,databaseIdProvider?,mappers?)".」這樣的錯，有可能是你沒有按照順序設定，如properties->settings->typeAliases…等
這時Dept.xml就只要寫resultType="Dept"就可以了(不一定要resultType才可以用)
也可以用註解，@Alias("xxx") 寫在 java bean class 的最上面，但要配合掃瞄包，也就是 package name，這別名可以在 xml 使用
都設定不會有問題，都可以用，如果只有掃瞄包，不寫 @Alias，預設是類名稱，開頭大小寫都可以
※增刪改查
Dept.xml如下，注意#{}裡面是對應getter方法的field而parameterType，為傳進去的參數類型； resultType為傳回的結果
雖然getDeptById，我寫int，但我傳Dept整個物件依然可以，不過為了好維護，大家都能看懂的方式，最好寫物件就傳物件，寫int就傳int
<?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="Dept">
<select id="getDeptById" parameterType="int" resultType="Dept">
SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO = #{deptNo}
</select>
<select id="findAll" resultType="Dept">
SELECT DEPTNO, DNAME, LOC FROM DEPT
</select>
<insert id="insert" parameterType="Dept">
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES(#{deptNo},#{dName},#{loc})
</insert>
<update id="update" parameterType="Dept">
UPDATE DEPT SET
DNAME = #{dName}
,LOC = #{loc}
WHERE
DEPTNO = #{deptNo}
</update>
<delete id="delete" parameterType="Dept">
DELETE FROM DEPT WHERE DEPTNO = #{deptNo}
</delete>
</mapper>
測試類：注意參數要對應Dept.xml的parameterType
InputStream is = null;
SqlSession sqlSession = null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder()
.build(is);
sqlSession = factory.openSession();
// 新增
// System.out.println("----------Dept.insert----------");
// Dept deptIns = new Dept();
// deptIns.setDeptNo(60);
// deptIns.setdName("Sales");
// deptIns.setLoc("zh_CN");
// int suc = sqlSession.insert("Dept.insert", deptIns);
// System.out.println("成功新增" + suc + "筆！");
// 修改
// System.out.println("----------Dept.update----------");
// Dept deptUpd = new Dept();
// deptUpd.setDeptNo(60);
// deptUpd.setdName("Shopping");
// deptUpd.setLoc("zh_CN");
// int upd = sqlSession.update("Dept.update", deptUpd);
// System.out.println("成功修改" + upd + "筆！");
// 刪除
// System.out.println("----------Dept.delete----------");
// Dept deptDel = new Dept();
// deptDel.setDeptNo(60);
// int del = sqlSession.delete("Dept.delete", deptDel);
// System.out.println("成功刪除" + del + "筆！");
// 查詢一筆
System.out.println("----------Dept.getDeptById----------");
//Dept d = new Dept();
//d.setDeptNo(90);
Dept dept = sqlSession.selectOne("Dept.getDeptById", 90);
System.out.println("deptNo=" + dept.getDeptNo());
System.out.println("dName=" + dept.getdName());
System.out.println("loc=" + dept.getLoc());
// 查詢全部
// System.out.println("----------Dept.findAll----------");
// List<Dept> dept = sqlSession.selectList("Dept.findAll");
// for (Dept deptAll : dept) {
// System.out.println("deptNo=" + deptAll.getDeptNo());
// System.out.println("dName=" + deptAll.getdName());
// System.out.println("loc=" + deptAll.getLoc());
// }
sqlSession.commit();
} catch (IOException e) {
e.printStackTrace();
} finally {
sqlSession.close();
try {
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
※xml裡的變數
Dept.xml裡的DEPTNO, DNAME, LOC欄位重覆好幾次，可以統一用一個類似變數的東西控管，如下：<sql id="column">
DEPTNO, DNAME, LOC
</sql>
<select id="getDeptById" parameterType="int" resultType="Dept">
SELECT
<include refid="column" />
FROM DEPT WHERE DEPTNO = #{deptNo}
</select>
<select id="findAll" resultType="Dept">
SELECT
<include refid="column" />
FROM DEPT
</select>
<insert id="insert" parameterType="Dept">
INSERT INTO DEPT(
<include refid="column" />
)
VALUES(#{deptNo},#{dName},#{loc})
</insert>
<update id="update" parameterType="Dept">
UPDATE DEPT SET
DNAME = #{dName}
,LOC = #{loc}
WHERE
DEPTNO = #{deptNo}
</update>
<delete id="delete" parameterType="Dept">
DELETE FROM DEPT WHERE DEPTNO
= #{deptNo}
</delete>
用<sql>標籤當變數，用<include refid="">可取得※MybatisUtil
和Hibernate一樣，每次都要呼叫SessionFactory太麻煩了，所以加上這個類，以下是高手提供的寫法，ThreadLocal(區域執行緒)，目的是把變數存在目前的執行緒中, 讓每個執行中的執行緒都有一份, 而且彼此之間不會互相影響package cn.mldn.util;
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;
public class MyBatisSessionFactory {
private static final String CONFIG = "mybatis-config.xml";
//ThreadLocal<SqlSession>讓SqlSession不會重覆被呼叫
private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sessionFactory;
private static Reader reader = null;
static {
try {
reader = Resources.getResourceAsReader(CONFIG);
sessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
private MyBatisSessionFactory() {
}
public static SqlSession getSession() {
SqlSession session = (SqlSession) threadLocal.get();
if (session == null) {
if (sessionFactory == null) {
rebuildSessionFactory();
}
session = (sessionFactory != null) ? sessionFactory.openSession()
: null;
threadLocal.set(session);
}
return session;
}
public static void rebuildSessionFactory() {
try {
reader = Resources.getResourceAsReader(CONFIG);
sessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void closeSession() {
SqlSession session = (SqlSession) threadLocal.get();
threadLocal.set(null);
if (session != null) {
session.close();
}
}
public static SqlSessionFactory getSessionFactory() {
return sessionFactory;
}
public static Reader getConfiguration() {
return reader;
}
}
這樣子呼叫就把sqlSession = factory.openSession();改成sqlSession = MybatisUtil.getSession();
然後try catch拿掉，最後MybatisUtil.closeSession();即可，寫一下好了：
//本來是這樣
InputStream is = null;
SqlSession sqlSession = null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder()
.build(is);
sqlSession = factory.openSession();
//CRUD...
sqlSession.commit();
} catch (IOException e) {
e.printStackTrace();
} finally {
sqlSession.close();
try {
if (is != null) {
is.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//改成以下模樣
SqlSession sqlSession = MybatisUtil.getSession();
//CRUD...
sqlSession.commit();
MybatisUtil.closeSession();
※傳變數給xml
Map<String, Object> map = new HashMap<String, Object>();
//key要對應Dept.xml
map.put("column", "dname");//欄位名稱
map.put("keyWord", "%a%");//給like尋找的關鍵字
map.put("start", 2);// 從第幾筆
map.put("end", 4);// 到第幾筆
SqlSession session = MybatisUtil.getSession();
System.out.println("AllCount=" + session.selectOne("Dept.getAllCount", map));
List<Dept> all = session.selectList("Dept.findAllBySplit", map);
for(Dept d:all){
System.out.print(d.getDeptNo() + "\t");
System.out.print(d.getdName() + "\t");
System.out.println(d.getLoc());
}
MybatisUtil.closeSession();
Demp.xml這樣設定，我用的是Oracle，所以就要用oracle的語法
<select id="findAllBySplit" parameterType="java.util.Map" resultType="Dept">
SELECT <include refid="column" />
FROM (
SELECT ROWNUM AS RANK, <include refid="column" /> FROM DEPT ORDER BY DEPTNO
)
WHERE
LOWER(${column}) LIKE #{keyWord} AND
RANK BETWEEN #{start} AND #{end}
</select>
<select id="getAllCount" parameterType="java.util.Map" resultType="java.lang.Integer">
SELECT COUNT(deptno)
FROM DEPT
WHERE LOWER(${column}) LIKE #{keyWord}
</select>
沒有留言:
張貼留言