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

时间：2024-03-24 19:34:44

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

需要在WebContent下的lib下导入两个包

mybatis-3.2.5.jar

ojdbc6.jar

 package com.xdl.entity;

 import java.io.Serializable;

 public class Dept implements Serializable{

     private Integer deptno;//类型和名称与表保持一致

     private String dname;

     private String loc;

     public Integer getDeptno() {

         return deptno;

     }

     public void setDeptno(Integer deptno) {

         this.deptno = deptno;

     }

     public String getDname() {

         return dname;

     }

     public void setDname(String dname) {

         this.dname = dname;

     }

     public String getLoc() {

         return loc;

     }

     public void setLoc(String loc) {

         this.loc = loc;

     }

 }

Dept

 package com.xdl.Mapper;

 import java.util.List;

 import org.apache.ibatis.annotations.Param;

 import com.xdl.entity.Dept;

 public interface DeptMapper {

     /**

      * 查询所有

      *

      */

     public List<Dept> findAll();

     /**

      * 通过id查询

      *

      */

     public Dept findById(int no);

     /**

      * 插入

      *

      */

     public int save(Dept dept);

     /**

      * 修改

      *

      */

     public int update(Dept dept);

     /**

      * 通过id删除

      *

      */

     public int delete(int no);

     /**

      * 排序

      *

      */

     public List<Dept> findAllOrder(@Param("n") String no);

 }

DeptMapper

 <?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

 <mapper namespace="com.xdl.Mapper.DeptMapper">

     <select id="findAll" resultType="com.xdl.entity.Dept">

         select * from dept

     </select>

     <select id="findById" parameterType="int" resultType="com.xdl.entity.Dept">

         select *

         from dept where deptno = #{no}

     </select>

     <select id="findAllOrder" parameterType="String" resultType="com.xdl.entity.Dept">

         select * from dept order by ${n}

     </select>

     <insert id="save" parameterType="com.xdl.entity.Dept">

         insert into dept

         (deptno,dname,loc) values (dept_seq.nextval,#{dname},#{loc})

     </insert>

     <update id="update">

         update dept set dname = #{dname},loc = #{loc} where

         deptno = #{deptno}

     </update>

     <delete id="delete">

         delete from dept where deptno = #{no}

     </delete>

 </mapper>

deptmapper.xml

 <?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

 "http://mybatis.org/dtd/mybatis-3-config.dtd">

 <configuration>

     <!-- 将底层日志打印 -->

     <settings>

         <setting name="logImpl" value="STDOUT_LOGGING" />

     </settings>

     <environments default="environment">

         <environment id="environment">

             <transactionManager type="JDBC" />

             <!-- 指定数据库连 -->

             <dataSource type="POOLED">

                 <property name="driver" value="oracle.jdbc.OracleDriver" />

                 <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />

                 <property name="username" value="SCOTT" />

                 <property name="password" value="tiger" />

             </dataSource>

         </environment>

     </environments>

     <!-- 指定SQL定义文件 -->

     <mappers>

         <mapper resource="com/xdl/sql/DeptMapper.xml" />

     </mappers>

 </configuration>

sqlmap-config.xml

 package com.xdl.test;

 import java.io.Reader;

 import org.apache.ibatis.io.Resources;

 import org.apache.ibatis.session.SqlSession;

 import org.apache.ibatis.session.SqlSessionFactory;

 import org.apache.ibatis.session.SqlSessionFactoryBuilder;

 public class MyBatisUtil {

     static SqlSessionFactory factory;

     static {

         try {

             String conf = "sqlmap-config.xml"; // 定义配置文件

             SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

             Reader reader = Resources.getResourceAsReader(conf);

             factory = builder.build(reader);

         } catch (Exception e) {

             e.printStackTrace();

         }

     }

     public static SqlSession getSession() {

         SqlSession sqlSession = factory.openSession();

         return sqlSession;

     }

 }

MybatisUtli

写一个测试类(实现查询和排序)

 package com.xdl.test;

 import java.util.List;

 import org.apache.ibatis.session.SqlSession;

 import org.junit.Test;

 import com.xdl.Mapper.DeptMapper;

 import com.xdl.entity.Dept;

 public class TestDeptMapper {

     /**

      * 查询所有

      */

     @Test

     public void testFindAll() {

         SqlSession sqlSession = MyBatisUtil.getSession();

         // sqlSession.getMapper(接口.class); 根据DeptMapper映射器接口动态生成实现对象

         DeptMapper deptDao = sqlSession.getMapper(DeptMapper.class);

         System.out.println(deptDao.getClass().getName());

         List<Dept> list = deptDao.findAll();

         for (Dept dept : list) {

             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());

         }

         sqlSession.close();

     }

     /**

      * 进行排序

      */

     @Test

     public void testOrderBy() {

         SqlSession sqlSession = MyBatisUtil.getSession();

         // sqlSession.getMapper(接口.class); 根据DeptMapper映射器接口动态生成实现对象

         DeptMapper deptDao = sqlSession.getMapper(DeptMapper.class);

         System.out.println(deptDao.getClass().getName());

         List<Dept> list = deptDao.findAllOrder("deptno");

         for (Dept dept : list) {

             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());

         }

         sqlSession.close();

     }

 }

TestDeptMapper

写一个测试类(实现增删改查排序)

 package com.xdl.test;

 import java.util.List;

 import org.apache.ibatis.session.SqlSession;

 import org.junit.Test;

 import com.xdl.entity.Dept;

 public class TestDept {

     private static SqlSession sqlSession = MyBatisUtil.getSession();

     /**

      * 查询所有

      */

     @Test

     public void testFindAll() {

         // 使用sqlSession操作SQL selectList("id",parameterType值)

         List<Dept> list = sqlSession.selectList("com.xdl.Mapper.DeptMapper.findAll");

         for (Dept dept : list) {

             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());

         }

         sqlSession.close();

     }

     /**

      * 根据ID查询

      */

     @Test

     public void testFindById() {

         Dept dept = sqlSession.selectOne("com.xdl.Mapper.DeptMapper.findById", 10);

         if (dept != null) {

             System.out.println(dept.getDeptno() + ":" + dept.getDname() + ":" + dept.getLoc());

         } else {

             System.out.println("查询结果为空~~");

         }

         sqlSession.close();

     }

     /**

      * 插入

      */

     @Test

     public void testSave() {

         Dept dept = new Dept();

         dept.setDname("xian");

         dept.setLoc("dayanta");

         int rows = sqlSession.insert("com.xdl.Mapper.DeptMapper.save", dept);

         String str = "条记录插入成功";

         System.out.println(rows + str);

         sqlSession.commit();

         sqlSession.close();

     }

     /**

      * 修改

      */

     @Test

     public void testUpdate() {

         Dept dept = new Dept();

         dept.setDeptno(10);

         dept.setDname("spring");

         dept.setLoc("bj");

         int rows = sqlSession.update("com.xdl.Mapper.DeptMapper.update", dept);

         String str = "条记录修改成功";

         System.out.println(rows + str);

         sqlSession.commit();

         sqlSession.close();

     }

     /**

      * 通过id删除

      */

     @Test

     public void testDelete() {

         Dept dept = new Dept();

         int rows = sqlSession.delete("com.xdl.Mapper.DeptMapper.delete", 1);

         String str = "条记录删除成功";

         System.out.println(rows + str);

         sqlSession.commit();

         sqlSession.close();

     }

 }

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 ${}适合用在字段名或表名位置；#{}适合用在字段值位置

相关文章