Mybatis實現部門表增刪改查以及排序

廢話不說,直接開門見山! 需要在WebContent下的lib下導入兩個包 mybatis-3.2.5.jar ojdbc6.jar 1 package com.xdl.entity; 2 3 import java.io.Serializable; 4 5 public class Dept im ...

廢話不說,直接開門見山!

需要在WebContent下的lib下導入兩個包

mybatis-3.2.5.jar

ojdbc6.jar

 1 package com.xdl.entity;
 2 
 3 import java.io.Serializable;
 4 
 5 public class Dept implements Serializable{
 6     private Integer deptno;//類型和名稱與表保持一致
 7     private String dname;
 8     private String loc;
 9     
10     public Integer getDeptno() {
11         return deptno;
12     }
13     public void setDeptno(Integer deptno) {
14         this.deptno = deptno;
15     }
16     public String getDname() {
17         return dname;
18     }
19     public void setDname(String dname) {
20         this.dname = dname;
21     }
22     public String getLoc() {
23         return loc;
24     }
25     public void setLoc(String loc) {
26         this.loc = loc;
27     }
28     
29     
30 }

Dept

 1 package com.xdl.Mapper;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.annotations.Param;
 6 
 7 import com.xdl.entity.Dept;
 8 
 9 public interface DeptMapper {
10     /**
11      * 查詢所有
12      * 
13      */
14     public List<Dept> findAll();
15 
16     /**
17      * 通過id查詢
18      * 
19      */
20     public Dept findById(int no);
21 
22     /**
23      * 插入
24      * 
25      */
26     public int save(Dept dept);
27 
28     /**
29      * 修改
30      * 
31      */
32     public int update(Dept dept);
33 
34     /**
35      * 通過id刪除
36      * 
37      */
38     public int delete(int no);
39 
40     /**
41      * 排序
42      * 
43      */
44     public List<Dept> findAllOrder(@Param("n") String no);
45 }

DeptMapper

 1 <?xml version="1.0" encoding="UTF-8" ?>  
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <mapper namespace="com.xdl.Mapper.DeptMapper">
 5     <select id="findAll" resultType="com.xdl.entity.Dept">
 6         select * from dept
 7     </select>
 8     <select id="findById" parameterType="int" resultType="com.xdl.entity.Dept">
 9         select *
10         from dept where deptno = #{no}
11     </select>
12     <select id="findAllOrder" parameterType="String" resultType="com.xdl.entity.Dept">
13         select * from dept order by ${n}
14     </select>
15     <insert id="save" parameterType="com.xdl.entity.Dept">
16         insert into dept
17         (deptno,dname,loc) values (dept_seq.nextval,#{dname},#{loc})
18     </insert>
19     <update id="update">
20         update dept set dname = #{dname},loc = #{loc} where
21         deptno = #{deptno}
22     </update>
23     <delete id="delete">
24         delete from dept where deptno = #{no}
25     </delete>
26 </mapper>

deptmapper.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>  
 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 3 "http://mybatis.org/dtd/mybatis-3-config.dtd">
 4 <configuration>
 5     <!-- 將底層日誌列印 -->
 6     <settings>
 7         <setting name="logImpl" value="STDOUT_LOGGING" />
 8     </settings>
 9     <environments default="environment">
10         <environment id="environment">
11             <transactionManager type="JDBC" />
12             <!-- 指定資料庫連 -->
13             <dataSource type="POOLED">
14                 <property name="driver" value="oracle.jdbc.OracleDriver" />
15                 <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />
16                 <property name="username" value="SCOTT" />
17                 <property name="password" value="tiger" />
18             </dataSource>
19         </environment>
20     </environments>
21     <!-- 指定SQL定義文件 -->
22     <mappers>
23         <mapper resource="com/xdl/sql/DeptMapper.xml" />
24     </mappers>
25 </configuration>

sqlmap-config.xml

 1 package com.xdl.test;
 2 
 3 import java.io.Reader;
 4 
 5 import org.apache.ibatis.io.Resources;
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.apache.ibatis.session.SqlSessionFactory;
 8 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 9 
10 public class MyBatisUtil {
11     static SqlSessionFactory factory;
12     static {
13         try {
14             String conf = "sqlmap-config.xml"; // 定義配置文件
15             SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
16             Reader reader = Resources.getResourceAsReader(conf);
17             factory = builder.build(reader);
18         } catch (Exception e) {
19             e.printStackTrace();
20         }
21     }
22 
23     public static SqlSession getSession() {
24         SqlSession sqlSession = factory.openSession();
25         return sqlSession;
26     }
27 }

MybatisUtli

寫一個測試類(實現查詢和排序)

 1 package com.xdl.test;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.junit.Test;
 7 
 8 import com.xdl.Mapper.DeptMapper;
 9 import com.xdl.entity.Dept;
10 
11 public class TestDeptMapper {
12     /**
13      * 查詢所有
14      */
15     @Test
16     public void testFindAll() {
17         SqlSession sqlSession = MyBatisUtil.getSession();
18         // sqlSession.getMapper(介面.class); 根據DeptMapper映射器介面動態生成實現對象
19         DeptMapper deptDao = sqlSession.getMapper(DeptMapper.class);
20         System.out.println(deptDao.getClass().getName());
21         List<Dept> list = deptDao.findAll();
22         for (Dept dept : list) {
23             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());
24         }
25         sqlSession.close();
26     }
27 
28     /**
29      * 進行排序
30      */
31     @Test
32     public void testOrderBy() {
33         SqlSession sqlSession = MyBatisUtil.getSession();
34         // sqlSession.getMapper(介面.class); 根據DeptMapper映射器介面動態生成實現對象
35         DeptMapper deptDao = sqlSession.getMapper(DeptMapper.class);
36         System.out.println(deptDao.getClass().getName());
37         List<Dept> list = deptDao.findAllOrder("deptno");
38         for (Dept dept : list) {
39             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());
40         }
41         sqlSession.close();
42     }
43 }

