MyBatis首次使用并查询数据库中表的数据

MyBatis首次使用并查询数据库中表的数据

1、传统Jdbc代码问题分析

public class JdbcTest {
    public static void main(String[] args) {
        try {
            //1.加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接对象
            String url = "jdbc:mysql://192.168.10.137:3306/ssm";
            Connection connection = DriverManager.getConnection(url, 
                                                                "root",
                                                                "1111");
            String sql = "select * from account";
            //3.准备PreparedStatement对象
            PreparedStatement pst = connection.prepareStatement(sql);
            //4.执行sql语句
            ResultSet rs = pst.executeQuery();
            //5.遍历结果集
            while(rs.next()){
                Integer id = rs.getInt("id");
                String name = rs.getString("name");
                Double money = rs.getDouble("money");
                System.out.println(id + " " + name + " " + money);
            }
            //6.关闭资源
            rs.close();
            pst.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

传统JDBC存在的问题

1. 手动创建和释放链接

2. sql语句在代码中硬编码

3. 对结果的解析

2、MyBatis框架概述

MyBatis是一个优秀的持久层框架，它对jdbc的操作数据库的过程进行封装，使开发者只需要关注 SQL 本身，而不需要花费精力去处理例如注册驱动、创建connection、创建statement、手动设置参数、获取结果集等jdbc繁杂的过程代码。

? MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis。iBatis一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。

3、MyBatis入门案例

3.1、使用idea创建一个maven项目后，修改pom.xml文件中内容为：

<properties>
        <!-- 项目源码及编译输出的编码 -->
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <!-- 项目编译JDK版本 -->
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
    <build>
        <!-- 如果不添加此节点src/main目录下的所有配置文件都会被漏掉。 -->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
    </build>

3.2、在src-main-resources下创建log4j.properties和mybatis-config.xml两个配置文件

log4j.properties内容如下：

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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>
    <!--使用dev环境-->
    <environments default="dev">
        <!--dev环境-->
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <!--使用连接池中的数据源-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
    <!-- 扫描映射文件(一定要按自己的路径填写) -->
    <mappers>
        <mapper resource="cn/fpl1116/dao/UserDao.xml"/>
    </mappers>
</configuration>

3.3、创建Java代码，在自定义包下创建dao包和pojo包，dao包下创建UserDao.java和UserDao.xml，pojo包下创建User.java

UserDao.java内容如下：

package cn.fpl1116.dao;


import cn.fpl1116.pojo.User;

import java.util.List;

public interface UserDao {
    List<User> findAll();
}

UserDao.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">
<!--namespace：唯一，接口全类名-->
<mapper namespace="cn.fpl1116.dao.UserDao">
    <!--
        id：和接口方法名保持一致
        resultType：和接口返回类型保持一致
    -->
    <select id="findAll" resultType="cn.fpl1116.pojo.User">
        select * from user
    </select>
</mapper>

User.java内容如下:

package cn.fpl1116.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

3.4、在test下创建测试类MyBatisTest.java

内容如下：

package cn.fpl1116.test;

import cn.fpl1116.dao.UserDao;
import cn.fpl1116.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

public class MyBatisTest {

    private InputStream inputStream;
    private SqlSession sqlSession;

    @Before
    public void createSqlSession() throws IOException {
        // 加载配置文件
        String resource = "mybatis-config.xml";
        inputStream = Resources.getResourceAsStream(resource);

        // 创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(inputStream);
        //获得数据库会话实例
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void testFindAll(){
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.findAll();
        for(User user : userList) {
            System.out.println(user);
        }
    }

    @After
    public void closeSqlSession() throws IOException {
        sqlSession.close();
        inputStream.close();
    }
}

3.5、创建好的项目结构如下

4、创建对应的数据库mybatis

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50732
Source Host           : localhost:3306
Source Database       : mybatis

Target Server Type    : MYSQL
Target Server Version : 50732
File Encoding         : 65001

Date: 2021-12-24 11:51:18
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL COMMENT '编号',
  `uid` int(11) DEFAULT NULL COMMENT '用户编号',
  `money` double DEFAULT NULL COMMENT '金额',
  PRIMARY KEY (`id`),
  KEY `FK_Reference_8` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('1', '41', '1000');
INSERT INTO `account` VALUES ('2', '45', '1000');
INSERT INTO `account` VALUES ('3', '41', '2000');

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `ROLE_NAME` varchar(30) DEFAULT NULL COMMENT '角色名称',
  `ROLE_DESC` varchar(60) DEFAULT NULL COMMENT '角色描述',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '院长', '管理整个学院');
INSERT INTO `role` VALUES ('2', '总裁', '管理整个公司');
INSERT INTO `role` VALUES ('3', '校长', '管理整个学校');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `password` varchar(20) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('41', '张三丰', '111', '2018-02-27 17:47:08', '男', '上海徐汇');
INSERT INTO `user` VALUES ('42', '宋远桥', '111', '2018-03-02 15:09:37', '女', '北京昌平');
INSERT INTO `user` VALUES ('43', '俞莲舟', '111', '2018-03-04 11:34:34', '女', '陕西西安');
INSERT INTO `user` VALUES ('45', '张翠山', '111', '2018-03-04 12:04:06', '男', '山东济南');
INSERT INTO `user` VALUES ('46', '殷梨亭', '111', '2018-03-07 17:37:26', '男', '河北张家口');
INSERT INTO `user` VALUES ('48', '莫声谷', '111', '2018-03-08 11:44:00', '女', '山西太原');

-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES ('1', '45', '1');
INSERT INTO `user_role` VALUES ('2', '41', '1');
INSERT INTO `user_role` VALUES ('3', '45', '2');

5、修改好自己数据库的账号和密码后，运行测试类结果如下: