MyBatis对表执行CRUD操作

雨点打透心脏的1/2处 2022-07-13 00:39 207阅读 0赞

## 一、使用MyBatis对表执行CRUD操作——基于XML的实现 ##

1、定义sql映射xml文件

userMapper.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="com.baowei.entity.User">
    
    	
    	
    	
    	<select id="findUserById" parameterType="int" resultType="com.baowei.entity.User">
    		SELECT
    		* FROM USER WHERE id=#{value}
    	</select>
    
    	
    	<select id="findUserByName" parameterType="java.lang.String"
    		resultType="com.baowei.entity.User">
    		SELECT * FROM USER WHERE name LIKE '%${value}%'
    	</select>
    
    	
    	<insert id="insertUser" parameterType="com.baowei.entity.User">
    		
    		<selectKey keyProperty="id" order="AFTER" resultType="int">
    			SELECT
    			LAST_INSERT_ID()
    		</selectKey>
    		insert into user(name,age)
    		values(#{name},#{age})
    
    	</insert>
    
    	
    	<delete id="deleteUser" parameterType="java.lang.Integer">
    		delete from user where
    		id=#{id}
    	</delete>
    
    	
    	<update id="updateUser" parameterType="com.baowei.entity.User">
    		update user set
    		name=#{name},age=#{age}
    		where id=#{id}
    	</update>
    
    </mapper>

2、在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="db.properties">
    	</properties>
    	<environments default="development">
    		<environment id="development">
    			
    			<transactionManager type="JDBC" />
    			
    			<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>
    		
    		<mapper resource="com/baowei/mapping/userMapper.xml" />
    		
    	</mappers>
    
    </configuration>

3、单元测试类代码如下：

import java.io.InputStream;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Before;
    import org.junit.Test;
    
    import com.baowei.entity.User;
    
    public class UserDaoXmlTest {
    
    	private SqlSession sqlSession;
    
    	@Before
    	public void setUp() throws Exception {
    
    		String resource = "SqlMapConfig.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
    				.build(inputStream);
    		sqlSession = sqlSessionFactory.openSession();
    	}
    
    	@Test
    	public void testFindUserById() throws Exception {
    		String statement = "com.baowei.entity.User.findUserById";// 映射sql的标识字符串
    		// 执行查询返回一个唯一user对象的sql
    		User user = sqlSession.selectOne(statement, 2);
    		System.out.println(user);
    	}
    
    	@Test
    	public void testfindUserByName() throws Exception {
    
    		String statement = "com.baowei.entity.User.findUserByName";// 映射sql的标识字符串
    		// 执行查询返回一个唯一user对象的sql
    		User user = sqlSession.selectOne(statement, "1111");
    		System.out.println(user);
    
    	}
    
    	@Test
    	public void testinsertUser() throws Exception {
    		String statement = "com.baowei.entity.User.insertUser";// 映射sql的标识字符串
    		// 执行查询返回一个唯一user对象的sql
    
    		User user = new User();
    		user.setAge(4444);
    		user.setName("44");
    
    		sqlSession.insert(statement, user);
    		sqlSession.commit();
    		System.out.println(user.getId());
    	}
    
    	@Test
    	public void testdeleteUser() throws Exception {
    		String statement = "com.baowei.entity.User.deleteUser";// 映射sql的标识字符串
    		// 执行查询返回一个唯一user对象的sql
    
    		sqlSession.insert(statement, 19);
    		sqlSession.commit();
    	}
    
    	@Test
    	public void testupdateUser() throws Exception {
    		String statement = "com.baowei.entity.User.updateUser";// 映射sql的标识字符串
    		// 执行查询返回一个唯一user对象的sql
    		User user = new User();
    		user.setId(19);
    		user.setAge(122);
    		user.setName("12");
    
    		sqlSession.update(statement, user);
    		sqlSession.commit();
    	}
    
    }

## 二、使用MyBatis对表执行CRUD操作——基于注解的实现 ##

1、定义sql映射的接口

IUserMapper接口的代码如下：

import java.util.List;
    import org.apache.ibatis.annotations.Delete;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Select;
    import org.apache.ibatis.annotations.Update;
    
    import com.baowei.entity.User;
    
    public interface IUserMapper {
    	// 使用@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帮我们动态构建出来，我们只需要直接拿来使用即可。

2、在SqlMapConfig.xml.xml文件中注册这个映射接口

<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    	<properties resource="db.properties">
    	</properties>
    	<environments default="development">
    		<environment id="development">
    			
    			<transactionManager type="JDBC" />
    			
    			<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>
    		
    		
    		
    		<mapper class="com.baowei.mapping.IUserMapper" />
    	</mappers>
    
    </configuration>

单元测试类的代码如下：

import java.io.InputStream;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Before;
    import org.junit.Test;
    
    import com.baowei.entity.User;
    import com.baowei.mapping.IUserMapper;
    
    public class UserDaoAnnationTest {
    
    	private SqlSession sqlSession;
    
    	@Before
    	public void setUp() throws Exception {
    
    		String resource = "SqlMapConfig.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
    				.build(inputStream);
    		sqlSession = sqlSessionFactory.openSession();
    	}
    
    	@Test
    	public void testFindUserById() throws Exception {
    		// 得到UserMapperI接口的实现类对象，UserMapperI接口的实现类对象由sqlSession.getMapper(UserMapperI.class)动态构建出来
    		IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
    		User user = mapper.getById(1);
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    		System.out.println(user.toString());
    	}
    
    	@Test
    	public void testinsertUser() throws Exception {
    		// 得到UserMapperI接口的实现类对象，UserMapperI接口的实现类对象由sqlSession.getMapper(UserMapperI.class)动态构建出来
    		IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
    		User user = new User();
    		user.setName("666");
    		user.setAge(20);
    		int add = mapper.add(user);
    		sqlSession.commit();
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    		System.out.println(add);
    	}
    
    	@Test
    	public void testdeleteUser() throws Exception {
    		// 得到UserMapperI接口的实现类对象，UserMapperI接口的实现类对象由sqlSession.getMapper(UserMapperI.class)动态构建出来
    		IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
    
    		mapper.deleteById(22);
    		sqlSession.commit();
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    	}
    
    	@Test
    	public void testupdateUser() throws Exception {
    		// 得到UserMapperI接口的实现类对象，UserMapperI接口的实现类对象由sqlSession.getMapper(UserMapperI.class)动态构建出来
    		IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
    		User user = new User();
    		user.setId(22);
    		user.setName("777");
    		user.setAge(20);
    		mapper.update(user);
    		sqlSession.commit();
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    
    	}
    
    }

## 三、使用MyBatis对表执行CRUD操作——基于Mapper代理的实现 ##

1、定义UserMapper.java的接口，和userMapper.xml的映射文件

![20161217133705709][]

2、关于userMapper.xml的配置

![20161217133813121][]

3、UserMapper.java的内容

import java.util.List;
    
    import com.baowei.entity.User;
    
    public interface UserMapper {
    
    	public User findUserById(int id) throws Exception;
    
    	public List<User> findUserByName(String name) throws Exception;
    
    	public void insertUser(User user) throws Exception;
    
    	public void deleteUser(int id) throws Exception;
    
    	public void updateUser(User user) throws Exception;
    }

4、userMapper.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="com.baowei.mapper.UserMapper">
    
    	
    	
    	
    	<select id="findUserById" parameterType="int" resultType="com.baowei.entity.User">
    		SELECT
    		* FROM USER WHERE id=#{value}
    	</select>
    
    	
    	<select id="findUserByName" parameterType="java.lang.String"
    		resultType="com.baowei.entity.User">
    		SELECT * FROM USER WHERE name LIKE '%${value}%'
    	</select>
    
    	
    	<insert id="insertUser" parameterType="com.baowei.entity.User">
    		
    		<selectKey keyProperty="id" order="AFTER" resultType="int">
    			SELECT
    			LAST_INSERT_ID()
    		</selectKey>
    		insert into user(name,age)
    		values(#{name},#{age})
    
    	</insert>
    
    	
    	<delete id="deleteUser" parameterType="java.lang.Integer">
    		delete from user where
    		id=#{id}
    	</delete>
    
    	
    	<update id="updateUser" parameterType="com.baowei.entity.User">
    		update user set
    		name=#{name},age=#{age}
    		where id=#{id}
    	</update>
    
    </mapper>

5、在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="db.properties">
    	</properties>
    	<environments default="development">
    		<environment id="development">
    			
    			<transactionManager type="JDBC" />
    			
    			<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>
    		<mapper class="com.baowei.mapper.UserMapper" />
    	</mappers>
    
    </configuration>

6、测试代码

import java.io.InputStream;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Before;
    import org.junit.Test;
    import com.baowei.entity.User;
    import com.baowei.mapper.UserMapper;
    
    public class UserDaoMapperTest {
    
    	private SqlSession sqlSession;
    
    	@Before
    	public void setUp() throws Exception {
    
    		String resource = "SqlMapConfig.xml";
    		InputStream inputStream = Resources.getResourceAsStream(resource);
    
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
    				.build(inputStream);
    		sqlSession = sqlSessionFactory.openSession();
    	}
    
    	@Test
    	public void testFindUserById() throws Exception {
    		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    		User user = mapper.findUserById(1);
    		sqlSession.close();
    		System.out.println(user.toString());
    	}
    
    	@Test
    	public void testinsertUser() throws Exception {
    		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    		User user = new User();
    		user.setName("666");
    		user.setAge(20);
    		mapper.insertUser(user);
    		sqlSession.commit();
    		sqlSession.close();
    	}
    
    	@Test
    	public void testdeleteUser() throws Exception {
    		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    		mapper.deleteUser(23);
    		sqlSession.commit();
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    	}
    
    	@Test
    	public void testupdateUser() throws Exception {
    		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    		User user = new User();
    		user.setId(23);
    		user.setName("777");
    		user.setAge(20);
    		mapper.updateUser(user);
    		sqlSession.commit();
    		// 使用SqlSession执行完SQL之后需要关闭SqlSession
    		sqlSession.close();
    
    	}
    
    }

## 四、代码下载 ##

[代码下载][Link 1]

[20161217133705709]: /images/20220713/9f72f940a624420e86e5948b61a0040e.png
[20161217133813121]: /images/20220713/c6d0c318c6d749f4bc006c2c3ceb676f.png
[Link 1]: http://pan.baidu.com/s/1nvbeDPv