Mybatis 增删查改(CRUD)操作

CRUD分别是增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。
基于原来原来搭建的项目：ssm20181229test,传送门，学习了一下基本的数据库访问的增删查改操作。

Step1 创建了新的数据库

创建了新的数据库ssm20181229test,并创建了table:user，user表结构和内容如下：

Step2更改了User.java类

package com.cr.pojo;
public class User {
	private int userId;
	private String userName;
	private String userPassword;
	private int userAge;
	private String userEmail;

	public int getUserAge() {
		return userAge;
	}
	public void setUserAge(int userAge) {
		this.userAge = userAge;
	}
	public String getUserEmail() {
		return userEmail;
	}
	public void setUserEmail(String userEmail) {
		this.userEmail = userEmail;
	}
	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 getUserPassword() {
		return userPassword;
	}

	public void setUserPassword(String userPassword) {
		this.userPassword = userPassword;
	}
}

Step3 创建了测试用的mybatis.xml配置文件

在src/test/resources源文件下创建了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>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.cj.jdbc.Driver" />
				<property name="url" 
				value="jdbc:mysql://localhost/ssm20181229test?useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true" />
				<property name="username" value="root" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<mapper resource="UserMapper.xml" />
	</mappers>
</configuration>

Step4 修改了UserMapper.java

代码如下：

package com.cr.mapper;

import com.cr.pojo.User;
public interface UserMapper
{
	
	public String getPasswordByName(String userName);
	//select
	public User getUserById(int userId);
	//insert
	public void insertUser(User user);
	//delete
	public void deleteUserById(int userId);	
	//update
	public void updateUser(User user);
}

Step5 修改了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.cr.mapper.UserMapper">

     <select id="getPasswordByName" parameterType="String" resultType="String">
        SELECT userPassword
        FROM user
        WHERE userName = #{userName}
    </select>
    
    <select id="getUserById" parameterType="int" resultType="com.cr.pojo.User">
    	SELECT *
    	FROM user
    	WHERE userId = #{userId}
    </select>
    
    <insert id="insertUser" parameterType="com.cr.pojo.User">
    	INSERT INTO user(userName,userPassword,userAge,userEmail)
    	VALUES(#{userName},#{userPassword},#{userAge},#{userEmail})
    </insert>
    
    <delete id="deleteUserById" parameterType="int">
    	DELETE FROM user
    	WHERE userId = #{userId}
    </delete>
    
    <update id="updateUser" parameterType="com.cr.pojo.User">
    	UPDATE USER 
    	SET
    	userName = #{userName},
    	userPassword = #{userPassword},
    	userAge = #{userAge},
    	userEmail = #{userEmail}
    	WHERE
    	userId = #{userId}
    </update>
</mapper>

Step6 增加了测试类UserMapperTest.java
在src/test/java源文件夹下创建了测试类UserMapperTest.java，代码如下：

package com.cr.test;

import java.io.Reader;

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 com.cr.mapper.UserMapper;
import com.cr.pojo.User;


public class UserMapperTest {
	 private static SqlSessionFactory sqlSessionFactory;
	    private static Reader reader;
	    

	    static {
	        try {
	            reader = Resources.getResourceAsReader("mybatis.xml");
	            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }

	    public static SqlSessionFactory getSession() {
	        return sqlSessionFactory;
	    }

	    /**
	     * @param args
	     */
	    public static void main(String[] args) {
	      // testSelect(1);
	       
//	       User user = new User();
//	       user.setUserId(0);
//	       user.setUserName("huachengyu");
//	       user.setUserEmail("ssssqas@qq.com");
//	       user.setUserAge(28);
//	       user.setUserPassword("221111");
//	       testInsert(user);
//	       testSelect(7);
	    	//testDelete(7);
	    	
	       User user = new User();
	       user.setUserId(6);
	       user.setUserName("huachengyu");
	       user.setUserEmail("ssssqas@qq.com");
	       user.setUserAge(28);
	       user.setUserPassword("221111");
	       testUpdate(user);
	    }
	    
	    
	    //select查询测试模块：根据id获得User对象
	    private static void testSelect(int userId) 
	    {
	    	 SqlSession session = sqlSessionFactory.openSession();
		        try 
		        {
		        	UserMapper userMapper = session.getMapper(UserMapper.class);
		        	System.out.println(" Select User from user where id="+userId+"...");
		        	User user = userMapper.getUserById(userId);
		        	if(user != null)
		        	{
		        		System.out.println("user name:"+user.getUserName()+",user age:"+user.getUserAge()
		        		+",user email:"+user.getUserEmail());
		        	}

		        } finally {
		            session.close();
		        }
	    }
	    //delete查询测试模块：
	    private static void testDelete(int userId) 
	    {
	    	SqlSession session = sqlSessionFactory.openSession();
	        try 
	        {
	        	UserMapper userMapper = session.getMapper(UserMapper.class);
	        	System.out.println(" Test delete...");
	        	userMapper.deleteUserById(userId);
	        	//注意这里需要提交session
	        	session.commit();
	        } finally {
	            session.close();
	        }
	    }
	    //insert测试模块：向user表中插入行
	    private  static void testInsert(User user)
	    {
	    	SqlSession session = sqlSessionFactory.openSession();
	        try 
	        {
	        	UserMapper userMapper = session.getMapper(UserMapper.class);
	        	System.out.println(" Test insert...");
	        	userMapper.insertUser(user);
	        	//注意这里需要提交session
	        	session.commit();
	        } finally {
	            session.close();
	        }
	    }
	    //update测试模块
	    private static void testUpdate(User user)
	    {
	    	SqlSession session = sqlSessionFactory.openSession();
	        try 
	        {
	        	UserMapper userMapper = session.getMapper(UserMapper.class);
	        	System.out.println(" Test update...");
	        	userMapper.updateUser(user);
	        	//注意这里需要提交session
	        	session.commit();
	        } finally {
	            session.close();
	        }
	    }
}

结语
至此，整个代码创建完成，并成功的实现了CRUD功能。整个测试需要用到的文件：
User.java,UserMapper.java,UserMapperTest.java,UserMapper.xml,mybatis.xml。
注意事项：
1.UserMapperTest.java中除了select操作，其他三个操作均会对user表的信息产生更改，需要提交事务(session.commit())。
2.xml配置文件中’&‘是禁用字符，故而用转义字符代替（’&’’）