mybatis（一）—-实现crud操作

一、mybatis环境搭建

1、所需资源

a、mybatis-3.2.7.zip下的jar包

b、Eclipse开发集成工具

2、建Java Project,导包

3、建立数据库和表

CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT ‘用户名称’,
`birthday` date default NULL COMMENT ‘生日’,
`sex` char(1) default NULL COMMENT ‘性别’,
`address` varchar(256) default NULL COMMENT ‘地址’,
PRIMARY KEY  (`id`),
UNIQUE KEY `id` USING BTREE (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、po类User.java和供查询的UserQuery.java

package top.einino.po;

import java.util.Date;

public class User {
public Integer id;
public String username;
public String gender;
public Date birthday;
public String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return “User [id=” + id + “, username=” + username + “, gender=”
+ gender + “, birthday=” + birthday + “, address=” + address
+ “]”;
}

}

package top.einino.po;

import java.util.ArrayList;
import java.util.List;

public class UserQuery extends User{
//查询字段 字符串拼接形式，例”username,sex”
private String fields;
//定义排序集合
private List<OrderFields> orderBy = new ArrayList<OrderFields>();
//分页设置，起始行
private Integer startRow;
//每页行数,初定10
private int rows = 10;

public String getFields() {
return fields;
}
public void setFields(String fields) {
this.fields = fields;
}

public List<OrderFields> getOrderBy() {
return orderBy;
}
public void setOrderBy(List<OrderFields> orderBy) {
this.orderBy = orderBy;
}
public Integer getStartRow() {
return startRow;
}
public void setStartRow(Integer startRow) {
this.startRow = startRow;
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
this.rows = rows;
}

private class OrderFields{
//排序字段
private String field;
//排序规则
private String order;

public OrderFields(String field, String order) {
super();
this.field = field;
this.order = order;
}
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public String getOrder() {
return order;
}
public void setOrder(String order) {
this.order = order;
}

}
//添加排序
public void addOrder(String field, String order) {
orderBy.add(new OrderFields(field, order));
}

}

 

5、新建Source Folder类型的文件夹config，加入mybatis的主要配置文件：SqlMapConfig.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>

<!– 加载数据库连接信息 –>
<properties resource=”db.properties“></properties>
<!– 自定义别名操作，所有该包下的类引用时都可以使用简单类名称，不需要使用全类名–>
<typeAliases>

<package name=”top.einino.po”/>

</typeAliases>
<!– mybatis的运行环境 –>
<environments default=”development“>

<environment id=”development“>

<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>

<mappers>

<!– 加载映射文件 –>
<package name=”top.einino.mapper”/>

</mappers>

</configuration>

6、加入数据库资源文件：db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=自己的用户名
jdbc.password=自己的密码

二、实现crud操作

7、写UserMapper.xml

<?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=”top.einino.mapper.UserMapper“>

<!– 定义usermap –>
<resultMap type=”user” id=”usermap“>
<id property=”id” column=”id”/>
<result property=”username” column=”username”/>
<result property=”gender” column=”sex”/>
<result property=”birthday” column=”birthday”/>
<result property=”address” column=”address”/>
</resultMap>

<!– 定义sql字段 –>
<!– 查询字段 –>
<sql id=”selectFields“>
select
<if test=”fields != null”>
${fields}
</if>
<if test=”fields == null”>
id,username,gender,birthday,address
</if>
from user
</sql>
<!– 查询条件 –>
<sql id=”condition“>
<where>
<if test=”id != null”>
and id = #{id}
</if>
<if test=”username != null”>
and username like  ‘%${username}%’
</if>
<if test=”gender != null”>
and gender = #{gender}
</if>
<if test=”birthday != null”>
and birthday = #{birthday}
</if>
<if test=”address != null”>
and address = #{address}
</if>
</where>
</sql>
<!– orderby排序 –>
<sql id=”order“>
<if test=”orderBy != null and orderBy.size > 0″>
order by
<foreach collection=”orderBy” item=”ob” separator=”,”>
${ob.field}  ${ob.order}
</foreach>
</if>

</sql>
<!– 分页 –>
<sql id=”limitPage“>
<if test=”startRow != null”>
limit #{startRow}, #{rows}
</if>

