JDBC 与 MyBatis

JDBC

JDBC API

1. Driver 是一个接口，定义了各个驱动程序必须要实现的功能, 是驱动程序的抽象，通过操作 Driver 接口即可以实现对各个驱动程序的操作。
2. DriverManager 是 Driver 的管理类。用户通过 Class.forname(DriverName) 可以向 DriverManager 注册一个驱动程序。然后通过 DriverManager 的 getConnection(DB_URL, USER, PASS) 方法就可以调用该驱动程序，建立到后端数据库的物理连接。
   

   常用的JDBC URL 格式
   * MySQL: jdbc:mysql://<ip>:<port>/database
   * ORACLE: jdbc:oracle:thin:@<ip>:<port>:database
   * MicroSoft SQL Server: jdbc:microsoft:sqlserver://<ip>:<port>;DatabaseName=database

3. Connection 对象代表 Java 应用程序到后端数据库的一条物理连接,基于这条连接可以执行一些 SQL 语句.
   Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
   Connection 常用方法
   Statement stmt = conn.createStatement();
   Statement 对象是 SQL 语句的对象, 可以通过它来执行 SQL语句
   查询: ResultSet rs = stmt.executeQuery('select * from table');
   删除: stmt.execute('...');
   修改: stmt.executeUpdate('...');
   删除和修改返回一个 INT 值，表示本次操作影响了多少条数据元组。

构建JDBC程序的步骤

1. 装载驱动程序
2. 建立数据库连接
3. 执行SQL语句
4. 获取执行结果
5. 清理环境

业务场景分析

大量数据读取

1. SQL过滤条件比较弱，一次可能读出较多记录
2. 需要读取数据库表中所有记录
   由于JVM有内存大小限制，一次性将大量数据读取会造成内存溢出异常。
   解决方案 游标: 提供一种客户端读取部分服务器端结果集的机制
   使用方法:
   
   • 在 DB_URL 中增加 useCursorFetch=true 参数 jdbc:mysql://<ip>:<port>/<database>?useCursorFetch=true
   • 使用 PreparedStatement 来代替 Statement。 相比 Statement， PreparedStatement要求在生成PreparedStatement对象时就要传入 SQL 语句。 该 SQL 语句是个参数格式化的语句，其 where 的条件都是通过 ? 的形式来表示的， 后续通过 PreparedStatement 的 setString 和 setInt 方法来设置参数。 通过setFetchSize来设置客户端JDBC每次从服务端取回的记录的数量。

读取数据库表大字段(如文章、图片等)

由于字段内容过大，可能会造成JVM内存溢出
解决方案 流方式：以二进制流的方式将大字段划分为多个区间逐个读取

// 获取对象流
InputStream in = rs.getBinaryStream("字段名称");
// 将对象流写入文件
File f = new File(FILE_URL);
OutputStream out = null;
out = new FileOutputStream(f);
int temp = 0;
while ((temp = in.read()) != -1) {
    out.write(temp);
}
in.close();
out.close();

大量数据插入操作

利用循环执行 stmt.executeUpdate 来进行插入海量数据会十分缓慢
解决方案 批处理: 通过发送一次 SQL 可以处理多条数据
通过 Statement 的 addBatch() executeBatch() 以及 clearBatch()来实现批处理

DBCP 数据库连接池

public class DBPoolTest {
    public static BasicDataSource ds = null;
    public final static String DRIVER_NAME = "com.mysql.jdbc.Driver";
    public final static String USER_NAME = "root";
    public final static String PASSWORD = "***";
    public final static String DB_URL = "jdbc:mysql://localhost/database";

    public static void dbpoolInit() {
        // 创建数据库连接池
        ds = new BasicDataSource();
        // 配置数据库连接池
        ds.setUrl(DB_URL);
        ds.setDriverClassName(DRIVER_NAME);
        ds.setPassword(PASSWORD);
        ds.setUsername(USER_NAME);
    }

    public void dbPoolTest() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 租借数据库连接
            conn = ds.getConnection();

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from users");
            while (rs.next()) {
                System.out.println(rs.getString("userName"));
            }
        } catch (SQLException e) {

        } finally {
            // 归还数据库连接
            try {
                if (conn != null)
                    conn.close();
                if (stmt != null)
                    stmt.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {

            }
        }
    }

    public static void main(String[] args) {
        dbpoolInit();
        new DBPoolTest().dbPoolTest();

    }

}

高级配置

