jdbc java oracle_JDBC：JAVA & Oracle

我们先运行一个简单的demo(代码中涉及到个人信息的都用<>代替了):

import java.io.*;

import java.sql.*;

import oracle.jdbc.driver.*;

import oracle.sql.*;

public class JDBC {

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

{

String username, password;

username = "";// Your Oracle Account ID

password = ""; // Password of Oracle Account

//-----------Connection--------------------------------

//Register JDBC driver

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

//JDBC Thin driver-This is a Type 4, 100% pure Java driver for client-side use.

//DriverManager.getConnectioin(String url, String username, String password)

//url = jdbc:oracle:driver:@database:port:sid

OracleConnection conn =

(OracleConnection)DriverManager.getConnection(

"jdbc:oracle:thin::1521:dbms",

username, password);

//A Statement object is a class which sends your SQL statement to the DBMS.

Statement stmt = conn.createStatement();

//Pass SQL statement on to the DBMS.

//For SELECT statement, the method to use is executeQuery.

// For statements that create or modify tables, the method to use is executeUpdate.

//declare a reference of the class ResultSet to hold our results

ResultSet rset = stmt.executeQuery("SELECT EMPNO, ENAME, JOB FROM EMP");

while (rset.next())//when next is not null

{

System.out.println(rset.getInt(1)

+ " " + rset.getString(2)

+ " " + rset.getString(3));

}

System.out.println(

);

conn.close();

}

}

接下来对这部分代码进行讲解：

import jar包

import java.io.*;

import java.sql.*;

import oracle.jdbc.driver.*;

import oracle.sql.*;

注册JDBC驱动器：DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

建立连接：

OracleConnection conn =(OracleConnection)DriverManager.getConnection("jdbc:oracle:thin::1521:dbms",username, password);

建立连接有两种格式：

DriverManager.getConnectioin(String url, String username, String password)

url = jdbc:oracle:driver:@database:port:sid

OR

DriverManager.getConnection(String url)

url = jdbc:oracle:driver:username/password@database:port:sid

获取执行SQL的对象：Statement stmt = conn.createStatement();

执行SQL：对于不同的SQL语句，执行的方法也不一样：

//执行DQL，例如select,返回结果集合(ResultSet)

stmt.executeQuery(“SELECT * FROM EMP”);

//执行DML，例如insert\update\delete,返回一个int,表示受影响的行数

stmt.executeUpdate(“CREATE TABLE COFFEES “ +

“ (COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, “ +

“ SALES INTEGER, TOTAL INTEGER)”);

Boolean stmt.execute(String sql)//可以执行任意sql,返回是否执行成功

这里要注意一点，就是Oracle数据库中进行了操作后一定要记得手动commit,否则是查询不到的。但查询不到不会报错，只是返回的ResultSet是一个空对象，调用它的next方法就可以看出返回是false。

提取ResultSet中的结果：

while (rset.next())//when next is not null

{

System.out.println(rset.getInt(1)

+ " " + rset.getString(2)

+ " " + rset.getString(3));

}

System.out.println(

);

关闭连接：conn.close();

Prepared statement

预编译语句PreparedStatement 是java.sql中的一个接口，它是Statement的子接口。通过Statement对象执行SQL语句时，需要将SQL语句发送给DBMS，由DBMS首先进行编译后再执行。预编译语句和Statement不同，在创建PreparedStatement 对象时就指定了SQL语句，该语句立即发送给DBMS进行编译。当该编译语句被执行时，DBMS直接运行编译后的SQL语句，而不需要像其他SQL语句那样首先将其编译。预编译的SQL语句处理性能稍微高于普通的传递变量的办法。

————————————————

版权声明：本文为CSDN博主「yushuifirst」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/yushuifirst/article/details/45201623

使用预编译：

PreparedStatement prepareQuery = conn.prepareStatement(

"SELECT ENO, ENAME, ZIP, HDATE FROM EMPLOYEES WHERE ENO = ?");

System.out.println(

"Enter -1 to quit program when prompted for employee number");

System.out.println();

snum = readEntry("employee number: ");

enumber = Integer.valueOf(snum).intValue();

while (enumber != -1)

{

prepareQuery.setInt(1, enumber);

rset = prepareQuery.executeQuery();

while (rset.next())

{

System.out.println(rset.getInt(1) + " " +

rset.getString(2) + " " +

rset.getInt(3) + " " +

rset.getDate(4));

}

snum = readEntry("employee number: ");

enumber = Integer.valueOf(snum).intValue();

}

这里的SQL使用了变量占位符？，这给了预编译SQL一些灵活性，因为虽然我们可能要对某一类查询重复很多次，但是不一定每次的Entity或者Attribute都一样，更不好说能不能直接指定这些数据，所以在预编译SQL语句中用占位符是很常见的。

向预编译SQL语句中传参的格式是：,);。传参后，正常执行即可。

JDBC Transactions

JDBC中的命令是被自动commit的，如果不想这样，而想要手动commit,可以设置conn.setAutoCommit(false) 。之后想要提交的话，就要调用这个方法：conn.commit().想要恢复的话，也是这个方法：conn.setAutoCommit(true);

关闭自动提交，也是为了我们如果程序出问题的话回滚的方便。在关闭自动提交后，我们可以把方法conn.rollback();放到异常处理的try-catch-finally中

JDBC Callable statement

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task.

在数据库中创建一个简单的储存过程：

CREATE PROCEDURE SHOW_STAFF

AS

SELECT ENAME,JOB,MGR,HIREDATE

FROM EMP ORDER BY ENAME;

A CallableStatement object contains a call to a stored procedure, it does not contain the stored procedure itself.

CallableStatement cs = conn.prepareCall(“{call SHOW_STAFF}”);

ResultSet rset = cs.executeQuery();

When the driver encounters “{call SHOW_STAFF}”, it will translate this escape syntax(转义语法) into the native SQL used by the database to call the stored procedure named SHOW_STAFF.