Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

DBCP JDBC and Transparent Application Continuity

cptkirkshOct 6 2022

We have an older application that can't failover when one node of our Oracle RAC goes down. It seems it uses an older version of org.apache.commons.dbcp.BasicDataSource. I can make this work when I use UCP from Oracle but when I use the apache version the app dies as soon as I shut down the node of the RAC it is connected to. Am I missing something or does it not work with Apache DBCP? Thanks

import org.apache.commons.dbcp2.BasicDataSource;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;






public class BasicDB{




    private static BasicDataSource dataSource;


    private static BasicDataSource getDataSource() {


        {


            if (dataSource == null)
            {
                BasicDataSource ds = new BasicDataSource();
                ds.setUrl("jdbc:oracle:thin:@pdb_tac");
                ds.setUsername("my_user");
                ds.setPassword("mypassword");
                //ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
                ds.setDriverClassName("oracle.jdbc.replay.OracleDataSourceImpl");
                ds.setDefaultAutoCommit(false);


                ds.setInitialSize(5);
                ds.setMinIdle(5);
                ds.setMaxIdle(10);
                ds.setMaxOpenPreparedStatements(100);


                dataSource = ds;
            }
            return dataSource;
        }


    }






    final static String ORACLE_WALLET= "c:/mywallet;




    private void pressAnyKeyToContinue()
    {
        System.out.print("Press any key to continue...");
        try { System.in.read(); }
        catch(Exception e) { e.printStackTrace(); }
    }
    public String getInstanceName(Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("select instance_name from v$instance");
        String r = new String();


        for(ResultSet result = pstmt.executeQuery(); result.next(); r = result.getString("instance_name")) {
        }


        pstmt.close();
        return r;
    }






    private void doTx(Connection c, int numValue) throws SQLException {
        String updsql = "UPDATE test SET v=UPPER(v) WHERE id=?";
        PreparedStatement pstmt = null;
        pstmt = c.prepareStatement(updsql);
        c.setAutoCommit(false);


        for(int i = 0; i < numValue; ++i) {
            pstmt.setInt(1, i);
            pstmt.executeUpdate();
        }


        c.commit();
        pstmt.close();
    }








    public static void main(String[] args) throws SQLException {
        //Connection conn = null;
        BasicDB self = new BasicDB();
        int numValue = 5000;




        try {
            System.setProperty("oracle.net.tns_admin", "C:/oracle/product/19.0.0/client_1/network/admin");
            System.setProperty("oracle.net.wallet_location", "(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${ORACLE_WALLET})))");
           BasicDataSource dataSource = BasicDB.getDataSource();
           Connection conn = dataSource.getConnection();




            PrintStream var10000 = System.out;
            String var10001 = self.getInstanceName(conn);
            var10000.println("Instance Name = " + var10001);


            System.out.println("Performing transactions");
            self.pressAnyKeyToContinue();
            self.doTx(conn, numValue);


            var10001 = self.getInstanceName(conn);
            var10000.println("Instance Name = " + var10001);


        } catch (SQLException e) {
            System.out.println("BasicDB - " + "SQLException occurred : "
                    + e.getMessage());
        }


    }
}

````
Comments
Post Details
Added on Oct 6 2022
2 comments
356 views