Подключение к Oracle из java

Переменные окружения:

CLASSPATH=C:\app\client\oracle\product\12.1.0\client_1\jdbc\lib\ojdbc6.jar;C:\app\client\oracle\product\12.1.0\client_1\jlib\orai18n.jar;.
Path=C:\app\client\oracle\product\12.1.0\client_1\bin;C:\Program Files\Java\jdk1.8.0_25\bin;


TestDBOracle.java


import java.sql.*;

public class TestDBOracle {

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

        //jdbc драйвер можно зарегистрировать так:
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // или так:
        // DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

       
        // Для подключения можно использовать тонкий драйвер
        //jdbc:oracle:thin:@//host:port/service   
        String url = "jdbc:oracle:thin:@//alpha:1521/testdb_p.localdomain"; 
        // или драйвер OCI
        // String url = "jdbc:oracle:oci:@//alpha:1521/testdb_p.localdomain";
        // Причем для обоих драйверов вместо //host:port/service , можно указать так:
        // String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=alpha.localdomain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=testdb_p.localdomain)))";
        // Для кластера так:
        // String url = "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1-VIP)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2-VIP)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORARAC)))";   


        Connection conn = DriverManager.getConnection(url,"scott","tiger");

        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        while (rset.next()) {
            System.out.println (rset.getString(1));
        }

        stmt.close();   

        System.out.println ("Success!");

    }
}


C:\project\java>java TestDBOracle

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Success!

C:\project\java>



Еще несколько примеров использования jdbc:

import java.sql.*;

public class instest
{
    static public void main(String args[]) throws Exception
    {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection
            conn = DriverManager.getConnection
            ("jdbc:oracle:thin:@heesta:1521:ORA12CR1","scott","tiger");
        conn.setAutoCommit( false );
        Statement stmt = conn.createStatement();
        for( int i = 0; i < 25000; i++ )
        {
            stmt.execute
            ("insert into "+ args[0] +
                " (x) values(" + i + ")" );
        }
        conn.commit();
        conn.close();
    }
}



import java.sql.*;

public class instest
{
    static public void main(String args[]) throws Exception
    {
        System.out.println( "start" );
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection
            conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@heesta:1521:ORA12CR1", "scott","tiger");
        conn.setAutoCommit( false );
        PreparedStatement pstmt =
            conn.prepareStatement
            ("insert into "+ args[0] + " (x) values(?)" );
        for( int i = 0; i < 25000; i++ )
        {
            pstmt.setInt( 1, i );
            pstmt.executeUpdate();
        }
        conn.commit();
        conn.close();
        System.out.println( "done" );
    }
}



import java.sql.*;

public class perftest
{
    public static void main (String arr[]) throws Exception
    {
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        Connection con = DriverManager.getConnection
            ("jdbc:oracle:thin:@csxdev:1521:ORA12CR1", "scott", "tiger");
        Integer iters = new Integer(arr[0]);
        Integer commitCnt = new Integer(arr[1]);
        con.setAutoCommit(false);
        doInserts( con, 1, 1 );
        Statement stmt = con.createStatement ();
        stmt.execute( "begin dbms_monitor.session_trace_enable(waits=>true); end;" );
        doInserts( con, iters.intValue(), commitCnt.intValue() );
       con.close();
    }
    static void doInserts(Connection con, int count, int commitCount )
    throws Exception
    {
        PreparedStatement ps =
            con.prepareStatement
            ("insert into test " +
             "(id, code, descr, insert_user, insert_date)"
             + " values (?,?,?, user, sysdate)");

            int rowcnt = 0;
            int committed = 0;
            for (int i = 0; i < count; i++ )
            {
                ps.setInt(1,i);
                ps.setString(2,"PS - code" + i);
                ps.setString(3,"PS - desc" + i);
                ps.executeUpdate();
                rowcnt++;
                if ( rowcnt == commitCount )
                {
                    con.commit();
                    rowcnt = 0;
                    committed++;
                }
            }
            con.commit();
            System.out.println
            ("pstatement rows/commitcnt = " + count + " / " + committed );
     }
}

Java Update

На металинке говорится что доступен Java SE TZUpdater.

Java Time Zone Updater Tool tzupdater quits with "There's no tzdata available for this Java runtime" [ID 1330586.1]

Сам Java SE TZUpdater скачиваем по ссылке:

http://www.oracle.com/technetwork/java/javase/downloads/tzupdater-download-513681.html


Заходим на сервер egar-app1.msk.vbrr.loc
su - oracle


