mybatis注解开发实现CRUD(重要)
1 Mapper接口中使用注解
public interface StudentMapper {
//查询全部
@Select("select * from student")
public abstract List<Student> selectAll();
//根据id查询
@Select("select * from student where id=#{id}")
public abstract Student selectById(Integer id);
//新增数据
@Insert("insert into student values(null,#{name},#{age})")
public abstract Integer insert(Student stu);
//修改数据
@Update("update student set name=#{name},age=#{age} where id=#{id}")
public abstract Integer update(Student stu);
//删除数据
@Delete("delete from student where id=#{id}")
public abstract Integer delete(Integer id);
}
2 核心配置文件引入Mapper
<!--加载映射文件-->
<mappers>
<!--引入mapper接口的位置-->
<!--<mapper class="com.itheima.mapper.StudentMapper"/>-->
<!--引入整个包-->
<package name="com.itheima.mapper"/>
</mappers>
3 测试类
public class StudentTest {
@Test
public void testSelectAll() throws IOException {
//1、加载核心配置文件
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//2、获取工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、获取SqlSession核心对象
SqlSession sqlSession = factory.openSession(true);
//4、获取接口的实现类代理对象,执行操作
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = mapper.selectAll();
//5、处理/打印结果
list.forEach(stu-> System.out.println(stu));
//6、释放资源
sqlSession.close();
}
@Test
public void testSelectById() throws IOException {
//1、加载核心配置文件
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//2、获取工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、获取SqlSession核心对象
SqlSession sqlSession = factory.openSession(true);
//4、获取接口的实现类代理对象,执行操作
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectById(3); //底层使用sqlSession.selectOne()方法
//5、处理/打印结果
System.out.println(student);
//6、释放资源
sqlSession.close();
}
@Test
public void testInsert() throws IOException {
//1、加载核心配置文件
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//2、获取工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、获取SqlSession核心对象
SqlSession sqlSession = factory.openSession(true);
//4、获取接口的实现类代理对象,执行操作
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Integer count = mapper.insert(new Student(null, "刘曲", 30));
//5、处理/打印结果
System.out.println("count = " + count);
//6、释放资源
sqlSession.close();
}
@Test
public void testUpdate() throws IOException {
//1、加载核心配置文件
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//2、获取工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、获取SqlSession核心对象
SqlSession sqlSession = factory.openSession(true);
//4、获取接口的实现类代理对象,执行操作
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Integer count = mapper.update(new Student(13, "刘曲", 40));
//5、处理/打印结果
System.out.println("count = " + count);
//6、释放资源
sqlSession.close();
}
@Test
public void testSelectDelete() throws IOException {
//1、加载核心配置文件
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//2、获取工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、获取SqlSession核心对象
SqlSession sqlSession = factory.openSession(true);
//4、获取接口的实现类代理对象,执行操作
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Integer count = mapper.delete(13);
//5、处理/打印结果
System.out.println("count = " + count);
//6、释放资源
sqlSession.close();
}
}
mybatis注解多表开发(注解)
实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现,使用注解开发后,我们可以使用@Results注解,@Result注解,@One注解,@Many注解组合完成复杂关系的配置
1 一对一
OneToOneMapper接口中执行查询
public interface OneToOneMapper {
//查询全部
@Select("select * from card")
@Results({
//id = true 表示是主键,默认值是false
@Result(id = true,column = "id",property = "id"), //id标签
@Result(column = "number",property = "number"), //result标签
//column = "pid" 此处的column = "pid"表示拿着pid的值传递到selectById方法中查询对应的person信息,封装到property = "p"属性中
@Result(
column = "pid",//根据查询出的card表中的pid字段来查询person表
property = "p",//被包含对象的变量名
javaType = Person.class,//被包含对象的实际数据类型
/*
one=@One 一对一固定写法
select属性:指定调用哪个接口中的那个方法
*/
one = @One(select = "com.itheima.mapper.PersonMapper.selectById")), //result标签
})
public abstract List<Card> selectAll();
}
新建PersonMaper接口,创建方法根据id查询
public interface PersonMapper {
@Select("select * from person where id=#{id}")
Person selectById(Integer id);
}
测试方法
/*
测试一对一关系:查询所有card的信息,包括每个card对应的person信息
*/
@Test
public void testOneToOneMapper(){
//加载资源
SqlSession sqlSession = null;
try {
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//获取SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//获取SqlSession对象
sqlSession = factory.openSession(true);
//获取mapper接口的代理对象,执行方法
OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
List<Card> cards = mapper.selectAll();
//打印结果
cards.forEach(card -> System.out.println(card));
} catch (IOException e) {
e.printStackTrace();
} finally {
if(sqlSession!=null){
//释放资源
sqlSession.close();
}
}
}
2 一对多
OneToManyMapper接口中执行查询
//查询全部
@Select("select * from classes")
@Results({
@Result(id = true,column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(
property = "students",
javaType = List.class,
column = "id",
many = @Many(select = "com.itheima.mapper.StudentMapper.selectByCid")),
})
public abstract List<Classes> selectAll();
新建StudentMaper接口,创建方法根据id查询
public interface StudentMapper {
@Select("select * from student where cid=#{cid}")
List<Student> selectByCid(int cid);
}
测试方法
/*
测试一对多关系:查询所有班级以及该班级的所有学生信息
*/
@Test
public void testOneToManyMapper(){
//加载资源
SqlSession sqlSession = null;
try {
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//获取SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//获取SqlSession对象
sqlSession = factory.openSession(true);
//获取mapper接口的代理对象,执行方法
OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
List<Classes> classes = mapper.selectAll();
//打印结果
classes.forEach(cla -> System.out.println(cla));
} catch (IOException e) {
e.printStackTrace();
} finally {
if(sqlSession!=null){
//释放资源
sqlSession.close();
}
}
}
3 多对多
ManyToManyMapper接口中执行查询
public interface ManyToManyMapper {
//查询全部
//@Select("select * from student") //没有选课的学生也能被查到
@Select("select distinct s.* from student s,stu_cr sc where s.id=sc.sid") //没有选课的学生查不到
@Results({
@Result(id = true,column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(column = "age",property = "age"),
@Result(
property = "courses",
javaType = List.class,
column = "id",
many = @Many(select = "com.itheima.mapper.CourseMapper.selectBySid")),
})
public abstract List<Student> selectAll();
}
新建CourseMaper接口,创建方法根据id查询
public interface CourseMapper {
@Select("select c.* from stu_cr sc,course c where sc.cid=c.id and sc.sid=#{sid}")
public List<Course> selectBySid(Integer sid);
}
测试方法
/*
测试多对多关系:查询所有学生及其选课信息
*/
@Test
public void testManyToManyMapper(){
//加载资源
SqlSession sqlSession = null;
try {
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//获取SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//获取SqlSession对象
sqlSession = factory.openSession(true);
//获取mapper接口的代理对象,执行方法
ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);
List<Student> list = mapper.selectAll();
//打印结果
list.forEach(stu -> System.out.println(stu));
} catch (IOException e) {
e.printStackTrace();
} finally {
if(sqlSession!=null){
//释放资源
sqlSession.close();
}
}
}
4 懒加载 — 了解
4.1 在核心配置文件中开启懒加载
<!--开启全局的延迟加载开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--取消默认触发懒加载的方法-->
<setting name="lazyLoadTriggerMethods" value=""/>
4.2 如果某个多表操作需要取消懒加载,就使用fetchType = FetchType.EAGER取消,那么就是立即加载
public interface ManyToManyMapper {
//查询全部
//@Select("select * from student")
@Select("select distinct s.* from student s,stu_cr sc where s.id=sc.sid")
@Results({
@Result(id = true,column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(column = "age",property = "age"),
@Result(
property = "courses",
column = "id",
many = @Many(select = "com.itheima.mapper.CourseMapper.selectBySid",fetchType = FetchType.EAGER)),
})
public abstract List<Student> selectAll();
mybatis构建SQL(了解)
-查询操作
@SelectProvider:生成查询用的SQL语句注解
type属性:生成SQL语句功能类字节码对象
method属性:指定调用方法(方法名字)
-新增操作
@InsertProvider:生成新增用的SQL语句注解
type属性:生成SQL语句功能类字节码对象
method属性:指定调用方法(方法名字)
-更新操作
@UpdateProvider:生成修改的SQL语句注解
type属性:生成SQL语句功能类字节码对象
method属性:指定调用方法(方法名字)
-删除操作
@DeleteProvider:生成修改的SQL语句注解
type属性:生成SQL语句功能类字节码对象
method属性:指定调用方法(方法名字)
1.提供类和方法返回Sql语句
public class ReturnSql {
public String getSelectCondition(Student stu){
//select * from student where id=#{id} and name=#{name} and age=#{age}
//创建初始化语句
// select * from student
SQL sql = new SQL().SELECT("*").FROM("student");
//判断
if(stu!=null && stu.getId()!=null){
sql.WHERE("id=#{id}");
}
if(stu!=null && stu.getName()!=null){
sql.WHERE("name=#{name}"); //where方法会自动添加上and关键字
}
if(stu!=null && stu.getAge()!=null){
sql.WHERE("age=#{age}"); //where方法会自动添加上and关键字
}
// select * from student where id=#{id} and name=#{name} and age=#{age}
return sql.toString();
}
/*public String getSelectCondition(Student stu){
//select * from student where id=#{id} and name=#{name} and age=#{age}
//创建初始化语句
StringBuilder sb=new StringBuilder("select * from student where 1=1");
//判断
if(stu!=null && stu.getId()!=null){
sb.append(" and id=#{id}");
}
if(stu!=null && stu.getName()!=null){
sb.append(" and name=#{name}"); //where方法会自动添加上and关键字
}
if(stu!=null && stu.getAge()!=null){
sb.append(" and age=#{age}"); //where方法会自动添加上and关键字
}
return sb.toString();
}*/
}
2.在Mapper接口的方法上使用@XxxxProvider注解使用构建的sql
@SelectProvider(type = ReturnSql.class,method = "getSelectCondition")
public abstract List<Student> selectCondition(Student stu);
3.测试类测试
@Test
public void testSelectCondition() throws IOException {
//1、加载核心配置文件
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
//2、获取工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、获取SqlSession核心对象
SqlSession sqlSession = factory.openSession(true);
//4、获取接口的实现类代理对象,执行操作
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
//创建条件
Student stu=new Student();
//stu.setId(2);
stu.setAge(25);
//执行查询
List<Student> list = mapper.selectCondition(stu);
//5、处理/打印结果
list.forEach(student -> System.out.println(student));
//6、释放资源
sqlSession.close();
}
补充:注解方式实现动态SQL的脚本方式
//多条件查询
@Select("<script>select * from student\n" +
" <where>\n" +
" <if test=\"id != null\">\n" +
" id=#{id}\n" +
" </if>\n" +
" <if test=\"name != null and name !='' \">\n" +
" and name=#{name}\n" +
" </if>\n" +
" <if test=\"age != null\">\n" +
" and age=#{age}\n" +
" </if>\n" +
" </where></script>")
public abstract List<Student> selectCondition(Student stu);
//根据多个id查询
@Select("<script>select * from student\n" +
" <where>\n" +
" <foreach collection=\"list\" open=\"id in(\" close=\")\" item=\"id\" separator=\",\">\n" +
" #{id}\n" +
" </foreach>\n" +
" </where></script>")
public abstract List<Student> selectByIds(List<Integer> ids);
案例改进
1 项目环境搭建
- 执行给定的sql,创建项目需要的数据库和表。
- 创建动态web项目,项目名为mybatis_sms。
- 将资料中mybatis_sms中的src目录和web目录中的所有内容复制到自己的mybatis_sms中
- 修改config.properties中连接数据库的参数
- 在tomcat中修改项目的虚拟目录为sms
2、mybatis环境搭建
- 复制mybatis和log4j相关的jar包
- 复制mybatis核心配置文件和log4j的配置文件
- 修改mybatis核心配置文件(修改引入的属性文件、别名配置、Mapper)
3、删除StudentDaoImpl,在StudentDao接口中使用注解配置CRUD
/*
Dao层接口
*/
public interface StudentDao {
//查询所有学生信息
@Select("select * from student")
public abstract ArrayList<Student> findAll();
//条件查询,根据id获取学生信息
@Select("select * from student where sid=#{id}")
public abstract Student findById(Integer id);
//新增学生信息
@Insert("insert into student(sid,name,age,birthday)values(null,#{name},#{age},#{birthday})")
public abstract int insert(Student stu);
//修改学生信息
@Update("update student set name=#{name},age=#{age},birthday=#{birthday} where sid=#{sid}")
public abstract int update(Student stu);
//删除学生信息
@Delete("delete from student where sid=#{sid}")
public abstract int delete(Integer id);
}
4、在utils包下创建MybatisUtils工具类
public class MybatisUtils {
private MybatisUtils(){}
//获取SqlSession对象
public static SqlSession openSession() throws IOException {
return openSession(false);
}
//获取SqlSession对象
public static SqlSession openSession(boolean autoCommit) throws IOException {
InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
return factory.openSession(autoCommit);
}
//释放资源
public static void close(SqlSession sqlSession){
if(sqlSession!=null){
sqlSession.close();
}
}
}
5、在StudentServiceImpl中使用mybatis接口代理方式实现dao
public class StudentServiceImpl implements StudentService {
@Override
public List<Student> findAll() {
SqlSession sqlSession = null;
try {
//从工具类中获取SqlSession对象
sqlSession = MybatisUtils.openSession(true);
//获取dao接口代理对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
//执行操作
return studentDao.findAll();
} catch (IOException e) {
e.printStackTrace();
} finally {
MybatisUtils.close(sqlSession);
}
return null;
}
@Override
public Student findById(Integer sid) {
SqlSession sqlSession = null;
try {
//从工具类中获取SqlSession对象
sqlSession = MybatisUtils.openSession(true);
//获取dao接口代理对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
return studentDao.findById(sid);
} catch (IOException e) {
e.printStackTrace();
} finally {
MybatisUtils.close(sqlSession);
}
return null;
}
@Override
public void save(Student student) {
SqlSession sqlSession = null;
try {
//从工具类中获取SqlSession对象
sqlSession = MybatisUtils.openSession(true);
//获取dao接口代理对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
//保存
studentDao.insert(student);
} catch (IOException e) {
e.printStackTrace();
} finally {
MybatisUtils.close(sqlSession);
}
}
@Override
public void update(Student student) {
SqlSession sqlSession = null;
try {
//从工具类中获取SqlSession对象
sqlSession = MybatisUtils.openSession(true);
//获取dao接口代理对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
studentDao.update(student);
} catch (IOException e) {
e.printStackTrace();
} finally {
MybatisUtils.close(sqlSession);
}
}
@Override
public void delete(Integer sid) {
SqlSession sqlSession = null;
try {
//从工具类中获取SqlSession对象
sqlSession = MybatisUtils.openSession(true);
//获取dao接口代理对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
studentDao.delete(sid);
} catch (IOException e) {
e.printStackTrace();
} finally {
MybatisUtils.close(sqlSession);
}
}
}