在idea上用MyBatis写一套增删改查 多表联查一对一

pom.xml

---

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

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.kgc.kjde1036.zym</groupId>
    <artifactId>day123</artifactId>
    <version>1.0-SNAPSHOT</version>

    <name>day123</name>
    <!-- FIXME change it to the project's website -->
    <url>http://www.example.com</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>

        <!--添加lombok依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

        <!-- mybatis驱动 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>

        <!--  添加mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

        <!--  logger依赖依赖-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>


    </dependencies>
    <build>
        <resources>
            <resource>

                <directory>src\main\java</directory><!--所在的目录-->
                <includes>
                    <!--包括目录下的.properties或者.xml文件都会扫描到-->
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>
</project>

 resources

---

 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>
    <!--禁止自动匹配-->
    <!-- 第二中方法禁止自动匹配固定的-->
    <!--    <settings>-->
    <!--        <setting name="autoMappingBehavior" value="NONE"/>-->
    <!--    </settings>-->

    <!--    &lt;!&ndash;    起别名&ndash;&gt;-->


    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <typeAliases>
        <typeAlias type="org.pojo.User" alias="User"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://182.2.1:3306/smbms?zeroDateTimeBehavior=convertToNull&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!--    注册-->
    <mappers>
        <mapper resource="org\mapper\StubentMapper.xml"/>
    </mappers>
</configuration>

log4j.propertoies

网上找得到的

### 设置###
log4j.rootLogger = debug,stdout,D,E

### 输出信息到控制抬 ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### 输出DEBUG 级别以上的日志到=./logs/log.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = ./logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG 
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### 输出ERROR 级别以上的日志到=./logs/log.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =./logs/log.log 
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR 
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

 Utils层

---

 MyBatis Utils

静态代码块

获取connfig2.xml的输入流 也就是上面xml的

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

/**
 * @author
 * @date：2021/12/13
 * @description：
 */
public class MyBatisUtils {

    //静态方式  并不是最佳解决方案 最佳方式Spring
    //静态代码块 自己管理 来管理生命周期
    private static SqlSessionFactory factory;

