Mybatis实现简单增删查改(使用注解)

有了springboot,怎么能少得了mybatis这个好兄弟呢

请注意,本文编写于 187 天前,最后修改于 186 天前,其中某些信息可能已经过时。

框架这种东西,在部署前到处都是坑(特别是对完美主义者来说),但是啊在部署后是真的香

1.工程目录结构

1.1mybatis工程结构

mybatis工程结构
mybatis工程结构

1.2mysql结构

user.sql

drop database if exists demo;
create database demo;
use demo;
drop database if exists demo;
create database demo;
use demo;


/*用户表*/
create table User(
uid varchar(10) primary key,
uname varchar(20) not null,
password varchar(20) not null,
email varchar(50) unique not null,
sSuper int DEFAULT 1,
delmark int DEFAULT 1

);
ALTER TABLE activity.User ADD UNIQUE (uname);

insert into User values("2017764310","自由","a1138312802","1138312802@qq.com");
insert into User values("2017764311","哲学家","a1138312802","3481723658@qq.com");

create table Record(
number varchar(20) not null primary key,
uid varchar(10) not null,
date date not null
);

insert into Record values("1142312","2017764310","2019-12-29");
insert into Record values("1142373","2017764310","2019-12-29");
insert into Record values("1142424","2017764311","2019-12-29");

mysql结构
mysql结构

2.创建工程


然后等待加载项目后创建resource文件夹在main目录下,看第一张图

3.创建实体类在model包

实体类Record.java

package model;

import java.util.Date;
import java.util.List;

/**
 * @ClassName: Record
 * @Description: TODO
 * @author: 陈龙
 * @date: 2019/12/29/029  11:46
 */
public class Record {
    private String number;
    private Date date;
    private String uid;
//    private User user;
//    private List<User> userList;

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

//    public List<User> getUserList() {
//        return userList;
//    }
//
//    public void setUserList(List<User> userList) {
//        this.userList = userList;
//    }
//
//    public User getUser() {
//        return user;
//    }
//
//    public void setUser(User user) {
//        this.user = user;
//    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }



    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @Override
    public String toString() {
        return "Record{" +
                "number='" + number + '\'' +
                ", date=" + date +
                ", uid='" + uid + '\'' +
                '}';
    }
}

实体类user.java

package model;

import java.util.List;

/**
 * @ClassName: User
 * @Description: TODO
 * @author: 陈龙
 * @date: 2019/12/27/027  17:14
 */

public class User {
    private String uid;
    private String uname;
    private String password;
    private String email;
    private Integer sSuper;
    private Integer delmark;
    private List<Record> recordList;



    public User() {
    }

    public User(String uid, String uname, String password, String email, Integer sSuper, Integer delmark) {
        this.uid = uid;
        this.uname = uname;
        this.password = password;
        this.sSuper = sSuper;
        this.email = email;
        this.delmark = delmark;
    }


    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getUid() {
        return uid;
    }

    public void setUid(String uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getsSuper() {
        return sSuper;
    }

    public void setsSuper(Integer sSuper) {
        this.sSuper = sSuper;
    }

    public Integer getDelmark() {
        return delmark;
    }

    public void setDelmark(Integer delmark) {
        this.delmark = delmark;
    }

    public List<Record> getRecordList() {
        return recordList;
    }

    public void setRecordList(List<Record> recordList) {
        this.recordList = recordList;
    }

    @Override
    public String toString() {
        return "User{" +
                "uid='" + uid + '\'' +
                ", uname='" + uname + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                ", sSuper=" + sSuper +
                ", delmark=" + delmark +
                ", recordList=" + recordList +
                '}';
    }
}

4.Dao层

RecordMapper.java

package mybatis.dao;

import model.Record;

import java.util.List;
import java.util.Map;

/**
 * @ClassName: RecordMapper
 * @Description: TODO
 * @author: 陈龙
 * @date: 2019/12/29/029  11:44
 */
public interface RecordMapper {
    List<Map<String, Object>> getUserRecordByUserId(String id);
    List<Record> getAll();
    List<Record> getByUid(String uid);
}

UserMapper.java

使用注解的方式,当调用方法时会调用其上面的方法

package mybatis.dao;

import com.sun.org.apache.xpath.internal.objects.XObject;
import model.Record;
import model.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @ClassName: UserMapper
 * @Description: TODO
 * @author: 陈龙
 * @date: 2019/12/28/028  11:18
 */
public interface UserMapper {
    /*
     * 1.修改Mapper文件中的命名空间
     * 2.将mapper.xml放到与接口同在的包下
     */
//    int insert(User bean);
    /*
        以下形参名称与mapper文件中${参数名}或#{参数名}无关,但是与@Param("名称")有关
        #{}:spEl表达式,推荐使用,功能强大很多。会做类型处理
        ${}:el表达式,只是取值不会做类型处理
     */


