MyBatis基础-03

MyBatis_sqlMapper

代码示例：

Cat.java:

 1 package com.atguigu.bean;
 2 
 3 public class Cat {
 4     
 5     private Integer id;
 6     private String name;
 7     private Integer gender;
 8     private Integer age;
 9     /**
10      * @return the id
11      */
12     public Integer getId() {
13         return id;
14     }
15     /**
16      * @param id the id to set
17      */
18     public void setId(Integer id) {
19         this.id = id;
20     }
21     /**
22      * @return the name
23      */
24     public String getName() {
25         return name;
26     }
27     /**
28      * @param name the name to set
29      */
30     public void setName(String name) {
31         this.name = name;
32     }
33     /**
34      * @return the gender
35      */
36     public Integer getGender() {
37         return gender;
38     }
39     /**
40      * @param gender the gender to set
41      */
42     public void setGender(Integer gender) {
43         this.gender = gender;
44     }
45     /**
46      * @return the age
47      */
48     public Integer getAge() {
49         return age;
50     }
51     /**
52      * @param age the age to set
53      */
54     public void setAge(Integer age) {
55         this.age = age;
56     }
57     /* (non-Javadoc)
58      * @see java.lang.Object#toString()
59      */
60     @Override
61     public String toString() {
62         return "Cat [id=" + id + ", name=" + name + ", gender=" + gender
63                 + ", age=" + age + "]";
64     }
65     
66     
67 
68 }

Employee.java:

 1 package com.atguigu.bean;
 2 
 3 import org.apache.ibatis.type.Alias;
 4 
 5 //批量起别名后自定义别名，不区分大小写
 6 //@Alias("emp2")
 7 public class Employee {
 8 
 9     private Integer id;
10     private String empName;
11     private String email;
12     private Integer gender;
13     private String loginAccount;
14 
15     public Employee() {
16     }
17 
18     public Employee(Integer id, String empName, String email, Integer gender) {
19         this.id = id;
20         this.empName = empName;
21         this.email = email;
22         this.gender = gender;
23     }
24 
25     public Integer getId() {
26         return id;
27     }
28     public void setId(Integer id) {
29         this.id = id;
30     }
31     public String getEmpName() {
32         return empName;
33     }
34     public void setEmpName(String empName) {
35         this.empName = empName;
36     }
37     public String getEmail() {
38         return email;
39     }
40     public void setEmail(String email) {
41         this.email = email;
42     }
43     public Integer getGender() {
44         return gender;
45     }
46     public void setGender(Integer gender) {
47         this.gender = gender;
48     }
49     public String getLoginAccount() {
50         return loginAccount;
51     }
52 
53     public void setLoginAccount(String loginAccount) {
54         this.loginAccount = loginAccount;
55     }
56     /* (non-Javadoc)
57      * @see java.lang.Object#toString()
58      */
59 
60     @Override
61     public String toString() {
62         return "Employee{" +
63                 "id=" + id +
64                 ", empName='" + empName + '\'' +
65                 ", email='" + email + '\'' +
66                 ", gender=" + gender +
67                 ", loginAccount='" + loginAccount + '\'' +
68                 '}';
69     }
70 }

Key.java:

 1 package com.atguigu.bean;
 2 
 3 /**
 4  * 钥匙表
 5  * @author lfy
 6  *
 7  */
 8 public class Key {
 9     
10     private Integer id;//钥匙的id
11     private String keyName;//钥匙的名
12     
13     private Lock lock;//当前钥匙能开哪个锁；
14     
15     
16     
17     /**
18      * @return the lock
19      */
20     public Lock getLock() {
21         return lock;
22     }
23     /**
24      * @param lock the lock to set
25      */
26     public void setLock(Lock lock) {
27         this.lock = lock;
28     }
29     /**
30      * @return the id
31      */
32     public Integer getId() {
33         return id;
34     }
35     /**
36      * @param id the id to set
37      */
38     public void setId(Integer id) {
39         this.id = id;
40     }
41     /**
42      * @return the keyName
43      */
44     public String getKeyName() {
45         return keyName;
46     }
47     /**
48      * @param keyName the keyName to set
49      */
50     public void setKeyName(String keyName) {
51         this.keyName = keyName;
52     }
53     /* (non-Javadoc)
54      * @see java.lang.Object#toString()
55      */
56     @Override
57     public String toString() {
58         return "Key [id=" + id + ", keyName=" + keyName + ", lock=" + lock
59                 + "]";
60     }
61     
62     
63 
64 }

