实例:用户和账户
一个用户可以有多个账户
一个账户只能属于一个用户(一个用户可有多个账户)
实现功能:主要是一对多和一对一的操作
查询用户 ,可以同时得到用户信息和用户下所有的账户信息。
查询账户, 可以同时得到账户信息和所属用户信息
增删改用户信息
1.创建两个表user 、account
2.创建实体类创建user与account 并生成get、set、tostring方法
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Account> accounts;
public class Account {
private Integer id;
private Integer uid;
private Double money;
private User user;
3.dao层接口
IUserDao:
package com.powersi.dao;
import com.powersi.domain.User;
import java.util.List;
public interface IUserDao {
//查询所有用户 包含用户和账户信息
List<User> findAll();
//删除用户 并删除用户包含的account信息
void deleteUser(int id);
//修改用户
void updateUser(User user);
//新增用户
void saveUser(User user);
}
IAccountDao:
package com.powersi.dao;
import com.powersi.domain.Account;
import java.util.List;
public interface IAccountDao {
//查询所有账户
List<Account> findAll();
}
4.配置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">
<!-- mybatis的主配置文件 -->
<configuration>
<!-- 配置环境 -->
<environments default="mysql">
<!-- 配置mysql的环境-->
<environment id="mysql">
<!-- 配置事务的类型-->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源(连接池) -->
<dataSource type="POOLED">
<!-- 配置连接数据库的4个基本信息 -->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/powersi"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!-- 指定映射配置文件的位置,映射配置文件指的是每个dao独立的配置文件 -->
<mappers>
<mapper resource="com/powersi/dao/IUserDao.xml"/>
<mapper resource="com/powersi/dao/IAccountDao.xml"/>
</mappers>
</configuration>
5.配置dao的映射文件
IUserDao.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="com.powersi.dao.IUserDao">
<!--定义user的resultMap-->
<resultMap id="UserAccountMap" type="com.powersi.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<!--配置一对多映射-->
<collection property="accounts" ofType="com.powersi.domain.Account">
<result property="id" column="aid"></result>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
</collection>
</resultMap>
<!--查询所有用户信息 包含用户和账户信息-->
<select id="findAll" resultMap="UserAccountMap">
select user.*,account.id as aid,account.uid,account.money from user left join account on user.id = account.uid;
</select>
<!--新增用户信息-->
<insert id="saveUser" parameterType="com.powersi.domain.User">
insert into user (username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address});
</insert>
<!--删除用户信息 级联删除-->
<delete id="deleteUser" parameterType="java.lang.Integer">
DELETE u,a FROM user as u LEFT JOIN account as a ON u.id = a.UID WHERE u.id= #{id}
</delete>
<!--修改用户信息-->
<update id="updateUser" parameterType="com.powersi.domain.User">
update user set username=#{username}, birthday=#{birthday} ,sex=#{sex},address=#{address} where id =#{id}
</update>
</mapper>
IAccountDao.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="com.powersi.dao.IAccountDao">
<!--定义封装account和user的resultMap-->
<resultMap id="AccountUserMap" type="com.powersi.domain.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--配置一对一关系映射,封装user-->
<association property="user" column="uid" javaType="com.powersi.domain.User">
<result property="id" column="id"></result>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<select id="findAll" resultMap="AccountUserMap">
select u.* ,a.id as aid ,a.uid ,a.money from account a , user u where u.id=a.uid
</select>
</mapper>
6.创建测试类
UserTest
package com.power.test;
import com.powersi.dao.IUserDao;
import com.powersi.domain.User;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class UserTest {
private InputStream in;
private SqlSession sqlSession;
private IUserDao userDao;
@Before
public void init() throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建SqlSessionFactory工厂
SqlSessionFactory factory= new SqlSessionFactoryBuilder().build(in);
//3.使用工厂生产SqlSession对象
sqlSession = factory.openSession();
//4.使用SqlSession创建Dao接口的代理对象
userDao = sqlSession.getMapper(IUserDao.class);
}
@After
public void destory() throws Exception {
//6.释放资源
sqlSession.close();
in.close();
}
/**
* 测试查询用户信息,可以同时得到用户信息和用户下所有的账户信息。
*/
@Test
public void testfindAll() {
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println("------每个用户的的信息------");
System.out.println(user);
System.out.println(user.getAccounts());
}
}
/**
* 测试保存用户信息
*/
@Test
public void testSave(){
User user =new User();
user.setUsername("陈心诺");
user.setBirthday(new Date());
user.setSex("女");
user.setAddress("长沙池水沟子");
userDao.saveUser(user);
sqlSession.commit();
}
/**
* 测试删除用户信息
*/
@Test
public void testDelect(){
userDao.deleteUser(43);
sqlSession.commit();
}
/**
* 测试修改用户信息
*/
@Test
public void testUpdate(){
User user =new User();
user.setUsername("陈心诺01");
user.setBirthday(new Date());
user.setSex("女");
user.setAddress("长沙池水沟子");
user.setId(41);
userDao.updateUser(user);
sqlSession.commit();
}
}
AccountTest
package com.power.test;
import com.powersi.dao.IAccountDao;
import com.powersi.domain.Account;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class AccountTest {
private InputStream in;
private SqlSession sqlSession;
private IAccountDao accountDao;
@Before
public void init() throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建SqlSessionFactory工厂
SqlSessionFactory factory= new SqlSessionFactoryBuilder().build(in);
//3.使用工厂生产SqlSession对象
sqlSession = factory.openSession();
//4.使用SqlSession创建Dao接口的代理对象
accountDao = sqlSession.getMapper(IAccountDao.class);
}
@After
public void destory() throws Exception {
//6.释放资源
sqlSession.close();
in.close();
}
/**
* 测试查询账户信息,可以同时得到账户信息和所属用户信息
*/
@Test
public void testfindAll(){
List<Account> accounts = accountDao.findAll();
for (Account account :accounts){
System.out.println("------每个account的信息------");
System.out.println(account);
System.out.println(account.getUser());
}
}
}
7.测试结果
testfindAll():查询所有结果包(包含账户信息)
testSave() 陈心诺01 结果
testDelect()级联删除测试(删除id为43的用户同时删除名下所有账户)
testUpdate()修改测试(id为42的用户信息修改)
修改前后对比
查询所有账户(包含用户信息)