java web开发入门七（mybatis）基于intellig idea
mybatis
一、 入门开发步骤
1.导入相关jar包
mybatis3.4.2核心开发包
asm-5.1.jar
cglib-3.2.4.jar
commons-logging-1.1.3.jar
log4j-1.2.17.jar
mybatis-3.4.2.jar
mysql驱动包
mysql-connector-java-5.1.7-bin.jar
2.定义实体及实体映射文件
定义member实体
package com.eggtwo.entity;import java.math.BigDecimal;
import java.util.Date;
public class Member {
private int id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
private String name;
private int age;
private Date birthday;
private boolean man;
private BigDecimal score;
public BigDecimal getScore() {
return score;
}
public void setScore(BigDecimal score) {
this.score = score;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public boolean isMan() {
return man;
}
public void setMan(boolean man) {
this.man = man;
}
}
View Code
定义MemberMapper.xml
<?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="memberDao">
<!--
*******当实体属性和表字段名称一致的话resultMap标签可以省略********
resultMap标签：映射实体和表关系
id:映射关系id,要唯一
type:实体全路径
-->
<resultMap id="memberMap" type="com.eggtwo.entity.Member">
<!--id：映射主键属性
result:映射非主键属性
property:实体属性名称
column:表字段名称
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="birthday" column="birthday"/>
<result property="man" column="man"/>
<result property="score" column="score"/>
</resultMap>
<insert id="add" parameterType="com.eggtwo.entity.Member" useGeneratedKeys="true" keyProperty="id">
insert into t_member(name,age,birthday,man,score)
values(#{name},#{age},#{birthday},#{man},#{score})
</insert>
<update id="update" parameterType="com.eggtwo.entity.Member">
update t_member set
name = #{name},
age = #{age},
birthday = #{birthday},
man = #{man},
score = #{score}
where id = #{id}
</update>
<delete id="delete" parameterType="int">
delete from t_member where id = #{id}
</delete>
<!-- <select >-->
<!--resultType使用mybatis.xml中设置的别名，这样可以简化难度-->
<select id="getById" parameterType="int" resultType="memberEntity">
select id,name,age,birthday,man,score
from t_member
where id=#{id}
</select>
<!--
理论上resultType的值应该是:List<com.eggtwo.entity.Member>
实际上只需要写List集合中的类型就可以
-->
<select id="getAll" resultType="com.eggtwo.entity.Member">
select *
from t_member
</select>
<!--分页：多参数的写法-->
<select id="getPageList" parameterType="map" resultType="com.eggtwo.entity.Member">
select id,name,age,birthday,man,score
from t_member limit #{start},#{size}
</select>
</mapper>
View Code
3.定义mybatis.cfg.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>
<!-- 加载类路径下的属性文件 -->
<!--加载数据库连接配置信息-->
<properties resource="jdbc.properties"/>
<!-- 设置类型别名 -->
<typeAliases>
<!--设置实体类Member的别名：memberEntity-->
<typeAlias type="com.eggtwo.entity.Member" alias="memberEntity"/>
</typeAliases>
<!-- 设置一个默认的连接环境信息 -->
<environments default="mysql_developer">
<!-- 连接环境信息，取一个任意唯一的名字 -->
<environment id="mysql_developer">
<!-- mybatis使用jdbc事务管理方式 -->
<transactionManager type="jdbc"/>
<!-- mybatis使用连接池方式来获取连接 -->
<dataSource type="pooled">
<!--配置与数据库交互的4个必要属性 -->
<!-- 直接配置方式，不推荐-->
<!--<property name="driver" value="com.mysql.jdbc.Driver"/>-->
<!-- <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>-->
<!--<property name="username" value="root"/>-->
<!-- <property name="password" value="123456"/>-->
<!--直接在jdbc.properties文件中配置连接-->
<property name="driver" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<!-- 加载映射文件-->
<mappers>
<mapper resource="com/eggtwo/entity/MemberMapper.xml"/>
</mappers>
</configuration>
View Code
mybatis.cfg.xml加载的jdbc.properties文件
mysql.driver=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://127.0.0.1:3306/test
mysql.username=root
mysql.password=123456
4.定义mybatis帮助类：MybatisUtil
package com.eggtwo.dao;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.Reader;
import java.sql.Connection;
public class MybatisUtil {
private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
private static SqlSessionFactory sqlSessionFactory;
/**
* 加载位于src/mybatis.xml配置文件
*/
static{
try {
Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 禁止外界通过new方法创建
*/
private MybatisUtil(){}
/**
* 获取SqlSession
*/
public static SqlSession getSqlSession(){
//从当前线程中获取SqlSession对象
SqlSession sqlSession = threadLocal.get();
//如果SqlSession对象为空
if(sqlSession == null){
//在SqlSessionFactory非空的情况下，获取SqlSession对象
sqlSession = sqlSessionFactory.openSession();
//将SqlSession对象与当前线程绑定在一起
threadLocal.set(sqlSession);
}
//返回SqlSession对象
return sqlSession;
}
/**
* 关闭SqlSession与当前线程分开
*/
public static void closeSqlSession(){
//从当前线程中获取SqlSession对象
SqlSession sqlSession = threadLocal.get();
//如果SqlSession对象非空
if(sqlSession != null){
//关闭SqlSession对象
sqlSession.close();
//分开当前线程与SqlSession对象的关系，目的是让GC尽早回收
threadLocal.remove();
}
}
}
View Code
5.定义MemberDao测试mybatis增删查改
View Code
6.测试
public static void main(String[] args) throws Exception { Connection conn = MybatisUtil.getSqlSession().getConnection();
System.out.println(conn != null ? "连接成功" : "连接失败");
MemberDao memberDao = new MemberDao();
Member member = new Member();
member.setId(2);
member.setAge(14);
member.setName("张三1");
member.setBirthday(new Date());
member.setMan(true);
member.setScore(new BigDecimal(123.24));
memberDao.add(member);
// memberDao.update(member);
// memberDao.delete(3);
Member member1 = memberDao.getById(2);
System.out.println(member1.getName());
List<Member> memberList = memberDao.getPageList(2,2);
System.out.println("size:"+memberList.size());
for (Member m : memberList){
System.out.println(m.getId());
}
}
View Code
二、 动态SQL
1.多条件查询
mapper.xml配置
<select id="getListByWhere" parameterType="map" resultType="com.eggtwo.entity.Member">select id,name,age,birthday,man,score
from t_member
<where>
<if test="name!=null and name!=''">
and name like #{name}
</if>
<if test="score!=null">
and score > #{score}
</if>
</where>
</select>
dao调用
public List<Member> getListByWhere(String name,BigDecimal score) throws Exception { SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
Map<String,Object> map=new LinkedHashMap<>();
map.put("name",name==null?null: "%"+name+"%");//做like查询
map.put("score",score);
List<Member> memberList= sqlSession.selectList("memberDao.getListByWhere",map);
return memberList;
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
return null;
}
测试：
List<Member> memberList = memberDao.getListByWhere("3",new BigDecimal(30));2.部分更新字段
mapper.xml配置
<update id="dynamicUpdate" parameterType="map">update t_member
<set>
<if test="name!=null">
name = #{name},
</if>
<if test="age!=null">
age = #{age},
</if>
<if test="birthday!=null">
birthday = #{birthday},
</if>
<if test="man!=null">
man = #{man},
</if>
<if test="score!=null">
score = #{score},
</if>
</set>
where id=#{id}
</update>
dao调用：
public void dynamicUpdate(int id, String name,Integer age,Date birthday,Boolean man,BigDecimal score) throws Exception { SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
Map<String,Object> map=new LinkedHashMap<>();
map.put("id",id);
map.put("name",name);
map.put("age",age);
map.put("birthday",birthday);
map.put("man",man);
map.put("score",score);
sqlSession.update("memberDao.dynamicUpdate", map);
//提交事务
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
//事务回滚
sqlSession.rollback();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
}
测试：
memberDao.dynamicUpdate(2,"jack",null,null,false,null);
3.根据id集合或数组不确定删除：delete from table where id in(id1,id2,id3,……)
mapper.xml配置
<!--根据ids数组批量删除数据--><delete id="batchDelete" >
delete from t_member where id in
<!--
循环数组
解析成：(1,2,34)
#id表示数组中的每一个元素，名称可以任意写
-->
<foreach collection="array" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<!--根据ids列表批量删除数据-->
<delete id="batchDeleteList" >
delete from t_member where id in
<!--
循环数组
解析成：(1,2,34)
#id表示数组中的每一个元素，名称可以任意写
-->
<foreach collection="list" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
dao调用：
public void batchDelete(int[] ids) throws Exception { SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
sqlSession.update("memberDao.batchDelete", ids);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
//事务回滚
sqlSession.rollback();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
}
public void batchDeleteList(List<Integer> ids) throws Exception {
SqlSession sqlSession = null;
try {
sqlSession = MybatisUtil.getSqlSession();
sqlSession.update("memberDao.batchDeleteList", ids);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
//事务回滚
sqlSession.rollback();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
}
测试：
memberDao.batchDelete(new int[]{1,4,5}); List<Integer> list=new ArrayList<>() ;
list.add(1);
list.add(17);
list.add(18);
memberDao.batchDeleteList(list);
4.动态插入部分字段
这个不常用
三、 多表查询
1.一对一映射
班级包t_grade和学生表t_student表
获取学生信息时同时把学生对应的班级信息获取出来
第一步：创建实体
Grade实体
package com.eggtwo.entity;import java.util.List;
public class Grade {
private Integer id;
private String gradeName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getGradeName() {
return gradeName;
}
public void setGradeName(String gradeName) {
this.gradeName = gradeName;
}
}
Grade
Student实体
package com.eggtwo.entity;import java.util.Date;
public class Student {
private Integer id;
private String name;
private Integer age;
private Date birthday;
private Boolean man;
private Grade grade;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Boolean getMan() {
return man;
}
public void setMan(Boolean man) {
this.man = man;
}
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
}
com.eggtwo.entity.Student
第二步：建立mapper.xml
GradeMapper.xml
<?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="gradeDao">
<!--
*******当实体属性和表字段名称一致的话resultMap标签可以省略********
resultMap标签：映射实体和表关系
id:映射关系id,要唯一
type:实体全路径
-->
<resultMap id="gradeMap" type="com.eggtwo.entity.Grade">
<!--id：映射主键属性
result:映射非主键属性
property:实体属性名称
column:表字段名称
-->
<id property="id" column="id"/>
<result property="gradeName" column="gradeName"/>
</resultMap>
</mapper>
View Code
StudentMapper.xml
<?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="studentDao">
<resultMap id="studentMap" type="com.eggtwo.entity.Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="birthday" column="birthday"/>
<result property="man" column="man"/>
<!--引用GradeMapper.xml中的resultMap，不需要再写一遍映射-->
<association property="grade" resultMap="gradeDao.gradeMap"/>
</resultMap>
<!--返回值类型用resultMap代替resultType，可以解决实体字段类型和表字段类型不一致的问题-->
<select id="getById" parameterType="int" resultMap="studentMap">
select s.id,s.name,s.age,s.birthday,s.man,g.id,g.gradeName
from t_student s, t_grade g
where s.gradeId=g.id
and s.id=#{id}
</select>
</mapper>
View Code
第三步：将mapper.xml加入到mybatis.cfg.xml文件中
<mappers><mapper resource="com/eggtwo/entity/StudentMapper.xml"/>
<mapper resource="com/eggtwo/entity/GradeMapper.xml"/>
</mappers>
View Code
第四步：编写StudentDao
package com.eggtwo.dao;import com.eggtwo.entity.Member;
import com.eggtwo.entity.Student;
import org.apache.ibatis.session.SqlSession;
import java.math.BigDecimal;
import java.sql.Connection;
import java.util.*;
public class StudentDao {
public Student getById(int id) throws Exception {
SqlSession sqlSession = null;
Student student = null;
try {
sqlSession = MybatisUtil.getSqlSession();
student = sqlSession.selectOne("studentDao.getById", id);
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
return student;
}
}
View Code
第五步：测试
public static void main(String[] args) throws Exception { StudentDao studentDao = new StudentDao();
Student s = studentDao.getById(2);
System.out.println(s.getName());
}
View Code
2.一对多映射
根据班级名称获取学生列表信息
第一步：创建实体
Grade实体
package com.eggtwo.entity;import java.util.ArrayList;
import java.util.List;
public class Grade {
private Integer id;
private String gradeName;
private List<Student> studentList = new ArrayList<Student>();
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getGradeName() {
return gradeName;
}
public void setGradeName(String gradeName) {
this.gradeName = gradeName;
}
public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
}
View Code
Student实体
package com.eggtwo.entity;import java.util.Date;
public class Student {
private Integer id;
private String name;
private Integer age;
private Date birthday;
private Boolean man;
private Grade grade;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Boolean getMan() {
return man;
}
public void setMan(Boolean man) {
this.man = man;
}
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
}
View Code
第二步：建立mapper.xml
GradeMapper.xml和StudentMapper.xml
<?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="gradeDao">
<!--
*******当实体属性和表字段名称一致的话resultMap标签可以省略********
resultMap标签：映射实体和表关系
id:映射关系id,要唯一
type:实体全路径
-->
<resultMap id="gradeMap" type="com.eggtwo.entity.Grade">
<!--id：映射主键属性
result:映射非主键属性
property:实体属性名称
column:表字段名称
-->
<id property="id" column="id"/>
<result property="gradeName" column="gradeName"/>
</resultMap>
</mapper>
View Code
<?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="studentDao">
<resultMap id="studentMap" type="com.eggtwo.entity.Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="birthday" column="birthday"/>
<result property="man" column="man"/>
</resultMap>
<select id="getListByGradeName" parameterType="string" resultMap="studentMap">
select s.id,s.name,s.age,s.birthday,s.man
from t_student s, t_grade g
where s.gradeId=g.id
and g.gradeName=#{gradeName}
</select>
</mapper>
View Code
第三步：将mapper.xml加入到mybatis.cfg.xml文件中
省略
第四步：编写StudentDao
public List<Student> getListByGradeName(String gradeName) throws Exception { SqlSession sqlSession = null;
List<Student> studentList = null;
try {
sqlSession = MybatisUtil.getSqlSession();
studentList = sqlSession.selectList("studentDao.getListByGradeName",gradeName);
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
return studentList;
}
View Code
第五步：测试
List<Student> studentList = studentDao.getListByGradeName("一班");3.自定义实体对象（不和数据库表对应）用于接收多表查询出来的复合数据
第一步：定义实体GradeStudent
package com.eggtwo.entity;import java.util.Date;
public class GradeStudent {
private Integer gradeId;
private String gradeName;
private Integer studentId;
private String studentName;
private Integer studentAge;
private Date studentBirthday;
public Integer getGradeId() {
return gradeId;
}
public void setGradeId(Integer gradeId) {
this.gradeId = gradeId;
}
public String getGradeName() {
return gradeName;
}
public void setGradeName(String gradeName) {
this.gradeName = gradeName;
}
public Integer getStudentId() {
return studentId;
}
public void setStudentId(Integer studentId) {
this.studentId = studentId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public Integer getStudentAge() {
return studentAge;
}
public void setStudentAge(Integer studentAge) {
this.studentAge = studentAge;
}
public Date getStudentBirthday() {
return studentBirthday;
}
public void setStudentBirthday(Date studentBirthday) {
this.studentBirthday = studentBirthday;
}
public Boolean getStudentMan() {
return studentMan;
}
public void setStudentMan(Boolean studentMan) {
this.studentMan = studentMan;
}
private Boolean studentMan;
}
View Code
第二步：定义GradeStudentMapper.xml
<?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="gradeStudentDao">
<resultMap id="gradeStudentMap" type="com.eggtwo.entity.GradeStudent">
<result property="studentId" column="studentId"/>
<result property="studentName" column="studentName"/>
<result property="studentAge" column="studentAge"/>
<result property="studentBirthday" column="studentBirthday"/>
<result property="studentMan" column="studentMan"/>
<result property="gradeId" column="gradeId"/>
<result property="gradeName" column="gradeName"/>
</resultMap>
<!--返回值类型用resultMap代替resultType，可以解决实体字段类型和表字段类型不一致的问题-->
<select id="getGradeStudentList" resultMap="gradeStudentMap">
select s.id as studentId,
s.name as studentName,
s.age as studentAge,
s.birthday as studentBirthday,
s.man as studentMan,
g.id as gradeId,
g.gradeName
from t_student s, t_grade g
where s.gradeId=g.id
</select>
</mapper>
View Code
第三步：将GradeStudentMapper.xml加入mybatis.xml
<mappers><mapper resource="com/eggtwo/entity/MemberMapper.xml"/>
<mapper resource="com/eggtwo/entity/StudentMapper.xml"/>
<mapper resource="com/eggtwo/entity/GradeMapper.xml"/>
<mapper resource="com/eggtwo/entity/GradeStudentMapper.xml"/>
</mappers>
View Code
第四步：编写dao
public List<GradeStudent> getGradeStudentList() throws Exception { SqlSession sqlSession = null;
List<GradeStudent> gradeStudentList = null;
try {
sqlSession = MybatisUtil.getSqlSession();
gradeStudentList = sqlSession.selectList("gradeStudentDao.getGradeStudentList");
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭连接
MybatisUtil.closeSqlSession();
}
return gradeStudentList;
}
View Code
以上是 java web开发入门七（mybatis）基于intellig idea 的全部内容， 来源链接： utcz.com/z/393570.html
