侧边栏壁纸
博主头像
coydone博主等级

记录学习，分享生活的个人站点

  • 累计撰写 306 篇文章
  • 累计创建 51 个标签
  • 累计收到 0 条评论
标签搜索

目 录CONTENT

文章目录
Java框架

改良CRUD代码

coydone
coydone
2022-03-09 / 0 评论 / 0 点赞 / 363 阅读 / 18,149 字 / 正在检测是否收录...
温馨提示：
本文最后更新于 2022-04-10，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

源码：mybatis03.zip

准备

数据库介绍

1、grade年级表：字段：年级编号：gid；年级名称：gname。

2、student学生表：字段：学号：xh；学生名：name；年龄：age；性别：sex；birthday：生日；state：状态；地址：address；年级编号：gid。

代码编写思路

  1. 先建立工程，在pom.xml中配置相应的环境。

  2. 创建相应的包结构。dao层Mybatis官方推荐命名为Mapper。比如包结构为com.coydone下的entity实体包、mapper映射包（对应以前的dao层）、utils包（存放工具类）、test（测试包）。

  3. 导入mybatis的配置文件mybatis-config.xml，查看修改相应的参数。

  4. 导入相应的工具类。进行封装了的MybatisUtils。

  5. 编写Mapper方法。

  6. 在对应的Mapper.xml中编写sql语句。

  7. 编写测试类。

新建Maven工程，配置pom.xml

<dependencies>
    <!-- MySQL依赖-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <!-- 加入MyBatis 依赖-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency>
    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

<build>
    <!--配置相关的资源进行打包-->
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
    </resources>
</build>

创建包结构，导入配置和工具类

项目结构：

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>
    <typeAliases>
        <package name="com.coydone.entity"></package>
    </typeAliases>
    <!-- 指定义连接数据的配置 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/k0503db?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root123"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载sql映射文件-->
    <mappers>
        <package name="com.coydone.mapper"/>
    </mappers>
</configuration>

MyBatisUtil.java

package com.coydone.utils;

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.Reader;
public class MyBatisUtil {
	private MyBatisUtil(){
	}
	private static final String RESOURCE = "mybatis-config.xml";
	private static SqlSessionFactory sqlSessionFactory = null;
	private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
	static {
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader(RESOURCE);
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			sqlSessionFactory = builder.build(reader);
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new ExceptionInInitializerError("初始化MyBatis错误，请检查配置文件或数据库");
		}
	}
	public static SqlSessionFactory getSqlSessionFactory(){
		return sqlSessionFactory;
	}
	public static SqlSession getSession(){
		//sessionTL的get()方法根据当前线程返回其对应的线程内部变量，
		//也就是我们需要的Session，多线程情况下共享数据库链接是不安全的。
		//ThreadLocal保证了每个线程都有自己的Session。
		SqlSession session = threadLocal.get();
		// 如果session为null，则打开一个新的session
		if (session == null){
			session = (sqlSessionFactory !=null) ?sqlSessionFactory.openSession():null;
			threadLocal.set(session); // 5
		}
		return session;
	}
	public static void closeSession(){
		SqlSession session = (SqlSession) threadLocal.get(); // 2
		threadLocal.set(null);
		if (session !=null){
			session.close();
		}
	}
}

实体类

Grade.java

package com.coydone.entity;
import java.io.Serializable;
//年级表
public class Grade implements Serializable {
    private Integer gid;
    private String gname;
    //省略构造方法、getter()、setter()方法、toString()方法
}

Student.java

package com.coydone.entity;
import java.io.Serializable;
import java.util.Date;
public class Student implements Serializable {
    private Integer xh;
    private String name;
    private Integer age;
    private String sex;
    private Date birthday;
    private Integer state;
    private String address;
    private Integer gid;

    //省略构造方法、getter()、setter()方法、toString()方法
}

进行功能的编码及测试

  • 遵循一功能一方法、一数据表一实体类一映射。

  • 注意增删改操作需要提交SQL事务。

对Student表进行单表CURD操作：

查询所有学生

//在mapper包下创建StudentMapper接口
public interface StudentMapper {
    List<Student> findAll();
}

//在mapper包下创建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包下的StudentMapper id与方法名对应-->
<mapper namespace="com.coydone.mapper.StudentMapper">
    <select id="findAll" resultType="Student">
        select * from student
    </select>
</mapper>
    
//编写测试类 使用单元测试
import org.junit.Test;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class StudentTest {
    //通过工具类获取sqlSession对象，再获取出对应的映射对象，studentMapper就可以调用定义的方法了。
    SqlSession sqlSession = MyBatisUtil.getSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    @Test
    public void findAll(){
        List<Student> students = studentMapper.findAll();
        for (Student student : students) {
            System.out.println(student);
        }
    }
}

