我遇到的问题一个是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+idorg.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 <iftest="name !=null"> where name like #{name} </if> <iftest="age !=0"> and age=#{age} </if> </select> |
注:如果 name==null ,则 sql 拼写错误
<selectid="selectWhere"parameterType="map"resultType="Person"> select * from t_person <where> <iftest="name !=null"> name like #{name} </if> <iftest="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> <iftest="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_personorder 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"> <idcolumn="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"> <idcolumn="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"> <idcolumn="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"> <idcolumn="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> |