基本作用就是配置 JDBC 连接的有关信息,比如 URL 、用户名、密码等等
如:
<? 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 > <!-- 别名 --> < typeAliases > < typeAlias type = "org.leadfar.mybatis.Person" alias = "Person" /> </ typeAliases > <!-- 配置数据库连接信息 --> < environments default = "development" > < environment id = "development" > < transactionManager type = "JDBC" /> < dataSource type = "POOLED" > < property name = "driver" value = "com.mysql.jdbc.Driver" /> < property name = "url" value ="jdbc:mysql://localhost/mybatis" /> < property name = "username" value = "root" /> < property name = "password" value = "leadfar" /> </ dataSource > </ environment > </ environments > <!-- 映射文件定位 --> < mappers > < mapper resource = "org/leadfar/mybatis/PersonMapper.xml" /> </ mappers > </ configuration >
|
3 映射文件
基本作用就是编写 SQL 语句,同时给每个 SQL 语句定义一个唯一标识( ID ),在程序中通过此 ID 来调用这条SQL 语句。
<? 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" > <!-- 整个唯一标识 sql 的 id 为 namespace+id org.leadfar.mybatis.Person.insert--> < mapper namespace = "org.leadfar.mybatis.Person" > < insert id = "insert" parameterType = "Person" > insert into t_person(name,age,birthday) values(#{name},#{age},#{birthday}) </ insert >
</ mapper > |
4 程序代码
// 声明一个 session 管理工厂 SqlSessionFactory factory = null ; // 声明读取器 Reader reader = null ; try { // 通过读取器定位到主配置文件 reader = Resources.getResourceAsReader ( "SqlMapConfig.xml"); // 初始化工厂 factory = new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 打开一个会话 ( 类似于 jdbc 中创建数据库连接 ) SqlSession session = factory.openSession(); Person p = new Person(); p.setName( " 张三 " ); p.setAge(11); p.setBirthday( new Date()); // 进行插入 try { session.insert( "org.leadfar.mybatis.Person.insert" , p); // 事务提交 session.commit(); System. out .println( " 保存成功 !" ); } catch (Exception e) { e.printStackTrace(); // 事务回滚 session.rollback(); } finally { // 关闭连接 session.close(); } |
5sql 参数传递
1. 简单参数
< delete id = "delete" parameterType = "int" > delete from t_person where id=#{id} <!— 无所谓写什么都可以 à </ delete > |
2. 多个参数,建议采用 Map 包装
< select id = "selectLikeNameAndAgeBetween" parameterType = "map"resultType = "Person" > < include refid = "selectBasic" /> where name like #{name} and age between #{age1} and #{age2} </ select >
|
SqlSession session = factory .openSession(); Map map = new HashMap(); map .put( "name" , "% 张 %" ); map .put( "age1" , 0); map .put( "age2" , 100); List persons=session.selectList(Person. class .getName()+".selectLikeNameAndAgeBetween" ,map );
System. out .println(((Person)(persons.get(0))).getName()); session.close(); |
6sql 语句块
< sql id = "selectBasic" > select * from t_person </ sql > < select id = "selectLikeName" parameterType = "string" resultType ="Person" > < include refid = "selectBasic" /> where name like #{name} </ select > |
7 属性名与字段名不匹配
< resultMap type = "Person" id = "select-reusltMap" >
< result column = "sex" property = "gender" />
</ resultMap >
< select id = "selectLikeNameAndAgeBetweenResultMap" parameterType = "map" resultMap ="select-reusltMap" >
< include refid = "selectBasic" /> where name like #{name} and age between #{age1} and #{age2}
</ select >
8 动态 sql
8.1 .if
< select id = "selectIf" parameterType = "map" resultType = "Person" > select * from t_person < if test = "name !=null" > where name like #{name} </ if > < if test = "age !=0" > and age=#{age} </ if > </ select > |
注:如果 name==null ,则 sql 拼写错误
< select id = "selectWhere" parameterType = "map" resultType = "Person" > select * from t_person < where > < if test = "name !=null" > name like #{name} </ if > < if test = "age !=0" > and age=#{age} </ if > </ where > </ select > |
注 : 加 <where> 后则确保一定是 where 开头
8.2 .choose
类似于 switch
< select id = "selectChoose" parameterType = "map" resultType = "Person" > select * from t_person < choose > < when test = "name!=null" > where name like #{name} </ when > < otherwise > where name like '%%' </ otherwise > </ choose > < if test = "age !=0" > and age=#{age} </ if > </ select > |
8.3 .foreach
如 in 操作
< select id = "selectFor" parameterType = "list" resultType = "Person" > select * from t_person where id in < foreach collection = "list" item = "p" open = "(" close = ")" separator= "," > #{p} </ foreach > </ select > |
SqlSession session = factory .openSession(); List l = new ArrayList(); l .add(1); l .add(2); l .add(3); l .add(4); List persons=session.selectList(Person. class .getName()+".selectFor" ,l );
System. out .println(((Person)(persons.get(1))).getName()); session.close(); |
8.4 $
相当于转义,字符串替换
< select id = "selectIn" parameterType = "map" resultType = "Person" > select * from t_person where id in ${instr} </ select > |
SqlSession session = factory .openSession();
Map params= new HashMap(); //params.put("name", "% 张 %"); params.put( "instr" , "(1,2,3,4)" ); List persons=session.selectList(Person. class .getName()+".selectIn" ,params);
System. out .println(((Person)(persons.get(1))).getName()); session.close(); |
另外对于排序时由外部传入某字段
< select id = "selectOrderBy" parameterType = "map" resultType = "Person" > select * from t_person order by ${by}
</ select > |
SqlSession session = factory .openSession();
Map params= new HashMap(); //params.put("name", "% 张 %"); params.put( "by" , "age desc" ); List persons=session.selectList(Person. class .getName()+".selectOrderBy" ,params);
System. out .println(((Person)(persons.get(1))).getName()); session.close(); |
9 一对一映射
9.1 简单方法
设 Person 与 Address 是一对一关系
PersonMapper.xml
< resultMap type = "Person" id = "select-resultMap" > <!-- 一对一 ( 多对一 ) 通过 association,property 目标对象的属性 --> < association property = "address" select ="org.leadfar.mybatis.Address.selectAll" column = "id" javaType = "Address">
</ association >
</ resultMap > |
< select id = "selectAll" resultMap = "select-resultMap" > select * from t_person </ select > |
AddressMapper.xml
< select id = "selectAll" parameterType = "int" resultType = "Address" > select * from t_address where person_id=#{pId} </ select > |
9.2 解决 N+1 问题
< select id = "selectAllN1" resultMap = "select-resultMapN1" > select a.*,b.id addr_id, b.postCode,b.area from t_person a left join t_address b on a.id=b.person_id </ select > |
<!-- 这种方式能解决 N+1 问题,但是自动对象赋值将不行 --> < resultMap type = "Person" id = "select-resultMapN1" > < id column = "id" property = "id" /> < result column = "sex" property = "gender" /> < result column = "name" property = "name" /> < result column = "birthday" property = "birthday" /> < result column = "age" property = "age" /> < association property = "address" column = "id" javaType ="Address" > < id column = "addr_id" property = "id" /> < result column = "area" property = "area" /> < result column = "postCode" property = "postCode" /> </ association > </ resultMap > |
6.10 一对多映射
设 Person 和 Car 为一对多关系
< select id = "selectAllN1" resultMap = "select-resultMapN1" > select a.*,b.id addr_id, b.postCode,b.area from t_person a left join t_address b on a.id=b.person_id </ select > |
<!-- 这种方式能解决 N+1 问题,但是自动对象赋值将不行 --> < resultMap type = "Person" id = "select-resultMapN1" > < id column = "id" property = "id" /> < result column = "sex" property = "gender" /> < result column = "name" property = "name" /> < result column = "birthday" property = "birthday" /> < result column = "age" property = "age" /> < association property = "address" column = "id" javaType ="Address" > < id column = "addr_id" property = "id" /> < result column = "area" property = "area" /> < result column = "postCode" property = "postCode" /> </ association > <!-- 将 t_car 中相关的数据与目标对象 (Person) 中的 cars 属性进行对应 --> < collection property = "cars" column = "id" select ="org.leadfar.mybatis.Car.selectByPerson" ></ collection > </ resultMap > |