jdbc笔记（完整）

JDBC：java database connection

一  Why JDBC

    我们一直说，计算机是用来帮助人们进行快速运算的。运算最根本的依据是数据。那么我们需要有操作的数据的来源，并且最终计算完成后，需要数据能够长久的保存，也就是数据能够持久化。
    
    持久化：                                 写入到

    狭义上讲  内存对象 -------------------------------》持久化介质(磁带，磁盘，文件等。。)

        如果直接写入文件中，可以在应用程序中使用I/O操作来处理数据，但是获取有结构的数据，并且对数据进行制定查找，修改比较复杂，不好管理。后来尝试给文件制定规则，用来存储数据。 我们有xml文件，xml文件可以作为小型数据库，也是可以存储信息，并且具有结构化良好，可读性高，自我描述功能强的优点。但是这种格式存储的数据非常简单，不提供给我们对于检索，排序 等更多优化的性能，可以说，他本身在处理大数据量的需求下，是不适用的。所以xml我们仅适用于做小数据量的信息传递。针对于xml文件我们也有专门的解析和构建方式如Sax和Dom。然后有了数据库的产生。数据库提供了更强有力的数据存储和分析能力，例如：数据索引、排序、查找、相关一致性等。当数据库产生之后，如果我们的应用程序需要得到数据库中数据的支持，就需要和数据库之间产生交互。各语言和数据库之间交互的技术营运而生。JDBC解决java操作的内存中的数据和数据库之间的交互问题。
    

二  What JDBC

    连接数据库的方式：
        1. ODBC：开放数据库连接（Open Database Connectivity，ODBC）是微软公司开放服务结构（WOSA，Windows Open Services Architecture）中有关数据库的一个组成部分，是数据库访问接口标准。开放数据库互连定义了访问数据库API的一个规范，这些API独立于不同厂商的DBMS，也独立于具体的编程语言（但是Microsoft的ODBC文档是用C语言描述的，许多实际的ODBC驱动程序也是用C语言写的。）
    ODBC规范后来被X/OPEN和ISO/IEC采纳，作为SQL标准的一部分。简单点说，ODBC是基于C语言实现的。提供了语言和数据库进行交互的一致性的接口，便于语言和和数据库通信以及语言对数据库的各种操作。
    
        2. JDBC： Java Data Base Connectivity,java数据库连接
    可以认为是java版本的odbc。JDBC是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成
       JDBC API主要分为两部分：
           ①针对应用开发人员，必须遵循的规则。 对数据库进行增删改查操作

           ②对于数据库驱动(实现类)开发人员必须遵循的规则。 可以让用户通过驱动连接数据库

    我们主要关注针对于应用开发人员的标准。目前jdbc的版本已经到达5.0的版本。
            
        

三  数据库驱动       

        数据库软件可以由各个数据库厂商提供，我们熟知的mysql,oracle,sqlserver,access,db2等等都是由不同的厂商提供的，既有不同的开发商开发，因此对于数据库软件的实现过程都不尽相同。定义应用程序和数据库之间进行交互的规则(标准)。这个标准称之为驱动（Driver）。开发商在开发数据库软件时提供针对于Driver标准的实现，应用开发人员再开发时，通过标准Driver的调用转换成对实际数据库Driver程序的调用。因此，每一款数据库软件在实现时都会提供相对于Driver标准的一些列接口和类的实现。更明确一点说我们需要获取人家提供的这个驱动包才能保证程序和数据库之间的连接。  

四   JDBC驱动

    四种JDBC驱动：
    ①JDBC-ODBC桥 加ODBC
        作为jdk1.1后的一部分，是sun.jdbc.odbc包的一部分， 将jdbc调用转化为odbc调用，性能低，要求用户在终端上安装相应驱动。 适用于快速的原型开发，没有提供JDBC驱动的数据库如Access
        java --->JDBC-ODBC桥---->ODBC --->数据库厂商代码

     ②部分java实现的本地JDBC驱动
        相对①有所提高，直接利用开发商提供的本地库来直接与数据库通信
        java ----type2 ----native Database library ----数据库厂商代码

    ③jdbc网络纯java驱动程序
        将JDBC调用转换为DBMS无关的网络协议，然后由某个服务器将其转换为相应的DBMS调用，具有最大的灵活性，通常由那些非数据库厂商提供，是四种类型中最小的。
        java --->type3 ---java middleware --->jdbc Driver ----->数据库厂商代码
       
    ④本地协议纯java驱动程序
        将jdbc调用直接转化成响应的DBMS调用，最高的性能，通过自己的本地协议直接与数据库引擎通信，具备internet的装配能力，目前java程序连接数据库都是用纯jdbc驱动。使用纯jdbc驱动需要先获取数据库厂商提供的驱动包:针对于oracle数据库，可以从安装目录下的app\oracle\product\10.2.0\server\jdbc\lib下找一个叫做ojdbc14.jar。 根据jdbc版本的更新换代，现在最高的jdbc版本为4.0的版本。可以单独获取jar包，例如获取ojdbc5.jar,ojdbc6.jar.

       