Lock.java:

 1 package com.atguigu.bean;
 2 
 3 import java.util.List;
 4 
 5 /**
 6  * 锁子表
 7  * @author lfy
 8  *
 9  */
10 public class Lock {
11     private Integer id;
12     private String lockName;
13     //查询锁子的时候把所有的钥匙也查出来
14     private List<Key> keys;
15     //1-1关联   1-n关联  n-n关联
16     // 一个key开一把lock； 1-1
17     // 从lock来看key；1-n；
18     // 从key表看lock：n-1；
19     // n-n；
20     // student表   teacher表；
21     // 1-n；n-1；n-n；外键应该放在哪个表？
22     //结论：
23     //一对n；外键一定放在n的一端；
24     //n-n：中间表存储对应关系；
25     
26     
27     
28     
29     /**
30      * @return the keys
31      */
32     public List<Key> getKeys() {
33         return keys;
34     }
35     /**
36      * @param keys the keys to set
37      */
38     public void setKeys(List<Key> keys) {
39         this.keys = keys;
40     }
41     /**
42      * @return the id
43      */
44     public Integer getId() {
45         return id;
46     }
47     /**
48      * @param id the id to set
49      */
50     public void setId(Integer id) {
51         this.id = id;
52     }
53     /**
54      * @return the lockName
55      */
56     public String getLockName() {
57         return lockName;
58     }
59     /**
60      * @param lockName the lockName to set
61      */
62     public void setLockName(String lockName) {
63         this.lockName = lockName;
64     }
65     /* (non-Javadoc)
66      * @see java.lang.Object#toString()
67      */
68     
69     @Override
70     public String toString() {
71         return "Lock [id=" + id + ", lockName=" + lockName + "]";
72     }
73 
74 }

CatDao.java:

1 package com.atguigu.dao;
2 
3 import com.atguigu.bean.Cat;
4 
5 public interface CatDao {
6     
7     public Cat getCatById(Integer id);
8 
9 }

EmployeeDao.java:

 1 package com.atguigu.dao;
 2 
 3 import com.atguigu.bean.Employee;
 4 import org.apache.ibatis.annotations.MapKey;
 5 import org.apache.ibatis.annotations.Param;
 6 
 7 import java.util.List;
 8 import java.util.Map;
 9 
10 /**
11  * @Title: EmployeeDao
12  * @Description:
13  * @Author:
14  * @Version: 1.0
15  * @create 2020/6/25 11:11
16  */
17 public interface EmployeeDao {
18 
19     /**
20      *     id  empname       gender  email           login_account
21      ------  ------------  ------  --------------  ---------------
22      1  admin              0  admin@qq.com    a
23      * @param id
24      * @return
25      *
26      * 列名作为key，值作为value
27      */
28     public Map<String, Object> getEmpByIdReturnMap(Integer id);
29     public List<Employee> getAllEmps();
30     /**
31      * key就是这个记录的主键；value就是这条记录封装好的对象；
32      * @return
33      *
34      * 把查询的记录的id的值作为key封装这个map
35      * @MapKey("id")
36      */
37     @MapKey("id")
38     public Map<Integer, Employee> getAllEmpsReturnMap();
39     public Employee getEmpById(Integer id);
40     public Employee getEmpByIdAndEmpName(@Param("id") Integer id, @Param("empName") String empName);
41     public Employee getEmployeeByIdAndEmpName(Map<String,Object> map);
42     public int updateEmployee(Employee employee);
43     public boolean deleteEmployee (Integer id);
44     public int insertEmployee(Employee employee);
45     public int insertEmployee2(Employee employee);
46 
47 }

KeyDao.java:

 1 package com.atguigu.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.atguigu.bean.Key;
 6 
 7 public interface KeyDao {
 8     
 9     /**
10      * 将钥匙和锁信息一起查出
11      * @param id
12      * @return
13      */
14     public Key getKeyById(Integer id);
15     
16     public Key getKeyByIdSimple(Integer id);
17     
18     public List<Key> getKeysByLockId(Integer id);
19 
20 }

