mybatis 简单使用注解编写sql

 

pojo类,这是一个简单的java对象

package com.bean; public class Student { private long stuId; private String stuName; private long stuAge; public long getStuId() { return stuId; } public void setStuId(long stuId) { this.stuId = stuId; } public String getStuName() { return stuName; } public void setStuName(String stuName) { this.stuName = stuName; } public long getStuAge() { return stuAge; } public void setStuAge(long stuAge) { this.stuAge = stuAge; } @Override public String toString() { return "Student{" + "stuId=" + stuId + ", stuName='" + stuName + '\'' + ", stuAge=" + stuAge + '}'; } }

student表

create table student ( stu_id int auto_increment comment '学生id' primary key, stu_name varchar(64) default '张三' null comment '姓名', stu_age int null comment '年龄' ) comment '学生表';

mybatis配置文件mybatis-config.xml,连接配置根据自身情况修改

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/test"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <mapper class="com.bean.StudentMapper"/> </mappers> </configuration>

sql映射类StudentMapper接口

package com.bean; public interface StudentMapper { }

SqlSession工具类

package com.util; 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 java.io.IOException; import java.io.InputStream; public class SqlSessionUtil { static String resource = "mybatis-config.xml"; static InputStream inputStream = null; static SqlSessionFactory factory = null; static { try { inputStream = Resources.getResourceAsStream(resource); factory = new SqlSessionFactoryBuilder().build(inputStream); } catch ( IOException e) { e.printStackTrace(); } } public static SqlSession getSession() { return factory.openSession(); } }

前期配置完成

1. 查找所有学生的信息,输出

package com.bean; import org.apache.ibatis.annotations.Select; import java.util.List; public interface StudentMapper { @Select("select * from student") List<Student> findAll(); }

测试

package com.test; import com.util.SqlSessionUtil; import com.bean.Student; import com.bean.StudentMapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); List<Student> list = studentMapper.findAll(); for (Student student : list) { System.out.println(student); } } } }

结果

原因分析:数据库确实查询了select * from student,但是没有把查询到的数据导入pojo类中,也就是说没有建立结果映射关系

修改

package com.bean; import org.apache.ibatis.annotations.Select; import java.util.List; public interface StudentMapper { @Select("select stu_id as stuId,stu_name as stuName,stu_age as stuAge from student") List<Student> findAll(); }

因为实体类和数据库中的字段名不一致,所以需要用as把数据库中的每个字段变成实体类的字段名

stu_id,stu_age,stu_name:要查询数据库多的列名

as:别名

stuId,stuName,stuAge:pojo类的成员变量名

stu_id as stuId:查到一个stu_id结果自动调用stuId的setter方法赋值给stuId,其他的同理

2. 根据id查询

package com.bean; import org.apache.ibatis.annotations.Select; public interface StudentMapper { @Select("select stu_id as stuId,stu_name as stuName,stu_age as stuAge from student where stu_id = #{id}") Student findById(int id); }

测试

package com.test; import com.util.SqlSessionUtil; import com.bean.Student; import com.bean.StudentMapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); Student student = studentMapper.findById(2); System.out.println(student); } } }

结果

 

3. 多个条件查询

package com.bean; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; public interface StudentMapper { @Select("select stu_id as stuId,stu_name as stuName,stu_age as stuAge from student where stu_name = #{name} and stu_age = #{age}") Student findByNameAge(@Param("name") String name, @Param("age") int age); }

对于多个参数,需要使用到Param

#{name}中的name与@Param("name")中的name对应,@Param("name")中的name值就是String name的值,其他同理

测试

package com.test; import com.util.SqlSessionUtil; import com.bean.Student; import com.bean.StudentMapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); Student student = studentMapper.findByNameAge("张三",20); System.out.println(student); } } }

结果

4. 插入一条数据

package com.bean; import org.apache.ibatis.annotations.Insert; public interface StudentMapper { @Insert("insert into student(stu_name,stu_age)values(#{stuName},#{stuAge})") void addStudent(Student student); }

主键使用了自动生成id,所以不用为id插入插入数据

#{stuName}:自动会调用参数中的student的geter方法,getStudentName(),其他同理

测试,不要忘了使用commit提交,凡是涉及到更新添加删除的操作都需要提交

package com.test; import com.util.SqlSessionUtil; import com.bean.Student; import com.bean.StudentMapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); Student student=new Student(); student.setStuName("大哥"); student.setStuAge(46); studentMapper.addStudent(student); sqlSession.commit(); } } }

结果

5. List 插入多条数据

package com.bean; import org.apache.ibatis.annotations.Insert; import java.util.List; public interface StudentMapper { @Insert({ "<script>", "insert into student(stu_name,stu_age)", "values", "<foreach item='item' collection='list' separator=','>", "(#{item.stuName},#{item.stuAge})", "</foreach>", "</script>" }) int addStudentList(List<Student> list); }

collection： 指定要遍历的集合（三种情况 list，array，map) item：将当前遍历出的元素赋值给指定的变量 （相当于for循环中的i） separator:每个元素之间的分隔符  index:索引。遍历list的时候是index就是索引，item就是当前值  #{item.变量名}就能取出变量的值也就是当前遍历出的元素

注解插入多个数据确实不方便,建议使用xml方式

package com.test; import com.util.SqlSessionUtil; import com.bean.Student; import com.bean.StudentMapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.ArrayList; import java.util.List; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); List list=new ArrayList(); Student student=new Student(); student.setStuName("阿萨"); student.setStuAge(83); Student student1 = new Student(); student1.setStuName("马斯"); student1.setStuAge(19); list.add(student); list.add(student1); studentMapper.addStudentList(list); sqlSession.commit(); } } }

6. 更新数据

package com.bean; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Update; public interface StudentMapper { @Update("update student set stu_name = #{student.stuName} where stu_id = #{student.stuId}") int updateStudentById(@Param("student")Student student); }

测试

package com.test; import com.bean.Student; import com.bean.StudentMapper; import com.util.SqlSessionUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); Student student=new Student(); student.setStuId(5); student.setStuName("阿斯"); studentMapper.updateStudentById(student); sqlSession.commit(); } } }

结果

7. 删除

package com.bean; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Param; public interface StudentMapper { @Delete("delete from student where stu_id = #{student.stuId}") int deleteStudentById(@Param("student")Student student); }

测试

package com.test; import com.bean.Student; import com.bean.StudentMapper; import com.util.SqlSessionUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class MainTest { @Test public void test() { try (SqlSession sqlSession= SqlSessionUtil.getSession()){ StudentMapper studentMapper=sqlSession.getMapper(StudentMapper.class); Student student=new Student(); student.setStuId(5); studentMapper.deleteStudentById(student); sqlSession.commit(); } } }

结果

xml和注解各有各的优势,一般都是结合使用的