Mybatis:
官方回答Mybatisa是一款优秀的持久层框架,它支持自定义SQL、存储过程以及高级映射。
为什么需要Mybatis
○帮助程序员将数据存入到数据库中
○方便
○传统的JDBC代码太复杂了。简化。框架。自动化。
○优点
●简单易学
●灵活
●sql和代码分离,提高可维护性
●提供映射标签,支持对象与数据库的orm字段关系映射
●提供对象关系映射标签,支持对象关系组键维护
●提供xml标签,支持编写动态sql
配置文件是Mybatis的核心
最先加载的是:
<property name="username" value="dev_user"/>
<property name="password" value="F2Fa3!33TYyg"/>
最后加载的是:静态初始化代码块(优先级最高的) static { try { Properties properties = new Properties(); // properties.setProperty("username","sxj");
String resource = "mybatis-conf.xml";//MyBatis的核心配置文件路径
InputStream inputStream = Resources.getResourceAsStream(resource);//读取配置文件
//创建sqlSessionFactory
//这个类可以被实例化、使用和丢弃,一旦创建了sqlSessionFactory,就不再需要他了。
//因此SqlSessionFactoryBuilder 实例的最佳范围是方法范围(也就是局部方法变量)。
sqlSessionFactory = new SqlSessionFactoryBuilder()
//.build(inputStream);//只根据配置文件构建
//.build(inputStream,properties);//配置文件
.build(inputStream,"development",properties);//配置文件,指定使用的环境id,配置属性
} catch (IOException e) {
e.printStackTrace();
}
}
设置:一般使用默认的
配置映射器:
column=“数据库里面的字段” property=“po里面的字段” jdbcType=“JDBC类型”是一一对应关系
<mapper namespace="com.gx.dao.SysUserMapper">
<!--配置映射器-->
<resultMap id="baseMap" type="com.gx.po.SysUser">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified"/>
<result column="is_deleted" jdbcType="TINYINT" property="isDeleted"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="user_password" jdbcType="CHAR" property="userPassword"/>
<result column="salt" jdbcType="CHAR" property="salt"/>
<result column="department_id" jdbcType="INTEGER" property="departmentId"/>
<result column="position_id" jdbcType="INTEGER" property="positionId"/>
<result column="role_id" jdbcType="INTEGER" property="roleId"/>
<result column="real_name" jdbcType="VARCHAR" property="realName"/>
<result column="gender" jdbcType="TINYINT" property="gender"/>
<result column="birthday" jdbcType="DATE" property="birthday"/>
<result column="portrait" jdbcType="VARCHAR" property="portrait"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="mobile" jdbcType="VARCHAR" property="mobile"/>
<result column="qq" jdbcType="VARCHAR" property="qq"/>
<result column="wechat" jdbcType="VARCHAR" property="wechat"/>
<result column="userStatus" jdbcType="TINYINT" property="userStatus"/>
<result column="loginCount" jdbcType="INTEGER" property="loginCount"/>
<result column="remark" jdbcType="VARCHAR" property="remark"/>
</resultMap>
接口:
//单表 查询by id
//只有一个参数时可以不写@Param
SysUser selectById(@Param(“id”) int id);
根据id查询:对应
select * from sys_user where id = #{id}
</select>
后台代码:
/**
- 测试 单表by id
*/
@Test
public void TestSelectById(){
//1、获取SqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
//2、获取Mapper接口
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
SysUser user = sysUserMapper.selectById(1);
System.out.println(user);
}finally {
sqlSession.close();//记得关闭
}
}
查询结果:
SysUser{id=1, gmtCreate=Tue Mar 02 20:33:00 CST 2021, gmtModified=Sun Apr 04 18:13:43 CST 2021, isDeleted=0, userName=‘admin’, userPassword=‘c6c5a85aee621a5d717a30fd0ced75d5’, salt=‘60517174’, departmentId=1, positionId=5, roleId=1, realName=‘施显军’, gender=1, birthday=Wed Mar 31 00:00:00 CST 2021, portrait=‘20210331_220656994_10702005119500.gif’, email=‘null’, mobile=‘13711223344’, qq=‘null’, wechat=‘null’, userStatus=1, loginCount=null, remark=‘null’
新增:
接口:
/*
新增
*/
int insert(SysUser user);
映射:
insert into sys_user (id, gmt_create, gmt_modified,
is_deleted, user_name, user_password,
salt, department_id, position_id,
role_id, real_name, gender,
birthday, portrait, email,
mobile, qq, wechat,
user_status, login_count, remark
)
values (#{id,jdbcType=INTEGER}, #{gmtCreate,jdbcType=TIMESTAMP}, #{gmtModified,jdbcType=TIMESTAMP},
#{isDeleted,jdbcType=TINYINT}, #{userName,jdbcType=VARCHAR}, #{userPassword,jdbcType=CHAR},
#{salt,jdbcType=CHAR}, #{departmentId,jdbcType=INTEGER}, #{positionId,jdbcType=INTEGER},
#{roleId,jdbcType=INTEGER}, #{realName,jdbcType=VARCHAR}, #{gender,jdbcType=TINYINT},
#{birthday,jdbcType=DATE}, #{portrait,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR},
#{mobile,jdbcType=VARCHAR}, #{qq,jdbcType=VARCHAR}, #{wechat,jdbcType=VARCHAR},
#{userStatus,jdbcType=TINYINT}, #{loginCount,jdbcType=INTEGER}, #{remark,jdbcType=VARCHAR}
)
</insert>
测试新增:
/**
- 新增
*/
@Test
public void TestInsert(){
//1、获取SqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
//2、获取Mapper接口
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
SysUser user=new SysUser();
user.setUserName("insert20210804");
user.setUserPassword("insert20210804");
user.setRealName("测试20210804");
user.setGmtCreate(new Date());
//调用新增
int intR=sysUserMapper.insert(user);
System.out.println("新增结果:"+intR);
//新增 修改 删除记得提交事务
sqlSession.commit();//!!!提交事务
}finally {
sqlSession.close();//记得关闭
}
}
新增结果:
新增返回主键:
接口代码:
/**
*新增返回主键
*/
i
nt insertReturnKey(SysUser user);
<!--新增返回主键-->
<insert id="insertReturnKey" parameterType="com.gx.po.SysUser" 返回id的主要代码
useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into sys_user (id, gmt_create, gmt_modified,
is_deleted, user_name, user_password,
salt, department_id, position_id,
role_id, real_name, gender,
birthday, portrait, email,
mobile, qq, wechat,
user_status, login_count, remark
)
values (#{id,jdbcType=INTEGER}, #{gmtCreate,jdbcType=TIMESTAMP}, #{gmtModified,jdbcType=TIMESTAMP},
#{isDeleted,jdbcType=TINYINT}, #{userName,jdbcType=VARCHAR}, #{userPassword,jdbcType=CHAR},
#{salt,jdbcType=CHAR}, #{departmentId,jdbcType=INTEGER}, #{positionId,jdbcType=INTEGER},
#{roleId,jdbcType=INTEGER}, #{realName,jdbcType=VARCHAR}, #{gender,jdbcType=TINYINT},
#{birthday,jdbcType=DATE}, #{portrait,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR},
#{mobile,jdbcType=VARCHAR}, #{qq,jdbcType=VARCHAR}, #{wechat,jdbcType=VARCHAR},
#{userStatus,jdbcType=TINYINT}, #{loginCount,jdbcType=INTEGER}, #{remark,jdbcType=VARCHAR}
)
测试新增返回主键: /** * 新增返回主键 */
@Test
public void TestInsertReturnKey(){
//1、获取SqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
//2、获取Mapper接口
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
//
SysUser user=new SysUser();
user.setUserName("insert20210804");
user.setUserPassword("insert20210804");
user.setRealName("测试20210804");
user.setGmtCreate(new Date());
//调用新增
int intR=sysUserMapper.insertReturnKey(user);//返回受影响的行数
System.out.println("新增结果:"+intR+";主键:"+user.getId());
//新增 修改 删除记得提交事务
sqlSession.commit();//!!!提交事务
}finally {
sqlSession.close();//记得关闭
}
}
测试结果:
修改:
接口:
/*
修改 根据主键
*/
int updateById(SysUser user);
映射:
update sys_user
set gmt_create = #{gmtCreate,jdbcType=TIMESTAMP},
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
is_deleted = #{isDeleted,jdbcType=TINYINT},
user_name = #{userName,jdbcType=VARCHAR},
user_password = #{userPassword,jdbcType=CHAR},
salt = #{salt,jdbcType=CHAR},
department_id = #{departmentId,jdbcType=INTEGER},
position_id = #{positionId,jdbcType=INTEGER},
role_id = #{roleId,jdbcType=INTEGER},
real_name = #{realName,jdbcType=VARCHAR},
gender = #{gender,jdbcType=TINYINT},
birthday = #{birthday,jdbcType=DATE},
portrait = #{portrait,jdbcType=VARCHAR},
email = #{email,jdbcType=VARCHAR},
mobile = #{mobile,jdbcType=VARCHAR},
qq = #{qq,jdbcType=VARCHAR},
wechat = #{wechat,jdbcType=VARCHAR},
user_status = #{userStatus,jdbcType=TINYINT},
login_count = #{loginCount,jdbcType=INTEGER},
remark = #{remark,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
测试代码:
/**
修改
*/
@Test
public void TestUpdateById(){
//1、获取SqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
//2、获取Mapper接口
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
SysUser dbUser=sysUserMapper.selectById(10);
//修改需要修改的数据
dbUser.setUserName("update20210804");
//调用新增
int intR=sysUserMapper.updateById(dbUser);//返回受影响的行数
System.out.println("修改结果:"+intR);
//新增 修改 删除记得提交事务
sqlSession.commit();//!!!提交事务
}finally {
sqlSession.close();//记得关闭
}
}
测试结果:
删除:
接口:
/*
删除 根据主键
*/
int deleteById(int id);
映射:
delete from sys_user where id=#{id}
</delete>
测试代码:
/**
删除
*/
@Test
public void TestDeleteById(){
//1、获取SqlSession
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
//2、获取Mapper接口
SysUserMapper sysUserMapper = sqlSession.getMapper(SysUserMapper.class);
//调用新增
int intR=sysUserMapper.deleteById(10);//返回受影响的行数
System.out.println("删除结果:"+intR);
//新增 修改 删除记得提交事务
sqlSession.commit();//!!!提交事务
}finally {
sqlSession.close();//记得关闭
}
}
测试结果:
删除后