五  JDBC应用开发接口

    java.sql.*;  ------标准版
    javax.sql.*; ------企业版。  
  我们主要看java.sql.*包下的接口和类：
    
    1).Driver: 驱动的标准接口。
    2).DriverManager: 驱动管理器，管理多个加载的驱动程序;能够根据数据库的url标识，自动查找合适的数据库驱动类。
    3).Connection: 和数据库连接的对象形式，代表了一个和数据库的连接；

    4).Statement:  用以执行SQL语句                                                                                                                                           PreparedStatement(继承自Statement);

                c. CallableStatement(继承自PreparedStatement);
    5).ResultSet: 结果集，用来获取select语句返回的数据结果
    

六  JDBC的执行流程

    1.由DriverManager根据数据库的URL标识，自动识别查找注册给DriverManager的多各Driver对象，
    2.通过调用Driver对象的Connect方法和数据库之间建立起来连接（此时返回Connection对象）
    3.建立起来了解之后，由Statement对象负责搬运sql语句到数据库服务端执行，然后将执行结果搬回程序端
    4.处理程序端返回的ResultSet。
    

七  数据库的URL标识

     数据库url标识的存在主要是为了能够让DriverManager通过这个标示可以正确的识别使用的数据库，以及查找到正确的Driver对象，并且通过这个Driver可以和数据库之间建立起来连接。这个url由数据库厂商提供。
     基本格式：
        jdbc:subprotocol:subname
        example: jdbc:odbc:dbname (通过jdbc-odbc桥的方式实现的数据库连接url)
        oracle ：jdbc:oracle:thin:@localhost:1521:xe
        mysql : jdbc:mysql://localhost:3306/javademo?characterEncoding=UTF8    
    
----------------------------------------------------------------------------------------------------------------------------------
Model 2   USing    JDBC

一  JDBC编程步骤

  1.注册驱动/加载驱动
  2.获取连接
  3.创建Statement对象
  4.执行sql
  5.处理结果集
  6.关闭资源。

