JDBC：JAVA & Oracle

2023-08-26 15:22:40

JDBC：JAVA & Oracle

本文中未加标注的资源均来自于PolyU数据库课程的实验材料。仅作为学习使用，如有侵权，请联系删除

JDBC是什么

我之前写过一篇关于数据库和JAVA的博文,这里直接贴上相关的内容：

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。 JDBC也是Sun Microsystems的商标。 JDBC是面向关系型数据库的。

Java定义了一套操作所有关系型数据库的规则（接口），具体的实现由各自的数据库软件负责，从而实现用一套统一的Java代码操作所有的关系型数据库，即以接口类型调用方法，真正实现的是实现类中的方法

四类JDBC驱动程序

JDBC bridge driver.
This type uses bridge technology to connect a Java client to a third-party API such as Open Database Connectivity (ODBC).
Native API (partly Java driver).
This type of driver wraps a native API with Java classes.
Network protocol (pure Java driver).
This type of driver communicates using a network protocol to a middle-tier server.
Native protocol (pure Java driver).
This type of driver, written in Java, communicates directly with the database.

Oracle对JDBC的实现

JDBC OCI driver
This is a Type 2 driver that uses Oracle’s native OCI interface. It is commonly referred to as the OCI driver.
JDBC Thin driver
This is a Type 4, 100% pure Java driver for client-side use. (The driver we use in our lab )。

It can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client-side.

The JDBC Thin driver communicates with the server using SQL*Net to access Oracle Database.

个人感觉，这种驱动的主要目的是为应用程序服务、调库用的
JDBC internal driver
This is a Type 2, native code driver for server-side use with Java code that runs inside the Oracle database’s JServer JVM.
JDBC server-side Thin driver
This is a Type 4 100% pure Java driver for server-side used with Java code that runs inside the Oracle database’s JServer JVM.

Once this Java class is compiled into the database you will need to publish a call specification for it's method. This involves writing a PL/SQL "Wrapper" to allow the method to be called from PL/SQL. The following code sample creates a function called Addition which publishes the Mathematics method Add.

From:https://oracle-base.com/articles/8i/jserver-java-in-the-database

个人感觉，这种驱动的主要目的是用来增强数据库的功能的，是为了数据库本身服务的

JDBC实践

这里我们选择Oracle数据库和对应与Oracle数据库的JDBC实现jar包：ojdbc7.jar

在IDEA下新建一个项目，将Jar包添加到IDEA项目的依赖中：File->Project Structure->Modules->Dependencies->点击那个加号->JARS or dictionaries

我们先运行一个简单的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 = "<userName>";         // Your Oracle Account ID
        password = "<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:<database>: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:<database>: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语句中传参的格式是：<prepareQuery.setInt(<第几个参数>,<参数值>);。传参后，正常执行即可。

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.

码农公寓

JDBC：JAVA & Oracle

JDBC是什么

四类JDBC驱动程序

Oracle对JDBC的实现

JDBC实践

Prepared statement

JDBC Transactions

JDBC Callable statement

相关文章