MyBatis 参数中的 #{param} 和 ${param}

创建User表

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(12) COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名称',
  `password` varchar(80) COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
  `last_token` text COLLATE utf8mb4_general_ci COMMENT '登陆时的token',
  `status` tinyint(4) DEFAULT '0' COMMENT '用户状态 -1代表已删除 0代表正常 1代表冻结',
  `created_at` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `updated_at` bigint(20) DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
复制代码

填充数据

BEGIN;
INSERT INTO `users` VALUES (1, 'test1', '$2y$10$.mzu/iNaNDJRex.vA06IQu7xg5vwvE8PYhsPQPaIV74uK4BcrzUUm', NULL, 0, 20190625075319, 20190625075319);
INSERT INTO `users` VALUES (2, 'test2', '$2y$10$P5ALENaHchjrwN8NXUrDAeuVGQmcoyuSzvfhaC073cgPh2slPEzZ.', NULL, 0, 20190625075639, 20190625075639);
INSERT INTO `users` VALUES (3, 'test3', '$2y$10$0fqd.LhBt4CBjM4W2X/Dse1i2jer1iJ8gtTjS4S7ZwF5wkxNpbqDC', NULL, 0, 20190625075644, 20190625075644);
INSERT INTO `users` VALUES (4, 'test4', '$2y$10$2NJQKm2XhGvxfLNJtEnZ7evKZyopCOvEpRsJlr0.TvDSezUu08FqG', NULL, 0, 20190625075647, 20190625075647);
INSERT INTO `users` VALUES (5, 'test5', '$2y$10$BfRNt3G9CXeiVjHTDrhTQ.5qkzxkXzWTft1w3qF4NlQG4eg/3lv5S', NULL, 0, 20190625075649, 20190625075649);
INSERT INTO `users` VALUES (6, 'test6', '$2y$10$13PeW77Ew9xT6xiK64FNp.Fg8BC2niYmT3Ad6mMxdG/2Rh4i028bG', NULL, 0, 20190625075655, 20190625075655);
INSERT INTO `users` VALUES (7, 'test7', '$2y$10$9pLgW11lDgvbOLM26GfEieR00i0EAiY.OvaG8N/pbbLXEZ/vbEW6u', NULL, 0, 20190625075658, 20190625075658);
INSERT INTO `users` VALUES (8, 'test8', '$2y$10$9vL5kyKK9AXQR6D1pHsUSuQ2j.iAktYLiqZgYMsXUDKqZUBDKG3KG', NULL, 0, 20190625075702, 20190625075702);
INSERT INTO `users` VALUES (9, 'test9', '$2y$10$xZpmI/sy8Imiqw42K6zdDOKqvxNvjGhZrIZQxcnECWYs14zDBR9v.', NULL, 0, 20190625075705, 20190625075705);
INSERT INTO `users` VALUES (10, 'test10', '$2y$10$bkQuVvfCasSvLbXoZrdq7eT3ki.689KqQXMadFQ0xpMukTwgta9l6', NULL, 0, 20190625075709, 20190625075709);
INSERT INTO `users` VALUES (11, 'test11', '$2y$10$2E4Nr6dqjAIK/sDh20a9AO1EpoI/VRgoiV4MlLLUhK8Tn7tZDO/lq', NULL, 0, 20190625085421, 20190625085421);
INSERT INTO `users` VALUES (12, 'test12', '$2y$10$buSHoWkLe/NoLWLwO67C4u04nYBCrGsVwsdAS6YYWj/LV9UqD41eS', NULL, 0, 20190625085518, 20190625085518);
INSERT INTO `users` VALUES (13, 'test13', '$2y$10$TPQgLzKoU1kAveN2Xi5NGuqwTj804SlXt6R3X3VfuRHiEULdl8SKK', NULL, 0, 20190625085528, 20190625085528);
COMMIT;
复制代码

2.使用#{param}

使用MyBatis的过程中，向 SQL 语句中传参是必不可少的。在使用MyBatis的大多数情况下是使用 #{} 来接收参数。

2.1.根据Name查询用户信息

编写根据用户ID查询用户信息的查询接口：

UserPo selectUserByName(@Param("name") String name);
复制代码

接口对应的XML文件 UserMapper.XML 中对应的 <select> 标签如下：

<select id="selectUserByName" parameterType="string" resultMap="BaseResult">
    SELECT
    *
    FROM USERS
    WHERE name = #{name}
</select>
复制代码

selectUserByName 的SQL语句接收一个String类型的参数，参数符号 #{name} 。

调用 selectUserByName("test1") 方法，根据Name查询用户信息，实际执行SQL如下：

SELECT * FROM USERS WHERE name = 'test1'
复制代码

运行截图：

2.2.等效JDBC代码

String selectUserByName = "SELECT * FROM USERS WHERE name=?";
PreparedStatement ps = conn.prepareStatement(selectUserByName);
ps.setInt(1,name);
复制代码

3.使用${param}

在MyBatis XML中使用 ${} 来接收参数。

3.1.根据Name查询用户信息

使用 2.1.根据Name查询用户信息 的查询方法，修改 UserMapper.xml 中 selectUserByName 的参数符号为 ${name} 。 再次调用 selectUserByName("test1") 方法，实际执行SQL如下：

SELECT * FROM USERS WHERE name = test1 
复制代码

但是SQL执行失败，此时的错误信息如下：

### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'test1' in 'where clause'
### The error may exist in file [/Users/heng/worker/java/springboot_practice/springboot_mybatis/target/classes/mapper/UserDao.xml]
### The error may involve com.example.mybatis.dao.UserDao.selectUserByName-Inline
### The error occurred while setting parameters
### SQL: SELECT         *         FROM USERS         WHERE name = test1
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'test1' in 'where clause'
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'test1' in 'where clause'] with root cause
复制代码

SQL语句错误， test1 不是 Users 表中的字段。

修改调用方法 selectUserByName("'test1'") ，实际执行SQL如下：

SELECT * FROM USERS WHERE name = 'test1'
复制代码

运行截图：

3.2.等效JDBC代码

String selectUserByName = "SELECT * FROM USERS WHERE name=" + name;
复制代码