使用 JDBC 操作数据库

开发环境

[Java环境] ：jdk1.8.0_91
[开发工具] ： IDEA 2016.2.5
[数据库] ： MySQL 5.7.13
[项目管理工具]：Maven 3.1.1

示例代码

表结构：

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

java 代码：

import java.sql.*;

/**
 * 使用 jdbc 操作数据库
 */
public class JdbcTest {

    public static void main(String[] args){
        //数据库连接
        Connection connection = null;
        //预编译的Statement，使用预编译的Statement可以提高数据库性能
        PreparedStatement preparedStatement = null;
        //结果集
        ResultSet resultSet = null;
        try
        {
            //加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            //通过驱动管理类获取数据库链接
            connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/db_mybatis?characterEncoding=utf-8", "root", "123456");
            //定义sql语句 ?表示占位符（在这里表示username）
            String sql = "select * from tb_user where username = ?";
            //获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
            preparedStatement.setString(1, "王五");
            //向数据库发出sql执行查询，查询出结果集
            resultSet =  preparedStatement.executeQuery();
            //遍历查询结果集
            while(resultSet.next())
            {
                System.out.println(resultSet.getString("id")+"  "+resultSet.getString("username"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //释放资源
            if(resultSet!=null)
            {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null)
            {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null)
            {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
    }

思考

每次数据库操作都需要创建/释放 数据库连接, 频繁创建/释放浪费数据库资源，影响性能
解决方案：数据库连接池，统一管理数据库连接对象的创建、释放

sql 语句硬编码，修改sql 需要重新编译，不利于系统的维护
解决方案：将 sql 语句抽取出来动态获取

向 preparedStatement 中设置参数硬编码在java 代码中，不利于系统的维护
解决方案：将 sql 语句占位符参数绑定抽取出来，动态绑定

从 resultSet 中遍历结果集数据时，获取表字段存在硬编码，不利于系统维护
解决方案：将查询结果自动映射成 java 对象

总结：原始 jdbc 方式操作数据库，存在性能问题、存在硬编码不利于后续维护

使用持久层框架-MyBatis

期望框架解决的问题：

数据源、事务、数据库连接池抽取到全局配置文件中，灵活配置
sql 语句、设置占位符参数、结果集数据获取抽取到配置文件中，自由灵活的映射方式可以半自动化的生成 SQL语句

框架使用（入门级）：

引入框架程序包（此处采用Maven 方式）
配置日志工具（默认使用log4j 输出日志信息，log4j.properties）
定义全局配置文件（此处使用 mybatis-config.xml）
定义 sql 映射文件（*-mapper.xml）
加载全局配置文件生成会话工厂（SqlSessionFactory）
通过会话工厂创建会话对象（SqlSession）执行 *-mapper.xml 中定义 sql

入门程序

预期功能：

使用 MyBatis 实现单表的 CRUD

Maven 引入相关程序包配置 pom.xml :

<!--依赖归类-->
<properties>

    <junit.version>3.8.1</junit.version>
    <slf4j.version>1.6.6</slf4j.version>
    <log4j.version>1.2.12</log4j.version>
    <mybatis.version>3.2.2</mybatis.version>
    <mysql.connector.version>5.1.30</mysql.connector.version>
</properties>
<dependencies>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>3.8.1</version>
        <scope>test</scope>
    </dependency>

    <!-- log start -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>${log4j.version}</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>${slf4j.version}</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>${slf4j.version}</version>
    </dependency>

    <!-- log end -->

    <!-- mysql-connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql.connector.version}</version>
    </dependency>

    <!-- mybatis start-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>${mybatis.version}</version>
    </dependency>
</dependencies>

<build>
    <resources>
        <!--资源文件打包-->
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
                <include>**/*.properties</include>
            </includes>
        </resource>
    </resources>
</build>

classpath下创建log4j.properties

# Global logging configuration
#在开发环境日志级别要设置为DEBUG、生产环境要设置为INFO或者ERROR
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默认使用log4j作为输出日志信息

MyBatis 全局配置 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>
    <!-- 和spring整合后 environments配置将废除-->
    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理,事务由 Mybatis 控制-->
            <transactionManager type="JDBC" />
            <!-- 数据库连接池,由Mybatis管理，db_mybatis，Mysql用户名root，123456 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/db_mybatis?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>

    <!--加载 mapper 文件 路径-->
    <mappers>
        <mapper resource="mapperDir2/user-mapper.xml" />
    </mappers>
</configuration>

sql mapper 配置文件user-mapper.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 ：命名空间，对 sql 进行分类化管理，用于隔离 sql 语句-->
<mapper namespace="test">
    <!--通过select执行数据库查询
       id:标识映射文件中的sql
       将sql语句封装到mappedStatement对象中，所以id称为Statement的id
       #{}：表示占位符
       #{id}：其中的id表示接收输入的参数，参数名称就是id，如果输入参数是简单类型，那么#{}中的参数名可以任意，可以是value或者其他名称
       parameterType：表示指定输入参数的类型
       resultType：表示指定sql输出结果的所映射的java对象类型
    -->
    <!-- 根据id获取用户信息 -->
    <select id="findUserById" parameterType="int" resultType="cn.guan.mybatis.example2.User">
        select * from tb_user where id = #{id}
    </select>

    <!-- 自定义条件查询用户列表
        resultType：指定就是单条记录所映射的java对象类型
          ${}:表示拼接sql串，将接收到的参数内容不加修饰的拼接在sql中
          使用${}拼接sql，会引起sql注入
          ${value}：接收输入参数的内容，如果传入类型是简单类型，${}中只能够使用value
    -->
    <select id="findUserByUsername" parameterType="java.lang.String" resultType="cn.guan.mybatis.example2.User">
        select * from tb_user where username like '%${value}%'
    </select>

    <!-- 添加用户 -->
    <insert id="insetrUser" parameterType="cn.guan.mybatis.example2.User">
        <!--
        添加selectKey实现将主键返回
        keyProperty:返回的主键存储在pojo中的哪个属性
        order：selectKey的执行顺序，是相对与insert语句来说，由于mysql的自增原理执行完insert语句之后才将主键生成，所以这里selectKey的执行顺序为after
        resultType:返回的主键是什么类型
        LAST_INSERT_ID():是mysql的函数，返回auto_increment自增列新记录id值。
        -->
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            select LAST_INSERT_ID()
        </selectKey>
        insert into tb_user(username, birthday, sex, address)
        values(#{username}, #{birthday}, #{sex}, #{address})
    </insert>

    <!--删除用户-->
    <delete id="deleteUserById" parameterType="int">
        delete from tb_user where id = #{id}
    </delete>

    <!--根据id更新用户
        需要输入用户的id
        传入用户要更新的信息
        parameterType指定user对象，包括id和更新信息，id必须存在
        #{id}：从输入对象中获取id属性值
    -->
    <update id="updateUserById" parameterType="cn.guan.mybatis.example2.User">
        update tb_user set username = #{username}, birthday = #{birthday}, sex = #{sex}, address = #{address} where id = #{id}
    </update>

</mapper>

java 程序：

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.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * 使用MyBatis 实现 单表 CRUD
 */
public class MyBatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void initEnv(){
        String configFile = "mybatis-config.xml";
        try {
            //1、加载 mybatis 全局配置文件
            InputStream configInputStream =  Resources.getResourceAsStream(configFile);
            //2、 根据配置文件创建会话工厂  DefaultSqlSessionFactory
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(configInputStream);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据用户 id（主键）查询用户信息
     */
    @Test
    public void test1(){
        //3、通过会话工厂创建会话
        // openSession(boolean autoCommit)  默认false
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //通过SqlSession操作数据库
        //第一个参数：映射文件中Statement的id，等于 = namespace + "." + Statement的id
        //第二个参数：指定和映射文件中所匹配的parameterType类型的参数
        //sqlSession.selectOne 结果与映射文件中所匹配的resultType类型的对象
        User user = sqlSession.selectOne("test.findUserById", 1);

        System.out.println(user==null?"null":user.toString());

        //释放资源
        sqlSession.close();
    }

    /**
     *根据用户名称模糊查询用户信息列表
     */
    @Test
    public void test2(){
        //3、通过会话工厂创建会话
        // openSession(boolean autoCommit)  默认false
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //通过SqlSession操作数据库
        //第一个参数：映射文件中Statement的id，等于 = namespace + "." + Statement的id
        //第二个参数：指定和映射文件中所匹配的parameterType类型的参数

        //selectList 查询结果可能多条
        //list中的user和映射文件中resultType所指定的类型一致
        List<User> list = sqlSession.selectList("test.findUserByUsername", "小明");

        System.out.println(list);

        //释放资源
        sqlSession.close();
    }

    /**
     * 添加用户
     */
    @Test
    public void test3() throws ParseException {
        //3、通过会话工厂创建会话
        // openSession(boolean autoCommit)  默认false
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd");
        user.setUsername("张三");
        user.setSex("男");
        user.setBirthday(sdf.parse("1997-09-05"));
        user.setAddress("广东广州");
        sqlSession.insert("test.insetrUser", user);
        
        System.out.println(user==null?"null":user.toString());
        //释放资源
        sqlSession.close();
    }



    /**
     * 删除用户
     */
    @Test
    public void test4(){
        //3、通过会话工厂创建会话
        // openSession(boolean autoCommit)  默认false
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //通过SqlSession操作数据库
        //第一个参数：映射文件中Statement的id，等于 = namespace + "." + Statement的id
        //第二个参数：指定和映射文件中所匹配的parameterType类型的参数

        sqlSession.delete("test.deleteUserById", 1);

        //提交事务
        

        //释放资源
        sqlSession.close();
    }

    /**
     * 根据id更新用户信息
     * @throws ParseException
     */
    @Test
    public void test5() throws ParseException {
        //3、通过会话工厂创建会话
        // openSession(boolean autoCommit)  默认false
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //为了设置生日的日期输入
        SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd");

        User user = new User();
        //根据id更新用户信息
        user.setId(2);
        user.setUsername("张四");
        user.setBirthday(sdf.parse("2015-01-12"));
        user.setSex("女");
        user.setAddress("上海黄埔");

        //通过SqlSession操作数据库
        //第一个参数：映射文件中Statement的id，等于 = namespace + "." + Statement的id
        //第二个参数：指定和映射文件中所匹配的parameterType类型的参数
        sqlSession.update("test.updateUserById", user);

        //提交事务
        

        //释放资源
        sqlSession.close();
    }

}

总结

#{ } 和 ${ } 的区别

#{ }表示一个占位符号，通过#{ }可以实现 preparedStatement 向占位符中设置值，自动进行java 类型和 jdbc 类型转换，#{ } 可以有效防止sql注入。#{ } 可以接收简单类型值或 pojo 属性值（通过 OGNL 读取对象中的值，属性.属性.属性..方式获取对象属性值）。如果 parameterType 传输单个简单类型值，#{ }括号中可以是 value 或其它名称。
${ } 表示拼接 sql 串，通过${ }可以将 parameterType 传入的内容拼接在 sql 中且不进行 jdbc 类型转换， ${ }可以接收简单类型值或 pojo 属性值（通过 OGNL 读取对象中的值，属性.属性.属性..方式获取对象属性值），如果 parameterType 传输单个简单类型值，${}括号中只能是 value。

parameterType 和 resultType 区别

parameterType：指定输入参数类型，mybatis 通过 ognl 从输入对象中获取参数值拼接在 sql 中。
resultType：指定输出结果类型，mybatis 将 sql 查询结果的一行记录数据映射为 resultType 指定类型的对象。

自增主键返回与非自增主键返回

自增主键返回
添加selectKey实现将主键返回
keyProperty:返回的主键存储在pojo中的哪个属性
order：selectKey的执行顺序，是相对与insert语句来说，由于mysql的自增原理执行完insert语句之后才将主键生成，所以这里selectKey的执行顺序为after
resultType:返回的主键是什么类型
LAST_INSERT_ID():是mysql的函数，返回auto_increment自增列新记录id值。

MySQL：

<insert id="insetrUser" parameterType="cn.guan.mybatis.example2.User">
    <!--
    添加selectKey实现将主键返回
    keyProperty:返回的主键存储在pojo中的哪个属性
    order：selectKey的执行顺序，是相对与insert语句来说，由于mysql的自增原理执行完insert语句之后才将主键生成，所以这里selectKey的执行顺序为after
    resultType:返回的主键是什么类型
    LAST_INSERT_ID():是mysql的函数，返回auto_increment自增列新记录id值。
    -->
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
        select LAST_INSERT_ID()
    </selectKey>
    insert into tb_user(username, birthday, sex, address)
    values(#{username}, #{birthday}, #{sex}, #{address})
</insert>

MySQL 自增主键：执行 insert 提交之前自动生成一个自增主键，通过 MySQL 函数获取到刚插入记录的自增主键： LAST_INSERT_ID() ，是在 insert 函数之后调用。

非自增主键返回

MySQL使用uuid()：
使用 MySQL 的 uuid() 函数生成主键，需要修改表中 id 字段类型为 String ，长度设置为 35 位，执行思路：先通过 uuid() 查询到主键，将主键输入到 sql 语句中；执行 uuid() 语句顺序相对于 insert 语句之前执行。

<insert id="insetrUser" parameterType="cn.guan.mybatis.example2.User">
    <!--使用 MySQL 的 uuid()生成主键
      执行过程：
      首先通过uuid()得到主键，将主键设置到user对象的id属性中
      其次执行insert时，从user对象中取出id属性值
    -->
    <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.String">
              select uuid()
    </selectKey>
    insert into user(id, username, birthday, sex, address) values(#{id}, #{username}, #{birthday}, #{sex}, #{address})
</insert>

Oracle使用序列生成主键：
首先自定义一个序列且用于生成主键

<insert  id="insertUser" parameterType="cn.itcast.mybatis.po.User">
<selectKey resultType="java.lang.Integer" order="BEFORE" 
  keyProperty="id">
  SELECT 自定义序列.NEXTVAL FROM DUAL
</selectKey>
insert into user(id,username,birthday,sex,address) 
     values(#{id},#{username},#{birthday},#{sex},#{address})
</insert>

全部频道

MyBatis由浅入深（实践篇）-1

使用 JDBC 操作数据库

使用持久层框架-MyBatis

入门程序

总结