Mybatis实现部门表增删改查以及排序
废话不说,直接开门见山!
需要在WebContent下的lib下导入两个包
mybatis-3.2.5.jar
ojdbc6.jar
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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>
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
写一个测试类(实现查询和排序)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
写一个测试类(实现增删改查排序)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
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 ${}适合用在字段名或表名位置;#{}适合用在字段值位置