<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis_demo.mapper.UserMapper">
<!-- #{}占位符 -->
<select id="selectUserById" parameterType="Integer" resultType="com.mybatis_demo.domain.User">
select * from t_user where uid = #{uid}
</select>
<!-- ${value}字符串拼接 容易发生sql注入,建议使用占位符#{}-->
<select id="selectUserByName" parameterType="String" resultType="com.mybatis_demo.domain.User">
<!-- select * from t_user where uname like '%${value}%' -->
select * from t_user where uname like concat('%',#{uname},'%')
</select>
<insert id="insertUser" parameterType="com.mybatis_demo.domain.User">
insert into t_user values(null,#{uname},#{age},#{address})
</insert>
<delete id="deleteUserById" parameterType="Integer">
delete from t_user where uid=#{uid}
</delete>
<update id="updateUser" parameterType="com.mybatis_demo.domain.User">
<!-- update t_user set uname=#{uname},age=#{age},address=#{address} where uid=#{uid} -->
update t_user
<!-- set标签可以拼接update条件,同时能够删除多余的符号,平时拼接字符串总是需要手动去除多余的逗号什么的分隔符 -->
<!-- <set>
<if test="uname!=null and address!=''">uname=#{uname},</if>
<if test="age!=null">age=#{age},</if>
<if test="address!=null and address!=''">address=#{address}</if>
</set> -->
<trim prefix="set" suffixOverrides=",">
<if test="uname!=null and address!=''">uname=#{uname},</if>
<if test="age!=null">age=#{age},</if>
<if test="address!=null and address!=''">address=#{address}</if>
</trim>
where uid=#{uid}
</update>
<select id="selectByMap" parameterType="hashmap" resultType="com.mybatis_demo.domain.User">
select * from t_user where uname like concat('%',#{uname},'%') and uid<#{uid}
</select>
<select id="selectByList" parameterType="java.util.List" resultType="com.mybatis_demo.domain.User">
select * from t_user where uid in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<select id="selectReturnString" parameterType="String" resultType="String">
select address from t_user where uname=#{uname}
</select>
<select id="selectByMoreParamter" resultType="com.mybatis_demo.domain.User">
<!-- select * from t_user where uname like concat('%',#{param1},'%') and age = #{param2} and address = #{param3} -->
select * from t_user where uname like concat('%',#{arg0},'%') and age = #{arg1} and address = #{arg2}
</select>
</mapper>
三、mapper动态代理接口
public interface UserMapper {
//mybatis使用mapper动态代理
//4大原则,一个注意
//1.接口中的方法名需要与对应mapper.xml的id一致
//2.接口中的返回值需要与对应mapper.xml的返回值类型保持一致
//3.接口中的参数需要与对应mapper.xml的参数类型、个数、参数名保持一致
//4.对应mapper.xml的名字空间需要修改成对应接口的全包名
//注意:mapper动态代理根据返回值类型,mybatis会自动选择调用selectone还是selectlist....
//根据用户id获取用户信息
public User selectUserById(Integer uid);
//根据用户名模糊查询用户信息
public List<User> selectUserByName(String uname);
//插入用户
public void insertUser(User user);
//根据用户id删除用户
public void deleteUserById(Integer uid);
//更新用户信息
public void updateUser(User user);
//多参数查询
//用map封装条件
public List<User> selectByMap(Map<String,Object> map);
//用list封装条件
public List<User> selectByList(List<Integer> list);
//直接传多个参数
public User selectByMoreParamter(String uname,Integer age,String address);
}
四、测试代码
package com.mybatis_demo.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.mybatis_demo.domain.User;
import com.mybatis_demo.mapper.UserMapper;
public class TestMapper {
//多条件查询
//当传入多个参数时,使用#{arg0}、#{arg1},arg+下标获取参数,下标从0开始
//或者使用#{param1},param+下标获取参数下标从1开始
@Test
public void test_selectByMoreParamter() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selectByMoreParamter("白", 4, "北京朝阳区");
System.out.println(user);
} catch (IOException e) {
e.printStackTrace();
}
}
//用list封装条件
@Test
public void test_selectByList() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> list = new ArrayList<Integer>();
list.add(5);
list.add(3);
list.add(123);
list.add(19);
List<User> users = mapper.selectByList(list);
for (User user : users) {
System.out.println(user);
}
} catch (IOException e) {
e.printStackTrace();
}
}
//用map封装条件
@Test
public void test_selectByMap() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<String,Object>();
map.put("uname", "小");
map.put("uid", 10);
List<User> list = mapper.selectByMap(map);
for (User user : list) {
System.out.println(user);
}
} catch (IOException e) {
e.printStackTrace();
}
}
//测试更新用户
@Test
public void test_updateUser() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUid(5);
user.setUname("大白");
mapper.updateUser(user);
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
//测试删除用户
@Test
public void test_deleteUserById() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
mapper.deleteUserById(2);
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
//测试插入用户
@Test
public void test_insertUser() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUname("一只程序鸟");
user.setAge(23);
user.setAddress("东莞");
mapper.insertUser(user);
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
//测试根据用户名模糊查询用户信息
@Test
public void test_selectUserByName() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory build = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = build.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> list = mapper.selectUserByName("老");
for (User user : list) {
System.out.println(user);
}
} catch (IOException e) {
e.printStackTrace();
}
}
//测试根据id获取用户信息
@Test
public void test_selectUserById() {
try {
//读取配置文件
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建SqlSessionFactoryBuilder对象,用来获取SqlSessionFactory对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
SqlSessionFactory sqlSessionFactory = builder.build(in);
//利用sqlSessionFactory获取session对象
SqlSession session = sqlSessionFactory.openSession();
//通过session对象获取对应mapper接口
UserMapper mapper = session.getMapper(UserMapper.class);//获取对应的mapper
//利用对应mapper接口获取对象
User user = mapper.selectUserById(102);//调用对应mapper接口的方法
System.out.println(user);
} catch (IOException e) {
e.printStackTrace();
}
}
}