java oracle 执行sql_一些常用java执行SQL方式(oracle)

在oracle下，使用java调用oracle的存储过程：

1.先建一个存储过程：

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

( 注释：v_a in number  in是指明输入参数  默认为是输入参数

v_ret out number 是指明输出参数

v_temp in out number  即是输出又是输入参数)

is  begin

if(v_a > v_b)

v_ret := v_a;

else

v_ret := v_b;

endif;

v_temp : = v_temp + 1;

end;

2 java 程序调用：

import java.sql.*;

public class TestProc {

/**

* @param args

*/

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

}

}

3.使用Transcation的例子

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);  //conn默认自动提交， 改为非自动提交

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

}

}

}

}

4.滚动结果集

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

}

}

}

5,可以更新的结果集 oracle  class12.jar 不支持下面的方式，其他数据库请自己尝试。当然不不推荐使用

import java.sql.*;

//可更新的滚动结果集

public class TestUpdataRs {

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

ResultSet 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(SQLException e){

e.printStackTrace();

}

}

}

6.Datasource是用来替换DriverManager的，主要支持连接池和分布式。

7.RowSet继承ResultSet；支持断开的结果集(ResultSet不支持)；支持javaBean标准