Mybatis实现部门表增删改查以及排序

2018-08-21 05:32:08来源：博客园阅读 ()

废话不说,直接开门见山!

需要在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 }