LockDao.java:

 1 package com.atguigu.dao;
 2 
 3 import com.atguigu.bean.Lock;
 4 
 5 public interface LockDao {
 6     
 7     //查锁子的时候将所有钥匙也查出来
 8     public Lock getLockById(Integer id);
 9     
10     public Lock getLockByIdSimple(Integer id);
11     
12     public Lock getLockByIdByStep(Integer id);
13 
14 }

CatDao.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 <mapper namespace="com.atguigu.dao.CatDao">
 6     <!-- getCatById(Integer) 
 7     resultType="com.atguigu.bean.Cat"：使用默认规则；属性列名一一对应
 8     resultMap="mycat":查出数据封装结果的时候，使用mycat自定义的规则
 9     -->
10     <select id="getCatById" resultMap="mycat">
11         select * from t_cat where id=#{id}
12     </select>
13 
14     <!-- 自定义结果集（resultMap）：自己定义每一列数据和javaBean的映射规则
15     type="":指定为哪个javaBean自定义封装规则；全类名
16     id="":唯一标识；让别名在后面引用
17     
18         id   cName     cAge  cgender
19         1    加菲猫      12     0
20      -->
21     <resultMap type="Cat" id="mycat">
22         <!-- 指定主键列的对应规则；
23         column="id":指定哪一列是主键列
24         property="":指定cat的哪个属性封装id这一列数据
25          -->
26         <id property="id" column="id"/>
27         <!-- 普通列 -->
28         <result property="name" column="cName"/>
29         <result property="age" column="cAge"/>
30         <result property="gender" column="cgender"/>
31     </resultMap>
32 </mapper>

EmployeeDao.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="com.atguigu.dao.EmployeeDao">
  7     <!--namespace="com.atguigu.dao.EmployeeDao"写接口的全类名-->
  8     <!--这个文件中能写的所有标签
  9         cache
 10         cache-ref
 11         delete、update、insert、select
 12         parameterMap：参数map，废弃的  原本是来做复杂参数映射的
 13         resultMap：结果映射，自定义结果集的封装规则
 14         sql：抽取可重用的sql
 15 
 16         databaseId：指定这个CRUD属于哪个数据库
 17         parameterType：传入的参数类型，默认可不写，TypeHandler自动推断
 18         statementType：默认PREPARED（安全）
 19     -->
 20 
 21     <!--传参到底能传哪些-->
 22     <select id="getEmpById" resultType="Employee">
 23         select * from t_employee where id=#{id}
 24     </select>
 25 
 26     <select id="getEmpByIdAndEmpName" resultType="Employee">
 27         select * from t_employee where id=#{id} and empname=#{empName}
 28     </select>
 29 
 30     <!--
 31         现象：
 32             1）单个参数：
 33                基本类型：
 34                     取值：#{随便写}
 35 
 36             2）多个参数：
 37                取值：#{参数名}无效
 38                可用：0,1（参数的索引）或者param1，param2（第几个参数paramN）
 39                原因：只要传入了多个参数，mybatis会自动地将这些参数封装在一个map集合中，封装时使用的key就是参数的索引和参数的第几个标识
 40                     Map<String,Object> map = new HashMap<>();
 41                     map.put("1",传过来的值);
 42                     map.put("2",传入的值);
 43                     #{key}就是从这个map中取值
 44 
 45              3）@Param：为参数指定key：命名参数，推荐使用此方法
 46                     使用指定的key
 47 
 48              4）传入pojo（javaBean）
 49                 取值：#{pojo的属性名}
 50 
 51              5）传入map：将多个要使用的参数封装起来
 52                 取值：#{key}
 53 
 54              扩展：多个参数自动封装map
 55              method01(@Param("id")lnteger id,String empName,Employee employee);
 56              Integer id ->#{id}
 57              String empName ->#{param2}
 58              Employee employee(取出这个里面jemail) ->#{param3.email}
 59 
 60              无论传入什么参数都要能正确地取出值
 61              #{key/属性名}
 62 
 63             1）#{key}取值的时候可以设置一些规则：
 64                 id=#{id,jdbcType=Integer}
 65                 javaType、jdbcType、mode、numericScale、resultMap、typeHandler、jdbcTypeName、expression
 66             只有jdbcType才可能是需要被指定的；
 67                 默认不指定jdbcType；mysql没问题；oracle没问题；
 68                 万一传入的数据是null；
 69                 mysql插入null没问题；【oracle不知道null到底是什么类型；】
 70 
 71             实际上在mybatis中：两种取值方式：
 72             #{属性名}：是参数预编译的方式，参数的位置都是用？替代，参数后来都是预编译设置进去的；安全，不会有sql注入问题
 73             ${属性名}：不是参数预编译，而是直接和sql语句进行拼串；不安全；
 74                 //id=1 or 1=1 or and empname=
 75                 传入一个'1 or 1=1 or'；
 76                 有：sql语句只有参数位置是支持预编译的；
 77                 log_2017_12、log_2018_1；
 78                 select * from log_2018_1 where id=? and empname=?
 79 
 80             id=${id} and empname=#{empName}:
 81             select * from t_employee where id=1 and empname=?
 82 
 83             id=#{id} and empname=#{empName}：
 84             select * from t_employee where id=? and empname=?
 85 
 86  一般都是使用#{}；安全；在不支持参数预编译的位置要进行取值就使用${}；（如表名）
 87     -->
 88 
 89     <select id="getEmployeeByIdAndEmpName" resultType="Employee">
 90         select * from ${tableName} where id=${id} and empname=#{empName}
 91     </select>
 92 
 93     <update id="updateEmployee">
 94         UPDATE t_employee SET empname=#{empName},gender=#{gender},email=#{email} WHERE id=#{id}
 95     </update>
 96 
 97     <delete id="deleteEmployee">
 98         DELETE FROM t_employee WHERE id= #{id}
 99     </delete>
