SSM—mybatis框架-数据（CRUD）增删改查（单表）-#和$的区别

这篇博客介绍了MyBatis中对单表进行增删改查的基本操作，包括使用 Cars 实体类与 Mapper 接口的方法实现，并展示了对应的 XML 映射文件配置。同时，文章探讨了#和$的区别，#作为预编译占位符能防止SQL注入，而$直接拼接字符串易引发注入风险。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

1、增删改查（单表)

mybatis搭建https://blog.csdn.net/qq_44126024/article/details/115483367?spm=1001.2014.3001.5501

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Cars {
    private int id;
    private String name;
    private String phone;
    private Date out_date;
}

1.1、增

接口

public interface CarsMapper {

    int insertInfo(Cars cars);
    
}

Mapper.xml

<insert id="insertInfo" parameterType="cars">
    insert into cars(name,phone,out_date) value(#{name},#{phone},now())
</insert>

测试

@Test
public void insertInfo(){
    Cars cars = new Cars();
    cars.setName("宝马");
    cars.setPhone("12312312312");
    System.out.println(mapper.insertInfo(cars));
}

结果！

[DEBUG] 2021-04-07 14:27:59,726 Logging initialized using ‘class org.apache.ibatis.logging.log4j.Log4jImpl’ adapter.
[DEBUG] 2021-04-07 14:27:59,791 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:27:59,792 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:27:59,792 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:27:59,792 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:27:59,874 Opening JDBC Connection
[DEBUG] 2021-04-07 14:28:00,055 Created connection 2041416495.
[DEBUG] 2021-04-07 14:28:00,056 Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@79ad8b2f]
[DEBUG] 2021-04-07 14:28:00,059 ==> Preparing: insert into cars(name,phone,out_date) value(?,?,now())
[DEBUG] 2021-04-07 14:28:00,110 ==> Parameters: 宝马(String), 12312312312(String)
[DEBUG] 2021-04-07 14:28:00,114 <== Updates: 1
1

1.2、删

接口

public interface CarsMapper {
    
    int insertInfo(Cars cars);

    int deleteInfo(int id);
}

Mapper.xml

<delete id="deleteInfo" parameterType="int">
    delete from cars where id = #{id}
</delete>

测试

    @Test
    public void deleteInfo(){
        Cars cars = new Cars();
        System.out.println(mapper.deleteInfo(11));
    }

结果

[DEBUG] 2021-04-07 14:31:37,035 Logging initialized using ‘class org.apache.ibatis.logging.log4j.Log4jImpl’ adapter.
[DEBUG] 2021-04-07 14:31:37,130 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:31:37,130 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:31:37,131 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:31:37,131 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:31:37,253 Opening JDBC Connection
[DEBUG] 2021-04-07 14:31:37,523 Created connection 2041416495.
[DEBUG] 2021-04-07 14:31:37,523 Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@79ad8b2f]
[DEBUG] 2021-04-07 14:31:37,526 ==> Preparing: delete from cars where id = ?
[DEBUG] 2021-04-07 14:31:37,584 ==> Parameters: 3(Integer)
[DEBUG] 2021-04-07 14:31:37,588 <== Updates: 1
1

int类型会，那么其他类型也是一样的！就不一一列举了

1.3、改（多个参数）

多个参数，我们要使用@paran(“id”)绑定，单个参数同其他一样！

接口

public interface CarsMapper {

    int insertInfo(Cars cars);

    int deleteInfo(int id);
    
    int updateInfo(@Param("id") int id,@Param("name") String name);
    
}

Mapper.xml

<update id="updateInfo" >
    update cars set name = #{name} where id = #{id}
</update>

测试

@Test
public void updateInfo(){
    System.out.println(mapper.updateInfo(3,"王五"));
    System.out.println(mapper.getCars());
}

结果