</sql>
<!– 添加用户 –>
<insert id=”saveUser” parameterType=”user”>
<selectKey keyProperty=”id” order=”AFTER” resultType=”java.lang.Integer”>
select LAST_INSERT_ID()
</selectKey>
insert into user(username,
sex,
birthday,
address)
values
(#{username},
#{gender},
#{birthday}, #{address})
</insert>
<!– 通过id查询用户 –>
<select id=”findUserById” parameterType=”int” resultMap=”usermap”>
select *
from user
where id = #{id}
</select>
<!– 通过查询条件，查询用户列表 –>
<select id=”findUserListByCondition” parameterType=”userQuery” resultMap=”usermap”>
<include refid=”selectFields”></include>
<include refid=”condition”></include>
<include refid=”order”></include>
<include refid=”limitPage”></include>
</select>

<!– 通过id删除用户 –>
<delete id=”deleteUserById” parameterType=”java.lang.Integer”>
delete from user
where id = #{id}
</delete>

<!– 通过id删除用户 –>
<delete id=”deleteUserByIds” parameterType=”Integer[]”>
delete from user
where
id in
<foreach collection=”array” item=”id” open=”(” close=”)” separator=”,”>
#{id}
</foreach>
</delete>

<!– 更新用户 –>
<update id=”updateUser” parameterType=”user”>
update user set username = #{username},
sex = #{gender},
birthday = #{birthday},
address = #{address}
where id = #{id}
</update>
</mapper>

6、写UserMapper.java

package top.einino.mapper;

import java.util.List;

import top.einino.po.User;
import top.einino.po.UserQuery;

public interface UserMapper {
//添加用户
public void saveUser(User user);
//通过id查找用户
public User findUserById(Integer id);
//通过查询条件查询id
public List<User> findUserListByCondition(UserQuery userQuery);
//通过id删除用户
public void deleteUserById(Integer id);
//通过id数组删除用户
public void deleteUserByIds(Integer[] ids);
//修改用户
public void updateUser(User user);
}

7、测试类

package top.einino.junit;

import java.io.IOException;
import java.util.Date;
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 org.junit.Test;

import top.einino.mapper.UserMapper;
import top.einino.po.User;
import top.einino.po.UserQuery;

public class TestUserMapper {

 

 private SqlSessionFactory sqlSessionFactory;

@Before
public void setSqlSessionFactory() throws IOException{

//通过配置文件获得sqlSessionFactory
sqlSessionFactory = new  SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(“SqlMapConfig.xml”));

}
//测试添加用户
@Test
public void saveUser(){

User user = new User();
user.setUsername(“einino”);
user.setGender(“男”);
user.setBirthday(new Date());
user.setAddress(“广州”);
//获得sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//获得接口
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//调用方法
mapper.saveUser(user);
//测试是否返回id
System.out.println(user.getId());
//提交
sqlSession.commit();
//关闭
sqlSession.close();

}

//测试通过id查找用户
@Test
public void findUserById(){

SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findUserById(1);
System.out.println(user);
sqlSession.close();

}

//通过查询条件，查询user集合
@Test
public void findUserListByCondition(){

UserQuery userQuery = new UserQuery();
userQuery.setFields(“id,username,sex”);
// userQuery.setUsername(“张”);
userQuery.addOrder(“id”, “asc”);
userQuery.setStartRow(10);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.findUserListByCondition(userQuery);
for(User user : users){

System.out.println(user);

}
sqlSession.close();

}
//通过id删除用户
@Test
public void deleteUserById(){

SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUserById(34);
sqlSession.commit();
sqlSession.close();

}
//通过数组id删除用户
@Test
public void deleteUserByIds(){

SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Integer[] ids = {35, 36, 37};
mapper.deleteUserByIds(ids);
sqlSession.commit();
sqlSession.close();

}
//更新用户
@Test
public void updateUser(){

SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(26);
user.setUsername(“bingo”);
user.setAddress(“广州”);
user.setGender(“男”);
user.setBirthday(new Date());
mapper.updateUser(user);
sqlSession.commit();
sqlSession.close();

}

}

三、小结

该博文主要搭建了mybatis的环境，以及实现增删查改操作，并且查询功能很丰富。

如果有疑问或者对该博文有何看法或建议或有问题的，欢迎评论，恳请指正！