mybatis
CRUD是指在做增加(Create)、读取(Retrieve)(重新得到数据)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。
这儿主要讲解mybatis的增删改查,对入门没有了解的,可以去参考上一篇文章。如有不对之处,请谅解,并提出,本人也是才自学的新手。
浅谈MyBatis 之 入门(一)
数据库表
表内容:
总配置文件
MyBatis-config.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>
<!-- 引入外部 配置 文件 -->
<properties resource="jdbc.properties" />
<!-- 配置 别名 -->
<typeAliases>
<typeAlias alias="Dept" type="com.wm.mybatis.POJO.Dept"/>
</typeAliases>
<environments default="development">
<environment id="development" >
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClass}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 配置的映射文件 -->
<mappers>
<mapper resource="mapper/deptCURD.xml" />
</mappers>
</configuration>
POJO
Dept.java
package com.wm.mybatis.POJO;
public class Dept {
private Integer id ;
private String name ;
private String address ;
public Dept(){}
public Dept(Integer id, String name, String address) {
super();
this.id = id;
this.name = name;
this.address = address;
}
public Integer getId() {
System.out.println(id);
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
System.out.println(name);
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
System.out.println(address);
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "Dept [id=" + id + ", name=" + name + ", address=" + address
+ "]";
}
}
首先写一个公共类
公共类SessionManagerUtil.java
是实现 获取sqlsession 用的,这样做的好处就是:便于管理当前线程与session的一个关系,还有就是 便于操作session。
package com.wm.mybatis.util;
import java.io.IOException;
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 SessionManagerUtil {
// 同一个线程 下 session 操作
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sessionFactory = null;
// 静态加载块 加载配置文件
static{
try {
Reader config = Resources.getResourceAsReader("MyBatis-config.xml");
sessionFactory = new SqlSessionFactoryBuilder().build(config);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException();
}
}
// 防止直接new
private SessionManagerUtil(){}
// 获取session
public static SqlSession getSession(){
SqlSession sqlSession = threadLocal.get();
if (sqlSession == null) {
sqlSession = sessionFactory.openSession();
threadLocal.set(sqlSession);
}
return sqlSession;
}
/// 关闭session
public static void closeSession(){
SqlSession sqlSession = threadLocal.get();
if (sqlSession != null) {
sqlSession.close();
threadLocal.remove(); // 与当前线程 分离
}
}
}
CRUD
增加
首先配置映射文件
<?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.wm.mybatis.dao.IDeptCURDMapperDao">
<!-- 由于数据库表字段 和 JavaBean Dept类属性 不一致 所以 要配置 resultMap 来实现一一对应-->
<resultMap type="Dept" id="resultDept">
<result property="id" column="d_id" />
<result property="name" column="d_name" />
<result property="address" column="d_address" />
</resultMap>
<!-- 增加 部门 -->
<insert id="addDept" parameterType="Dept" >
insert into base_55demo.demo_mawei_dept(d_id,d_name,d_address) values(#{id},#{name},#{address})
</insert>
</mapper>
DAO层:
DeptCURDMapperDaoImpl
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.wm.mybatis.POJO.Dept;
import com.wm.mybatis.util.SessionManagerUtil;
public class DeptCURDMapperDaoImpl{
// 添加部门
public void addDept(Dept dept) throws Exception {
SqlSession session = null;
try {
session = SessionManagerUtil.getSession(); //获取session
int count = session.insert(IDeptCURDMapperDao.class.getName()+".addDept", dept);
System.out.println("插入了记录:" + count + " 条");
// 更新 要提交
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback(); // 回滚
throw e;
} finally{
SessionManagerUtil.closeSession(); //关闭session
}
}
}
测试
TestDeptCURD
package com.wm.mybatis.Test;
import java.util.List;
import org.junit.Test;
import com.wm.mybatis.POJO.Dept;
import com.wm.mybatis.dao.DeptCURDMapperDaoImpl;
public class TestDeptCURD {
// 增加
@Test
public void addDept() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
dao.addDept(new Dept(35, "卫生部", "香港"));
}
}
结果
数据库表:
删除
映射配置
<!-- 删除 -->
<delete id="deleteDept" parameterType="Dept">
delete from base_55demo.demo_mawei_dept t where t.d_id = #{id} and t.d_name = #{name}
</delete>
<!-- 删除部门 根据 ID -->
<delete id="deleteDeptById" parameterType="int">
delete from base_55demo.demo_mawei_dept t where t.d_id = #{id}
</delete>
DAO层
// 删除
public void deleteDept(Dept dept) throws Exception {
SqlSession session = null;
try {
session = SessionManagerUtil.getSession();
int count = session.delete(IDeptCURDMapperDao.class.getName()+".deleteDept", dept);
System.out.println("删除了记录:"+count+" 条");
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
throw e;
} finally{
SessionManagerUtil.closeSession();
}
}
//删除部门 根据 ID来执行
public void deleteDeptById(int id) throws Exception {
SqlSession session = null;
try {
session = SessionManagerUtil.getSession();
int count = session.delete(IDeptCURDMapperDao.class.getName()+".deleteDeptById", id);
System.out.println("删除了记录:"+count+" 条");
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
throw e;
} finally{
SessionManagerUtil.closeSession();
}
}
测试
// 删除
@Test
public void deleteDept() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
dao.deleteDept(new Dept(9, "9", "9"));
}
// 根据ID 删除
@Test
public void deleteDeptById() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
dao.deleteDeptById(8);
dao.deleteDeptById(12);
}
结果
数据库表:
结果表明:ID为8、9、12的数据都被删除。
修改
配置映射
<!-- 更新 -->
<update id="updateDept" parameterType="Dept">
update base_55demo.demo_mawei_dept t
set t.d_name = #{name} , t.d_address = #{address}
where t.d_id = #{id}
</update>
DAO层
// 修改更新
public void updateDept(Dept dept) throws Exception {
SqlSession session = null;
try {
session = SessionManagerUtil.getSession();
int count = session.update(IDeptCURDMapperDao.class.getName()+".updateDept", dept);
System.out.println("更新了记录:"+count+" 条");
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
throw e;
} finally{
SessionManagerUtil.closeSession();
}
}
测试
// 更新
@Test
public void updateDept() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
Dept dept = dao.getDeptById(6);
dept.setName("计费BOSS"); //修改数据
dao.updateDept(dept);
}
结果
数据库表:
查询
配置映射
<!-- 根据ID 来查询部门 -->
<select id="getDeptById" parameterType="int" resultMap="resultDept">
select *
from base_55demo.demo_mawei_dept t
where t.d_id = #{id}
</select>
<!-- 查询所有的部门 -->
<select id="getDeptALL" resultMap="resultDept">
select * from base_55demo.demo_mawei_dept t
</select>
<!-- 分页查询 -->
<select id="getDeptByPage" resultMap="resultDept" parameterType="map">
<![CDATA[
select dept.d_id,dept.d_name, dept.d_address
from (select rownum num, t.*
from base_55demo.demo_mawei_dept t
where rownum <= (#{start}+#{num})) dept
where dept.num > #{start}
]]>
</select>
<!-- 查询 总记录数 -->
<select id="getTotalNum" resultType="int">
select count(0) from base_55demo.demo_mawei_dept
</select>
DAO层
//查询 通过 ID
public Dept getDeptById(int id) {
SqlSession session = SessionManagerUtil.getSession();
Dept dept = session.selectOne(IDeptCURDMapperDao.class.getName()+".getDeptById", id);
SessionManagerUtil.closeSession();
System.out.println(dept);
return dept;
}
//查询 所以 部门
public List<Dept> getDeptALL() {
SqlSession session = SessionManagerUtil.getSession();
List<Dept> depts = session.selectList(IDeptCURDMapperDao.class.getName()+".getDeptALL");
SessionManagerUtil.closeSession();
return depts;
}
// 分页查询
public List<Dept> getDeptByPage(int start, int num){
SqlSession session = SessionManagerUtil.getSession();
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("start", start);
map.put("num", num);
List<Dept> depts = session.selectList(IDeptCURDMapperDao.class.getName()+".getDeptByPage", map);
return depts;
}
// 查询所有记录
public int getTotalNum(){
SqlSession session = SessionManagerUtil.getSession();
int num = session.selectOne(IDeptCURDMapperDao.class.getName()+".getTotalNum");
SessionManagerUtil.closeSession();
return num;
}
测试
// 根据 ID 查询
@Test
public void getDeptById() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
Dept dept = dao.getDeptById(19);
System.out.println(dept);
}
// 查询所有的
@Test
public void getDeptALL() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
List<Dept> depts = dao.getDeptALL();
for (Dept dept : depts) {
System.out.println(dept);
}
}
// 分页查询
@Test
public void getDeptByPage() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
int totalNum = dao.getTotalNum();
int pageNum = 4;
int totalPage = (totalNum % pageNum == 0) ? (totalNum / pageNum) : (totalNum / pageNum) + 1 ;
for (int i = 0; i < totalPage; i++) {
System.out.println("第 "+(i+1)+" 页");
List<Dept> depts = dao.getDeptByPage(i*pageNum,pageNum);
for (Dept dept : depts) {
System.out.println(dept);
}
}
}
// 查询总记录
@Test
public void getTotalNum() throws Exception{
DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl();
int num = dao.getTotalNum();
System.out.println("总共:"+num+" 条记录");
}
结果
此处 展示分页查询的结果,其他的查询比较简单。
总结
- 1、如果映射文件配置namespace 为dao接口类路径,则在实现操作时,可以简化。
<mapper namespace="com.wm.mybatis.dao.IDeptCURDMapperDao">
此处就可以写成 IDeptCURDMapperDao.class.getName()
session.insert(IDeptCURDMapperDao.class.getName()+".addDept", dept);
- 2、如果配置文件中返回的是多结果查询,本应该是List,但是这儿配置List里面放置的类型为返回值类型。resultMap=”resultDept”
<!-- 分页查询 -->
<select id="getDeptByPage" resultMap="resultDept" parameterType="map">
- 3、如果配置映射参数是map时,获取值的名字要与map放入的名字一致。parameterType=”map”
<!-- 分页查询 -->
<select id="getDeptByPage" resultMap="resultDept" parameterType="map">
<![CDATA[
select dept.d_id,dept.d_name, dept.d_address
from (select rownum num, t.*
from base_55demo.demo_mawei_dept t
where rownum <= (#{start}+#{num})) dept
where dept.num > #{start}
]]>
</select>
// 分页查询
public List<Dept> getDeptByPage(int start, int num){
SqlSession session = SessionManagerUtil.getSession();
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("start", start);
map.put("num", num);
List<Dept> depts = session.selectList(IDeptCURDMapperDao.class.getName()+".getDeptByPage", map);
return depts;
}
配置映射文件中和dao层的map放入值名称要一致
map.put(“start”, start);
map.put(“num”, num);
4、一个小技巧
在使用mybatis时,配置Log4j配置,可以打印 显示出(sessions连接ID、连接的开启、关闭、及执行的SQL、动态SQL参数等信息)
在log4j.properties文件中加入:
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
显示的结果如下:
这样便于学习mybatis,可以看见过程及背后的SQL。