1 Mybatis映射文件--增删改查
- POJO类
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
@Alias("emp")
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", gender='" + gender + '\'' +
", email='" + email + '\'' +
'}';
}
}
- EmployeeMapper.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.demo1.IEmployeeDAO">
<!--
namespace：命名空间 指定为接口的全类名
id:唯一标识 和接口中的方法名相同
resultType：返回值类型
-->
<select id="findById" parameterType="int" resultType="emp" databaseId="mysql">
select id,last_name,email,gender from employee where id = #{id}
</select>
<insert id="addEmployee" parameterType="cn.demo1.Employee">
insert into employee(last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
<update id="updateEmployee" parameterType="cn.demo1.Employee">
update employee set last_name = #{lastName} ,email = #{email},gender = #{gender} where id = #{id}
</update>
<delete id="deleteEmployeeById" parameterType="java.lang.Integer">
delete from employee where id = #{id}
</delete>
</mapper>
- IEmployeeDAO
package cn.demo1;
public interface IEmployeeDAO {
public Employee findById(Integer id);
public void addEmployee(Employee employee);
public void updateEmployee(Employee employee);
public void deleteEmployeeById(Integer id);
}
- 配置文件--log4j.properties
### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c\:mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### set log levels - for more verbose logging change 'info' to 'debug' ###
log4j.rootLogger=debug, stdout
- 配置文件--db.properties
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis jdbc.user=root jdbc.password=root
- 配置文件--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">
<configuration>
<!--
Mybatis可以使用properties属性来引入外部properties配置文件内容
resource:引入类路径下的资源
url:引入网络路径或磁盘路径下的资源
-->
<properties resource="db.properties"></properties>
<settings>
<!--
是否开启自动驼峰规则映射，即如果数据表中的字段是last_name到Java中的lastName等类似的映射
-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--
typeAliases:别名处理器，可以为Java类型起别名
-->
<typeAliases>
<!--
类很多情况下，可以批量设置别名这个包下的每一个类创建一个默认的别名，就是简单类名小写。
-->
<package name="cn.demo1"/>
</typeAliases>
<!--
environments ，Mybatis可以配置多种环境
environment，配置一个具体的环境信息，必须有两个标签，ID表示当前环境的唯一标识
transactionManager s事务管理器
type 事务管理器的类型
JDBC this.typeAliasRegistry.registerAlias("JDBC", JdbcTransactionFactory.class);
MANAGER this.typeAliasRegistry.registerAlias("MANAGED", ManagedTransactionFactory.class);
dataSource
JNDI this.typeAliasRegistry.registerAlias("JNDI", JndiDataSourceFactory.class);
POOLED this.typeAliasRegistry.registerAlias("POOLED", PooledDataSourceFactory.class);
UNPOOLED this.typeAliasRegistry.registerAlias("UNPOOLED", UnpooledDataSourceFactory.class);
-->
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
</mappers>
</configuration>
- 测试类
package cn.test;
import cn.demo1.Employee;
import cn.demo1.IEmployeeDAO;
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.Test;
import java.io.IOException;
import java.io.InputStream;
/**
*
* 描述：
*/
public class MybatisTest {
/**
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = dao.findById(1);
System.out.print(employee);
session.close();
}
/**
* 测试增加
* @throws IOException
*/
@Test
public void demo2() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = new Employee();
employee.setLastName("呵呵");
employee.setGender("女");
employee.setEmail("aaa@aaa.com");
dao.addEmployee(employee);
session.commit();
session.close();
}
/**
* 测试修改
* @throws IOException
*/
@Test
public void demo3() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = new Employee();
employee.setId(1);
employee.setLastName("呵呵");
employee.setGender("女");
employee.setEmail("aaa@aaa.com");
dao.updateEmployee(employee);
session.commit();
session.close();
}
/**
* 测试修改
* @throws IOException
*/
@Test
public void demo4() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
dao.deleteEmployeeById(1);
session.commit();
session.close();
}
}
2. 主键生成方式
2.1 若数据库支持自动生成主键的字段（比如MySQL和SQLServer），则可以设置unGeneratedKeys="true"，然后再把keyProperty设置到目标属性上。
<insert id="addEmployee" parameterType="cn.demo1.Employee" useGeneratedKeys="true" keyProperty="id">
insert into employee(last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
@Alias("emp")
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", gender='" + gender + '\'' +
", email='" + email + '\'' +
'}';
}
}
package cn.demo1;
public interface IEmployeeDAO {
public Employee findById(Integer id);
public void addEmployee(Employee employee);
public void updateEmployee(Employee employee);
public void deleteEmployeeById(Integer id);
}
<?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.demo1.IEmployeeDAO">
<!--
namespace：命名空间 指定为接口的全类名
id:唯一标识 和接口中的方法名相同
resultType：返回值类型
-->
<select id="findById" parameterType="int" resultType="emp" databaseId="mysql">
select id,last_name,email,gender from employee where id = #{id}
</select>
<insert id="addEmployee" parameterType="cn.demo1.Employee" useGeneratedKeys="true" keyProperty="id">
insert into employee(last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
<update id="updateEmployee" parameterType="cn.demo1.Employee">
update employee set last_name = #{lastName} ,email = #{email},gender = #{gender} where id = #{id}
</update>
<delete id="deleteEmployeeById" parameterType="java.lang.Integer">
delete from employee where id = #{id}
</delete>
</mapper>
<?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>
<!--
Mybatis可以使用properties属性来引入外部properties配置文件内容
resource:引入类路径下的资源
url:引入网络路径或磁盘路径下的资源
-->
<properties resource="db.properties"></properties>
<settings>
<!--
是否开启自动驼峰规则映射，即如果数据表中的字段是last_name到Java中的lastName等类似的映射
-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--
typeAliases:别名处理器，可以为Java类型起别名
-->
<typeAliases>
<!--
类很多情况下，可以批量设置别名这个包下的每一个类创建一个默认的别名，就是简单类名小写。
-->
<package name="cn.demo1"/>
</typeAliases>
<!--
environments ，Mybatis可以配置多种环境
environment，配置一个具体的环境信息，必须有两个标签，ID表示当前环境的唯一标识
transactionManager s事务管理器
type 事务管理器的类型
JDBC this.typeAliasRegistry.registerAlias("JDBC", JdbcTransactionFactory.class);
MANAGER this.typeAliasRegistry.registerAlias("MANAGED", ManagedTransactionFactory.class);
dataSource
JNDI this.typeAliasRegistry.registerAlias("JNDI", JndiDataSourceFactory.class);
POOLED this.typeAliasRegistry.registerAlias("POOLED", PooledDataSourceFactory.class);
UNPOOLED this.typeAliasRegistry.registerAlias("UNPOOLED", UnpooledDataSourceFactory.class);
-->
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
</mappers>
</configuration>
/**
* 测试增加
* @throws IOException
*/
@Test
public void demo2() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = new Employee();
employee.setLastName("呵呵");
employee.setGender("女");
employee.setEmail("aaa@aaa.com");
dao.addEmployee(employee);
System.out.print(employee.getId());
session.commit();
session.close();
}
2.2 对于不支持自增主键的数据库，则可以使用selectKey子元素；selectKey元素将会首先运行，id会被设置，后插入语句会被调用。
<insert id="addEmployee" parameterType="cn.demo1.Employee" databaseId="oracle">
<selectKey order="BEFORE" keyProperty="id" resultType="java.lang.Integer">
select employee_seq.nextval from dual;
</selectKey>
insert into employee(id,last_name,email,gender) values (#{id},#{lastName},#{email},#{gender})
</insert>
3 参数传递
-
单个参数：
- 可以接受基本类型，对象类型，集合类型的值。这种情况可直接使用这个参数，不需要经过任何处理。
- 多个参数：
- 任意多个参数，都会被Mybatis重新包装成一个Map传入。Map的key是param1，param2,0,1...值就是参数的值。
-
命名参数：
- 为参数使用@param起一个名字，Mybatis就会将这些参数封装到map中，key就是我们自己指定的名字。
- POJO：
- 当这些参数属于业务的POJO的时候，我们直接传递POJO。#{属性值}中的属性值为POJO的属性值。
- Map：
- 我们也可以封装多个参数为map，直接传递。
- 注意：
- 如果是Collection类型的话，那么key是collection。
- 如果是List类型的话，那么key是list。
- 如果是数组的话，那么key是array。
- #{key}：获取参数的值，预编译到SQL中。安全。${key}：获取参数的值，拼接到SQL中，有SQL注入问题。
- #{}：更丰富的用法
- jdbcType：通常需要在某种特定的条件下设置
- 当我们使用的数据为null的时候，有些数据库不能识别mybatis对null的默认处理，比如Oracle。
- mybatis对所有的null默认映射的是OTHER，Oracle不支持。
- 解决方案：
- #{email，jdbcType=NULL}
- 在全局配置文件中配置jdbcTypeForNUll=NULL.
4. select元素
- select元素来定义查询操作。
- id：唯一标识符。
- 用来引用这条语句，需要和接口的方法名一致。
- parameterType：参数类型
- 可以不传，Mybatis会根据TypeHandler自动推断。
- resultType：返回值类型
- 别名或者全类名，如果返回的是集合，定义集合中元素的类型。不能和resultMap同时使用。
4.1 resultType：返回的是List集合
public List<Employee> findAll();
<select id="findAll" resultType="cn.demo1.Employee">
select * from employee
</select>
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
List<Employee> employees = dao.findAll();
System.out.print(employees);
session.close();
}
4.2 resultType：返回的是Map集合---返回的是一条记录
- 如果返回的是Map集合，那么Map集合的key是列名，而value是key对应的值
//key就是列名 值是对应的值
public Map<String,Object> findEmployeeById(Integer id);
<select id="findEmployeeById" resultType="map">
select * from employee where id = #{id}
</select>
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Map<String, Object> employee = dao.findEmployeeById(5);
System.out.print(employee);
session.close();
}
4.3 resultType：返回的是Map集合---返回的是多条记录
//key是这条记录的主键，value是记录封装后的JavaBean
@MapKey("id")
public Map<Integer,Employee> findEmployeeLikeName(String name);
<select id="findEmployeeLikeName" resultType="cn.demo1.Employee">
select * from Employee where last_name like '%${value}%'
</select>
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Map<Integer, Employee> map = dao.findEmployeeLikeName("呵");
System.out.print(map);
session.close();
}
4.4 resultMap--自定义结果映射规则
主要代码：
public Employee getEmployeeById(Integer id);
<!--
自定义某个Javabean的封装规则
type：自定义规则的Javabean
id:唯一id方便引用
-->
<resultMap id="emp" type="cn.demo1.Employee">
<!--
指定主键列的封装规则
id定义主键
cloumn 指定那一列
property：指定对应的Javabean属性
result定义普通列封装规则
-->
<id property="id" column="id"/>
<result column="last_name" property="lastName"></result>
<result column="gender" property="gender"></result>
<result column="email" property="email"></result>
</resultMap>
<select id="getEmployeeById" resultMap="emp">
select * from employee where id = #{id}
</select>
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = dao.getEmployeeById(5);
System.out.print(employee);
session.close();
}
全部代码：
- POJO
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", gender='" + gender + '\'' +
", email='" + email + '\'' +
'}';
}
}
- 接口
package cn.demo1;
import org.apache.ibatis.annotations.MapKey;
import java.util.List;
import java.util.Map;
public interface IEmployeeDAO {
public Employee getEmployeeById(Integer id);
}
- 映射文件
<?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.demo1.IEmployeeDAO">
<!--
自定义某个Javabean的封装规则
type：自定义规则的Javabean
id:唯一id方便引用
-->
<resultMap id="emp" type="cn.demo1.Employee">
<!--
指定主键列的封装规则
id定义主键
cloumn 指定那一列
property：指定对应的Javabean属性
result定义普通列封装规则
-->
<id property="id" column="id"/>
<result column="last_name" property="lastName"></result>
<result column="gender" property="gender"></result>
<result column="email" property="email"></result>
</resultMap>
<select id="getEmployeeById" resultMap="emp">
select * from employee where id = #{id}
</select>
</mapper>
- 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>
<settings>
<!--
是否开启自动驼峰规则映射，即如果数据表中的字段是last_name到Java中的lastName等类似的映射
-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
</mappers>
</configuration>
- 测试
package cn.test;
import cn.demo1.Employee;
import cn.demo1.IEmployeeDAO;
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.Test;
import sun.font.LayoutPathImpl;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
/**
*
* 描述：
*/
public class MybatisTest {
/**
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = dao.getEmployeeById(5);
System.out.print(employee);
session.close();
}
}
4.5 resultMap--关联查询--级联属性封装结果集
SQL：
CREATE DATABASE mybatis;
USER DATABASE mybatis;
CREATE TABLE employee(
id ) PRIMARY KEY AUTO_INCREMENT,
last_name ),
gender ),
email )
);
CREATE TABLE department(
id ) PRIMARY KEY AUTO_INCREMENT,
department_name )
)
);
ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(id);
- POJO
package cn.demo1;
public class Department {
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", departmentName='" + departmentName + '\'' +
'}';
}
}
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
private Department department;
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"email='" + email + '\'' +
", gender='" + gender + '\'' +
", lastName='" + lastName + '\'' +
", id=" + id +
'}';
}
}
- 映射文件 -- EmployeeMapper.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.demo1.IEmployeeDAO">
<resultMap id="mybatis" type="cn.demo1.Employee">
<id property="id" column="id"/>
<result property="lastName" column="last_name"/>
<result property="email" column="email"/>
<result property="gender" column="gender"/>
<result property="department.id" column="dept_id"/>
<result property="department.departmentName" column="department_name"/>
</resultMap>
<select id="getEmployeeAndDepartmentById" resultMap="mybatis">
SELECT e.id id,e.last_name last_name ,e.email email,e.gender gender,e.dept_id dept_id ,d.department_name department_name FROM employee e , department d WHERE e.dept_id = d.id AND e.id = #{id} ;
</select>
</mapper>
- 接口--IEmployeeDAO
package cn.demo1;
import org.apache.ibatis.annotations.MapKey;
import java.util.List;
import java.util.Map;
public interface IEmployeeDAO {
public Employee getEmployeeAndDepartmentById(Integer id);
}
- db.properties
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8 jdbc.user=root jdbc.password=root
- 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>
<!--
<settings>
<!–
是否开启自动驼峰规则映射，即如果数据表中的字段是last_name到Java中的lastName等类似的映射
–>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>-->
<properties resource="db.properties"/>
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<!--<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>-->
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
</mappers>
</configuration>
- 测试
package cn.test;
import cn.demo1.Employee;
import cn.demo1.IEmployeeDAO;
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.Test;
import java.io.IOException;
import java.io.InputStream;
/**
*
* 描述：
*/
public class MybatisTest {
/**
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = dao.getEmployeeAndDepartmentById(6);
System.out.print(employee);
System.out.print(employee.getDepartment());
session.close();
}
}
4.6 resultMap--关联查询--association定义关联对象的封装规则
<resultMap id="mybatis2" type="cn.demo1.Employee">
<id property="id" column="id"/>
<result property="lastName" column="last_name"/>
<result property="email" column="email"/>
<result property="gender" column="gender"/>
<!--
association可以指定联合的JavaBean对象
property:指定那个属性是联合对象
javaType:指定这个属性对象的类型
-->
<association property="department" javaType="cn.demo1.Department">
<id column="dept_id" property="id"/>
<result column="department_name" property="departmentName"/>
</association>
</resultMap>
4.6 resultMap--关联查询--association进行分布查询
- POJO
package cn.demo1;
public class Department {
private Integer id;
private String departmentName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", departmentName='" + departmentName + '\'' +
'}';
}
}
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
private Department department;
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"email='" + email + '\'' +
", gender='" + gender + '\'' +
", lastName='" + lastName + '\'' +
", id=" + id +
'}';
}
}
- 接口
package cn.demo1;
public interface IDepartmentDAO {
public Department getDepartmentById(Integer id);
}
package cn.demo1;
import org.apache.ibatis.annotations.MapKey;
import java.util.List;
import java.util.Map;
public interface IEmployeeDAO {
public Employee getEmployeeByStep(Integer id);
}
- 映射文件--DepartmentMapper.xml和EmployeeMapper.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.demo1.IDepartmentDAO">
<resultMap id="department" type="cn.demo1.Department">
<id column="id" property="id"></id>
<result column="department_name" property="departmentName"/>
</resultMap>
<select id="getDepartmentById" resultMap="department">
select id ,department_name from department where id = #{id}
</select>
</mapper>
<?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.demo1.IEmployeeDAO">
<!--
使用association进行分布查询
先按照员工Id查询员工信息
根据员工信息中的dept_id的值去部门表查询部门信息
将部门设置到员工中
-->
<resultMap id="emp" type="cn.demo1.Employee">
<id property="id" column="id"/>
<result property="lastName" column="last_name"/>
<result property="email" column="email"/>
<result property="gender" column="gender"/>
<!--
定义关联对象的封装规则
-->
<association property="department" select="cn.demo1.IDepartmentDAO.getDepartmentById" column="dept_id">
</association>
</resultMap>
<select id="getEmployeeByStep" resultMap="emp">
select id,last_name,email,gender,dept_id from employee where id = #{id}
</select>
</mapper>
- db.properties
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8 jdbc.user=root jdbc.password=root
- 核心配置文件
<?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>
<!--
<settings>
<!–
是否开启自动驼峰规则映射，即如果数据表中的字段是last_name到Java中的lastName等类似的映射
–>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>-->
<properties resource="db.properties"/>
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<!--<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>-->
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
<mapper resource="cn/demo1/DepartmentMapper.xml"/>
</mappers>
</configuration>
- 测试类
package cn.test;
import cn.demo1.Employee;
import cn.demo1.IEmployeeDAO;
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.Test;
import java.io.IOException;
import java.io.InputStream;
/**
*
* 描述：
*/
public class MybatisTest {
/**
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IEmployeeDAO dao = session.getMapper(IEmployeeDAO.class);
Employee employee = dao.getEmployeeByStep(6);
System.out.print(employee);
System.out.print(employee.getDepartment());
session.close();
}
}
4.6 resultMap--关联查询--association进行分布查询及延迟加载
- 开启延迟加载和属性按需加载
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
4.7 resultMap--关联查询--collection定义关联集合封装规则
- POJO
package cn.demo1;
import java.util.List;
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", departmentName='" + departmentName + '\'' +
'}';
}
}
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
private Department department;
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"email='" + email + '\'' +
", gender='" + gender + '\'' +
", lastName='" + lastName + '\'' +
", id=" + id +
'}';
}
}
- 接口
package cn.demo1;
public interface IDepartmentDAO {
public Department getDepartmentAndEmployeeById(Integer id);
}
- 映射文件
<?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.demo1.IDepartmentDAO">
<resultMap id="department" type="cn.demo1.Department">
<id column="id" property="id"></id>
<result column="department_name" property="departmentName"/>
<!--
collection定义关联集合类型的属性的封装规则
ofType：指定集合里面元素的类型
-->
<collection property="emps" ofType="cn.demo1.Employee">
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<select id="getDepartmentAndEmployeeById" resultMap="department">
SELECT e.id eid,e.last_name last_name ,e.email email,e.gender gender,d.id id ,d.department_name department_name FROM employee e , department d WHERE e.dept_id = d.id AND d.id = #{id}
</select>
</mapper>
- 核心配置文件
<?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="db.properties"/>
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<!--<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>-->
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
<mapper resource="cn/demo1/DepartmentMapper.xml"/>
</mappers>
</configuration>
- 测试
package cn.test;
import cn.demo1.Department;
import cn.demo1.Employee;
import cn.demo1.IDepartmentDAO;
import cn.demo1.IEmployeeDAO;
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.Test;
import java.io.IOException;
import java.io.InputStream;
/**
*
* 描述：
*/
public class MybatisTest {
/**
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IDepartmentDAO dao = session.getMapper(IDepartmentDAO.class);
Department department = dao.getDepartmentAndEmployeeById(1);
System.out.print(department);
System.out.print(department.getEmps());
session.close();
}
}
4.7 resultMap--关联查询--collection进行分布查询
- POJO
package cn.demo1;
import java.util.List;
public class Department {
private Integer id;
private String departmentName;
private List<Employee> emps;
public List<Employee> getEmps() {
return emps;
}
public void setEmps(List<Employee> emps) {
this.emps = emps;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", departmentName='" + departmentName + '\'' +
'}';
}
}
package cn.demo1;
import org.apache.ibatis.type.Alias;
/**
* 描述：POJO
*/
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
private Department department;
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Employee{" +
"email='" + email + '\'' +
", gender='" + gender + '\'' +
", lastName='" + lastName + '\'' +
", id=" + id +
'}';
}
}
- 接口
package cn.demo1;
public interface IDepartmentDAO {
public Department getDepartmentByIdStep(Integer id);
}
package cn.demo1;
import org.apache.ibatis.annotations.MapKey;
import java.util.List;
import java.util.Map;
public interface IEmployeeDAO {
public List<Employee> getEmployeesByDeptId(Integer id);
}
- 映射文件
<?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.demo1.IEmployeeDAO">
<resultMap id="emp" type="cn.demo1.Employee">
<id property="id" column="id"/>
<result property="lastName" column="last_name"/>
<result property="email" column="email"/>
<result property="gender" column="gender"/>
</resultMap>
<select id="getEmployeesByDeptId" resultMap="emp">
select * from employee where dept_id = #{id}
</select>
</mapper>
<?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.demo1.IDepartmentDAO">
<resultMap id="department" type="cn.demo1.Department">
<id column="id" property="id"></id>
<result column="department_name" property="departmentName"/>
<collection property="emps" select="cn.demo1.IEmployeeDAO.getEmployeesByDeptId" column="id"/>
</resultMap>
<select id="getDepartmentByIdStep" resultMap="department" >
select id,department_name from department where id = #{id}
</select>
</mapper>
- 核心配置文件
<?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="db.properties"/>
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<environments default="mysql_development">
<environment id="mysql_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<!--<environment id="oracle_development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"/>
<property name="url" value="${oracle.url}"/>
<property name="username" value="${oracle.user}"/>
<property name="password" value="${oracle.password}"/>
</dataSource>
</environment>-->
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<mappers>
<mapper resource="cn/demo1/EmployeeMapper.xml"/>
<mapper resource="cn/demo1/DepartmentMapper.xml"/>
</mappers>
</configuration>
- 测试
package cn.test;
import cn.demo1.Department;
import cn.demo1.Employee;
import cn.demo1.IDepartmentDAO;
import cn.demo1.IEmployeeDAO;
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.Test;
import java.io.IOException;
import java.io.InputStream;
/**
*
* 描述：
*/
public class MybatisTest {
/**
* 测试查询
* @throws IOException
*/
@Test
public void demo1() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
IDepartmentDAO dao = session.getMapper(IDepartmentDAO.class);
Department department = dao.getDepartmentByIdStep(1);
System.out.print(department);
System.out.print(department.getEmps());
session.close();
}
}