MyBatis_Study_002(进阶,增删改查)

源码:https://github.com/carryLess/mbtsstd-002.git

1.主配置文件

<?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">

<!-- (以上)文件头在解压的文件夹中mybatis-3.4.4.pdf文件中搜索mybatis-3-config.dtd即可得到 -->

<configuration>

    <!-- 指定属性配置文件 -->

    <properties resource="jdbc.properties" />

    <!--

        配置类的别名,我建议使用package这种写法

        这样写会将该包中所有类的简单类名配置为别名,简单方便

        ,还有别的写法,自行google

     -->

    <typeAliases>

        <package name="model" />

    </typeAliases>

    <!-- 配置MyBatis运行环境 -->

    <environments default="development">

        <environment id="development">

            <!-- 使用JDBC事务管理 -->

            <transactionManager type="JDBC"/>

            <dataSource type="POOLED">

                <property name="driver" value="${jdbc.driver}"/>

                <property name="url" value="${jdbc.url}"/>

                <property name="username" value="${jdbc.username}"/>

                <property name="password" value="${jdbc.password}"/>

            </dataSource>

        </environment>

    </environments>

    <!-- 注册映射文件 -->

    <mappers>

        <mapper resource="dao/mapper.xml"/>

        <!--

            实际开发中可能有多个映射文件,而其中sql标签的id相同时候,执行过程就会报错

            我们可以根据mapper映射文件中的namespace属性来区分,调用时候用如下方式

            namespace.id

        -->

        <!--

            <mapper resource="dao/mapper2.xml"/>

        -->

    </mappers>

</configuration>

2.jdbc.properties配置文件

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/test

jdbc.username=root

jdbc.password=root

3.sql映射文件

<?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">

<!-- 文件头在解压的文件夹中mybatis-3.4.4.pdf文件中搜索mybatis-3-mapper.dtd即可得到 -->

