пятница, 19 декабря 2014 г.

Подключение к 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 );
     }
}






Комментариев нет:

Отправить комментарий