MYBatis工程搭建并实现Mapper配置查询数据

创建项目

该项目目录下图所示

spring基础包：

<dependencies>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>5.2.13.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-beans</artifactId>
        <version>5.2.13.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.2.13.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-expression</artifactId>
        <version>5.2.13.RELEASE</version>
    </dependency>
</dependencies>

刷新maven等待自动下载

核心配置文件

文件路径为\src\main\resources文件名为applicationContext.xml

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

    <!-- bean definitions here -->

</beans>

编写代码测试

接口类

新建接口类
Java、右键新建service.UserService 接口：Interface

package services;

public interface UserService {
    public void saveUser();
}

新建实现类

package services.impl;

import services.UserService;
public class UserServiceImpl implements UserService {
    public void saveUser() {
        System.out.println("service的save方法执行了");
    }
}

补充配置文件

将我们自定义的实现类交给Spring的容器管理

<!-- 此标签的作用是利用反射机制将UserServiceImpl类的实例交给Spring容器 -->
<bean id="userService" class="services.impl.UserServiceImpl"/>

测试类

新建测试类

public class DemoTest {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
        UserService service = (UserService) context.getBean("userService");
        service.saveUser();
    }
}

如何使用MyBatis连接数据库

配置MyBatis
1.使用IDEA创建Maven工程
2.IDAE配置Maven
准备数据源

# 删除mybatis_demo数据库
drop database if exists mybatis_demo;

# 创建mybatis_demo数据库
create database mybatis_demo;

# 使用mybatis_demo数据库
use mybatis_demo;

# 创建account表
create table user (
    id int auto_increment primary key,
    username varchar(20),
    age int,
    score int
);

# 新增数据
insert into user (id, username, age, score) values
(1,'peter', 18, 100), (2,'pedro', 24, 200),
(3,'jerry', 28, 500), (4,'mike', 12, 300),
(5,'tom', 27, 1000);

JDBC连接及操作数据库 引入MySQL驱动包

<dependencies>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
    </dependency>
</dependencies>

刷新maven等待自动下载
代码实现

public class JDBCDemo {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis_demo", "root", "root");
        //3.获取Statement对象
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user WHERE id = ?");
        preparedStatement.setInt(1, 1);
        //4.执行SQL语句返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //5.遍历结果集
        while (resultSet.next()) {
            System.out.println("username: " + resultSet.getString("username"));
            System.out.println("age: " + resultSet.getString("age"));
        }
        //6.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

执行结果

引入MyBatis依赖
mybatis：MyBatis基础包
logback-classic：日志依赖
分别导入这两个包

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/ch.qos.logback/logback-classic -->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.3.0-alpha5</version>
    <scope>test</scope>
</dependency>

刷新maven等待自动下载
代码实现
新建编程式配置文件：StartNoXml.java

@SuppressWarnings({"SqlResolve", "SqlNoDataSourceInspection", "Duplicates"})
public class StartNoXml {
    public static void main(String[] args) throws SQLException {
        // 准备jdbc事务类
        JdbcTransactionFactory jdbcTransactionFactory = new JdbcTransactionFactory();
        // 配置数据源
        PooledDataSource dataSource = new PooledDataSource(
                "com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/mybatis_demo?useSSL=false",
                "root",
                "root");
        // 配置环境，向环境中指定环境id、事务和数据源
        Environment environment = new Environment.Builder("development")
                .transactionFactory(jdbcTransactionFactory)
                .dataSource(dataSource).build();
        // 新建 MyBatis 配置类
        Configuration configuration = new Configuration(environment);
        // 得到 SqlSessionFactory 核心类
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
        // 开始一个 sql 会话
        SqlSession session = sqlSessionFactory.openSession();
        // 得到 sql 连接并运行 sql 语句
        PreparedStatement preStatement = session
                .getConnection()
                .prepareStatement("SELECT * FROM user WHERE id = ?");
        preStatement.setInt(1, 1);
        ResultSet result = preStatement.executeQuery();
        // 验证结果
        while (result.next()) {
            System.out.println("username: " + result.getString("username"));
            System.out.println("age: " + result.getString("age"));
        }
        // 关闭会话
        session.close();
    }
}

MyBatis实现Mapper配置并查询数据

什么是Mapper

