Mybatis的好处是sql与代码的分离,以及能够轻松使用自定义的sql
下面以小例子的形式来讲解一下Mybatis的增删改查:
有以下两种方式:
1.基于XML
2.基于Annotation
前提:数据库那边只需要创建一张user表:create table user (id int,name text,age int);
第一种:基于XML配置文件的CRUD
首先看一下project结构
其中:
lib中的jar包都可以在网上下载到,如果直接使用Maven则无需手动下载了;
conf.xml是mybatis的总配置文件,它定义了数据库的接口,以及表与对象的映射关系;
其内容为:
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
<property name="username" value="root" />
<property name="password" value="930712" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 注册userMapper.xml文件,
userMapper.xml位于com.mapping这个包下,所以resource写成com/mapping/userMapper.xml-->
<mapper resource="com/mapping/userMapper.xml"/>
</mappers>
</configuration>
重要的是dataSource属性和mapper属性;
userMapper.xml中定义了sql,其内容如下:
<?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.mapping.userMapper">
<!--
根据id查询得到一个user对象
-->
<select id="getUser" parameterType="int" resultType="com.xjtu.User">
select * from user where id=#{id}
</select>
<!--
查询全部
-->
<select id="getAll" resultType="com.xjtu.User">
select * from user
</select>
<!--
插入一条记录
-->
<insert id="insertUser" parameterType="com.xjtu.User">
insert into user(id,name,age) values(#{id},#{name},#{age})
</insert>
<!--
根据id删除一条记录
-->
<delete id="deleteById" parameterType="int">
delete from user where id =#{id}
</delete>
<!--
更改一条记录
-->
<update id="updateUser" parameterType="com.xjtu.User">
update user set name=#{name},age=#{age} where id=#{id}
</update>
</mapper>
在标签中编写查询的SQL语句, 例如:设置select标签的id属性为getUser,id属性值必须是唯一的,不能够重复
使用parameterType属性指明查询时使用的参数类型,resultType属性指明查询返回的结果集类型
resultType="com.xjtu.User"就表示将查询结果封装成一个User类的对象返回
User类就是user表所对应的实体类
User类:
package com.xjtu;
public class User {
private int id;
private String name;
private int age;
public void set(int id,String name,int age){
this.id=id;
this.name=name;
this.age=age;
}
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User [age=" + age + ", id=" + id + ", name=" + name + "]";
}
}
package com.xjtu;
import java.io.IOException;
import java.io.Reader;
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.Before;
public class Test {
private String statement="";
private SqlSession session=null;
@Before
public void init() throws IOException{
// mybatis的配置文件
String resource = "conf.xml";
// 使用MyBatis提供的Resources类加载mybatis的配置文件(它也加载关联的映射文件)
Reader reader = Resources.getResourceAsReader(resource);
// 构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()
.build(reader);
// 创建能执行映射文件中sql的sqlSession
session = sessionFactory.openSession();
}
@org.junit.Test
public void testQueryById(){
//getUser是select标签的id属性值,通过select标签的id属性值就可以找到要执行的SQL
statement = "com.mapping.userMapper.getUser";// 映射sql的标识字符串
// 执行查询返回一个唯一user对象的sql
User user = session.selectOne(statement, 1);
System.out.println(user);
session.close();
}
@org.junit.Test
public void testQueryAll(){
statement = "com.mapping.userMapper.getAll";
List<User> userList = session.selectList(statement);
for (User user2 : userList) {
System.out.println(user2);
}
session.close();
}
@org.junit.Test
public void tesInsert(){
statement = "com.mapping.userMapper.insertUser";
User user=new User();
user.set(5,"ldx", 15);
int result=session.insert(statement, user);
if (result==1) {
session.commit();
}
System.out.println(result);
session.close();
}
@org.junit.Test
public void testDelete(){
statement = "com.mapping.userMapper.deleteById";
int result=session.delete(statement, 2);//id=2
if (result==1) {
session.commit();
}
System.out.println(result);
session.close();
}
@org.junit.Test
public void testUpdate(){
statement = "com.mapping.userMapper.updateUser";
User user=new User();
user.setId(3);
user.setAge(30);
user.setName("wdl");
int result=session.update(statement, user);
if (result==1) {
session.commit();
}
session.close();
}
}
第二种:基于Annotation(注解)的CRUD
这里主要是把userMapper.xml用UserMapperI来代替了;
在conf.xml中把<mappers></mappers>标签中的<mapper resource="com/mapping/userMapper.xml"/>
改为:<mapper class="com.mapping.UserMapperI" />
即把sql的定义用一个接口来定义,注意:
不需要手动实现该接口,具体的实现类由MyBatis帮我们动态构建出来,直接拿来使用即可。
UserMapperI的源代码为:
package com.mapping;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.xjtu.User;
public interface UserMapperI {
// 使用@Insert注解指明insert方法要执行的SQL
@Insert("insert into user values(#{id},#{name},#{age})")
public int insert(User user);
@Delete("delete from user where id=#{id}")
public int delete(int id);
@Update("update user set name=#{name},age=#{age} where id=#{id}")
public int update(User user);
@Select("select * from user where id=#{id}")
public User queryById(int id);
@Select("select * from user")
public List<User> queryAll();
}
package com.xjtu;
import java.io.IOException;
import java.io.Reader;
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.Before;
import com.mapping.UserMapperI;
public class Test {
private SqlSession session = null;
private UserMapperI mapper = null;
@Before
public void init() throws IOException {
// mybatis的配置文件
String resource = "conf.xml";
// 使用MyBatis提供的Resources类加载mybatis的配置文件(它也加载关联的映射文件)
Reader reader = Resources.getResourceAsReader(resource);
// 构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder()
.build(reader);
// 创建能执行映射文件中sql的sqlSession
session = sessionFactory.openSession();
// session = sessionFactory.openSession(boolean
// autoCommit);//true自动提交增删改
mapper = session.getMapper(UserMapperI.class);
}
@org.junit.Test
public void testQueryById() {
User user = mapper.queryById(1);
System.out.println(user);
session.close();
}
@org.junit.Test
public void testQueryAll() {
List<User> userList = mapper.queryAll();
for (User user : userList) {
System.out.println(user);
}
session.close();
}
@org.junit.Test
public void tesInsert() {
User user = new User();
user.set(6, "wp", 24);
int result = mapper.insert(user);
if (result == 1) {
session.commit();
}
session.close();
}
@org.junit.Test
public void testDelete() {
int result = mapper.delete(4);
if (result == 1) {
session.commit();
}
session.close();
}
@org.junit.Test
public void testUpdate() {
User user = new User();
user.setId(3);
user.setAge(30);
user.setName("wyx");
int result = mapper.update(user);
if (result == 1) {
session.commit();
}
session.close();
}
}
1.如果你没有设置SqlSession为自动commit:
在执行增删改后一定要执行SqlSession的commit方法,来把操作保存到数据库,否则会有执行成功,但数据库无变化的问题;
自动commit设置:
SqlSession session = sessionFactory.openSession(autoCommit);//autoCommit为true,自动提交增删改
2.以上两种方式都比较常用,相对来说,基于注解会简洁一点;