Mybatis(四)

一、目录结构

二、代码

1、AccountDao

 1 package cn.bijian.dao;
 2 
 3 import cn.bijian.model.Account;
 4 
 5 import java.util.List;
 6 
 7 public interface AccountDao {
 8     /*
 9         查询account以及所对应的user的username和address（第二种方式，一对一）
10      */
11     List<Account> findAll();
12 }

2、RoleDao

 1 package cn.bijian.dao;
 2 
 3 import cn.bijian.model.Role;
 4 
 5 import java.util.List;
 6 
 7 public interface RoleDao {
 8     /*
 9     查询Role以及Role所对应的user信息(多对多)
10      */
11     List<Role> findAll();
12 }

3、UserDao

 1 package cn.bijian.dao;
 2 
 3 import cn.bijian.model.AccountUser;
 4 import cn.bijian.model.User;
 5 import cn.bijian.pojo.QueryVo;
 6 
 7 import java.util.List;
 8 
 9 public interface UserDao {
10     /*
11         if标签的使用
12      */
13     List<User> findByUser(User user);
14     /*
15         where标签的使用
16      */
17     List<User> findByUser2(User user);
18     /*
19         foreach标签的使用
20      */
21     List<User> findByQueryVo(QueryVo queryVo);
22     /*
23         查询account以及所对应的user的username和address(一对一)
24      */
25     List<AccountUser> findAll();
26     /*
27     查询user以及user对应的account(一对多)
28      */
29     List<User> findAll2();
30 }

4、Account

 1 package cn.bijian.model;
 2 
 3 import java.io.Serializable;
 4 
 5 public class Account implements Serializable {
 6     private Integer id;
 7     private Integer uid;
 8     private Double money;
 9     private User user;
10 
11     public Integer getId() {
12         return id;
13     }
14 
15     public void setId(Integer id) {
16         this.id = id;
17     }
18 
19     public Integer getUid() {
20         return uid;
21     }
22 
23     public void setUid(Integer uid) {
24         this.uid = uid;
25     }
26 
27     public Double getMoney() {
28         return money;
29     }
30 
31     public void setMoney(Double money) {
32         this.money = money;
33     }
34 
35     public User getUser() {
36         return user;
37     }
38 
39     public void setUser(User user) {
40         this.user = user;
41     }
42 
43     @Override
44     public String toString() {
45         return "Account{" +
46                 "id=" + id +
47                 ", uid=" + uid +
48                 ", money=" + money +
49                 ", user=" + user +
50                 '}';
51     }
52 }

5、AccountUser

 1 package cn.bijian.model;
 2 
 3 import java.io.Serializable;
 4 
 5 public class AccountUser extends Account implements Serializable {
 6     private String username;
 7     private String address;
 8 
 9     public String getUsername() {
10         return username;
11     }
12 
13     public void setUsername(String username) {
14         this.username = username;
15     }
16 
17     public String getAddress() {
18         return address;
19     }
20 
21     public void setAddress(String address) {
22         this.address = address;
23     }
24 
25     @Override
26     public String toString() {
27         return "AccountUser{" +
28                 "username='" + username + '\'' +
29                 ", address='" + address + '\'' +
30                 '}';
31     }
32 }

6、Role

 1 package cn.bijian.model;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 public class Role implements Serializable {
 7     private Integer id;
 8     private String role_name;
 9     private String role_desc;
10 
11     private List<User> users;
12 
13     public Integer getId() {
14         return id;
15     }
16 
17     public void setId(Integer id) {
18         this.id = id;
19     }
20 
21     public String getRole_name() {
22         return role_name;
23     }
24 
25     public void setRole_name(String role_name) {
26         this.role_name = role_name;
27     }
28 
29     public String getRole_desc() {
30         return role_desc;
31     }
32 
33     public void setRole_desc(String role_desc) {
34         this.role_desc = role_desc;
35     }
36 
37     public List<User> getUsers() {
38         return users;
39     }
40 
41     public void setUsers(List<User> users) {
42         this.users = users;
43     }
44 
45     @Override
46     public String toString() {
47         return "Role{" +
48                 "id=" + id +
49                 ", role_name='" + role_name + '\'' +
50                 ", role_desc='" + role_desc + '\'' +
51                 ", users=" + users +
52                 '}';
53     }
54 }

