JDBC详细总结_jdbc用户搜索

转载请注明出处：http://blog.csdn.net/github_39430101/article/details/77844465

JDBC原理

JDBC是什么

Java Database Connectivity: Java访问数据库的解决方案。
JDBC是Java应用程序访问数据库的里程碑式解决方案。Java研发者希望用相同的方式访问不同的数据库，以实现与具体数据库无关的Java操作界面。
JDBC定义了一套标准接口，即访问数据库的通用API，不同的数据库厂商根据各自数据库的特点去实现这些接口。

JDBC定义了一些接口

• 驱动管理 DriverManager
• 连接接口 Connection DatabasemetaData
• 语句对象接口 Statement PreparedStatement CallableStatement
• 结果集接口 ResultSet ResultMetaData

JDBC工作原理

JDBC只定义接口，具体实现由各个数据厂商负责。程序使用时只需要调用接口，实际调用的是底层数据库厂商的实现部分。

JDBC访问数据库的工作过程：

1. 加载驱动，建立连接
2. 创建语句对象
3. 执行SQL语句
4. 处理结果集
5. 关闭连接

Driver接口及驱动类加载

要使用JDBC接口，需要先将对应数据库的实现部分(驱动)加载进来
mysql:

Class.forName("com.mysql.jdbc.Driver");
1

这条语句的含义是：装载驱动类，驱动类通过static块实现在DriverManager中的自动注册

Connection接口

Connection接口负责应用程序对数据库的连接，在加载驱动之后，使用url、username、password三个参数，创建到具体数据库的连接。

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("url","username","password");
1
2

Statement接口

Statement接口用来处理发送到数据库的SQL语句对象，通过Connection对象创建。主要有三个常用方法：

Statement stmt = conn.createStatement();
//execute方法，如果执行的sql是查询语句且有结果集则返回true，如果是非查询语句或者没有结果集，返回false
boolean flag = stmt.execute(sql);
//执行查询语句，返回结果集
ResultSet rs = stmt.executeQuery(sql);
//执行DML语句，返回影响的记录数
int flag = stmt.executeUpdate(sql);
1
2
3
4
5
6
7

ResultSet接口

执行查询SQL语句后返回的结果集，由ResultSet接口接收。
常用处理方式：遍历/判断是否由结果

String sql = "select * from table";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
    System.out.println(rs.getInt("age")+rs.getString("name"));
}
1
2
3
4
5

查询的结果存放在ResultSet对象的一系列行中，指针的最初位置在行首，使用next()方法用来在行间移动，getXXX()方法用来取得字段的内容。

JDBC基础编程

连接管理

通过连接工具类获取连接
在工程中，通常编写一个访问数据库的工具类，此后所有访问数据库的操作，都从工具类中获取连接。
实现工具类的两种方式：

• 直接把数据配置写在工具类
• 把数据库配置写在一个properties属性文件里，工具类读入属性文件，逐行获取数据库参数。

通过属性文件维护连接属性：

#驱动类名
jdbc.driver = com.mysql.jdbc.Driver
#连接字符串
jdbc.url = jdbc:mysql://localhost/test
jdbc.user = wz
jdbc.password = 12345
1
2
3
4
5
6

完整示例

