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(); } }