<mapper namespace="model.Student">

    <!-- parameterType属性,框架会根据SQLSession中传递的参数检测到,所以我们一般不用指定 -->

    <insert id="insertStudent" parameterType="model.Student">

        insert into student(name,age,score)

        values(#{name},#{age},#{score})

    </insert>

    <!--

        resultType:获取的主键的类型

        keyProperty:指出主键在java实体类中的属性名称

     -->

    <insert id="insertStudentCatchId">

        insert into student(name,age,score)

        values(#{name},#{age},#{score})

        <selectKey resultType="int" keyProperty="id" order="AFTER">

            select @@IDENTITY

            <!-- select last_insert_id(); -->

        </selectKey>

    </insert>

    <delete id="deletById">

        <!-- 这个#{xxx}是占位符,为delete方法的第二个参数,xxx随意填写 -->

        delete from student where id = #{xxx}

    </delete>

    <update id="updateStudent">

        <!-- #{}占位符,里面字符串与实体类的属性名称一致 -->

        update student set name = #{name},age=#{age},score=#{score} where id = #{id}

    </update>

    <!--

        resultType指定返回的结果集中每一条记录封装的对象的类型

        这里的Student是配置的别名(主配置文件中配置),如果没有配置别名,可以写全限定类名

     -->

    <select id="selectAll" resultType="Student">

        select * from student

    </select>

    <select id="selectOne" resultType="Student">

        <!-- 这个#{xxx}是占位符,xxx随意填写 -->

        select * from student where id = #{xxx}

    </select>

    <select id="selectByName" resultType="Student">

        <!-- 模糊查询 -->

        select * from student

        where name like concat('%',#{xxx},'%')

        <!--

            或者写成如下形式

            where name like '%' #{xxx} '%'

        -->

        <!--

            上两种拼接参数会动态的传入sql中,还有以下写法是纯sql拼接,可能引起sql注入

            必须写成如下形式  '%${value}%'

            where name like '%${value}%'

        -->

    </select>

    <select id="selectByMap1" resultType="Student">

        select * from student where age &gt; #{age}

    </select>

    <!-- 查询返回简单类型 -->

    <select id="selectRowCount" resultType="Integer">

        select count(1) from student

    </select>

</mapper>

4.dao接口

package dao;

import model.Student;

import java.util.List;

import java.util.Map;

/**

 * Created by carryLess on 2017/11/29.

 */

public interface IStudentDao {

    /**

     * 插入数据

     * @param student

     */

    void insertStudent(Student student);

    /**

     * 插入数据 获取id

     * @param student

     */

    void insertStudentCatchId(Student student);

    /**

     * 根据id 删除数据

     * @param id

     */

    int deletById(int id);

    /**

     * 修改数据 传入model

     * @param student

     */

    void updateStudent(Student student);

    /**

     * 查询返回所有数据的集合

     * @return

     */

    List<Student> selectAll();

    /**

     * 查询返回map

     * @return

     */

    Map<String,Student> selectMap();

    /**

     * 根据id查询实体类

     * @param id

     * @return

     */

    Student selectById(Integer id);

    /**

     * 根据姓名查询

     * @param name

     * @return

     */

    List<Student> selectByName(String name);

    /**

     * 传递map为参数进行查询

     * @param map

     * @return

     */

    List<Student> selectByMap(Map<String,Object> map);

    /**

     * 返回简单类型 Integer

     * @return

     */

    Integer selectRowCount();

}

5.dao实现类

package dao;

import model.Student;

import org.apache.ibatis.session.SqlSession;

import utils.MyBatisUtils;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

/**

 * Created by carryLess on 2017/11/29.

 */

public class StudentDaoImpl implements IStudentDao {

    private SqlSession sqlSession;

    @Override

    public void insertStudent(Student student) {

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            sqlSession.insert("insertStudent",student);

            sqlSession.commit();

        }finally {

            //关闭sqlSession

            if(sqlSession != null){

                sqlSession.close();

            }

        }

    }

    @Override

    public void insertStudentCatchId(Student student) {

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            sqlSession.insert("insertStudentCatchId",student);

            sqlSession.commit();

        }finally {

            //关闭sqlSession

            if(sqlSession != null){

                sqlSession.close();

            }

        }

    }

    @Override

    public int deletById(int id) {

        int opCount;

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            opCount = sqlSession.delete("deletById",id);

            sqlSession.commit();

        }finally {

            //关闭sqlSession

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return opCount;

    }

    @Override

    public void updateStudent(Student student) {

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            sqlSession.update("updateStudent",student);

            sqlSession.commit();

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

    }

    @Override

    public List<Student> selectAll() {

        List<Student> selectAll = new ArrayList<>();

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            selectAll = sqlSession.selectList("selectAll");

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return selectAll;

    }

    @Override

    public Map<String, Student> selectMap() {

        Map<String,Student> sMap;

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            /*第一个参数时映射文件中sql标签的id,第二个参数指定字段名称,以此为map的key*/

            sMap = sqlSession.selectMap("selectAll", "id");

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return sMap;

    }

    @Override

    public Student selectById(Integer id) {

        Student student;

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            student = sqlSession.selectOne("selectOne",id);

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return student;

    }

    @Override

    public List<Student> selectByName(String name) {

        List<Student> students;

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            students = sqlSession.selectList("selectByName",name);

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return students;

    }

    @Override

    public List<Student> selectByMap(Map<String, Object> map) {

        List<Student> students;

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            students = sqlSession.selectList("selectByMap1",map);

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return students;

    }

    @Override

    public Integer selectRowCount() {

        Integer rowCount;

        try {

            sqlSession = MyBatisUtils.getSqlSession();

            rowCount = sqlSession.selectOne("selectRowCount");

        }finally {

            if(sqlSession != null){

                sqlSession.close();

            }

        }

        return rowCount;

    }

}

6.实体类

package model;

/**

 * Created by carryLess on 2017/11/29.

 */

public class Student {

    private Integer id;

    private String name;

    private Integer age;

    private double score;

    public Student() {

    }

    public Student(String name, Integer age, double score) {

        this.name = name;

        this.age = age;

        this.score = score;

    }

    @Override

    public String toString() {

        return "Student{" +

                "id=" + id +

                ", name='" + name + '\'' +

                ", age=" + age +

                ", score=" + score +

                '}';

    }

    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 double getScore() {

        return score;

    }

    public void setScore(double score) {

        this.score = score;

    }

}

7.工具类

package 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.IOException;

import java.io.InputStream;

/**

 * Created by carryLess on 2017/11/30.

 */

public class MyBatisUtils {

    /*

    * SqlSession 由SqlSessionFactory对象创建,

    * 而SqlSessionFactory对象为重量级对象

    * 并且是线程安全的,所以我们将其设为单例

    * */

    private static SqlSessionFactory factory;

    /**

     * 私有化构造方法,避免该工具类在外部被实例化

     */

    private MyBatisUtils(){}

    /**

     * 获取 SqlSession

     * @return

     */

    public static SqlSession getSqlSession(){

        try {

            if(factory == null){

                //读取配置文件

                InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");

                //创建工厂类

                factory = new SqlSessionFactoryBuilder().build(inputStream);

            }

        } catch (IOException e) {

            e.printStackTrace();

            return null;

        }

        /*

        * factory.openSession(true);     创建一个有自动提交功能的SqlSession

        * factory.openSession(false);    创建一个没有自动提交功能的SqlSession,需要手动提交

        * factory.openSession();         同factory.openSession(false);

        */

        return factory.openSession();

    }

}

8.测试类

package test;

import dao.IStudentDao;

import dao.StudentDaoImpl;

import model.Student;

import org.junit.Before;

import org.junit.Test;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

 * Created by carryLess on 2017/11/29.

 */

public class MyTest {

    private IStudentDao dao;

    @Before

    public void initDao(){

        dao = new StudentDaoImpl();

    }

    @Test

    public void insertTest(){

        Student student = new Student("张三",18,60.0);

        IStudentDao dao = new StudentDaoImpl();

        dao.insertStudent(student);

    }

    @Test

    public void insertCatchIdTest(){

        Student student = new Student("李四",14,34.3);

        System.out.println(student);

        dao.insertStudentCatchId(student);

        System.out.println(student);

    }

    @Test

    public void testDeletById(){

       dao.deletById(8);

    }

    @Test

    public void testUpdate(){

        Student student = new Student("wangmazi",24,54);

        student.setId(2);

        dao.updateStudent(student);

    }

    @Test

    public void testSelectAll(){

        List<Student> students = dao.selectAll();

        System.out.println(students);

    }

    @Test

    public void testSelectMap(){

        Map<String, Student> selectMap = dao.selectMap();

        System.out.println(selectMap);

    }

    @Test

    public void testSelectOne(){

        Student student = dao.selectById(3);

        System.out.println(student);

    }

    @Test

    public void testSelectByNames(){

        List<Student> students = dao.selectByName("w");

        System.out.println(students);

    }

    @Test

    public void testSelectByMap(){

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

        map.put("age",10);

        List<Student> students = dao.selectByMap(map);

        System.out.println(students);

    }

    @Test

    public void testSelectRowCount(){

        System.out.println(dao.selectRowCount());

    }

}

9.建表sql

CREATE TABLE `student` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) DEFAULT NULL,

  `age` int(3) DEFAULT NULL,

  `score` double DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

巴特西

MyBatis_Study_002(进阶,增删改查)

最新文章

热门文章