1. 第一次访问数据库连接池时由于连接池中没有连接需要等待连接建立，所以第一次访问速度会比较慢。可以通过 BasicDataSource 的 setInitialSize() 方法在应用程序启动时在连接池中预置一定数量的连接。
2. 通过BasicDataSource 的 setMaxTotal 方法可以设置最大连接数，当达到最大连接数时，后续连接请求进入等待队列。起到限流保护数据库的作用。
3. 通过 BasicDataSource 的 setMaxWaitMillis 方法设置请求最大等待时间，超过该时间则会抛出异常。
4. 通过 BasicDataSource 的 setMaxIdle 方法可以设置最大空闲连接数，当空闲连接超过该值时数据库连接池会销毁多余连接。可以减少后端数据库不必要的资源的损耗
5. 通过 BasicDataSource 的 setMinIdle 方法可以保证连接池有足够的连接可以被租借

DBCP 定期检查，确保数据库连接池中的连接都是有效的
6. 通过 BasicDataSource 的 setTestWhileIdle() 方法可以开启定期检查功能
7. 通过 BasicDataSource 的 setMinEvictableldleTimeMillis() 方法设置销毁连接的最小空闲时间(最好小于服务器端数据库自动关闭连接的阈值时间)
8. 通过 BasicDataSource 的 setTimeBtweenEvictionRunsMillis() 方法设置检查运行时间的时间间隔

SQL注入与防范

什么是数据库注入

web应用架构下，客户端用户无法直接访问数据库,必须通过发送http请求到服务器，由服务器访问后端数据库。SQL注入利用应用业务程序漏洞，伪装自己的请求，欺骗业务程序达到获取数据库数据的目的

SQL 注入就是用户在输入表单或URL参数中输入SQL命令达到欺骗服务器的目的，破坏原有的SQL语义,发送恶意的SQL语句到后端数据库，导致数据库信息泄露。
实例代码:

// 检索登录用户名与密码
String sql = "select * from user where userName = '" + userName + "' and password = '" password + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

此时如果用户传入的用户名为 ZhangSan';-- 密码随便输入，此时程序中设定的SQL语句就会变为两句，并且由于--这个SQL注释符的原因，后一句SQL语句被注释掉。最终就变为了select * from user where userName = 'ZhangSan'; 攻击者不需要密码就可以成功登录。

解决方案

不要使用动态拼接的SQL语句，因为这样SQL语句的语义在运行前是不确定的。可以使用格式化的SQL语句,先确定SQL的语义再传入参数。

String sql = "select * from users where userName = ? and password = ?";
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setString(1, userName);
ptmt.setString(2, password);
ptmt.executeQuery();

其他注意事项

• 仅给与web应用访问数据库的最小权限
• 避免Drop table 等权限
• 禁止直接将后端数据库异常信息暴露给用户。应该对后端异常信息进行必要的封装，避免用户直接查看到后端异常
• 机密信息禁止明文存储。涉密信息需要加密处理

事务

事务原理与开发

事务(Transaction)是并发控制的基本单位，指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要满足ACID特性。
ACID特性:原子性，一致性，隔离性，持久性

JDBC 事务控制

开启事务: connection.setAutoCommit(false); 此后该connection的所有sql语句都会作为JDBC的一个事务来执行.
提交事务: connection.commit();
回滚事务: connection.rollback();

MyBatis

ORM(Obeject/Relation Mapping)

持久化类与数据库表之间的映射关系
对持久化对象的操作自动转换成对关系数据库的操作
如何映射？
* 关系数据库的每一行映射为一个对象
* 关系数据库的每一列映射为对象的一个属性

MyBatis

前身是 apache 基金会下的一个开源项目 iBatis
是一个支持自定义SQL、存储过程和高级映射的持久化框架
使用XML或者注解配置
能够映射基本数据元素、接口、Java对象到数据库

MyBatis 工作流机制

1. 根据XML或者注解加载SQL语句、参数映射、结果映射到内存
2. 应用程序调用API传入参数和SQLID
3. MyBatis 自动生成SQL语句完成数据库访问，转换执行结果返回应用程序。

MyBatis 的使用

配置操作

<!-- 配置 SqlSessionFactory -->
<configuration>
    <environments default="development">
        <environment id="development">
            <!-- 设为jdbc表明事务的提交和回滚交给mybatis执行，如果是manager的话是交给外部容器执行 -->
            <transactionManager type="jdbc"></transactionManager>
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://databaseURL"></property>
                <property name="username" value="username"></property>
                <property name="password" value="password"></property>
            </dataSource>
        </environment>
    </environments>
    <!-- 注册后文配置的映射关系 -->
    <mappers>
        <mapper resource="映射关系配置文件的路径" />
    </mappers>
