（学习记录）mybatis简单增删改查的小项目

入门学习ing...

项目总体结构：

 

数据库内容：

一、mybatisconfig.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>
	<!-- 别名 -->
	<typeAliases>
		<typeAlias alias="UserInfo" type="com.sp.po.UserInfo"/>
	</typeAliases>
	
	<environments default="developments">
		<environment id="mysql">
			<transactionManager type="JDBC"></transactionManager>
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"></property>
				<property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>
				<property name="username" value="root"></property>
				<property name="password" value="root"></property>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/sp/mapper/UserInfoMapper.xml"/>
	</mappers>

</configuration>

二、创建实体类UserInfo

package com.sp.po;

public class UserInfo {

	private int userId;
	private String userName;
	private String userSex;

	public int getUserId() {
		return userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getUserSex() {
		return userSex;
	}

	public void setUserSex(String userSex) {
		this.userSex = userSex;
	}

	@Override
	public String toString() {
		return "UserInfo [userId=" + userId + ", userName=" + userName + ", userSex=" + userSex + "]";
	}
	
	
}

三、创建DBFactory.java以获取SqlSessionFactory接口对象

package com.sp.utils;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class DBFactory {
	
	public static SqlSessionFactory getsqlSessionFactory() {
		
		try{
			String resource =  "mybatisconfig.xml";
			InputStream in = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in,"mysql");
			return sqlSessionFactory;
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
}

四、实现mybatis简单查询

1、创建sql映射文件UserInfoMapper.xml

<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sp.mapper">

	<sql id="userinfo">
		user_id,user_name,user_sex
	</sql>
	
	<!-- 根据编号查询信息 -->
	<select id="selectUser" parameterType="Integer" resultType="hashmap">
		select 
		<include refid="userinfo"></include>
		from user_info
		where user_id=#{userId}
	</select>
	
	</delete>
</mapper>

2、创建测试程序UserTest.java

package com.sp.test;

import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Before;
import org.junit.Test;

import com.sp.po.UserInfo;
import com.sp.utils.DBFactory;

public class UserTest {
	
	@Test
	public void select1() {
		SqlSessionFactory sqlSessionFactory = DBFactory.getsqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		HashMap map = session.selectOne("com.sp.mapper.selectUser",4);
		System.out.println("编号："+map.get("user_id"));
		System.out.println("姓名："+map.get("user_name"));
		System.out.println("性别："+map.get("user_sex"));
	}

	
}

采用单元测试框架JUnit进行测试的查询结果：

3、第二种查询（采用resultMap元素）

在映射文件UserInfoMapper.xml中写入：

    <resultMap id="usermap" type="UserInfo">
		<result property="userId" column="user_id"></result>
		<result property="userName" column="user_name"></result>
		<result property="userSex" column="user_sex"></result>
	</resultMap>

    <select id="getUser" parameterType="Integer" resultMap="usermap">
		select 
		<include refid="userinfo"></include>
		from user_info
		where user_id=#{userId}
	</select>

在测试程序UserTest.java中写入：

	@Test
	public void select2() {
		SqlSessionFactory sqlSessionFactory = DBFactory.getsqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		UserInfo user = session.selectOne("com.sp.mapper.getUser",3);
		System.out.println(user);
	}

采用单元测试框架JUnit进行测试的查询结果：

 

五、实现mybatis简单添加

在映射文件UserInfoMapper.xml中写入：

	<!-- 添加信息 -->
	<insert id="addUser" parameterType="UserInfo">
			insert into user_info (user_id,user_name,user_sex)
			values(#{userId},#{userName},#{userSex})
	</insert>

在测试程序UserTest.java中写入： 

	@Test
	public void add() {
		SqlSessionFactory sqlSessionFactory = DBFactory.getsqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		
		UserInfo user = new UserInfo();
		user.setUserId(7);
		user.setUserName("曹操");
		user.setUserSex("男");
		
		int cont = session.insert("com.sp.mapper.addUser",user);
		if(cont>0) {
			System.out.println("添加成功！");
		}else {
			System.out.println("添加失败！");
		}
		
		//提交事务
		session.commit();
		session.close();
	}
	

采用单元测试框架JUnit进行测试的添加结果：

刷新数据库内容：

 

六、实现mybatis简单修改

在映射文件UserInfoMapper.xml中写入：

	<!-- 根据编号修改信息 -->
	<update id="updateUser" parameterType="UserInfo">
		update user_info set user_name=#{userName},user_sex=#{userSex}
		where user_id=#{userId}
	</update>

在测试程序UserTest.java中写入：

	//全局变量 避免重复代码
	private SqlSession session;
	
	@Before
	public void init() {
		SqlSessionFactory sqlSessionFactory = DBFactory.getsqlSessionFactory();
		session = sqlSessionFactory.openSession();
	}
	
	@Test
	public void update() {
		UserInfo user = new UserInfo();
		user.setUserId(7);
		user.setUserName("貂蝉");
		user.setUserSex("女");
		
		int count = session.update("com.sp.mapper.updateUser",user);
		if(count>0) {
			System.out.println("修改成功！");
		}else {
			System.out.println("修改失败！");
		}
		
		//提交事务
		session.commit();
		session.close();
	}

采用单元测试框架JUnit进行测试的修改结果：

刷新数据库内容：

 

七、实现mybatis简单删除

在映射文件UserInfoMapper.xml中写入：

	<!-- 根据编号删除信息 -->
	<delete id="deleteUser" parameterType="int">
		delete from user_info
		where user_id=#{userId}
	</delete>

在测试程序UserTest.java中写入：

	@Test
	public void delete() {
		int count = session.delete("com.sp.mapper.deleteUser",10);
		if(count>0) {
			System.out.println("删除成功！");
		}else {
			System.out.println("删除失败！");
		}
		
		//提交事务
		session.commit();
		session.close();
	}

采用单元测试框架JUnit进行测试的删除结果：

刷新数据库内容：