java web开发入门七(mybatis)基于intellig idea

java

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.Driver

mysql.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

回到顶部