[DEBUG] 2021-04-07 14:36:36,234 Logging initialized using ‘class org.apache.ibatis.logging.log4j.Log4jImpl’ adapter.
[DEBUG] 2021-04-07 14:36:36,317 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:36:36,317 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:36:36,318 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:36:36,318 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:36:36,404 Opening JDBC Connection
[DEBUG] 2021-04-07 14:36:36,582 Created connection 111156771.
[DEBUG] 2021-04-07 14:36:36,582 Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6a01e23]
[DEBUG] 2021-04-07 14:36:36,585 ==> Preparing: update cars set name = ? where id = ?
[DEBUG] 2021-04-07 14:36:36,620 ==> Parameters: 王五(String), 3(Integer)
[DEBUG] 2021-04-07 14:36:36,624 <== Updates: 1
1
[DEBUG] 2021-04-07 14:36:36,626 ==> Preparing: select * from cars
[DEBUG] 2021-04-07 14:36:36,631 ==> Parameters:
[DEBUG] 2021-04-07 14:36:36,653 <== Total: 3
[Cars(id=1, name=玛莎拉肚, phone=13611111111, out_date=Fri Apr 02 19:54:40 CST 2021), Cars(id=2, name=玛莎拉蒂, phone=11122233344, out_date=Mon Apr 05 15:18:56 CST 2021), Cars(id=3, name=王五, phone=11122233344, out_date=Mon Apr 05 15:21:48 CST 2021)]

Process finished with exit code 0

1.4、查

接口

public interface CarsMapper {

    int insertInfo(Cars cars);

    int updateInfo(@Param("id") int id,@Param("name") String name);

    int deleteInfo(int id);
    
     List<Cars> getCars();
    
}

Mapper.xml

<select id="getCars" resultType="cars">
    select * from cars
</select>

测试

@Test
public void getCars(){
    System.out.println(mapper.getCars());
}

结果！

[DEBUG] 2021-04-07 14:39:12,140 Logging initialized using ‘class org.apache.ibatis.logging.log4j.Log4jImpl’ adapter.
[DEBUG] 2021-04-07 14:39:12,198 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:39:12,199 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:39:12,199 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:39:12,199 PooledDataSource forcefully closed/removed all connections.
[DEBUG] 2021-04-07 14:39:12,280 Opening JDBC Connection
[DEBUG] 2021-04-07 14:39:12,447 Created connection 1653844940.
[DEBUG] 2021-04-07 14:39:12,447 Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6293abcc]
[DEBUG] 2021-04-07 14:39:12,450 ==> Preparing: select * from cars
[DEBUG] 2021-04-07 14:39:12,482 ==> Parameters:
[DEBUG] 2021-04-07 14:39:12,515 <== Total: 3
[Cars(id=1, name=玛莎拉肚, phone=13611111111, out_date=Fri Apr 02 19:54:40 CST 2021), Cars(id=2, name=玛莎拉蒂, phone=11122233344, out_date=Mon Apr 05 15:18:56 CST 2021), Cars(id=3, name=朱丽叶, phone=11122233344, out_date=Mon Apr 05 15:21:48 CST 2021)]

注意！！！ 我在测试的没有加事务提交，当然query语句不需要提交事务，在我们写业务代码的时候，切记不要忘记提交事务！

sqlSession.commit();
sqlSession.close();

2、#和$

2.1、#，占位符

 <insert id="insertInfo" parameterType="cars">
     insert into cars(name,phone,out_date) value(#{ame},#{phone},now())
 </insert>

#{} 占位符，是经过预编译的,编译好 SQL 语句再取值,#方式能够防止 sql 注入

#{}：select * from t_user where uid=#{uid}

我们通过日志组件打印的日志可以看到，#占位符，相当于prepareStatement

[DEBUG] 2021-04-07 17:02:30,526 ==> Preparing: insert into cars(name,phone,out_date) value(?,?,now())
[DEBUG] 2021-04-07 17:02:30,572 ==> Parameters: 宝马(String), 12312312312(String)

2.2、$，拼接符

<insert id="insertInfo" parameterType="cars">
    insert into cars(name,phone,out_date) value('${name}','${phone}',now())
</insert>

${} 拼接符，会传入参数字符串,取值以后再去编译 SQL 语句,$方式无法防止 Sql 注入

${} select * from t_user where uid= ${id}’

如果属性是字符串的话需要加单引 ‘${name}’ 相当于executeUpdate

[DEBUG] 2021-04-07 17:13:58,633 ==> Preparing: insert into cars(name,phone,out_date) value(‘dasd’,‘12312312312’,now())
[DEBUG] 2021-04-07 17:13:58,668 ==> Parameters:

注意:MyBatis 排序时使用 order by 动态参数时需要注意，用$而不是#