一、說明
前邊兩篇腿已經邁進門了，這篇開始講實體類別名、多參數、動態SQL等
二、開搞
數據庫表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
DROP TABLE IF EXISTS `test`;CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `salary` decimal(10, 2) NOT NULL, `age` int(11) NULL DEFAULT NULL, `city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 43 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of test-- ----------------------------INSERT INTO `test` VALUES (1, '小明', 40000.00, 18, '北京', '程序猿');INSERT INTO `test` VALUES (2, '小強', 50000.00, 19, '南京', '程序汪');INSERT INTO `test` VALUES (3, '小月月', 50000.00, 20, '天津', '程序狗');INSERT INTO `test` VALUES (4, '小月鳥', 40000.00, 21, '廣州', '程序屌絲'); |
2.1 實體類別名
2.1.1 第一種方式
1.創建實體類
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
package entity;import java.math.BigDecimal;/** * 一個生活在互聯網底層，做著增刪改查的碼農,不諳世事的造作 * @create 2021-08-25 22:05 */public class TestEntity { private Long id; private String name; private BigDecimal salary; private Integer age; private String city; private String job; // get set方法省略 IntelliJ IDEA 生成快捷鍵是Alt+Inert 選擇Getter and Setter // toString 方法省略 IntelliJ IDEA 生成快捷鍵是Alt+Inert 選擇 toString} |
2.創建XML
mybatis-config.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<?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> <!--這里一定注意順序 --> <typeAliases> <typeAlias type="entity.TestEntity" alias="testEntity"/> </typeAliases> <!--省略environments 看前2篇 --> <!--省略掃描 看前2篇--></configuration> |
配置文件順序要這樣配置：
<properties>...</properties>
<settings>...</settings>
<typeAliases>...</typeAliases>
<typeHandlers>...</typeHandlers>
<objectFactory>...</objectFactory>
<objectWrapperFactory>...</objectWrapperFactory>
<plugins>...</plugins>
<environments>...</environments>
<databaseIdProvider>...</databaseIdProvider>
<mappers>...</mappers>
3.使用別名
|
1
2
3
4
|
<!--根據主鍵查詢--><select id="get" resultType="testEntity"> select * from test where id = #{id}</select> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); System.out.println(mapper); // 查詢數據 TestEntity testEntity = mapper.get(1L); System.out.println(testEntity); } }} |
2.1.2 第二種方式
掃描包路徑 mybatis-config.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<?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> <!--掃描包路徑--> <typeAliases> <package name="entity"/> </typeAliases> <!--省略environments 看前2篇 --> <!--省略掃描 看前2篇--></configuration> |
用掃描包路徑的方式，實體類別名默認就是java類首字母小寫
例如：TestEntity --> testEntity
還可以注解指定：
|
1
2
3
4
|
@Alias("testEntityxxoo")public class TestEntity { // 其他省略} |
如果寫了注解@Alias 別名就不是”testEntity”了 ，就變成”testEntityxxoo“
2.1.3 mybatis默認別名
| 映射類型 | |
|---|---|
| _byte | byte |
| _long | long |
| _short | short |
| _int | int |
| _integer | int |
| _double | double |
| _float | float |
| _boolean | boolean |
| string | String |
| byte | Byte |
| long | Long |
| short | Short |
| int | Integer |
| integer | Integer |
| double | Double |
| float | Float |
| boolean | Boolean |
| date | Date |
| decimal | BigDecimal |
| bigdecimal | BigDecimal |
| object | Object |
| map | Map |
| hashmap | HashMap |
| list | List |
| arraylist | ArrayList |
| collection | Collection |
| iterator | Iterator |
2.2 插入數據返回自增主鍵
2.2.1方式一
|
1
2
3
4
|
<!--增加--><insert id="save" useGeneratedKeys="true" keyProperty="id"> INSERT INTO `test`( `name`, `salary`) VALUE (#{name}, #{salary});</insert> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); // 測試id是否到了實體類里邊 TestEntity testEntity = new TestEntity(); testEntity.setName("小鴨子"); testEntity.setSalary(new BigDecimal(100000)); mapper.save(testEntity); System.out.println("主鍵:"+testEntity.getId()); } }} |
輸出結果：
主鍵不是直接返回的，而是把主鍵值設置到插入的對象里的
2.2.2 方式二
|
1
2
3
4
5
6
7
|
<!--增加--><insert id="save"> <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long"> SELECT LAST_INSERT_ID() </selectKey> INSERT INTO `test`(`id`, `name`, `salary`) VALUE (#{id},#{name}, #{salary})</insert> |
2.3 多參數
2.3.1 一個參數
|
1
2
|
// 根據主鍵查詢TestEntity get(Long id); |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<!--根據主鍵查詢--><select id="get" resultType="testEntity"> select * from test where id = #{id}</select><select id="get" resultType="testEntity"> select * from test where id = #{xx}</select><select id="get" resultType="testEntity"> select * from test where id = #{oo}</select><select id="get" resultType="testEntity"> select * from test where id = #{aaabbb}</select> |
如果只有一個參數，并且參數類型是Java基礎類型或String類型，那么使用這個參數的時候
#{xxoo} xxoo可以是任意字符 與方法輸入參數名稱無關
上邊例子中：id、xx、oo、aaabbb 都可以使用 ，但是哈，我們一般見名知意，傳遞的什么參數(id),我們xml就用#{傳遞的參數} 這不是必須但可以遵循這個規范
2.3.2 多個參數 之實體類
|
1
2
|
// 新增void save(TestEntity testEntity); |
|
1
2
3
4
|
<!--增加--><insert id="save"> INSERT INTO `test`(`name`, `salary`) VALUE (#{name}, #{salary})</insert> |
這個很容易明白，實體類參數叫什么 這里#{}里邊就用什么
2.3.3 多個參數之@Param注解
|
1
2
|
// 根據名稱模糊查詢List<TestEntity> listByNameAndAge(@Param("name") String name,@Param("age") Integer age); |
|
1
2
3
4
5
6
7
8
9
10
11
|
<!--根據名稱和年齡查尋--><select id="listByNameAndAge" resultType="testentity"> select * from test where 1=1 <if test="name != null"> and name like CONCAT('%',#{name},'%') </if> <if test="age != null"> and age = #{age} </if></select> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); List<TestEntity> list = mapper.listByNameAndAge("小強", 19); System.out.println(list); } }} |
2.3.4 多個參數之Map
用Map跟用實體類差不多 就key值當做是實體類的字段名稱就可以
|
1
2
|
// 多參數Map 方式傳遞List<TestEntity> listByNameAndAgeMap(Map<String, Object> param); |
|
1
2
3
4
5
6
7
8
9
10
11
|
<!--param多參數map使用--><select id="listByNameAndAgeMap" resultType="testentity"> select * from test where 1=1 <if test="name != null"> and name like CONCAT('%',#{name},'%') </if> <if test="age != null"> and age = #{age} </if></select> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); Map<String,Object> param = new HashMap<>(); param.put("name", "小強"); param.put("age", 19); List<TestEntity> list = mapper.listByNameAndAgeMap(param); System.out.println(list); } }} |
2.3.5 多個參數之默認
默認有兩套參數：
arg0、arg1、arg2、argxxx ; arg從0開始按照方法參數順序
param1、param2、param3、paramxxx ; param從1開始按照方法參數順序
|
1
2
|
// 什么都不用List<TestEntity> listByNameAndAgeNone(String name, int age); |
|
1
2
3
4
5
6
7
8
9
10
11
|
<!--用默認順序--><select id="listByNameAndAgeNone" resultType="testentity"> select * from test where 1=1 <if test="arg0 != null"> and name like CONCAT('%',#{arg0},'%') </if> <if test="arg1 != null"> and age = #{arg1} </if></select> |
|
1
2
3
4
5
6
7
8
9
10
11
|
<!--用默認順序--><select id="listByNameAndAgeNone" resultType="testentity"> select * from test where 1=1 <if test="param1 != null"> and name like CONCAT('%',#{param1},'%') </if> <if test="param2 != null"> and age = #{param2} </if></select> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); List<TestEntity> list = mapper.listByNameAndAgeNone("小月", 20); System.out.println(list); } }} |
2.3.6 數組參數之基礎值&實體類
注意傳遞數組的話，默認參數名稱為arry
1. 根據多個年齡查詢數據：
|
1
2
|
// 根據年齡集合查詢List<TestEntity> listByAges(int[] ages); |
|
1
2
3
4
5
6
7
8
9
10
|
<select id="listByAges" resultType="testentity"> select * from test where 1=1 <if test="array != null and array.length >0"> and age in <foreach collection="array" item="age" open="(" separator="," close=")"> #{age}</foreach> </if> </select> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); int[] ages = new int[]{19,20}; List<TestEntity> list = mapper.listByAges(ages); System.out.println(list); } }} |
2. 根據名稱和年齡多條件查詢
例如：名稱是小強并且年齡是19 或者名稱是小月月年齡是20 的數據
|
1
2
|
// 根據多組參數查詢List<TestEntity> listByNameAndAges(TestEntity[] params); |
|
1
2
3
4
5
6
7
8
9
10
11
|
<select id="listByNameAndAges" resultType="testentity"> select * from test where 1=1 <if test="array != null and array.length >0"> and ( <foreach collection="array" item="item" separator="or" > (name = #{item.name} and age = #{item.age}) </foreach> ) </if></select> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
public class TestMain { public static void main(String[] args) throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); try (SqlSession session = sqlSessionFactory.openSession()) { // 通過sesson獲取Mapper 這個Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); TestEntity[] params = new TestEntity[2]; TestEntity testEntity01 = new TestEntity(); testEntity01.setName("小強"); testEntity01.setAge(19); TestEntity testEntity02 = new TestEntity(); testEntity02.setName("小月月"); testEntity02.setAge(20); params[0] = testEntity01; params[1] = testEntity02; List<TestEntity> list = mapper.listByNameAndAges(params); System.out.println(list); } }} |
最后輸出的sql格式是這樣的：
select* from test where 1=1 and (
(name = '小強' and age = 19) or
(name = '小月月' and age = 20)
)
2.3.7 集合參數之基礎值&實體類
集合與數組差不多，但還是有點兒差別
不同點1： 集合如果不指定參數名稱的話默認使用：collection或者list 不是array
不同點2：集合判斷大小是這樣的 用的size() 不是length
|
1
|
<if test="list != null and list.size() >0"></if> |
2.4 四大標簽的說明
select是Mybatis使用最多的標簽之一，他與insert update delete不同，他不對數據庫值做改變，只是查
insert、 update、 delete 會對數據庫的值做變更，這三個標簽可以混用，也就是說他們功能一樣，出三個的意義就是為了業務上可以區分一下是新增、修改還是刪除。一般我們也遵循這個使用。
2.5 嘮嘮
沒寫動態Sql相關的東西 后邊幾篇寫吧
下期預告：
# {}${} 這哥倆的區別與使用
到此這篇關于一小時迅速入門Mybatis之實體類別名與多參數 動態SQL的文章就介紹到這了,更多相關Mybatis 動態SQL 多參數 實體類別名內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/qq_36291682/article/details/119974538