7、User

 1 package cn.bijian.model;
 2 
 3 import java.io.Serializable;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 public class User implements Serializable {
 8     private Integer id;
 9     private String username;
10     private Date birthday;
11     private String sex;
12     private String address;
13 
14     private List<Account> accounts;
15 
16     public Integer getId() {
17         return id;
18     }
19 
20     public void setId(Integer id) {
21         this.id = id;
22     }
23 
24     public String getUsername() {
25         return username;
26     }
27 
28     public void setUsername(String username) {
29         this.username = username;
30     }
31 
32     public Date getBirthday() {
33         return birthday;
34     }
35 
36     public void setBirthday(Date birthday) {
37         this.birthday = birthday;
38     }
39 
40     public String getSex() {
41         return sex;
42     }
43 
44     public void setSex(String sex) {
45         this.sex = sex;
46     }
47 
48     public String getAddress() {
49         return address;
50     }
51 
52     public void setAddress(String address) {
53         this.address = address;
54     }
55 
56     public List<Account> getAccounts() {
57         return accounts;
58     }
59 
60     public void setAccounts(List<Account> accounts) {
61         this.accounts = accounts;
62     }
63 
64     @Override
65     public String toString() {
66         return "User{" +
67                 "id=" + id +
68                 ", username='" + username + '\'' +
69                 ", birthday=" + birthday +
70                 ", sex='" + sex + '\'' +
71                 ", address='" + address + '\'' +
72                 ", accounts=" + accounts +
73                 '}';
74     }
75 }

8、QueryVo

 1 package cn.bijian.pojo;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 public class QueryVo implements Serializable {
 7     private List<Integer> ids;
 8 
 9     public List<Integer> getIds() {
10         return ids;
11     }
12 
13     public void setIds(List<Integer> ids) {
14         this.ids = ids;
15     }
16 }

9、AccountDao.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="cn.bijian.dao.AccountDao">
 7     <!--
 8         column:数据库列名
 9         property：实体类属性
10         property="user"：account的属性user
11     -->
12     <resultMap id="accountMap" type="cn.bijian.model.Account">
13         <id column="aid" property="id"></id>
14         <result column="uid" property="uid"></result>
15         <result column="money" property="money"></result>
16         <association property="user" javaType="cn.bijian.model.User">
17             <id column="id" property="id"></id>
18             <result column="username" property="username"></result>
19             <result column="sex" property="sex"></result>
20             <result column="birthday" property="birthday"></result>
21             <result column="address" property="address"></result>
22         </association>
23     </resultMap>
24 
25     <select id="findAll" resultMap="accountMap">
26         select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid=u.id
27     </select>
28 
29 </mapper>

10、RoleDao.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="cn.bijian.dao.RoleDao">
 7     <resultMap id="roleMap" type="cn.bijian.model.Role">
 8         <id column="rid" property="id"></id>
 9         <id column="role_name" property="role_name"></id>
10         <id column="role_desc" property="role_desc"></id>
11         <collection property="users" ofType="cn.bijian.model.User">
12             <id column="id" property="id"></id>
13             <result column="username" property="username"></result>
14             <result column="sex" property="sex"></result>
15             <result column="birthday" property="birthday"></result>
16             <result column="address" property="address"></result>
17         </collection>
18     </resultMap>
19 
20     <select id="findAll" resultMap="roleMap">
21         select u.*,r.id as rid,r.role_name,r.role_desc from role r
22         left outer join user_role ur on r.id=ur.rid
23         left outer join user u on u.id=ur.uid
24     </select>
25 </mapper>

11、UserDao

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="cn.bijian.dao.UserDao">
 7     <select id="findByUser" parameterType="cn.bijian.model.User" resultType="cn.bijian.model.User">
 8         select * from user where 1=1
 9         <if test="username != null and username !=''">
