MyBatis
1. 什么是MyBatis?
答:是一款基于ORM持久层的数据访问框架。
2. 为什么要使用MyBatis?
答:它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。
3. 什么是ORM?
答:ORM对象关系映射。
它是一种将内存中的对象保存到关系型数据库中的技术。
主要负责实体域对象的持久化,封装数据库访问细节。
提供了实现持久化层的另一种模式,采用映射元数据(XML)来描述对象-关系的映射细节,使得ORM中间件能在任何一个Java应用的 业务逻辑层和数据库之间充当桥梁。
4. MyBatis的工作原理
答:
5. MyBatis中的核心对象
SqlSessionFactory如何创建?有什么作用?常用方法?
答:
SqlSessionFactory它是mybatis的核心对象,通过这个对象可以获取到与数据库的连接(SqlSession)。
同时这个对象中保存着读取到的核心配置文件(mybatis-config.xml)中的信息(1.数据源 2.SQL映射文件)。
加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
定义一个Configuration对象,其中包含数据源、事务、mapper文件资源以及影响数据库行为属性设置settings
通过配置对象,则可以创建一个SqlSessionFactoryBuilder对象
通过 SqlSessionFactoryBuilder 获得SqlSessionFactory 的实例。
SqlSessionFactory 的实例可以获得操作数据的SqlSession实例,通过这个实例对数据库进行操作
SqlSession如何创建?有什么作用?常用方法?
答:
SqlSession对象提供的常用方法:
1. insert(statement, parameter):添加数据的方法
参数1【String】:需要执行的sql映射文件中的sql语句【数据访问接口+抽象方法名称】
参数2【Object】:输入参数。
2. update(statement, parameter):修改数据的方法
参数1【String】:需要执行的sql映射文件中的sql语句【数据访问接口+抽象方法名称】
参数2【Object】:输入参数。
3.selectOne(statement, parameter):查询一个数据的方法
参数1【String】:需要执行的sql映射文件中的sql语句【数据访问接口+抽象方法名称】
参数2【Object】:输入参数。
4.session.selectList(statement):查询所有数据的方法
参数1【String】:需要执行的sql映射文件中的sql语句【数据访问接口+抽象方法名称】
5.delete(statement, parameter):删除数据的方法
参数1【String】:需要执行的sql映射文件中的sql语句【数据访问接口+抽象方法名称】
参数2【Object】:输入参数。
6. getMapper(class):得到数据访问接口对象
参数1【Class】:被获取的数据访问接口的反射对象
7. commit():提交执行
8. close():关闭sqlsession
SqlSession对象它表示的是与数据库之间的一个连接(会话)。
通过SqlSession对象可以对数据库进行CRUD操作。
但是SqlSession对象在使用的时候必须指明到底需要执行的是Mapper文件中的哪个SQL
package com.click369.test1;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.click369.mybatis.javabean.Person;
import com.click369.mybatis.mapper.PersonMapper;
public class MyTest2 {
/**
* 测试添加
*/
@Test
public void testInsertPerson(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
Person person=new Person();
person.setPername("zhangsan");
person.setPerage(23);
person.setPeraddress("西安");
int temp=session.insert("com.click369.mybatis.mapper.PersonMapper.insertPerson", person);
System.out.println("temp="+temp);
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
/**
* 测试修改
*/
@Test
public void testUpdatePerson(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
Person person=new Person();
person.setPerid(1);
person.setPername("lisi");
person.setPerage(23);
person.setPeraddress("西安");
int temp=session.update("com.click369.mybatis.mapper.PersonMapper.updatePerson", person);
System.out.println("temp="+temp);
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
/**
* 测试根据id查询
*/
@Test
public void testSelectPersonById(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
Person person=session.selectOne("com.click369.mybatis.mapper.PersonMapper.selectPersonById", 1);
System.out.println("person.name="+person.getPername());
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
/**
* 测试查询所有
*/
@Test
public void testSelectPerson(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
List<Person> personList=session.selectList("com.click369.mybatis.mapper.PersonMapper.selectPerson");
System.out.println("personList.size="+personList.size());
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
/**
* 测试根据id删除
*/
@Test
public void testDeletePersonById(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
int temp=session.delete("com.click369.mybatis.mapper.PersonMapper.deletePersonById", 1);
System.out.println("temp="+temp);
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
}
6. MyBatis相关的配置文件
MyBatis的核心配置文件
-
名称
MyBatis的核心配置文件的名称是mybatis-config.xml,也可以自己定义命名。 -
位置
2.1. 普通的Eclipse创建的java工程,一般都保存在src目录下。
2.2. 普通的Eclipse创建的javaWeb工程,一般都保存在src目录下,也可以保存在WEB-INF目录下
2.3. 普通的Eclipse创建的Maven的java工程,一般都保存在src/main/resources目录下。
2.4. 普通的Eclipse创建的Maven的javaWeb工程,一般都保存在src/main/resources目录下,也可以保存在WEB-INF目录下。
<?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>
<!-- 配置引入数据库链接字符串的资源文件 -->
<properties resource="myjdbc.properties"></properties>
<!-- 配置mybatis默认的连接数据库的环境 -->
<environments default="development">
<environment id="development">
<!-- 配置事物管理器 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${mydriver}"/>
<property name="url" value="${myurl}"/>
<property name="username" value="${myusername}"/>
<property name="password" value="${mypassword}"/>
</dataSource>
</environment>
</environments>
<!-- 配置MyBatis数据访问接口的SQL映射文件路径 -->
<mappers>
<!-- 如果SQL映射文件在数据访问接口包中 -->
<!-- <package name="com/click369/mybatis/mapper/PersonMapper.xml"/> -->
<!-- 如果SQL映射文件在src/main/resources中 -->
<mapper resource="PersonMapper.xml"/>
</mappers>
</configuration>
- 文件中的配置元素及其属性
3.1. 根元素
元素:配置引入其他的资源文件(.properties),通过提供的resource属性引入。
3.2. 元素:配置mybatis默认的连接数据库的环境
default属性:默认值是development
3.3. 元素:配置具体的某一个数据库环境,它是元素的子元素
id属性:默认值是development.
3.4. 元素:配置默认的事务管理器
type属性:配置事物管理器类型
3.5元素:配置具体数据库链接的元素,是元素的子元素。
type属性:数据源类型[配置是否使用数据连接池机制管理数据库链接]
3.6. 元素:配置数据源
name属性:数据源属性名称
value属性:数据源属性名称对应的取值
3.7. 元素:配置Mapper文件的路径
<mapper resource="com/click369/mybatis/mapper/PersonMapper.xml"/>
<mapper resource="PersonMapper.xml"/>
Sql映射文件【Mapper文件】
1.名称
Sql映射文件的名称不是随便给的,与数据访问接口的名称相同,以“.xml”结尾。有多少个数据访问接口,那么到时候就要创建多少个Sql映射文件。
2.位置
Sql映射文件的位置,一般都是放在与数据访问接口相同的包中。需要注意的是在MyBatis核心配置文件中配置Sql映射文件的访问路径是要注意路径的指出。
或者
或者
表示“com/click369/mybatis/mapper”包中的所有映射文件全部被加载。
例如:
<?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.click369.mybatis.mapper.PersonMapper">
<!-- 配置添加数据的sql语句 -->
<insert id="insertPerson" parameterType="com.click369.mybatis.javabean.Person">
insert into t_person values(null,#{pername},#{perage},#{peraddress})
</insert>
<!-- 配置修改数据的sql语句 -->
<update id="updatePerson" parameterType="com.click369.mybatis.javabean.Person">
update t_person set per_name=#{pername},
per_age=#{perage},
per_address=#{peraddress}
where per_id=#{perid}
</update>
<!-- 配置查询的返回值resultMap -->
<resultMap type="com.click369.mybatis.javabean.Person" id="personMap">
<id column="per_id" property="perid"/>
<result column="per_name" property="pername"/>
<result column="per_age" property="perage"/>
<result column="per_address" property="peraddress"/>
</resultMap>
<!-- 配置根据id查询数据的sql语句 -->
<select id="selectPersonById" parameterType="int" resultMap="personMap">
select * from t_person where per_id=#{perid}
</select>
<!-- 配置查询所有数据的sql语句 -->
<select id="selectPerson" resultMap="personMap">
select * from t_person
</select>
<!-- 配置删除数据的sql语句 -->
<delete id="deletePersonById" parameterType="int">
delete from t_person where per_id=#{perid}
</delete>
</mapper>
3.文件中的配置元素及其属性
答:
3.1. 根元素
namespace属性:配置数据访问接口的包名+接口名
3.2. 元素:配置添加的sql语句
id属性:配置数据访问接口中的用来添加数据的方法名称。
parameterType属性:配置添加参数的类型
3.3.元素:配置修改的sql语句
id属性:配置数据访问接口中的用来修改数据的方法名称。
parameterType属性:配置添加参数的类型
3.4.元素:配置删除的sql语句
id属性:配置数据访问接口中的用来修改数据的方法名称。
parameterType属性:配置添加参数的类型
3.5.元素:配置查询的sql语句
id属性:配置数据访问接口中的用来修改数据的方法名称。
parameterType属性:配置添加参数的类型
resultMap属性:配置查询的结果类型。(数据库表中的列名称与实体类中的成员变量的名称不同)
resultType属性:配置查询的结果类型。(数据库表中的列名称与实体类中的成员变量的名称相同)
3.6.元素:配置查询的结果类型的元素。(把数据库表中的列名称与实体类中的成员变量的名称映射起来)
id属性:查询的结果类型的名称。
type属性:配置需要映射的java实体类的类名
3.7.元素:配置的是主键列的映射关系
column属性:配置数据库表的列名。
property属性:配置实体类的成员变量名称.
3.8. 元素:配置除主键之外的其他列
column属性:配置数据库表的列名。
property属性:配置实体类的成员变量名称.
注意:元素、元素、元素、元素的id属性是不能重复,意味着数据访问接口中不能出现方法重载的现象。
7.MyBatis的核心配置文件中的typeAliases元素有什么作用?如何配置?如何使用?
答:
typeAliases元素出现在MyBatis的核心配置文件(MyBatis-config.xml)中。
typeAliases元素用来设置实体类的别名(短名称)。方便在sql映射文件中使用这个短名称。
一个类一个别名:
<typeAliases>
<typeAlias alias="Author" type="domain.blog.Author"/>
<typeAlias alias="Blog" type="domain.blog.Blog"/>
<typeAlias alias="Comment" type="domain.blog.Comment"/>
<typeAlias alias="Post" type="domain.blog.Post"/>
<typeAlias alias="Section" type="domain.blog.Section"/>
<typeAlias alias="Tag" type="domain.blog.Tag"/>
</typeAliases>
设置一个包名,MyBatis 会在包名下面搜索需要的 Java Bean,会使用 Bean 的首字母小写的非限定类名来作为它的别名。
<typeAliases>
<package name="domain.blog"/>
</typeAliases>
通过注解的方式设置别名:
@Alias("author")
public class Author {
...
}
例如1:
mybatis-config.xml
<!-- 设置别名 -->
<typeAliases>
<typeAlias type="com.click369.mybatis.javabean.Person" alias="person"/>
</typeAliases>
PersonMapper.xml
<!-- 配置查询的返回值resultMap -->
<resultMap type="person" id="personMap">
<id column="per_id" property="perid" />
<result column="per_name" property="pername"/>
<result column="per_age" property="perage"/>
<result column="per_address" property="peraddress"/>
</resultMap>
<!-- 配置根据id查询数据的sql语句 -->
<select id="selectPersonById" parameterType="int" resultMap="personMap">
select * from t_person where per_id=#{perid}
</select>
例如2:
mybatis-config.xml
<typeAliases>
<package name="com.click369.mybatis.javabean"/>
</typeAliases>
PersonMapper.xml
<!-- 配置查询的返回值resultMap -->
<resultMap type="person/Person" id="personMap">
<id column="per_id" property="perid" />
<result column="per_name" property="pername"/>
<result column="per_age" property="perage"/>
<result column="per_address" property="peraddress"/>
</resultMap>
<!-- 配置根据id查询数据的sql语句 -->
<select id="selectPersonById" parameterType="int" resultMap="personMap">
select * from t_person where per_id=#{perid}
</select>
8.Sql映射文件中的select元素resultType与 resultMap属性的区别?【输出数据就是返回值】
答:
resultmap与resulttype的区别为:对象不同bai、duzhi描述不同、类型适用不同
一、对象不同
1、resultmap:resultMap如果查询出来的列名和pojo的属性名dao不一致,通过定义一个resultMap对列名和pojo属性名之间作一个映射关系。
2、resulttype:resultType使用resultType进行输出映射,只有查询出来的列名和pojo中的属性名一致,该列才可以映射成功。
二、描述不同
1、resultmap:resultMap对于一对一表连接的处理方式通常为在主表的pojo中添加嵌套另一个表的pojo,然后在mapper.xml中采用association节点元素进行对另一个表的连接处理。
2、resulttype:resultType无法查询结果映射到pojo对象的pojo属性中,根据对结构集查询遍历的需要选择使用resultType还是resultMap。
三、类型适用不同
1、resultmap:mybatis中在查询进行select映射的时候,返回类型可以用resultType,也可以用resultMap。
2、resulttype:resultType是直接表示返回类型的,而resultMap则是对外部ResultMap的引用,但是resultType跟resultMap不能同时存在。
9.Sql映射文件中的parameterType属性传入参数
答:(insert/update/delete/select元素的输入参数)
传入的参数类型:简单数据类型(string、long、double)、pojo(javaBean)类型、HashMap类型
测试传入pojo(JavaBean)类型
package com.click369.mybatis.mapper;
import java.util.List;
import java.util.Map;
import com.click369.mybatis.javabean.User;
public interface UserMapper {
boolean insertUser(User student);
}
<!-- 配置添加信息的SQL语句 -->
<insert id="insertUser" parameterType="user">
insert into t_user values(null,#{username},#{userpass},#{userage},#{useraddress})
</insert>
@Test
public void testInsertUser(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
UserMapper userMapper =session.getMapper(UserMapper.class);
for(int i=1;i<=10;i++){
User user=new User();
user.setUsername("zhangsan_"+i);
user.setUserpass("password_"+i);
user.setUserage(20+i);
user.setUseraddress("address_"+i);
boolean flag=userMapper.insertUser(user);
}
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
测试传入简单数据类型
package com.click369.mybatis.mapper;
import java.util.List;
import java.util.Map;
import com.click369.mybatis.javabean.User;
public interface UserMapper {
User selectUserById(int usreid);
}
<!-- 配置resultMap映射 -->
<resultMap type="User" id="usermap">
<id column="user_id" property="userid"/>
<result column="user_name" property="username"/>
<result column="user_pass" property="userpass"/>
<result column="user_age" property="userage"/>
<result column="user_address" property="useraddress"/>
</resultMap>
<!-- 配置根据id查询数据的sql语句 -->
<select id="selectUserById" parameterType="int" resultMap="usermap">
select * from t_user where user_id=#{userid}
</select>
@Test
public void testSelectUserById(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
UserMapper userMapper =session.getMapper(UserMapper.class);
User user=userMapper.selectUserById(5);
System.out.println(user.getUserid()+" "+user.getUsername()+" "+user.getUserpass());
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
测试传入HashMap类型
package com.click369.mybatis.mapper;
import java.util.List;
import java.util.Map;
import com.click369.mybatis.javabean.User;
public interface UserMapper {
List<User> selectUserByWhere(Map parameter);
}
<!-- 配置resultMap映射 -->
<resultMap type="User" id="usermap">
<id column="user_id" property="userid"/>
<result column="user_name" property="username"/>
<result column="user_pass" property="userpass"/>
<result column="user_age" property="userage"/>
<result column="user_address" property="useraddress"/>
</resultMap>
<!-- 配置根据条件查询数据的sql语句 -->
<select id="selectUserByWhere" parameterType="hashMap" resultMap="usermap">
select * from t_user where user_name=#{name} and user_pass=#{pass} and user_age=#{age}
</select>
@Test
public void testSelectUserByWhere(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
UserMapper userMapper =session.getMapper(UserMapper.class);
Map<String,Object> parameter=new HashMap<String,Object>();
parameter.put("name", "zhangsan_2");
parameter.put("pass", "password_2");
parameter.put("age", 22);
List<User> userlist=userMapper.selectUserByWhere(parameter);
for(User user:userlist){
System.out.println(user.getUserid()+" "+user.getUsername()+" "+user.getUserpass());
}
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
10. #{}和${}的用法
答:
#{} 实现的是sql语句的预处理,之后执行的sql中用?号代替。使用时不需要关注参数的数据类型。mybatis会自动实现数据类型转换,并且可以防止sql注入。
${}实现sql语句的拼接操作,不做数据类型转换,需要自行判断数据类型,不能防止sql注入。
总结:#{}占位符,用于参数传递。 ${}用于sql拼接
#{}----PreparedStatement接口
PreparedStatement ps=Connection.prepareStatement(“select * from t_user where user_name=? and user_pass=?”);
ps.setString(1,”zhangsan_3”);
Ps.setString(2,”password_3”);
${}----Statement接口
Statement s=Connection.createStatement();
ResultSet rs=s.executeQuery(“select * from t_user where username=’”+myname+”’ and user_pass=’”+mypass+”’”);
package com.click369.mybatis.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.click369.mybatis.javabean.User;
public interface UserMapper {
User selectUserByArgs(@Param("myname")String testname,@Param("mypass")String testpass);
}
<!-- 配置用户名和密码查询数据的sql语句 -->
<select id="selectUserByArgs" resultMap="usermap">
select * from t_user where user_name='${myname}' and user_pass='${mypass}'
</select>
@Test
public void testSelectUserByArgs(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
UserMapper userMapper =session.getMapper(UserMapper.class);
User user=userMapper.selectUserByArgs("zhangsan_3", "password_3");
System.out.println(user.getUserid()+" "+user.getUsername()+" "+user.getUserpass());
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
注意:在Mapper.xml文件中,如果使用${自定义名称} ,那么在对应的方法上需要使用@Param注解来指定这个自定义的名称。
11. 什么是动态SQL?为什么使用动态SQL?有哪些动态SQL?如何使用?
答:动态SQL它一般是根据用户输入或外部条件动态组合的SQL语句块。
动态SQL能灵活的发挥SQL强大的功能、方便的解决一些其它方法难以解决的问题。相信使用过动态SQL的人都能体会到它带来的便利,然而动态SQL有时候在执行性能(效率)上面不如静态SQL,而且使用不恰当,往往会在安全方面存在隐患(SQL 注入式攻击)。
常用的动态 SQL元素
if
choose (when, otherwise)
trim (where, set)
Foreach
11.1测试if 元素
需求:查询用户信息,如果输入了用户名,根据用户名进行模糊查找,如果没有用户名或用户名为空,那么就查询所有。
package com.click369.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.click369.javabean.PersonBean;
public interface PersonMapper {
/**
* 根据姓名查询信息
* 如果姓名不为空那么就根据本信息模糊查询,否则查询所有
*/
public List<PersonBean> selectByName1(@Param("pername")String pername);
/**
* 根据姓名查询信息
* 如果姓名不为空那么就根据本信息模糊查询,否则查询所有
*/
public List<PersonBean> selectByName2(@Param("pername")String name);
/**
* 根据姓名查询信息
* 如果姓名不为空那么就根据本信息模糊查询,否则查询所有
*/
public List<PersonBean> selectByName3(PersonBean person);
/**
* 根据姓名查询信息
* 如果姓名不为空那么就根据本信息模糊查询,否则查询所有
*/
public List<PersonBean> selectByName4(Map<String,Object> map);
}
<?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.click369.mapper.PersonMapper">
<resultMap id="personMap" type="PersonBean">
<id column="per_id" property="perid"/>
<result column="per_name" property="pername"/>
<result column="per_age" property="perage"/>
<result column="per_address" property="peraddress"/>
</resultMap>
<select id="selectByName1" parameterType="String" resultMap="personMap">
select * from t_person where 1=1
<if test="pername != null and pername !=''">
and per_name like '%${pername}%'
</if>
</select>
<select id="selectByName2" parameterType="String" resultMap="personMap">
select * from t_person where 1=1
<if test="pername != null and pername !=''">
and per_name like #{pername}
</if>
</select>
<select id="selectByName3" parameterType="PersonBean" resultMap="personMap">
select * from t_person where 1=1
<if test="pername != null and pername !=''">
and per_name like #{pername}
</if>
</select>
<select id="selectByName4" parameterType="hashMap" resultMap="personMap">
select * from t_person where 1=1
<if test="pername != null and pername !=''">
and per_name like #{pername}
</if>
</select>
</mapper>
package test;
import java.io.InputStream;
import java.util.HashMap;
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.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.click369.javabean.PersonBean;
import com.click369.mapper.PersonMapper;
public class TestMain {
@Test
public void testselectIf1(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
//List<PersonBean> personlist=mapper.selectByName1("zhang");
//List<PersonBean> personlist=mapper.selectByName1("");
List<PersonBean> personlist=mapper.selectByName1(null);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
@Test
public void testselectIf2(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
//List<PersonBean> personlist=mapper.selectByName2("%zhang%");
//List<PersonBean> personlist=mapper.selectByName2("");
List<PersonBean> personlist=mapper.selectByName2(null);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
@Test
public void testselectIf3(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
PersonBean person=new PersonBean();
//person.setPername("%zhang%");
//person.setPername("");
person.setPername(null);
List<PersonBean> personlist=mapper.selectByName3(person);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
@Test
public void testselectIf4(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
Map<String,Object> map=new HashMap<String,Object>();
//map.put("pername", "%zhang%");
//map.put("pername", "");
map.put("pername", null);
List<PersonBean> personlist=mapper.selectByName4(map);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
}
11.2测试choose (when, otherwise)元素,每次只匹配一个条件。
需求:查询用户信息,如果输入了用户名,根据用户名进行模糊查找,
如果输入了年龄,根据年龄进行匹配查找,
如果输入了地址,根据地址进行模糊查找,
如果查询条件都为空,那么就查询所有。
package com.click369.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.click369.javabean.PersonBean;
public interface PersonMapper {
/**
* 查询用户信息,如果输入了用户名,根据用户名进行模糊查找,
如果输入了年龄,根据年龄进行匹配查找,
如果输入了地址,根据地址进行模糊查找,
如果查询条件都为空,那么就查询所有。
*/
public List<PersonBean> selectByWhere1(PersonBean person);
public List<PersonBean> selectByWhere2(Map<String,Object> map);
}
<?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.click369.mapper.PersonMapper">
<resultMap id="personMap" type="PersonBean">
<id column="per_id" property="perid"/>
<result column="per_name" property="pername"/>
<result column="per_age" property="perage"/>
<result column="per_address" property="peraddress"/>
</resultMap>
<select id="selectByWhere1" parameterType="hashMap" resultMap="personMap">
select * from t_person where 1=1
<choose>
<when test="pername != null and pername != ''">
and per_name like #{pername}
</when>
<when test="perage != null and perage != 0">
and per_age=#{perage}
</when>
<when test="peraddress != null and peraddress != ''">
and per_address like #{peraddress}
</when>
</choose>
</select>
<select id="selectByWhere2" parameterType="PersonBean" resultMap="personMap">
select * from t_person where 1=1
<choose>
<when test="pername != null and pername != ''">
and per_name like #{pername}
</when>
<when test="perage != null and perage != 0">
and per_age=#{perage}
</when>
<when test="peraddress != null and peraddress != ''">
and per_address like #{peraddress}
</when>
</choose>
</select>
</mapper>
package test;
import java.io.InputStream;
import java.util.HashMap;
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.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.click369.javabean.PersonBean;
import com.click369.mapper.PersonMapper;
public class TestMain {
@Test
public void testselectChoose1(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
PersonBean person=new PersonBean();
person.setPername("");
person.setPerage(0);
person.setPeraddress("%西安%");
List<PersonBean> personlist=mapper.selectByWhere1(person);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
for(PersonBean per:personlist){
System.out.println(per.getPername());
}
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
@Test
public void testselectChoose2(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
Map<String,Object> map=new HashMap<String,Object>();
map.put("pername", null);
map.put("perage", 0);
map.put("peraddress", "%西安%");
List<PersonBean> personlist=mapper.selectByWhere2(map);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
for(PersonBean per:personlist){
System.out.println(per.getPername());
}
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
}
11.3 测试where元素
需求:根据用户名和年龄还有地址进行组合条件查询
package com.click369.mapper;
import java.util.List;
import java.util.Map;
import com.click369.javabean.PersonBean;
public interface PersonMapper {
/**
* 根据用户名和年龄还有地址进行组合条件查询
*/
public List<PersonBean> selectByWhere1(PersonBean person);
/**
* 根据用户名和年龄还有地址进行组合条件查询
*/
public List<PersonBean> selectByWhere2(Map<String,Object> map);
}
<?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.click369.mapper.PersonMapper">
<resultMap id="personMap" type="PersonBean">
<id column="per_id" property="perid"/>
<result column="per_name" property="pername"/>
<result column="per_age" property="perage"/>
<result column="per_address" property="peraddress"/>
</resultMap>
<select id="selectByWhere1" parameterType="hashMap" resultMap="personMap">
select * from t_person where 1=1
<if test="pername != null and pername != ''">
and per_name like #{pername}
</if>
<if test="perage != null and perage != 0">
and per_age=#{perage}
</if>
<if test="peraddress != null and peraddress != ''">
and per_address like #{peraddress}
</if>
</select>
<select id="selectByWhere2" parameterType="PersonBean" resultMap="personMap">
select * from t_person
<where>
<if test="pername != null and pername != ''">
and per_name like #{pername}
</if>
<if test="perage != null and perage != 0">
and per_age=#{perage}
</if>
<if test="peraddress != null and peraddress != ''">
and per_address like #{peraddress}
</if>
</where>
</select>
</mapper>
package test;
import java.io.InputStream;
import java.util.HashMap;
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.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.click369.javabean.PersonBean;
import com.click369.mapper.PersonMapper;
public class TestMain {
@Test
public void testselectChoose1(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
PersonBean person=new PersonBean();
//where 1=1 and per_name like '%li%'
person.setPername("%li%");
//where 1=1 and per_name like '%li%' and per_age=24
person.setPerage(24);
//where 1=1 and per_name like '%li%' and per_age=24 and per_address like '%北%'
person.setPeraddress("%北%");
List<PersonBean> personlist=mapper.selectByWhere1(person);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
for(PersonBean per:personlist){
System.out.println(per.getPername());
}
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
@Test
public void testselectChoose2(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
Map<String,Object> map=new HashMap<String,Object>();
map.put("pername", "%li%");
map.put("perage", 24);
map.put("peraddress", "%京%");
List<PersonBean> personlist=mapper.selectByWhere2(map);
//commit():提交执行
session.commit();
System.out.println(personlist.size());
for(PersonBean per:personlist){
System.out.println(per.getPername());
}
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
}
11.4 测试set元素
需求:修改用户的信息
package com.click369.mapper;
import com.click369.javabean.PersonBean;
public interface PersonMapper {
/**
* 修改用户的信息
*/
public void update1(PersonBean person);
}
<?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.click369.mapper.PersonMapper">
<update id="update1" parameterType="PersonBean" >
update t_person
<set>
<if test="pername != null and pername != ''">
per_name=#{pername},
</if>
<if test="perage != null and perage != 0">
per_age=#{perage},
</if>
<if test="peraddress != null and peraddress != ''">
per_address=#{peraddress}
</if>
</set>
<where>
per_id=#{perid}
</where>
</update>
</mapper>
package test;
import java.io.InputStream;
import java.util.HashMap;
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.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.click369.javabean.PersonBean;
import com.click369.mapper.PersonMapper;
public class TestMain {
@Test
public void testUpdate1(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
PersonBean person=new PersonBean();
person.setPerid(3);
//update t_person set per_name='zhaosi',per_age=44,per_address='test' ......
person.setPername("zhaosi");
person.setPerage(44);
person.setPeraddress("test");
mapper.update1(person);
//commit():提交执行
session.commit();
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
}
11.5 测试 foreach
需要:1.根据id批量删除数据
2.批量添加数据
package com.click369.mapper;
import java.util.List;
import com.click369.javabean.PersonBean;
public interface PersonMapper {
/**
* 根据id批量删除数据
*/
public void delete(List<Integer> idlist);
/**
* 批量添加数据
*/
public void insert(List<PersonBean> personlist);
}
<?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.click369.mapper.PersonMapper">
<delete id="delete" parameterType="java.util.List">
delete from t_person where per_id in
<!-- (2,3,4,6) -->
<foreach collection="list" item="id" open="(" close=")" separator="," >
#{id}
</foreach>
</delete>
<insert id="insert" parameterType="java.util.List">
insert into t_person values
<foreach collection="list" item="person" separator=",">
(null,#{person.pername},#{person.perage},#{person.peraddress})
</foreach>
</insert>
</mapper>
package test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.click369.javabean.PersonBean;
import com.click369.mapper.PersonMapper;
public class TestMain {
@Test
public void testdelete(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
List<Integer> idlist=new ArrayList<Integer>();
idlist.add(7);
idlist.add(8);
idlist.add(9);
mapper.delete(idlist);
//commit():提交执行
session.commit();
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
@Test
public void testinsert(){
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session=sqlSessionFactory.openSession();
PersonMapper mapper=session.getMapper(PersonMapper.class);
List<PersonBean> personlist=new ArrayList<PersonBean>();
PersonBean per1=new PersonBean();
per1.setPername("test");
per1.setPerage(23);
per1.setPeraddress("西安");
PersonBean per2=new PersonBean();
per2.setPername("value");
per2.setPerage(24);
per2.setPeraddress("北京");
PersonBean per3=new PersonBean();
per3.setPername("testvalue");
per3.setPerage(25);
per3.setPeraddress("shanghai");
personlist.add(per1);
personlist.add(per2);
personlist.add(per3);
mapper.insert(personlist);
//commit():提交执行
session.commit();
}catch(Exception e){
//close():关闭sqlsession
e.printStackTrace();
}
}
}
12. mybatis的缓存处理?
答: 缓存提高程序的性能,提高数据的读取速度。现在的互联网时代,几乎所有的网络相关的产品都用到了缓存机制。
mybatis框架提供了两种缓存机制:一级缓存和二级缓存,默认开启一级缓存。
一级缓存
mybatis默认是开启一级缓存。
当使用openSession方法之后,获取到SqlSession对象,只要使用当前这个SqlSession对象进行数据库的操作,那么执行的是相同的 sql(相同的语句和参数),mybatis不进行sql的执行,而是从缓存中找到对应的数据返回。
mybatis的一级缓存:属于SqlSession级别的(会话级别的)。当使用mybatis执行查询时,mybatis会先到一级缓存中找有没有需要查询的数据,如果有就返回,而不会执行sql去数据库中查询。
强制不缓存—openSession(true);(每次去数据库查询数据)
二级缓存
mybatis的二级缓存是当前这个Mapper文件的namespace范围。
也就是同一个namespace下的所有查询语句可以共享二级缓存中的数据。
二级缓存是可以跨越多个session的。只要它们是同一个mapper下的namespace即可。
要开启二级缓存,你需要在你的 SQL 映射(Mapper)文件中添加一行:
二级缓存是在session关闭之前将数据写入到缓存区域,并且要求数据对象必须可序列化。
13. mybatis的高级查询是一对一查询操作有几种方式,每一种如何配置?
答:2种
用户基本信息表
create table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_age int,
user_address varchar(30)
);
用户身份证信息表
create table t_card(
card_id int primary key auto_increment,
user_id_fk int not null unique,
card_code varchar(20),
card_year int
);
ALTER TABLE t_card ADD CONSTRAINT user_id_fk FOREIGN KEY(user_id_fk) REFERENCES t_user(user_id);
insert into t_user values(null,'zhangsan',23,'西安');
insert into t_card values(null,1,'111111111',20);
package com.click369.mybatis.javabean;
import java.io.Serializable;
/**
* 保存用户信息的java实体类
* @author Administrator
*/
@SuppressWarnings("serial")
public class User implements Serializable{
private int userid;
private String username;
private int userage;
private String useraddress;
private Card myCard;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getUserage() {
return userage;
}
public void setUserage(int userage) {
this.userage = userage;
}
public String getUseraddress() {
return useraddress;
}
public void setUseraddress(String useraddress) {
this.useraddress = useraddress;
}
public Card getMyCard() {
return myCard;
}
public void setMyCard(Card myCard) {
this.myCard = myCard;
}
}
注意:需要在User类中添加一个Card类的控制对象。
package com.click369.mybatis.javabean;
import java.io.Serializable;
/**
* 保存用户身份证信息的java实体类
* @author Administrator
*/
@SuppressWarnings("serial")
public class Card implements Serializable{
private int cardid;
private int useridfk;
private String cardcode;
private int cardyear;
public int getCardid() {
return cardid;
}
public void setCardid(int cardid) {
this.cardid = cardid;
}
public int getUseridfk() {
return useridfk;
}
public void setUseridfk(int useridfk) {
this.useridfk = useridfk;
}
public String getCardcode() {
return cardcode;
}
public void setCardcode(String cardcode) {
this.cardcode = cardcode;
}
public int getCardyear() {
return cardyear;
}
public void setCardyear(int cardyear) {
this.cardyear = cardyear;
}
}
package com.click369.mybatis.mapper;
import com.click369.mybatis.javabean.User;
/**
* 用户信息的数据访问接口
* 注意:数据库操作方法是不能有重载,后面配置MyBatisSQL映射文件的时候需要使用这个方法名称
* @author Administrator
*/
public interface UserMapper {
/**
* 根据用户id得到用户和用户身份证信息
* @param userid
* @return
*/
User selectUserById(int userid);
}
嵌套resultMap 1
<?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.click369.mybatis.mapper.UserMapper">
<!-- 配置查询的结果集映射 -->
<resultMap type="User" id="userMap">
<id column="user_id" property="userid"></id>
<result column="user_name" property="username"/>
<result column="user_age" property="userage"/>
<result column="user_address" property="useraddress"/>
<association property="myCard" javaType="Card" resultMap="cardMap"></association>
</resultMap>
<resultMap type="Card" id="cardMap">
<id column="card_id" property="cardid"></id>
<result column="user_id_fk" property="useridfk"/>
<result column="card_code" property="cardcode"/>
<result column="card_year" property="cardyear"/>
</resultMap>
<!-- 根据用户id得到用户和用户身份证信息 -->
<select id="selectUserById" parameterType="int" resultMap="userMap">
select u.user_id,u.user_name,u.user_age,u.user_address,
c.card_id,c.user_id_fk,c.card_code,c.card_year
from t_user as u inner join t_card as c
on u.user_id=c.user_id_fk where u.user_id=#{userid};
</select>
</mapper>
嵌套resultMap 2
<?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.click369.mybatis.mapper.UserMapper">
<!--嵌套resultMap 2 -->
<resultMap type="User" id="userMap">
<id column="user_id" property="userid"></id>
<result column="user_name" property="username"/>
<result column="user_age" property="userage"/>
<result column="user_address" property="useraddress"/>
<association property="myCard" javaType="Card">
<id column="card_id" property="cardid"></id>
<result column="user_id_fk" property="useridfk"/>
<result column="card_code" property="cardcode"/>
<result column="card_year" property="cardyear"/>
</association>
</resultMap>
<select id="selectUserById" parameterType="int" resultMap="userMap">
select u.user_id,u.user_name,u.user_age,u.user_address,
c.card_id,c.user_id_fk,c.card_code,c.card_year
from t_user as u inner join t_card as c
on u.user_id=c.user_id_fk where u.user_id=#{userid};
</select>
</mapper>
package com.click369.javabean;
/**
* 保存用户身份证信息的javabean类
* @author Administrator
*/
import java.io.Serializable;
public class CardBean implements Serializable{
private int cardid;
private int useridfk;
private String cardcode;
private int cardyear;
//保存用户信息的成员变量
private UserBean userBean;
public int getCardid() {
return cardid;
}
public void setCardid(int cardid) {
this.cardid = cardid;
}
public int getUseridfk() {
return useridfk;
}
public void setUseridfk(int useridfk) {
this.useridfk = useridfk;
}
public String getCardcode() {
return cardcode;
}
public void setCardcode(String cardcode) {
this.cardcode = cardcode;
}
public int getCardyear() {
return cardyear;
}
public void setCardyear(int cardyear) {
this.cardyear = cardyear;
}
public UserBean getUserBean() {
return userBean;
}
public void setUserBean(UserBean userBean) {
this.userBean = userBean;
}
}
package com.click369.mapper;
import com.click369.javabean.CardBean;
/**
* 用户身份证信息的数据库访问接口
* @author Administrator
*/
public interface CardMapper {
/**
* 查询用户身份证信息,并且得到用户的基本信息
*/
CardBean selectCardAndUserByCardId(int cardid);
}
<?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.click369.mapper.CardMapper">
<!-- 嵌套select -->
<resultMap type="CardBean" id="cardMap">
<id column="card_id" property="cardid"></id>
<result column="user_id_fk" property="useridfk"/>
<result column="card_code" property="cardcode"/>
<result column="card_year" property="cardyear"/>
<association property="userBean" javaType="UserBean" column="user_id_fk" select="getUserByUserId"></association>
</resultMap>
<select id="selectCardAndUserByCardId" parameterType="int" resultMap="cardMap">
select * from t_card where card_id=#{cardid}
</select>
<resultMap type="UserBean" id="userMap">
<id column="user_id" property="userid"></id>
<result column="user_name" property="username"/>
<result column="user_age" property="userage"/>
<result column="user_address" property="useraddress"/>
</resultMap>
<select id="getUserByUserId" parameterType="int" resultMap="userMap">
select * from t_user where user_id=#{user_id_fk}
</select>
</mapper>
@Test
public void testselectCardAndUserByCardId(){
SqlSession session=null;
try{
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
session=sqlSessionFactory.openSession();
CardMapper cardMapper=session.getMapper(CardMapper.class);
CardBean card=cardMapper.selectCardAndUserByCardId(1);
session.commit();
System.out.println(card.getCardcode()+" "+card.getUserBean().getUsername());
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
14. mybatis的高级查询是一对多查询操作有几种方式,每一种如何配置?
答:
创建班级表
create table t_class(
class_id int primary key auto_increment,
class_code varchar(20),
class_name varchar(20)
);
#创建学生表
create table t_student(
stu_id int primary key auto_increment,
class_id_fk int not null,
stu_name varchar(20),
stu_age int,
stu_address varchar(20)
);
#添加外键关系
ALTER TABLE t_student ADD CONSTRAINT class_id_fk FOREIGN KEY(class_id_fk) REFERENCES t_class(class_id);
#测试数据
insert into t_class values(null,'20190123','javaEE班');
insert into t_student values(null,1,'zhangsan',23,'西安');
insert into t_student values(null,1,'lisi',24,'北京');
insert into t_student values(null,1,'wangwu',25,'上海');
注意:外键通常都是由多方维护。
package com.click369.mybatis.javabean;
import java.io.Serializable;
import java.util.List;
/**
* 保存班级信息的java实体类
* @author Administrator
*
*/
@SuppressWarnings("serial")
public class ClassBean implements Serializable{
private int classid;
private String classcode;
private String classname;
private List<StudentBean> studentlist;
public int getClassid() {
return classid;
}
public void setClassid(int classid) {
this.classid = classid;
}
public String getClasscode() {
return classcode;
}
public void setClasscode(String classcode) {
this.classcode = classcode;
}
public String getClassname() {
return classname;
}
public void setClassname(String classname) {
this.classname = classname;
}
public List<StudentBean> getStudentlist() {
return studentlist;
}
public void setStudentlist(List<StudentBean> studentlist) {
this.studentlist = studentlist;
}
}
package com.click369.mybatis.javabean;
import java.io.Serializable;
/**
* 保存学生信息的java实体类
* @author Administrator
*
*/
@SuppressWarnings("serial")
public class StudentBean implements Serializable{
private int stuid;
private int classidfk;
private String stuname;
private int stuage;
private String stuaddress;
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public int getClassidfk() {
return classidfk;
}
public void setClassidfk(int classidfk) {
this.classidfk = classidfk;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public int getStuage() {
return stuage;
}
public void setStuage(int stuage) {
this.stuage = stuage;
}
public String getStuaddress() {
return stuaddress;
}
public void setStuaddress(String stuaddress) {
this.stuaddress = stuaddress;
}
}
package com.click369.mybatis.mapper;
import com.click369.mybatis.javabean.ClassBean;
/**
* 班级信息的数据访问接口
* @author Administrator
*/
public interface ClassBeanMapper {
/**
* 根据班级编号查询班级信息
* @param userid
* @return
*/
ClassBean selectClassBeanById(int classid);
}
<?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.click369.mybatis.mapper.ClassBeanMapper">
<!--嵌套resultMap 1 -->
<resultMap type="ClassBean" id="classMap">
<id column="class_id" property="classid"></id>
<result column="class_code" property="classcode"/>
<result column="class_name" property="classname"/>
<collection property="studentlist" ofType="StudentBean" resultMap="studentMap"></collection>
</resultMap>
<resultMap type="StudentBean" id="studentMap">
<id column="stu_id" property="stuid"></id>
<result column="class_id_fk" property="classidfk"/>
<result column="stu_name" property="stuname"/>
<result column="stu_age" property="stuage"/>
<result column="stu_address" property="stuaddress"/>
</resultMap>
<select id="selectClassBeanById" parameterType="int" resultMap="classMap">
select c.class_id,c.class_code,c.class_name,
s.stu_id,s.class_id_fk,s.stu_name,s.stu_age,s.stu_address
from t_class as c inner join t_student as s
on c.class_id=s.class_id_fk where c.class_id=#{classid}
</select>
</mapper>
<?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.click369.mybatis.mapper.ClassBeanMapper">
<!--嵌套resultMap 2 -->
<resultMap type="ClassBean" id="classMap">
<id column="class_id" property="classid"></id>
<result column="class_code" property="classcode"/>
<result column="class_name" property="classname"/>
<collection property="studentlist" ofType="StudentBean">
<id column="stu_id" property="stuid"></id>
<result column="class_id_fk" property="classidfk"/>
<result column="stu_name" property="stuname"/>
<result column="stu_age" property="stuage"/>
<result column="stu_address" property="stuaddress"/>
</collection>
</resultMap>
<select id="selectClassBeanById" parameterType="int" resultMap="classMap">
select c.class_id,c.class_code,c.class_name,
s.stu_id,s.class_id_fk,s.stu_name,s.stu_age,s.stu_address
from t_class as c inner join t_student as s
on c.class_id=s.class_id_fk where c.class_id=#{classid}
</select>
</mapper>
<?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.click369.mybatis.mapper.ClassBeanMapper">
<!-- 嵌套select -->
<resultMap type="ClassBean" id="classMap">
<id column="class_id" property="classid"></id>
<result column="class_code" property="classcode"/>
<result column="class_name" property="classname"/>
<collection property="studentlist" column="class_id" ofType="StudentBean" select="getStudentInfo"></collection>
</resultMap>
<select id="selectClassBeanById" parameterType="int" resultMap="classMap">
select * from t_class where class_id=#{classid}
</select>
<resultMap type="StudentBean" id="studentMap">
<id column="stu_id" property="stuid"></id>
<result column="class_id_fk" property="classidfk"/>
<result column="stu_name" property="stuname"/>
<result column="stu_age" property="stuage"/>
<result column="stu_address" property="stuaddress"/>
</resultMap>
<select id="getStudentInfo" parameterType="int" resultMap="studentMap">
select * from t_student where class_id_fk=#{classid}
</select>
</mapper>
@Test
public void testSelectClassById(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
ClassBeanMapper classMapper =session.getMapper(ClassBeanMapper.class);
ClassBean classbean=classMapper.selectClassBeanById(1);
List<StudentBean> stulist=classbean.getStudentlist();
for(StudentBean stu:stulist){
System.out.println(classbean.getClassid()+
" "+classbean.getClasscode()+
" "+classbean.getClassname()+
" "+stu.getStuname()+
" "+stu.getStuage()+
" "+stu.getStuaddress());
}
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
}
15. mybatis的高级查询是多对多查询操作如何配置?
答: 需求:根据角色id查询角色信息,并且得到角色所属组的内容
#创建角色表
create table t_role(
role_id int primary key auto_increment,
role_name varchar(20),
role_info varchar(20)
);
#创建角色所属组表
create table t_group(
group_id int primary key auto_increment,
group_name varchar(20),
group_info varchar(20)
);
#创建一个维护数据关系的中间表
create table t_role_group(
id int primary key auto_increment,
role_id_fk int,
group_id_fk int
);
#创建外键
ALTER TABLE t_role_group ADD CONSTRAINT role_id_fk FOREIGN KEY(role_id_fk) REFERENCES t_role(role_id);
ALTER TABLE t_role_group ADD CONSTRAINT group_id_fk FOREIGN KEY(group_id_fk) REFERENCES t_group(group_id);
#添加测试数据
insert into t_role values(null,'管理员','管理其他用户');
insert into t_role values(null,'普通用户','具有普通权限');
insert into t_group values(null,'CRM组','负责开发维护CRM项目');
insert into t_group values(null,'ERP组','负责开发维护ERP项目');
insert into t_role_group values(null,1,1);
insert into t_role_group values(null,1,2);
insert into t_role_group values(null,2,1);
insert into t_role_group values(null,2,2);
package com.click369.mybatis.javabean;
import java.io.Serializable;
import java.util.List;
/**
* 保存角色信息的java实体类
* @author Administrator
*/
@SuppressWarnings("serial")
public class RoleBean implements Serializable{
private int roleid;
private String rolename;
private String roleinfo;
private List<GroupBean> grouplist;
public int getRoleid() {
return roleid;
}
public void setRoleid(int roleid) {
this.roleid = roleid;
}
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public String getRoleinfo() {
return roleinfo;
}
public void setRoleinfo(String roleinfo) {
this.roleinfo = roleinfo;
}
public List<GroupBean> getGrouplist() {
return grouplist;
}
public void setGrouplist(List<GroupBean> grouplist) {
this.grouplist = grouplist;
}
}
package com.click369.mybatis.javabean;
import java.io.Serializable;
import java.util.List;
/**
* 保存组信息的java实体类
* @author Administrator
*/
@SuppressWarnings("serial")
public class GroupBean implements Serializable{
private int groupid;
private String groupname;
private String groupinfo;
private List<RoleBean> rolelist;
public int getGroupid() {
return groupid;
}
public void setGroupid(int groupid) {
this.groupid = groupid;
}
public String getGroupname() {
return groupname;
}
public void setGroupname(String groupname) {
this.groupname = groupname;
}
public String getGroupinfo() {
return groupinfo;
}
public void setGroupinfo(String groupinfo) {
this.groupinfo = groupinfo;
}
public List<RoleBean> getRolelist() {
return rolelist;
}
public void setRolelist(List<RoleBean> rolelist) {
this.rolelist = rolelist;
}
}
package com.click369.mybatis.mapper;
import com.click369.mybatis.javabean.RoleBean;
/**
* 角色信息的数据访问接口
*@author Administrator
*/
public interface RoleBeanMapper {
/**
* 根据角色id查询角色信息
* @param userid
* @return
*/
RoleBean selectRoleBeanById(int roleid);
}
package com.click369.mybatis.mapper;
import com.click369.mybatis.javabean.GroupBean;
/**
* 组信息的数据访问接口
*@author Administrator
*/
public interface GroupBeanMapper {
/**
* 根据组id查询组信息
* @param userid
* @return
*/
GroupBean selectGroupBeanById(int groupid);
}
<?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.click369.mybatis.mapper.RoleBeanMapper">
<!--嵌套resultMap 1 -->
<resultMap type="RoleBean" id="roleMap">
<id column="role_id" property="roleid"/>
<result column="role_name" property="rolename"/>
<result column="role_info" property="roleinfo"/>
<collection property="grouplist" ofType="GroupBean" resultMap="groupMap"></collection>
</resultMap>
<resultMap type="GroupBean" id="groupMap">
<id column="group_id" property="groupid"/>
<result column="group_name" property="groupname"/>
<result column="group_info" property="groupinfo"/>
</resultMap>
<select id="selectRoleBeanById" parameterType="int" resultMap="roleMap">
select r.role_id,r.role_name,r.role_info,
g.group_id,g.group_name,g.group_info
from t_role as r inner join t_role_group as rg
on r.role_id = rg.role_id_fk
inner join t_group as g
on g.group_id = rg.group_id_fk
where r.role_id=#{roleid};
</select>
</mapper>
<?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.click369.mybatis.mapper.GroupBeanMapper">
<resultMap type="GroupBean" id="groupMap">
<id column="group_id" property="groupid"/>
<result column="group_name" property="groupname"/>
<result column="group_info" property="groupinfo"/>
<collection property="rolelist" ofType="RoleBean" resultMap="roleMap"></collection>
</resultMap>
<resultMap type="RoleBean" id="roleMap">
<id column="role_id" property="roleid"/>
<result column="role_name" property="rolename"/>
<result column="role_info" property="roleinfo"/>
</resultMap>
<select id="selectGroupBeanById" parameterType="int" resultMap="groupMap">
select g.group_id,g.group_name,g.group_info,
r.role_id,r.role_name,r.role_info
from t_group as g inner join t_role_group as rg
on g.group_id = rg.group_id_fk
inner join t_role as r
on r.role_id = rg.role_id_fk
where g.group_id=#{groupid}
</select>
</mapper>
<!-- 配置MyBatis数据访问接口的SQL映射文件路径 -->
<mappers>
<mapper resource="com/click369/mybatis/mapper/RoleBeanMapper.xml"/>
<mapper resource="com/click369/mybatis/mapper/GroupBeanMapper.xml"/>
</mappers>
@Test
public void testSelectRoleById(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
RoleBeanMapper roleMapper =session.getMapper(RoleBeanMapper.class);
RoleBean rolebean=roleMapper.selectRoleBeanById(1);
List<GroupBean> grouplist=rolebean.getGrouplist();
for(GroupBean group:grouplist){
System.out.println(rolebean.getRolename()+
" "+rolebean.getRoleinfo()+
" "+group.getGroupname()+
" "+group.getGroupinfo());
}
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
@Test
public void testSelectGroupById(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
GroupBeanMapper groupMapper =session.getMapper(GroupBeanMapper.class);
GroupBean groupbean=groupMapper.selectGroupBeanById(1);
List<RoleBean> rolelist=groupbean.getRolelist();
for(RoleBean role:rolelist){
System.out.println(groupbean.getGroupname()+
" "+groupbean.getGroupinfo()+
" "+role.getRolename()+
" "+role.getRoleinfo());
}
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}
16. MyBatis提供的分页查询原理?
答:mybatis的插件机制,本质就拦截指定的查询操作,然后在查询的操作添加相关的逻辑。
使用PageHelper实现分页
16.1Pom中添加分页插件的依赖
<!-- 配置分页依赖 -->
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
16.2在MyBatis的核心配置文件中配置插件
<!-- 配置分页插件的拦截器 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 配置方言,使用的是那个库 ,在PageHelper5.0之后 不需要配置-->
<!-- <property name="dialect" value="mysql"/> -->
</plugin>
</plugins>
数据访问接口
List<User> findUserByPage();
Mapper映射
<resultMap type="User" id="usermap">
<id column="user_id" property="userid"/>
<result column="user_name" property="username"/>
<result column="user_age" property="userage"/>
<result column="user_address" property="useraddress"/>
</resultMap>
<select id="findUserByPage" resultMap="usermap">
select * from t_user
</select>
测试分页
@Test
public void testSelectUserByPage(){
SqlSession session=null;
try{
//加载mybatis配置文件
InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory接口对象
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession接口对象【有增删该查方法】
session=sqlSessionFactory.openSession();
//通过SqlSession接口对象得到数据访问接口对象
UserMapper userMapper =session.getMapper(UserMapper.class);
// 设置分页的起始页码和每页的显示数据的行数
//startPage(1, 4);
//参数1--起始页码
//参数2--显示数据的行数
PageHelper.startPage(2,10);
List<User> userList=userMapper.findUserByPage();
// 获取分页的详细信息
PageInfo<User> info = new PageInfo<User>(userList);
System.out.println("当前页=="+info.getPageNum());
System.out.println("每页的数量=="+info.getPageSize());
System.out.println("当前页的数量=="+info.getSize());
System.out.println("总记录数=="+info.getTotal());
System.out.println("总页数=="+info.getPages());
for(User user:info.getList()){
System.out.println(user.getUserid()+" "+user.getUsername());
}
//提交执行
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
session.close();
}
}