一、mybatis 简单注解
关键注解词 :
@Insert : 插入sql , 和xml insert sql语法完全一样
@Select : 查询sql, 和xml select sql语法完全一样
@Update : 更新sql, 和xml update sql语法完全一样
@Delete : 删除sql, 和xml delete sql语法完全一样
@Param : 入参
@Results : 结果集合
@Result : 结果
1.bean领域模型
package cn.xm.exam.bean.common;
import java.util.Date;
import org.apache.struts2.json.annotations.JSON;
/**
* 查询年龄大于55岁的人()
* @author QiaoLiQiang
* @time 2018年1月25日下午4:03:02
*/
public class Message {
private String messageid;
private String name;
private String idcode;
private String sex;
private Date birthday;
private String emptype;
private String isdispose;
public String getMessageid() {
return messageid;
}
public void setMessageid(String messageid) {
this.messageid = messageid == null ? null : messageid.trim();
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public String getIdcode() {
return idcode;
}
public void setIdcode(String idcode) {
this.idcode = idcode == null ? null : idcode.trim();
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex == null ? null : sex.trim();
}
@JSON(format="yyyy-MM-dd")
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getEmptype() {
return emptype;
}
public void setEmptype(String emptype) {
this.emptype = emptype == null ? null : emptype.trim();
}
public String getIsdispose() {
return isdispose;
}
public void setIsdispose(String isdispose) {
this.isdispose = isdispose == null ? null : isdispose.trim();
}
@Override
public String toString() {
return "Message [messageid=" + messageid + ", name=" + name + ", idcode=" + idcode + ", sex=" + sex
+ ", birthday=" + birthday + ", emptype=" + emptype + ", isdispose=" + isdispose + "]";
}
}
2.Mapper接口定义:
MessageCustomMapper.java(最后五个方法注解实现)
package cn.xm.exam.mapper.common.custom;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import cn.xm.exam.bean.common.Message;
/**
* 消息mapper(查询内部外部年龄超过55的人)
*
* @author QiaoLiQiang
* @time 2018年1月25日下午4:07:45
*/
public interface MessageCustomMapper {
/**
* 根据员工类型查询超过55岁的人
*
* @return
* @param empType:员工类型:0短委,1内部
* @throws SQLException
*/
public List<Message> getMessageByEmptype(String empType) throws SQLException;
/**
* 修改消息的状态(设置为已读 1)
*
* @param messageId
* 消息ID
* @return
* @throws SQLException
*/
public int updateMessageStatusByMessageId(String messageId) throws SQLException;
/**
* 测试注解使用Mybatis
*/
@Insert(" insert into message values (#{messageid,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{idcode,jdbcType=CHAR}, #{sex,jdbcType=VARCHAR}, #{birthday,jdbcType=DATE}, #{emptype,jdbcType=VARCHAR},#{isdispose,jdbcType=VARCHAR})")
public int insertIntoByAnnotation(Message message) throws SQLException;
@Select("select * from message where messageid = #{messageId}")
public Message getMessageById(@Param("messageId")String messageId)throws SQLException;
@Update("update message set name=#{name} where messageid=#{messageid}")
public int updateMessageById(Message message)throws SQLException;
@Select("select * from message where messageid = #{messageId}")
public Map getMessageMapById(@Param("messageId")String messageId)throws SQLException;
@Delete("delete from message where messageid = #{messageId}")
public int deleteByMessageId(@Param("messageId")String messageid)throws SQLException;
}
MessageCustomMapper.xml(只实现部分方法)
<?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="cn.xm.exam.mapper.common.custom.MessageCustomMapper">
<!-- 查询未读的消息,每次显示6条 -->
<select id="getMessageByEmptype" parameterType="string"
resultType="cn.xm.exam.bean.common.Message">
select * from message where empType=#{value} and isDispose='0' limit 0,6
</select>
<!-- 根据ID设置消息状态 -->
<update id="updateMessageStatusByMessageId">
update message set isDispose='1' where messageid = #{value}
</update>
</mapper>
3.测试如下:
package cn.xm.exam.test.daoTest;
import java.sql.SQLException;
import java.util.Map;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import cn.xm.exam.bean.common.Message;
import cn.xm.exam.mapper.common.custom.MessageCustomMapper;
/**
* mybatis注解测试
*
* @author QiaoLiQiang
* @time 2018年3月10日下午3:47:40
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/application*")
public class MybatisAnnotationTest {
@Autowired
private MessageCustomMapper messageCustomMapper;
@Test
public void testAdd() throws SQLException{
Message message = new Message();
message.setSex("1");
message.setMessageid("001");
message.setName("张三");
int result = messageCustomMapper.insertIntoByAnnotation(message);
System.out.println(result);
}
@Test
public void testSelect() throws SQLException{
Message result = messageCustomMapper.getMessageById("001");
System.out.println(result);
}
@Test
public void testUpdate() throws SQLException{
Message message = new Message();
message.setSex("2");
message.setMessageid("001");
message.setName("李四");
int result = messageCustomMapper.updateMessageById(message);
System.out.println(result);
}
@Test
public void testSelectMap() throws SQLException{
Map result = messageCustomMapper.getMessageMapById("001");
System.out.println(result);
}
@Test
public void testDelete() throws SQLException{
int result = messageCustomMapper.deleteByMessageId("001");
System.out.println(result);
}
}
结果:
(1)testAdd方法:
(2)testSelect方法:
(3)testUpdate方法:
(4)testSelectMap方法:(测试返回类型是Map)
{sex=1, name=李四, messageid=001}
(5)testDelete方法:
1
二、动态SQL
对应关系如下
@Insert :@InsertProvider
@Select :@SelectProvider
@Update :@UpdateProvider
@Delete :@DeleteProvider
四个provider注解标识 使用了动态SQL, 使用语法格式 :
@UpdateProvider(type = UserProvider.class, method =
如何使用动态SQL:
public class UserProvider {
/**
* udpate
* @param UserDO userDO
* @return
*/
public String updateSQL(final UserDO userDO) {
return new SQL() {
{
UPDATE("t_user");
SET("gmt_modified = now()");
if (userDO.getUserName() != null) {
SET("user_name = #{userName}");
}
WHERE("id = #{id}}");
}
}.toString();
}
}
由此可见动态SQL还是用XML写SQL语句比较方便
本文提到的知识点比较基础, 如需深入了解见官网文档or看下源码。
总结:
1、xml 和 注解 如何选择 ? 因人而异, 每个人的撸码各有各的习惯, xml 、注解各有优缺点, xml的缺点 : 当模型属性有变更时, 需要从DO改到DAO改到xml, 想想就蛋疼了~ xml也有优点 , SQL片段复用方便, 语法平易近人, 不像注解, 构造个动态语句, 还得建个类, 而且当你需要构造一段SQL被多处引用时, annotation 显得无能为力了~重复写相同的SQL片段, 代码就显得冗余了, 这时必须借助 xml来抽取共同使用。吐槽了下mybatis注解, 那注解岂不是毫无用处? no , mybatis 适合模型属性经常变更的场景, 因为可以结合反射, 正则匹配动态构造SQL(纯粹瞎歪歪, 个人想法, 应该可以实现, 改天撸一发)。可以说, mybatis的注解 优点 很好弥补了 xml缺点。两者互补~