特殊SQL的执行
一. 模糊查询
1. 准备工作
SpecialSQLMapper.java
创建映射文件SpecialSQLMapper.xml
测试:
2.模糊查询
SpecialSQLMapper.java
/**
* 通过用户名模糊查询用户信息
* @param mohu
* @return
*/
List<User> getUserByLike(@Param("mohu") String mohu);
SpecialSQLMapper.xml
<mapper namespace="com.atguigu.mybatis.mapper.SpecialSQLMapper">
<!--List<User> getUserByLike(@Param("mohu") String mohu);-->
<select id="getUserByLike" resultType="User">
<!-- select * from t_user where username like '%#{mohu}%' 这个会报错 -->
<!--select * from t_user where username like '%${mohu}%'-->
<!--select * from t_user where username like concat('%',#{mohu},'%')-->
select * from t_user where username like "%"#{mohu}"%"
</select>
</mapper>
测试:SpecialSQLMapperTest
@Test
public void testGetUserByLike(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<User> list = mapper.getUserByLike("a");
list.forEach(System.out::println);
}
拓展:
当执行 select * from t_user where username like '%#{mohu}%' 这个时会报错
错误如下
1.DEBUG 10-23 16:19:55,538 ==> Preparing: select * from t_user where username like '%?%' (BaseJdbcLogger.java:137)
%#{mohu}%解析后让问号代替变成'%?%' ?属于字符串的一部分 不会看成点位符来解析
也就是这条sql没有任何的点位符 可是我们写#{} 说明要有占位符,所以他就调用译编译的方法为占为符赋值,但是sql中并没有占位符
2. Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). SQL异常 当前参数的地索引超出范围 1>参数有个数 当前参数是0 操作的个数是1
报错解释
public void testJDBC(){
try {
Class.forName("");
Connection connection = DriverManager.getConnection("","","");
/*String sql = "select * from t_user where username like '%?%'";
PreparedStatement ps = connection.prepareStatement(sql);*/
//ps.setString(1, "a");
String sql = "insert into t_user values()";
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet resultSet = ps.getGeneratedKeys();
resultSet.next();
int id = resultSet.getInt(1);
} catch (Exception e) {
e.printStackTrace();
}
}
拓展:当报很多错误的时候我们应该从上往上看
主要错误主要体现在Caused by: Error Exception:
/**
* 批量删除
* @param ids
*/
void deleteMoreUser(@Param("ids") String ids);
SpecialSQLMapper.xml
<!--void deleteMoreUser(@Param("ids") String ids);//ids:4,5-->
<delete id="deleteMoreUser">
delete from t_user where id in(${ids})
</delete>
测试: SpecialSQLMapperTest
@Test
public void testDeleteMoreUser(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
mapper.deleteMoreUser("4,5");
}
为了测试我们把数据库添加到5 删除4和5 如图
删除后结果:
三. 动态设置表名
/**
* 动态设置表名,查询用户信息
* @param tableName
* @return
*/
List<User> getUserList(@Param("tableName") String tableName);
SpecialSQLMapper.xml
<!--List<User> getUserList(@Param("tableName") String tableName);-->
<select id="getUserList" resultType="User">
select * from ${tableName}
</select>
测试: SpecialSQLMapperTest
@Test
public void testGetUserList(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<User> list = mapper.getUserList("t_user");
list.forEach(System.out::println);
}
四. 添加功能获取自增的主键(重要,以后用得相当)
SpecialSQLMapper.java
/**
* 添加用户信息并获取自增的主键
* @param user
*/
void insertUser(User user);
SpecialSQLMapper.xml
<!--void insertUser(User user);-->
<!--
useGeneratedKeys:表示当前添加功能使用自增的主键
keyProperty:将添加的数据的自增主键为实体类类型的参数的属性赋值
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
</insert>
测试: SpecialSQLMapperTest
@Test
public void testInsertUser(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
User user = new User(null,"xiaoming","123456",23,"男","123@qq.com");
mapper.insertUser(user);
System.out.println(user);
}
useGeneratedKeys:设置使用自增的主键
keyProperty :因为增删改有统一的返回值是受影响的行数,因此只能将获取的自增的主键放在传输的参数 user 对象的某个属性中