二  分步骤详解

    1.注册驱动(加载驱动)

        注册的方式：
          1.使用类加载器(使用反射的方式)
             Class.forName(driverName);
          2.实例化Driver
             Driver driver = new oracle.jdbc.driver.OracleDriver();
             DriverManager.registerDriver(driver);
          public class Driver extends NonRegisteringDriver implements java.sql.Driver {  
                   static {  
                    try {  
                        java.sql.DriverManager.registerDriver(new Driver());  
                    } catch (SQLException E) {  
                        throw new RuntimeException("Can't register driver!");  
                      }  
             }  
          3.加虚拟机参数jdbc.drivers

            -Djdbc.drivers=oracle.jdbc.driver.OracleDriver

   2.建立连接

        连接方式:
   1.DriverManager的getConnection方法
          getConnection(url);//没有用户名密码
          //将用户名密码存放在java.util.Properties对象中
          getConnection(url,properties);
          getConnection(url,user,passwd);      
          DriverManager中的getConnection其实也是调用的Driver.connect方法,因此可以直接使用。
    2.直接调用Driver.connect方法执行
          Driver d = new oracle.jdbc.driver.OracleDriver();

          d.connect(url,properties);

   3.创建Statement

        Statement：connection.createStatement();
          1.创建时不需要传递sql语句，但是执行时需要传递sql语句
          2.如果涉及到动态参数的传递，必须使用字符串拼接
        
        PreparedStatement：connection.prepareStatement(String sql);
          1.创建时就需要传递sql语句，执行的时候不需要传递sql语句
          2.如果涉及到动态参数的传递，可以使用字符串拼接，也可以使用？占位的形式，给?号传值使用的是 pstmt.setType(index,value); index从1开始
          3.提供预编译的功能,某种程度上可以避免sql注入的问题
          4.提前做语法检查，在给？赋值的过程中要求数据类型一定要匹配，这样在某种程度上可以避免因为数据类型不匹配而发生的异常

        CallableStatement:主要用来执行pl/sql的一些过程，函数等。

   4.执行sql语句

      ①execute:返回boolean类型的值，代表是否有结果集返回(如果执行select操作，是有ResultSet的，返回值为true)
      ②executeUpdate：返回int类型的值，代表的是，操作执行完成后，受影响的数据库的行计数(针对于insert,update,delete)
      ③executeQuery:返回的是ResultSet。ResultSet:类似于指针或者游标的东西，里边保存的不是所有的结果，而是指向结果集的正上方。所以如果一旦连接关闭那ResultSet将取不到值（sekect操作的结果）
   

   5.处理结果:有结果集，处理结果集

    ResultSet
    next(),每执行一次，向下移动一次，如果有值，返回true,如果没值返回false
    while(rs.next()){
        rs.getType(index/columnName);   如果传的是index,那么索引是从1开始的。
        例如：
            执行select id,last_name from s_emp;
            那么1代表的就是id,依次类推
    }
    

   6.关闭资源

    先开的后关

   

package jdbc.day01;

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

public class SimpleJDBC {
	private static Connection conn;
	private static Statement stmt;
	private static ResultSet rs;
	public static void main(String []args) throws SQLException{
		try{
			//注册驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			//获取连接    getConnection方法的参数url,userName,userPassword
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hjh","hjh");
			
			//创建Statement对象，创建sql语句
			String sql = "select * from s_emp";
			String sql2 = "delete from emp_41";
			stmt = conn.createStatement();
			
			//System.out.println("excute:"+stmt.executeUpdate(sql2));//4
			System.out.println("excuteUpdate:"+stmt.executeUpdate(sql2));//4
			
			//处理结果集    ResultSet只会在select语句才有
			rs = stmt.executeQuery(sql);
			
			//rsִ�У�next�ж��Ƿ�����ݣ�get��ȡ���
			while(rs.next()){
				System.out.println("id:"+rs.getInt("id")+"  name:"+rs.getString(2)+"  dept_id:"+rs.getInt("dept_id"));
			}
				
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			//关闭资源
			if(rs!=null)rs.close();
			if(stmt!=null)stmt.close();
			if(conn!=null)conn.close();
		}
	}
}

   

三  Statement和PreparedStatement

     Statement：
        1.创建时不需要传递sql语句，但是执行时需要传递sql语句
        2.如果涉及到动态参数的传递，必须使用字符串拼接
    PreparedStatement：
        1.创建时就需要传递sql语句，执行的时候不需要传递sql语句
        2.如果涉及到动态参数的传递，可以使用字符串拼接，也可以使用？占位的形式。 要求在执行sql语句之前，给?号传值。给？传值使用的是  pstmt.setType(index,value); index代表给第几个？赋值。index从1开始
        3.提供预编译的功能,某种程度上可以避免sql注入的问题
        4.提前做语法检查，在给？赋值的过程中要求数据类型一定要匹配，这样在某种程度上可以避免因为数据类型不匹配而发生的异常
        

四  sql注入

        SQL注入攻击是指利用设计上的漏洞，在目标服务器上运行Sql语句以及进行其他方式的攻击,动态生成Sql语句时没有对用户输入的数据进行验证，是Sql注入攻击得逞的主要原因。对于JDBC而言,SQL注入攻击只对Statement有效,对PreparedStatement是无效的,这是因为PreparedStatement不允许在不同的插入时间改变查询的逻辑结构。
    
    例如：
        1.定义Sql语句，select　* from test where name = '';
        如果设置由用户输入用户名  test' or ‘1’ = '1 ,则sql语句变为 :select * from test where name = 'test' or '1'='1' ; 这条语句结果恒为true。
            
        2.如果设置 select * from test where name = '' and password = '';
            如果由用户输入查询条件 (“jack”,”123 or 1 = 1”) 则sql语句编程select * from test where name = ‘jack’ and passsword = 123 or 1 = 1;
            
    现在大家已经对SQL Injection的攻击有了初步的了解了，接下让我们学习如何防止SQL Injection。
    总的来说有以下几点：
    1.永远不要信任用户的输入，要对用户的输入进行校验，可以通过正则表达式，或限制长度，对单引号进行转换等。
    2.永远不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取。
    3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
    4.不要把机密信息明文存放，请加密或者hash掉密码和敏感的信息。
    5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装，把异常信息存放在独立的表中。
    
    

五  批处理

    业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。
    
    实现批处理有两种方式：第一种方式：Statement.addBatch(sql)
                                                第二种方式：PreparedStatement.addBatch()
                          
    执行批处理SQL语句    executeBatch()方法：执行批处理命令
                                            clearBatch()方法：清除批处理命令
    
    采用Statement.addBatch(sql)方式实现批处理：
            优点：可以向数据库发送多条不同的ＳＱＬ语句。

            缺点：?SQL语句没有预编译。

    statement = connection.createStatement();
    String sql = “insert into student values(1,’tom’)”
    String sql2 = “update student set id = 2 ”;
    statement.addBatch(sql);
    statement.addBatch(sql2);

    statement.executeBatch()；

  当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如

      Insert into user(name,password) values(‘aa’,’111’);
      Insert into user(name,password) values(‘bb’,’222’);
      Insert into user(name,password) values(‘cc’,’333’);
      Insert into user(name,password) values(‘dd’,’444’);
    
    采用PreparedStatement.addBatch()实现批处理
            优点：发送的是预编译后的SQL语句，执行效率高。
            缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。
    实例代码
    for(int i=0;i<50000;i++) {
        ps.setInt(1,i);
        ps.setString(2,"aaa"+i);
        ps.setInt(3,1000+i);
        ps.addBatch();
        if(i%5000 == 0) {
            System.out.println(i);
            ps.executeBatch();
            ps.clearBatch();
        }
    }
    ps.executeBatch();
    
    

六  事务控制

    jdbc是自动事务提交的，因此，执行一条sql语句之后，sql语句可以自动将数据插入到数据库，并且事务自动提交。如果不希望sql事务自动提交，设置：
        connection.setAutoCommint(false);
       如此需要在用户手动的提交事务。connection.commit();
       如果需要保存回滚点，可以使用:Savepoint a2  = connection.setSavepoint("a1");
       如果需要回滚事务到某一指定保存点，可以使用: Savepoint a2  = connection.rollback(a2);
       如果希望事务回滚，可以使用：connection.rollback();
       回滚的操作一般不用jdbc处理,建议oracle手动控制
    

七  三层框架

    通常意义上的三层架构就是将整个业务应用划分为：表现层（UI）、业务逻辑层（BLL）、数据访问层（DAL）。区分层次的目的即为了“高内聚，低耦合”的思想。

原理：
    1：数据访问层：主要是对原始数据（数据库或者文本文件等存放数据的形式）的操作层，而不是指原始数据，也就是说，是对数据的操作，而不是数据库， 具体为业务逻辑层或表示层提供数据服务．
    2：业务逻辑层：主要是针对具体的问题的操作，也可以理解成对数据层的操作，对数据业务逻辑处理，如果说数据层是积木，那逻辑层就是对这些积木的搭建。
    3：表示层：主要表示用户的操作终端，如果逻辑层相当强大和完善，无论表现层如何定义和更改，逻辑层都能完善地提供服务。

具体的区分方法
    1：数据访问层：主要看你的数据层里面有没有包含逻辑处理，实际上他的各个函数主要完成各个对数据文件的操作。而不必管其他操作。
    2：业务逻辑层：主要负责对数据层的操作。也就是说把一些数据层的操作进行组合。
    3：表示层：主要对用户的请求接受，以及数据的返回，为客户端提供应用程序的访问。位于最外层（最上层），最接近用户。用于显示数据和接收用户输入的数据，为用户提供一种交互式操作的界面。

优缺点
    优点：
          1、开发人员可以只关注整个结构中的其中某一层；
          2、可以很容易的用新的实现来替换原有层次的实现；
          3、可以降低层与层之间的依赖；
          4、有利于标准化；
          5、利于各层逻辑的复用。
          6、结构更加的明确
          7、在后期维护的时候，极大地降低了维护成本和维护时间

    缺点：
         1、降低了系统的性能。这是不言而喻的。如果不采用分层式结构，很多业务可以直接造访数据库，以此获取相应的数据，如今却必须通过中间层来完成。
         2、有时会导致级联的修改。这种修改尤其体现在自上而下的方向。如果在表示层中需要增加一个功能，为保证其设计符合分层式结构，可能需要在相应的业务逻辑层和数据访问层中都增加相应的代码。
         3、增加了开发成本。