1、带条件的增加
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="UserMapper">
<resultMap type="cn.banwxf.shoppingmall.beans.UserInfo" id="selectUser">
<id property="userId" column="user_id" />
<result property="password" column="user_password" />
<result property="userName" column="user_name" />
<result property="userScore" column="user_score" />
<result property="userLevel" column="user_grade" />
<result property="isVip" column="is_vip" />
<result property="headImage" column="head_image" />
</resultMap>
<!-- 查询指定id的所有数据, 当然可以不是全部,但字段必须一一对应 -->
<select id="login" parameterType="String" resultMap="selectUser">
select
user_id,user_name,user_password,user_score,user_grade,head_image,is_vip
from "user" where user_id=${value}
</select>
<!-- 插入一条数据 里面也可以用if标签 where标签 -->
<insert id="register" parameterType="cn.banwxf.shoppingmall.beans.UserInfo">
insert into
"user"(user_id,user_name,user_password,user_score,user_grade,head_image,is_vip)
values(#{userId},#{userName},#{password},#{userScore},#{userLevel},#{headImage},#{isVip})
</insert>
<!-- 更新某个字段数据,或者全部数据 if标签用于动态拼接sql,也可以用where标签动态拼接条件 -->
<update id="update_info" parameterType="cn.banwxf.shoppingmall.beans.UserInfo">
update "user" set
<trim prefixOverrides=",">
<if test="password!=null and password!=''">user_password=#{password} </if>
<if test="userName!=null and userName!=''">,user_name=#{userName} </if>
<if test="headImage!=null and headImage!=''">,head_image=#{headImage} </if>
<if test="userScore!=null and userScore!=''">,user_score=#{userScore} </if>
<if test="userLevel!=null and userLevel!=''">,user_grade=#{userLevel} </if>
<if test="isVip!=null and isVip!=''">,is_vip=#{isVip} </if>
</trim>
where user_id=#{userId}
</update>
</mapper>
各种各样的标签是为了动态的拼接sql,可以灵活使用,使开发者能面向逻辑编程,快速开发
if标签内和java中if相似,以上代码中是判null和''.
详细说明:
java代码:连接数据库,因为连接数据是可以并发操作的,所以可以直接获取一次,但是CURD每一次操作都是一个事务,必须重新开启
public class SessionFactory {
public static SqlSessionFactory sqlSessionFactory;
static{
if (sqlSessionFactory==null) {
initSqlSessionFactory();
}
}
public static void initSqlSessionFactory() {
InputStream is = null;
try {
is = Resources.getResourceAsStream("Config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
System.out.println(sqlSessionFactory.toString());
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
接下来就是在java中操作:因为是对jdbc的封装,所以在java代码中要手动提交事务,特别是insert,和update语句
public class UserDao {
/**
* 查询
*
* @param userId
* @return
*/
public UserInfo selectUser(String userId) {
SqlSession sqlSession = SessionFactory.sqlSessionFactory.openSession();
UserInfo userInfo = sqlSession.selectOne("UserMapper.login", userId);
return userInfo;
}
/**
* 插入
*
* @param info
* @return
*/
public int registUser(UserInfo info) {
SqlSession sqlSession = SessionFactory.sqlSessionFactory.openSession();
int insert = sqlSession.insert("UserMapper.register", info);
sqlSession.commit();
return insert;
}
/**
* 更新
*
* @param info
* @return
*/
public int updateUserInfo(UserInfo info) {
SqlSession sqlSession = SessionFactory.sqlSessionFactory.openSession();
System.out.println(info.toString());
int i = sqlSession.update("UserMapper.update_info", info);
sqlSession.commit();
return i;
}
}
说明:
注意:SqlSession对象相当于开启了一个事务,
因为不能并发(同时)操作一条数据或者字段,
所以不能只获取一次,他是线程不安全的,必须
每次操作都开启一个事务,操作完成后提交事务,
特别注意insert和update