数据库连接_由浅入深搭建Mybatis框架

1、使用IDEA创建maven工程

1.1、目录结构

1.2、引入mysql依赖包

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
</dependency>

2、创建数据库及数据

2.1、执行以下sql

创建数据库：

CREATE DATABASE ssmdemo;

创建表：

DROP TABLE IF EXISTS tb_user;
CREATE TABLE tb_user (
id CHAR(32) NOT NULL,
username VARCHAR(32) DEFAULT NULL,
PASSWORD VARCHAR(32) DEFAULT NULL,
NAME VARCHAR(32) DEFAULT NULL,
age INT(10) DEFAULT NULL,
sex INT(2) DEFAULT NULL,
birthday DATE DEFAULT NULL,
created DATETIME DEFAULT NULL,
updated DATETIME DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO ssmdemo.tb_user ( id,username, PASSWORD, NAME, age, sex, birthday, created, updated) VALUES (3,'yf', '123456', '叶凡', '22', '1','1990-09-02', SYSDATE(), SYSDATE());
INSERT INTO ssmdemo.tb_user ( id,username, PASSWORD, NAME, age, sex, birthday, created, updated) VALUES ( 2,'xnn', '123456', '小囡囡', '5', '1', '1993-09-05', SYSDATE(), SYSDATE());

3、JDBC方式连接数据库

3.1 新建JDBCTest.java

package com.mybatis.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/17
 */
public class JDBCTest {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;

        try {
            // 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取连接
            String url = "jdbc:mysql://127.0.0.1:3306/ssmdemo";
            String user = "root";
            String password = "123456";
            connection = DriverManager.getConnection(url, user, password);
            // 获取statement，preparedStatement
            String sql = "select * from tb_user where id=?";
            prepareStatement = connection.prepareStatement(sql);
            // 设置参数
            prepareStatement.setString(1, "2");
            // 执行查询
            rs = prepareStatement.executeQuery();
            // 处理结果集
            while (rs.next()) {
                System.out.println(rs.getString("username"));
                System.out.println(rs.getString("name"));
                System.out.println(rs.getInt("age"));
                System.out.println(rs.getDate("birthday"));
            }
        } finally {
            // 关闭连接，释放资源
            if (rs != null) {
                rs.close();
            }
            if (prepareStatement != null) {
                prepareStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

}

执行一下，发现会报错：

Exception in thread "main" java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone...

因为引入的MySQL服务是8.0.12及以上的版本，在新版的数据库使用的时区与本地时区有区别，标准时区使用的是Unix元年的时间为起始点到当前时间中间所做的动作。国际标准失去与本地相差 8 个小时，所以才会出现这种错误。

解决方法1（解决方法2在接下来spring整合mybatis时候讲）：
在连接数据之后，执行以下sql语句：

 set global time_zone='+8:00';

然后再运行JDBCTest.java中的main()方法，返回结果：

3.2、JDBC连接数据库缺点分析

4、搭建Mybatis框架

4.1、引入依赖：

<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.8</version>
</dependency>

4.2、配置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>
    <!-- 环境，可以配置多个，default：指定采用哪个环境 -->
    <typeAliases>
        <package name="com.mybatis.pojo"/>
    </typeAliases>
    <environments default="development">
        <!-- id：唯一标识 -->
        <environment id="development">
            <!-- 事务管理器，JDBC类型的事务管理器 -->
            <transactionManager type="JDBC"/>
            <!-- 数据源，池类型的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/ssmdemo"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

</configuration>

4.3、配置MyMapper.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 namespace="MyMapper">
    <!-- statement，内容：sql语句。id：唯一标识，随便写，在同一个命名空间下保持唯一
      resultType：sql语句查询结果集的封装类型,tb_user即为数据库中的表
    -->

    <select id="selectAll" resultType="user" parameterType="java.lang.String">
    select * from tb_user where id=#{id};
  </select>
</mapper>

4.4、在mybatis-config.xml中配上MyMapper.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>
    <!-- 环境，可以配置多个，default：指定采用哪个环境 -->
    <typeAliases>
        <package name="com.mybatis.pojo"/>
    </typeAliases>
    <environments default="development">
        <!-- id：唯一标识 -->
        <environment id="development">
            <!-- 事务管理器，JDBC类型的事务管理器 -->
            <transactionManager type="JDBC"/>
            <!-- 数据源，池类型的数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/ssmdemo"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/MyMapper.xml"/>
    </mappers>
</configuration>

4.5、创建MybatisTest.java

package com.mybatis.mybatis;

import com.mybatis.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 java.io.InputStream;
import java.util.List;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/18
 */
public class MybatisTest {
    public static void main(String[] args) throws Exception {
        // 指定全局配置文件
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 构建sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            // 操作CRUD，第一个参数：指定statement，规则：命名空间+“.”+statementId
            // 第二个参数：指定传入sql的参数：这里是用户id
            List<User> userList = sqlSession.selectList("MyMapper.selectAll","3");
            System.out.println(userList);
        } finally {
            sqlSession.close();
        }
    }
}

4.6、User.java文件

package com.mybatis.pojo;

import java.util.Date;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/18
 */
public class User {
    private String id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private String created;
    private String updated;

    public String getId() {
        return id;
    }

    public void setId(String 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 String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public String getCreated() {
        return created;
    }

    public void setCreated(String created) {
        this.created = created;
    }

    public String getUpdated() {
        return updated;
    }

    public void setUpdated(String updated) {
        this.updated = updated;
    }

    @Override
    public String toString() {
        return "User{" +
                "id='" + id + '\'' +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                ", birthday=" + birthday +
                ", created='" + created + '\'' +
                ", updated='" + updated + '\'' +
                '}';
    }
}

4.7、对比JDBC使用的好处：

1)配置了mybatis-config.xml 全局的配置文件 (配置数据源，连接数据库)
2)通过创建SqlSessionFactory中SqlSession对象操作数据库 CRUD
3)调用session.commit()，session.close()提交事务，关闭会话。不用每次都要在java代码中释放资源

