Mybatis初学进行后台MySQL数据库增删改查

最新推荐文章于 2025-07-06 14:43:14 发布

原创最新推荐文章于 2025-07-06 14:43:14 发布 · 587 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Java #MySQL #mybatis #web #后端

本文详细介绍了使用MyBatis框架进行数据库操作的具体步骤，包括原生开发模式、Dao接口开发和Mapper动态代理方式，并提供了完整的代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

看了两天的MyBatis文档，尝试自己跟着文档一步步实现这个框架，步骤循序渐进。

一、原生开发模式

MySQL数据库先建一张user表，有username，birthday，sex，address这几个字段。

sqlMapConfig.xml核心配置文件，建立数据库连接，把User.xml通过mappers标签加进去

<?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="jdbc.properties"/>
	<!-- 别名 包以其子包下所有类   头字母大小都行-->
	<typeAliases>
<!-- 		<typeAlias type="com.itheima.mybatis.pojo.User" alias="User"/> -->
		<package name="com.itheima.mybatis.pojo"/>
	</typeAliases>
	<!-- 和spring整合后 environments配置将废除    -->
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理 -->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}" />
				<property name="url"
					value="jdbc:mysql://localhost:3323/mybatis?characterEncoding=utf-8" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	
	<!-- Mapper的位置  Mapper.xml 写Sql语句的文件的位置 .mapper配置的几种方式 resource、url、class、package-->
	<mappers>
        <mapper resource="sqlmapa/User.xml"/>
        <mapper resource="mapper/UserMapper.xml"/>
	</mappers>
	
</configuration>

User.xml在这里面，用sql标签进行查询

<?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">
<!-- 写Sql语句   -->
<mapper namespace="test">
	<!-- 通过ID查询一个用户  id,类型，所对应的实体类的路径 -->
	
	<!-- mapper.xml文件中一个sql对应一个Mapped Statement对象，sql的id即是Mapped statement的id。  -->
	<select id="findUserBySex" parameterType="Integer" resultType="com.itheima.mybatis.pojo.User">
		select * from user where sex = #{v}
	</select>
	
	
	<!-- 进行like模糊查询 -->
	<select id="queryUserByUsername1"  parameterType="string" resultType="com.itheima.mybatis.pojo.User">
		select * from user where username like #{username}
	</select>
    
    
    <!-- 如果传入的参数是简单数据类型，${}里面必须写value -->
    <select id="queryUserByUsername2"  parameterType="string" resultType="com.itheima.mybatis.pojo.User">
		select * from user where username like '%${value}%'
	</select>
	
	
	<!--  实现添加用户INSERT INTO `user` (username,birthday,sex,address) VALUES 
                    ('黄忠','2016-07-26','1','三国')
	-->
	<insert id="saveUser" parameterType="com.itheima.mybatis.pojo.User">
	<!-- selectKey 标签实现主键返回 -->
	<!-- keyColumn:主键对应的表中的哪一列 -->
	<!-- keyProperty：主键对应的pojo中的哪一个属性 -->
	<!-- order：设置在执行insert语句前执行查询id的sql，还是在执行insert语句之后执行查询id的sql -->
		<!-- resultType：设置返回的id的类型
        SELECT LAST_INSERT_ID()是mysql的函数，返回auto_increment自增列新记录id值。-->
	       <selectKey keyColumn="id" keyProperty="id" order="AFTER"
		              resultType="int">
		              SELECT LAST_INSERT_ID()
	       </selectKey>
	
	    insert into user 
	    (username,birthday,sex,address) values 
	    (#{username},#{birthday},#{sex},#{address})
    </insert>
    
    
    
    <!-- 更新用户 -->
    <update id="updateUserById" parameterType="com.itheima.mybatis.pojo.User" >
        update user 
        set username = #{username} WHERE id = #{id}
    </update>
    
    <!-- 删除用户记录 -->
    <delete id="deleteUserById" parameterType="int" >
        delete from user where
        id = #{id}
    </delete>
    
</mapper>

User实体类

package com.itheima.mybatis.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String username;// 
	private String sex;// 
	private Date birthday;// 
	private String address;// 


	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}
}