Java SE TZUpdater уже загружен и разархивирован и на сервере egar-app1.msk.vbrr.loc находится по пути:

/home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar


$ ls -l /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar
-rw-rw-r--  1 oracle oinstall 472738 Oct  4 21:02 /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar
$

В ORACLE_HOME ( /opt/oracle/ora_j2ee ) у нас имеется JDK и JRE

/opt/oracle/ora_j2ee/jdk/bin/
/opt/oracle/ora_j2ee/jre/1.4.2/bin/


Смотрим текущую  time zone data version  для наших  JDK и JRE

$cd /opt/oracle/ora_j2ee/jdk/bin/
$./java -jar /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar -V
tzupdater version 1.3.42-b02
JRE time zone data version: tzdata2003a
Embedded time zone data version: tzdata2011k

cd /opt/oracle/ora_j2ee/jre/1.4.2/bin/
$./java -jar /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar -V
tzupdater version 1.3.42-b02
JRE time zone data version: tzdata2003a
Embedded time zone data version: tzdata2011k


Другой способ посмотреть текущую  time zone data version  для наших  JDK и JRE

$ /usr/bin/od -c -j 11 -N 11 /opt/oracle/ora_j2ee/jre/1.4.2/lib/zi/ZoneInfoMappings
0000013   t   z   d   a   t   a   2   0   0   3   a
0000026
$

$ /usr/bin/od -c -j 11 -N 11 /opt/oracle/ora_j2ee/jdk/jre/lib/zi/ZoneInfoMappings
0000013   t   z   d   a   t   a   2   0   0   3   a
0000026
$


Перед патчем останавливаем сервер приложений:

cd /home/oracle/bin/pkg/ias_cold_backup
./stop_app_all.sh


Установка Java SE TZUpdater

$cd /opt/oracle/ora_j2ee/jdk/bin/

$ ./java -jar /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar -u -v
java.home: /opt/oracle/ora_j2ee/jdk/jre
java.vendor: Sun Microsystems Inc.
java.version: 1.4.2_06
JRE time zone data version: tzdata2003a
Embedded time zone data version: tzdata2011k
Extracting files... done.
Renaming directories... done.
Validating the new time zone data... done.
Time zone data update is complete.
$

cd /opt/oracle/ora_j2ee/jre/1.4.2/bin/

$ ./java -jar /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar -u -v
java.home: /opt/oracle/ora_j2ee/jdk/jre
java.vendor: Sun Microsystems Inc.
java.version: 1.4.2_06
JRE time zone data version: tzdata2003a
Embedded time zone data version: tzdata2011k
Extracting files... done.
Renaming directories... done.
Validating the new time zone data... done.
Time zone data update is complete.
$

Проверяем time zone data version  после установки Java SE TZUpdater для наших  JDK и JRE:

$cd /opt/oracle/ora_j2ee/jdk/bin/
$./java -jar /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar -V
tzupdater version 1.3.42-b02
JRE time zone data version: tzdata2011k
Embedded time zone data version: tzdata2011k

cd /opt/oracle/ora_j2ee/jre/1.4.2/bin/
$./java -jar /home/oracle/tzupdater-1.3.42-2011k/tzupdater.jar -V
tzupdater version 1.3.42-b02
JRE time zone data version: tzdata2011k
Embedded time zone data version: tzdata2011k


Другой способ посмотреть time zone data version  для наших  JDK и JRE

$ /usr/bin/od -c -j 11 -N 11 /opt/oracle/ora_j2ee/jre/1.4.2/lib/zi/ZoneInfoMappings

0000013   t   z   d   a   t   a   2   0   1   1   k
0000026
$


$ /usr/bin/od -c -j 11 -N 11 /opt/oracle/ora_j2ee/jdk/jre/lib/zi/ZoneInfoMappings

0000013   t   z   d   a   t   a   2   0   1   1   k
0000026
$


После установки Java SE TZUpdater запускаем сервер приложений:

cd /home/oracle/bin/pkg/ias_cold_backup
./start_app_all.sh

Java

Sample Java program

Put the following sample code in a file called Hello.java:

public class Hello {
public static void main(String[] args) {
for (int i=0; i < args.length; i++) {
System.out.println("Hello " + args[i]);
}
}
}

Compiling Java programs

Use the javac utility to compiler a java program:

javac Hello.java

Running Java programs

Start our "java" program (Hello) and pass some arguments to it (World):

java Hello World