5、通过UserDao接口实现调用

5.1、创建UserDao.java

package com.mybatis.dao;

import com.mybatis.pojo.User;

import java.util.List;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/18
 */
public interface UserDao {

    public List<User> selectAll();
}

5.2、创建UserDaoImpl.java

package com.mybatis.dao.impl;

import com.mybatis.dao.UserDao;
import com.mybatis.pojo.User;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/18
 */
public class UserDaoImpl implements UserDao {

    public SqlSession sqlSession;

    public UserDaoImpl(SqlSession sqlSession) {
        this.sqlSession = sqlSession;
    }

    @Override
    public List<User> selectAll() {
        return this.sqlSession.selectList("UserDao.selectAll");
    }
}

其中this.sqlSession.selectList("UserDao.selectAll");的UserDao是指xml文件中namespace = ‘UserDao’的文件，然后找其对应id = ‘selectAll’的sql语句

5.3、创建UserDaoMapper.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 namespace="UserDao">
    <select id="selectAll" resultType="user">
        select * from tb_user;
    </select>
</mapper>

5.4、在mybatis-config.xml中添加配置：

<mappers>
    <mapper resource="mappers/MyMapper.xml"/>
    <mapper resource="mappers/UserDaoMapper.xml"/>
</mappers>

5.5、添加UserDao的测试用例

pom.xml文件中添加依赖：

<dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
</dependency>

按住Alt+Enter,选择create test

5.6、编写UserDaoTest.java

package com.mybatis.test;

import com.mybatis.dao.UserDao;
import com.mybatis.dao.impl.UserDaoImpl;
import com.mybatis.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.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;


/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/18
 */


public class UserDaoTest {

    public UserDao userDao;
    public SqlSession sqlSession;

    @Before
    public void setUp() throws Exception {
        // mybatis-config.xml
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream is = Resources.getResourceAsStream(resource);
        // 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 获取sqlSession
        sqlSession = sqlSessionFactory.openSession();
        this.userDao = new UserDaoImpl(sqlSession);
    }


    @Test
    public void queryUserAll() throws Exception {
        List<User> userList = this.userDao.selectAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

}

6、通过动态代理实现

优点：通过编写接口和mapper.xml即可，不用写实现类

6.1、创建UserMapper.java

package com.mybatis.dao;

import com.mybatis.pojo.User;

import java.util.List;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/20
 */
public interface UserMapper {

    public List<User> selectAll();
}

6.2、创建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 namespace="com.mybatis.dao.UserMapper">
    <select id="selectAll" resultType="user">
        select * from tb_user;
    </select>
</mapper>

在UserMapper.xml中配置接口的全路径。
如果希望使用mybatis通过的动态代理的接口，就需要namespace中的值，和需要对应的Mapper(dao)接口的全路径一致
全局配置文件mybatis-config.xml引入UserMapper.xml

<mappers>
    <mapper resource="mappers/MyMapper.xml"/>
    <mapper resource="mappers/UserDaoMapper.xml"/>
    <mapper resource="mappers/UserMapper.xml"/>
</mappers>

6.3、创建UserMapperTest.java

package com.mybatis.test;

import com.mybatis.dao.UserDao;
import com.mybatis.dao.UserMapper;
import com.mybatis.dao.impl.UserDaoImpl;
import com.mybatis.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.Before;
import org.junit.Test;

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

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/20
 */
public class UserMapperTest {
    public UserMapper userMapper;
    public SqlSession sqlSession;

