1.保存操作
<insert id="insertUser" parameterType="user" useGenerateKeys="true" keyProperty="id">
insert into user(username,birthday,sex,address)
values(#{username},#{birthday},#{sex},# {address})
</insert>
2.更新操作
<update id="updateUserById" parameterType="user">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address}
where id =#{id}
</update>
3.删除操作
<delete id="deleteUserById" parameterType="int">
delete from user where id=#{id}
</delete>
4.查询操作
resultType属性,表示使用什么类型的对象封装结果集中每一行数据
<select id="queryUserById" resultType="User" parameterType="int">
select * from user where id = #{id}
</select>
<!--#{} 占位符,中间的参数名可以是任意的,输出的时候,如果是Stirng会自动加上''
${} 占位符,原样输出,里面是什么就输出什么,参数名必须是value
-->
<select id="queryUserLikeName" resultType="user" parameterType="String">
select * from user where username like '%${value}%'
</select>
5.别名配置处理
别名 (typeAlias) 是一个类因为全限定名太长,使用一个简短名称去指代
MyBatis中别名不区分大小写
1)自定义别名
修改mybatis-config.xml文件:
直接给一个类起别名:<typeAlias type="net.seehope.spring.mybatis.pojo.User" alias="user"/>
给一个包中所有类起别名:
<typeAliases>
<package name="net.seehope.spring.mybatis.pojo" />
</typeAliases>
2)系统自带别名
处理结果集时使用的结果集中的列名,而并不是表中的列名。所以我们可以设置查询结果集中列的别名,把列的别名设置和对象中的属性同名。
6. resultMap元素
resultMap元素定义了一个ORM的具体映射规则
7.Java代码
public class MyBatisTest {
private static SqlSessionFactory sessionFactory;
private UserMapper userMapper;
private SqlSession session;
@BeforeClass
public static void init() throws IOException {
sessionFactory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("mybatis/mybatis-config.xml"));
}
@Before
public void openSession() {
session = sessionFactory.openSession();
userMapper = session.getMapper(UserMapper.class);
}
public void queryUserByNameTest() {
List<User> userList = userMapper.queryUserLikeName("mt");
for (User user2 : userList) {
System.out.println(ReflectionToStringBuilder.toString(user2, ToStringStyle.MULTI_LINE_STYLE));
}
}
public void querUserByIdTest() {
User user = userMapper.queryUserById(1);
System.out.println(ReflectionToStringBuilder.toString(user, ToStringStyle.MULTI_LINE_STYLE));
}
public void insertUserTest() {
System.out.println(ReflectionToStringBuilder.toString(
userMapper.insertUser(new User("fdasfdsa", new Date(), 'm', "123")), ToStringStyle.MULTI_LINE_STYLE));
}
public void updateUserByIdTest() {
userMapper.updateUserById(new User(1, "fdasfdsa", new Date(), 'm', "123"));
}
public void deleteUserByIdTest() {
userMapper.deleteUserById(51);
}
@After
public void commitSession() {
session.commit();
session.close();
}
}
8.动态SQL
1)if元素用于判断,一般用于是否应该包含某一个查询条件
<if test="boolean 表达式"></if>
<update id="updateUserExt" parameterType="UserExt">
update user
<set>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
<if test="address!=null and address!=''">
address=#{address},
</if>
</set>
where id =#{id}
</update>
2)foreach
SQL中有时候使用IN关键字,如WHERE id IN(10,20,30),此时可以使用${ids}直接拼接sql,但是会导致SQL注入问题,要避免SQL注入,只能使用#{}方式,此时就可以配合使用foreach元素了。
<delete id="forEachIdList">
delete from user where id in
<foreach collection="idList" item="id" open="AND id IN ("
close=")" separator=",">
#{id}
</foreach>
</delete>
3)sql和include
使用sql可以把相同片段起一个名字,并使用include元素在sql任意位置使用
<sql id="forEachIdList">
<foreach collection="idList" item="id" open="AND id IN ("
close=")" separator=",">
#{id}
</foreach>
</sql>
<select id="queryUserByIdList" parameterType="UserQueryVo"
resultType="User">
select * from user
<where>
<include refid="forEachIdList"></include>
</where>
</select>