MyBatis入门教程（一）-ORMpping技术|MyBatis的开发步骤|动态SQL|MyBatis元素|MyBatis实现1:1 以及 1:N关联关系|MyBatis****|综合案例（详细）
SSM-MyBatis
1、ORMapping技术，对象关系映射技术，将关系的操作（连接数据库，sql）转成对象的做，说白了就是要保存一个新闻信息的话，无需开发人员辨写SQL语句，只通过对象就可以将这个对象封装的数据保存在数据库中。
2、主流的ORMpping技术，Hibernate|MyBatis（iBatis），数据持久技术，所以呢HIbernate|MyBatis都属于数据持久层技术
3、MyBatis的开发步骤
①、去官网下载对应的版本
②、导入对应的jar包
aopalliance-1.0.jar
cglib-2.1.3.jar
commons-logging-1.1.1.jar
hamcrest-core-1.3.jar
junit-4.12.jar
log4j-1.2.17.jar
mybatis-3.2.8.jar
mybatis-spring-1.1.1.jar
mysql-connector-java-5.1.7-bin.jar
ojdbc14.jar
③、配置MyBatis的环境（可以告知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">
<!--
*.dtd:文档类型定义 约定了文档中有哪些元素，甚至有哪些属性，哪些元素在前那些元素在后，哪些元素只能出现一次等等
就是有提示功能
*.xsd:模式文件。模式文件和dtd文件功能一致，但是xsd文件是通过xml技术辨写的。
-->
<configuration>
<!-- 配置MyBatis的核心环境 一个项目可以连接多个不同的数据，可以在environments内配置多个environment-->
<environments default="development">
<environment id="development">
<!-- 数据库事务管理方式 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源 -->
<dataSource type="POOLED">
<!-- 指定数据源的相关参数 -->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>
</dataSource>
</environment>
</environments>
<!-- 配置MyBatis的映射文件路径 可以有多个mapper-->
<mappers>
<!-- 注意：这个里的路径使用/分割。 而不是一个全类名.()-->
<mapper resource="com/rock/pojo/PersonMapper.xml"/>
</mappers>
</configuration>
映射文件-----
<?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">
<!-- namespace:命名空间必须指定为全类名 -->
<mapper namespace="com.rock.dao.IPersonDao">
<!-- insert ：是完成信息保存用
包括 delete |update|insert|select===><> 每个元素都必须有一个id属性，这个id属性指定了dao接口中的方法名
-->
<insert id="savePerson" parameterType="com.rock.pojo.Person">
insert into person(pid,pname,paddr,age) values(#{pid},#{pname},#{paddr},#{age})
</insert>
<delete id="delPerson"></delete>
</mapper>
④、完成数据的CRUD操作（SqlSession）
package com.rock.test;
import java.io.IOException;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.rock.pojo.Person;
/**
* 利用JUnit完成测试
* @author Administrator
*
*/
public class PersonJUnit {
private SqlSession sqlSession;
@Before
public void init(){
try {
sqlSession=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testSavePerson(){
Person p=new Person("A0001","刘备","三国",40);
int a=sqlSession.insert("com.rock.dao.IPersonDao.savePerson",p);
System.out.println(a);
}
@Test
public void testDeletePerson(){
int a=sqlSession.delete("com.rock.dao.IPersonDao.delPerson","A0001");
System.out.println(a);
}
@After
public void destroy(){
System.out.println("功能测试完毕.............................");
}
}
实验：独立完成更新地址的功能
4、综合案例
CarMapper.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="com.rock.dao.CarMapper">
<!-- 自定义结果map -->
<resultMap type="Car" id="carResultMap">
<id property="cid" column="cid"/>
<result property="brand" column="brand"/>
<result property="maxspeed" column="maxspeed"/>
<result property="price" column="price"/>
</resultMap>
<resultMap type="Card" id="cardResultMap">
<id property="id" column="cid"/>
<result property="logo" column="brand"/>
<result property="mspeed" column="maxspeed"/>
<result property="price" column="price"/>
</resultMap>
<insert id="saveCar" parameterType="Car">
insert into car(cid,brand,maxspeed,price) values(#{cid},#{brand},#{maxspeed},#{price})
</insert>
<!--
resultType="Car":要求Car这个bean中的变量名和表的列名一致
若不一致，表中的数据将存不进这个bean对象中
-->
<select id="queryById" parameterType="string" resultType="Car">
select cid,brand,maxspeed,price from car where cid=#{cid}
</select>
<!--
当表中列名和POJO变量名不同的解决方案
①、通过ResultMap映射
②、通过在辨写sql语句时指定别名的方式，将别名和POJO类中的变量保持一致
-->
<select id="queryById" parameterType="string" resultMap="carResultMap">
select cid,brand,maxspeed,price from car where cid=#{cid}
</select>
<select id="queryCardById" parameterType="string" resultType="Card">
select cid id,brand logo,maxspeed mspeed,price from car where cid=#{cid}
</select>
</mapper>
PersonMapper.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">
<!-- namespace:命名空间必须指定为全类名 -->
<mapper namespace="com.rock.dao.IPersonDao">
<!-- insert ：是完成信息保存用
1、id属性：包括 delete |update|insert|select===><> 每个元素都必须有一个id属性，这个id属性指定了dao接口中的方法名
2、parameterType:指定方法的参数类型,,同时由于地层是通过发射机制创建的这个对象的实例，所以这里必须给定全名名
为了简化配置，所以我们经常在MyBatis的配置文件中给这写类型定义别名
<typeAliases>
<typeAlias type="com.rock.pojo.Person" alias="Person"/>
</typeAliases>
3、resultType:指定方法的返回值的类型
-->
<insert id="savePerson" parameterType="com.rock.pojo.Person">
insert into person(pid,pname,paddr,age) values(#{pid},#{pname},#{paddr},#{age})
</insert>
<!-- 删除员工信息 -->
<delete id="delPerson" parameterType="String">
delete from person where pid=#{pid}
</delete>
<!-- 更新员工信息 -->
<update id="updatePerson" parameterType="Person">
UPDATE person SET pname=#{pname},paddr=#{paddr},age=#{age} WHERE pid=#{pid}
</update>
<!-- 根据主键检索员工信息 -->
<select id="queryById" parameterType="String" resultType="Person">
select a.pid,a.pname,a.paddr,a.age from person a where a.pid=#{pid};
</select>
<!--
根据主键获取对应的Person数据，但是不是以JavaBean形式返回，而是以Map集合方式返回
Map中的key：对应了列名
Map中的value：对应了列的值
-->
<select id="queryPersion" parameterType="String" resultType="hashmap">
select * from person where pid=#{pid}
</select>
</mapper>
CarMapper.java
package com.rock.dao;
import java.util.List;
import java.util.Map;
import com.rock.pojo.Car;
import com.rock.pojo.Card;
public interface CarMapper {
public int saveCar(Car car);
public int deleteCar(String cid);
public int deleteCar(Car car);
public int updateCar(Car car);
public Car queryById(String cid);
public Car queryByCar(Car car);
public List<Car> queryAll();
public Map<String,Car> queryAllToMap();
public Card queryCardById(String id);
}
IPersonDao.java
package com.rock.dao;
import java.util.Map;
import com.rock.pojo.Person;
public interface IPersonDao {
/**
* TODO:完成person的保存
* @param person
* @return
*/
public int savePerson(Person person);
/**
* TODO:根据paid删除person信息
* @param pid
* @return
*/
public int delPerson(String pid);
/**
* TODO:完成信息修改
* @param p
* @return
*/
public int updatePerson(Person p);
/**
* TODO:根据编号获取指定的数据
* @param pid
* @return
*/
public Person queryById(String pid);
public Map<String,Object> queryPersion(String pid);
}
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">
<!--
*.dtd:文档类型定义 约定了文档中有哪些元素，甚至有哪些属性，哪些元素在前那些元素在后，哪些元素只能出现一次等等
就是有提示功能
*.xsd:模式文件。模式文件和dtd文件功能一致，但是xsd文件是通过xml技术辨写的。
-->
<configuration>
<!-- 指定外部的配置文件，然后再数据源配置中直接应用外部文件中的内容 -->
<properties resource="init.properties"></properties>
<typeAliases>
<typeAlias type="com.rock.pojo.Person" alias="Person"/>
<typeAlias type="com.rock.pojo.Car" alias="Car"/>
<typeAlias type="com.rock.pojo.Card" alias="Card"/>
</typeAliases>
<!-- 配置MyBatis的核心环境 一个项目可以连接多个不同的数据，可以在environments内配置多个environment-->
<environments default="development">
<environment id="development">
<!-- 数据库事务管理方式 -->
<!-- MyBatis中可以配置两种类型的事务管理器
JDBC: 常用
MANAGED : 不常用
-->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源 -->
<!--
MyBatis默认有三种不同类型的数据源
POOLED： 连接池数据源，，适应于有一定并发量的项目中
UNPOOLED：每次使用完毕关闭资源，速度会比较慢，有些内部管理软件使用
JNDI：远程方法调用，，和EJD绑定使用
-->
<dataSource type="POOLED">
<!-- 指定数据源的相关参数 -->
<!-- ${mysql.driver}==>即返回配置文件中mysql.driver这个key对应的值 -->
<property name="driver" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<!-- 配置MyBatis的映射文件路径 可以有多个mapper-->
<mappers>
<!-- 注意：这个里的路径使用/分割。 而不是一个全类名.()-->
<mapper resource="com/rock/pojo/PersonMapper.xml"/>
<mapper resource="com/rock/dao/CarMapper.xml"/>
</mappers>
</configuration>
PersonDao的单元测试类
package com.rock.test;
import java.io.IOException;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.rock.dao.IPersonDao;
import com.rock.pojo.Person;
/**
* 利用JUnit完成测试
* @author Administrator
*
*/
public class PersonJUnit {
private SqlSession sqlSession;
@Before
public void init(){
try {
sqlSession=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testSavePerson(){
Person p=new Person("A0001","刘备","三国",40);
int a=sqlSession.insert("com.rock.dao.IPersonDao.savePerson",p);
System.out.println(a);
}
@Test
public void testDeletePerson(){
int a=sqlSession.delete("com.rock.dao.IPersonDao.delPerson","A0001");
System.out.println(a);
}
@Test
public void testUpdatePerson(){
Person p=new Person("A0001","关羽","中国北京",50);
int a=sqlSession.update("com.rock.dao.IPersonDao.updatePerson",p);
System.out.println(a);
}
@Test
public void testQueryById(){
// select检索数据，由于检索数据的结果可有多种不同的形式，所以呢？sqlSession提供了不同方法
// selectOne：即是只返回一个对象
Person p=sqlSession.selectOne("com.rock.dao.IPersonDao.queryById","A0001");
System.out.println(p);
}
@Test
public void testqueryPerson(){
// Map<Stirng,Object> all=sqlSession.selectOne("com.rock.dao.IPersonDao.queryPerson","A0001");
Map<String,Object> all=sqlSession.getMapper(IPersonDao.class).queryPersion("A0001");
for(String k:all.keySet()){
System.out.println(k+"::"+all.get(k));
}
}
@After
public void destroy(){
System.out.println("功能测试完毕.............................");
}
}
Car的单元测试类
package com.rock.test;
import java.io.IOException;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.rock.dao.CarMapper;
import com.rock.pojo.Car;
import com.rock.pojo.Card;
public class CarTest {
private SqlSession sqlSession;
private CarMapper iCarDao;
@Before
public void init(){
try {
sqlSession=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession(true);
iCarDao=sqlSession.getMapper(CarMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testSaveCar(){
Car car=new Car("C0002","BMW",300,500000);
CarMapper iCarDao=sqlSession.getMapper(CarMapper.class);
System.out.println(iCarDao.saveCar(car));
}
@Test
public void testqueryById(){
CarMapper iCarDao=sqlSession.getMapper(CarMapper.class);
Car car =iCarDao.queryById("C0002");
System.out.println(car);
}
@Test
public void testqueryCardById(){
CarMapper iCarDao=sqlSession.getMapper(CarMapper.class);
Card card =iCarDao.queryCardById("C0002");
System.out.println(card);
}
}
5、知识总结
①、dtd和xsd文件：用于约束xml中的元素及属性甚至是相关的值。应用—>提示
②、事务管理类型有几种 JDBC|MANAGED
③、数据源类型几种 POOLED | UNPOOLED |JNDI
④、ResultMap|ResultType
⑤、当pojo类中的属性和表中的列名不一致的解决方案 SQL别名|通过ResultMap
6、User的全案例
①、User表
CREATE TABLE `user` (
`userid` VARCHAR(50) NOT NULL,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(300) DEFAULT NULL,
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`userid`)
)
②、User.java
package com.rock.pojo;
public class User {
private String userid;
private String username;
private String password;
private int age;
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User [userid=" + userid + ", username=" + username + ", password=" + password + ", age=" + age + "]";
}
public User(String userid, String username, String password, int age) {
super();
this.userid = userid;
this.username = username;
this.password = password;
this.age = age;
}
public User() {
super();
}
}
③、Mapper.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="com.rock.dao.IUserDao">
<resultMap type="User" id="userBaseMaps">
<id property="userid" column="userid"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="age" column="age"/>
</resultMap>
<insert id="saveUser" parameterType="User">
insert into user(userid,username,password,age) values(#{userid},#{username},#{password},#{age});
</insert>
<delete id="deleteUser">
delete from user where userid=#{userid}
</delete>
<update id="updateUser" parameterType="User">
update user set username=#{username},password=#{password},age=#{age} where userid=#{userid}
</update>
<!--
<select id="queryAll" resultType="User">
select * from user
</select>
-->
<select id="queryAll" resultMap="userBaseMaps">
select * from user
</select>
<select id="queryByAge" resultType="User" parameterType="int" >
select * from user where age>#{age}
</select>
<select id="queryByID" resultType="User" parameterType="String" >
select * from user where userid=#{userid}
</select>
<select id="queryAllToMap" resultType="java.util.HashMap">
select * from user
</select>
</mapper>
④ 、UserJUnit.java
package com.rock.test;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.rock.dao.IUserDao;
import com.rock.pojo.User;
public class UserTest {
private IUserDao iUserDao;
@Before
public void init(){
try {
SqlSession sqlSession=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession(true);
iUserDao=sqlSession.getMapper(IUserDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testSaveUser(){
System.out.println(iUserDao.saveUser(new User("ruby","刘备","admin",20)));
System.out.println(iUserDao.saveUser(new User("dsfore","张飞","123123",5)));
}
@Test
public void testDeleteUser(){
System.out.println(iUserDao.deleteUser("rock"));
}
@Test
public void testUpdateUser(){
System.out.println(iUserDao.updateUser(new User("rock","关羽","123456",30)));
}
@Test
public void testQueryAllUser(){
List<User> all=iUserDao.queryAll();
for(User user:all)
System.out.println(user);
}
@Test
public void testqueryByAge(){
List<User> all=iUserDao.queryByAge(10);
for(User user:all)
System.out.println(user);
}
@Test
public void testqueryByID(){
User user=iUserDao.queryByID("ruby");
System.out.println(user);
}
@Test
public void testqueryAllToMap(){
List<Map<String,Object>> all=iUserDao.queryAllToMap();
for(Map<String,Object> maps:all){
for(String key:maps.keySet())
System.out.print(key+"="+maps.get(key)+"\t");
System.out.println();
}
}
}
⑤、IUserDao.java
package com.rock.dao;
import java.util.List;
import java.util.Map;
import com.rock.pojo.User;
/**
* @author Administrator
*
*/
public interface IUserDao {
public int saveUser(User user);
public int deleteUser(String userid);
public int updateUser(User user);
public List<User> queryAll();
/**
* TODO:检索比age大的人都有谁
* @param age
* @return
*/
public List<User> queryByAge(int age);
public User queryByID(String userid);
public List<Map<String,Object>> queryAllToMap();
}
7、动态SQL，，，MyBatis元素
<insert> <delete> <update><select > <sql><include> <if></if><trim></trim><where></where>
<choose></choose><when></when><otherwise></otherwise><foreach></foreach>
Employee.java
package com.rock.pojo;
public class Employee {
private int empno;
private String ename;
private String email;
private String emobile;
private String sex;
private int deptno;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmobile() {
return emobile;
}
public void setEmobile(String emobile) {
this.emobile = emobile;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Employee [empno=" + empno + ", ename=" + ename + ", email=" + email + ", emobile=" + emobile
+ ", sex=" + sex + ", deptno=" + deptno + "]";
}
public Employee(int empno, String ename, String email, String emobile, String sex, int deptno) {
super();
this.empno = empno;
this.ename = ename;
this.email = email;
this.emobile = emobile;
this.sex = sex;
this.deptno = deptno;
}
public Employee() {
super();
}
}
EmployeeMapper.dao
package com.rock.dao;
import java.util.List;
import com.rock.pojo.Employee;
public interface EmployeeMapper {
public List<Employee> queryByLike(Employee employee);
public List<Employee> queryAll();
public List<Employee> queryByEname2Sex(Employee employee);
public List<Employee> queryByMultiCondition(Employee employee);
public int updateEmployee(Employee employee);
public int updateEmployee1(Employee employee);
public Employee queryByID(int empno);
public int saveEmployee(Employee employee);
public int deleteBatch(int[] ids);
}
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="com.rock.dao.EmployeeMapper">
<sql id="employeeColumnLists">
empno ,ename,email,emobile,sex,deptno
</sql>
<sql id="employeeColumnListsNoEmpNo">
ename,email,emobile,sex,deptno
</sql>
<resultMap type="Employee" id="employeeBaseMaps">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"/>
<result property="email" column="email"/>
<result property="emobile" column="emobile"/>
<result property="sex" column="sex"/>
<result property="deptno" column="deptno"/>
</resultMap>
<!-- 检索所有信息 -->
<select id="queryAll" resultMap="employeeBaseMaps">
select <include refid="employeeColumnLists"/> from employee
</select>
<!-- 模糊检索 -->
<select id="queryByLike" resultMap="employeeBaseMaps" parameterType="Employee">
select <include refid="employeeColumnLists"/> from employee where 1=1
<if test="ename!=null">
and ename like '%${ename}%'
</if>
</select>
<!-- 如同Java中的switch...case -->
<select id="queryByEname2Sex" resultMap="employeeBaseMaps" parameterType="Employee">
select <include refid="employeeColumnLists"/> from employee where 1=1
<choose>
<when test="ename!=null">
and ename=#{ename}
</when>
<when test="sex!=null">
and sex=#{sex}
</when>
<otherwise>
and deptno=1
</otherwise>
</choose>
</select>
<!-- where元素的使用
注意：在where元素内若一个条件都不成立，MyBatis就不会在sql文中加入where子句
-->
<select id="queryByMultiCondition" parameterType="Employee" resultMap="employeeBaseMaps">
select <include refid= "employeeColumnLists"/>
from employee
<where>
<if test="ename!=null">
ename=#{ename}
</if>
<if test="deptno!=null">
and deptno=#{deptno}
</if>
<if test="sex!=null">
and sex=#{sex}
</if>
</where>
</select>
<!-- 利用trim元素完成sql文格式化 -->
<!-- Employee [empno=3, ename=0ee431, deptno=1] -->
<update id="updateEmployee" parameterType="Employee">
update employee
<!-- set
ename=#{ename},
emobile=#{emobile},
deptno=#{deptno},
sex=#{sex}
where empno=#{empno} -->
<trim prefix="set" suffixOverrides="," suffix="where empno=#{empno}">
<if test="ename!=null">
ename=#{ename},
</if>
<if test="email!=null">
email=#{email},
</if>
<if test="emobile!=null">
emobile=#{emobile},
</if>
<if test="deptno!=null">
deptno=#{deptno},
</if>
<if test="sex!=null">
sex=#{sex},
</if>
</trim>
</update>
<select id="queryByID" parameterType="int" resultMap="employeeBaseMaps">
select <include refid="employeeColumnLists"/> from employee where empno=#{empno}
</select>
<update id="updateEmployee1" parameterType="Employee">
update employee
set
ename=#{ename},
emobile=#{emobile},
email=#{email},
deptno=#{deptno},
sex=#{sex}
where empno=#{empno}
</update>
<!-- trim运用在insert语句中 -->
<!-- 利用trim优化sql语句 -->
<insert id="saveEmployee" parameterType="Employee">
insert into employee
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="ename!=null">ename,</if>
<if test="email!=null">email,</if>
<if test="emobile!=null">emobile,</if>
<if test="sex!=null">sex,</if>
<if test="deptno!=null">deptno,</if>
</trim>
<trim prefix=" values (" suffix=")" suffixOverrides=",">
<if test="ename!=null">#{ename},</if>
<if test="email!=null">#{email},</if>
<if test="emobile!=null">#{emobile},</if>
<if test="sex!=null">#{sex},</if>
<if test="deptno!=null">#{deptno},</if>
</trim>
</insert>
<!-- 批量删除，利用foreach元素 -->
<delete id="deleteBatch" parameterType="int">
<!-- [3,4,5,6] -->
<!-- DELETE FROM employee WHERE empno IN(3,4,5,6) -->
DELETE FROM employee WHERE empno IN
<!-- DELETE FROM employee WHERE empno IN ( ? , ? , ? , ? ) -->
<foreach collection="array" open="(" close=")" separator="," item="ids">
#{ids}
</foreach>
</delete>
</mapper>
EmployeeJunit.java
package com.rock.test;
import java.io.IOException;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.rock.dao.EmployeeMapper;
import com.rock.pojo.Employee;
public class EmployeeTest {
private SqlSession sqlSession;
private EmployeeMapper employeeMapper;
@Before
public void init(){
try {
sqlSession=new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession(true);
employeeMapper=sqlSession.getMapper(EmployeeMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testQueryAll(){
List<Employee> all=employeeMapper.queryAll();
for(Employee e:all)
System.out.println(e);
}
@Test
public void testQueryAllByLike(){
Employee e=new Employee();
e.setEname("7a");
List<Employee> all=employeeMapper.queryByLike(e);
for(Employee e1:all)
System.out.println(e1);
}
@Test
public void queryByEname2Sex(){
Employee e=new Employee();
// e.setEname("0ee431");
// e.setSex("M");
List<Employee> all=employeeMapper.queryByEname2Sex(e);
for(Employee e1:all)
System.out.println(e1);
}
@Test
public void testqueryByMultiCondition(){
Employee e=new Employee();
e.setEname("0ee431");
// e.setSex("M");
e.setDeptno(1);
List<Employee> all=employeeMapper.queryByMultiCondition(e);
for(Employee e1:all)
System.out.println(e1);
}
@Test
public void testupdateEmployee(){
Employee e=new Employee();
e.setEname("关羽");
// e.setSex("M");
e.setDeptno(2);
e.setEmpno(5);
System.out.println(employeeMapper.updateEmployee(e));
}
@Test
public void testupdateEmployee1(){
Employee e=employeeMapper.queryByID(6);
e.setEname("张飞");
System.out.println(employeeMapper.updateEmployee1(e));
}
@Test
public void testsaveEmployee(){
Employee e=employeeMapper.queryByID(3);
e.setEname("赵云");
e.setEmail(null);
System.out.println(employeeMapper.saveEmployee(e));
}
@Test
public void testdeleteBatch(){
int args[]={2,7,8,9};
int a=employeeMapper.deleteBatch(args);
System.out.println(a);
}
}
log4j.properties
log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
8、MyBatis实现1:1 以及 1:N关联关系
MyBatis实现多表联查
①、在POJO类中添加另一一张表的所有列名，然后在sql语句中辨写多表联查sql语句
②、在mybatis的映射文件中通过association元素实现级联操作进而完成多表连接查询
①、1:1实现
①、在其中的一端中的pojo类中添加另一端的对象
public class User {
private String userid;
private String username;
private String password;
private int age;
private String cid;
private Car car;// 映射当前这个人的汽车
@Override
public String toString() {
return "User [userid=" + userid + ", username=" + username + ", password=" + password + ", age=" + age
+ ", cid=" + cid + ", car=" + car + ", cars=" + cars + "]";
}
}
②、在User这个映射文件中通过设置级联检索
<resultMap type="com.rock.multitable.User" id="userColumnListsWithCarOOP">
<id property="userid" column="userid"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="age" column="age"/>
<result property="cid" column="cid"/>
<!--
联级操作，联合查询
property：当前对象中变量名
javaType：对应的那一端的类型
column：指定关联的列（即另一端的主键列）
-->
<association property="car" javaType="com.rock.multitable.Car" column="cid">
<id property="cid" column="cid"/>
<result property="brand" column="brand"/>
<result property="maxspeed" column="maxspeed"/>
<result property="price" column="price"/>
</association>
</resultMap>
③、在映射文件中将返回值类型设置为resultMap类型，并关联如上map即可
案例：根据部门表和员工表实现如下功能，检索一个人，要求包含有这个类的办公地点信息
①、定义Employee POJO类
public class Employee {
private int empno;
private String ename;
private String email;
private String emobile;
private String sex;
private int deptno;
private Department department;//当前这个人属于哪个部门
}
②、在Employee的映射文件中配置级联操作
<resultMap type="com.rock.multitable.Employee" id="employeeMapsWithDepartment">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"/>
<result property="deptno" column="deptno"/>
<association property="department" javaType="com.rock.multitable.Department" column="deptno">
<id property="deptno" column="deptno"/>
<result property="dname" column="dname"/>
<result property="location" column="location"/>
</association>
</resultMap>
<select id="queryAll" resultMap="employeeMapsWithDepartment">
select e.*,d.* from employee e inner join department d on e.deptno=d.deptno
</select>
②、N:1 映射的实现
①、在1的那一端的pojo类中添加集合属性
public class Department {
private int deptno;
private String dname;
private String location;
private List<Employee> emps;
}
②、在1一端的映射文件中配置级联操作
<resultMap type="com.rock.multitable.Department" id="deptMaps">
<id property="deptno" column="deptno"/>
<result property="dname" column="dname"/>
<result property="location" column="location"/>
<!--
collection:因为要映射的是一个List集合
property:1一端的POJO类中的集合变量名
ofType:集合持有的类型
column，将指定列的等值传递给select属性指定的方法中
select：指定select语句
-->
<collection property="emps" ofType="com.rock.multitable.Employee" column="deptno" select="queryEmpsFromDept"></collection>
</resultMap>
<select id="queryEmpsFromDept" resultType="com.rock.multitable.Employee">
select * from employee where deptno=#{deptno}
</select>
9、MyBatis****
步骤：
①、百度MyBatis Generator
然后单击Quick Start Guide
单击XML Configuration File Reference 连接。然后拷贝测试代码到项目中自定义的xml文件中，比如mbg.xml
按照如下代码修改
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="DB2Tables" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!-- 配置数据源参数 -->
<jdbcConnection
driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://127.0.0.1:3306/ssm"
userId="root"
password="admin">
</jdbcConnection>
<javaTypeResolver >
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- 配置pojo类 -->
<javaModelGenerator
targetPackage="com.generator.pojo"
targetProject=".\src"> <!-- .\当前项目 若是maven项目 应该设置为。 .\src\main\java-->
<property name="enableSubPackages" value="true" />
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- 配置映射文件 -->
<sqlMapGenerator
targetPackage="com.generator.dao"
targetProject=".\src">
<property name="enableSubPackages" value="true" />
</sqlMapGenerator>
<!-- 配置dao接口 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.generator.dao"
targetProject=".\src">
<property name="enableSubPackages" value="true" />
</javaClientGenerator>
<!--
****基于那些表创建哪些POJO
tableName：表名
domainObjectName：POJO类的类名
-->
<table tableName="department" domainObjectName="Department"></table>
<table tableName="employee" domainObjectName="Employee"></table>
<table tableName="car" domainObjectName="Car"></table>
</context>
</generatorConfiguration>
②、创建Java运行文件，直接拷贝java代码执行即可
package com.rock.test;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;
public class MBG {
public static void main(String[] args)
throws IOException, XMLParserException, InvalidConfigurationException, SQLException, InterruptedException {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
File configFile = new File("NewFile.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
System.out.println("ending............");
}
}