JDBC操作数据库汇总

JDBC（Java Database Connectivity）

一、SQL语言回顾

1、Select select * from T where …

2、Insert insert into T values(…)

3、Create create table T(…)

4、Delete delete from T where…

5、Update update T set t1=… and t2=.. where t3=…

6、Drop drop table T

 

二、JDBC编程步骤

 1、Load the Driver

Class.forName( )|Class.forName( ).newInstance( )|new DriverName( )

实例化时自动向DriverManager注册，不需显式调用DriverManager.registerDriver方法

2、Connect to the DataBase

 DriverManager.getConnection( )

3、Execute the SQL

 Connection.CreateStatement( )

 Statement.executeQuery( )

 Statement.executeUpdate( )

4、Retrieve the result data

 循环取得结果while(rs.next( ))

5、Show the result data

 将数据库中的各种类型转换为Java中的类型(getXXX)方法

6、Close

 Close the resultset ./ close the statement / close the connection

 

三、JDBC操作oracle数据库

1、 JDBC连接oracle数据库：

import java.sql.*;

publicclass TestJDBC {

   public static void main(String[] args) throws ClassNotFoundException, SQLException {

      Class.forName("oracle.jdbc.driver.OracleDriver");

      //new oracle.jdbc.driver.OracleDriver();

      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

   }

}

 

2、 向数据库中取字段，遍历数据库，并显示出相应字段：

import java.sql.*;

publicclass TestJDBC {

   public static void main(String[] args) throws ClassNotFoundException, SQLException {

      Class.forName("oracle.jdbc.driver.OracleDriver");

      //new oracle.jdbc.driver.OracleDriver();

      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

      Statement stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("select * from dept");

      while(rs.next()) {

          System.out.println(rs.getString("deptno"));

          System.out.println(rs.getInt("deptno"));

      }

      rs.close();

      stmt.close();

      conn.close();

   }

}

 

3、 捕捉相关异常，完善JDBC编程：

import java.sql.*;

publicclass TestJDBC {

   public static void main(String[] args) throws ClassNotFoundException, SQLException {

      ResultSet rs = null;

      Statement stmt = null;

      Connection conn = null;

      try {

          Class.forName("oracle.jdbc.driver.OracleDriver");

         conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

          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();

          }

      }

   }

}

 

四、JDBC编程高级

1、 JDBC处理DML语句：

import java.sql.*;

publicclass TestDML {

   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:solid", "scott", "tiger");

          stmt = conn.createStatement();

          String sql = "insert into dept2 values (99,'develop','changsha')";

          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();

          }

      }

   }

}

 

2、 JDBC处理DML语句2：

import java.sql.*;

publicclass TestDML2 {

   public static void main(String[] args) {

      if (args.length != 3) {

          System.out.println("Arguments error,please enter again!");

          System.exit(-1);

      }

      

      int deptno = 0;

      try {

          deptno = Integer.parseInt(args[0]);

      } catch (NumberFormatException e) {

          System.out.println("Arguments error");

          System.exit(-1);

      }

      

      String dname = args[1];

      String loc = args[2];

      

      Connection conn = null;

      Statement stmt = null;

      try {

          Class.forName("oracle.jdbc.driver.OracleDriver");

         conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

          stmt = conn.createStatement();

          String sql = "insert into dept2 values (" + deptno + ",'" + dname + "','" + loc + "' )";

System.out.println(sql);

          stmt.executeUpdate(sql);

      } catch (ClassNotFoundException e) {

          e.printStackTrace();

      } catch (SQLException e) {

          e.printStackTrace();

      } finally {

          try {

             if (conn != null) {

                 conn.close();

                 conn = null;

             }

             if (stmt != null) {

                 stmt.close();

                 stmt = null;

             }

          } catch (SQLException e) {

             e.printStackTrace();

          }         

      }

   }

}

 

3、 JDBC处理PreparedStatement，可以灵活指定SQL语句中的变量

import java.sql.*;

publicclass TestPrepStmt {

   public static void main(String[] args) {

      if (args.length != 3) {

          System.out.println("Arguments error,please enter again!");

          System.exit(-1);

      }

      int deptno = 0;

      try {

          deptno = Integer.parseInt(args[0]);

      } catch (NumberFormatException e) {

          System.out.println("Arguments error");

          System.exit(-1);

      }

      

      String dname = args[1];

      String loc = args[2];

      

      Connection conn = null;

      PreparedStatement pstmt = null;

      try {

          Class.forName("oracle.jdbc.driver.OracleDriver");

         conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:solid", "scott", "tiger");

          pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");

          pstmt.setInt(1, deptno);

          pstmt.setString(2, dname);

          pstmt.setString(3, loc);

          pstmt.executeUpdate();

      } catch (ClassNotFoundException e) {

          e.printStackTrace();

      } catch (SQLException e) {

          e.printStackTrace();

      } finally {

          try {

             if (conn != null) {

                 conn.close();

                 conn = null;

             }

             if (pstmt != null) {

                 pstmt.close();

                 pstmt = null;

             }

          } catch (SQLException e) {

             e.printStackTrace();

          }         

      }

   }

}

 

4、 JDBC处理储存过程

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:@192.168.0.1:1521:SXT", "scott", "tiger");

             CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");

             cstmt.registerOutParameter(3, Types.INTEGER);

             cstmt.registerOutParameter(4, 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();

      }

 

}

 

5、 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:@192.168.0.1:1521:SXT", "scott", "tiger");

             /*

             Statement 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();

             stmt.close();

             */

             

             PreparedStatement ps = conn.prepareStatement("insert into dept2 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();

 

      }

 

}

 

6、 JDBC处理Transaction

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();

                                  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();

                    }

             }

      }

}

 

7、 JDBC处理可滚动的结果

import java.sql.*;

public class TestScroll {

      public static void main(String args[]) {

 

             try {

                    new oracle.jdbc.driver.OracleDriver();

                    String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";

                    Connection conn = DriverManager

                                  .getConnection(url, "scott", "tiger");

                    Statement stmt = conn.createStatement(

                                  ResultSet.TYPE_SCROLL_INSENSITIVE,

                                  ResultSet.CONCUR_READ_ONLY);

                    ResultSet rs = stmt

                                  .executeQuery("select * from emp order by sal");

                    rs.next();

                    System.out.println(rs.getInt(1));

                    rs.last();

                    System.out.println(rs.getString(1));

                    System.out.println(rs.isLast());

                    System.out.println(rs.isAfterLast());

                    System.out.println(rs.getRow());

                    rs.previous();

                    System.out.println(rs.getString(1));

                    rs.absolute(6);

                    System.out.println(rs.getString(1));

                    rs.close();

                    stmt.close();

                    conn.close();

             } catch (SQLException e) {

                    e.printStackTrace();

             }

      }

}