Mybatis实现部门表增删改查以及排序
分类:
IT文章
•
2022-07-29 19:15:16
废话不说,直接开门见山!
需要在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 >
6 select * from dept
7 </select>
8 <select >
9 select *
10 from dept where deptno = #{no}
11 </select>
12 <select >
13 select * from dept order by ${n}
14 </select>
15 <insert >
16 insert into dept
17 (deptno,dname,loc) values (dept_seq.nextval,#{dname},#{loc})
18 </insert>
19 <update >
20 update dept set dname = #{dname},loc = #{loc} where
21 deptno = #{deptno}
22 </update>
23 <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 >
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 ${}适合用在字段名或表名位置;#{}适合用在字段值位置