通过主键查询单个学生

//StudentMapper接口下的方法
Student findById(Integer xh);

//StudentMapper.xml下的sql配置
<select id="findById" parameterType="int" resultType="Student">
    select * from student where xh = #{xh}
</select>
    
//测试
@Test
public void findById(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入要查询的学号:");
    int xh =input.nextInt();

    Student student = studentMapper.findById(xh);
    System.out.println(student);
}

添加学生

//StudentMapper接口下的方法
int addStudent(Student student);

//StudentMapper.xml下的sql配置
<insert id="addStudent" parameterType="Student">
    insert into student(xh,name,age,sex,birthday,state,address,gid) values(#{xh},#{name},#{age},#{sex},#{birthday},#{state},#{address},#{gid})
</insert>
   
//测试
@Test
public void addStudent(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入学号:");
    int xh=input.nextInt();
    System.out.println("请输入姓名:");
    String name=input.next();
    System.out.println("请输入年龄:");
    int age =input.nextInt();
    System.out.println("请输入性别:");
    String sex=input.next();
    System.out.println("请输入生日:");
    String birthday =input.next();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date date = null;
    try {
        date = sdf.parse(birthday);
    } catch (ParseException e) {
        e.printStackTrace();
    }

    System.out.println("请输入状态:");
    int state=input.nextInt();
    System.out.println("请输入地址:");
    String address=input.next();
    System.out.println("请输入gid:");
    int gid=input.nextInt();

    Student student = new Student(xh,name,age,sex,date,state,address,gid);
    int rows = studentMapper.addStudent(student);
    sqlSession.commit();
    if(rows>0)
        System.out.println("添加成功");
    else
        System.out.println("添加失败");
}

根据主键修改

//StudentMapper接口下的方法
int updateStudent(Student student);

//StudentMapper.xml下的sql配置
<update id="updateStudent" parameterType="Student">
    update student set name = #{name},address = #{address}  where xh = #{xh}
</update>
   
//测试  先通过主键查询查询出单个的学生，存入student对象中，在修改student。
@Test
public void updateStudent(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入要修改的学号:");
    int xh=input.nextInt();
    System.out.println("请输入修改的姓名:");
    String name=input.next();
    System.out.println("请输入修改的地址:");
    String address=input.next();

    Student student = studentMapper.findById(xh);
    student.setName(name);
    student.setAddress(address);

    int rows = studentMapper.updateStudent(student);
    sqlSession.commit();
    if(rows>0)
        System.out.println("修改成功");
    else
        System.out.println("修改失败");
}

根据主键删除

//StudentMapper接口下的方法
int deleteStudent(Integer xh);


//StudentMapper.xml下的sql配置
<delete id="deleteStudent" parameterType="int" >
    delete from student where xh = #{xh}
</delete>

//测试
@Test
public void deleteStudent(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入要删除的学号:");
    int xh=input.nextInt();

    int rows = studentMapper.deleteStudent(xh);
    sqlSession.commit();
    if(rows>0)
        System.out.println("删除成功");
    else
        System.out.println("删除失败");
}

实现条件查询

我们可以将查询所需的条件封装成一个实体类。每次需要增加条件时只需要在其实体类中添加一个属性。

package com.coydone.entity;
import java.io.Serializable;
//学生条件
public class StudentCondition implements Serializable {
    private String name;
    private Integer startAge;
    private Integer endAge;
    private String address;
	//省略getter和setter方法
}

//StudentMapper接口下的方法
List<Student> findStudentByCondition(StudentCondition condition);

//StudentMapper.xml下的sql配置
<select id="findStudentByCondition" parameterType="StudentCondition" resultType="Student">
    select * from student where 1=1
    <if test="name!=null">
        and name like concat('%',#{name},'%')
    </if>
    <if test="startAge!=null">
    	and age > #{startAge}
	</if>
    <if test="endAge!=null">
        and age &lt; #{endAge}
	</if>
    <if test="address!=null">
        and address like concat('%',#{address},'%')
    </if>
</select>
        
//测试
@Test
public void findByConditon(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入要查询的姓名:");
    String name =input.nextLine();

    System.out.println("请输入起始年龄:");
    String startAge =input.nextLine();

    System.out.println("请输入结束年龄:");
    String endAge =input.nextLine();

    System.out.println("请输入地址:");
    String address =input.nextLine();

    StudentCondition condition = new StudentCondition();

    if (name!=null&&!name.equals("")){
    	condition.setName(name);
    }
    if (!startAge.equals("")){
    	condition.setStartAge(Integer.parseInt(startAge));
    }
    if (!endAge.equals("")){
    	condition.setEndAge(Integer.parseInt(endAge));
    }
    if (!address.equals("")){
    	condition.setAddress(address);
    }
    List<Student> students = studentMapper.findStudentByCondition(condition);
    for (Student student : students) {
        System.out.println(student);
    }
    //关闭sqlSession
    MyBatisUtil.closeSession();
}

多条件分页查询

分页所需的两个参数为当前记录的索引值和每页显示的记录数。所以我们要先条件查询表的总记录数，在通过总记录数和每页显示的记录数可以获取出总页码数。分页的条件我们可以重新封装为一个PageUtils工具类。再将查询的参数和分页的查询使用Map集合进行存储。

package com.coydone.utils;
public class PageUtils {
    private Integer currentPage;//当前页
    private Integer startIndex;//开始索引
    private Integer pageSize=5;//每页显示条数
    private Integer totalCount;//总记录数
    private Integer totalPage;//总页数

    public Integer getCurrentPage() {
        return currentPage;
    }
    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
        this.startIndex=(this.currentPage-1)*this.pageSize;
    }
    public Integer getPageSize() {
        return pageSize;
    }
    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }
    public Integer getTotalCount() {
        return totalCount;
    }
    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
        //计算总页数
        this.totalPage=(int)Math.ceil((this.totalCount*1.0/this.pageSize));
    }
    public Integer getTotalPage() {
        return totalPage;
    }
    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }
    public Integer getStartIndex() {
        return startIndex;
    }
    public void setStartIndex(Integer startIndex) {
        this.startIndex = startIndex;
    }
}

