一、crud
selectKey
在sql语句前面执行(BEFORE)
先执行mysql里的select uuid();
赋值给id属性,然后执行sql插入语句
(主键回填?)
<insert id="addAccount" parameterType="org.kk.mybatis01.model.Account" >
<selectKey resultType="java.lang.String" keyProperty="id" order="BEFORE">
select uuid();
</selectKey>
insert into account (username,money) value (#{username},#{money});
</insert>
crud
mapper:
<?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="org.kk.mybatis01.mapper.AccountMapper">
<select id="getAllAccounts" resultType="org.kk.mybatis01.model.Account">
select * from account;
</select>
<insert id="addAccount" parameterType="org.kk.mybatis01.model.Account" >
<selectKey resultType="java.lang.String" keyProperty="id" order="BEFORE">
select uuid();
</selectKey>
insert into account (username,money) value (#{username},#{money});
</insert>
<delete id="deleteAccountById" parameterType="java.lang.Integer">
delete from account where id=#{id};
</delete>
<update id="updateAccountById" parameterType="org.kk.mybatis01.model.Account">
update account set username=#{username} where id=${id};
</update>
</mapper>
@Test
public void addAccount()
{
sqlSession=SqlSessionFactoryUtils.getInstance().openSession();
Account account=new Account();
account.setUsername("kk");
account.setMoney(200);
// account.setId(3);
int insert = sqlSession.insert("org.kk.mybatis01.mapper.AccountMapper.addAccount", account);
System.out.println("insert="+insert); //数据库受影响的行数
sqlSession.commit();//记得提交
}
@Test
public void deleteAccountById()
{
sqlSession=SqlSessionFactoryUtils.getInstance().openSession();
int delete = sqlSession.delete("org.kk.mybatis01.mapper.AccountMapper.deleteAccountById", 4);
System.out.println(delete);
sqlSession.commit();
}
@Test
public void updateAccountById()
{
sqlSession=SqlSessionFactoryUtils.getInstance().openSession();
Account account=new Account();
account.setId(3);
account.setUsername("kk2");
int update = sqlSession.update("org.kk.mybatis01.mapper.AccountMapper.updateAccountById", account);
System.out.println("update="+update);
sqlSession.commit();
}
@After
public void after(){
sqlSession.close();
}
以上的crud是模板化程度很高的代码,所以进行以下优化:
在mapper.xml中定义好操作后,只需要写接口:
package org.kk.mybatis01.mapper;
import org.kk.mybatis01.model.Account;
public interface AccountMapper {
Integer addAccount(Account account);
Integer deleteAccountById(Integer id);
Integer updateAccountById(Account account);
Account selectAccountById(Integer id);
}
在mybatis-config中配置mapper:
<mappers>
<!-- <mapper resource="org/kk/mybatis01/mapper/AccountMapper.xml"/>-->
<package name="org.kk.mybatis01.mapper"/>
</mappers>
注意package中写的只是包名。
然后使用:传入AccountMapper.class获得相应的mapper,通过mapper进行crud
@Test
public void selectAccountById()
{
sqlSession=SqlSessionFactoryUtils.getInstance().openSession();
AccountMapper mapper = sqlSession.getMapper(AccountMapper.class);
Account account = mapper.selectAccountById(2);
System.out.println(account);
}
总结实际开发中:需要一个接口mapper和mapper.xml文件
二、parameterType
mapper映射文件,是MyBatis的重要部分
#和$的区别
两个都可以传递参数,但是传递方式不太一样。
#号 相当于PrepareStatement,sql语句中使用占位符
$ 相当于Statement ,sql语句直接字符串拼接,而不是使用占位符
mapper中有多个参数如何处理
<update id="updateUsernameById">
update account set username=#{username} where id=#{id};
</update>
一个参数的话可以直接指定parameterType,
如果有多个参数,对应的接口可以使用@Param注解:
Integer updateUsernameById(@Param("username") String username, @Param("id") Long id);
参数是实体类,也可以@Param注解;
参数是map parameter.type=“java.util.HashMap”,那么sql语句有两个参数。