💝💝💝欢迎来到我的博客,很高兴能够在这里和您见面!希望您在这里可以感受到一份轻松愉快的氛围,不仅可以获得有趣的内容和知识,也可以畅所欲言、分享您的想法和见解。
非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝
✨✨ 欢迎订阅本专栏 ✨✨
一.介绍
动态 SQL 是指在运行时动态生成 SQL 语句,它可以根据不同的条件和参数生成不同的 SQL 语句,从而实现灵活性和可重用性。
在许多编程语言中,动态 SQL 可以使用字符串拼接或参数化查询的方式来实现。使用字符串拼接的方法,可以通过在字符串中插入变量或条件语句,来动态构建 SQL 语句。
动态 SQL 可以用于很多不同的场景,比如:
- 搜索和过滤:动态 SQL 可以根据用户的搜索条件和过滤条件生成不同的 SQL 查询语句,从而实现灵活的搜索和过滤功能。
- 分页和排序:动态 SQL 可以根据用户的分页和排序参数生成不同的 SQL 查询语句,从而实现分页和排序功能。
- 动态表名和列名:动态 SQL 可以根据不同的表名和列名生成不同的 SQL 查询语句,从而实现灵活的查询功能。
- 多表查询:动态 SQL 可以根据不同的关联条件和查询条件生成不同的 SQL 查询语句,从而实现多表查询功能。
- 动态更新和删除:动态 SQL 可以根据不同的更新和删除条件生成不同的 SQL 更新和删除语句,从而实现灵活的更新和删除功能。
需要注意的是,动态 SQL 也有一些缺点和风险,比如 SQL 注入攻击。如果动态 SQL 中包含不受信任的用户输入,那么攻击者可以通过输入特定的字符串来修改 SQL 查询语句,从而执行恶意操作。因此,使用动态 SQL 时需要谨慎处理用户输入,并使用参数化查询或其他安全措施来防止 SQL 注入攻击。
二.使用
1.简单查询
@InsertProvider(type = StockMarketProvider.class, method = "insertStockMarketSql")
@SelectKey(keyProperty = "id", keyColumn = "id", before = false, resultType = int.class, statement = {"SELECT LAST_INSERT_ID() AS ID"})
public void insertStockMarket(StockMarket stockMarket);
2.多列
@SelectProvider(type = StockMarketProvider.class, method = "selectByStockIdSql")
@Results(value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "dqj", column = "dqj"),
@Result(property = "bhz", column = "bhz"),
@Result(property = "bhl", column = "bhl"),
@Result(property = "jkp", column = "jkp"),
@Result(property = "zsp", column = "zsp"),
@Result(property = "zgj", column = "zgj"),
@Result(property = "zdj", column = "zdj"),
@Result(property = "ztj", column = "ztj"),
@Result(property = "dtj", column = "dtj"),
@Result(property = "hsl", column = "hsl"),
@Result(property = "zf", column = "zf"),
@Result(property = "syl", column = "syl"),
@Result(property = "sjl", column = "sjl"),
@Result(property = "cjl", column = "cjl"),
@Result(property = "cje", column = "cje"),
@Result(property = "zsz", column = "zsz"),
@Result(property = "ltsz", column = "ltsz"),
@Result(property = "mr1", column = "mr1"),
@Result(property = "mr2", column = "mr2"),
@Result(property = "mr3", column = "mr3"),
@Result(property = "mr4", column = "mr4"),
@Result(property = "mr5", column = "mr5"),
@Result(property = "mc1", column = "mc1"),
@Result(property = "mc2", column = "mc2"),
@Result(property = "mc3", column = "mc3"),
@Result(property = "mc4", column = "mc4"),
@Result(property = "mc5", column = "mc5"),
@Result(property = "mr1Num", column = "mr1_num"),
@Result(property = "mr2Num", column = "mr2_num"),
@Result(property = "mr3Num", column = "mr3_num"),
@Result(property = "mr4Num", column = "mr4_num"),
@Result(property = "mr5Num", column = "mr5_num"),
@Result(property = "mc1Num", column = "mc1_num"),
@Result(property = "mc2Num", column = "mc2_num"),
@Result(property = "mc3Num", column = "mc3_num"),
@Result(property = "mc4Num", column = "mc4_num"),
@Result(property = "mc5Num", column = "mc5_num"),
@Result(property = "status", column = "status"),
@Result(property = "stockId", column = "stock_id")
})
public StockMarket selectByStockId(int stockId);
3.扩展类
@UpdateProvider(type = StockMarketProvider.class, method = "updateByStockIdSql")
public void update(StockMarket stockMarket);
4.limit
@SelectProvider(type = StockMarketProvider.class, method = "selectByOrderAtLimitSql")
@Results(value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "dqj", column = "dqj"),
@Result(property = "bhz", column = "bhz"),
@Result(property = "bhl", column = "bhl"),
@Result(property = "jkp", column = "jkp"),
@Result(property = "zsp", column = "zsp"),
@Result(property = "zgj", column = "zgj"),
@Result(property = "zdj", column = "zdj"),
@Result(property = "ztj", column = "ztj"),
@Result(property = "dtj", column = "dtj"),
@Result(property = "hsl", column = "hsl"),
@Result(property = "zf", column = "zf"),
@Result(property = "syl", column = "syl"),
@Result(property = "sjl", column = "sjl"),
@Result(property = "cjl", column = "cjl"),
@Result(property = "cje", column = "cje"),
@Result(property = "zsz", column = "zsz"),
@Result(property = "ltsz", column = "ltsz"),
@Result(property = "mr1", column = "mr1"),
@Result(property = "mr2", column = "mr2"),
@Result(property = "mr3", column = "mr3"),
@Result(property = "mr4", column = "mr4"),
@Result(property = "mr5", column = "mr5"),
@Result(property = "mc1", column = "mc1"),
@Result(property = "mc2", column = "mc2"),
@Result(property = "mc3", column = "mc3"),
@Result(property = "mc4", column = "mc4"),
@Result(property = "mc5", column = "mc5"),
@Result(property = "mr1Num", column = "mr1_num"),
@Result(property = "mr2Num", column = "mr2_num"),
@Result(property = "mr3Num", column = "mr3_num"),
@Result(property = "mr4Num", column = "mr4_num"),
@Result(property = "mr5Num", column = "mr5_num"),
@Result(property = "mc1Num", column = "mc1_num"),
@Result(property = "mc2Num", column = "mc2_num"),
@Result(property = "mc3Num", column = "mc3_num"),
@Result(property = "mc4Num", column = "mc4_num"),
@Result(property = "mc5Num", column = "mc5_num"),
@Result(property = "status", column = "status"),
@Result(property = "stockId", column = "stock_id")
})
public List<StockMarket> selectByOrderAtLimit(@Param("orderby") String orderBy, @Param("limit") int limit);
5.Provider 扩展
@SelectProvider(type = StockMarketProvider.class, method = "selectDiefuListSql")
@Results(value = {
@Result(id = true, property = "id", column = "id"),
@Result(property = "dqj", column = "dqj"),
@Result(property = "bhz", column = "bhz"),
@Result(property = "bhl", column = "bhl"),
@Result(property = "jkp", column = "jkp"),
@Result(property = "zsp", column = "zsp"),
@Result(property = "zgj", column = "zgj"),
@Result(property = "zdj", column = "zdj"),
@Result(property = "ztj", column = "ztj"),
@Result(property = "dtj", column = "dtj"),
@Result(property = "hsl", column = "hsl"),
@Result(property = "zf", column = "zf"),
@Result(property = "syl", column = "syl"),
@Result(property = "sjl", column = "sjl"),
@Result(property = "cjl", column = "cjl"),
@Result(property = "cje", column = "cje"),
@Result(property = "zsz", column = "zsz"),
@Result(property = "ltsz", column = "ltsz"),
@Result(property = "mr1", column = "mr1"),
@Result(property = "mr2", column = "mr2"),
@Result(property = "mr3", column = "mr3"),
@Result(property = "mr4", column = "mr4"),
@Result(property = "mr5", column = "mr5"),
@Result(property = "mc1", column = "mc1"),
@Result(property = "mc2", column = "mc2"),
@Result(property = "mc3", column = "mc3"),
@Result(property = "mc4", column = "mc4"),
@Result(property = "mc5", column = "mc5"),
@Result(property = "mr1Num", column = "mr1_num"),
@Result(property = "mr2Num", column = "mr2_num"),
@Result(property = "mr3Num", column = "mr3_num"),
@Result(property = "mr4Num", column = "mr4_num"),
@Result(property = "mr5Num", column = "mr5_num"),
@Result(property = "mc1Num", column = "mc1_num"),
@Result(property = "mc2Num", column = "mc2_num"),
@Result(property = "mc3Num", column = "mc3_num"),
@Result(property = "mc4Num", column = "mc4_num"),
@Result(property = "mc5Num", column = "mc5_num"),
@Result(property = "status", column = "status"),
@Result(property = "stockId", column = "stock_id")
})
public List<StockMarket> selectDiefuList(int limit);
❤️❤️❤️本人水平有限,如有纰漏,欢迎各位大佬评论批评指正!😄😄😄
💘💘💘如果觉得这篇文对你有帮助的话,也请给个点赞、收藏下吧,非常感谢!👍 👍 👍
🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧!🌙🌙🌙