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&serverTimezone=UTC&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("[email protected]");
// user.setUserAge(28);
// user.setUserPassword("221111");
// testInsert(user);
// testSelect(7);
//testDelete(7);
User user = new User();
user.setUserId(6);
user.setUserName("huachengyu");
user.setUserEmail("[email protected]");
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配置文件中’&‘是禁用字符，故而用转义字符代替（’&’’）