项目结构
SqlSessoinFactoryUtil.java
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.Reader;
public class SqlSessionFactoryUtil {
private static SqlSessionFactory sqlSessionFactory = null;
public static SqlSessionFactory getSqlSessionFactory(){
if(sqlSessionFactory == null){
String resource = "mybatis.xml";
try {
Reader reader = Resources.getResourceAsReader( resource );
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
}
return sqlSessionFactory;
}
}
定义Mybatis配置文件:mybatis.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>
<!-- 加入DB配置文件 -->
<properties resource="mysql.properties"></properties>
<!-- 配置配置项 -->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="jdbcTypeForNull" value="NULL"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!--名别名-->
<typeAliases>
<package name="com.imooc.mybatis.bean"/>
</typeAliases>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
<!--<plugin interceptor="com.mooc.mybatis.interceptor.MyFirstInterceptor">
<property name="hello" value="world"></property>
</plugin>
<plugin interceptor="com.mooc.mybatis.interceptor.MySecondInterceptor">
</plugin>-->
</plugins>
<environments default="dev_mysql">
<environment id="dev_mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.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="com/imooc/mybatis/dao/PersonMapper.xml"/>
</mappers>
</configuration>
mybatis.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=123456
即通过Mybatis通过读取配置文件mybatis.xml来完成一系列的sql操作。
定义Dao接口:PersonMapper.java
package com.imooc.mybatis.dao;
import com.imooc.mybatis.bean.Person;
import org.apache.ibatis.annotations.Param;
import java.util.Collection;
import java.util.List;
import java.util.Map;
public interface PersonMapper {
void deletePerson(Integer id);
List<Person> selectByNameAndGender(String username, String gender);
List<Person> selectByNameAndGender2(Map<String,Object> param);
List<Person> selectByNameAndGender3(@Param( "p1" ) String name, @Param( "p2" ) String gender);
List<Person> selectByCollection(Collection list);
List<Person> selectByArray(Integer[] integers);
List<Person> selectByIds(Integer[] integers);
}
定义mapper文件:PersonMapper.xml
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--使用命名空间来实现mapper的xml文件和对应的接口文件的关联-->
<mapper namespace="com.imooc.mybatis.dao.PersonMapper">
<resultMap id="resultMap" type="person">
<id property="id" column="id" javaType="Integer"/>
<result property="username" column="username" javaType="String"/>
<result property="email" column="email" javaType="String"/>
<result property="gender" column="gender" javaType="String"/>
<result property="deptId" column="dept_id" javaType="Integer"/>
</resultMap>
<delete id="deletePerson" parameterType="Integer">
delete from person where id = #{i}
</delete>
<select id="selectByNameAndGender" resultMap="resultMap" >
select * from person where username = #{param1} and gender = #{param2}
</select>
<select id="selectByNameAndGender2" resultMap="resultMap" >
select * from person where username = #{name} and gender = #{sex}
</select>
<select id="selectByNameAndGender3" resultMap="resultMap" >
select * from person where username = #{p1} and gender = #{p2}
</select>
<select id="selectByCollection" resultMap="resultMap" >
select * from person where id = #{list[1]}
</select>
<select id="selectByArray" resultMap="resultMap" >
select * from person where id = #{array[2]}
</select>
<!--array充当数组方式时的key,idv表示每一项的值,index在list中充当索引,在map中充当key,通过,将每个#{div}分隔开
构造出 select * from person where id in (#{id1},#{id2},#{id3})的形式,从而形成sql语句-->
<select id="selectByIds" resultMap="resultMap" >
select * from person where id in
<foreach collection="array" item="idv" open="(" close=")" index="i" separator=",">
#{idv}
</foreach>
</select>
</mapper>
定义测试:Demo.java
import com.imooc.mybatis.bean.Person;
import com.imooc.mybatis.dao.PersonMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
public class Demo {
private SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession();
private PersonMapper personMapper = sqlSession.getMapper( PersonMapper.class );
/**
* 根据ID删除所对应的Person数据,传入单个参数
*/
@Test
public void deletePerson(){
personMapper.deletePerson( 7 );
sqlSession.commit();
}
@Test
public void selectByNameAndGender(){
List<Person> list = personMapper.selectByNameAndGender( "tom","F" );
System.out.println( list );
}
/**
* 出入map参数
*/
@Test
public void selectByNameAndGender2(){
Map<String,Object> map = new HashMap<String,Object>( );
map.put( "name","tom" );
map.put( "sex","F" );
List<Person> list = personMapper.selectByNameAndGender2(map);
System.out.println( list );
}
/**
* 通过@Param注解传入参数
*/
@Test
public void selectByNameAndGender3(){
List<Person> list = personMapper.selectByNameAndGender3("tom","F");
System.out.println( list );
}
/**
* 集合类型作为参数
*/
@Test
public void selectByCollection(){
List<Person> list = personMapper.selectByCollection(Arrays.asList( 2,8 ));
System.out.println( list );
}
/**
* 以数组作为参数
*/
@Test
public void selectByArray(){
List<Person> list = personMapper.selectByArray(new Integer[]{1,2,8});
System.out.println( list );
}
/**
* 以数组作为参数,使用foreach循环
*/
@Test
public void selectByIds(){
List<Person> list = personMapper.selectByIds(new Integer[]{1,2,8});
System.out.println( list );
}
}
Mybatis之参数传递
typeAliases的作用
<typeAliases>
<package name="com.imooc.mybatis.bean"/>
</typeAliases>
如果注释掉:则会出现如下异常,
故typeAliases配置的作用是使得实体类对于mybatis是可见的。如果不配置,则实体类对于mybatis是不可见的,即mybatis无法找到实体类,更别谈实体类和数据库中数据表的映射和关联了。只有当实体类对于mybatis不是透明的,mybatis才能基于实体类进行相应的sql操作。
Dao接口中的返回值类型
Dao接口设定返回值类型为List和Person这两种情况是不同的,如果设定的返回只类型是List,则mybatis会将mapper中的select语句查找出来的结果封装成LIst,如List<Person> list,如果在Dao接口设定的返回值类型为一个实体,比如Person,则mybatis会默认你想要查找一条记录,mybatis会判断查询结果中是否是一条记录,如果是就继续执行,如果不是就报错。所以,查询单条记录时Dao接口中的返回值类型设为一个对象,如Person。如果查询多条记录,或者查询结果可能不止一条时,Dao接口中的返回值类型要设为Lis<某个实体>,如List<Person>
关于resultMap和resultTpye
如果resultMap和resultType一个都不指定,则会报错。
Caused by: org.apache.ibatis.executor.ExecutorException: A query was run and no Result Maps were found for the Mapped Statement 'com.imooc.mybatis.dao.PersonMapper.selectByNameAndGender'. It's likely that neither a Result Type nor a Result Map was specified.
mybatis默认(不指定paramTpye时)处理多参数情况
按照Dao接口中的参数来传递sql语句中的参数
当然,参数写成如下形式也行
Mybatis之单参数处理(Mybatis会自动进行参数的赋值)
1.PersonMapper.java中定义接口
void deletePerson(Integer id);
2.mapper文件中进行delete
<delete id="deletePerson" parameterType="Integer">
delete from person where id = #{i} <!--这里参数名是任意的-->
</delete>
3.测试
/**
* 根据ID删除所对应的Person数据,传入单个参数
*/
@Test
public void deletePerson(){
personMapper.deletePerson( 7 );
sqlSession.commit();
}
Mybatis之多参数处理的三种方法
方法1:封装POJO类(类似java中的实体类,在mapper文件中通过#{属性名}取出属性值)
可使用拥有不同参数个数的实体类的构造方法来构造POJO对象。
方法2:封装成Map(在mapper文件中通过#{key}取出map中对应的值)
如果参数比较少,并且没有对应的JavaBean,可以封装成Map。
1.PersonMapper.java中定义接口
List<Person> selectByNameAndGender2(Map<String,Object> param);
2.mapper文件中进行select
<select id="selectByNameAndGender2" resultMap="resultMap" >
select * from person where username = #{name} and gender = #{sex}
</select>
3.测试
@Test
public void selectByNameAndGender2(){
Map<String,Object> map = new HashMap<String,Object>( );
map.put( "name","tom" );
map.put( "sex","F" );
List<Person> list = personMapper.selectByNameAndGender2(map);
System.out.println( list );
}
方法3:使用@Param注解
Mybatis帮助我们实现将@Param("key_name")定义的各个参数封装成一个Map,然后再在mapper文件中通过#{key_name}取出参数值。
1.PersonMapper.java中定义接口
List<Person> selectByNameAndGender3(@Param( "p1" ) String name, @Param( "p2" ) String gender);
2.mapper文件中进行select
<select id="selectByNameAndGender3" resultMap="resultMap" >
select * from person where username = #{p1} and gender = #{p2}
</select>
3.测试
/**
* 通过@Param注解传入参数
*/
@Test
public void selectByNameAndGender3(){
List<Person> list = personMapper.selectByNameAndGender3("tom","F");
System.out.println( list );
}
Mybatis之使用Collection,List,Arrays进行参数传递。
当使用结合类型作为参数时,因为集合采用的是List来实现的。所以在mapper文件中,key有两种形式,分别为list和collection
1.定义Dao接口
List<Person> selectByCollection(Collection list);
2.定义mapper文件
<select id="selectByCollection" resultMap="resultMap" >
select * from person where id = #{list[1]} <!--这里list换为collection亦可-->
</select>
3.进行测试
/**
* 集合类型作为参数
*/
@Test
public void selectByCollection(){
List<Person> list = personMapper.selectByCollection(Arrays.asList( 2,8 ));
System.out.println( list );
}
Mybatis之使用数组进行参数传递
当使用数组进行参数传递时,key只有一种形式,即array
1.定义Dao接口
List<Person> selectByArray(Integer[] integers);
2.定义mapper文件
<select id="selectByArray" resultMap="resultMap" >
select * from person where id = #{array[2]} <!--array是key-->
</select>
3.进行测试
/**
* 以数组作为参数
*/
@Test
public void selectByArray(){
List<Person> list = personMapper.selectByArray(new Integer[]{1,2,8});
System.out.println( list );
}
Mybatis之使用数组进行参数传递(使用foreach循环)
1.定义Dao接口
List<Person> selectByIds(Integer[] integers);
2.定义mapper文件
<!--array充当数组方式时的key,idv表示每一项的值,index在list中充当索引,在map中充当key,通过,将每个#{div}分隔开
构造出 select * from person where id in (#{id1},#{id2},#{id3})的形式,从而形成sql语句-->
<select id="selectByIds" resultMap="resultMap" >
select * from person where id in
<foreach collection="array" item="idv" open="(" close=")" index="i" separator=",">
#{idv}
</foreach>
</select>
3.进行测试
/**
* 以数组作为参数,使用foreach循环
*/
@Test
public void selectByIds(){
List<Person> list = personMapper.selectByIds(new Integer[]{1,2,8});
System.out.println( list );
}