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

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

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

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);
}

<?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>

<?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> 

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;
    }
}

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

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();
    }
}

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

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();
    }
}

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