</configuration>

// 构造对象
public class User {
    private int id;
    private String userName;
    private String corp;

    public User(Integer id, String userName, String corp) {
        this.id = id;
        this.userName = userName;
        this.corp = corp;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
}

// 构造接口
public interface GetUserInfo {
    public User getUser(int id);
    public void addUser(User user);
    public void updateUser(User user);
    public void deleteUser(User user);
}

<!-- 创建Java对象和SQL语句映射关系配置文件 -->
<mapper namespace="package.GetUserInfo">
    <!-- 在select 标签中编写查询的SQL语句，设置select标签的id属性为 -->
    <select id="getUser" parameterType="int" resultType="package.User">
        select id, userName, corp from user where id =#{id}
    </select>
</mapper> 
<!-- 将映射关系配置文件注册到之前的 SqlSessionFactory 的配置文件中-->

完成数据库查询

流程：加载配置文件 -> 生成 SqlSessionFactory -> 获取 SqlSession -> Session 执行 SQL

//1. 声明配置文件目录位置
String resource = "conf.xml";
//2. 加载应用配置文件
InputStream is = HelloMyBatis.class.getClassLoader().getResourceAsStream(resource);
//3. 创建SqlSessionFactory
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//4. 获取Session
SqlSession session = sessionFactory.openSession();
try {
    //5. 获取操作类
    GetUserInfo getUserInfo = session.getMapper(GetUserInfo.class);
    //6. 完成查询操作
    User user = getUserInfo.getUser(1);
    System.out.println(user.getId() + " " + user.getUserName() + " " + user.getCorp());
    } finally {
    //7. 关闭Session
    session.close();
}

MayBatis 的优势和劣势

优势: 入门门槛较低、更加灵活、SQL优化
劣势: 需要自己编写SQL，工作量大、数据库移植性差

通过注解的方式声明

public interface GetUserInfoAnnotation {
    @Select("select * from users where id = #{id}")
    public User getUser(int id);
}

利用 ResultMap 处理复杂关系

ResultMap 元素是 MyBatis 中最重要最强大的元素。可以用于实现复杂查询结果到复杂对象关联关系的转化
以选课系统为例，数据库应该有四张表
1. users: id, userName, corp
2. userCourse: id, userId, courseId
3. course: id, courseName, teacherId
4. teachers: id, teacherName

• 通过构造方法的方式来对对象进行赋值建立映射关系
  类在实例化时，用注入的方式将数据库的检索结果利用对象的构造函数注入到对象中
• Collection
  实现一对多的关联
  id - 一个 ID 结果; 标记结果作为 ID 可以帮助提高整体效能
  result - 注入到字段或 JavaBean 属性的普通结果
• Association
  实现复杂类型之间的关联
  id - 一个 ID 结果; 标记结果作为 ID 可以帮助提高整体效能
  result - 注入到字段或 JavaBean 属性的普通结果

// 定义 User 类
public class User {
    private int id;
    private String userName;
    private String corp;
    private List<Course> courses;

    public User(Integer id, String userName, String corp) {
        this.id = id;
        this.userName = userName;
        this.corp = corp;
    }
    // 此处省略了 getter 和 setter没有写出
}
// 定义 Course 类
public class Course {
    private int id;
    private String courseName;
    private Teacher teacher;
}

// 定义  Teacher 类
public class Teacher {
    private int id;
    private String teacherName;
}

<mapper namespace="net.gyronee.mybatis_resultMap.UserOp">
    <select id="getUser" parameterType="int" 
        resultMap="UserMap">
        select u.id as userId, userName, courseName, corp, c.id as courseId, teacherName
        from users u left join userCourse uc on u.id = uc.userId left join
        course c on c.id = uc.courseId left join teachers on teachers.id = c.id
        where u.id = #{id}
    </select>
    <resultMap type="net.gyronee.mybatis_resultMap.User" id="UserMap">
        <constructor>
            <idArg column="userId" javaType="int"/>
            <arg column="userName" javaType="String"/>
            <arg column="corp" javaType="String"/>
        </constructor>
        <collection property="courses" ofType="net.gyronee.mybatis_resultMap.Course">
            <id property="id" column="courseId"/>
            <result property="courseName" column="courseName"/>
            <association property="teacher" column="teacherId"
                javaType="net.gyronee.mybatis_resultMap.Teacher">
                <id property="id" column="teacherId"/>  
                <result property="teacherName" column="teacherName"/>
            </association>
        </collection>
    </resultMap>
</mapper>