【mysql系列】动态sql语句
2023-09-27 14:29:27 时间
💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。
非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝
✨✨ 欢迎订阅本专栏 ✨✨
一.介绍
动态 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 道阻且长,行则将至,让我们一起加油吧！🌙🌙🌙
相关文章
- 【MySQL从入门到精通】【高级篇】（五）MySQL的SQL语句执行流程
- MySQL 5.6 复制：GTID 的优点和限制（第一部分）
- mysql中日期的加减
- MySQL导入数据库1118错误解决方案[ERR] 1118 - Row size too large (＞ 8126). Changing some columns to TEXT or BLOB
- MySQL 启动日志报错：Starting MySQL. ERROR! The server quit without updating PID file
- golang github.com/go-sql-driver/mysql 遇到的数据库,设置库设计不合理的解决方法
- 疯狂膜拜！mysql远程访问命令
- MySQL系列：一句SQL，MySQL是怎么工作的？
- MYSQL 基本SQL语句
- mysql sql语句中用括号处理or和and的运算顺序
- Oracle、Mysql和SQL Server数据库连接的URL写法
- mysql 分页存储过程 一次返回两个记录集（行的条数，以及行记录）,DataReader的Read方法和NextResult方法
- 一句SQL实现MYSQL的递归查询
- 如何使用Mysql Workbench导出一整个sql文件,sql包？
- MySQL千万级多表关联SQL语句调优
- redis作为mysql的缓存服务器(读写分离)
- mysql记录执行的SQL语句
- 2022-08-02 mysql/stonedb慢SQL-Q18-内存使用暴涨分析
- 2022-10-08 mysql列存储引擎-TPCH数据集1GB数据量-测试SQL
- 玩转Mysql系列 - 第25天：sql中的where条件在数据库中提取与应用浅析
- mysql中获取一天、一周、一月时间数据的各种sql语句写法
- 一文了解MySQL中的多版本并发控制
- MySql 中 case when then else end 的用法
- win10下安装mysql-8.0.23-winx64