TestDeptMapper

寫一個測試類(實現增刪改查排序)

 1 package com.xdl.test;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.junit.Test;
 7 
 8 import com.xdl.entity.Dept;
 9 
10 public class TestDept {
11     private static SqlSession sqlSession = MyBatisUtil.getSession();
12 
13     /**
14      * 查詢所有
15      */
16     @Test
17     public void testFindAll() {
18         // 使用sqlSession操作SQL selectList("id",parameterType值)
19         List<Dept> list = sqlSession.selectList("com.xdl.Mapper.DeptMapper.findAll");
20         for (Dept dept : list) {
21             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());
22         }
23         sqlSession.close();
24     }
25 
26     /**
27      * 根據ID查詢
28      */
29     @Test
30     public void testFindById() {
31         Dept dept = sqlSession.selectOne("com.xdl.Mapper.DeptMapper.findById", 10);
32         if (dept != null) {
33             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());
34         } else {
35             System.out.println("查詢結果為空~~");
36         }
37         sqlSession.close();
38     }
39 
40     /**
41      * 插入
42      */
43     @Test
44     public void testSave() {
45         Dept dept = new Dept();
46         dept.setDname("xian");
47         dept.setLoc("dayanta");
48         int rows = sqlSession.insert("com.xdl.Mapper.DeptMapper.save", dept);
49         String str = "條記錄插入成功";
50         System.out.println(rows + str);
51         sqlSession.commit();
52         sqlSession.close();
53     }
54 
55     /**
56      * 修改
57      */
58     @Test
59     public void testUpdate() {
60         Dept dept = new Dept();
61         dept.setDeptno(10);
62         dept.setDname("spring");
63         dept.setLoc("bj");
64         int rows = sqlSession.update("com.xdl.Mapper.DeptMapper.update", dept);
65         String str = "條記錄修改成功";
66         System.out.println(rows + str);
67         sqlSession.commit();
68         sqlSession.close();
69     }
70 
71     /**
72      * 通過id刪除
73      */
74     @Test
75     public void testDelete() {
76         Dept dept = new Dept();
77         int rows = sqlSession.delete("com.xdl.Mapper.DeptMapper.delete", 1);
78         String str = "條記錄刪除成功";
79         System.out.println(rows + str);
80         sqlSession.commit();
81         sqlSession.close();
82     }
83 }

TestDept

1.在MyBatis中定義SQL語句時,如果SQL里有?號,就必須寫parameterType=””參數是int就寫對應的類型並且名字可以自定義

2. 在查詢的時候最後會返回一個結果集對象,所以就必須在後面繼續追加resultType=”包名.實體類名”

3 在執行DML的時候裡面要執行多個參數的時候,可以選擇集合或者對象,

parameterType=”包名.實體類名”.參數,類型和實體類要一致,參數不一致,可以通過給sql起別名解決,類型不一致就需要對框架里的部分參數進行轉換

4 通過實現增刪改查,發現DQL有resultType屬性,DML都沒有resultType屬性數據訪問層(Dao)

5 mapper.xml映射器里的<mapper namespace=”包名.介面名”,才可以達到Mapper.xml里的資料庫代碼映射到介面中

總結為:

a. Mapper介面中方法名和sql定義id值保持一致

b. Mapper介面中方法參數類型和sql定義中parameterType類型保持一致

c. Mapper介面中方法返回類型,多行查詢返回List,單行查詢返回對象類型和resultType保持一致DML返回類型為int或void

Mapper映射器規則

1 Mapper介面中方法名與SQL定義id值保持一致

2 Mapper介面中方法參數類型與SQL定義中parameterType類型保持一致

3 Mapper介面中方法返回類型，多行查詢返回List、單行返回對象，類型與resultType保持一致;增刪改操作返回類型為int或void

4 SQL定義文件namespace需要指定為"包名.介面名"

參數映射中${}和#{}的區別

1 #{}參數映射機制採用的是PrepareStatement，將SQL和參數分開發送

2 ${}參數映射機制採用Statement，將SQL和參數拼一起發送執行

3 建議採用#{}方式，更安全

4 ${}適合用在欄位名或表名位置；#{}適合用在欄位值位置