- 所需jar包 mysql-connector-java-5.1.0-bin.jar mybatis-3.2.2-sources.jar mybatis-3.2.2.jar log4j-1.2.17.jar
- 实体类 cn.pojo
- 业务逻辑实现层 cn.impl
- MyBatisUtils.java
-
/** * */ package cn.dao.impl; 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; /** * 公共类 * 1、用于获取sqlSession对象 * 2、用于关闭sqlSession对象 */ public class MyBatisUtils { static SqlSessionFactory factory = null; static{ try { //1、将读取的配置文件信息序列化流 InputStream is = Resources.getResourceAsStream("mybatis-config.xml"); //2、创建SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * 创建sqlSession对象 * @return */ public static SqlSession openSqlSession(){ return factory.openSession(); } /** * 关闭sqlSession资源 * @param sqlSession */ public static void closeSqlSession(SqlSession sqlSession){ sqlSession.close(); } }
2.xxxxMapper
a.xxxxMapper.java 数据访问层接口
b.xxxxMapper.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="cn.dao.impl.student.StudentMapper">
<!--
<cache eviction="FIFO" flushInterval="60000"
size="512" readOnly="true"/>
-->
<!-- 执行的SQL语句都在此写 -->
<!-- id:调用SQL语句的名称 必须唯一
resultType:返回的结果类型,基本类型,对象, 集合
parameterType:参数类型,只有一个参数[基本类型,对象,集合]
参数命名一定要和#{}里面的名称保持一致
-->
<select id="getStudentByGradeId" resultType="Student" parameterType="Integer" useCache="true">
select studentNo, studentName, gradeId from Student where gradeId=#{gId}
</select>
<select id="getStudentByName" resultType="Student" parameterType="String">
SELECT studentNo, studentName, gradeId FROM Student WHERE stuName LIKE concat('%',#{stuName},'%')
</select>
<!-- 多个值封装城对象后,要求对象的属性名必须和#{}里的参数名一致 -->
<select id="getStudentsByTJ" parameterType="Student" resultType="Student">
select studentNo, studentName, gradeId from Student where gradeId=#{gradeId} and stuName LIKE concat('%',#{studentName},'%')
</select>
<!-- 集合的key必须和#{}里的参数名一致! -->
<select id="getStudentsByTJ2" parameterType="Map" resultType="Student">
select studentNo, studentName, gradeId from Student where gradeId=#{gId} and stuName LIKE concat('%',#{sName},'%')
</select>
<!-- 注解里的参数名必须和#{}里的参数名一致! -->
<select id="getStudentsByTJ3" resultType="Student" >
select studentNo, studentName, gradeId from Student where gradeId=#{gId} and stuName LIKE concat('%',#{sName},'%')
</select>
<!-- 自己封装属性和列名的映射关系
当查询的数据库列名和属性名不一致时,就需要自定义封装
id的值写select标签的resultMap值,成对应关系
type写封装数据的类型,通常情况下写的都是当前xml映射文件对应的实体类
resultMap和resultType不能同时使用!
-->
<resultMap type="Student" id="stuInfo">
<result property="studentNo" column="stuNo"/>
<result property="studentName" column="stuName"/>
<result property="gradeId" column="gradeId"/>
</resultMap>
<!-- 注解里的参数名必须和#{}里的参数名一致! -->
<select id="getStudentsByTJ4" resultMap="stuInfo">
select stuNo, stuName, gradeId from Student where gradeId=#{gId} and stuName LIKE concat('%',#{sName},'%')
</select>
<resultMap type="Student" id="stuGradeInfo">
<result property="studentNo" column="studentNo"/>
<result property="studentName" column="studentName"/>
<result property="gradeId" column="gradeId"/>
<result property="gradeName" column="gradeName"/>
</resultMap>
<!-- 注解里的参数名必须和#{}里的参数名一致! -->
<select id="getStudentsByTJ5" resultMap="stuGradeInfo">
SELECT studentNo, studentName, grade.gradeId as gradeId, grade.gradeName as gradeName FROM Student, Grade
WHERE Grade.gradeId = Student.gradeId
</select>
<!-- 为映射查询封装数据
association:配置一的一方
property:配置多的一方中 封装一的一方的属性名
javaType:配置一的一方的完全限定名 [该属性只在配置一的一方中写]
id:封装数据,能够区分为主键,效率稍高
-->
<resultMap type="Student" id="StudentAndGrade">
<result property="studentNo" column="studentNo"/>
<result property="studentName" column="studentName"/>
<!-- 配置学生信息映射到年级信息 -->
<association property="grade" javaType="Grade">
<id property="gradeId" column="gradeId" />
<result property="gradeName" column="gradeName"/>
</association>
</resultMap>
<!-- 映射查询年级信息 -->
<select id="getAllStudents" resultMap="StudentAndGrade">
SELECT s.studentNo studentNo,s.studentName studentName,g.gradeId gradeId,g.gradeName gradeName
FROM Student s,Grade g WHERE s.gradeId=g.gradeId
</select>
<!-- 使用if动态生成SQL语句 使用where动态去除关键字 -->
<select id="useIfSelectStudent" parameterType="Student" resultType="Student">
select studentNo, studentName from Student
<where>
<if test="gradeId != null and gradeId !='' ">
gradeId=#{gradeId}
</if>
<if test="studentName != null and studentName != '' ">
and studentName LIKE concat('%',#{studentName},'%')
</if>
</where>
</select>
<!-- 使用if动态生成SQL语句 使用set去除关键字 -->
<!--
<update id="useSetUpdateStudent" parameterType="Student">
update Student
<set>
<if test="studentName != null and studentName != '' ">
studentName=#{studentName},
</if>
<if test="gradeId != null and gradeId !='' ">
gradeId=#{gradeId}
</if>
</set>
where studentNo=#{studentNo}
//推荐修改全部列
update Student set studentName=#{studentName},gradeId=#{gradeId} where studentNo=#{studentNo}
</update>
-->
<!-- 使用trim动态生成SQL语句 使用prefix -->
<select id="useTrimOperateSql1" parameterType="Student" resultType="Student">
<trim prefix="select studentNo, studentName, gradeId from Student where" prefixOverrides="and | or" suffixOverrides="and | or">
<if test="studentName != null and studentName != '' ">
studentName LIKE concat('%',#{studentName},'%')
</if>
<if test="gradeId != null and gradeId !='' ">
and gradeId=#{gradeId}
</if>
</trim>
</select>
<!-- 使用trim动态生成SQL语句 使用suffix -->
<update id="useTrimOperateSql2" parameterType="Student" >
<trim prefix="update Student set " suffix="where studentNo=#{studentNo}" suffixOverrides=",">
<if test="studentName != null and studentName != '' ">
studentName=#{studentName},
</if>
<if test="gradeId != null and gradeId !='' ">
gradeId=#{gradeId}
</if>
</trim>
</update>
<!-- 使用Forearch动态生成SQL语句
foreach只用于组合参数
collection用于区分参数类型:数组写array list集合写list map集合写key
items和参数名以及#{}里的参数名保持一致
open以(开始
close以)结束
separator将多个参数用,分号
-->
<select id="useForearchSql" resultType="Student">
select studentNo, studentName from Student where studentNo in
<foreach collection="array" item="stuNos" open="(" close=")" separator=",">
#{stuNos}
</foreach>
</select>
<select id="useForearchSql2" resultType="Student">
select studentNo, studentName from Student where studentNo in
<foreach collection="list" item="stuNos" open="(" close=")" separator=",">
#{stuNos}
</foreach>
</select>
<select id="useForearchSql3" resultType="Student">
select studentNo, studentName from Student where studentNo in
<foreach collection="mapKey" item="stuNos" open="(" close=")" separator=",">
#{stuNos}
</foreach>
</select>
</mapper>
- 核心配置文件 mybatis-config.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">
<!-- 配置项信息 mybatis的总文件-->
<configuration>
<!-- 配置数据库访问信息 -->
<!-- 方式一
<properties resource="database.properties"></properties>
-->
<!-- 方式二 -->
<properties>
<property name="driveName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/MySchool"/>
<property name="loginName" value="root"/>
<property name="loginPwd" value="root"/>
</properties>
<!-- 配置MyBatis的配置行为 -->
<settings>
<!-- 配置mybatis的log实现为LOG4J -->
<setting name="logImpl" value="LOG4J" />
<!-- 配置是否自动映射 默认PARTIAL自动映射 -->
<!-- <setting name="autoMappingBehavior" value="PARTIAL"/> -->
<!-- 配置二级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
<!-- 配置实体类别名 给XXXMapper.xml文件中的封装数据类型使用的-->
<typeAliases>
<!--
<typeAlias type="cn.bdqn.pojo.Grade" alias="Grade"/>
<typeAlias type="cn.pojo.Student" alias="Student"/>
-->
<package name="cn.pojo"/>
</typeAliases>
<!-- 运行环境 -->
<environments default="development">
<!-- 配置项 -->
<environment id="development">
<!-- 事务操作 -->
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED:mybatis自带的数据源,JNDI:基于tomcat的数据源
采用的是方式一:资源文件的key值
采用的是方式二:访问数据库资源的值写的是property的name名称
运行环境中的数据源的name是固定的
-->
<dataSource type="POOLED">
<property name="driver" value="${driveName}"/>
<property name="url" value="${url}"/>
<property name="username" value="${loginName}"/>
<property name="password" value="${loginPwd}"/>
</dataSource>
</environment>
</environments>
<!-- 将mapper文件加入到配置文件中 识别ORM配置信息 -->
<mappers>
<mapper resource="cn/dao/impl/grade/GradeMapper.xml"/>
<mapper resource="cn/dao/impl/student/StudentMapper.xml"/>
</mappers>
- 提供连接数据库的数据database.properties
-
#tigongshuju driveName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/MySchool loginName=root loginPwd=root
- 日志文件 log4j.properties
- 操作与调用
- 创建SqlSessionFactory对象,读取配置文件
- 创建SqlSession对象
- 调用mapper文件进行数据操作