JDBC编程

JDBC是数据库连接纽带,统一数据库接口。

JDBC编程步骤：

1.Load the Driver
　　<1.Class.forName()|Class.forname().newinstance()|new DirverName()
　　<2.实例化时自动向DirverManager注册，不需显式调用DriverManger.registerDriver()方法

2.Connect to the DataBase
　　<1.DriverManager.getConnection()

3.Execute the SQL
　　<1.Connection CreateStatement()
　　<2.Statement.excuteQuery()　　--用于select语句
　　<3.Statement.executeUpdate()　　--用于insert,update,delete语句
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

JDBC连接oracle例子程序：

import java.sql.*;

public class TestJDBC {

    public static void main(String[] args) {
        ResultSet rs = null;
        Statement stmt = null;
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //new oracle.jdbc.driver.OracleDriver();另一种new Driver的方式
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from dept");
            while(rs.next()) {
                System.out.println(rs.getString("deptno"));
                System.out.println(rs.getInt("deptno"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } 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 e) {
                e.printStackTrace();
            }
        }
    }

}

利用命令行向oracle中插入记录：

import java.sql.*;

public class TestDML2 {

    public static void main(String[] args) {
        if(args.length != 3) {
            System.out.println("Parameter Error! Please Input Again!");
            System.exit(-1);
        }
        
        int deptno = 0;
        
        try {
            deptno = Integer.parseInt(args[0]);
        } catch (NumberFormatException e) {
            System.out.println("Parameter Error! Deptno should be Number Format!");
            System.exit(-1);
        }
        
        String dname = args[1];
        String loc = args[2];
        
        Statement stmt = null;
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123");
            stmt = conn.createStatement();
            String sql = "insert into dept values (" + deptno + ",'" + dname + "','" + loc + "')";    //注意sql格式
System.out.println(sql);    //将打印出的语句放到sql中执行，看哪里出错是最常利用的方法
            stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if(conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

 

用PrepareStatement灵活的指定sql（防止sql注入的一种方法）：

import java.sql.*;


public class TestPreStmt {

    public static void main(String[] args) {
        if(args.length != 3) {
            System.out.println("Parameter Error! Please Input Again!");
            System.exit(-1);
        }
        
        int deptno = 0;
        
        try {
            deptno = Integer.parseInt(args[0]);
        } catch (NumberFormatException e) {
            System.out.println("Parameter Error! Deptno should be Number Format!");
            System.exit(-1);
        }
        
        String dname = args[1];
        String loc = args[2];
        
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123");

            pstmt = conn.prepareStatement("insert into dept values (?, ?, ?)");    //用prepareStatement灵活指定sql,?为占位符

            pstmt.setInt(1, deptno);    //设定第1个值为deptno
            pstmt.setString(2, dname);    //设定第2个值为dname
            pstmt.setString(3, loc);    //设定第3个值为loc

            pstmt.executeUpdate();    //注意与上面的stmt.executeUpdate(sql);不同

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }
                if(conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

 

JAVA通过JDBC调用存储过程（通过CallableStatement）：

先在ORACLE中定义这个存储过程:

create or replace procedure P
 (v_a in number,v_b number,v_ret out number,v_temp in out number)
is

begin
    if(v_a > v_b) then
        v_ret := v_a;
    else
        v_ret := v_b;
end if;
    v_temp := v_temp +1;
end;
/

 

import java.sql.*;
public class TestProc {

    public static void main(String[] args) throws Exception {
        
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123");

        CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");    /*通过conn.prepareCall创建CallableStatement对象并调用存储过程p，该存储过程有四个参数，第1、2个参数为输入参　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　数，第三个参数是输入输出参数，第四个参数为输出参数*/
        cstmt.registerOutParameter(3, Types.INTEGER);    //声明第三个参数是输入参数，且类型是Types.INTEGER
        cstmt.registerOutParameter(4, Types.INTEGER);    //声明第四个参数是输入参数，且类型是Types.INTEGER

        cstmt.setInt(1, 3);    //设置第一个参数值
        cstmt.setInt(2, 4);    //设置第二个参数值
        cstmt.setInt(4, 5);    //设置第三个参数值

        cstmt.execute();    //调用存储过程

        System.out.println(cstmt.getInt(3));
        System.out.println(cstmt.getInt(4));
        cstmt.close();
        conn.close();
    }

}

 

JDBC批处理：

import java.sql.*;
public class TestBatch {


    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "abc123");

        //第一种批处理方法
        /*
        Statement stmt = conn.createStatement();    //一个Statement只可以处理一条aql语句
        stmt.addBatch("insert into dept values (51, '500', 'haha')");    //将语句加入批处理
        stmt.addBatch("insert into dept values (52, '500', 'haha')");    //将语句加入批处理
        stmt.addBatch("insert into dept values (53, '500', 'haha')");    //将语句加入批处理
        stmt.executeBatch();    //Statement一次执行批处理
        stmt.close();
        */

        //第二种批处理方法        
        PreparedStatement ps = conn.prepareStatement("insert into dept values (?, ?, ?)");
        ps.setInt(1, 61);
        ps.setString(2, "haha");
        ps.setString(3, "bj");
        ps.addBatch();
        
        ps.setInt(1, 62);
        ps.setString(2, "haha");
        ps.setString(3, "bj");
        ps.addBatch();
        
        ps.setInt(1, 63);
        ps.setString(2, "haha");
        ps.setString(3, "bj");
        ps.addBatch();
        
        ps.executeBatch();
        ps.close();
        
        conn.close();

    }

}

处理Tracnsaction:

import java.sql.*;
public class TestTransaction {


    public static void main(String[] args) {
        
        Connection conn = null;
        Statement stmt = null;
        
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger");
            
            conn.setAutoCommit(false);    //取消自动提交
            stmt = conn.createStatement();    
            stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
            stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
            stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
            stmt.executeBatch();
            conn.commit();    //自己提交
            conn.setAutoCommit(true);    //恢复现场
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch(SQLException e) {
            
            e.printStackTrace();
            
            try {
                if(conn != null)
                {
                    conn.rollback();    //有(SQLException时rollback
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            try {
                if(stmt != null)
                    stmt.close();
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        

    }

}