Mybatis（二）——增删改查

1. 建立初始项目

新建MybatisProject2项目，新建实体类Student和数据库表student，其中类的属性与表的属性名保持一致，否则需要特殊配置才能映射成功。
根据Mybatis（一），建立初步的项目，项目结构如下

sql表结构如下

Student类代码如下，注意属性名与表中的属性名称一致

public class Student {
    private int stuNo;
    private String stuName;
    private int stuAge;
    private String gradeName;
    .............
}

con.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.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3307/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载映射文件-->
        <mapper resource="mappers/StudentMapper.xml"/>
    </mappers>
</configuration>

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="com.santiago.maven.StudentMapper">
    <select id="queryStudentByStuNo" resultType="com.santiago.maven.Student" parameterType="int">
        select * from student where stuno = #{stuno}
    </select>
</mapper>

测试代码如下

package com.santiago.maven;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class TestMybatis {
    public static void main(String[] args) {
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        //StudentMapper.xml中的namespace和id共同确定唯一的sql
        String statement = "com.santiago.maven.StudentMapper.queryStudentByStuNo";
        Student student = session.selectOne(statement, 1);
        System.out.println(student);
        session.close();
    }
}

测试结果如下

2. 添加增删改查功能

Mybatis约定：

• parameterType和returnType各自最多只能有一个，对于插入语句，输入的参数类型可能有多个，参数类型应该用类或集合表示。
• 如果输入的参数是简单类型（8个基本类型+String），则可以使用任意占位符#{xxx}，否则只能使用具体的属性名，如#{parameterName}
• 如果返回值类型是对象，无论返回的对象是一个还是多个，resultType只写一个，例如com.santiago.maven.Student

在StudentMapper.xml加入如下语句

    <insert id="addStudent" parameterType="com.santiago.maven.Student">
        insert into student(stuno,stuname,stuage,gradename) values(#{stuNo},#{stuName},#{stuAge},#{gradeName})
    </insert>

    <delete id="deleteStudentByStuNo" parameterType="int">
        delete from student where stuno = #{stuno}
    </delete>

    <update id="updateStudentByStuNo" parameterType="com.santiago.maven.Student">
        update student set stuname = #{stuName}, stuage = #{stuAge}, gradename = #{gradeName} where stuno = #{stuNo}
    </update>

    <select id="queryAll" resultType="com.santiago.maven.Student">
        select * from student
    </select>

修改TestMybatis.java，内容如下

package com.santiago.maven;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;

public class TestMybatis {

    public static void queryStudentByStuNo(){
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        String statement = "com.santiago.maven.StudentMapper.queryStudentByStuNo";
        Student student = session.selectOne(statement, 1);
        System.out.println(student);
        session.close();
    }

    public static void queryAll(){
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        String statement = "com.santiago.maven.StudentMapper.queryAll";
        List<Student> students = session.selectList(statement);
        System.out.println(students);
        session.close();
    }

    public static void addStudent(){
        Student stu = new Student(3,"lc",19,"1");
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        String statement = "com.santiago.maven.StudentMapper.addStudent";
        int count = session.insert(statement, stu);
        //因为采用的是JDBC的方式，所以需要手动提交事务
        session.commit();
        System.out.println("增加了" + count + "名学生" + stu);
        session.close();
    }
    public static void deleteStudentByStuNo(){
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        String statement = "com.santiago.maven.StudentMapper.deleteStudentByStuNo";
        int count = session.delete(statement, 3);
        session.commit();
        System.out.println("删除了" + count + "个学生");
        session.close();
    }

    public static void updateStudentByStuNo(){
        Student stu = new Student(1,"wpl",21,"yansan");
        InputStream stream = Student.class.getClassLoader().getResourceAsStream("conf.xml");
        SqlSessionFactory ssf=new SqlSessionFactoryBuilder().build(stream);
        SqlSession session=ssf.openSession();
        String statement = "com.santiago.maven.StudentMapper.updateStudentByStuNo";
        int count = session.update(statement,stu);
        session.commit();
        System.out.println("修改了" + count + "个学生的信息");
        session.close();
    }

    public static void main(String[] args) {
        queryAll();
        updateStudentByStuNo();
        queryAll();
    }
}