    @Select("select * from user")
    List<User> getAllUser();


    @Results(
            id = "userRecord",
            value = {
                    @Result(property = "uid", column = "uid"),
                    @Result(property = "uname", column = "uname"),
                    @Result(property = "recordList", column = "uid", many = @Many(select = "mybatis.dao.RecordMapper.getByUid"))
            }
    )
    @Select("select * from user where uid=#{uid}")
    User getRecordByUid(User bean);

    @Insert("insert into User(uid, uname, password, email) values(#{uid},#{uname},#{password},#{email})")
    int addUser(User bean);

    @Update("update user set email=#{newEmail} where uid=#{uid}")
    int updateEmail(@Param("uid") String uid,@Param("newEmail") String newEmail);
}

5.主函数App.java

package org.example;

import model.Record;
import model.User;
import mybatis.dao.RecordMapper;
import mybatis.dao.UserMapper;
import org.apache.ibatis.io.ResolverUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import tools.DBTool;
import tools.ReflectTool;

import java.io.IOException;
import java.util.*;

/**
 * Hello world!
 */
public class App {
    private SqlSession session = DBTool.getSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    RecordMapper recordMapper = session.getMapper(RecordMapper.class);


    //查找所有用户
    @Test
    public void t1() {
        List<User> users = userMapper.getAllUser();
        System.out.println(users);

    }

    //插入用户表
    @Test
    public void t2() {
        Random random = new Random();
        User bean = new User("20177643" + random.nextInt(100),
                String.valueOf(random.nextInt(100)),
                "0.0",
                String.valueOf(random.nextInt(100)),
                1,
                1);
        System.out.println("插入的用户信息为"+bean);
        int userInsert = userMapper.addUser(bean);
        System.out.println("成功修改" + userInsert + "条记录");
        List<User> users = userMapper.getAllUser();
        System.out.println(users);
        session.commit();
    }

    //更新用户表
    @Test
    public void t3() {
        User bean = new User();
        bean.setUid("2017764310");
        int userUpdate = userMapper.updateEmail(bean.getUid(), "1138312802@qq.com");
        System.out.println("成功修改" + userUpdate + "条记录");
        session.commit();
    }


    //根据id查找订单详细,一对多
    @Test
    public void t4() {
        User bean = new User();
        bean.setUid("2017764310");
        User user = userMapper.getRecordByUid(bean);
        System.out.println(user);


    }


    //查找所有订单
    @Test
    public void t5() {


//      Map<String, Object> detailRecord = recordMapper.getUserRecordByUserId("2017764310");
        List<Record> record = recordMapper.getAll();
        System.out.println(record.get(0));
    }


    public static void main(String[] args) throws IOException {


    }
}

6.tool包

DBTool.java

加载mybatis—config.xml的jdbc配置,在spring*mybatis只用application-contact.xml配置即可

package tools;

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;

/**
 * @ClassName: DBTools
 * @Description: TODO
 * @author: 陈龙
 * @date: 2019/12/27/027  22:30
 */
public class DBTool {
    public static SqlSessionFactory sessionFactory;

