MyBatis(一)实例二-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41532872/article/details/88140557

本文详细介绍了MyBatis框架的配置流程，包括db.properties、log4j.properties及mybatis.cfg.xml等文件的设置，解析了如何通过Mapper接口与XML映射文件实现数据库操作，涵盖了插入、更新、删除、查询等功能，并展示了如何利用MyBatis进行分页查询和参数绑定。

db.properties

#key=value
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis_db
username=root
password=tiger

log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
# File output...
# log4j.appender.stdout=org.apache.log4j.FileAppender
# log4j.appender.stdout.File=log.txt
# log4j.appender.stdout.Append=true
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} [%p] [%t] - %m%n

mybatis.cfg.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文件 -->
  <properties resource="db.properties"></properties>
  <!-- 配置类型别名 -->
  <typeAliases>
      <!-- typeAlias指定一个类的别名 type:类的全限定名 alias:别名   -->
   <!--   <typeAlias type="com.sxt.domain.Employee"   alias="emp"/>  -->
   <!-- package为某个包下所有的类批量配置别名，默认别名就是类名本身(不区分大小写) -->
   <package name="com.sxt.domain"/>
  </typeAliases>
	<!-- 配置当前数据库环境（信息），default用来指定当前默认的数据库环境 -->
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理器控制事务 -->
			<transactionManager type="JDBC" />
			<!-- 配置数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- 加载sql映射文件的三种方式
	   1.通过mapper标签的resource属性来加载，值是Mapper.xml的物理路径
	   2.通过mapper标签的class属性来加载，值是Mapper接口的全限定名,
	   前提是Mapper接口和Mapper.xml要在同一级包下且文件名要相同,
	   mybatis会加载与Mapper接口在同一包下且名字相同的Mapper.xml
	   3.通过package标签来加载，package是指定Mapper接口所在的包名称,mybatis会自动扫描该包下所有Mapper接口
	   并加载映射文件，前提条件和方式2相同，即Mapper接口和Mapper.xml要在同一级包下且文件名要相同
	 -->
    <mappers>
		<!-- <mapper resource="com/sxt/mapper/EmployeeMapper.xml" /> -->
	<!-- 	<mapper class="com.sxt.mapper.EmployeeMapper"/> -->
	      <package name="com.sxt.mapper"/>
	</mappers>
</configuration>

domain

package com.sxt.domain;

/**
 * 员工实体类
 * 
 * @author lujun
 * 
 */
public class Employee {
	private Integer eid;
	private String ename;
	private String sex;
	private Integer salary;

	public Integer getEid() {
		return eid;
	}

	public void setEid(Integer eid) {
		this.eid = eid;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Integer getSalary() {
		return salary;
	}

	public void setSalary(Integer salary) {
		this.salary = salary;
	}

	public Employee(String ename, String sex, Integer salary) {
		super();
		this.ename = ename;
		this.sex = sex;
		this.salary = salary;
	}

	public Employee() {
		super();
		// TODO Auto-generated constructor stub
	}

	@Override
	public String toString() {
		return "Employee [eid=" + eid + ", ename=" + ename + ", sex=" + sex
				+ ", salary=" + salary + "]";
	}

}

mapper

package com.sxt.mapper;

import java.util.List;

import com.sxt.domain.Employee;

/**
 * Mapper接口，相当于传统开发的DAO接口
 * @author lujun
 *
 */
public interface EmployeeMapper {
	   //插入
		int  insert(Employee  emp);
		
		//修改
		int update(Employee  emp);
		
		//删除
		int delete(int  eid);
		//查询单个记录
		Employee  queryById(int eid);
		//全查询
		List<Employee> queryAll();	
		//查询指定性别，且工资大于等于指定参数的员工信息
		List<Employee>  queryByCondition(String sex,int salary);
		
		//分页查询查指定页号的数据集合,参数1：要查询那一页的首行的索引  参数2：每页条数
		List<Employee> queryData(int startIndex,int pageSize);
		//查询总记录数,省略
		
}

<?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.sxt.mapper.EmployeeMapper">
  <!-- 
        DAO层代理开发要满足四个规范:
         1.Mapper接口的全限定名和Mapper.xml的namespace一致
         2.Mapper接口的方法名和Mapper.xml的statment的id一致(不是完整唯一标识)
         3.Mapper接口的方法的参数类型和Mapper.xml的statment的parameterType一致
         4.Mapper接口的方法的返回值类型和Mapper.xml的statment的resultType一致
   -->

   <insert id="insert"  parameterType="employee"  useGeneratedKeys="true">
  insert into t_employee(ename,sex,salary) values(#{ename},#{sex},#{salary})
                                                          
      <!--    insert into t_employee(ename,sex,salary) values('${ename}','${sex}',${salary}) -->
   </insert>
   <update id="update" parameterType="com.sxt.domain.Employee">
      update t_employee set ename=#{ename},sex=#{sex},salary=#{salary} where  eid=#{eid}
   </update>
  
     <delete id="delete"  parameterType="int">
        delete from t_employee where eid=#{sid}
     </delete>
     
     <!-- 定义sql片段,里面内容就是多个SQL语句共同的部分 -->
     <sql id="empSql"> eid,ename,salary</sql>
     <select id="queryById"  resultType="Employee">
    <!--    select *  from t_employee where eid=#{sid} -->
       <!-- include标签：引用sql片段,refid="sql片段的id" -->
        select   <include refid="empSql"></include>   from t_employee where eid=${value}
     </select>
     
     <select id="queryAll"  resultType="employee">
       select  <include refid="empSql"></include>   from t_employee
     </select>
     <!-- 
     多个简单参数的绑定：
     MyBatis会构造一个Map,把实参作为value，用param1,param2..作为key存到Map中
 类似于：map.put(“param1”,"男");
map.put(“param2”,7500);
  在Mapper.xml文件中通过#{key}来获取Map中的参数值，例如#{param1}绑定参数1的值，#{param2}绑定参数2的值
      -->
   <select id="queryByCondition" resultType="com.sxt.domain.Employee">
     select *  from t_employee  where sex=#{param1} and salary>=#{param2}
     </select> 
  <select id="queryData"  resultType="com.sxt.domain.Employee">
  select *  from t_employee limit  #{param1},#{param2}
 </select>

  
</mapper>

test

package com.sxt.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.sxt.domain.Card;
import com.sxt.domain.Employee;
import com.sxt.mapper.CardMapper;
import com.sxt.mapper.EmployeeMapper;
import com.sxt.util.MyBatisUtil;

public class MyBatisCrudTest {
	@Test
	public void test1() {
		// 获取SqlSession
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		// 获取Mapper接口的实现类对象,是mybatis通过JDK动态代理产生的代理对象
		EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

		// System.out.println(mapper.getClass());
		Employee emp = new Employee("赵敏", "女", 6000);

		mapper.insert(emp);
		// 提交事务
		sqlSession.commit();
		sqlSession.close();
	}

	// 测试使用Mapper接口完成查询操作
	@Test
	public void test2() {
		// 获取SqlSession
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		// 获取Mapper接口的实现类对象,是mybatis通过JDK动态代理产生的代理对象
		EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

		List<Employee> list = mapper.queryAll();
		for (Employee employee : list) {
			System.out.println(employee);
		}
		sqlSession.close();
	}

	// 测试方法有多个简单参数时，占位符的绑定
	@Test
	public void test3() {
		// 获取SqlSession
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		// 获取Mapper接口的实现类对象,是mybatis通过JDK动态代理产生的代理对象
		EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
		List<Employee> list = mapper.queryByCondition("男", 7500);
		for (Employee employee : list) {
			System.out.println(employee);
		}
		sqlSession.close();
	}
	//测试使用resultMap封装结果集
	@Test
	public void test4() {
		SqlSession sqlSession = MyBatisUtil.getSqlSession();
		CardMapper cardMapper = sqlSession.getMapper(CardMapper.class);
		List<Card> list = cardMapper.queryAll();
		for (Card card : list) {
			System.out.println(card);
		}
		sqlSession.close();
	}
	//测试分页查询
		@Test
		public void test5() {
			SqlSession sqlSession = MyBatisUtil.getSqlSession();
			EmployeeMapper  empMapper=sqlSession.getMapper(EmployeeMapper.class);
			//分页参数1：要查询的页号
			int currentPage=2;
			//分页参数2：每页条数
			int pageSize=4;
			//计算当前页首行的索引
			int startIndex=(currentPage-1)*pageSize;
			List<Employee> emplist = empMapper.queryData(startIndex, pageSize);
			for (Employee emp : emplist) {
				System.out.println(emp);
			}
			sqlSession.close();
		}
}

util

package com.sxt.util;

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;

/**
 * mybatis工具类
 * 
 * @author lujun
 * 
 */
public class MyBatisUtil {

	private static SqlSessionFactory sqlSessionFactory;
	static {
		String resource = "mybatis.cfg.xml";
		InputStream inputStream = null;
		// 加载主配置文件
		try {
			inputStream = Resources.getResourceAsStream(resource);
			sqlSessionFactory = new SqlSessionFactoryBuilder()
					.build(inputStream);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static SqlSession getSqlSession() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		return sqlSession;
	}
}