Mybatis 原生DAO实现数据增删改查

一、开发环境

1. JDK
   java version “1.8.0_171”
   Java™ SE Runtime Environment (build 1.8.0_171-b11)
   Java HotSpot™ 64-Bit Server VM (build 25.171-b11, mixed mode)
2. Eclipse
   Eclipse Java EE IDE for Web Developers.
   Version: Oxygen.3a Release (4.7.3a)
3. MySQL
   其中的数据库文件在地址：点击下载
4. Mybatis
   mybatis-3.4.6

二、项目结构

三、编写SqlMapConfig.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="config/db.properties"></properties>

	<!-- 定义别名 -->
	<typeAliases>
		<!-- 针对批量别名定义,别名就是类名（首字母大小写都可以） -->
		<package name="com.po"/>
	</typeAliases>
	
	<!-- 和Spring整合后environments配置将废除 -->
	<environments default="development">
		<environment id="development">
			<!-- 使用JDBC事务管理，事务控制由mybatis管理 -->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池，由mybatis管理 -->
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>

	<!-- 加载映射文件 -->
	<mappers>
		<!-- 通过resource加载 -->
		<mapper resource="config/sqlmap/User.xml" />
	</mappers>
</configuration>

db.properties文件内容为：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=123456

四、编写User.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">

<!-- namespace:命名空间，作用是对sql进行分类化管理, 注意：在mapper代理方法开发， namespace有特殊作用 -->
<mapper namespace="test">
	<select id="findUserById" parameterType="int" 
		resultType="user">
		SELECT * FROM USER WHERE ID = #{id}
	</select>
	
	<select id="findUserByName" parameterType="java.lang.String"
		resultType="user">
		SELECT * FROM USER WHERE USERNAME LIKE '%${value}%'
	</select>
	
	<insert id="insertUser" parameterType="user">
		<!-- 
			将插入的主键返回到user对象中 
			SELECT LAST_INSERT_ID():得到将insert进去的记录的主键值，只适用与自增主键
			keyProperty：将查询到的主键设置到parameterType指定的对象的哪个属性
			order：SQL语句执行的顺序，BEFORE或AFTER
		-->
		<selectKey keyProperty="id" order="AFTER" resultType="int">
			SELECT LAST_INSERT_ID()
		</selectKey>
		INSERT INTO USER(USERNAME,SEX,BIRTHDAY,ADDRESS) VALUES(#{username},#{sex},#{birthday},#{address})
	</insert>
	
	<delete id="deleteUser" parameterType="int">
		DELETE FROM USER WHERE ID = #{id}
	</delete>
	
	<update id="updateUser" parameterType="user">
		UPDATE USER 
		SET USERNAME = #{username}, SEX = #{sex}, BIRTHDAY = #{birthday}, ADDRESS = #{address}
		WHERE ID = #{id}
	</update>
</mapper>

五、编写接口UserDao.java

package com.dao;

import java.util.List;

import com.po.User;

/**
 * 描述：原生dao接口，管理用户
 * @author 欧阳
 * @since 2018年10月9日
 */
public interface UserDao {
	
	//根据id查询用户信息
	public User findUserById(int id) throws Exception;
	
	//根据username查询用户信息
	public List<User> findUserByName(String name) throws Exception;
	
	//插入用户信息
	public void insertUser(User user) throws Exception;
	
	//更新用户信息
	public void updateUser(User user) throws Exception;
	
	//根据id删除用户信息
	public void deleteUserById(int id) throws Exception;
}

六、实现接口UserDaoImpl.java

package com.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.po.User;

/**
 * 描述：原生dao开发实现类
 * @author 欧阳
 * @since 2018年10月9日
 */
public class UserDaoImpl implements UserDao {
	//通过构造函数注入SqlSessionFactory
	private SqlSessionFactory  sqlSessionFactory;
	public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
		super();
		this.sqlSessionFactory = sqlSessionFactory;
	}
	
	@Override
	public User findUserById(int id) throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		User user = sqlSession.selectOne("test.findUserById", id);
		sqlSession.close();
		return user;
	}
	
	@Override
	public List<User> findUserByName(String name) throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		List<User> user = sqlSession.selectList("test.findUserById", name);
		sqlSession.close();
		return user;
	}

	@Override
	public void insertUser(User user) throws Exception{
		SqlSession sqlSession = sqlSessionFactory.openSession();
		sqlSession.insert("test.insertUser", user);
		sqlSession.commit();
		sqlSession.close();
	}

	@Override
	public void deleteUserById(int id) throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		sqlSession.delete("test.deleteUser", id);
		sqlSession.commit();
		sqlSession.close();
	}

	@Override
	public void updateUser(User user) throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		sqlSession.insert("test.updateUser", user);
		sqlSession.commit();
		sqlSession.close();
	}

}

七、测试

package com.dao;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.po.User;

public class UserDaoImplTest {
	
	private SqlSessionFactory  sqlSessionFactory;
	//此方法是在执行testFindUserById之前执行
	@Before
	public void setUp() throws Exception {
		String resource = "config/SqlMapConfig.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 创建会话工厂
		this.sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(inputStream);
	}
	
	@Test
	public void testFindUserById() {
		UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
		try {
			User user = userDao.findUserById(2);
			System.out.println(user);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testFindUserByName() {
		UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
		try {
			List<User> users = userDao.findUserByName("小");
			System.out.println(users.size());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testInsertUser() {
		UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
		try {
			//插入用户对象
			User user = new User();
			user.setUsername("王小姐");
			user.setAddress("中国");
			
			userDao.insertUser(user);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testDeleteUserById() {
		UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
		try {
			userDao.deleteUserById(17);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	@Test
	public void testUpdateUser() {
		UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
		try {
			//插入用户对象
			User user = new User();
			user.setId(3);
			user.setUsername("王小姐");
			user.setAddress("中国");
			
			userDao.updateUser(user);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

八、问题总结

1、在 UserDao 接口的实现类 UserDaoImpl 中，各实现方法存在大量模板方法，存在代码冗余，不利于程序员开发和维护；

2、调用 sqlSession 方法时将 statement 的id硬编码了；

3、在UserDaoImpl 类中每个实现方法中调用sqlSession 方法时传入的变量，由于sqlsession方法使用泛型，即使变量类型传入错误，在编译阶段也不报错，不利于程序员开发。

在Mybatis 开发时使用Mapper 代理开发将解决这些问题。Mybatis 学习笔记——使用Mapper代理的方式实现数据增删改查