    static{
        try {
            //使用MyBatis提供的Resources类加载mybatis的配置文件
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            //构建sqlSession的工厂
            sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    //创建能执行映射文件中sql的sqlSession
    public static SqlSession getSession(){
        return sessionFactory.openSession();
    }

}

7.resources资源配置类文件

RecordMapper.xml

本来是在这里写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">
<mapper namespace="mybatis.dao.RecordMapper">

<!--    一对多表连接查询-->
        <resultMap id="detailRecord" type="record">
            <id property="number" column="number" />
            <result property="date" column="date" />
<!--            <association property="user" javaType="user">-->
<!--                <id property="uid" column="uid" />-->
<!--                <result property="uname" column="uname" />-->
<!--                <result property="password" column="password" />-->
<!--                <result property="email" column="email" />-->
<!--                <result property="sSuper" column="sSuper" />-->
<!--                <result property="delmark" column="delmark" />-->
<!--            </association>-->
            <collection property="userList" column="uid"
            select="mybatis.dao.UserMapper.getById">
            </collection>
        </resultMap>

<!--    <resultMap id="detailRecord" type="record">-->
<!--        <id property="number" column="number"/>-->
<!--        <result property="date" column="date"/>-->
<!--        &lt;!&ndash;发起另一个查询&ndash;&gt;-->
<!--        <association property="user" column="user_id" javaType="user"-->
<!--                     select="mybatis.dao.new1.UserMapper.getById">-->
<!--        </association>-->
<!--    </resultMap>-->

    <select id="getById" resultMap="detailRecord">
        select * from record a,user b
        <where>
            a.uid=b.uid and a.uid=#{uid}
        </where>
    </select>

    <!--    <resultMap id="userRecord" type="map">-->
    <!--        <id column="uid" property="uid"></id>-->
    <!--        <result column="uname" property="uname"/>-->
    <!--        <result column="email" property="email"/>-->
    <!--        <result column="number" property="number"/>-->
    <!--        <result column="date" property="date"/>-->
    <!--    </resultMap>-->

    <select id="getByUid" resultType="model.Record">
        select * from record where uid=#{uid}
    </select>

    <select id="getAll" resultType="model.Record">
        select * from record
    </select>
</mapper>

UserMapper.xml

由于在usermapper.java使用了注解,所以这里大部分都注释掉了

<?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="mybatis.dao.UserMapper">
    <!--
        namespace:名称空间
            比如下面配置了一个select,要使用的话必须通过namespace.id
        select:
            id:该sql命令的获取标识
            resultType:类的全限定名,返回类型
    -->

<!--    <resultMap id="userRecord" type="user">-->
<!--        <id property="uid" column="uid" />-->
<!--        <result property="uname" column="uname" />-->
<!--        <result property="password" column="password" />-->
<!--&lt;!&ndash;        <result property="email" column="email" />&ndash;&gt;-->
<!--&lt;!&ndash;        <result property="sSuper" column="sSuper" />&ndash;&gt;-->
<!--&lt;!&ndash;        <result property="delmark" column="delmark" />&ndash;&gt;-->
<!--        <collection property="recordList" column="uid"-->
<!--                     select="mybatis.dao.RecordMapper.getByUid">-->

<!--        </collection>-->
<!--    </resultMap>-->



<!--    <select id="getAll" resultType="model.User">-->
<!--        select * from user-->
<!--    </select>-->

<!--    <select id="getRecordByUid" resultMap="userRecord">-->
<!--        select * from user where uid=#{uid}-->
<!--    </select>-->

    <select id="getUserById" resultType="user">
        select * from user where uid=#{uid}
    </select>

<!--    <insert id="insertUser" >-->
<!--        insert into user values(#{uid},#{uname},#{password},#{email},#{sSuper},#{delmark})-->
<!--    </insert>-->
<!--    <update id="updateEmail" >-->
<!--        update user set email=#{newEmail} where email=#{oldEmail}-->
<!--    </update>-->
    <delete id="delUser">
        delete user where uname=#{uname}
    </delete>
</mapper>

application-context.xml

用xpring事务配置jdbc连接,在于spring整合时用

<?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:p="http://www.springframework.org/schema/p"
       xmlns:c="http://www.springframework.org/schema/c"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           https://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context
                           https://www.springframework.org/schema/context/spring-context.xsd
                           http://mybatis.org/schema/mybatis-spring
                           http://mybatis.org/schema/mybatis-spring.xsd

http://mybatis.org/schema/mybatis-spring ">

    <!--引入jdbc.properties-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--添加注解支持-->
<!--    <context:annotation-config/>-->
<!--    <context:component-scan base-package="neusoft.service"/>-->
    <!--配置数据源
        1.spring自带
        2.c3p0
        3.DBCP
        4.JNDI:如果应用配置在高性能的应用服务器(如WebLogic或Websphere等)上,
            我们可能更希望使用应用服务器本身提供的数据源。应用服务器的数据源 使用JNDI开放调用者使用,
            Spring为此专门提供引用JNDI资源的JndiObjectFactoryBean类
        5.druid
    -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${user}"/>
        <property name="password" value="${password}"/>
    </bean>
    <!--整合mybatis
            1.使用spring管理mybatis所有组件,比如mapper接口
            记住SqlSessionFactoryBean这个类
            2.使用spring事务处理
    -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
    </bean>
    <!--扫描mapper接口,注意引入命名空间-->
    <mybatis:scan base-package="mybatis.dao"/>
</beans>

jdbc.properties

按照自己配置即可

driver:com.mysql.jdbc.Driver
url:jdbc:mysql://localhost:3306/demo?useUnicode=true&amp;characterEncoding=utf8
user:root
password:123456

log4j.xml

自动生成,具体不知道用来作什么

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="console" class="org.apache.log4j.ConsoleAppender">
        <param name="Target" value="System.out" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="[CateringLog] %d{yyyy-MM-dd HH:mm:ss,SSS} %-5p %c - %m%n" />
        </layout>
    </appender>
    <appender class="org.apache.log4j.RollingFileAppender" name="file">
        <param name="File" value="d:/companyProject/logs/catering.log" />
        <param name="Append" value="true" />
        <param name="MaxFileSize" value="1024KB" />
        <param name="MaxBackupIndex" value="5" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="[CateringLog] %d{yyyy-MM-dd HH:mm:ss,SSS} %-5p %c - %m%n" />
        </layout>
    </appender>
    <root>
        <priority value="debug" />
        <appender-ref ref="console" />
        <appender-ref ref="file" />
    </root>
    <!--通过<logger></logger>的定义可以将各个包中的类日志输出到不同的日志文件中-->
    <logger name="org.springframework">
        <level value="ERROR" />
    </logger>
    <logger name="org.mybatis.spring">
        <level value="ERROR" />
    </logger>
    <logger name="net.sf.ehcache">
        <level value="ERROR" />
    </logger>
    <logger name="com.mchange.v2">
        <level value="ERROR" />
    </logger>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
</log4j:configuration>

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>
    <properties resource="jdbc.properties"></properties>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
<!--        <typeAlias type="model.Record" alias="record"></typeAlias>-->
<!--        用在mapper类的别名下,可以直接用实体类名称代替类名.java-->
        <package name="model"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        <mapper resource="mybatis/dao/UserMapper.xml"/>-->
<!--        <mapper resource="mybatis/dao/RecordMapper.xml"/>-->
<!--        加载mybatis.dao下面的所有mapper类,注意要用斜杠-->
        <package name="mybatis/dao"/>
    </mappers>
</configuration>

8.结果图

首先获得连接并getmapper

    private SqlSession session = DBTool.getSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    RecordMapper recordMapper = session.getMapper(RecordMapper.class);

按照注解@Test一个模块一个模块实现

点击运行即可
点击运行即可

t1():查找所有用户

    @Test
    public void t1() {
        List<User> users = userMapper.getAllUser();
        System.out.println(users);

    }

查找所有用户
查找所有用户

t2():用户表插入一条记录

   //插入用户表
    @Test
    public void t2() {
        Random random = new Random();
        User bean = new User("20177643" + random.nextInt(100),
                String.valueOf(random.nextInt(100)),
                "0.0",
                String.valueOf(random.nextInt(100)),
                1,
                1);
        System.out.println("插入的用户信息为"+bean);
        int userInsert = userMapper.addUser(bean);
        System.out.println("成功修改" + userInsert + "条记录");
        List<User> users = userMapper.getAllUser();
        System.out.println(users);
        session.commit();
    }

插入一条记录
插入一条记录

t3():根据uid更新用户表邮箱

    //更新用户表
    @Test
    public void t3() {
        User bean = new User();
        bean.setUid("2017764310");
        int userUpdate = userMapper.updateEmail(bean.getUid(), "99999999@qq.com");
        System.out.println("成功修改" + userUpdate + "条记录");
        session.commit();
    }

根据用户uid更新邮箱
根据用户uid更新邮箱

t4():根据uid查找某个用户订单详细,一对多

//根据uid查找某个用户订单详细,一对多
       @Test
    public void t4() {
        User bean = new User();
        bean.setUid("2017764310");
        User user = userMapper.getRecordByUid(bean);
        System.out.println(user);


    }

根据uid查找某个用户订单详细
根据uid查找某个用户订单详细

9.参考资料

  1. mybatis文档
  2. maven repoistory

添加新评论

评论列表