最近在用mybatis3,sql是动态自己写的,刚刚接触,有些语法需要自己找,这里找了一篇文章,解决了不少问题,大家参考一下。
我遇到的问题一个是like ,一个in的语法,下面都有介绍,
like语法 :比如 like #{name} ,那么这个name提前定以后字符,如: String name="%张%";
in的语法:在写select的时候不应该要 <![CDATA[ ]]>这种标记,他会把不识别foreach,可以把foreach放到这个的外面。我是直接去掉了,可以跑起来程序。这里记录一下,供大家参考,这里的 <![CDATA[ ]]>标记,是为了一些xml一些特殊字符的,比如sql中有'<'这种字符会跟xml中的冲突,这时候要用到这个标记
1 什么是 Mybatis
. Mybatis
1 什么是 Mybatis
Mybatis 是对 JDBC 的封装,它隐藏了具体的 JDBC的 API ,它把 SQL 语句放到了配置文件中,它能自动把不同的输入数据映射到SQL 语句的动态参数上,它能自动把 SQL 语句的执行结果映射为 JAVA对象…… MyBatis 是一个持久化层的框架!
Mybatis 是一个 ORM 框架
可以说 Mybatis 是一个半自动的 ORM 框架,Hibernate 是全自动的
2 主配置文件
基本作用就是配置 JDBC 连接的有关信息,比如 URL 、用户名、密码等等
如:
<?xmlversion= "1.0" encoding = "UTF-8"?> <!DOCTYPEconfiguration PUBLIC"-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <!--配置管理器 --> <configuration> <!--别名 --> <typeAliases> <typeAliastype= "org.leadfar.mybatis.Person" alias = "Person"/> </typeAliases> <!--配置数据库连接信息 --> <environmentsdefault= "development" > <environmentid = "development"> <transactionManagertype= "JDBC" /> <dataSourcetype= "POOLED" > <propertyname= "driver" value = "com.mysql.jdbc.Driver"/> <propertyname= "url" value ="jdbc:mysql://localhost/mybatis"/> <propertyname= "username" value = "root"/> <propertyname= "password" value = "leadfar"/> </dataSource> </environment> </environments> <!--映射文件定位 --> <mappers> <mapperresource= "org/leadfar/mybatis/PersonMapper.xml" /> </mappers> </configuration>
|
3 映射文件
基本作用就是编写 SQL 语句,同时给每个 SQL 语句定义一个唯一标识(ID ),在程序中通过此 ID 来调用这条SQL语句。
<?xmlversion= "1.0" encoding = "UTF-8"?> <!DOCTYPEmapper 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--> <mappernamespace= "org.leadfar.mybatis.Person" > <insertid = "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 =newSqlSessionFactoryBuilder().build(reader); }catch(IOException e) { //TODOAuto-generated catch block e.printStackTrace(); } //打开一个会话 ( 类似于 jdbc 中创建数据库连接) SqlSession session = factory.openSession(); Person p =newPerson(); p.setName(" 张三 " ); p.setAge(11); p.setBirthday(newDate()); //进行插入 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. 简单参数
<deleteid = "delete"parameterType= "int" > delete from t_person where id=#{id} <!—无所谓写什么都可以 à </delete> |
2. 多个参数,建议采用 Map 包装
<selectid = "selectLikeNameAndAgeBetween"parameterType= "map" resultType = "Person"> <includerefid= "selectBasic" /> where name like #{name} and age between #{age1} and #{age2} </select>
|
SqlSession session =factory.openSession(); Mapmap = newHashMap(); 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 语句块
<sqlid = "selectBasic"> select * from t_person </sql> <selectid = "selectLikeName"parameterType= "string" resultType = "Person"> <includerefid= "selectBasic" /> where name like #{name} </select> |
7 属性名与字段名不匹配
<resultMaptype= "Person" id ="select-reusltMap">
<resultcolumn= "sex" property = "gender"/>
</resultMap>
<selectid = "selectLikeNameAndAgeBetweenResultMap"parameterType= "map" resultMap="select-reusltMap" >
<includerefid= "selectBasic" /> where name like #{name} and age between #{age1} and #{age2}
</select>
8 动态 sql
8.1 .if
<selectid = "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 拼写错误
<selectid = "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
<selectid = "selectChoose"parameterType= "map" resultType = "Person"> select * from t_person <choose> <whentest= "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 操作
<selectid = "selectFor"parameterType= "list" resultType = "Person"> select * from t_person where id in <foreachcollection= "list" item = "p"open= "(" close = ")"separator= ","> #{p} </foreach> </select> |
SqlSession session =factory.openSession(); Listl = newArrayList(); 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 $
相当于转义,字符串替换
<selectid = "selectIn"parameterType= "map" resultType = "Person"> select * from t_person where id in ${instr} </select> |
SqlSession session =factory.openSession();
Map params=newHashMap(); //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(); |
另外对于排序时由外部传入某字段
<selectid = "selectOrderBy"parameterType= "map" resultType = "Person"> select * from t_person order by ${by}
</select> |
SqlSession session =factory.openSession();
Map params=newHashMap(); //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
<resultMaptype= "Person" id ="select-resultMap"> <!--一对一 ( 多对一 ) 通过 association,property 目标对象的属性 --> <associationproperty= "address" select ="org.leadfar.mybatis.Address.selectAll"column= "id" javaType = "Address">
</association>
</resultMap> |
<selectid = "selectAll"resultMap= "select-resultMap" > select * from t_person </select> |
AddressMapper.xml
<selectid = "selectAll"parameterType= "int" resultType = "Address"> select * from t_address where person_id=#{pId} </select> |
9.2 解决 N+1 问题
<selectid = "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 问题,但是自动对象赋值将不行--> <resultMaptype= "Person" id ="select-resultMapN1"> <id column = "id"property= "id" /> <resultcolumn= "sex" property = "gender"/> <resultcolumn= "name" property = "name"/> <resultcolumn= "birthday" property = "birthday"/> <resultcolumn= "age" property = "age"/> <associationproperty= "address" column= "id" javaType = "Address"> <id column = "addr_id"property= "id" /> <resultcolumn= "area" property = "area"/> <resultcolumn= "postCode" property = "postCode"/> </association> </resultMap> |
6.10 一对多映射
设 Person 和 Car 为一对多关系
<selectid = "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 问题,但是自动对象赋值将不行--> <resultMaptype= "Person" id ="select-resultMapN1"> <id column = "id"property= "id" /> <resultcolumn= "sex" property = "gender"/> <resultcolumn= "name" property = "name"/> <resultcolumn= "birthday" property = "birthday"/> <resultcolumn= "age" property = "age"/> <associationproperty= "address" column= "id" javaType = "Address"> <id column = "addr_id"property= "id" /> <resultcolumn= "area" property = "area"/> <resultcolumn= "postCode" property = "postCode"/> </association> <!--将 t_car 中相关的数据与目标对象(Person)中的 cars 属性进行对应--> <collectionproperty= "cars" column = "id"select="org.leadfar.mybatis.Car.selectByPerson" ></ collection > </resultMap> |