MyBatis 使用接口增删改查和两表一对一级联查询
导包
总配置文件
<?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> <properties resource="db.properties"></properties> <typeAliases> <package name="maya.model"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers><!--自动装备包里的接口--> <package name="maya.dao"/> </mappers> </configuration>
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=db
password=123
数据库
两个实体类
package maya.model; public class Student { private Integer sno; private String sname; private String ssex; private Integer sclass; private Integer mark; public Student() { super(); } public Student(Integer sno, String sname, String ssex, Integer sclass, Integer mark) { super(); this.sno = sno; this.sname = sname; this.ssex = ssex; this.sclass = sclass; this.mark = mark; } public Integer getSno() { return sno; } public void setSno(Integer sno) { this.sno = sno; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getSsex() { return ssex; } public void setSsex(String ssex) { this.ssex = ssex; } public Integer getSclass() { return sclass; } public void setSclass(Integer sclass) { this.sclass = sclass; } public Integer getMark() { return mark; } public void setMark(Integer mark) { this.mark = mark; } @Override public String toString() { return "Student [sno=" + sno + ", sname=" + sname + ", ssex=" + ssex + ", sclass=" + sclass + ", mark=" + mark + "]"; } }
package maya.model; import java.util.Date; public class StudentInfo { private Integer id; private Student student; private String saddress; private Date sbirthday; public StudentInfo() { super(); } public StudentInfo(Integer id, Student student, String saddress, Date sbirthday) { super(); this.id = id; this.student = student; this.saddress = saddress; this.sbirthday = sbirthday; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Student getStudent() { return student; } public void setStudent(Student student) { this.student = student; } public String getSaddress() { return saddress; } public void setSaddress(String saddress) { this.saddress = saddress; } public Date getSbirthday() { return sbirthday; } public void setSbirthday(Date sbirthday) { this.sbirthday = sbirthday; } @Override public String toString() { return "StudentInfo [id=" + id + ", student=" + student + ", saddress=" + saddress + ", sbirthday=" + sbirthday + "]"; } }
两个接口
package maya.dao; import java.util.List; import java.util.Map; import maya.model.Student; /* * 学生信息操作接口 */ public interface StudentMapper { /** * 添加一条学生信息 * @param student * @return */ public Integer addStu(Student stu); /** * 删除学生信息 * @param stu * @return */ public Integer delStu(Integer sno); /** * map条件查询 * @param map * @return */ public List<Student> getStuByMap(Map<String, Object> map); /** * 查单条学生信息 */ public Student getStuBySno(Integer sno); /** * 修改学生信息 */ public Integer updateStu(Student stu); }
package maya.dao; import java.util.List; import maya.model.StudentInfo; /** * 学生记录的其他信息 * @author User * */ public interface StudentInfoMapper { /** * 一对一级联查询 * @return */ public List<StudentInfo> selectAll(); }
对应的两个配置问文件，注意接口名要跟实配置文件名字一样
<?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="maya.dao.StudentMapper"> <resultMap type="student" id="stuList"></resultMap> <!-- 添加 --> <insert id="addStu" parameterType="student"> insert into student values(sq_mybatis.nextval,#{sname},#{ssex},#{sclass},#{mark}) </insert> <!-- 多条件查询 --> <select id="getStuByMap" parameterType="Map" resultMap="stuList"> select * from student s where s.sname like #{sname} and s.ssex=#{ssex} </select> <!-- 根据主键查询 --> <select id="getStuBySno" parameterType="Integer" resultType="student"> select * from student s where s.sno=#{sno} </select> <!-- 删除 --> <delete id="delStu" parameterType="Integer"> delete from student s where s.sno=#{sno} </delete> <!-- 修改 --> <update id="updateStu" parameterType="student"> update student s set s.sname=#{sname}, s.ssex=#{ssex}, s.sclass=#{sclass}, s.mark=#{mark} where s.sno=#{sno} </update> </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="maya.dao.StudentInfoMapper"> <!-- 一对一级联查询 --> <!-- 方法一 ：--> <resultMap type="studentInfo" id="siList"> <id property="id" column="id"/> <result property="student.sno" column="sno"/> <result property="student.sname" column="sname"/> <result property="student.ssex" column="ssex"/> <result property="student.sclass" column="sclass"/> <result property="student.mark" column="mark"/> <result property="saddress" column="saddress"/> <result property="sbirthday" column="sbirthday"/> </resultMap> <!-- 方法二：推荐 --> <resultMap type="studentInfo" id="siList1"> <association property="student" column="sno" select="maya.dao.StudentMapper.getStuBySno"></association> </resultMap> <select id="selectAll" resultMap="siList1"> select * from studentinfo si left join student s on si.sno=s.sno </select> </mapper>
sqlsession工具类
package maya.util; /** * Mybatis工具类 * @author User * */ import java.io.IOException; import java.io.InputStream; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MybatisSqlSessionFactoryUtil { private static SqlSessionFactory ssf; private static SqlSession ss; /** * 获取Mybatis核心SqlSessionFactory */ public static SqlSessionFactory getSqlSessionFactory() { InputStream in = null; try { in = Resources.getResourceAsStream("mybatis-config.xml"); ssf = new SqlSessionFactoryBuilder().build(in); in.close(); } catch (IOException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } return ssf; } /** * 获取SqlSession */ public static SqlSession getSqlSession() { ss = getSqlSessionFactory().openSession(); return ss; } }
Stundent测试用例
package maya.util; import static org.junit.Assert.*; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import maya.dao.StudentMapper; import maya.model.Student; public class StudentJUnit { private SqlSession ss; private StudentMapper sm; public void addStutest() { /*Student s = new Student(); s.setSname("巨魔战将"); s.setSsex("男"); s.setSclass(0402); s.setMark(25); int i = ss.insert("maya.dao.StudentMapper.addStu",s); */ Student s = new Student(null,"qq","男",2,55); int i = sm.addStu(s); } @Test public void getStuMany() { Map<String, Object> map = new HashMap<String,Object>(); map.put("ssex", "女"); map.put("sname", "风行%"); List<Student> list = sm.getStuByMap(map); for (Student s : list) { System.out.println(s); } } public void delStu() { int i = sm.delStu(19); } public void updateStu() { Student s = new Student(18,"董小姐","女",403,27); sm.updateStu(s); System.out.println(s); } public void getStuOne() { Student s = sm.getStuBySno(14); System.out.println(s); } @Before public void setUp() throws Exception { ss = MybatisSqlSessionFactoryUtil.getSqlSession(); sm = ss.getMapper(StudentMapper.class); } @After public void tearDown() throws Exception { ss.commit(); ss.close(); } }
StundentInfo测试用例
package maya.util; import static org.junit.Assert.*; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import maya.dao.StudentInfoMapper; import maya.model.StudentInfo; public class StudentInfoJUnit { private SqlSession ss; private StudentInfoMapper sim; @Test public void test() { List<StudentInfo> list = sim.selectAll(); for (StudentInfo si : list) { System.out.println(si); } } @Before public void setUp() throws Exception { ss = MybatisSqlSessionFactoryUtil.getSqlSession(); sim = ss.getMapper(StudentInfoMapper.class); } @After public void tearDown() throws Exception { ss.commit(); ss.close(); } }
多条件查询结果：
StudentInfo [id=1, student=Student [sno=14, sname=敌法师, ssex=男, sclass=403, mark=23], saddress=近卫, sbirthday=Thu Apr 20 00:00:00 CST 2017]
StudentInfo [id=2, student=Student [sno=16, sname=痛苦女王, ssex=女, sclass=402, mark=21], saddress=天灾, sbirthday=Wed Apr 19 00:00:00 CST 2017]
