MyBatis的CRUD操作
查询
标签:< select id="" resultType="" >
序号参数绑定
public interface UserDao {
//使用原生参数绑定
public User selectUserByIdAndPwd(Integer id , String pwd);
}
<select id="selectUserByIdAndPwd" resultType="user">
SELECT * FROM t_users
WHERE id = #{arg0} AND password = #{arg1} <!--arg0 arg1 arg2 ...-->
</select>
<select id="selectUserByIdAndPwd" resultType="user">
SELECT * FROM t_users
WHERE id = #{param1} AND password = #{param2} <!--param1 param2 param3 ...-->
</select>
注解参数绑定【推荐】
import org.apache.ibatis.annotations.Param; //引入注解
public interface UserDao {
//使用MyBatis提供的@Param进行参数绑定
public User selectUserByIdAndPwd(@Param("id") Integer id , @Param("pwd") String pwd);
}
<select id="selectUserByIdAndPwd" resultType="user">
SELECT * FROM t_users
WHERE id = #{id} AND password = #{pwd} <!-- 使用注解值 @Param("pwd") -->
</select>
Map参数绑定
public interface UserDao {
//添加Map进行参数绑定
public User selectUserByIdAndPwd_map(Map values);
}
Map values = new HashMap(); //测试类创建Map
values.put("myId",1); //自定义key,绑定参数
values.put("myPwd","123456");
User user = userDao.selectUserByIdAndPwd_map(values);
<select id="selectUserByIdAndPwd_map" resultType="user">
SELECT * FROM t_users
WHERE id = #{myId} AND password = #{myPwd} <!-- 通过key获得value -->
</select>
对象参数绑定
public interface UserDao {
//使用对象属性进行参数绑定
public User selectUserByUserInfo(User user);
}
<select id="selectUserByUserInfo" resultType="user">
SELECT * FROM t_users
WHERE id = #{id} AND password = #{password} <!-- #{id}取User对象的id属性值、#{password}同理 -->
</select>
模糊查询
public interface UserDao {
public List<User> selectUsersByKeyword(@Param("keyword") String keyword);
}
<mapper namespace="com.qf.mybatis.part1.different.UserDao">
<select id="selectUsersByKeyword" resultType="user">
SELECT * FROM t_users
WHERE name LIKE concat('%',#{keyword},'%') <!-- 拼接'%' -->
</select>
</mapper>
删除
标签:< delete id="" parameterType="" >
<delete id="deleteUser" parameterType="int">
DELETE FROM t_users
WHERE id = #{id} <!--只有一个参数时,#{任意书写}-->
</delete>
修改
标签:< update id="" parameterType="" >
<update id="updateUser" parameterType="user">
UPDATE t_users SET name=#{name}, password=#{password}, sex=#{sex}, birthday=#{birthday}
WHERE id = #{id} <!--方法参数为对象时,可直接使用#{属性名}进行获取-->
</update>
添加
标签:< insert id="" parameterType="" >
<!--手动主键-->
<insert id="insertUser" parameterType="user">
INSERT INTO t_users VALUES(#{id},#{name},#{password},#{sex},#{birthday},NULL);
</insert>
<!--自动主键-->
<insert id="insertUser" parameterType="user">
<!-- 自动增长主键,以下两种方案均可 -->
INSERT INTO t_users VALUES(#{id},#{name},#{password},#{sex},#{birthday},NULL);
INSERT INTO t_users VALUES(NULL,#{name},#{password},#{sex},#{birthday},NULL);
</insert>
主键回填
标签:< selectKey id="" parameterType="" order=“AFTER|BEFORE”>
通过last_insert_id()查询主键
create table t_product(
id int primary key auto_increment,
name varchar(50)
)default charset = utf8;
class Product{
private Integer id;
private String name;
//set+get ...
}
<mapper namespace="com.qf.mybatis.part1.basic.ProductDao">
<insert id="insertProduct" parameterType="product">
<selectKey keyProperty="id" resultType="int" order="AFTER"> <!-- 插入之后 -->
SELECT LAST_INSERT_ID() <!-- 适用于整数类型自增主键 -->
</selectKey>
INSERT INTO t_product(id,name) VALUES(#{id},#{name})
</insert>
</mapper>
通过uuid()查询主键
create table t_order(
id varchar(32) primary key, # 字符型主键
name varchar(50)
)default charset = utf8;
class Order{
private Integer id;
private String name;
//set+get ...
}
<mapper namespace="com.qf.mybatis.part1.basic.OrderDao">
<insert id="insertOrder" parameterType="order">
<selectKey keyProperty="id" resultType="String" order="BEFORE"><!-- 插入之前 -->
SELECT REPLACE(UUID(),'-','') <!-- 适用于字符类型主键 -->
</selectKey>
INSERT INTO t_order(id,name) VALUES(#{id},#{name})
</insert>
</mapper>