//条件查询表的总记录数
int findCounts(StudentCondition condition);

<select id="findCounts" parameterType="StudentCondition" resultType="int">
    select count(1) from student where 1=1
    <if test="name!=null">
        and name like concat('%',#{name},'%')
    </if>
    <if test="startAge!=null">
        and age > #{startAge}
    </if>
    <if test="endAge!=null">
        and age &lt; #{endAge}
    </if>
    <if test="address!=null">
        and address like concat('%',#{address},'%')
    </if>
</select>

@Test
//条件查询总记录数
public void findCounts(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入要查询的姓名:");
    String name =input.nextLine();

    System.out.println("请输入起始年龄:");
    String startAge =input.nextLine();

    System.out.println("请输入结束年龄:");
    String endAge =input.nextLine();

    System.out.println("请输入地址:");
    String address =input.nextLine();

    StudentCondition condition = new StudentCondition();

    if (name!=null&&!name.equals("")){
        condition.setName(name);
    }
    if (!startAge.equals("")){
        condition.setStartAge(Integer.parseInt(startAge));
    }
    if (!endAge.equals("")){
        condition.setEndAge(Integer.parseInt(endAge));
    }

    if (!address.equals("")){
        condition.setAddress(address);
    }

    int counts = studentMapper.findCounts(condition);
    System.out.println(counts);

    //关闭sqlSession
    MyBatisUtil.closeSession();
}

//多条件分页查询
List<Student> findAllByLimit(Map<String, Object> map);

<select id="findAllByLimit" parameterType="map" resultType="Student">
    select * from student where 1=1
    <if test="name!=null">
        and name like concat('%',#{name},'%')
    </if>
    <if test="startAge!=null">
        and age > #{startAge}
    </if>
    <if test="endAge!=null">
        and age &lt; #{endAge}
    </if>
    <if test="address!=null">
        and address like concat('%',#{address},'%')
    </if>
    limit #{startIndex},#{pageSize}
</select>

@Test
//多条件分页查询
public void findAllByLimit(){
    Scanner input=new Scanner(System.in);
    System.out.println("请输入要查询的姓名:");
    String name =input.nextLine();

    System.out.println("请输入起始年龄:");
    String startAge =input.nextLine();

    System.out.println("请输入结束年龄:");
    String endAge =input.nextLine();

    System.out.println("请输入地址:");
    String address =input.nextLine();

    PageUtils pageUtils = new PageUtils();
    //设置当前页
    pageUtils.setCurrentPage(1);
    //设置每页显示的总条数
    pageUtils.setPageSize(5);

    StudentCondition condition = new StudentCondition();
    int counts = studentMapper.findCounts(condition);
    pageUtils.setTotalCount(counts);

    Map<String , Object> map = new HashMap<>();

    if (name!=null&&!name.equals("")){
        map.put("name", name);
    }
    if (!startAge.equals("")){
        map.put("startAge", startAge);
    }
    if (!endAge.equals("")){
        map.put("endAge", endAge);
    }

    if (!address.equals("")){
        map.put("address", address);
    }

    map.put("startIndex", pageUtils.getStartIndex());
    map.put("pageSize", pageUtils.getPageSize());

    List<Student> students = studentMapper.findAllByLimit(map);
    for (Student student : students) {
        System.out.println(student);
    }
    System.out.println("当前第"+pageUtils.getCurrentPage()+"页，共"+counts+"条");
    //关闭sqlSession
    MyBatisUtil.closeSession();
}

多表连接查询

多表查询在本案例中有两种：

多对一：查询所有学生的信息同时将其所在的年级信息查询出来。此时学生表为主表，年级表为从表，我们要在主表中添加从表的属性。又因为一个学生只能对应一个年级，所有我们可以直接添加年级的属性值（推荐），在从表中属性过多的时候，我们添加从表的对象会比较方便（情况很少，不推荐）。

一对多：查询年级信息及每个年级下的所有学生。此时年级表为主表，学生表为从表，一个年级下存在着多个学生，我们需要在年级表中采用泛型集合的方式List<Student>。

查询所有学生及所在年级

方式一

//在Student表中添加年级表属性
private String gname;
//添加getter和setter方法（省略）

//在StudentMapper中写查询方法
List<Student> findAllStudentAndGrade();

//在StudentMapper.xml中写查询方法sql
<select id="findAllStudentAndGrade" resultType="Student">
    SELECT s.*,g.gname FROM student s LEFT JOIN grade g ON s.gid=g.gid
</select>

//测试
@Test
public void findAllStudentAndGrade(){
	List<Student> studentAndGrade = studentMapper.findAllStudentAndGrade();
    for (Student student : studentAndGrade) {
    	System.out.println(student+student.getGname());
    }
}

方式二

//添加年级对象
private Grade grade;
//添加getter和setter方法（省略）

List<Student> findAllStudentAndGrade2();

//此时返回的查询结果为两张表的复杂类型，我们通过association来映射对应的实体Grade
<resultMap id="result" type="Student">
  <id column="xh" property="xh"></id>
  <result column="name" property="name"></result>
  <result column="age" property="age"></result>
  <result column="sex" property="sex"></result>
  <result column="birthday" property="birthday"></result>
  <result column="state" property="state"></result>
  <result column="address" property="address"></result>
  <association property="grade" javaType="Grade">
    <result column="gname" property="gname"></result>
  </association>
</resultMap>
<select id="findAllStudentAndGrade2" resultMap="result">
    SELECT s.*,g.gname FROM student s LEFT JOIN grade g ON s.gid=g.gid
</select>
    
//测试
@Test
public void findAllStudentAndGrade2(){
    List<Student> allStudentAndGrade2 = studentMapper.findAllStudentAndGrade2();
    for (Student student : allStudentAndGrade2) {
    	System.out.println(student+student.getGrade().getGname());
    }
}

查询所有年级及每个年级所有的学生

//在grade表中添加Student表的list集合
private List<Student> students;
//添加getter和setter方法（省略）

//在GradeMapper中写查询方法
List<Grade> findAllGradeAndStudent();

//在GradeMapper.xml中写查询方法sql
//此时查询的结果为复杂类型，只能用resultMap，使用collection将从表中的数据以list集合形式返回
<resultMap id="gradesAndStu" type="Grade">
  <id column="gid" property="gid"></id>
  <result column="gname" property="gname"></result>
  <collection property="students" ofType="Student">
    <id column="xh" property="xh"></id>
    <result column="name" property="name"></result>
    <result column="age" property="age"></result>
    <result column="sex" property="sex"></result>
    <result column="birthday" property="birthday"></result>
    <result column="state" property="state"></result>
    <result column="address" property="address"></result>
  </collection>
</resultMap>
<select id="findAllGradeAndStudent" resultMap="gradesAndStu">
SELECT g.*,s.* FROM grade g LEFT JOIN student s ON g.gid=s.gid ORDER BY g.gid ASC
</select>

//测试
@Test
public void findAllGradeAndStudent(){
    List<Grade> gradeAndStudent = gradeMapper.findAllGradeAndStudent();
    for (Grade grade : gradeAndStudent) {
        System.out.println(grade.getGname());
        List<Student> students = grade.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
    }
}
0
Mybatis
  1. qrcode alipay
  2. qrcode weixin
版权归属： coydone
本文链接： https://www.coydone.com/archives/196
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区