编写一个抽象类，在这个抽象写一个加载SqlSessionFactory封装了一个实现的方法，返回sqlSession对象，便于获取该对象子类继承，另外一个写抽象方法。

package com.itheima.mybatis.junit;

import java.io.IOException;
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;

public abstract class MybatisFirst {//加载过程，写成抽象类的形式，子类继承
	
	
	public  SqlSession testMybatis() throws IOException{//抽象类里面的普通方法，创建sqlsession
		 
        String resource = "sqlMapConfig.xml";
		
		InputStream in = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
				
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		return sqlSession;
		
	}
	public abstract void TestMybatis() throws IOException ;
}

写一个子类作为测试类，通过super()继承该抽象类的SqlSession对象，进行具体的sqlsession.select()方法传参进行查询

package com.itheima.mybatis.junit;


import java.io.IOException;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.itheima.mybatis.pojo.User;

/**
 * @author Administrator
 *
 */
public class MybatisFirstTest extends MybatisFirst{//子类，继承了父类的加载过程，把sqlSession通过super拿过来，用SqlSession对象接
		                                           //执行具体业务查询

	@Test
	public void TestMybatis () throws IOException {//该测试类，把mybatis核心配置引进，创建会话工厂SqlSessionFactory对象SqlSession，进行查询。
	    	
		SqlSession sqlSession = super.testMybatis();
		
		
        try {//查询一条记录
			List<Object> list0 = sqlSession.selectList("test.findUserBySex", 1);
		    for(Object user1 : list0){
		    	System.out.println(user1);
		    }
		} finally {
			sqlSession.close();//关闭之后，之后的查询不能进行
		}
		
		try {//模糊查询一条或者多条记录
			List<Object> list1 = sqlSession.selectList("queryUserByUsername1", "%王%");
			for(Object user2 : list1){
			System.out.println(user2);
		}
		} finally {
			sqlSession.close();
		}
		
		try {//查询一条或者多条记录
			List<Object> list2 = sqlSession.selectList("queryUserByUsername2", "王");
		    for(Object user3 : list2){
			System.out.println(user3);
		}	
		} finally {
			sqlSession.close();
		}	
			
		try {
		User uu = new User();//插入用户记录
		uu.setUsername("张飞");
		uu.setBirthday(new Date());
		uu.setSex("男");
		uu.setAddress("蜀国");
		sqlSession.insert("saveUser", uu);
		System.out.println(uu);
		sqlSession.commit();//事务commit插入才成功
		} finally {
			sqlSession.close();
		}
		
	
		try {
		User uu1 = new User();//更新用户
		uu1.setId(28);
		uu1.setUsername("关羽");
		uu1.setBirthday(new Date());
		uu1.setSex("男");
		uu1.setAddress("蜀国");
		sqlSession.update("updateUserById", uu1);
		System.out.println(uu1);	
		sqlSession.commit();//事务commit插入才成功
		} finally {
			sqlSession.close();
		}
			
		try {//删除用户记录
		sqlSession.delete("deleteUserById", 32);
		sqlSession.commit();
		} finally {
			sqlSession.close();
		}
	}
	
}

至此，方法一原始的mybatis框架进行简单的增删改查就完成了。进行JUNIT测试可以在控制台看到相应的update行数更新打印。

二、原始Dao接口开发，DaoImp实现方法，测试类进行传参具体测试，sqlMapConfig.xml和User.xml可以不需要重新建立，拿来直接用即可。

UserDao接口

package com.itheima.mybatis.Dao;

import java.util.List;

import com.itheima.mybatis.pojo.User;

public interface UserDao {//通过原始Dao进行开发，一个UserDao接口，一个ImplUserDao继承重写具体方法，再写一个测试类具体传参
	
