Mybatis增删改查(非接口，接口两种方式)

青旅半醒 2023-10-05 11:20 14阅读 0赞

#### MybatisCRUD ####

*  CRUD简称
 *  1.非接口实现
 *   *  1.1创建mapper.xml映射文件
     *  1.2编写sql语句
     *   *  1.2.1 新增数据
         *  1.2.2 删除数据
         *  1.2.3 更改数据
         *  1.2.4 查询数据
     *  1.3配置mybatis核心配置文件
 *  2. 接口实现
 *   *  2.1 创建接口
     *  2.2 创建mapper映射文件
     *  2.3 配置mybatis核心配置文件
     *  2.4 test测试
 *  3. mybatisUtil工具类（减少重复代码编写）

## CRUD简称 ##

CRUD:增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)

## 1.非接口实现 ##

### 1.1创建mapper.xml映射文件 ###

namespace：名称空间，填写对应mapper.xml全限名

### 1.2编写sql语句 ###

#### 1.2.1 新增数据 ####

mapper.xml:  
parameterType：输入类型

<insert id="insertEmployee" parameterType="com.zwh.pojo.Employee">
    	insert into employee
    (name,gender,birthday,mobile,email,position,note) values(#{name},#{gender},#{birthday},#{mobile},#{email},#{position},#{note})
    </insert>

test测试文件:

@Test
    	public void insertEmployee() throws IOException {
        
    		/* 读取配置文件 */
    		InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    		/* 根据配置文件构建SqlSessionFactory会话工厂 */
    		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    		/* 通过SqlSessionFactory创建SqlSession */
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		Employee employee=new Employee();
    		employee.setName("李四");
    		employee.setGender(1);
    		employee.setBirthday(new Date());
    		employee.setMobile("1365445855");
    		employee.setEmail("123456@qq.com");
    		employee.setPosition("工程师");
    		employee.setNote("新增");
    		/* SqlSession执行映射文件中自定义的SQL，并返回映射结果 */
    		try {
        			
    			int insert =
     sqlSession.insert("com.zwh.mapper.EmployeeMapper.insertEmployee", employee);
    			System.out.println("添加成功："+insert+"行发生更改");
    			/* 提交事务 */
    			sqlSession.commit();
    		} finally {
        		
    			sqlSession.close();
    		}		
    	}

#### 1.2.2 删除数据 ####

mapper.xml：

<delete id="deleteEmployee" parameterType="Integer">
    	delete from employee where empid=#{empid}
    </delete>

test测试文件：

@Test
    	public void deleteEmployee() throws IOException {
        
    		/* 读取配置文件 */
    		InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    		/* 根据配置文件构建SqlSessionFactory会话工厂*/
    		SqlSessionFactory sqlSessionFactory =
     new SqlSessionFactoryBuilder().build(inputStream);
    		/* 通过SqlSessionFactory创建SqlSession */
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		/* SqlSession执行映射文件中自定义的SQL，并返回映射结果 */
    		try {
        			
    			int insert =
     sqlSession.delete("com.zwh.mapper.EmployeeMapper.deleteEmployee", 6);
    			System.out.println("删除成功："+insert+"行发生更改");
    			/* 提交事务 */
    			sqlSession.commit();
    		} finally {
        		
    			sqlSession.close();
    		}		
    	}

#### 1.2.3 更改数据 ####

mapper.xml:

<update id="updateEmployee" parameterType="com.zwh.pojo.Employee">
    	update employee set name=#{name} where empid=#{empId}
    </update>

test测试文件：

@Test
    	public void updateEmployee() throws IOException {
        
    		/* 读取配置文件 */
    		InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    		/* 根据配置文件构建SqlSessionFactory会话工厂*/
    		SqlSessionFactory sqlSessionFactory =
     new SqlSessionFactoryBuilder().build(inputStream);
    		/* 通过SqlSessionFactory创建SqlSession */
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		Employee employee=new Employee();
    		employee.setEmpId(2);
    		employee.setName("王五");
    		/* SqlSession执行映射文件中自定义的SQL，并返回映射结果 */
    		try {
        			
    			int insert =
     sqlSession.update("com.zwh.mapper.EmployeeMapper.updateEmployee", employee);
    			System.out.println("修改成功："+insert+"行发生更改");
    			/* 提交事务 */
    			sqlSession.commit();
    		} finally {
        		
    			sqlSession.close();
    		}		
    	}

#### 1.2.4 查询数据 ####

mapper.xml:  
resultType：查询结果集返回类型

<select id="selectEmployee" parameterType="Integer" resultType="employee">
    	select * from employee where empid=#{empId}
    </select>

test测试文件：  
sqlSession.selectOne()：查询单个  
sqlSession.selectList()：查询多个

@Test
    	public void selectEmployee() throws IOException {
        
    		/* 读取配置文件 */
    		InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
    		/* 根据配置文件构建SqlSessionFactory会话工厂 */
    		SqlSessionFactory sqlSessionFactory =
     new SqlSessionFactoryBuilder().build(inputStream);
    		/* 通过SqlSessionFactory创建SqlSession */
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		/* SqlSession执行映射文件中自定义的SQL，并返回映射结果 */
    		try {
        
    			Employee employee = 
    sqlSession.selectOne("com.zwh.mapper.EmployeeMapper.selectEmployee", 1);
    			System.out.println(employee);
    		} finally {
        
    			/*关闭SqlSession*/
    			sqlSession.close();
    		}
    	}

### 1.3配置mybatis核心配置文件 ###

<?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>
        
    	<typeAliases>
           
            <typeAlias alias="employee" type="com.zwh.pojo.Employee"/>
            
    		<package name="com.zwh.pojo"/>
    	</typeAliases>
    	
    	<environments default="mysql">
    	
    		<environment id="mysql">
    		
    			<transactionManager type="JDBC"></transactionManager>
    			
    			<dataSource type="POOLED">
    				<property name="driver" value="com.mysql.jdbc.Driver" />
    				<property name="url"
    					value="jdbc:mysql://127.0.0.1:3306/mybatisdb?characterEncoding=utf-8" />
    				<property name="username" value="root" />
    				<property name="password" value="123456" />
    			</dataSource>
    		</environment>
    	</environments>
    	
    	<mappers>
    		<mapper resource="com/zwh/mapper/EmployeeMapper.xml" />
    	</mappers>
    </configuration>

## 2. 接口实现 ##

### 2.1 创建接口 ###

public interface EmployeeDao {
        
    	public Employee selectEmployee(int empid);
    }

### 2.2 创建mapper映射文件 ###

namespace名称空间,填写对应接口全限定名  
由于非接口方式的sql块代码和接口方式的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">
    <mapper namespace="com.zwh.dao.EmployeeDao">
    
    <select id="selectEmployee" parameterType="Integer" resultType="employee">
    	select * from employee where empid=#{empid}
    </select>
    </mapper>

### 2.3 配置mybatis核心配置文件 ###

mybatis核心配置文件中添加EmployeeDaoMapper.xml映射文件地址

### 2.4 test测试 ###

@Test
    	public void selectEmployee() {
        
    		SqlSession sqlSession = MybatisUtils.getSession();
    		EmployeeDao mapper = sqlSession.getMapper(EmployeeDao.class);
    		Employee  employee = mapper.selectEmployee(1);
    		sqlSession.close();
    	}

## 3. mybatisUtil工具类（减少重复代码编写） ##

public class MybatisUtils {
        
    	private static SqlSessionFactory sqlSessionFactory=null;
    	static {
        
    		try {
        
    			InputStream inputStream = 
    Resources.getResourceAsStream("mybatis-config.xml");
    			sqlSessionFactory= new SqlSessionFactoryBuilder().build(inputStream);
    		}catch(IOException e) {
        
    			e.printStackTrace();
    		}
    	}
    	
    	public static SqlSession getSession() {
        
    		return sqlSessionFactory.openSession();
    	}