 1. 通过SqlSession.getMapper会返回一个mapper的代理类MapperProxy，当调用mapper中的方法的时候，实际上是调用了代理类MapperProxy的invoke方法，
    在invoke方法中会使用sqlSession进行数据库的操作，当然sqlSession并不直接操作数据库，而是由Executor和数据库打交道。
    
 2. 通过调用Java方法来执行SQL语句；

准备数据源
第一步明白 MyBatis连接数据
准备数据库语句

# 删除mybatis_demo数据库
drop database if exists mybatis_demo;

# 创建mybatis_demo数据库
create database mybatis_demo;

# 使用mybatis_demo数据库
use mybatis_demo;

# 创建account表
create table user (
    id int auto_increment primary key,
    username varchar(20),
    age int,
    score int
);

# 新增数据
insert into user (id, username, age, score) values
(1,'peter', 18, 100), (2,'pedro', 24, 200),
(3,'jerry', 28, 500), (4,'mike', 12, 300),
(5,'tom', 27, 1000);

注解方式使用Mapper
在mybatis-config.xml配置文件中输入

<!-- mapper配置 -->
<mappers>
    <mapper class="mapper.UserMapper"/>
</mappers>

新建mapper包，并在其下新建UserMapper.java类
User模块Mapper层：UserMapper.java

package mapper;

public interface UserMapper {
    /**
     * 通过用户id查询用户名称
     *
     * @param id 用户id
     * @return 用户名称
     */
    String selectUsernameById(Integer id);
    
@Select("SELECT username FROM user WHERE id = #{id}")

}
User模块测试类：UserTest.java

```java
@SuppressWarnings({"Duplicates"})
public class UserTest {
    public static void main(String[] args) throws IOException, SQLException {
        // 读取配置文件
        InputStream configuration = Resources.getResourceAsStream("mybatis-config.xml");
        // 得到 SqlSessionFactory 核心类
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
        // 开始一个 sql 会话
        SqlSession session = sqlSessionFactory.openSession();
        // 得到 mapper
        UserMapper mapper = session.getMapper(UserMapper.class);
        // 调用注解的SQL
        String username = mapper.selectUsernameById(1);
        System.out.println("username: " + username);
        // 关闭会话
        session.close();
    }
}

以上完成整个配置操作
XML方式使用Mapper
代码实现
因为我们在mybatis-config.xml配置文件中已经添加过了mapper配置，所以说不需要在配置文件中添加额外的配置

在 UserMapper 接口中，我们再新增一个方法selectUserAgeById，该方法的作用是通过用户id查询用户年龄

User模块Mapper层：UserMapper.java

/**
 * 通过用户id查询用户年龄
 *
 * @param id 用户id
 * @return 用户年龄
 */
Integer selectUserAgeById(Integer id);

这次使用XML的方式添加SQL语句，首先在资源文件夹下新建mapper包，并在其下新建名为UserMapper的XML文件

User模块Mapper层配置文件：UserMapper.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="mapper.UserMapper">
</mapper>

xml文件中添加

<select id="selectUserAgeById" resultType="java.lang.Integer">
    SELECT age FROM user WHERE id = #{id}
</select>

User模块测试类：UserTest.java

// 调用XML的SQL
Integer age = mapper.selectUserAgeById(1);
System.out.println("age: " + age);

select-resultType

代码实现
在UserMapper.xml文件中

<select id="selectUserById" resultType="">
    SELECT * FROM user WHERE id = #{id}
</select>

在entity包下创建User实体类：User.java

package entity;

public class User {
    private Integer id;
    private String username;
    private Integer age;
    private Integer score;
    // 省略getter&setter方法
    // 省略toString方法
}

把UserMapper.xml文件中selectUserById标签的返回类型改为上面创建的实体类

resultType="entity.User"

User模块Mapper层：UserMapper.java

/**
 * 通过用户id查询用户信息
 *
 * @param id
 * @return
 */
User selectUserById(Integer id);

User模块测试类：UserTest.java

// 调用通过用户id查询用户信息的方法
User user = mapper.selectUserById(1);
System.out.println(user);

select-resultMap

代码实现
User模块Mapper层配置文件：UserMapper.xml

<resultMap id="userMap" type="entity.User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="age" column="age"/>
    <result property="score" column="score"/>
</resultMap>

把UserMapper.xml文件中selectUserById标签的返回类型改为上面创建的resultMap

resultMap="userMap"