JDBC简介

JDBC(Java Data Base Connectivity, Java数据库连接)是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

Why JDBC?
各种不同的数据库有着各自的API接口，要想访问各种数据库，我们必须实现不同的访问方法。

而JDBC提供了一套标准，各数据库厂商只需要实现JDBC的标准接口，然后我们就可以通过JDBC的统一接口来访问各种数据库了。

然后对于程序员来说，就不需要再关注和学习各种数据库的访问API了，我们只需要通过JDBC的统一接口来访问就可以了。

JDBC编程步骤

1. Load the Driver
   
   1. Class.forName() | Class.forName().newInstance() | new DriverName()
   2. 实例化时自动向DriverManager注册，不需要显示调用DriverManager.registerDriver方法
2. Connect to the DataBase
   
   1. DriverManager.getConnection()
3. Execute the SQL
   
   1. Connection.CreateStatement()
   2. Statement.executeQuery()
   3. Statement.executeUpdate()
4. Retrieve the result data
   
   1. 循环取得结果 while(rs.next())
5. Show the result data
   
   1. 将数据库中的各种类型转换为Java中的类型（getXXX）方法
6. Close
   
   1. close the resultset. / close the statement / close the connection

示例1：

package javastudy.basics;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JDBCTest {
    public static void main(String[] args) {
        String driver = "org.netezza.Driver";
        String url = "url";
        String userName = "userName";
        String pwd = "pwd";
        String sql = "select * from table";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, pwd);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while(rs.next()) {
                System.out.println(rs.getString("univ"));
                System.out.println(rs.getString("secId"));
                System.out.println(rs.getString("perfId"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(JDBCTest.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(JDBCTest.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                    rs = null;
                }

                if(stmt != null) {
                    stmt.close();
                    stmt = null;
                }

                if(conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException ex) {
                Logger.getLogger(JDBCTest.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

示例2： 命令行输入参数

package javastudy.basics;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JdbcPsptTest {
    public static void main(String[] args) {
        if(args.length != 3) {
            System.out.println("The arguments input is not right, please retype!");
            System.exit(-1);
        }

        String univCode = args[0];
        String secId = args[1];
        String perfId = args[2];

        String driver = "org.netezza.Driver";
        String url = "url";
        String userName = "userName";
        String pwd = "pwd";

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, pwd);
            pstmt = conn.prepareStatement("insert into Table (?, ?, ?)");
            pstmt.setString(1, univCode);
            pstmt.setString(2, secId);
            pstmt.setString(3, perfId);
            pstmt.executeUpdate();

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(JdbcPsptTest .class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(JdbcPsptTest .class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                    rs = null;
                }

                if(pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }

                if(conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException ex) {
                Logger.getLogger(JdbcPsptTest .class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

示例3：批处理

package javastudy.basics;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JdbcBatchTest {
    public static void main(String[] args) {
        String driver = "org.netezza.Driver";
        String url = "url";
        String userName = "userName";
        String pwd = "pwd";

        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, pwd);
            stmt = conn.createStatement();
            stmt.addBatch("insert into table values ('FO1', 'secId1', 'perfId1')");
            stmt.addBatch("insert into table values ('FO2', 'secId2', 'perfId2')");
            stmt.addBatch("insert into table values ('FO3', 'secId3', 'perfId3')");
            stmt.executeBatch();

            pstmt = conn.prepareStatement("insert into table values (?, ?, ?)");
            pstmt.setString(1, "FO1");
            pstmt.setString(2, "SECID1");
            pstmt.setString(3, "perfId1");
            pstmt.addBatch();

            pstmt.setString(1, "FO2");
            pstmt.setString(2, "SECID2");
            pstmt.setString(3, "perfId2");
            pstmt.addBatch();

            pstmt.setString(1, "FO3");
            pstmt.setString(2, "SECID3");
            pstmt.setString(3, "perfId3");
            pstmt.addBatch();            
            pstmt.executeBatch();

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(JdbcBatchTest .class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(JdbcBatchTest .class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {               
                if(stmt != null) {
                    stmt.close();
                    stmt = null;
                }

                if(pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }                

                if(conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException ex) {
                Logger.getLogger(JdbcBatchTest .class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

事务：
就是一组原子操作单元，从数据库的角度来说，就是一组SQL指令，要么全部执行成功，要么就撤销所有操作。

为什么需要事务：
例如，A账户转账1000元到B账户，那么首先从A账户扣除1000元，然后在B账户增加1000元。但是如果在A账户扣完款后，出现网络故障，B账户转入1000元失败，整个业务结束。所以必须做出控制，在整个过程中只要有一个转账业务失败就撤销所有操作，这样就保证了整个业务的完整性。

示例：

package javastudy.basics;

import java.sql.*;

public class TestTransaction {
    public static void main(String[] args) {

            String driver = "org.netezza.Driver";
            String url = "jdbc:netezza://host:5480/db;loginTimeout=60000";
            String userName = "userName";
            String pwd = "pwd";

            Connection conn = null;
            Statement stmt = null;

        try {    
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, pwd);
            conn.setAutoCommit(false);
            stmt.addBatch("insert into table values ('FO1', 'secId1', 'perfId1')");
            stmt.addBatch("insert into table values ('FO2', 'secId2', 'perfId2')");
            stmt.addBatch("insert into table values ('FO3', 'secId3', 'perfId3')");
            stmt.executeBatch();            
            conn.commit();
            conn.setAutoCommit(true);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();

            if(conn != null) {
                try {
                    conn.rollback();
                    conn.setAutoCommit(true);
                } catch (SQLException ex1) {
                    ex.printStackTrace();
                }
            }
        } finally {
            try {
                if(stmt != null) {
                    stmt.close();
                }

                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
    }    
}