    static {

        try {
            //获取mybatis-config.xml的输入流
            InputStream is = Resources.getResourceAsStream("config2.xml");
            //创建SqkSessionFactory对象来完成配置文件读取
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession createSqlSession() {
        return factory.openSession(false);//true 为自动提交事务}

    }

    public static void closeSqlSession(SqlSession sqlSession) {
        if (null != sqlSession) {
            sqlSession.close();
        }
    }

pojo 

---

实体类 要和数据库的一致

user

package org.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @author
 * @date：2021/12/14
 * @description：
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User {
    private int id;
    private String userCode;
    private String userName;
    private String userPassword;
    private int gender;
    private Date birthday;
    private String phone;
    private String address;
    /*外键“角色ID”*/
    private int userRole;
    private long createdBy;
    private Date creationDate;
    private long modifyBy;
    private Date modifyDate;

}

dao层 接口

---

package org.dao;

import org.apache.ibatis.annotations.Param;
import org.pojo.User;

import java.util.List;

/**
 * @author
 * @date：2021/12/17
 * @description：
 */
public interface UserDao {
        /**
         * 根据id查询一个
         * @param id
         * @return
         */
        User selOne(int id);
        /**
         * 查询全部
         * @return
         */
        List<User> seltow();
        /**
         * 添加
         * @param sut
         * @return
         */
        int add(User sut);
        /**
         * 删除
         * @param id
         * @return
         */

        int delete(int id);
        /**
         * 修改
         * @param id
         * @param userName
         * @return
         */
        int updSut(@Param("id") int id , @Param("userName") String userName);


    }

 

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 namespace="org.dao.UserDao">
    <!-- 查询单个-->
    <!-- smbms_user 是表名 -->
    <select id="selOne" resultType="org.pojo.User">
        select * from smbms_user where id=#{id}
    </select>
    <!--查询全部-->
    <select id="seltow" resultType="org.pojo.User">
        select * from smbms_user
    </select>

    <!--添加-->
    <insert id="add" parameterType="org.pojo.User">
        insert smbms_user(userCode,userName,userPassword,gender,birthday,phone,address)
        value(#{userCode},#{userName},#{userPassword},#{gender},#{birthday},#{phone},#{address})
    </insert>

    <!--    删除-->
    <delete id="delete">
        delete from smbms_user where id = #{id}
    </delete>
    <!--   修改-->
    <!-- 下面数据和实体类对应 也和接口对应-->
    <update id="updSut">
        update smbms_user set userName=#{userName} where id=#{id}
    </update>

测试类 

---

package org.example;

import org.apache.ibatis.session.SqlSession;

import org.apache.log4j.Logger;
import org.dao.UserDao;
import org.junit.Test;
import org.pojo.User;
import org.utils.MyBatisUtils;

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

/**
 * @author
 * @date：2021/12/17
 * @description：
 */
public class StudentTest {
    Logger logger = Logger.getLogger(StudentTest.class);

    //查询单条数据
    @Test
    public void testStudent() {

        SqlSession session = MyBatisUtils.createSqlSession();

        UserDao mapper = session.getMapper(UserDao.class);
        User UserDao = mapper.selOne(2);
        System.out.println(UserDao.toString());
        MyBatisUtils.closeSqlSession(session);

    }

    //查看全部
    @Test
    public void testStudent1() {
        SqlSession session = MyBatisUtils.createSqlSession();

        UserDao mapper = session.getMapper(UserDao.class);
        List<User> studentList = mapper.seltow();

        for (User student : studentList)
            System.out.println(student);

        MyBatisUtils.closeSqlSession(session);


    }

    @Test
    //修改
    public void testStudent2() {
        SqlSession session = MyBatisUtils.createSqlSession();
        //Alt+回车
        UserDao mapper = session.getMapper(UserDao.class);

        int r = mapper.updSut(1, "李明");
        logger.debug("r");

    }

    @Test
    // 删除
    public void testStudent3() {
        SqlSession session = MyBatisUtils.createSqlSession();
        UserDao mapper = session.getMapper(UserDao.class);
        int r = mapper.delete(5);
        logger.debug("r");

    }

    @Test
    //添加
    public void testStudent4() {
        SqlSession session = MyBatisUtils.createSqlSession();//打开链接
        UserDao mapper = session.getMapper(UserDao.class);
        User stu = new User();
        stu.setUserCode("wangwu");
        stu.setUserName("王五");
        stu.setUserPassword("0000000");
        stu.setGender(2);
        stu.setBirthday(Date.valueOf("1983-12-10"));
        stu.setPhone("13367890902");
        stu.setAddress("北京市东城区前门东大街12号");
        int r = mapper.add(stu);
        logger.debug("r");
        session.close();
    }

多表联查一对一

接口

     /**
         * 多表联查（推荐用法）
         * @return
         */
        List<User> search();

    }

Mappper.xml 

<!-- 多表联查一对一-->
    <!--resulMap 可以封装结果集 ID属性 唯一标识，type属性实体类名：-->
    <resultMap id="UserList" type="User">
        <!--用户编码 ，用户姓名 address，用户角色 年龄-->
        <!-- resul标签中的column属性：对应数据库表明：property对应实体类-->
        <!-- 对应的是数据库查什么数据就些什么数据名上去-->
        <result column="userCodev" property="userCode"/>
        <result column="userName" property="userName"/>
        <result column="address" property="address"/>
        <result column="roleName" property="roleName"/>
        <result column="birthday" property="birthday"/>
    </resultMap>
    <!--    select标签中的resultMap属性值对应上面标签的id值 一点都不能错  强烈建议复制粘贴-->
    <select id="search" resultMap="UserList">
        select u.*,roleName
        from `smbms_user` u, `smbms_role` r
        where u.userRole=r.id;

    </select>
</mapper>

测试类 

 

    //多表联查一对一
    @Test
    public void test() {
        SqlSession SqlSession = MyBatisUtils.createSqlSession();
        //快捷键Ait +回车
        UserDao mapper = SqlSession.getMapper(UserDao.class);
        List<User> userList = mapper.search();
        for (User user : userList) {
            logger.debug(user);
        }
        MyBatisUtils.closeSqlSession(SqlSession);

    }

}