	User findUserBySex(int sex);//返回一个查询结果,在数据库里面sex我用的是1或者2表示
	
	List<User> queryUserByUsername1(String user2);//返回多个查询结果
	
	List<User> queryUserByUsername2(String user3);//返回多个查询结果
	
	void saveUser(User uu);//保存时没有返回值,因为xml里面没有resultType,插入一条
	
	void updateUserById(User uu1);//更新可以没有返回值，通过查询用户名进行更新
	
	void deleteUserById(int id);//删除时没有返回值，不需要打印，通过id删除
}

ImpUserDao接口实现类,接口的这些查询方法通过sqlSession的查询方法实现，没有传参进去，等着在测试类里面传参
package com.itheima.mybatis.Dao;

import java.util.List;

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


import com.itheima.mybatis.pojo.User;

public class ImplUserDao implements UserDao {

	private SqlSessionFactory sqlSessionFactory;

	public ImplUserDao(SqlSessionFactory sqlSessionFactory) {
		super();
		this.sqlSessionFactory = sqlSessionFactory;
	}
	
	@Override
	public User findUserBySex(int sex) {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		User user = sqlSession.selectOne("findUserBySex", sex);
		// 释放资源
		sqlSession.close();
		return user;
	
	}

	@Override
	public List<User> queryUserByUsername1(String user2) {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		List<User> user = sqlSession.selectList("queryUserByUsername1", user2);
		sqlSession.close();
		return user;
	}

	@Override
	public List<User> queryUserByUsername2(String user3) {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		List<User> user = sqlSession.selectList("queryUserByUsername2", user3);
		sqlSession.close();
		return user;
	}

	@Override
	public void saveUser(User uu) {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		sqlSession.insert("saveUser", uu);
		sqlSession.commit();
		sqlSession.close();
	}

	@Override
	public void updateUserById(User uu1) {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		sqlSession.update("updateUserById", uu1);
		sqlSession.commit();
		sqlSession.close();
	}

	@Override
	public void deleteUserById(int id) {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		User user = sqlSession.selectOne("deleteUserById", id);
		sqlSession.commit();
		// 释放资源
		sqlSession.close();
		
	}

}

测试类

package com.itheima.mybatis.junit;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.itheima.mybatis.Dao.ImplUserDao;
import com.itheima.mybatis.Dao.UserDao;
import com.itheima.mybatis.pojo.User;

public  class UserDaoTest   {//一开始定义成了抽象类，结果不能进行单元测试。。。
     
	
	private SqlSessionFactory sqlSessionFactory;

	@Before//进行核心加载
	public void init() throws Exception {
		// 创建SqlSessionFactoryBuilder
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		// 加载SqlMapConfig.xml配置文件
		InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
		// 创建SqlsessionFactory
		this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
	}
	
	@Test
	public void testfindUserBySex(){//查询性别
		UserDao userDao = new ImplUserDao(this.sqlSessionFactory);
		User user = userDao.findUserBySex(2);
		System.out.println(user);
	}
	
	
	@Test
	public void testqueryUserByUsername1(){//模糊查询一条或者多条
		UserDao userDao = new ImplUserDao(this.sqlSessionFactory);
		List<User> list1 = userDao.queryUserByUsername1("%王%");
		for (User user : list1) {
			System.out.println(user);
		}
	}
	
	@Test
	public void testqueryUserByUsername2(){//查询一条或者多条
		UserDao userDao = new ImplUserDao(this.sqlSessionFactory);
		List<User> list2 = userDao.queryUserByUsername2("王");
		for (User user : list2) {
			System.out.println(user);
		}
	}
	