public class Demo {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String BD_URL = "jdbc:mysql://localhost/test";
    static final String USER = "username";
    static final String PASS = "password";
    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "select id,age from table";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                int id = rs.getInt("id");
                int age = rs.getInt("age");
                System.out.print("ID"+id);
                System.out.print("Age"+age);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch(SQLException e){
                e.printStackTrace();
        } catch(SQLException e2){
                e2.printStackTrace();
        } finally {
            try{
                if(stmt != null)
                stmt.close();
            } catch (SQLException e3){
            }
            try{
                if(conn != null)
                conn.close();
            } catch(SQLException e4){
                e4.printStackTrace();
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

连接池技术

为什么要使用连接池

数据库连接的建立及关闭资源消耗巨大。传统数据库访问方式：一次数据库访问对应一个物理连接，每次操作数据库都要打开、关闭该物理连接，系统性能严重损坏。这才出现了数据库连接池。
系统初始运行时，主动建立足够的连接，组成一个池，每次应用程序请求数据库连接时，无需重新打开连接，而是从池中取出已有的连接，使用完后，不再关闭，而是归还。

连接池中连接的释放与使用原则

• 应用启动时，创建初始化数目的连接
• 当申请时无连接可用或者达到指定的最小连接数，按增量参数值创建新的连接。
• 为确保连接池中最小的连接数的策略：
  
  1. 动态检查：定时检查连接池，一旦发现数量小于最小连接数，则补充相应的新连接，保证连接池正常运转
  2. 静态检查：空闲连接不足时，系统才检查是否达到最小连接数
     

通过DataSource获取连接

先通过属性文件获取连接池参数，然后加载这些参数，获得连接：

private static BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(driveClassName);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
Connection conn = dataSource.getConnection();
1
2
3
4
5
6

连接池参数

• 初始连接数
• 最大连接数
• 最小连接数
• 每次增加的连接数
• 超时时间
• 最大空闲连接
• 最小空闲连接

JDBC核心API

Statement

通过Connection对象创建Statement的方式

Connection.createStatement();
1

执行INSERT,UPDATE和DELETE等DML操作

Statement.executeUpdate();
1

执行SELECT

Statement.executeQuery();
1

通过Statement对象返回SQL语句执行后的结果集：

String sql = "select id,age from table";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
1
2
3

PreparedStatement原理

Statement主要用于执行静态SQL语句，即内容固定不变的SQL语句。Statement每执行一次都要对传入的SQL语句编译一次，效率较差。
某些情况下，SQL语句只是其中的参数有所不同，其余子句完全相同，适用于PreparedStatement。
PreparedStatement的另外一个好处就是预防sql注入攻击。
PreparedStatement是接口，继承自Statement接口。
使用PreparedStatement时，SQL语句已提前编译，三种常用方法 execute、executeQuery和executeUpdate已被更改，以使之不再需要参数。

PreparedStatement实例包含已事先编译的 SQL 语句，SQL 语句可有一个或多个 IN 参数，IN参数的值在 SQL 语句创建时未被指定。该语句为每个 IN 参数保留一个问号（“？”）作为占位符。
每个问号的值必须在该语句执行之前，通过适当的setInt或者setString等方法提供。
由于PreparedStatement对象已预编译过，所以其执行速度要快于 Statement 对象。因此，多次执行的 SQL 语句经常创建为PreparedStatement对象，以提高效率。
通常批量处理时使用PreparedStatement。

PreparedStatement pstmt = conn.prepareStatement("update table set name=? where id = ?");
pstmt.setLong(1,"小明");
pstmt.setInt(2,1001);
pstmt.executeUpdate();
1
2
3
4

SQL注入

先看这样一段SQL

String sql = "select * from t where username ='" + name +"' and password = '" + passwd + "'";
1

输入用户名和密码参数后，数据库接收到的完整sql语句是这样的：

select * from t where username = 'xiaoming' and password = '123';
1

如果用户输入的passwd参数是”or 1=1”，则数据库收到的语句是：

select * from t where username = 'scott' and password = '' or '1'='1';
1

此SQL语句的where语句条件为true。即用户不需要输入正确的账号密码，也能登录。这种现象称为SQL注入。

通过PreparedStatement防止SQL Injection,对JDBC而言，SQL注入攻击只对Statement有效，对PreparedStatement无效，因为PreparedStatement不允许在插入参数时改变SQL语句的逻辑结构。

ResultSet

结果集遍历

String sql = "select name, id from t";
    rs = stmt.executeQuery(sql);
    while (rs.next()) {
        name = rs.getString("name");
        id = rs.getInt("id");       
Date hiredate = rs.getDate("hiredate");
    }
rs.close();
1
2
3
4
5
6
7
8

JDBC高级编程

事物简介

事务(Transaction):数据库中保证交易可靠的机制。在JDBC中，事务默认是自动提交的。

事物四大特性：

• 原子性(Atomicity):事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
• 一致性(Consistency):事务在完成时，必须使所有的数据都保持一致状态
• 隔离性(Isolation):由并发事务所作的修改必须与任何其他并发事物所作的修改隔离
• 持久性:事务完成之后，它对于系统的影响是永久性的

JDBC事物API

• Connection.getAutoCommit() 获得当前事务的提交方式，默认为true
• Connection.setAutoCommit() 设置事务的提交属性，参数是true：自动提交；反之。
• Connection.commit() 提交事物
• Connection.rollback() 回滚事务

JDBC处理事务的通常模式

• 先将事务的自动提交关闭
• 执行事务中的若干SQL语句
• 事务提交:SQL失败则回滚
• 恢复JDBC的事务提交状态，释放资源。

try{
// 1.定义用于在事务中执行的SQL语句
String sql1 = "update account set amount = amount - " + amount + " where id = '" + from + "'";
String sql2 = "update account set amount = amount + " + amount + " where id = '" + to + "'";
autoCommit = con.getAutoCommit(); // 2.获得自动提交状态
con.setAutoCommit(false); // 3.关闭自动提交
stmt.executeUpdate(sql1); // 4.执行SQL语句
stmt.executeUpdate(sql2);
con.commit(); // 5.提交
con.setAutoCommit(autoCommit); // 6.将自动提交功能恢复到原来的状态
//其他语句;
}catch(SQLException e){
    conn.rollback();//异常时回滚
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

分页查询

利用数据库的分页SQL语句，实现在Java程序中数据表的分页。

String sql = "select * from (select  rownumrn, empno, ename, job,mgr, sal, from (select * from emp order by empno) ) where rn between ? and ?";
1

两个占位符分别是结果集的起点和终点，计算后，替代SQL中的占位符：

int begin = (page - 1) * pageSize + 1;
int end = begin +pageSize - 1;
1
2

page:返回第几页
pageSize:每页多少条

stmt = con.prepareStatement(sql);
stmt.setInt(1, begin);
stmt.setInt(2, end);
rs = stmt.executeQuery();
1
2
3
4

这种分页方式，每次只向数据库请求一页的数据量，内存压力小适合大数据量数据表。
另一种分页策略介绍：基于缓存的分页技术（也被称作假分页），一次性把数据全部取出来放在缓存中,根据用户要看的页数(page)和每页记录数(pageSize),计算把哪些数据输出显示，将可滚动结果集的指针移动到指定位置。
这种方式只访问数据库一次 , 第一次取数比较慢 , 以后每页都从缓存中取 , 比较快，比较适合小数据量 , 如果数据量大 , 对内存压力较大。

JDBC实现MySQL分页查询

select * from t limit begin,pageSize;
1

begin:从第几条开始显示
pageSize:每页多少条