Mybatis参考文档 http://www.mybatis.org/mybatis-3/zh/index.html
使用Mybatis完成Dao层功能
1、导入jar包“mybatis-3.2.7.jar”
2、在src下创建配置文件“mybatis-config.xml”,在该文件中配置与数据库连接的内容
3、添加提示信息
在“windows”的“preferences”中打开如下页面:
Location:mybatis-3-config.dtd在电脑中的路径
Key:-//mybatis.org//DTD Config 3.0//EN
2中的代码如下:
<?xml version="1.0"?>
<!DOCTYPE configuration PUBLIC
"-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 默认与default的数据库建立连接 -->
<environments default="cs">
<!-- id为要连接的数据库,可同时配置多个 -->
<environment id="cs">
<!-- 事务管理,连接方式:JDBC/MANAGED -->
<transactionManager type="JDBC"></transactionManager>
<!-- 数据源:POOLED/UNPOOLED -->
<dataSource type="POOLED">
<!-- 在缓冲池中配置与数据库连接的参数 -->
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/oa" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 配置映射信息 -->
<mappers>
<mapper resource="com/xmx/oa/service/IPersonService-mapper.xml"></mapper>
</mappers>
</configuration>
4、创建MybatisUtils类解析mybatis-config.xml
代码:
package com.xmx.mybatis;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisUtils {
//定义工厂
public static SqlSessionFactory sqlSessionfactory;
static{
try {
//与配置文件建立连接
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//生产工厂
sqlSessionfactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionfactory.openSession();
}
public void closeSqlSession(SqlSession sqlSession){
if(sqlSession!=null){
sqlSession.close();
}
}
}
5、创建业务逻辑层“com.xmx.oa.model.entity”--“Person.java”
package com.xmx.oa.model.entity;
public class Person {
int id;
String name;
String sex;
int age;
String phone;
public Person(String name, String sex, String phone) {
super();
this.name = name;
this.sex = sex;
this.phone = phone;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Person(int id, String name, String sex, int age, String phone) {
super();
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.phone = phone;
}
public Person() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", sex=" + sex + ", age="
+ age + ", phone=" + phone + "]";
}
}
6、创建sql映射的xml文件
创建接口“IPersonService.java”:
package com.xmx.oa.service;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.xmx.oa.model.entity.Person;
public interface IPersonService {
public int addPerson(Person p);
public int deletePerson(int id);
public int deleteMulPerson(@Param("id") int id[]);
public int updatePerson(Person p);
public List<Person> findAllPerson();
public Person findById(int id);
public List<Person> findVaguePerson(String name);
public List<Person> findByPage(int currentpage,int size);
}
其中:(以下内容可结合后面的步骤来阅读)
(1)当有有2个(及以上)的参数时,有3种处理方法
以分页查询 public List<Person> findByPage(int currentpage,int size); 为例:
i.把这两个参数封装到对象中,传递一个对象
在“Person.java”中,追加定义两个变量:
int currentpage;
int size;
并生成他们的构造方法:
public Person(int currentpage, int size) {
super();
this.currentpage = currentpage;
this.size = size;
}
在“IPersonService.java”中修改该方法参数为“Person p”:
public List<Person> findByPage(Person p);
“IPersonService-mapper.xml”中的写法:
<select id="findByPage" resultType="com.xmx.oa.model.entity.Person"
parameterType="com.xmx.oa.model.entity.Person">
select * from users limit #{currentpage}, #{size}
</select>
测试代码:
@Test
//分页查询
public void test7(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
Person p1 = new Person(1,3);
List<Person>list = service.findByPage(p1);
for(Person p:list){
System.out.println(p.toString());
}
}
ii.在参数前加上一个注入@Param("")
“IPersonService.java”中,导入相应的包,方法的参数部分为:
public List<Person> findByPage(@Param("currentpage")int currentpage,
@Param("size")int size);
“IPersonService-mapper.xml”中的写法:
<!-- 参数类型为map -->
<select id="findByPage" resultType="com.xmx.oa.model.entity.Person" parameterType="map">
select * from users limit #{currentpage}, #{size}
</select>
测试代码:
@Test
//分页查询
public void test7(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
List<Person>list = service.findByPage(2, 3);
for(Person p:list){
System.out.println(p.toString());
}
}
iii.修改sql语句
“IPersonService.java”中保持不变:
public List<Person> findByPage(int currentpage,int size);
“IPersonService-mapper.xml”中的写法:
<select id="findByPage" resultType="com.xmx.oa.model.entity.Person">
select * from users limit #{0}, #{1}
</select>
测试代码同ii。
(2)参数为数组时的写法
如删除多组方法,使用注入:
public int deleteMulPerson(@Param("id") int id[]);
“IPersonService-mapper.xml”中的写法:
<delete id="deleteMulPerson">
delete from users where id in
<foreach collection="id" item="v" open="(" close=")" separator=",">
#{v}
</foreach>
</delete>
删除在某个范围内(“in”)的id。
<foreach>语句表示id数组中的所有元素v,以“(”开头,以“)”结尾,元素直接分隔符为“,”;
如:数组中元素为“2,4”时,对应的sql语句为:delete from users where id in (2,4)
测试代码:
@Test
//批量删除
public void testDel(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
int id[] = {2,3};
int i = service.deleteMulPerson(id);
//提交事务,不然写不进数据库
sqlSession.commit();
System.out.println(i);
}
在com.xmx.oa.service下面创建sql映射文件,名为“IPersonService-mapper.xml”:
同样和“mapper”一样要先添加提示信息,以方便后续代码的编写。
<?xml version="1.0"?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xmx.oa.service.IPersonService">
<insert id="addPerson" parameterType="com.xmx.oa.model.entity.Person">
insert into users values (#{id},#{name},#{sex},#{age},#{phone})
</insert>
<delete id="deletePerson" parameterType="int">
delete from u
sers where id=#{id}
</delete>
<delete id="deleteMulPerson">
delete from users where id in
<foreach collection="id" item="v" open="(" close=")" separator=",">
#{v}
</foreach>
</delete>
<update id="updatePerson" parameterType="com.xmx.oa.model.entity.Person">
update users set name=#{name}, sex=#{sex}, age=#{age}, phone=#{phone} where id=#{id}
</update>
<select id="findAllPerson" resultType="com.xmx.oa.model.entity.Person">
select * from users
</select>
<select id="findById" resultType="com.xmx.oa.model.entity.Person" parameterType="int">
select * from users where id=#{id}
</select>
<select id="findVaguePerson" resultType="com.xmx.oa.model.entity.Person" parameterType="String">
select * from users where name like concat('%',#{name},'%')
</select>
<select id="findByPage" resultType="com.xmx.oa.model.entity.Person">
select * from users limit #{0}, #{1}
</select>
</mapper>
其中:
(1)<mapper></mapper>
namespace:该映射文件对应的接口类 |
接口中的方法的返回值一定为int,不能为boolean |
id:对应你的接口中的方法名 |
parameterType:接口中的方法参数类型 |
resultType:接口中方法的返回类型,insert和update中没有该属性 |
#(属性值):得到你传递的参数值 |
sql语句后不要加分号 |
(2)各种sql语句方法
增加 | <insert></insert> | insert into users values (#{id},#{name},#{sex},#{age},#{phone}) |
删除 | <delete></delete> | delete from users where id=#{id} //单个删除 delete from users where id in (n,m) //批量删除 |
修改 | <update></update> | update users set name=#{name}, sex=#{sex}, age=#{age}, phone=#{phone} where id=#{id} |
(3)resultType详解
resultType:表示查询后封装成什么类型来显示。
当表的字段名和代码的属性名一致时(name=name),可以直接查:
select * from users
不一致时查询会报空指针,例如字段名为“cid, cname…”,而表名为“id, name…”:
有2种处理方法:
i.指定别名
select id as cid, name as cname, sex as csex, age as cage, phone as cphone from users
ii.用resultMap来配置
<select id="findAllPerson" resultType="com.xmx.oa.model.entity.Person">
select * from users
</select>
<resultMap type="com.xmx.oa.model.entity.Person" id="rm">
<result column="id" property="cid" />
<result column="name" property="cname" />
</resultMap>
type | 所对应的实体类 |
id | 所起的名字,在sql语句中用到 |
column | 表中字段名 |
property | 代码中属性名 |
(4) 模糊匹配的sql写法
i.常规方法,双“%”表示模糊匹配:
<select id="findVaguePerson" resultType="com.xmx.oa.model.entity.Person" parameterType="String">
select * from users where name like concat('%',#{name},'%')
</select>
concat()连接字符串。
ii.加入“ statementType="STATEMENT" ”,用“$”取值:
<select id="findVaguePerson" resultType="com.xmx.oa.model.entity.Person"
parameterType="String" statementType="STATEMENT">
select * from users where name like '${'%'+'name'+'%'}'
</select>
statementType:标记使用什么的对象操作SQL语句。
取值说明:
1、STATEMENT:直接操作sql,不进行预编译,获取数据:${}
2、PREPARED:预处理,参数,进行预编译,获取数据:#{} 默认
3、CALLABLE:执行存储过程
显示的sql语句为:
select * from users where name like '%name%'
该语句仅是匹配姓名中出现“name”的人,而不是name这个属性的值。
如果写成以下几种之一:
select * from users where name like '${'%'+name+'%'}'
select * from users where name like '${name}'
select * from users where name like ${name}
会发生报错:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'name' in 'class java.lang.String'
### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'name' in 'class java.lang.String'
如果写成:
select * from users where name like ${'name'}
不会报错,但会查询出所有的人物信息,而不是按姓名模糊查询。
如果写成:
select * from users where name like '${'name'}'
事实上的sql语句为:
select * from users where name like 'name'
虽然是“like”,但是没有通配符,实际上是精确匹配,而数据库中并没有姓名为“name”的人,因此结果为空。
暂时该方法有没有正确写法,又该怎么写还不清楚。
7、所有测试方法
package com.xmx.mybatis.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.xmx.mybatis.MybatisUtils;
import com.xmx.oa.model.entity.Person;
import com.xmx.oa.service.IPersonService;
public class MybatisTest {
@Test
//添加
public void test1(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
Person p = new Person(2,"梁山伯","男",20,"1231413");
int i = service.addPerson(p);
//提交事务,不然写不进数据库
sqlSession.commit();
System.out.println(i);
}
@Test
//删除
public void test2(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
int i = service.deletePerson(2);
//提交事务,不然写不进数据库
sqlSession.commit();
System.out.println(i);
}
@Test
//批量删除
public void testDel(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
int id[] = {2,3};
int i = service.deleteMulPerson(id);
//提交事务,不然写不进数据库
sqlSession.commit();
System.out.println(i);
}
@Test
//修改
public void test3(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
Person p = new Person(3,"sdgd","男",20,"1231413");
int i = service.updatePerson(p);
//提交事务,不然写不进数据库
sqlSession.commit();
System.out.println(i);
}
@Test
//查询所有
public void test4(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
List<Person>list = service.findAllPerson();
for(Person p:list){
System.out.println(p.toString());
}
}
@Test
//按照id查询
public void test5(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
Person p = new Person();
p = service.findById(4);
System.out.println(p);
}
@Test
//按名字模糊查询
public void test6(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
List<Person>list = service.findVaguePerson("户");
for(Person p:list){
System.out.println(p.toString());
}
}
@Test
//分页查询
public void test7(){
//1.获取sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.获取需要操作的接口实例
//通过解析接口对应的映射文件,根据映射文件中的配置来获取接口实例
IPersonService service = sqlSession.getMapper(IPersonService.class);
List<Person>list = service.findByPage(2, 3);
for(Person p:list){
System.out.println(p.toString());
}
}