	@Test
	public void testsaveUser(){//插入一条用户记录
		UserDao userDao = new ImplUserDao(this.sqlSessionFactory);
		User uu = new User();
		uu.setUsername("张飞");
		uu.setBirthday(new Date());
		uu.setSex("男");
		uu.setAddress("蜀国");
		userDao.saveUser(uu);
		System.out.println(uu);
	}
  
	
	@Test
	public void testupdateUserById(){//更新一条用户记录，对id=28的进行更新
		UserDao userDao = new ImplUserDao(this.sqlSessionFactory);
		User uu1 = new User();//更新用户
		uu1.setId(28);
		uu1.setUsername("关羽");
		uu1.setBirthday(new Date());
		uu1.setSex("男");
		uu1.setAddress("蜀国");
		userDao.updateUserById(uu1);
		System.out.println(uu1);
	}
	
    @Test
    public void testdeleteUserById(){//根据id删除一条记录
    	UserDao userDao = new ImplUserDao(this.sqlSessionFactory);
        userDao.deleteUserById(34);    	
    }
    
	 
}

通过方式二，既可以用Dao接口的形式进行Mybatis对数据库增删改查操作，但是缺点也是显而易见的，

原始Dao开发中存在以下问题：

Dao方法体存在重复代码：

通过SqlSessionFactory创建sqlsession，调用SqlSession的数据库操作方法

调用sqlSession的数据库操作方法需要指定statement的id，这里存在硬编码，不得于开发维护。

三、通过Mapper动态代理方式

Mapper接口开发方法只需要程序员编写Mapper接口（相当于Dao接口），由Mybatis框架根据接口定义创建接口的动态代理对象，代理对象的方法体同上边Dao接口实现类方法。

Mapper接口开发需要遵循以下规范：

1、Mapper.xml文件中的namespace与mapper接口的类路径相同。

2、Mapper接口方法名和Mapper.xml中定义的每个statement的id相同

3、Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql 的parameterType的类型相同

4、Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同

在sqlMapConfig.xml核心配置文件里面加一条UserMapper.xml

<!-- Mapper的位置  Mapper.xml 写Sql语句的文件的位置 .mapper配置的几种方式 resource、url、class、package-->
	<mappers>
        <mapper resource="sqlmapa/User.xml"/>
        <mapper resource="mapper/UserMapper.xml"/>
	</mappers>

接着创建UserMapper.xml，该配置文件其实和上文提到的User.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">

<!-- namespace：命名空间，用于隔离sql -->
<!-- 还有一个很重要的作用，使用动态代理开发DAO，1. namespace必须和Mapper接口类路径一致 -->

