用MyBatis进行数据库的增删改查

前提是MyBatis环境部署好了，参考地址：

https://www.cnblogs.com/package-java/p/10316536.html

为了方便演示，我提前在数据库插入了数据方便查询

1. 创建新的数据库`tedu_ums`；

   CREATE DATABASE tedu_ums;

2. 在新数据库中创建数据表`t_user`，表中至少包含id, username, password, age, phone, email这6个属性；

   USE tedu_ums;

   CREATE TABLE t_user (
       id INT AUTO_INCREMENT,
       username VARCHAR(20) UNIQUE NOT NULL,
       password VARCHAR(20) NOT NULL,
       age INT,
       phone VARCHAR(20),
       email VARCHAR(50),
       PRIMARY KEY(id)
   ) DEFAULT CHARSET=utf8;

3. 添加不少于10条数据；

   INSERT INTO t_user
       (username, password, age, phone, email)
   VALUES
       ('root', '1234', 18, '13800138001', 'root@tedu.cn'),
       ('admin', '4567', 19, '13800138002', 'admin@tedu.cn'),
       ('jack', '1234', 20, '13800138003', 'jack@tedu.cn'),
       ('tom', '1234', 22, '13800138010', 'tom@tedu.cn'),
       ('jerry', '1234', 25, '13800138011', 'jerry@tedu.cn'),
       ('rose', '1234', 21, '13800138004', 'rose@tedu.cn'),
       ('mike', '1234', 22, '13800138005', 'mike@tedu.cn'),
       ('lily', '1234', 23, '13800138006', 'lily@tedu.cn'),
       ('lucy', '1234', 24, '13800138007', 'lucy@tedu.cn'),
       ('mary', '1234', 25, '13800138008', 'mary@tedu.cn'),
       ('alex', '1234', 26, '13800138009', 'alex@tedu.cn');

4. 查询所有数据；

   SELECT id,username,password,age,phone,email FROM t_user;

一、创建实体类

每张数据表都应该有1个对应的实体类，所以，创建`cn.tedu.mybatis.entity.User`类，属性的数量与类型请参考数据表的设计：

package cn.tedu.mybatis.entity;

public class User {

    private Integer id;

    private String username;

    private String password;

    private Integer age;

    private String phone;

    private String email;

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public String getPassword() {

        return password;

    }

    public void setPassword(String password) {

        this.password = password;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    public String getPhone() {

        return phone;

    }

    public void setPhone(String phone) {

        this.phone = phone;

    }

    public String getEmail() {

        return email;

    }

    public void setEmail(String email) {

        this.email = email;

    }

    @Override

    public String toString() {

        return "User [id=" + id + ", username=" + username + ", password=" + password + ", age=" + age + ", phone="

                + phone + ", email=" + email + "]";

    }

}

二、创建接口，声明抽象方法

创建`cn.tedu.mybatis.mapper.UserMapper`接口，并在接口中声明“插入用户数据”的抽象方法：

package cn.tedu.mybatis.mapper;

import cn.tedu.mybatis.entity.User;

public interface UserMapper {

    Integer addnew(User user);//增

    User findById(Integer id);//查

    Integer updatenew(User user);//改

    Integer deletenew(Integer id);//删

}

关于抽象方法，在MyBatis中，执行的操作如果是增、删、改，返回值均使用`Integer`，表示受影响的行数；方法的名称可以自定义，只要不违反Java的命名规则即可，另外，不允许在接口中使用重载机制；参数也可以自定义，如果执行的是增加操作，参数应该是与数据表对应的实体类的类型。

三、配置接口所在的包并配置配置XML文件的位置与数据源

1、在MyBatis中，通过`MapperScannerConfigurer`类扫描持久层接口的，所以，应该在`spring-dao.xml`文件中进行配置：
2、MyBatis通过`SqlSessionFactoryBean`获取数据源，并且扫描配置了SQL语句的XML文件，最终由MyBatis框架来执行SQL语句，所以，需要在`spring-dao.xml`中配置`SqlSessionFactoryBean`：

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"

    xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"

    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"

    xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"

    xmlns:jpa="http://www.springframework.org/schema/data/jpa"

