- package com.yelang;
- import java.sql.*;
- public class TestJdbc
- {
- public static void main(String[] args)
- {
- Connection conn = null;
- Statement stmt = null;
- ResultSet rs = null;
- try
- {
- // Class.forName("com.mysql.jdbc.Driver").newInstance();
- // new com.mysql.jdbc.Driver();
- Class.forName("com.mysql.jdbc.Driver");
- conn = DriverManager.getConnection(
- "jdbc:mysql://localhost:3306/shiyan", "root", "111");
- stmt = conn.createStatement();
- rs = stmt.executeQuery("select * from user");
- while (rs.next())
- {
- System.out.println(rs.getString("username"));
- System.out.println(rs.getString("password"));
- }
- }
- 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();
- }
- }
- }
- }
PreparedStatement
- import java.sql.*;
- public class TestPrepStmt
- {
- 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");
- // new oracle.jdbc.driver.OracleDriver();
- conn = DriverManager.getConnection(
- "jdbc:oracle:thin:@192.168.0.1:1521:SXT", "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 (pstmt != null)
- {
- pstmt.close();
- pstmt = null;
- }
- if (conn != null)
- {
- conn.close();
- conn = null;
- }
- }
- catch (SQLException e)
- {
- e.printStackTrace();
- }
- }
- }
- }
存储过程
SQL>create or replace procedure p
2 (v_a in number,v_b number,v_ret out number,v_temp in out number)
3 is
4
5 begin
6 if(v_a > v_b) then
7 v_ret := v_a;
8 else
9 v_ret := v_b;
10 end if;
11 v_temp := v_temp + 1;
12 end;
- import java.sql.*;
- public class TestProc
- {
- public static void main(String[] args)
- {
- Connection conn = null;
- CallableStatement cstmt = null;
- try
- {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection(
- "jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
- 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));
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- finally
- {
- try
- {
- if (cstmt != null)
- {
- cstmt.close();
- cstmt = null;
- }
- if (conn != null)
- {
- conn.close();
- conn = null;
- }
- }
- catch (SQLException e)
- {
- e.printStackTrace();
- }
- }
- }
- }
批处理
- import java.sql.*;
- public class TestBatch
- {
- public static void main(String[] args)
- {
- Connection conn = null;
- PreparedStatement ps = null;
- try
- {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- conn = DriverManager.getConnection(
- "jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
- 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();
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- finally
- {
- try
- {
- if (ps != null)
- {
- ps.close();
- ps = null;
- }
- if (conn != null)
- {
- conn.close();
- conn = null;
- }
- }
- catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- }
- }
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();
- }
- }
- }
- }
JDBC处理可滚动的结果集
- import java.sql.*;
- public class TestScroll
- {
- public static void main(String args[])
- {
- Connection conn = null;
- Statement stmt = null;
- ResultSet rs = null;
- try
- {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
- conn = DriverManager.getConnection(url, "scott", "tiger");
- stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
- ResultSet.CONCUR_READ_ONLY);
- 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));
- }
- 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处理可更新的结果集
- import java.sql.*;
- public class TestUpdataRs
- {
- public static void main(String args[])
- {
- Connection conn = null;
- Statement stmt = null;
- ResultSet rs = null;
- try
- {
- Class.forName("oracle.jdbc.driver.OracleDriver");
- String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
- conn = DriverManager.getConnection(url, "scott", "tiger");
- stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
- ResultSet.CONCUR_UPDATABLE);
- rs = stmt.executeQuery("select * from emp2");
- rs.next();
- // 更新一行数据
- rs.updateString("ename", "AAAA");
- rs.updateRow();
- // 插入新行
- rs.moveToInsertRow();
- rs.updateInt(1, 9999);
- rs.updateString("ename", "AAAA");
- rs.updateInt("mgr", 7839);
- rs.updateDouble("sal", 99.99);
- rs.insertRow();
- // 将光标移动到新建的行
- rs.moveToCurrentRow();
- // 删除行
- rs.absolute(5);
- rs.deleteRow();
- // 取消更新
- // rs.cancelRowUpdates();
- }
- 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();
- }
- }
- }
- }