<mapper namespace="com.itheima.mybatis.Mapper.UserMapper"><!-- namespace地址为 mapper接口地址-->
    <!-- 根据用户id查询用户 -->
	<!-- 2. id必须和Mapper接口方法名一致 -->
	<!-- 3. parameterType必须和接口方法参数类型一致 -->
	<!-- 4. resultType必须和接口方法返回值类型一致 -->
    <select id = "queryUserById" parameterType = "int" resultType="com.itheima.mybatis.pojo.User">
       select * from user
       where id = #{id} 
    </select>
    
    <select id ="queryUserByuserName1" parameterType ="string" resultType ="com.itheima.mybatis.pojo.User">
       select * from user
       where username like '%${value}%'
    </select>
    
    <select id = "queryUserByuserName2" parameterType = "string" resultType ="com.itheima.mybatis.pojo.User">
       select * from user
       where username like #{username}  
    </select>
    
    <insert id = "saveUser" parameterType ="com.itheima.mybatis.pojo.User">
           <!-- 主键自增 -->
         <selectKey keyProperty="id" keyColumn="id" order="AFTER"
			resultType="int">
			select last_insert_id()
		 </selectKey>

       insert into user 
	   (username,birthday,sex,address) values 
	   (#{username},#{birthday},#{sex},#{address})
    </insert>


    <update id = "updateByUserId" parameterType = "com.itheima.mybatis.pojo.User" >
        update user 
        set username = #{username} WHERE id = #{id}
    </update>
    
    <delete id = "deleteByUserId" parameterType = "int">
        delete from user where
        id = #{id} 
    </delete>
</mapper>

接着，创建UserMapper接口，写增删改查的抽象方法，在测试类里面具体传参实现

package com.itheima.mybatis.Mapper;

import java.util.List;

import com.itheima.mybatis.pojo.User;

public interface UserMapper {
	
	User queryUserById(int id);
	
	List<User> queryUserByuserName1(String userName1 );
	
	List<User> queryUserByuserName2(String userName2);
	
	void saveUser(User user);
	
	void updateByUserId(User user);
	
	void deleteByUserId(int id);

}

UserMapperTest类

package com.itheima.mybatis.junit;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

import com.itheima.mybatis.Mapper.UserMapper;
import com.itheima.mybatis.pojo.User;
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;

public class UserMapperTest {
	private SqlSessionFactory sqlSessionFactory;

	@Before//进行核心加载
	public void init() throws Exception {
		// 创建SqlSessionFactoryBuilder
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		// 加载SqlMapConfig.xml配置文件
		InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
		// 创建SqlsessionFactory
		this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
	}
	@Test
	public void testqueryUserById(){
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		try {
			//从SqlSession获取UserMappper接口的代理对象
			UserMapper userMapper = sqlSession.getMapper(UserMapper.class);//非常重要
			//User user = sqlSession.selectOne("queryUserById", 1);//通过mapper动态代理，不需要手动进行selectOne来查询，直接对接口方法传参即可
			User user = userMapper.queryUserById(1);
		    sqlSession.commit();
		    System.out.println(user);
		} finally {
			sqlSession.close();
		}	 
		
	}
	@Test
	public void testqueryUserByuserName1(){
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		try {
			List<User> list1 = userMapper.queryUserByuserName1("王");
			for(User user1 : list1){
				System.out.println(user1);
			}
		} finally {
			sqlSession.close();
		}
		
	}
	@Test
	public void testqueryUserByuserName2(){
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		try {
			List<User> list2 = userMapper.queryUserByuserName2("王");
			for(User user2 : list2){
				System.out.println(user2);
			}
			sqlSession.commit();
		} finally {
			sqlSession.close();
		}
		
	}
	@Test
	public void testsaveUser(){
		SqlSession sqlsession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlsession.getMapper(UserMapper.class);
		try {
		User user = new User();
		user.setAddress("中国");
		user.setBirthday(new Date());
		user.setSex("男");
		user.setUsername("张飞");
		userMapper.saveUser(user);
		sqlsession.commit();
		} finally {
		sqlsession.close();
		
	}

  }
	
	@Test
	public void testupdateByUserId(){
		SqlSession sqlsession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlsession.getMapper(UserMapper.class);
		try {			
			User user = new User();
			user.setAddress("中国");
			user.setBirthday(new Date());
			user.setId(42);
			user.setSex("男");
			user.setUsername("关羽");
			userMapper.updateByUserId(user);
			System.out.println(user);
			sqlsession.commit();
		} finally {
			sqlsession.close();
		}
	}
	
	@Test
	public void testdeleteByUserId(){
		SqlSession sqlsession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlsession.getMapper(UserMapper.class);
		try {
			userMapper.deleteByUserId(29);
			sqlsession.commit();
		} finally {
			sqlsession.close();
		}
	}
}

通过Mapper动态代理的方式，只需要在配置文件配好的基础上进行接口开发，在测试类里面直接创建接口类对象userMapper，然后直接用该对象调用UserMapper定义的增删改查的抽象方法直接传参，就完成查询，十分方便。如果接口里面抽象方法是查询一条，则可以直接返回User的对象，如果是查询多条，可以用List<User>进行接收，用增强for打印输出查询栈。如果是insert或者update等非查询方法，可以不用打印输出栈，即不用pojo User类型进行接收，直接刷新MySQL看User表即可。高级部分后续补上。