    xsi:schemaLocation="

        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd

        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd

        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd

        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd

        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd

        http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd

        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd

        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd

        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">

    <!-- 加载数据库的配置文件 -->

    <util:properties id="dbConfig" location="classpath:db.properties"></util:properties>

<!--     配置数据源 -->

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

<property name="url" value="#{dbConfig.url}"></property>

<property name="driverClassName" value="#{dbConfig.driver}"></property>

<property name="username" value="#{dbConfig.username}"></property>

<property name="password" value="#{dbConfig.password}"></property>

<property name="initialSize" value="#{dbConfig.initialSize}"></property>

<property name="maxActive" value="#{dbConfig.maxActive}"></property>

</bean>

<!-- 扫描持久层接口 -->

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

<!-- 配置接口文件所在的包 -->

<property name="basePackage" value="cn.tedu.mybatis.mapper"></property>

</bean>

<!-- 或取数据源 -->

<bean class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource"></property>

<property name="mapperLocations" value="classpath:mappers/*.xml"></property>

</bean>

</beans>

四、在XML中配置接口方法对应的SQL语句

在`src/main/resources`下创建名为`mappers`文件夹，创建名为UserMapper.xml的文件

根节点必须是`<mapper>`，且根节点的`namespace`表示对应的接口文件，然后，添加子节点，以对应接口中的抽象方法：

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"

 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<!-- 根节点必须是mapper -->

<!-- namespace对应的接口文件 -->

<mapper namespace="cn.tedu.mybatis.mapper.UserMapper">

    <!-- 根据执行的操作类型选择节点 -->

    <!-- id:对应的抽象方法的方法名 -->

    <!-- 值：#{}括号里面的是User的属性名 -->

    <insert id="addnew">

        INSERT INTO t_user(

        username,password,age,phone,email

        ) value(

        #{username},#{password},#{age},#{phone},#{email}

        )

    </insert>

    <select id="findById" resultType="cn.tedu.mybatis.entity.User">

        SELECT id,username,password,age,phone,email FROM t_user WHERE id=#{id}

    </select>

    <insert id="addnewone">

        INSERT INTO t_user(

        username,password,age,phone,email

        ) value(

        #{username},#{password},#{age},#{phone},#{email}

        )

    </insert>

    <update id="updatenew">

        UPDATE t_user SET

        username=#{username},

        password=#{password},

        age = #{age},

        phone = #{phone},

        email=#{email}

        WHERE

        id=#{id}

    </update>

    <delete id="deletenew">

        DELETE FROM t_user WHERE age=#{age}

    </delete>

</mapper>

五、在测试类中测试

package web;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import org.springframework.context.support.AbstractApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import cn.tedu.mybatis.entity.User;

import cn.tedu.mybatis.mapper.UserMapper;

public class TestDemo01 {

    AbstractApplicationContext ac;

    UserMapper mapper;

    @Before

    public void doBefore() {

        ac = new ClassPathXmlApplicationContext("spring-dao.xml");

        mapper = ac.getBean("userMapper",UserMapper.class);//根据id或取对象

    }

    @After

    public void doafter() {

        ac.close();

    }

/*    测试插入*/

    @Test

    public void add() {

        User user = new User();

        user.setUsername("学海无崖");

        user.setPassword("12345600");

        Integer row = mapper.addnew(user);

        System.out.println("row:"+row);

    }

/*    测试查询*/

    @Test

    public void findById() {

        Integer id=8;

        User user = mapper.findById(id);

        System.out.println(user);

    }

/*    测试修改*/

    @Test

    public void updatenew() {

        User user = new User();

        user.setUsername("小珠佩琦");

        user.setPassword("5548");

        user.setAge(18);

        user.setEmail("44@qq.com");

        user.setPhone("448484");

        user.setId(6);

        Integer num = mapper.updatenew(user);

        System.out.println("num:"+num);

    }

/*    测试删除*/

    @Test

    public void deletenew() {

        Integer age = 60;

        Integer row = mapper.deletenew(age);

        System.out.println("row:"+row);

    }

}

巴特西

用MyBatis进行数据库的增删改查

最新文章

热门文章