【Mybatis】常见的crud操作

写出mybatis一些常见的crud的操作(mysql)

StudentMapper.xml

<?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="cn.qblank.entity.Student">
	<resultMap type="student" id="studentMap">
		<!-- <id property="id" column="id"/> -->
		<result property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="sal" column="sal"/>
	</resultMap>
	
	<!-- 添加学生 -->
	<insert id="add" parameterType="student">
		insert into students(id,name,sal) values(#{id},#{name},#{sal})
	</insert>
	
	<!-- 查询学生 -->
	<!-- mybatis会将查询出来的表记录和studentMap这个id所对应的映射结果相互匹配 -->
	<select id="findById" parameterType="int" resultMap="studentMap">
		select id,name,sal from students where id = #{id}
	</select>
	
	<!-- 查询所有 -->
	<select id="findAll" resultType="student">
		select id,name,sal from students
	</select>
	
	<!-- 修改学生 -->
	<update id="update" parameterType="student">
		update students set name = #{name},sal = #{sal} where id = #{id}
	</update>
	
	<delete id="delete" parameterType="int">
		delete from students where id = #{id}
	</delete>
	
	<!-- 
		注意：这个insert/update/delete标签只是一个模板，在做操作时，其实是以SQL语句为核心的
		     即在做增/删/时，insert/update/delete标签可通用，
		     但做查询时只能用select标签
	-->	
	<!-- 无条件分页查询  取出map中的页码和每页的大小 -->
	<select id="findAllWithFy" parameterType="map" resultMap="studentMap">
		select id,name,sal from students limit #{pstart},#{psize}
	</select>
	
	<!-- 有条件分页查询 -->
	<select id="findAllByOrderWithFy" parameterType="map" resultMap="studentMap">
		select id,name,sal from students where id > #{ptj} limit #{pstart},#{psize};
	</select>
	
</mapper>

然后写好对应的crud操作

/**
 * 添加学生
 */
public int add(Student student){
	SqlSession sqlSession = null;
	int rows = 0;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		rows = sqlSession.insert(Student.class.getName() +".add",student);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
	}finally{
		MybatisUtil.closeSqlSession();
	}
	return rows;
}

/**
 * 通过id查找学生
 * @param id
 * @return
 */
public Student findById(int id){
	SqlSession sqlSession = null;
	Student student = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		student = sqlSession.selectOne(Student.class.getName() +".findById",id);
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		MybatisUtil.closeSqlSession();
	}
	return student;
}

/**
 * 查询所有
 * @return
 */
public List<Student> findAll(){
	SqlSession sqlSession = null;
	List<Student> students = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		students = sqlSession.selectList(Student.class.getName() +".findAll");
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		MybatisUtil.closeSqlSession();
	}
	return students;
}

/**
 * 修改学生
 * @param student
 * @return
 */
public int update(Student student){
	SqlSession sqlSession = null;
	int rows = 0;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		rows = sqlSession.update(Student.class.getName() +".update",student);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
	}finally{
		MybatisUtil.closeSqlSession();
	}
	return rows;
}

/**
 * 删除一条记录
 * @param student
 * @return
 */
public int delete(int id){
	SqlSession sqlSession = null;
	int rows = 0;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		rows = sqlSession.delete(Student.class.getName() +".delete",id);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		sqlSession.rollback();
	}finally{
		MybatisUtil.closeSqlSession();
	}
	return rows;
}

/**
 * 无条件分页查询
 * @param start
 * @param size
 * @return
 */
public List<Student> findAllWithFy(int start,int size){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		LinkedHashMap<String, Object> map = new LinkedHashMap<>();
		map.put("pstart", start);
		map.put("psize", size);
		return sqlSession.selectList(Student.class.getName() +".findAllWithFy",map);
	} catch (Exception e) {
		e.printStackTrace();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}

/**
 * 有条件分页查询
 * @param start
 * @param size
 * @return
 */
public List<Student> findAllByOrderWithFy(int tj,int start,int size){
	SqlSession sqlSession = null;
	try {
		sqlSession = MybatisUtil.getSqlSession();
		LinkedHashMap<String, Object> map = new LinkedHashMap<>();
		map.put("ptj", tj);
		map.put("pstart", start);
		map.put("psize", size);
		return sqlSession.selectList(Student.class.getName() +".findAllByOrderWithFy",map);
	} catch (Exception e) {
		e.printStackTrace();
		throw e;
	}finally{
		MybatisUtil.closeSqlSession();
	}
}