mybatis
<?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.springmvc.dao.UserMapper">
<resultMap id="UserMap" type="com.springmvc.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex" ></result>
<result property="address" column="address"></result>
</resultMap>
<select id="findUserById" parameterType="int" resultType="com.springmvc.domain.User">
SELECT * FROM USER WHERE id=#{id}
</select>
<insert id="addUser" parameterType="com.springmvc.domain.User">
INSERT INTO USER (username,birthday,sex,address)VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
<select id="findAll" resultType="com.springmvc.domain.User" >
SELECT * FROM USER
</select>
<update id="updateUser" parameterType="com.springmvc.domain.User" >
UPDATE USER SET username=#{username} WHERE id=#{id}
</update>
<!--添加用戶(hù)-->
<insert id="insertUser" parameterType="com.mybaties.domain.User">
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO USER (username,birthday,sex,address)
VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
<select id="groupbyUsername" resultType="map">
SELECT username,COUNT(*) as count FROM USER GROUP BY username ORDER BY count DESC
</select>
</mapper>
mapper
<mapper namespace="com.springmvc.dao.UserMapper">
<resultMap id="UserMap" type="com.springmvc.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex" ></result>
<result property="address" column="address"></result>
</resultMap>
<select id="findUserById" parameterType="int" resultType="com.springmvc.domain.User">
SELECT * FROM USER WHERE id=#{id}
</select>
</mapper>
- namespace 命名空間是對(duì)應(yīng)的mapper的全限定名稱(chēng)。
resultMap bean屬性與數(shù)據(jù)庫(kù)字段對(duì)應(yīng)關(guān)系
<resultMap id="UserMap" type="com.springmvc.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex" ></result>
<result property="address" column="address"></result>
</resultMap>
- 當(dāng)Java bean 屬性與數(shù)據(jù)庫(kù)字段不一致時(shí)矫户，需要一個(gè)對(duì)應(yīng)關(guān)系，resultMap表示對(duì)應(yīng)關(guān)系茧痕。
- id（唯一） 用于select標(biāo)簽 的resultMap屬性值。
- type java bean 全限定名稱(chēng)。
- property bean的屬性名稱(chēng)袭艺。
- column 數(shù)據(jù)庫(kù)字段名稱(chēng)掏熬。
普通sql
select
<select id="findUserById" parameterType="int" resultType="com.springmvc.domain.User">
SELECT * FROM USER WHERE id=#{id}
</select>
- id 對(duì)應(yīng)mapper 的方法佑稠。
- parameterType 輸入?yún)?shù)類(lèi)型。
- resultType最小對(duì)象粒度 這里返回的數(shù)據(jù)是List<User>
對(duì)應(yīng)的resultType 是User旗芬。
- "#{id}" 表示占位符舌胶。
update
<update id="updateUser" parameterType="com.springmvc.domain.User" >
UPDATE USER SET username=#{username} WHERE id=#{id}
</update>
返回插入的id
<insert id="insertUser" parameterType="com.mybaties.domain.User">
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO USER (username,birthday,sex,address)
VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
聚合操作
<select id="groupbyUsername" resultType="map">
SELECT username,COUNT(*) as count FROM USER GROUP BY username ORDER BY count DESC
</select>
- resultType 這里返回的數(shù)據(jù)類(lèi)型是List<Map<String,Object>>
所以resultYpe對(duì)應(yīng)的最小粒度是map。
分頁(yè)
<select id="fenye" parameterType="Map" resultType="com.springmvc.domain.User">
SELECT * FROM USER limit #{start},#{count}
</select>
@RequestMapping("fenye")
public String fenye(Model model, Integer start) {
Map<String ,Object> parameter = new HashMap<>();
parameter.put("start",start*5);
parameter.put("count",5);
List<User> datas = userService.fenye(parameter);
System.out.println(datas);
model.addAttribute("name", datas);
return "demo";
}
動(dòng)態(tài)sql操作
動(dòng)態(tài)查詢(xún)
<!--動(dòng)態(tài)sql 查詢(xún)-->
<select id="dynamicSqlwithSelect" parameterType="map" resultType="com.springmvc.domain.User">
SELECT * FROM USER
<where>
<if test="pname!=null">
AND username LIKE #{pname}
</if>
<if test="paddress!=null" >
AND address=#{paddress}
</if>
</where>
</select>
動(dòng)態(tài)更新
<update id="dynamicSqlUpdate" parameterType="com.springmvc.domain.User">
update USER
<set>
<if test="username!=null">
username=#{username},
</if>
<if test="address != null">
address=#{address},
</if>
</set>
WHERE id=#{id}
</update>
- 不用寫(xiě)set 最后“,” 自動(dòng)刪除疮丛。
動(dòng)態(tài)刪除
<delete id="dynamicSqldelete" >
delete from user where id in
<foreach item="ids" collection="list" open="(" close=")" separator=",">
${ids}
</foreach>
</delete>
動(dòng)態(tài)插入
<sql id="key">
<trim suffixOverrides=",">
<if test="id!=null">
id,
</if>
<if test="username != null">
username,
</if>
<if test="birthday != null">
birthday,
</if>
<if test="address != null">
address,
</if>
<if test="sex != null">
sex,
</if>
</trim>
</sql>
<sql id="val">
<trim suffixOverrides=",">
<if test="id !=null">
#{id},
</if>
<if test="username!=null">
#{username},
</if>
<if test="birthday != null">
#{birthday},
</if>
<if test="address != null">
#{address},
</if>
<if test="sex!=null">
#{sex},
</if>
</trim>
</sql>
<insert id="dynamicInsert" parameterType="com.springmvc.domain.User">
insert into USER(<include refid="key"/>) VALUES (<include refid="val"/>)
</insert>
聯(lián)表查詢(xún)
bean
public class User implements Serializable {
private Integer id;
private String username="";
private String birthday;
private String sex;
private String address;
private List<Address> addressList = new ArrayList<>();
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Address> getAddressList() {
return addressList;
}
public void setAddressList(List<Address> addressList) {
this.addressList = addressList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday='" + birthday + '\'' +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", addressList=" + addressList +
'}';
}
}
public class Address {
private int id;
private String name;
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Address{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
一對(duì)一 一個(gè)地址對(duì)應(yīng)一個(gè)用戶(hù)
AddressMapper.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="com.springmvc.dao.AddressMapper">
<resultMap id="address" type="com.springmvc.domain.Address">
<id property="id" column="id"/>
<result property="name" column="name"></result>
</resultMap>
<resultMap id="address2" type="com.springmvc.domain.Address">
<id property="id" column="id"/>
<result property="name" column="name"></result>
<association property="user" resultMap="com.springmvc.dao.UserMapper.UserMap"/>
</resultMap>
<select id="findById" parameterType="int" resultMap="address2" >
SELECT a.name,u.username FROM address a INNER JOIN user u ON a.uid=u.id AND a.id=#{id}
</select>
</mapper>
- association 聯(lián)合查詢(xún) 相當(dāng)于引入幔嫂。
- property 屬性 type 指向的bean的屬性名稱(chēng)辆它。
- resultMap 由namespace+resultMap的id組成。
- 這里的select resultMap選擇是address2.
一對(duì)多 一個(gè)用戶(hù)對(duì)應(yīng)多個(gè)地址
<?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.springmvc.dao.UserMapper">
<resultMap id="UserMap" type="com.springmvc.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex" ></result>
<result property="address" column="address"></result>
<association property="addressList" resultMap="com.springmvc.dao.AddressMapper.address"/>
</resultMap>
<select id="findAddress" parameterType="int" resultMap="UserMap">
SELECT u.username,a.* FROM user u INNER JOIN address a ON a.uid=u.id AND u.id=#{id}
</select>
</mapper>
