普通的xml配置:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Dao.UserDao3">
<insert id="insert" parameterType="entity.User">
insert into user values(#{username},#{password},#{id},#{birthday})
</insert>
<update id="update" parameterType="entity.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
<delete id="delete" parameterType="int">
delete from user where id=#{id}
</delete>
<select id="findById" parameterType="int" resultType="entity.User">
select * from user where id=#{id}
</select>
<select id="findAll" resultType="entity.User">
select * from user
</select>
</mapper>
dao:
package Dao;
import entity.User;
import java.io.IOException;
import java.util.List;
public interface UserDao3
{
public void insert(User user);
public void update(User user);
public void delete(int id);
public User findById(int id);
public List<User> findAll();
}
测试:
private SqlSession sqlSession;
@Before/*执行测试方法先执行before里面的方法*/
public void before() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession(true);
}
@Test
public void insertTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
User user = new User();
user.setUsername("一一");
user.setPassword("999");
mapper.insert(user);
sqlSession.close();
}
@Test
public void updateTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
User user = new User();
user.setId(8);
user.setUsername("一");
user.setPassword("777");
mapper.update(user);
sqlSession.close();
}
@Test
public void deleteTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
mapper.delete(8);
sqlSession.close();
}
@Test
public void findByIdTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
User u = mapper.findById(3);
System.out.println(u);
sqlSession.close();
}
@Test
public void selectTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
List<User> userList = mapper.findAll();
for(User u:userList)
{
System.out.println(u);
}
sqlSession.close();
}
使用注解开发
首先要在sqlMapCongfig.xml把UserDao3.xml配置文件清掉,再加上包或者类扫描
<!--加载映射关系-->
<mappers>
<!--指定接口所在的包-->
<package name="Dao"/>
<!--<mapper class="Dao.UserDao3"></mapper> 或者指定扫描哪一个类-->
</mappers>
在Dao层进行注解开发
package Dao;
import entity.User;
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 java.io.IOException;
import java.util.List;
public interface UserDao3
{
@Insert("insert into user values(#{username},#{password},#{id},#{birthday})")
public void insert(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id=#{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
}
测试:
private SqlSession sqlSession;
@Before/*执行测试方法先执行before里面的方法*/
public void before() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sqlSessionFactory.openSession(true);
}
@Test
public void insertTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
User user = new User();
user.setUsername("一一");
user.setPassword("999");
mapper.insert(user);
sqlSession.close();
}
@Test
public void updateTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
User user = new User();
user.setId(8);
user.setUsername("一");
user.setPassword("777");
mapper.update(user);
sqlSession.close();
}
@Test
public void deleteTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
mapper.delete(8);
sqlSession.close();
}
@Test
public void findByIdTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
User u = mapper.findById(3);
System.out.println(u);
sqlSession.close();
}
@Test
public void selectTest()
{
UserDao3 mapper = sqlSession.getMapper(UserDao3.class);
List<User> userList = mapper.findAll();
for(User u:userList)
{
System.out.println(u);
}
sqlSession.close();
}
一对一:
orders类:
package entity;
import java.util.Date;
public class Orders {
private long id;
private Date ordertime;
private double total;
private long uid;
/*该订单属于哪个用户*/
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordertime=" + ordertime +
", total=" + total +
", uid=" + uid +
", user=" + user +
'}';
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Date ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public long getUid() {
return uid;
}
public void setUid(long uid) {
this.uid = uid;
}
}
package Dao;
import entity.Orders;
import entity.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrderMapper
{
/* @Select("select *,o.id oid from user u,orders o where u.id=o.uid")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(column = "uid",property = "user.id"),
@Result(column = "username",property = "user.username"),
@Result(column = "password",property = "user.password"),
@Result(column = "birthday",property = "user.birthday")
})
public List<Orders> findAll();*/
/*select * from user u,orders o where o.uid=u.id*/
@Select("select * from order where uid=#{uid}")/*为一对多查询作铺垫*/
public List<Orders> findByUid(int uid);
@Select("select * from orders")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
/*property:当前实体(order)属性名称(private User user中的user)
javaType: 当前实体中的属性类型(private User user中的User)*/
property = "user",
javaType = User.class,
column = "uid",//根据select * from orders查出来的uid,再去查询user表的数据
one = @One(select = "Dao.UserDao3.findById")
)
})
public List<Orders> findAll();
}
userDAO3:
package Dao;
import entity.User;
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 java.io.IOException;
import java.util.List;
public interface UserDao3
{
@Insert("insert into user values(#{username},#{password},#{id},#{birthday})")
public void insert(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id=#{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
}
一对多查询:
package entity;
import java.util.Date;
import java.util.List;
public class User {
private String username;
private String password;
private long id;
private Date birthday;
//当前用户存在哪些订单
private List<Orders> ordersList;
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "User{" +
"username='" + username + '\'' +
", password='" + password + '\'' +
", id=" + id +
", birthday=" + birthday +
", ordersList=" + ordersList +
'}';
}
}
userDao:
package Dao;
import entity.Orders;
import entity.User;
import org.apache.ibatis.annotations.*;
import java.io.IOException;
import java.util.List;
public interface UserDao3
{
@Insert("insert into user values(#{username},#{password},#{id},#{birthday})")
public void insert(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id=#{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
/*select * from user u,orders o where o.uid=u.id*/
@Select("select * from user")
@Results({
@Result(id=true,column = "id",property = "id"),/*表明id是主键*/
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(column = "birthday",property = "birthday"),
@Result(
property = "ordersList",javaType = List.class,
column = "id",根据select * from user查出来的id,再去查询orders表的数据
many = @Many(select = "Dao.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrder();
}
orderMapper:
package Dao;
import entity.Orders;
import entity.User;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrderMapper
{
/* @Select("select *,o.id oid from user u,orders o where u.id=o.uid")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(column = "uid",property = "user.id"),
@Result(column = "username",property = "user.username"),
@Result(column = "password",property = "user.password"),
@Result(column = "birthday",property = "user.birthday")
})
public List<Orders> findAll();*/
@Select("select * from orders where uid=#{uid}")
public List<Orders> findByUid(int uid);
@Select("select * from orders")
@Results({
@Result(column = "oid",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
/*property:当前实体(order)属性名称(private User user中的user)
javaType: 当前实体中的属性类型(private User user中的User)*/
property = "user",
javaType = User.class,
column = "uid",//根据select * from orders查出来的uid,再去查询user表的数据
one = @One(select = "Dao.UserDao3.findById")
)
})
public List<Orders> findAll();
}
测试:
@Test
public void UserTest()
{
UserDao3 userMapper = sqlSession.getMapper(UserDao3.class);
List<User> userList = userMapper.findUserAndOrder();
for(User o:userList)
{
System.out.println(o);
}
}
多对多查询:
SysUser类:
package entity;
import java.util.List;
public class SysUser {
private long id;
private String username;
private String email;
private String password;
private String phoneNum;
//描述的是当前用户具备哪些角色
private List<Role> roleList;
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(String phoneNum) {
this.phoneNum = phoneNum;
}
@Override
public String toString() {
return "SysUser{" +
"id=" + id +
", username='" + username + '\'' +
", email='" + email + '\'' +
", password='" + password + '\'' +
", phoneNum='" + phoneNum + '\'' +
", roleList=" + roleList +
'}';
}
}
SysUser类:
package entity;
public class SysRole {
private long id;
private String roleName;
private String roleDesc;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "SysRole{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
User接口:
package Dao;
import entity.SysUser;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface SysUserMapper
{
@Select("select * from sys_user")
@Results({
@Result(id=true,column = "userId",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "email",property = "email"),
@Result(column = "password",property = "password"),
@Result(column = "phoneNum",property = "phoneNum"),
@Result(
property = "roleList",javaType = List.class,
column = "id",//根据select * from sys_user查出来的id,再通过sys_user_role和sys_role表去sysUser有哪些角色
many = @Many(select = "Dao.RoleMapper.findByUserId")
)
})
public List<SysUser> findUserAndRole();
}
package Dao;
import entity.Role;
import entity.SysRole;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface RoleMapper
{
@Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id AND ur.userId=#{id}")
public List<SysRole> findByUserId(int uid);
}
sysRole类:
package entity;
public class SysRole {
private long id;
private String roleName;
private String roleDesc;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "SysRole{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
测试:
@Test
public void SysUserTest()
{
SysUserMapper mapper = sqlSession.getMapper(SysUserMapper.class);
List<SysUser> userList = mapper.findUserAndRole();
for(SysUser o:userList)
{
System.out.println(o);
}
}