<meta charset="utf-8">
特殊情況示栗：
修改實體類
package com.neusoft.domain;
import java.io.Serializable;
import java.util.Date;
/**
* @author Eric Lee
* @date 2020/9/3 09:45
*/
public class User implements Serializable {
private Integer userId;
private String userName;
private Date userBirthday;
private String userSex;
private String userAddress;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Date getUserBirthday() {
return userBirthday;
}
public void setUserBirthday(Date userBirthday) {
this.userBirthday = userBirthday;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userBirthday=" + userBirthday +
", userSex='" + userSex + '\'' +
", userAddress='" + userAddress + '\'' +
'}';
}
}
image
我們運行 findAll() 查詢結(jié)果如下
image
查出上面結(jié)果的原因是數(shù)據(jù)庫字段與實體類沒有對應(yīng)， usename可以封裝原因是window系統(tǒng)mysql數(shù)據(jù)不區(qū)分大小寫
解決方案1查詢時候起別名
<!-- 查詢所有-->
<select id="findAll" resultType="com.neusoft.domain.User">
select id as userId, username as userName, sex as userSex , birthday as userBirthday, address as userAddress from user
</select>
解決方案2 使用resultMap
<!-- 配置查詢結(jié)果的列名和實體屬性名的對應(yīng)關(guān)系-->
<!-- id 給定一個唯一標(biāo)識砂心， 是給select標(biāo)簽引用用的-->
<!-- type是指 實體類的全限定類名-->
<resultMap id="userMap" type="com.neusoft.domain.User">
<!-- id標(biāo)簽 主鍵字段的對應(yīng)-->
<id property="userId" column="id"></id>
<!-- result標(biāo)簽 用于指定非主鍵 ， column是數(shù)據(jù)庫中列名-->
<!-- property 用于指定實體類屬性名名稱-->
<result property="userName" column="username"></result>
<result property="userAddress" column="address"></result>
<result property="userSex" column="sex"></result>
<result property="userBirthday" column="birthday"></result>
</resultMap>
完整的IUserDao.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.neusoft.dao.IUserDao">
<!-- 配置查詢結(jié)果的列名和實體屬性名的對應(yīng)關(guān)系-->
<!-- id 給定一個唯一標(biāo)識柬泽， 是給select標(biāo)簽引用用的-->
<!-- type是指 實體類的全限定類名-->
<resultMap id="userMap" type="com.neusoft.domain.User">
<!-- id標(biāo)簽 主鍵字段的對應(yīng)-->
<id property="userId" column="id"></id>
<!-- result標(biāo)簽 用于指定非主鍵 ， column是數(shù)據(jù)庫中列名-->
<!-- property 用于指定實體類屬性名名稱-->
<result property="userName" column="username"></result>
<result property="userAddress" column="address"></result>
<result property="userSex" column="sex"></result>
<result property="userBirthday" column="birthday"></result>
</resultMap>
<!--<!– 查詢所有–>-->
<!-- <select id="findAll" resultType="com.neusoft.domain.User">-->
<!-- select id as userId, username as userName, sex as userSex , birthday as userBirthday, address as userAddress from user-->
<!-- </select>-->
<!-- 查詢所有-->
<!-- <select id="findAll" resultType="com.neusoft.domain.User">-->
<select id="findAll" resultMap="userMap">
select * from user
</select>
<!--<!– 通過id進行查詢–>-->
<!-- <select id="findById" parameterType="INT" resultType="com.neusoft.domain.User">-->
<select id="findById" parameterType="INT" resultMap="userMap">
select * from user where id = #{uid}
</select>
<!-- 保存用戶-->
<!-- savaUser-->
<insert id="savaUser" parameterType="com.neusoft.domain.User">
-- 配置保存時獲取插入id keyColumn數(shù)據(jù)庫中的列名 keyProperty實體類
<selectKey keyColumn="id" keyProperty="userId" resultType="INT">
select last_insert_id();
</selectKey>
insert into user (username, birthday, sex, address)
values (#{userName},#{userBirthday} ,#{userSex},#{userAddress})
</insert>
<!-- 更新用戶-->
<update id="updateUser" parameterType="com.neusoft.domain.User">
update user set username = #{userName},birthday=#{userBirthday},
sex=#{userSex}, address=#{userAddress} where id = #{userId}
</update>
<!--刪除用戶-->
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id = #{id}
</delete>
<!-- 根據(jù)姓名進行模糊查詢-->
<!-- <select id="findByName" parameterType="java.lang.String" resultType="com.neusoft.domain.User">-->
<select id="findByName" parameterType="java.lang.String" resultMap="userMap">
select * from user where username like #{username};
</select>
<!-- 查詢總記錄數(shù)-->
<select id="findTotal" resultType="java.lang.Integer">
select count(*) from user;
</select>
</mapper>
面試題 #{} 與${} 區(qū)別
{} 表示一個占位符號
通過#{}可以實現(xiàn) preparedStatement 向占位符中設(shè)置值方椎，自動進行 java 類型和 jdbc 類型轉(zhuǎn)換聂抢，
{}可以有效防止 sql 注入。 #{}可以接收簡單類型值或 pojo 屬性值棠众。 如果 parameterType 傳輸單個簡單類型值琳疏，#{}括號中可以是 value 或其它名稱。
[圖片上傳失敗...(image-947738-1599438087294)]
{}可以將 parameterType 傳入的內(nèi)容拼接在 sql中且不進行 jdbc 類型轉(zhuǎn)換闸拿， [圖片上傳失敗...(image-82ec10-1599438087294)]
{}括號中只能是 value
在sqlmapconfigure.xml中配置參數(shù)別名
<typeAliases>
<!-- 單個類起別名-->
<!-- <typeAlias type="com.neusoft.domain.User" alias="user"></typeAlias>-->
<!-- 配置起別名的包 當(dāng)指定之后空盼， 該包下的實體類都會注冊別名，并且類名就是別名新荤，不區(qū)分大小寫-->
<package name="com.neusoft.domain"/>
</typeAliases>
對應(yīng)的IUserDao.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.neusoft.dao.IUserDao">
<!-- 配置查詢結(jié)果的列名和實體屬性名的對應(yīng)關(guān)系-->
<!-- id 給定一個唯一標(biāo)識揽趾， 是給select標(biāo)簽引用用的-->
<!-- type是指 實體類的全限定類名-->
<resultMap id="userMap" type="user">
<!-- id標(biāo)簽 主鍵字段的對應(yīng)-->
<id property="userId" column="id"></id>
<!-- result標(biāo)簽 用于指定非主鍵 ， column是數(shù)據(jù)庫中列名-->
<!-- property 用于指定實體類屬性名名稱-->
<result property="userName" column="username"></result>
<result property="userAddress" column="address"></result>
<result property="userSex" column="sex"></result>
<result property="userBirthday" column="birthday"></result>
</resultMap>
<!--<!– 查詢所有–>-->
<!-- <select id="findAll" resultType="com.neusoft.domain.User">-->
<!-- select id as userId, username as userName, sex as userSex , birthday as userBirthday, address as userAddress from user-->
<!-- </select>-->
<!-- 查詢所有-->
<!-- <select id="findAll" resultType="com.neusoft.domain.User">-->
<select id="findAll" resultMap="userMap">
select * from user
</select>
<!--<!– 通過id進行查詢–>-->
<!-- <select id="findById" parameterType="INT" resultType="com.neusoft.domain.User">-->
<select id="findById" parameterType="INT" resultMap="userMap">
select * from user where id = #{uid}
</select>
<!-- 保存用戶-->
<!-- savaUser-->
<insert id="savaUser" parameterType="user">
-- 配置保存時獲取插入id keyColumn數(shù)據(jù)庫中的列名 keyProperty實體類
<selectKey keyColumn="id" keyProperty="userId" resultType="INT">
select last_insert_id();
</selectKey>
insert into user (username, birthday, sex, address)
values (#{userName},#{userBirthday} ,#{userSex},#{userAddress})
</insert>
<!-- 更新用戶-->
<update id="updateUser" parameterType="user">
update user set username = #{userName},birthday=#{userBirthday},
sex=#{userSex}, address=#{userAddress} where id = #{userId}
</update>
<!--刪除用戶-->
<delete id="deleteUser" parameterType="java.lang.Integer">
delete from user where id = #{id}
</delete>
<!-- 根據(jù)姓名進行模糊查詢-->
<!-- <select id="findByName" parameterType="java.lang.String" resultType="com.neusoft.domain.User">-->
<!-- select * from user where username like '%${value}%';
-->
<select id="findByName" parameterType="java.lang.String" resultMap="userMap">
select * from user where username like #{username};
</select>
<!-- 查詢總記錄數(shù)-->
<select id="findTotal" resultType="java.lang.Integer">
select count(*) from user;
</select>
</mapper>
