myBatisCURD - juedaiyuer/researchNote GitHub Wiki

#mybatis的CURD#

junit版本：4.10

##工具类MyBatis##

package juedaiyuer.util;

import java.io.InputStream;

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

public class MyBatisUtil {

	//获取SqlSessionFactory
	public static SqlSessionFactory getSqlSessionFactory()
	{
		// 项目配置文件所在位置
		String resource = "conf.xml";
		InputStream is = MyBatisUtil.class.getClassLoader().getResourceAsStream(resource);
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
		return factory;
	}

	//获取SqlSession
	public static SqlSession getSqlSession()
	{
		return getSqlSessionFactory().openSession();
	}

	//选择是否自动提交
	public static SqlSession getSqlSession(boolean isAutoCommit)
	{
		return getSqlSessionFactory().openSession(isAutoCommit);
	}

}

##MyBatis对表执行操作---基于XML实现##

###定义sql映射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，namespace的值习惯上设置成包名+sql映射文件名(去掉后缀)，这样就能够保证namespace的值是唯一的 -->
<mapper namespace="juedaiyuer.mapping.userMapper">
	<!-- 在select标签中编写查询的SQL语句， 设置select标签的id属性为getUser，id属性值必须是唯一的，不能够重复 使用parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型 
		resultType="juedaiyuer.entity.User"就表示将查询结果封装成一个User类的对象返回 User类就是users表所对应的实体类 -->
	<!-- 根据id查询得到一个user对象 -->
	<select id="getUser" parameterType="int" resultType="juedaiyuer.entity.User">
		select * from user where id=#{id}
	</select>

	<!-- 创建用户(Create) -->
	<insert id="addUser" parameterType="juedaiyuer.entity.User">
		insert into user(name,age) values(#{name},#{age})
	</insert>

	<!-- 删除用户(Remove) -->
	<delete id="deleteUser" parameterType="int">
		delete from user where id=#{id}
	</delete>

	<!-- 修改用户(Update) -->
	<update id="updateUser" parameterType="juedaiyuer.entity.User">
		update user set name=#{name},age=#{age} where id=#{id}
	</update>

	<!-- 查询全部用户 -->
	<select id="getAllUsers" resultType="juedaiyuer.entity.User">
		select * from user
	</select>
</mapper>

###单元测试###

package juedaiyuer.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import juedaiyuer.entity.User;
import juedaiyuer.util.MyBatisUtil;

import org.junit.Test;

//MyBatis的CURD---基于XML实现


public class junitTest {

	@Test
	public void testAdd()
	{
		//设定自动提交事务,不用进行sqlSession.commit()操作
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		String statement = "juedaiyuer.mapping.userMapper.addUser"; //映射sql的标识字符串
		User user = new User();
//		user.setName("王九九");
//		user.setAge(20);
	
		user.setName("小红帽");
		user.setAge(20);
	
		//执行插入操作
		int retResult = sqlSession.insert(statement,user);
		sqlSession.close();
		System.out.println(retResult);
	}

	@Test
	public void testUpdate()
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		String statement = "juedaiyuer.mapping.userMapper.updateUser"; //映射sql的标识字符串
		User user = new User();
		user.setId(4);
		user.setName("王九九");
		user.setAge(21);
		//执行更改操作
		int retResult = sqlSession.update(statement,user);
	
		sqlSession.close();
		System.out.println(retResult);
	
	}

	@Test
	public void testDelete()
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		String statement = "juedaiyuer.mapping.userMapper.deleteUser"; //映射sql的标识字符串
	
		int retResult = sqlSession.update(statement,5);
	
		sqlSession.close();
		System.out.println(retResult);
	}

	@Test
	public void testGetAll()
	{
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		String statement = "juedaiyuer.mapping.userMapper.getAllUsers"; //映射sql的标识字符串
	
		List<User> UserList = sqlSession.selectList(statement);
	
		sqlSession.close();
		System.out.println(UserList);
	
	}
}

##MyBatis对表执行操作---基于注解实现##

###定义sql映射的接口###

package juedaiyuer.mapping;

import java.util.List;
import juedaiyuer.entity.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author juedaiyuer
 * 定义sql映射的接口，使用注解指明方法要执行的SQL
 */
public interface UserMapperI {

	//使用@Insert注解指明add方法要执行的SQL
	@Insert("insert into user(name, age) values(#{name}, #{age})")
	public int add(User user);
	
	//使用@Delete注解指明deleteById方法要执行的SQL
	@Delete("delete from user where id=#{id}")
	public int deleteById(int id);
	
	//使用@Update注解指明update方法要执行的SQL
	@Update("update user set name=#{name},age=#{age} where id=#{id}")
	public int update(User user);
	
	//使用@Select注解指明getById方法要执行的SQL
	@Select("select * from user where id=#{id}")
	public User getById(int id);
	
	//使用@Select注解指明getAll方法要执行的SQL
	@Select("select * from user")
	public List<User> getAll();
}

需要说明的是，我们不需要针对UserMapperI接口去编写具体的实现类代码，这个具体的实现类由MyBatis帮我们动态构建出来，我们只需要直接拿来使用即可

###conf.xml文件中注册这个映射接口###

<mapper class="juedaiyuer.mapping.UserMapperI" />

###单元测试###

package juedaiyuer.test;

import java.util.List;

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

import juedaiyuer.entity.User;
import juedaiyuer.mapping.UserMapperI;
import juedaiyuer.util.MyBatisUtil;

/**
 * @author juedaiyuer
 * 
 *         1. Mybatis基于注解实现CURD单元测试
 * 
 */

public class TestCURDAnnotationMapper {

	@Test
	public void testAdd() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
	
		// 得到UserMapperI接口的实现类对象，UserMapperI接口的实现类对象由动态创建出来
		UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);

		User user = new User();
//		user.setName("厉倾城");
//		user.setAge(21);

		user.setName("苏子");
		user.setAge(20);
	
		int add = mapper.add(user);

		sqlSession.close();
		System.out.println(add);
	}

	@Test
	public void testUpdate(){
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);
	
		User user = new User();
		user.setId(5);
		user.setName("厉倾城");
		user.setAge(24);
	
		// 执行修改操作
		int retResult = mapper.update(user);
		sqlSession.close();
		System.out.println(retResult);
	}

	@Test
	public void testDelete() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);

		int retResult = mapper.deleteById(6);

		sqlSession.close();
		System.out.println(retResult);
	}

	@Test
	public void testGetUser(){
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);
	
		User user = mapper.getById(3);
		sqlSession.close();
		System.out.println(user);
	
	}

	@Test
	public void testGetAll() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
		UserMapperI mapper = sqlSession.getMapper(UserMapperI.class);

		List<User> userList = mapper.getAll();

		sqlSession.close();
		System.out.println(userList);
	}

}

##问题##

###插入数据乱码###

// mybatis的配置文件conf.xml
<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />

// 修改
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?characterEncoding=utf8" />

##source##

使用MyBatis对表执行CRUD操作

myBatisCURD - juedaiyuer/researchNote GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️