mybatis单表的curd操作

1、mapper.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="test">

	<insert id="insertStudent">
		INSERT INTO STUDENT(NAME, AGE, SCORE) VALUES(#{name}, #{age}, #{score})
	</insert>
	
	<insert id="insertStudentCatchId">
		INSERT INTO STUDENT(NAME, AGE, SCORE) VALUES(#{name}, #{age}, #{score})
		<selectKey resultType="int" keyProperty="id" order="AFTER">
			SELECT @@IDENTITY
		</selectKey>
	</insert>
	
	<delete id="deleteStudentById">
		DELETE FROM STUDENT WHERE ID = #{ID}
	</delete>
	
	<update id="updateStudent">
		UPDATE STUDENT SET NAME = #{name}, AGE = #{age}, SCORE = #{score} WHERE ID = #{id}
	</update>
	
	<select id="selectAllStudents" resultType="com.beans.Student">
		SELECT ID, NAME, AGE, SCORE FROM STUDENT
	</select>
	
	<select id="selectAllStudentsMap" resultType="com.beans.Student">
		SELECT ID, NAME, AGE, SCORE FROM STUDENT
	</select>
	
	<select id="selectStudentById" resultType="com.beans.Student">
		SELECT ID, NAME, AGE, SCORE FROM STUDENT WHERE ID = #{ID}
	</select>
	
	<select id="selectStudentByName" resultType="com.beans.Student">
		<!-- SELECT ID, NAME, AGE, SCORE FROM STUDENT WHERE NAME LIKE '%${VALUE}%' -->
		<!-- SELECT ID, NAME, AGE, SCORE FROM STUDENT WHERE NAME LIKE '%' #{name} '%' -->
		SELECT ID, NAME, AGE, SCORE FROM STUDENT WHERE NAME LIKE concat('%', #{name}, '%')
	</select>
</mapper>

2、mybatis.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>
	<!-- 加载jdbc.properties文件 -->
	<properties resource="jdbc.properties"/>
	
	<!-- 配置运行环境 -->
	<environments default="mysqlEM">
		<environment id="mysqlEM">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}"/>
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.user}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<!-- 加载映射文件 -->
	<mappers>
		<mapper resource="com/dao/mapper.xml"/>
	</mappers>
</configuration>

3、StudentDaoImpl

package com.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.beans.Student;
import com.utils.SqlSessionUtils;

public class StudentDaoImpl implements IStudentDao {

	private SqlSession sqlSession;
	
	public StudentDaoImpl() {
		this.sqlSession = SqlSessionUtils.getSqlSession();
	}

	@Override
	public void insertStudent(Student student) {
		try {
			sqlSession.insert("insertStudent", student);
			sqlSession.commit();
		}finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public void insertStudentCatchId(Student student) {
		try {
			sqlSession.insert("insertStudentCatchId", student);
			sqlSession.commit();
		}finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public void deleteStudentById(Integer id) {
		try {
			sqlSession.delete("deleteStudentById", id);
			sqlSession.commit();
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public void updateStudent(Student student) {
		try {
			sqlSession.update("updateStudent", student);
			sqlSession.commit();
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public List<Student> selectAllStudents() {
		try {
			List<Student> list = sqlSession.selectList("selectAllStudents");
			sqlSession.commit();
			return list;
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public Map<String, Object> selectAllStudentsMap() {
		try {
			Map<String, Object> map = sqlSession.selectMap("selectAllStudentsMap", "name");
			sqlSession.commit();
			return map;
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public Student selectStudentById(Integer id) {
		try {
			Student student = sqlSession.selectOne("selectStudentById", id);
			sqlSession.commit();
			return student;
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

	@Override
	public List<Student> selectStudentByName(String name) {
		try {
			List<Student> students = sqlSession.selectList("selectStudentByName", name);
			sqlSession.commit();
			return students;
		} finally {
			if (sqlSession != null) {
				sqlSession.close();
			}
		}
	}

}

4、MyTest

package com.test;



import java.util.List;
import java.util.Map;

import org.junit.Test;

import com.beans.Student;
import com.dao.IStudentDao;
import com.dao.StudentDaoImpl;

public class MyTest {
	
	private IStudentDao dao;
	
	@org.junit.Before
	public void Before() {
		dao = new StudentDaoImpl();
	}
	
	@Test
	public void test01() {
		Student student = new Student("王五", 25, 95.5);
		dao.insertStudent(student);
	}

	@Test
	public void test02() {
		Student student = new Student("赵六", 26, 96.5);
		System.out.println("插入之前：" + student);
		dao.insertStudentCatchId(student);
		System.out.println("插入之后：" + student);
	}
	
	@Test
	public void test03() {
		dao.deleteStudentById(43);
	}
	
	@Test
	public void test04() {
		Student student = new Student("杜朝", 22, 100);
		student.setId(36);
		dao.updateStudent(student);
	}
	
	@Test
	public void test05() {
		List<Student> list = dao.selectAllStudents();
		for (Student student : list) {
			System.out.println(student);
		}
	}
	
	@Test
	public void test06() {
		Map<String, Object> map = dao.selectAllStudentsMap();
		Object student = map.get("王五");
		System.out.println(student);
	}
	
	@Test
	public void test07() {
		Student student = dao.selectStudentById(36);
		System.out.println(student);
	}
	
	@Test
	public void test08() {
		List<Student> students = dao.selectStudentByName("王");
		for (Student student : students) {
			System.out.println(student);
		}
	}
}