0

I have some application connecting to Oracle database through SpringJDBC as well as plain JDBC.

JDBC URL pattern is - 

jdbc:oracle:thin:@hostname:portNumber/schemaName

We are trying to create a TNS entry which can be related with the application. For example if there is an application StockTrade then the schema name will be DBStockTrade.

However developers still need to know the hostname and port number for construction of JDBC URL and need to make binary change whenever the schema name is changed.

Is there a way to avoid using the hostname and port number and simply use the schema name for connecting to the database? The idea is to use some kind of property file containing the schema name only and get rid of other details.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
user3137375
  • 113
  • 1
  • 5
  • 1
    Why not to use jndi lookup? – Yegor Chumakov Jun 03 '15 at 20:50
  • Are these client/ server applications that are deployed to a large number of clients? Or three-tier applications deployed to a relatively small number of application servers? You could do something like use Oracle Internet Directory (OID) to store all your TNS information centrally and just use a static TNS alias in your application. But that would generally make more sense in a client/ server world where there are large numbers of client applications than in a three-tier world where there are a small number of identically configured app servers. – Justin Cave Jun 03 '15 at 21:02
  • could you try jdbc url jdbc:oracle:thin:@hostname:portNumber:Database_Name I'm using my project in this way and not cause problems. – Ömer Faruk Kurt Jun 03 '15 at 21:17
  • 1
    @ÖmerFarukKurt - how does that remove the dependency on the hostname and port? You're just using the SID instead of the service name? – Alex Poole Jun 03 '15 at 22:10
  • Externalize the config in a property file, or in JNDI and you don't need to make a "binary change". – Mark Rotteveel Jun 04 '15 at 06:33

1 Answers1

1

For the issue:

need to make binary change whenever the schema name is changed

We can simply put the JDBC URL into a configuration file

Then whenever the schema name changed, we only need to edit the configuration file and no binary change needed.

For example, save the JDBC URL to a .properties file

# config.properties

jdbc_url=jdbc:oracle:thin:@hostname:portNumber/schemaName

In the Java code:

String currentPath = this.getClass().getProtectionDomain().getCodeSource()
    .getLocation().toURI().getPath();
String configFile = currentPath + File.separator + "config.properties";
Properties prop = new Properties();
prop.load(new FileInputStream(configFile));
String jdbcUrl = prop.getProperty("jdbc_url")

So with the jdbcUrl we can connect to the database

Happy
  • 757
  • 9
  • 18