10             and username like #{username}
11         </if>
12         <if test="address != null and username !=''">
13             and address like #{address}
14         </if>
15     </select>
16 
17     <!--提取重复的代码片段-->
18     <sql id="defaultUser">
19         select * from user
20     </sql>
21 
22     <select id="findByUser2" parameterType="cn.bijian.model.User" resultType="cn.bijian.model.User">
23         <!--select * from user-->
24         <include refid="defaultUser"></include>
25         <where>
26             <if test="username != null and username !=''">
27                 and username like #{username}
28             </if>
29             <if test="address != null and username !=''">
30                 and address like #{address}
31             </if>
32         </where>
33     </select>
34 
35     <select id="findByQueryVo" parameterType="cn.bijian.pojo.QueryVo" resultType="cn.bijian.model.User">
36         <include refid="defaultUser"></include>
37         <where>
38             <if test="ids != null and ids.size()>0">
39                 <foreach collection="ids" item="id" open=" id in ( " close=" ) " separator=",">
40                     #{id}
41                 </foreach>
42             </if>
43         </where>
44     </select>
45 
46     <select id="findAll" resultType="cn.bijian.model.AccountUser">
47         select a.*,u.username,u.address from account a,user u where a.uid=u.id
48     </select>
49     <!--查询user以及user对应的account(一对多)-->
50     <resultMap id="userMap" type="cn.bijian.model.User">
51         <id column="id" property="id"></id>
52         <result column="username" property="username"></result>
53         <result column="sex" property="sex"></result>
54         <result column="birthday" property="birthday"></result>
55         <result column="address" property="address"></result>
56         <collection property="accounts" ofType="cn.bijian.model.Account">
57             <id column="aid" property="id"></id>
58             <result column="uid" property="uid"></result>
59             <result column="money" property="money"></result>
60         </collection>
61     </resultMap>
62     <select id="findAll2" resultMap="userMap">
63         select u.*,a.id as aid,a.uid,a.money from user u left outer join account a on u.id = a.uid
64     </select>
65 
66 </mapper>

12、SqlMapConfig.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <environments default="mysql">
 7         <environment id="mysql">
 8             <transactionManager type="JDBC"></transactionManager>
 9             <dataSource type="POOLED">  <!--连接池-->
10                 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
11                 <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
12                 <property name="username" value="root"/>
13                 <property name="password" value="123456"/>
14             </dataSource>
15         </environment>
16     </environments>
17 
18     <mappers>
19         <mapper resource="cn/bijian/dao/UserDao.xml"></mapper>
20         <mapper resource="cn/bijian/dao/AccountDao.xml"></mapper>
21         <mapper resource="cn/bijian/dao/RoleDao.xml"></mapper>
22     </mappers>
23 </configuration>

13、MybatisTest

 1 package cn.bijian.test;
 2 
 3 import cn.bijian.dao.UserDao;
 4 import cn.bijian.model.User;
 5 import cn.bijian.pojo.QueryVo;
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 import org.junit.After;
11 import org.junit.Before;
12 import org.junit.Test;
13 
14 import java.io.IOException;
15 import java.io.InputStream;
16 import java.util.ArrayList;
17 import java.util.List;
18 
19 public class MybatisTest {
20     private InputStream in;
21     private SqlSessionFactory sessionFactory;
22     private SqlSession session;
23     private UserDao userDao;
24 
25     @Before
26     public void init() throws IOException {
27         in = Resources.getResourceAsStream("SqlMapConfig.xml");
28         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
29         sessionFactory = builder.build(in);
30         session = sessionFactory.openSession();
31         userDao = session.getMapper(UserDao.class);
32     }
33 
34     @After
35     public void destroy() throws IOException {
36         session.commit();
37         session.close();
38         in.close();
39     }
40 
41     @Test
42     public void testFindByUser(){
43         User user = new User();
44         user.setUsername("%王%");
45         user.setAddress("%顺义%");
46         List<User> users = userDao.findByUser(user);
47         for (User u:users) {
48             System.out.println(u);
49         }
50     }
51 
52     @Test
53     public void testFindByUser2(){
54         User user = new User();
55         user.setUsername("%王%");
56         user.setAddress("%顺义%");
57         List<User> users = userDao.findByUser2(user);
58         for (User u:users) {
59             System.out.println(u);
60         }
61     }
62 
63     @Test
64     public void testFindByQueryVo(){
65         QueryVo queryVo = new QueryVo();
66         List<Integer> ids = new ArrayList<>();
67         ids.add(41);
68         ids.add(42);
69         ids.add(43);
70         ids.add(45);
71         ids.add(46);
72         ids.add(48);
73         queryVo.setIds(ids);
74         List<User> users = userDao.findByQueryVo(queryVo);
75         for (User user:users) {
76             System.out.println(user);
77         }
78     }
79 }

