JDBC

JDBC 工作的原理
加载驱动，建立连接
创建语句对象
执行SQL 语句
处理结果集
关闭连接

mysql
JDBC_DRIVER=com.mysql.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/RUNOOB
USER=root
PASSWORD=hiroot

package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlDemo {
    public static void main(String[] args) {
        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        String DB_URL = "jdbc:mysql://172.29.12.158:3306/emp";
        String USER = "root";
        String PASSWORD = "hiroot";
        String sql = "SELECT * FROM emp";
        Connection conn = null;
        Statement stmt = null;
        try {
            System.out.println("注册驱动...");
            Class.forName(JDBC_DRIVER);
            // 注册mysql的驱动
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            // 获取连接
            System.out.println("实例化查询...");
            stmt = conn.createStatement();
            //
            ResultSet set = stmt.executeQuery(sql);
            while (set.next()) {
                System.out.println(set.getInt(1) + ":" + set.getString(2));
            }

        } catch (ClassNotFoundException e) {
            System.out.println("mysql 驱动加载异常");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("连接mysql 异常");
            e.printStackTrace();
        }
    }
}

Statement 接口

boolean flag=stmt.execute(sql);
// 可以执行任何sql 但是常用来DDL 返回true 表示有结果集 false 没有结果集创建失败异常
ResultSet rs = stmt.executeQuery(sql);
//DQL SELECT
int flag = stmt.executeUpdate(sql);
//DML

处理SQL 执行结果
execute(ddl) 如果没有异常则成功
execteUpdate(dml) 返回数字表示更新数量
executeQuery(dql) 返回ResultSet结果集使用for遍历查询失败抛出异常

jdbc:mysql://127.0.0.1:3306/tc2_astc_production?autoReconnect=true&user=root&password=hiroot&useUnicode=true&characterEncoding=UTF-8&tcpKeepAlive=true

package mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlDemo2 {
    public static void main(String[] args) {
        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        String DB_URL = "jdbc:mysql://172.29.12.158:3306/emp";
        String USER = "root";
        String PASSWORD = "hiroot";
        String sql = "CREATE TABLE emp_3(id int,name varchar(100))";
        Connection conn = null;
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            Statement stmt = conn.createStatement();
            boolean flag = stmt.execute(sql);
            System.out.println(flag);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Properties
为了读取properties 文件设计的
底层就是文本文件的IO
Properties 实现了Map
内部是散列表限定了key 和value 都是String类型
方式load 流将文件读取为散列表
getProperty(key) 查询value

package mysql;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DbUtils {
    static String driver;
    static String url;
    static String user;
    static String password;

    static {
        Properties conf = new Properties();
        InputStream in = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
        try {
            conf.load(in);
            driver = conf.getProperty("jdbc.driver");
            url = conf.getProperty("jdbc.url");
            user = conf.getProperty("jdbc.user");
            password = conf.getProperty("jdbc.password");
        } catch (IOException e) {
            System.out.println("读取配置文件异常");
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() {

        try {

            Class.forName(driver);
            return DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            System.out.println("mysql 驱动加载失败");
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (SQLException e) {
            System.out.println("连接mysql失败");
            e.printStackTrace();
            throw new RuntimeException(e);
        }

    }

    public static void close(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlDemo3 {
    public static void main(String[] args) {
        Connection con = DbUtils.getConnection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM real_sse LIMIT 0,10");
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();// 释放查询结果 ，建议用完就释放
            stmt.close();// 释放语句对象，建议用完就释放
        } catch (SQLException e) {
            System.out.println("执行SQL异常");
            e.printStackTrace();
        } finally {
            DbUtils.close(con);
        }
    }
}

使用连接池
数据库的连接和关闭资源消耗巨大

apache dbcp

在当前项目基础上，再新建一个jar包，并引入我们刚刚自己新建编译的dbcp源码包（以后统一简称为本地dbcp包），此时，我们就可以着手实现使用dbcp连接池的应用了。像所有的连接池一样，在使用之前，我们要先来看一下都有哪些配置项。在dbcp官网的api页面上，各个配置项都有详细的描述，我们就结合官网的内容，简单总结一下。

按照功能类型，dbcp的配置项可以大致分为以下几类：

核心基础配置
username
password
url
driverClassName
有点JDBC编程基础的人一看就知道上面这几个参数分别是干啥用的了，没有它们，“连接池”中的“连接”，就无从谈起了。它们是dbcp的基本配置，其他属性都有默认值，唯独这四个属性一定要由用户指定，否则就dbcp就无法使用。话说回来，一个连接池不论性能好坏，功能多少，它最重要的作用，就是得到一个java.sql.Connection对象。

connection属性的配置
connectionProperties：连接属性，以键值对的形式将下面这些具体的属性串起来
defaultAutoCommit：是否默认自动提交
defaultReadOnly：是否为只读
defaultTransactionIsolation：默认的事务隔离级别
defaultCatalog：connection的默认路径
这部分配置的作用都是标记从池中获取的connection应该具备的属性，而它们是否生效，就要看具体的JDBC驱动是不是支持了。

池属性的配置
initialSize
maxActive
maxIdle
minIdle
maxWait
又是一组关键的配置，这组配置的作用控制连接池的容量。

一个稳定的连接池，其对系统资源的占用应该是稳定在一个固定的范围内的，maxActive、maxIdle和minIdle的这三个参数的作用正在于此。首先，maxActive的意思是，池中最多可容纳的活着的连接数量，它是整个连接池的边界，当池中活着的连接达到这个数值时，dbcp将不会再新建connection，而是等待获取其他线程释放的。maxIdle的意思是连接池最多可以保持的连接数，应用场景就是dbcp会定时回收池中那些空闲连接（已激活但未使用的连接），直到池中的数量减少到maxIdle为止。minIdle是maxIdle的反义词，及连接池中最少保持的连接。maxWait定义了获取连接的等待时间，如果超过这个时间则抛出异常（默认配置），initialSize则定义了dbcp在初始化时，新建的连接数量。

高可用属性的配置
connectionInitSqls
testOnBorrow
testOnReturn
validationQuery
validationQueryTimeout
在连接池的使用过程中保持高可用，是一个优秀的连接池必不可少的素质，那如何做到这一点呢，dbcp给出的答案就在上面这些配置项上。

connectionInitSqls是在一个connection被创建之后调用的一组sql，这组sql可用来记录日志，也可以用来初始化一些数据，总之，经过connectionInitSqls之后的connection一定是正常可用的。testOnBorrow和testOnReturn的关注点则在connection从池中“取出”和“归还”，这两个关键的动作上，当他们被设置为true时，在取出和归还connection时，都需要完成校验，如果校验不通过，这个connection将被销毁。校验的sql由validationQuery定义，且定义的sql语句必须是查询语句，而且查询至少一列。validationQueryTimeout定义的是校验查询时长，如果超过这个时间，则认定为校验失败。

除了上述配置，dbcp在运行时还在内部维护了一个“清理器”（Eviction），主要用于销毁那些已被创建，但长时间未被使用的连接，Eviction在运行的时候，会用到下列属性：

testWhileIdle：清楚一个连接时是否需要校验
timeBetweenEvictionRunsMillis：Eviction运行的时间周期
numTestsPerEvictionRun：Eviction在运行时一次处理几个连接
PreparedStatements是可以缓存是，尤其在一些支持游标的数据库中（Oracle、SQL Server、DB2、Sybase），启用PreparedStatements缓存和不启用直接的性能可能相差一个数量级。dbcp配置PreparedStatements缓存主要用到以下两个配置。

poolPreparedStatements：是否缓存PreparedStatements
maxOpenPreparedStatements：缓存PreparedStatements的最大个数

package mysql;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class DbUtils2 {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static int initSize;
    private static int maxActive;
    private static Properties conf;
    private static BasicDataSource bs;

    static {
        try {
            conf = new Properties();
            conf.load(DbUtils2.class.getClassLoader().getResourceAsStream("db.properties"));
            driver = conf.getProperty("jdbc.driver");
            url = conf.getProperty("jdbc.url");
            username = conf.getProperty("jdbc.user");
            password = conf.getProperty("jdbc.password");
            bs = new BasicDataSource();
            bs.setDriverClassName(driver);
            bs.setUrl(url);
            bs.setUsername(username);
            bs.setPassword(password);
            bs.setInitialSize(10);
            bs.setMaxActive(500);

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = bs.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException();
        }

    }

    public static void close(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

package mysql;

import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class MysqlDemo5 {
    public static void main(String[] args) {
        Connection conn = DbUtils2.getConnection();
        try {
            String sql = "SELECT * FROM real_sse LIMIT ?,?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, 0);
            ps.setInt(2, 10);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DbUtils2.close(conn);
        }

    }
}

ResultSetMetaData 数据结果集的元数据
喝查询出来的结果集相关从结果集ResultSet中获取

package mysql;

import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class MysqlDemo5 {
    public static void main(String[] args) {
        Connection conn = DbUtils2.getConnection();
        try {
            String sql = "SELECT * FROM real_sse LIMIT ?,?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, 0);
            ps.setInt(2, 10);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
                java.sql.ResultSetMetaData rsmd = rs.getMetaData();
                System.out.println(rsmd.getColumnCount());
                System.out.println(rsmd.getColumnName(1));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DbUtils2.close(conn);
        }

    }
}

可滚动结果集
（不建议使用，性能差.不做介绍）

事务处理

事务特性 ACID
原子性事务必须是原子工作单元（就是最小的单元，不可以在分的）
一致性事务完成的时候，数据必须是一致的（A+10 B-10）
隔离性事务在进行的时候不能被其他打扰
持久性事务完成后，影响是永久性的

JDBC
Connection.setAutoCommit() 设置书屋的提交属性参数是true（自动提交）和 false（取消自动提交）；

package mysql;

import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class MysqlDemo5 {
    public static void main(String[] args) {
        Connection conn = DbUtils2.getConnection();
        try {
            conn.setAutoCommit(false);
            String sql = "INSERT INTO emp(name) VALUES(?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "zx");
            ps.executeUpdate();
            conn.commit();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        } finally {
            DbUtils2.close(conn);
        }

    }
}

批量更新

1、执行一批SQL
2、1个执行计划一批参数

批量更新API
addBatch(String sql)
Statement 类的方法可以将多条sql 语句添加Statement 对象的SQL 语句列表中

addBatch()
PreparedStatement 类的方法可以将多条预编译的sql 语句添加到PreparedStatement对象的SQL语句列表中

executeBatch()
所有的SQL 发送给数据库进行处理

clearBatch()
清空当前SQL语句列表

public static void main(String[] args) throws Exception{
    Connection con = DBUtils.openConnection();
    String sql = "insert into emp2(empno,ename) values(?,?)";
    PreparedStatement stmt = con.prepareStatement(sql);
    //批量插入
    for(int i =5000;i<=6000;i++){
        stmt.setInt(1, i);
        stmt.setString(2, "M"+i);
        stmt.addBatch();//添加批处理
    }
    stmt.executeBatch();//执行批处理
    con.close();
}

返回 int[]

public static void main(String[] args) throws Exception{
    Connection con = DBUtils.openConnection();
    String sql = "insert into emp100(id,name) values(?,?)";
    PreparedStatement stmt = con.prepareStatement(sql);
    //批量插入
    for(int i =1;i<=2000000;i++){
        stmt.setInt(1, i);
        stmt.setString(2, "M"+i);
        stmt.addBatch();//添加批处理
    if(i%20000==0){
        stmt.executeBatch();
    }
    }
    stmt.executeBatch();//执行批处理
    con.close();
}

防止批量过出现OutOfMemory错误
如果PreparedStatement 对象中的缓存列表包含过多的待处理数据可能会产生OutOfMemory错误分段处理缓存列表

ps.clearBatch()//定次数清空

在 INSERT INTO de 时候就可以获取插入时候的ID自增主键

conn.prepareStatement(sql,{'id'});
ps.execteUpdate();
ResultSet rs = ps.getGFeneratedKeys();
System.out.println(rs.getInt(1));

DAO
数据访问对象
建立在数据库和业务层之间封装所有对数据库的访问
目的：数据访问逻辑和业务逻辑分开

为了建立一个健壮的java 应用，需要将素有对数据源的访问操作抽象封装在一个公共API中
1建立一个接口接口中定义了应用程序中将会用到的所有事物方法
2 建立接口的实现类实现接口对用的所有方法和数据库直接交互

在应用程序中当需要和数据源交互时就使用DAO接口不涉及任何数据库的具体操作

实体对象：
DAO 层需要定义对数据库中表的访问
对象关系映射（ORM）描述对象和数据表之间的映射将Java程序中的对象对应到关系数据库的表中
表和类对应
表中的字段和类的属性对应
记录和对象对应

写一个DAO
写一个实体类来描述数据库中的表
例如数据库中有一个表是 user 表
就要写一个User 类
写一个DAO
就要写一个 UserDao类来操作User类也就是操作user 表
例如查询user 记录插入user 记录删除记录

写一个 DAO 接口

posted on 2018-10-10 11:29 浪漫的偷笑阅读(171) 评论(0) 编辑收藏举报