一,查询
1.1,单个参数的绑定
//单个参数传递
public User findUserById(Integer id);
<!--
parameterType:指定输入参数的类型
resultType:指定数据结果封装的数据类型
#{id}:它代表占位符,相当于原来 jdbc 部分所学的?,都是用于替换实际的数据。
-->
<select id="findUserById" parameterType="java.lang.Integer"
resultType="com.by.pojo.User" >
select * from user where id=#{id}<!--只有一个参数时,#{任意书写}-->
</select>
@Test
public void testFindUserById(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.findUserById(41);
System.out.println(user);
}
1.2,序号参数绑定
//传递多个参数
public User findUserByIdAndName(Integer id, String username);
<select id="findUserByIdAndName" resultType="com.by.pojo.User" >
SELECT * FROM user
WHERE id = #{arg0} AND username = #{arg1} <!--arg0 arg1 arg2 ...-->
</select>
<select id="findUserByIdAndName" resultType="com.by.pojo.User" >
SELECT * FROM user
WHERE id = #{param1} AND username = #{param2} <!--param1 param2 param3 ...-->
</select>
@Test
public void testFindUserByIdAndName(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.findUserByIdAndName(41,"张三丰");
System.out.println(user);
}
1.3,注解参数绑定
@Test
public void testFindUserByIdAndName(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.findUserByIdAndName(41,"张三丰");
System.out.println(user);
}
<select id="findUserByIdAndName2" resultType="com.by.pojo.User" >
SELECT * FROM user
WHERE id = #{id} AND username = #{username}
</select>
@Test
public void testFindUserByIdAndName2(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = userDao.findUserByIdAndName2(41,"张三丰");
System.out.println(user);
}
1.4,对象参数绑定
//使用对象属性进行参数绑定
public User findUserByUserInfo(User user);
<select id="findUserByUserInfo" parameterType="com.by.pojo.User"
resultType="com.by.pojo.User">
SELECT * FROM user
WHERE id = #{id} AND username = #{username}<!--参数为对象时,#{属性名}-->
</select>
@Test
public void testFindUserByName(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User userInfo = new User();
userInfo.setId(41);
userInfo.setUsername("张三丰");
User user = userDao.findUserByUserInfo(userInfo);
System.out.println(user);
}
1.5,Map参数绑定
//使用Map进行参数绑定
public User findUserByMap(Map<String, Object> map);
<select id="findUserByMap"
parameterMap="java.util.Map" resultType="com.by.pojo.User">
SELECT * FROM user
WHERE id = #{id} AND username = #{username}
</select>
@Test
public void testFindUserByMap(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("id",41);
map.put("username","张三丰");
User user = userDao.findUserByMap(map);
System.out.println(user);
}
1.6,模糊查询
//模糊查询
public List<User> findUserByName(String username);
<select id="findUserByName" parameterType="string" resultType="com.by.pojo.User">
<!-- select * from user where username like concat('%',#{username},'%') -->
select * from user where username like '%${value}%'<!--${}括号中只能是value-->
</select>
@Test
public void testFindUserByName(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.findUserByName("张");
for (User user : userList) {
System.out.println(user);
}
}
1.7聚合函数查询
//聚合函数查询
public Integer getTotal();
<!--聚合函数查询-->
<select id="getTotal" resultType="int">
SELECT COUNT(id) FROM user
</select>
@Test
public void testGetTotal(){
Integer total = userDao.getTotal();
System.out.println(total);
}
二,删除
//删除
public void deleteUserById(Integer id);
<delete id="deleteUserById" parameterType="Integer">
DELETE FROM user
WHERE id = #{id}
</delete>
@Test
public void testDeleteUserById(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
userDao.deleteUserById(41);
sqlSession.commit();
}
三,修改
//修改
public void updateUserById(User user);
<update id="updateUserById" parameterType="com.by.pojo.User">
update user set username=#{username},password=#{password},
birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
@Test
public void testUpdateUserById(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setUsername("马德华");
user.setPassword("111");
user.setBirthday(new Date());
user.setSex("男");
user.setAddress("高老庄");
user.setId(42);
userDao.updateUserById(user);
sqlSession.commit();
}
四,添加
//添加
public void insertUser(User user);
<insert id="insertUser" parameterType="com.by.pojo.User">
<!--要注意主键回填
主键回填:新增之后,获取新增记录的id值
keyProperty="id":主键对应实体类的属性
order="AFTER":先执行插入语句,之后再执行查询语句
resultType="java.lang.Integer":主键的数据类型
-->
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
/*查询出刚刚插入的记录自增长id*/
select last_insert_id();
</selectKey>
insert into user(username,password,birthday,sex,address)
values(#{username},#{password},#{birthday},#{sex},#{address});
</insert>
或者
<!--
useGeneratedKeys=“true”:获取数据库生成的主键
keyProperty=“id”:主键对应实体类的属性
-->
<insert id="insertUser" useGeneratedKeys="true"
keyProperty="id" parameterType="com.by.pojo.User">
<!--
主键回填:新增之后,获取新增记录的id值
keyProperty="id":主键对应实体类的属性
order="AFTER":先执行插入语句,之后再执行查询语句
resultType="java.lang.Integer":主键的数据类型<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
/*查询出刚刚插入的记录自增长id*/
select last_insert_id();
</selectKey>
-->
insert into user(username,password,birthday,sex,address)
values(#{username},#{password},#{birthday},#{sex},#{address});
</insert>
@Test
public void testInsertUser(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setUsername("刘德华");
user.setPassword("111");
user.setBirthday(new Date());
user.setSex("男");
user.setAddress("香港");
userDao.insertUser(user);
System.out.println("新增记录的id值:"+user.getId());
sqlSession.commit();
}
六,sql注入问题
使用${} 符号有sql注入的问题
//sql注入
public User login(User user);
<select id="login" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
select * from user where username = '${username}' and password = '${password}'
</select>
@Test
public void testLogin(){
UserDao userDao = sqlSession.getMapper(UserDao.class);
User userInfo = new User();
userInfo.setUsername("张三丰' #");
userInfo.setPassword("123");
User user = userDao.login(userInfo);
System.out.println(user);
}
2.1, #{} 和{}的区别
#{} 符
1.#{} 表示一个占位符 通过#{} 可以实现 PreparedStatement(预编译)向占位符设置值,自动进行java 类型 和 jdbc类型转换
2.#{} 可以接受简单类型值或者pojo属性值, 如果 parameterType 传输单个简单类 型值,#{}括号中可以是 value 或其它名称。
3.#{} 可以有效的防止sql注入
${} 符
1.${}表示拼接sql串 通过${} 可以将parameterType 传入的内容拼接在sql中并且不进行jdbc类型转换
2.${} 可以接收简单类型或者pojo 属性值,结果parameterType 传输单个简单类型值,${} 括号中只能是value
3.${} 无法有效的防止sql注入
#{} 和 ${} 的区别表格
底层 | jdbc类型转换 | sql注入 | 单个简单类型参数 | |
#{} | preparedStatement | 转换 | 防止 | 任意 |
${} | Statement | 不转换 | 不防止 | value |
注意:
除模糊查询外,尽量不适用${}