14、MybatisTest2

 1 package cn.bijian.test;
 2 
 3 import cn.bijian.dao.UserDao;
 4 import cn.bijian.model.AccountUser;
 5 import org.apache.ibatis.io.Resources;
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.apache.ibatis.session.SqlSessionFactory;
 8 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 9 import org.junit.After;
10 import org.junit.Before;
11 import org.junit.Test;
12 
13 import java.io.IOException;
14 import java.io.InputStream;
15 import java.util.List;
16 
17 public class MybatisTest2 {
18     private InputStream in;
19     private SqlSessionFactory sessionFactory;
20     private SqlSession session;
21     private UserDao userDao;
22 
23     @Before
24     public void init() throws IOException {
25         in = Resources.getResourceAsStream("SqlMapConfig.xml");
26         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
27         sessionFactory = builder.build(in);
28         session = sessionFactory.openSession();
29         userDao = session.getMapper(UserDao.class);
30     }
31 
32     @After
33     public void destroy() throws IOException {
34         session.commit();
35         session.close();
36         in.close();
37     }
38     //第一种（一对一）
39     @Test
40     public void testFindAll(){
41         List<AccountUser> accountusers = userDao.findAll();
42         for (AccountUser accountuser:accountusers) {
43             System.out.println(accountuser.getId()+"-"+accountuser.getUid()+"-"+
44                     accountuser.getUsername()+"-"+accountuser.getAddress()+
45                     "-"+accountuser.getMoney());
46         }
47     }
48 }

15、MybatisTest3

 1 package cn.bijian.test;
 2 
 3 import cn.bijian.dao.AccountDao;
 4 import cn.bijian.dao.UserDao;
 5 import cn.bijian.model.Account;
 6 import cn.bijian.model.AccountUser;
 7 import org.apache.ibatis.io.Resources;
 8 import org.apache.ibatis.session.SqlSession;
 9 import org.apache.ibatis.session.SqlSessionFactory;
10 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
11 import org.junit.After;
12 import org.junit.Before;
13 import org.junit.Test;
14 
15 import java.io.IOException;
16 import java.io.InputStream;
17 import java.util.List;
18 
19 public class MybatisTest3 {
20     private InputStream in;
21     private SqlSessionFactory sessionFactory;
22     private SqlSession session;
23     private AccountDao accountDao;
24 
25     @Before
26     public void init() throws IOException {
27         in = Resources.getResourceAsStream("SqlMapConfig.xml");
28         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
29         sessionFactory = builder.build(in);
30         session = sessionFactory.openSession();
31         accountDao = session.getMapper(AccountDao.class);
32     }
33 
34     @After
35     public void destroy() throws IOException {
36         session.commit();
37         session.close();
38         in.close();
39     }
40     //第二种（一对一）
41     @Test
42     public void testFindAll(){
43         List<Account> accounts = accountDao.findAll();
44         for (Account account:accounts) {
45             System.out.println(account);
46         }
47     }
48 }

