package com.mybatis.demo.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.mybatis.demo.SysRole;
import com.mybatis.demo.SysUser;
public interface SysUserMapper {
<?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.mybatis.demo.mapper.SysUserMapper">
<resultMap type="com.mybatis.demo.SysUser" id="userMap">
<id property="id" column="id" />
<result property="userName" column="user_name" />
<result property="userPassword" column="user_password" />
<result property="userEmail" column="user_email" />
<result property="userInfo" column="user_Info" />
<result property="headImg" column="head_img" jdbcType="BLOB" />
<result property="userName" column="user_name" />
<result property="createTime" column="create_time" jdbcType="TIMESTAMP" />
</resultMap>
<!-- <select id="selectById" resultMap="userMap">
select * from sys_user where
id=#{id}
</select> -->
<!--查询全部,一般select 字段名称1,字段名称2 from table表名 ,而不是select * -->
<select id="selectAll" resultType="com.mybatis.demo.SysUser">
select * from sys_user;
</select>
<select id="selectRoleByid" resultType="com.mybatis.demo.SysRole">
select r.id ,
r.role_name roleName,
r.enabled,
r.create_by as createBy,
r.create_time as createTime,
u.user_Name as "user.name",
u.user_email as "user.userEmail"
from
sys_user u
inner join sys_user_role ur on u.id=ur.user_id
inner join sys_role r on ur.role_id =r.id
where u.id=#{userId}
</select>
<!-- sql语句分行写 这样报错非常明显可以看出是第几行 -->
<!--如果xml报错但是不显示,可以xml文件 鼠标右键,点击validate,然后之后错误就会显示出来的。 -->
<!-- <insert id="insert">
insert into sys_user values(#{id},#{userName},
#{userPassword},
#{userEmail},
#{userInfo},
#{headImg , jdbcType=BLOB}, #{createTime , jdbcType=TIMESTAMP})
</insert> -->
<!-- <insert id="insert1" useGeneratedKeys="true" keyProperty="id">
insert into sys_user (user_name ,user_password,user_email,user_info,head_img,create_time) values(#{userName},
#{userPassword},
#{userEmail},
#{userInfo},
#{headImg , jdbcType=BLOB}, #{createTime , jdbcType=TIMESTAMP})
</insert> -->
<!-- <delete id="deleteById">
delete from sys_user where id=#{id}
</delete> -->
<!-- jdbcType指定数据类型 -->
<!-- sql语法按照你的数据库版本来 -->
<!-- <update id="updateById">
update sys_user
set user_name=#{userName},
user_password=#{userPassword},
user_email=#{userEmail},
user_info=#{userInfo},
head_img =#{headImg,jdbcType=BLOB},
create_time=#{createTime,jdbcType=TIMESTAMP}
where id= #{id}
</update> -->
</mapper>
package ibatis.test;
import java.io.IOException;
import java.io.Reader;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.binding.MapperRegistry;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.BeforeClass;
import org.junit.Test;
import com.mybatis.demo.Country;
import com.mybatis.demo.SysRole;
import com.mybatis.demo.SysUser;
import com.mybatis.demo.mapper.SysUserMapper;
import junit.framework.Assert;
public class MyCountryTest {
private static SqlSessionFactory sql;
@BeforeClass
public static void init() throws IOException {
// 读取配置文件
Reader read = Resources.getResourceAsReader("mybatis-config.xml");
// 构建sqlsession工厂
sql = new SqlSessionFactoryBuilder().build(read);
read.close();
}
/*
* @Test public void testSelectAll(){ SqlSession openSession =
* sql.openSession(); List<Country> selectList =
* openSession.selectList("selectAll"); System.out.println(selectList);
* openSession.close(); }
*/
/**
* Mapped Statements collection does not contain value for
* com.mybatis.demo.UserMapper.selectAll Type interface
* com.mybatis.demo.UserMapper is not known to the MapperRegistry.
* 配置包扫描,所以mapper接口 应该放到扫描包路径下 解决上面异常
* Invalid bound statement (not found):
*
*/
/**
* <!-- 1. #将传入的数据都当成一个字符串,会对自动传入的数据加一个双引号。如:order by
* #user_id#,如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id,则解析成的sql为order
* by "id". 2. $将传入的数据直接显示生成在sql中。如:order by
* $user_id$,如果传入的值是111,那么解析成sql时的值为order by user_id,
* 如果传入的值是id,则解析成的sql为order by id. 3. #方式能够很大程度防止sql注入,而$方式无法防止Sql注入。 一般使用#
* -->
*/
@Test
public void testSelectSysUser() {
SqlSession session = sql.openSession();
SysUserMapper mapper = session.getMapper(SysUserMapper.class);
SysUser selcetByid = mapper.selectById(1);
System.out.println(selcetByid);
session.close();
}
@Test
public void testSelectAll(){
SqlSession session =sql.openSession();
SysUserMapper mapper = session.getMapper(SysUserMapper.class);
List<SysUser> selectAll = mapper.selectAll();
System.out.println(selectAll);
session.close();
}
@Test
public void testSelectRoleByid(){
SqlSession session =sql.openSession();
SysUserMapper mapper = session.getMapper(SysUserMapper.class);
List<SysRole> selectRoleByid = mapper.selectRoleByid(1);
System.out.println(selectRoleByid);
session.close();
}
/**
* reflection.ReflectionException
* 单词别写错,反射调用了getset 如果没有这个单词肯定报错
* There is no getter for property named
* sqlsession 不手动执行commit 不会提交数据库
*/
@Test
public void testInsert(){
SqlSession session =sql.openSession();
SysUserMapper mapper = session.getMapper(SysUserMapper.class);
SysUser user = new SysUser();
user.setCreateTime(new Date());
user.setHeadImg(new byte[]{1,2});
user.setUserEmail("test@163.com");
user.setUserInfo("66");
user.setUserName("521");
user.setUserPassword("123");
int insert = mapper.insert(user);
Assert.assertTrue(insert==1);
System.out.println(user.getId());
session.close();
}
/**
* 插入数据
*/
@Test
public void testInsert1(){
SqlSession session =sql.openSession();
SysUserMapper mapper = session.getMapper(SysUserMapper.class);
SysUser user = new SysUser();
user.setCreateTime(new Date());
user.setHeadImg(new byte[]{1,2});
user.setUserEmail("test@163.com");
user.setUserInfo("66");
user.setUserName("521");
user.setUserPassword("123");
int insert = mapper.insert1(user);
Assert.assertTrue(insert==1);
System.out.println(user.getId());
session.commit();
session.close();
}
/**
* 删除
* use mybatis;
* select * from sys_user;
*
*/
@Test
public void testDelete(){
SqlSession openSession = sql.openSession();
//MapperRegistry mapperRegistry = new MapperRegistry(this); 初始化时放入concurrentHashmap,然后get
SysUserMapper mapper = openSession.getMapper(SysUserMapper.class);
int deleteById = mapper.deleteById(1);
openSession.commit();
openSession.close();
}
/**
* 更新
* 不要按照自己的意愿来写,
*/
@Test
public void testUpdate(){
SqlSession openSession = sql.openSession();
SysUserMapper mapper = openSession.getMapper(SysUserMapper.class);
SysUser selectById = mapper.selectById(1);
selectById.setHeadImg(new byte[]{8,6,7});
mapper.updateById(selectById);
openSession.commit();
openSession.close();
}
}
<?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.mybatis.demo.mapper.SysUserMapper">
<resultMap type="com.mybatis.demo.SysUser" id="userMap">
<id property="id" column="id" />
<result property="userName" column="user_name" />
<result property="userPassword" column="user_password" />
<result property="userEmail" column="user_email" />
<result property="userInfo" column="user_Info" />
<result property="headImg" column="head_img" jdbcType="BLOB" />
<result property="userName" column="user_name" />
<result property="createTime" column="create_time" jdbcType="TIMESTAMP" />
</resultMap>
<!-- <select id="selectById" resultMap="userMap">
select * from sys_user where
id=#{id}
</select> -->
<!--查询全部,一般select 字段名称1,字段名称2 from table表名 ,而不是select * -->
<select id="selectAll" resultType="com.mybatis.demo.SysUser">
select * from sys_user;
</select>
<select id="selectRoleByid" resultType="com.mybatis.demo.SysRole">
select r.id ,
r.role_name roleName,
r.enabled,
r.create_by as createBy,
r.create_time as createTime,
u.user_Name as "user.name",
u.user_email as "user.userEmail"
from
sys_user u
inner join sys_user_role ur on u.id=ur.user_id
inner join sys_role r on ur.role_id =r.id
where u.id=#{userId}
</select>
<!-- sql语句分行写 这样报错非常明显可以看出是第几行 -->
<!--如果xml报错但是不显示,可以xml文件 鼠标右键,点击validate,然后之后错误就会显示出来的。 -->
<!-- <insert id="insert">
insert into sys_user values(#{id},#{userName},
#{userPassword},
#{userEmail},
#{userInfo},
#{headImg , jdbcType=BLOB}, #{createTime , jdbcType=TIMESTAMP})
</insert> -->
<!-- <insert id="insert1" useGeneratedKeys="true" keyProperty="id">
insert into sys_user (user_name ,user_password,user_email,user_info,head_img,create_time) values(#{userName},
#{userPassword},
#{userEmail},
#{userInfo},
#{headImg , jdbcType=BLOB}, #{createTime , jdbcType=TIMESTAMP})
</insert> -->
<!-- <delete id="deleteById">
delete from sys_user where id=#{id}
</delete> -->
<!-- jdbcType指定数据类型 -->
<!-- sql语法按照你的数据库版本来 -->
<!-- <update id="updateById">
update sys_user
set user_name=#{userName},
user_password=#{userPassword},
user_email=#{userEmail},
user_info=#{userInfo},
head_img =#{headImg,jdbcType=BLOB},
create_time=#{createTime,jdbcType=TIMESTAMP}
where id= #{id}
</update> -->
</mapper>
详细配置请参考上一篇文章地址:mybatis xml 方式的增删改查基本用法