Mybatis 的 Annotation Mybatis3.x(九)

※事前規劃

CREATE TABLE CHESS_TABLE(
    CID  NUMBER,
    NAME  VARCHAR(20),
    PRODUCT_DATE DATE,
    ATTENTION VARCHAR(20),
    CONSTRAINT CHESS_TABLE_PK PRIMARY KEY(CID)
);
COMMENT ON TABLE CHESS_TABLE IS '棋表';
COMMENT ON COLUMN CHESS_TABLE.CID IS '棋的ID';
COMMENT ON COLUMN CHESS_TABLE.NAME IS '棋的名稱';
COMMENT ON COLUMN CHESS_TABLE.PRODUCT_DATE IS '製造日期';
COMMENT ON COLUMN CHESS_TABLE.ATTENTION IS '注意事項';
CREATE SEQUENCE CHESS_TABLE_SEQUENCE;

※介面

．java bean是一定要的，沒什麼特別就不show了
．以下是介面，以往都要有DaoImpl的實作介面的東西，但因為有Annotation，所以不用了
．注意有底線的欄位，還是要用別名，不然會查到null

public interface IChessTableDAO {
    @Insert("INSERT INTO CHESS_TABLE(CID, NAME, PRODUCT_DATE, ATTENTION) VALUES (#{cid}, #{name}, #{productDate}, #{attention})")
    @SelectKey(before = true, keyProperty = "cid", resultType = java.lang.Integer.class, statement = "SELECT CHESS_TABLE_SEQUENCE.NEXTVAL FROM DUAL")
    public boolean insert(ChessTable chessTable);

    @Delete("DELETE FROM CHESS_TABLE WHERE CID = #{xxx}")
    public boolean delete(Integer cid);

    @Update("UPDATE CHESS_TABLE SET NAME = #{name}, PRODUCT_DATE = #{productDate}, ATTENTION = #{attention} WHERE CID = #{cid}")
    public boolean update(ChessTable chessTable);

    @Select("SELECT CID, NAME, PRODUCT_DATE PRODUCTDATE, ATTENTION FROM CHESS_TABLE WHERE CID = #{xxx}")
    public ChessTable findById(Integer cid);

    @Select("SELECT CID, NAME, PRODUCT_DATE PRODUCTDATE, ATTENTION FROM CHESS_TABLE")
    public List<ChessTable> findAll();

    @Select("SELECT COUNT(CID) FROM CHESS_TABLE WHERE ${ooo} LIKE #{xxx}")
    public int count(@Param("ooo") String column, @Param("xxx") String keyWord);
}

※測試類

#{xxx}和${xxx}就差在，有#的會幫我們轉換型態，譬如傳String，它會幫我們轉成「'xxx'」，而$就不會，可以利用這點debug

SqlSession sqlSession = MybatisUtil.getSession();
ChessTable ct = new ChessTable();
ct.setName("孔明棋");
ct.setProductDate(new Date());
ct.setAttention("不可食用！");

IChessTableDAO dao = sqlSession.getMapper(IChessTableDAO.class);

// 新增
// System.out.println(dao.insert(ct));
// System.out.println(ct.getCid());

// 修改
// ct.setName("黑白棋");
// ct.setCid(2);
// System.out.println(dao.update(ct));

// 查一筆
// ChessTable chessTable = dao.findById(2);
// System.out.println(chessTable.getName());
// System.out.println(chessTable.getProductDate());
// System.out.println(chessTable.getAttention());

// 查全部
// List<ChessTable> lct = dao.findAll();
// for(ChessTable l:lct){
// System.out.println(l.getCid());
// System.out.println(l.getName());
// System.out.println(l.getProductDate());
// System.out.println(l.getAttention());
// System.out.println("----------");
// }

// 查關鍵字
// System.out.println(dao.count("NAME", "%白%"));

// 刪除
System.out.println(dao.delete(2));

sqlSession.commit();
MybatisUtil.closeSession();

※Annotaion 加動態標籤

@Select({
	"<script>",
		"SELECT * FROM table where xxx in",
		"<foreach collection='ooo' item='id' open='(' separator=',' close=')'>",
		"#{id}",
		"</foreach>",
	"</script>"
})
List<String> getXxx(@Param("ooo") List<Long> ooo);

※Mapper註冊

執行到getMapper那一行會出「Type xxx is not known to the MapperRegistry.」的錯，之前是用xml設定，會去設定檔抓<mappers>裡的設定，所以這時要設定，有四種方式

以下是Util的設定兩者取其一，看是針對package或其中一隻設定，Util全貌可看Mybatis3.x(二)

reader = Resources.getResourceAsReader(CONFIG_FILE_LOCATION);
sessionFactory = new SqlSessionFactoryBuilder().build(reader);
//這是在static區塊裡，上兩行本來就有，下兩行擇其一
sessionFactory.getConfiguration().addMapper(IChessTableDAO.class);
// sessionFactory.getConfiguration().addMappers("org.mybatis.dao");

．如果不想寫在Util也可寫在設定檔，還是<mapper>，一樣是針對package或其中一隻
．是mapper class喔！不是之前的mapper resource

<configuration>
    <properties resource="jdbc.properties" />
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--     <package name="org.mybatis.dao"/> -->
        <mapper class="org.mybatis.dao.IChessTableDAO"/>
    </mappers>
</configuration>