16、MybatisTest4

 1 package cn.bijian.test;
 2 
 3 import cn.bijian.dao.UserDao;
 4 import cn.bijian.model.User;
 5 import cn.bijian.pojo.QueryVo;
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 import org.junit.After;
11 import org.junit.Before;
12 import org.junit.Test;
13 
14 import java.io.IOException;
15 import java.io.InputStream;
16 import java.util.ArrayList;
17 import java.util.List;
18 
19 public class MybatisTest4 {
20     private InputStream in;
21     private SqlSessionFactory sessionFactory;
22     private SqlSession session;
23     private UserDao userDao;
24 
25     @Before
26     public void init() throws IOException {
27         in = Resources.getResourceAsStream("SqlMapConfig.xml");
28         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
29         sessionFactory = builder.build(in);
30         session = sessionFactory.openSession();
31         userDao = session.getMapper(UserDao.class);
32     }
33 
34     @After
35     public void destroy() throws IOException {
36         session.commit();
37         session.close();
38         in.close();
39     }
40     //查询user以及user对应的account(一对多)
41     @Test
42     public void testFindAll2(){
43         List<User> users = userDao.findAll2();
44         for (User user:users) {
45             System.out.println(user);
46         }
47     }
48 }

17、MybatisTest5

 1 package cn.bijian.test;
 2 import cn.bijian.dao.RoleDao;
 3 import cn.bijian.model.Role;
 4 import org.apache.ibatis.io.Resources;
 5 import org.apache.ibatis.session.SqlSession;
 6 import org.apache.ibatis.session.SqlSessionFactory;
 7 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 8 import org.junit.After;
 9 import org.junit.Before;
10 import org.junit.Test;
11 
12 import java.io.IOException;
13 import java.io.InputStream;
14 import java.util.List;
15 
16 public class MybatisTest5 {
17     private InputStream in;
18     private SqlSessionFactory sessionFactory;
19     private SqlSession session;
20     private RoleDao roleDao;
21 
22     @Before
23     public void init() throws IOException {
24         in = Resources.getResourceAsStream("SqlMapConfig.xml");
25         SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
26         sessionFactory = builder.build(in);
27         session = sessionFactory.openSession();
28         roleDao = session.getMapper(RoleDao.class);
29     }
30 
31     @After
32     public void destroy() throws IOException {
33         session.commit();
34         session.close();
35         in.close();
36     }
37     //查询Role以及Role所对应的user信息(多对多)
38     @Test
39     public void testFindAll2(){
40         List<Role> roles = roleDao.findAll();
41         for (Role role:roles) {
42             System.out.println(role);
43         }
44     }
45 }

18、pom.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 
 3 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5   <modelVersion>4.0.0</modelVersion>
 6 
 7   <groupId>cn.bijian</groupId>
 8   <artifactId>Mybatis_review_2</artifactId>
 9   <version>1.0-SNAPSHOT</version>
10 
11   <name>Mybatis_review_2</name>
12   <!-- FIXME change it to the project's website -->
13   <url>http://www.example.com</url>
14 
15   <properties>
16     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
17     <maven.compiler.source>1.7</maven.compiler.source>
18     <maven.compiler.target>1.7</maven.compiler.target>
19   </properties>
20 
21   <dependencies>
22     <dependency>
23       <groupId>junit</groupId>
24       <artifactId>junit</artifactId>
25       <version>4.11</version>
26       <scope>test</scope>
27     </dependency>
28 
29     <dependency>
30       <groupId>mysql</groupId>
31       <artifactId>mysql-connector-java</artifactId>
32       <version>8.0.21</version>
33     </dependency>
34 
35     <dependency>
36       <groupId>log4j</groupId>
37       <artifactId>log4j</artifactId>
38       <version>1.2.12</version>
39     </dependency>
40 
41     <dependency>
42       <groupId>org.mybatis</groupId>
43       <artifactId>mybatis</artifactId>
44       <version>3.4.5</version>
45     </dependency>
46   </dependencies>
47 
48   <build>
49     <resources>
50 
51       <resource>
52         <directory>src/main/java</directory>
53         <includes>
54           <include>**/*.properties</include>
55           <include>**/*.xml</include>
56         </includes>
57         <filtering>false</filtering>
58       </resource>
59 
60       <resource>
61         <directory>src/main/resources</directory>
62         <includes>
63           <include>**/*.properties</include>
64           <include>**/*.xml</include>
65         </includes>
66       </resource>
67     </resources>
68   </build>
69 </project>

posted on 2021-10-29 11:14 晨曦生辉耀匕尖阅读(32) 评论(0) 编辑收藏举报

刷新页面返回顶部

Mybatis(四)

公告