您好,登錄后才能下訂單哦!
這篇文章主要介紹了Mybatis中實(shí)體類別名、多參數(shù)、動態(tài)SQL的示例分析,具有一定借鑒價(jià)值,感興趣的朋友可以參考下,希望大家閱讀完這篇文章之后大有收獲,下面讓小編帶著大家一起了解一下。
開始講實(shí)體類別名、多參數(shù)、動態(tài)SQL等
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, '小強(qiáng)', 50000.00, 19, '南京', '程序汪'); INSERT INTO `test` VALUES (3, '小月月', 50000.00, 20, '天津', '程序狗'); INSERT INTO `test` VALUES (4, '小月鳥', 40000.00, 21, '廣州', '程序?qū)沤z');
package entity; import java.math.BigDecimal; /** * @author 發(fā)現(xiàn)更多精彩 關(guān)注公眾號:木子的晝夜編程 * 一個(gè)生活在互聯(lián)網(wǎng)底層,做著增刪改查的碼農(nóng),不諳世事的造作 * @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 }
mybatis-config.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"> <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>
<!--根據(jù)主鍵查詢--> <select id="get" resultType="testEntity"> select * from test where id = #{id} </select>
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 這個(gè)Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); System.out.println(mapper); // 查詢數(shù)據(jù) TestEntity testEntity = mapper.get(1L); System.out.println(testEntity); } } }
掃描包路徑 mybatis-config.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"> <configuration> <!--掃描包路徑--> <typeAliases> <package name="entity"/> </typeAliases> <!--省略environments 看前2篇 --> <!--省略掃描 看前2篇--> </configuration>
用掃描包路徑的方式,實(shí)體類別名默認(rèn)就是java類首字母小寫
例如:TestEntity --> testEntity
還可以注解指定:
@Alias("testEntityxxoo") public class TestEntity { // 其他省略 }
如果寫了注解@Alias 別名就不是”testEntity”了 ,就變成”testEntityxxoo“
映射類型 | |
---|---|
_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 |
<!--增加--> <insert id="save" useGeneratedKeys="true" keyProperty="id"> INSERT INTO `test`( `name`, `salary`) VALUE (#{name}, #{salary}); </insert>
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 這個(gè)Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); // 測試id是否到了實(shí)體類里邊 TestEntity testEntity = new TestEntity(); testEntity.setName("小鴨子"); testEntity.setSalary(new BigDecimal(100000)); mapper.save(testEntity); System.out.println("主鍵:"+testEntity.getId()); } } }
輸出結(jié)果:
主鍵不是直接返回的,而是把主鍵值設(shè)置到插入的對象里的
<!--增加--> <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>
// 根據(jù)主鍵查詢 TestEntity get(Long id);
<!--根據(jù)主鍵查詢--> <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>
如果只有一個(gè)參數(shù),并且參數(shù)類型是Java基礎(chǔ)類型或String類型,那么使用這個(gè)參數(shù)的時(shí)候
#{xxoo} xxoo可以是任意字符 與方法輸入?yún)?shù)名稱無關(guān)
上邊例子中:id、xx、oo、aaabbb 都可以使用 ,但是哈,我們一般見名知意,傳遞的什么參數(shù)(id),我們xml就用#{傳遞的參數(shù)} 這不是必須但可以遵循這個(gè)規(guī)范
// 新增 void save(TestEntity testEntity);
<!--增加--> <insert id="save"> INSERT INTO `test`(`name`, `salary`) VALUE (#{name}, #{salary}) </insert>
這個(gè)很容易明白,實(shí)體類參數(shù)叫什么 這里#{}里邊就用什么
// 根據(jù)名稱模糊查詢 List<TestEntity> listByNameAndAge(@Param("name") String name,@Param("age") Integer age);
<!--根據(jù)名稱和年齡查尋--> <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>
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 這個(gè)Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); List<TestEntity> list = mapper.listByNameAndAge("小強(qiáng)", 19); System.out.println(list); } } }
用Map跟用實(shí)體類差不多 就key值當(dāng)做是實(shí)體類的字段名稱就可以
// 多參數(shù)Map 方式傳遞 List<TestEntity> listByNameAndAgeMap(Map<String, Object> param);
<!--param多參數(shù)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>
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 這個(gè)Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); Map<String,Object> param = new HashMap<>(); param.put("name", "小強(qiáng)"); param.put("age", 19); List<TestEntity> list = mapper.listByNameAndAgeMap(param); System.out.println(list); } } }
默認(rèn)有兩套參數(shù):
arg0、arg1、arg2、argxxx ; arg從0開始按照方法參數(shù)順序
param1、param2、param3、paramxxx ; param從1開始按照方法參數(shù)順序
// 什么都不用 List<TestEntity> listByNameAndAgeNone(String name, int age);
<!--用默認(rèn)順序--> <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>
<!--用默認(rèn)順序--> <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>
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 這個(gè)Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); List<TestEntity> list = mapper.listByNameAndAgeNone("小月", 20); System.out.println(list); } } }
注意傳遞數(shù)組的話,默認(rèn)參數(shù)名稱為arry
// 根據(jù)年齡集合查詢 List<TestEntity> listByAges(int[] ages);
<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>
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 這個(gè)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); } } }
例如:名稱是小強(qiáng)并且年齡是19 或者名稱是小月月年齡是20 的數(shù)據(jù)
// 根據(jù)多組參數(shù)查詢 List<TestEntity> listByNameAndAges(TestEntity[] params);
<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>
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 這個(gè)Mapper會編程Mybatis的代理Mapper TestMapper mapper = session.getMapper(TestMapper.class); TestEntity[] params = new TestEntity[2]; TestEntity testEntity01 = new TestEntity(); testEntity01.setName("小強(qiáng)"); 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 = '小強(qiáng)' and age = 19) or
(name = '小月月' and age = 20)
)
集合與數(shù)組差不多,但還是有點(diǎn)兒差別
不同點(diǎn)1: 集合如果不指定參數(shù)名稱的話默認(rèn)使用:collection或者list 不是array
不同點(diǎn)2:集合判斷大小是這樣的 用的size() 不是length
<if test="list != null and list.size() >0"></if>
select是Mybatis使用最多的標(biāo)簽之一,他與insert update delete不同,他不對數(shù)據(jù)庫值做改變,只是查
insert、 update、 delete 會對數(shù)據(jù)庫的值做變更,這三個(gè)標(biāo)簽可以混用,也就是說他們功能一樣,出三個(gè)的意義就是為了業(yè)務(wù)上可以區(qū)分一下是新增、修改還是刪除。一般我們也遵循這個(gè)使用。
感謝你能夠認(rèn)真閱讀完這篇文章,希望小編分享的“Mybatis中實(shí)體類別名、多參數(shù)、動態(tài)SQL的示例分析”這篇文章對大家有幫助,同時(shí)也希望大家多多支持億速云,關(guān)注億速云行業(yè)資訊頻道,更多相關(guān)知識等著你來學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。