    @Before
    public void setUp() throws Exception {
        // mybatis-config.xml
        String resource = "mybatis-config.xml";
        // 读取配置文件
        InputStream is = Resources.getResourceAsStream(resource);
        // 构建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        // 获取sqlSession
        sqlSession = sqlSessionFactory.openSession();
//        this.userDao = new UserDaoImpl(sqlSession);
        this.userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void queryUserAll() throws Exception {
        List<User> userList = this.userMapper.selectAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

7、Spring整合Mybatis

7.1、引入Spring和Mybatis相关依赖

pom.xml文件添加：

 <!--数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.3.RELEASE</version>
        </dependency>

        <!--spring集成Junit测试-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>4.1.3.RELEASE</version>
            <scope>test</scope>
        </dependency>

        <!--spring容器-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.1.3.RELEASE</version>
        </dependency>

7.2、配置spring配置文件：

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

    <!-- 加载配置文件 -->
    <context:property-placeholder location="classpath:properties/*.properties"/>
    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.database}"/>
        <property name="username" value="${jdbc.userName}"/>
        <property name="password" value="${jdbc.passWord}"/>
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="${jdbc.initialSize}"></property>
        <!-- 连接池最大数据库连接数  0 为没有限制 -->
        <property name="maxActive" value="${jdbc.maxActive}"></property>
        <!-- 连接池最大的空闲连接数，这里取值为20，表示即使没有数据库连接时依然可以保持20空闲的连接，而不被清除，随时处于待命状态 0 为没有限制 -->
        <property name="maxIdle" value="${jdbc.maxIdle}"></property>
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="${jdbc.minIdle}"></property>
        <!--最大建立连接等待时间。如果超过此时间将接到异常。设为-1表示无限制-->
        <property name="maxWait" value="${jdbc.maxWait}"></property>
    </bean>
    <!-- spring和MyBatis完美整合 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!-- 自动扫描mapping.xml文件 -->
        <property name="mapperLocations" value="classpath:mappers/*.xml"></property>
        <!--如果mybatis-config.xml没有特殊配置也可以不需要下面的配置-->
        <property name="configLocation" value="classpath:mybatis-config.xml" />
    </bean>

    <!-- DAO接口所在包名，Spring会自动查找其下的类 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.mybatis.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

    <!-- (事务管理)transaction manager -->
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

db.properties：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.database=jdbc:mysql://127.0.0.1:3306/ssmdemo?serverTimezone=UTC
jdbc.userName=root
jdbc.passWord=123456
jdbc.initialSize=0
jdbc.maxActive=20
jdbc.maxIdle=20
jdbc.minIdle=1
jdbc.maxWait=1000

其中在jdbc.database后面加了一串：?serverTimezone=UTC
为之前说的解决方法2，指定时区为UTC，否则启动报sqlException.
由于applicationContext-dao.xml中配置了Mapper接口扫描，所以删除mybatis-config.xml中的配置，否则报已映射错误：
Caused by: org.apache.ibatis.builder.BuilderException: Error parsing Mapper XML. Cause: java.lang.IllegalArgumentException: Mapped Statements collection already contains value for MyMapper.selectUser
删除mybatis-config.xml中的映射配置：

<!--<mappers>
        <mapper resource="mappers/MyMapper.xml"/>
        <mapper resource="mappers/UserDaoMapper.xml"/>
        <mapper resource="mappers/UserMapper.xml"/>
</mappers>-->

7.3、创建UserMapperSpringTest.java

package com.mybatis.test;

import com.mybatis.dao.UserMapper;
import com.mybatis.pojo.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.List;

/**
 * Author:LIUZHENSHENG247
 * Description:
 * CreateDate:2020/5/20
 */
@RunWith(SpringJUnit4ClassRunner.class)//junit整合spring的测试//立马开启了spring的注解
@ContextConfiguration(locations="classpath:spring/applicationContext-*.xml")//加载核心配置文件，自动构建spring容器
public class UserMapperSpringTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testQueryUserAll() {
        List<User> userList = this.userMapper.selectAll();
        for (User user : userList) {
            System.out.println(user);
        }
    }

}

8、SpringBoot整合Mybatis

可以参考这一篇
SpringBoot整合Mybatis框架过程