100 
101     <!--让mybatis自动地将自增id赋值给传入的employee对象的id属性
102         useGeneratedKeys="true"：原生jdbc获取自增主键的方法
103         keyProperty="id"：将刚才自增的id封装给哪个属性
104     -->
105 
106     <insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
107         INSERT INTO t_employee(empname,gender,email) VALUES(#{empName},#{gender},#{email})
108     </insert>
109 
110     <insert id="insertEmployee2" useGeneratedKeys="true" keyProperty="id">
111         <!--
112             查询主键
113                 order="BEFORE"：
114                 在核心sql语句之前先运行一个查询sql查到id，将查到的id赋值给javaBean的那个属性
115         -->
116         <selectKey order="BEFORE" resultType="integer" keyProperty="id">
117             select max(id)+1 from t_employee
118         </selectKey>
119         INSERT INTO t_employee(id,empname,gender,email) VALUES(#{id},#{empName},#{gender},#{email})
120     </insert>
121 
122     <!-- public List<Employee> getAllEmps(); -->
123     <!-- resultType=""：如果返回的是集合，写的是集合里面元素的类型 -->
124     <select id="getAllEmps" resultType="com.atguigu.bean.Employee">
125               select * from t_employee
126     </select>
127 
128     <!-- 查询返回一个map  -->
129     <!--public Map<String, Object> getEmpByIdReturnMap(Integer id);  -->
130     <select id="getEmpByIdReturnMap" resultType="map">
131           select * from t_employee where id=#{id}
132     </select>
133 
134     <!-- 查询多个返回一个map；查询多个情况下：集合里面写元素类型；
135       public Map<Integer, Employee> getAllEmpsReturnMap(); -->
136     <select id="getAllEmpsReturnMap" resultType="Employee">
137               select * from t_employee
138     </select>
139 
140 </mapper>

KeyDao.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  <mapper namespace="com.atguigu.dao.KeyDao">
 6  
 7      
 8      <!--public List<Key> getKeysByLockId(Integer id); 
 9      按照锁子id查出所有的key
10       -->
11      <select id="getKeysByLockId" resultType="com.atguigu.bean.Key">
12          select * from t_key where lockid=#{id}
13      </select>
14  
15      <!-- public Key getKeyByIdSimple(Integer id); -->
16      <!-- 查询key的时候也可以带上锁子信息  -->
17      <!-- 
18      private Integer id;//钥匙的id
19     private String keyName;//钥匙的名
20     private Lock lock;//当前钥匙能开哪个锁；
21       -->
22      <!--     id  keyname     lockid   -->
23      <select id="getKeyByIdSimple" resultMap="mykey02">
24          select * from t_key where id=#{id}
25      </select>
26      <resultMap type="com.atguigu.bean.Key" id="mykey02">
27          <id property="id" column="id"/>
28          <result property="keyName" column="keyname"/>
29          <!--告诉mybatis自己去调用一个查询查锁子
30          select=""：指定一个查询sql的唯一标识；mybatis自动调用指定的sql将查出的lock封装进来
31          public Lock getLockByIdSimple(Integer id);需要传入锁子id
32          告诉mybatis把哪一列的值传递过去
33          column：指定将哪一列的数据传递过去（将t_key表中的lockid传到t_lock中）
34            -->
35          <association property="lock" 
36              select="com.atguigu.dao.LockDao.getLockByIdSimple"
37              column="lockid" fetchType="lazy"></association>
38      </resultMap>
39  
40  
41  
42  
43  
44      <!-- getKeyById(Integer) -->
45      <!-- 
46          private Integer id;//钥匙的id
47         private String keyName;//钥匙的名
48         private Lock lock;//当前钥匙能开哪个锁；
49         
50         id  keyname     lockid     lid  lockName  
51       -->
52      <select id="getKeyById" resultMap="mykey">
53          select k.id,k.`keyname`,k.`lockid`,
54                l.`id` lid,l.`lockName` from t_key k
55             left join t_lock l on k.`lockid`=l.`id`
56             where k.`id`=#{id}
57      </select>
58      
59      <!-- 自定义封装规则：使用级联属性封装联合查询出的结果 -->
60 <!--      <resultMap type="com.atguigu.bean.Key" id="mykey">
61          <id property="id" column="id"/>
62          <result property="keyName" column="keyname"/>
63          <result property="lock.id" column="lid"/>
64          <result property="lock.lockName" column="lockName"/>
65      </resultMap> -->
66      
67      <!-- mybatis推荐的   <association property=""></association>-->
68      <resultMap type="com.atguigu.bean.Key" id="mykey">
69          <id property="id" column="id"/>
70          <result property="keyName" column="keyname"/>
71          <!-- 接下来的属性是一个对象，自定义这个对象的封装规则；使用association；表示联合了一个对象 -->
72          <!-- javaType：指定这个属性的类型 -->
73          <association property="lock" javaType="com.atguigu.bean.Lock">
74              <!-- 定义lock属性对应的这个Lock对象如何封装 -->
75              <id property="id" column="lid"/>
76              <result property="lockName" column="lockName"/>
77          </association>
78      </resultMap>
79  </mapper>

LockDao.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  <mapper namespace="com.atguigu.dao.LockDao">
 6  
 7      <!-- public Lock getLockByIdByStep(Integer id);
 8          id  lockName  
 9          1      1号锁   
10       -->
11      <select id="getLockByIdByStep" resultMap="mylockstep">
12          select * from t_lock where id=#{id}
13      </select>
14      <!-- collection分步查询 -->
15      <resultMap type="com.atguigu.bean.Lock" id="mylockstep">
16          <id property="id" column="id"/>
17          <result property="lockName" column="lockName"/>
18          <!-- collection指定集合类型的属性封装规则 -->
19          <collection property="keys"
20              select="com.atguigu.dao.KeyDao.getKeysByLockId"
21              column="{id=id}"></collection>
22              <!-- {key1=列名,key2=列名} -->
23      </resultMap>
24      
25      <!-- 
26          update bs_book 
27              set title=?,author=?,price=?,sales=?,stock=?,img_path=? 
28          where id=?";
29          
30          String sql = "update bs_book set";
31          if(employee.getTitle()){
32              sql+="title=?,"
33          }
34          if(){
35              sql + = "price=?,";
36          }
37       -->
38  
39      <!--public Lock getLockByIdSimple(Integer id);  -->
40      <select id="getLockByIdSimple" resultType="com.atguigu.bean.Lock">
41          select * from t_lock where id=#{id}
42      </select>
43      
44      
45  
46      <!-- public Lock getLockById(Integer id); -->
47      <select id="getLockById" resultMap="mylock">
48          select l.*,k.id kid,k.`keyname`,k.`lockid` from t_lock l 
49             left join t_key k on l.`id`=k.`lockid`
50             where l.id=#{id}
51      </select>
52      
53      <!--
54          private Integer id;
55         private String lockName;
56         //查询锁子的时候把所有的钥匙也查出来
57         private List<Key> keys;
58         
59         id      lockName               kid          keyname     lockid
60          3      303办公室的锁子           3           303钥匙1       3
61          3      303办公室的锁子           4           303钥匙2       3
62          3      303办公室的锁子           5           303钥匙3       3
63        -->
64      <resultMap type="com.atguigu.bean.Lock" id="mylock">
65          <id property="id" column="id"/>
66          <result property="lockName" column="lockName"/>
67          <!-- 
68          collection：定义集合元素的封装 
69              property="":指定哪个属性是集合属性
70              javaType：指定对象类型；association
71              ofType=""：指定集合里面元素的类型
72          -->
73          <collection property="keys" ofType="com.atguigu.bean.Key">
74              <!-- 标签体中指定集合中这个元素的封装规则 -->
75              <id property="id" column="kid"/>
76              <result property="keyName" column="keyname"/>
77          </collection>
78      </resultMap>
79  </mapper>

dbconfig.properties:

1 username=root
2 password=root
3 jdbcUrl=jdbc:mysql://localhost:3306/mybatis_0325?characterEncoding=utf-8&serverTimezone=GMT%2B8
4 driverClass=com.mysql.cj.jdbc.Driver

log4j.xml:

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 3  
 4 <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 5  
 6  <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
 7    <param name="Encoding" value="UTF-8" />
 8    <layout class="org.apache.log4j.PatternLayout">
 9     <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
10    </layout>
11  </appender>
12  <logger name="java.sql">
13    <level value="debug" />
14  </logger>
15  <logger name="org.apache.ibatis">
16    <level value="info" />
17  </logger>
18  <root>
19    <level value="debug" />
20    <appender-ref ref="STDOUT" />
21  </root>
22 </log4j:configuration>

mybatis-config.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 
 7     <properties resource="dbconfig.properties"></properties>
 8 
 9     <settings>
10         <!--驼峰命名    name：配置项的key，value：配置项的值-->
11         <setting name="mapUnderscoreToCamelCase" value="true"/>
12         <!-- 开启延迟加载开关 -->
13         <setting name="lazyLoadingEnabled" value="true"/>
14         <!-- 开启属性按需加载 -->
15         <setting name="aggressiveLazyLoading" value="false"/>
16     </settings>
17 
18     <typeAliases>
19         <!--批量起别名   name：指定包名，默认别名就是类名，不区分大小写-->
20         <package name="com.atguigu.bean"/>
21         <!--推荐使用全类名-->
22     </typeAliases>
23 
24 
25     <environments default="development">
26         <environment id="development">
27             <transactionManager type="JDBC"/>
28             <!-- 配置连接池 -->
29             <dataSource type="POOLED">
30                 <!--${}取出配置文件中的值-->
31                 <property name="driver" value="${driverClass}"/>
32                 <property name="url" value="${jdbcUrl}"/>
33                 <property name="username" value="${username}"/>
34                 <property name="password" value="${password}"/>
35             </dataSource>
36         </environment>
37     </environments>
38 
39 
40     <mappers>
41 
42         <mapper resource="mybatis/EmployeeDao.xml"/>
43         <mapper resource="mybatis/CatDao.xml"/>
44         <mapper resource="mybatis/KeyDao.xml"/>
45         <mapper resource="mybatis/LockDao.xml"/>
46 
47         <!--<package name="com.atguigu.dao"/>-->
48     </mappers>
49 </configuration>

MyBatisCRUDTest.java:

  1 package com.atguigu.test;
  2 
  3 import com.atguigu.bean.Cat;
  4 import com.atguigu.bean.Employee;
  5 import com.atguigu.dao.CatDao;
  6 import com.atguigu.dao.EmployeeDao;
  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.Before;
 12 import org.junit.Test;
 13 
 14 import java.io.IOException;
 15 import java.io.InputStream;
 16 import java.util.HashMap;
 17 import java.util.List;
 18 import java.util.Map;
 19 
 20 /**
 21  * @Title: MyBatisCRUDTest
 22  * @Description:
 23  * @Author:
 24  * @Version: 1.0
 25  * @create 2020/6/25 21:35
 26  */
 27 public class MyBatisCRUDTest {
 28 
 29     SqlSessionFactory sqlSessionFactory;
 30 
 31     @Before
 32     public void initSqlSessionFactory() throws IOException {
 33         //1、根据全局配置文件创建出一个SqlSessionFactory对象
 34         //SqlSessionFactory：是SqlSession工厂，负责创建SqlSession对象；
 35         //SqlSession:sql会话（代表和数据库的一次会话）;
 36         String resource = "mybatis-config.xml";
 37         InputStream inputStream = Resources.getResourceAsStream(resource);
 38         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 39     }
 40 
 41     @Test
 42     public void testInsert() {
 43         //1.获取和数据库的一次会话
 44         SqlSession openSession = sqlSessionFactory.openSession();
 45 
 46         try {
 47             //2.获取接口的映射器
 48             EmployeeDao mapper = openSession.getMapper(EmployeeDao.class);
 49             Employee employee = new Employee(null, "tomcat2", "tomcat2@qq.com", 1);
 50             //3.测试
 51             //int i = mapper.insertEmployee(employee);
 52             int j = mapper.insertEmployee2(employee);
 53             //System.out.println("----->" + i);
 54             // System.out.println("刚才插入的id：" + employee.getId());
 55             openSession.commit();
 56         } finally {
 57             //手动提交
 58 
 59             openSession.close();
 60         }
 61 
 62     }
 63 
 64     @Test
 65     public void test2() {
 66         SqlSession openSession = sqlSessionFactory.openSession();
 67         try {
 68             //2.获取接口的映射器
 69             EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
 70             Employee employee = employeeDao.getEmpByIdAndEmpName(1, "admin");
 71             System.out.println(employee);
 72 
 73         } finally {
 74             openSession.close();
 75         }
 76     }
 77 
 78 
 79     @Test
 80     public void test3() {
 81         SqlSession openSession = sqlSessionFactory.openSession();
 82         try {
 83             //2.获取接口的映射器
 84             EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
 85             Map<String, Object> map = new HashMap<>();
 86             map.put("id", 1);
 87             map.put("empName", "admin");
 88             map.put("tableName","t_employee");
 89             Employee employee = employeeDao.getEmployeeByIdAndEmpName(map);
 90             System.out.println(employee);
 91 
 92         } finally {
 93             openSession.close();
 94         }
 95     }
 96 
 97     @Test
 98     public void test04() {
 99         SqlSession openSession = sqlSessionFactory.openSession();
100         try {
101             EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
102 
103             //查询多条记录封装list
104             List<Employee> allEmps = employeeDao.getAllEmps();
105             for (Employee employee : allEmps) {
106                 System.out.println(employee);
107             }
108         } finally {
109             openSession.close();
110         }
111     }
112 
113     @Test
114     public void test05() {
115         SqlSession openSession = sqlSessionFactory.openSession();
116         try {
117             EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
118 
119             //查询条记录封装map
120             Map<String, Object> map = employeeDao.getEmpByIdReturnMap(1);
121             System.out.println(map);
122         } finally {
123             openSession.close();
124         }
125     }
126 
127     @Test
128     public void test06() {
129         SqlSession openSession = sqlSessionFactory.openSession();
130         try {
131             EmployeeDao employeeDao = openSession.getMapper(EmployeeDao.class);
132 
133             //查询多条记录封装map
134             Map<Integer, Employee> map = employeeDao.getAllEmpsReturnMap();
135             System.out.println(map);
136             Employee employee = map.get(1);
137             System.out.println(employee.getEmpName());
138 
139         } finally {
140             openSession.close();
141         }
142     }
143 
144     /**
145      * 默认mybatis自动封装结果集；
146      * 1）、按照列名和属性名一一对应的规则（不区分大小写）；
147      * 2）、如果不一一对应；
148      *         1）、开启驼峰命名法（满足驼峰命名规则  aaa_bbb  aaaBbb）
149      *         2）、起别名：
150      */
151     @Test
152     public void test07() {
153         SqlSession openSession = sqlSessionFactory.openSession();
154         try {
155             CatDao mapper = openSession.getMapper(CatDao.class);
156             Cat catById = mapper.getCatById(1);
157             System.out.println(catById);
158 
159         } finally {
160             openSession.close();
161         }
162     }
163 }

Test02.java:

  1 package com.atguigu.test;
  2 
  3 import com.atguigu.bean.Key;
  4 import com.atguigu.bean.Lock;
  5 import com.atguigu.dao.KeyDao;
  6 import com.atguigu.dao.LockDao;
  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.Before;
 12 import org.junit.Test;
 13 
 14 import java.io.IOException;
 15 import java.io.InputStream;
 16 import java.util.List;
 17 
 18 /**
 19  * @Title: Test02
 20  * @Description:
 21  * @Author:
 22  * @Version: 1.0
 23  * @create 2020/6/27 15:38
 24  */
 25 public class Test02 {
 26 
 27     SqlSessionFactory sqlSessionFactory;
 28 
 29     @Before
 30     public void initSqlSessionFactory() throws IOException {
 31         //1、根据全局配置文件创建出一个SqlSessionFactory对象
 32         //SqlSessionFactory：是SqlSession工厂，负责创建SqlSession对象；
 33         //SqlSession:sql会话（代表和数据库的一次会话）;
 34         String resource = "mybatis-config.xml";
 35         InputStream inputStream = Resources.getResourceAsStream(resource);
 36         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 37     }
 38 
 39     /**
 40      * 联合查询情况下
 41      * 1、使用级联属性封装联合查询后的所有结果
 42      */
 43     @Test
 44     public void test08() {
 45         SqlSession openSession = sqlSessionFactory.openSession();
 46         try {
 47             KeyDao mapper = openSession.getMapper(KeyDao.class);
 48             Key keyById = mapper.getKeyById(2);
 49             System.out.println(keyById);
 50 
 51         } finally {
 52             openSession.close();
 53         }
 54     }
 55 
 56     @Test
 57     public void test09(){
 58         SqlSession openSession = sqlSessionFactory.openSession();
 59         try {
 60             LockDao mapper = openSession.getMapper(LockDao.class);
 61             Lock lock = mapper.getLockById(3);
 62             System.out.println(lock);
 63             System.out.println("所有锁子如下：");
 64             List<Key> keys = lock.getKeys();
 65             for (Key key : keys) {
 66                 System.out.println(key);
 67             }
 68         } finally {
 69             openSession.close();
 70         }
 71     }
 72 
 73     /*
 74      * 分步查询：
 75      * 0）、查询钥匙的时候顺便查出锁子；
 76      * 1）、Key key = keyDao.getKeyById(1);
 77      * 2）、Lock lock = lockDao.getLockById(1);
 78      */
 79     @Test
 80     public void test10() throws InterruptedException{
 81 
 82         SqlSession openSession = sqlSessionFactory.openSession();
 83         try {
 84             KeyDao mapper = openSession.getMapper(KeyDao.class);
 85 
 86             Key key = mapper.getKeyByIdSimple(4);
 87             //严重性能；
 88             System.out.println(key.getKeyName());
 89             //按需加载；需要的时候再去查询；全局开启按需加载策略；
 90             //延迟加载：不着急加载（查询对象）
 91             Thread.sleep(3000);
 92             String lockName = key.getLock().getLockName();
 93             System.out.println(lockName);
 94 
 95         } finally {
 96             openSession.close();
 97         }
 98     }
 99 
100     /**
101      * 一般我们在工作的时候；写成两个方法
102      * public Key getKeySimple(Integer id);
103      *
104      * 推荐都来写连接查询
105      * public Key getKeyAssicate()
106      */
107     @Test
108     public void test11(){
109         SqlSession openSession = sqlSessionFactory.openSession();
110         try {
111 
112             LockDao mapper = openSession.getMapper(LockDao.class);
113             Lock lock = mapper.getLockByIdByStep(3);
114             System.out.println(lock.getLockName());
115 
116             List<Key> keys = lock.getKeys();
117             for (Key key : keys) {
118                 System.out.println(key.getKeyName());
119             }
120         } finally {
121             openSession.close();
122         }
123     }
124 }

posted @ 2020-07-01 17:08 清晨的第一抹阳光阅读(133) 评论(0) 收藏举报

刷新页面返回顶部

清晨的第一抹阳光

MyBatis基础-03

MyBatis_sqlMapper

公告