[JAVA] How to create a JDBC URL (Oracle Database, Thin)

Let's write how to make a JDBC URL (Oracle Database, Thin) 彡 (゚) (゚)

1. JDBC manual description

The description of the manual is as follows.

8.2 Database URL and database specifier https://docs.oracle.com/cd/E96517_01/jjdbc/data-sources-and-URLs.html#GUID-C4F2CA86-0F68-400C-95DA-30171C9FB8F0 The database URL is a string. The full URL syntax is: jdbc:oracle:driver_type:[username/password]@database_specifier  
8.2.4 Thin format service name syntax https://docs.oracle.com/cd/E96517_01/jjdbc/data-sources-and-URLs.html#GUID-EF07727C-50AB-4DCE-8EDC-57F0927FF61A Thin format service names are only supported by the JDBC Thin driver. The syntax is as follows: @//host_name:port_number/service_name

Since the above description is the simple connection (EZ CONNECT) itself, the manual for the simple connection is also linked. 彡 (゚) (゚)

8.1 Understanding Simple Connection Naming Methods https://docs.oracle.com/cd/E96517_01/netag/configuring-naming-methods.html#GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE CONNECT username@[//]host[:port][/service_name][:server][/instance_name]

As a general rule, you can create a JDBC URL by specifying the host name / port number / service name. Since it is the listener who manages these, first I will briefly explain the role of the listener.

2. Listener role

The listener is a process that resides on the server and makes connection requests from clients. The process of listening and managing / allowing connections to the Oracle Database.

This is an example of a dedicated server connection, but the following article is easy to understand for the operation of the listener.

What is a connection? https://www.oracle.com/technetwork/jp/articles/chapter5-1-101584-ja.html#p01b

The listener has one or more host names (IP addresses) / port numbers defined. The database service is dynamically registered with that listener.

When the lsnrctl status command is executed, the host name (IP address) / port number and You can check the registered database services. The following is a sample 彡 (゜) (゜)

$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 30-APR-2019 23:39:27
:
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))★ Host name and port number
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8081))(Presentation=HTTP)(Session=RAW))
:
Services Summary...
Service "64a52f53a7683286e053cda9e80aed76" has 1 instance(s).★ Services registered as listeners
  Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "784ac9d638bb5f59e0530100007f6047" has 1 instance(s).★ Services registered as listeners
  Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "AYSTEST" has 1 instance(s).★ Services registered as listeners
  Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).★ Services registered as listeners
  Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orclcdb" has 2 instance(s).★ Services registered as listeners
  Instance "orclcdb", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orclcdbXDB" has 1 instance(s).★ Services registered as listeners
  Instance "orclcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

3. What is a database service?

Database services are used to help identify Oracle Database workloads. It will be given a logical alias. You can create multiple services in one Oracle Database environment.

The following article is easy to understand Yade 彡 (゜) (゜)

Part 3 Connect via network https://www.oracle.com/technetwork/jp/database/articles/kusakabe/kusakabe-3-4490049-ja.html 4.2 Services Until Oracle8 ... (Omitted) ... It was the SID that is the Oracle instance identifier ... (Omitted) ... Oracle Database 10g extends the concept of services to abstract workloads ... (...) ... The added service is registered with the Oracle listener by the dynamic service registration mechanism.

Describe this database service name as the connection destination, such as JDBC URL / tnsnames.ora / Easy Connection (EZCONNECT).

4. Sample 1: Check connection while specifying JDBC URL with sqlcl

Try writing a sample JDBC URL with sqlcl (command line version of SQL Developer) 彡 (゚) (゚) Try connecting to the AYTEST service in 2. above. The JDBC URL for this case is:

jdbc:oracle:driver_type:[username/password]@//host_name:port_number/service_name ↓ jdbc:oracle:thin:@//0.0.0.0:1521/AYTEST

The execution sample is shown below. The JDBC URL is after the @ mark immediately after the user name of the CONNECT command. You can also see the JDBC URL with the sqlcl SHOW JDBC command.

cd /home/oracle/sqldeveloper/sqldeveloper/bin
./sql /nolog
CONNECT AYSHIBAT@jdbc:oracle:thin:@//0.0.0.0:1521/AYSTEST
SHOW JDBC

SQLcl: Release 18.3 Production on Wed May 01 01:06:43 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Password? (**********?) ********
Connected.

-- Database Info --
Database Product Name: Oracle
:
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Driver Major Version: 18
Driver Minor Version: 3
Driver URL: jdbc:oracle:thin:@//0.0.0.0:1521/AYSTEST ★JDBC URL
:

5. Sample 2: Connect to the OCI DB (DBaaS) CDB with sqlcl while specifying the JDBC URL

I will write a sample to connect to the CDB of OCI (Oracle Cloud Infrastructure) DB (DBaaS) with sqlcl JDBC URL 彡 (゚) (゚) Try using the sample from the article below.

Try connecting to the OCI Database (DBaaS) PDB with sqlplus. (Oracle Cloud Infrastructure) https://gonsuke777.hatenablog.com/entry/2019/02/19/211953 : Connection string to management service (CDB) (simple connection): dbname.subnetname.vcnname.oraclevcn.com:1521/dbname_cdb32r.subnetname.vcnname.oraclevcn.com :

The JDBC URL in the above case is as follows.

jdbc:oracle:driver_type:[username/password]@//host_name:port_number/service_name ↓ jdbc:oracle:thin:@//dbname.subnetname.vcnname.oraclevcn.com:1521/dbname_cdb32r.subnetname.vcnname.oraclevcn.com

The connection sample by sqlcl is as follows 彡 (゜) (゜)

./sql /nolog
CONNECT SYSTEM@jdbc:oracle:thin:@//dbname.subnetname.vcnname.oraclevcn.com:1521/dbname_iad32r.subnetname.vcnname.oraclevcn.com
SHOW JDBC;

Password? (**********?) ********
Connected.

-- Database Info --
Database Product Name: Oracle
:
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 12.2.0.1.0
Driver Major Version: 12
Driver Minor Version: 2
Driver URL: jdbc:oracle:thin:@//dbname.subnetname.vcnname.oraclevcn.com:1521/dbname_iad32r.subnetname.vcnname.oraclevcn.com ★JDBC URL
:

6. Sample 3: JDBC URL for Autonomous DB (ATP / ADW)

For JDBC URL in case of Autonomous DB (ADW / ATP), refer to the following article 彡 (゚) (゚)

Try connecting to Autonomous DB (ADW / ATP) with Java's JDBC Thin Driver. (OCI, Oracle Cloud Infrastructure) https://gonsuke777.hatenablog.com/entry/2019/02/26/023534

Specify the wallet storage location in TNS_ADMIN in some way. If you write TNS_ADMIN directly in the JDBC URL, 彡 (゚) (゚)

  1. Method 1: Write the wallet path (TNS_ADMIN) in the JDBC URL https://gonsuke777.hatenablog.com/entry/2019/02/26/023534#4-%E6%96%B9%E6%B3%951%E3%82%A6%E3%82%A9%E3%83%AC%E3%83%83%E3%83%88%E3%81%AE%E3%83%91%E3%82%B9TNS_ADMIN-%E3%82%92-JDBC-URL-%E3%81%AB%E8%A8%98%E8%BF%B0

    ★ The following is the JDBC URL jdbc:oracle:thin:@xxxxxx_high?TNS_ADMIN=/home/opc/app/opc/product/18.0.0/client_1/network/admin

See the article for the results.

7. Sample 4: Write the JDBC URL in a tnsnames.ora-like format

As described in the Manual JDBC URLs can also be written like tnsnames.ora. The following is the sample 彡 (゜) (゜)

* Actually described in one line
jdbc:oracle:thin:@
(DESCRIPTION_LIST=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=dbname.subnetname.vcnname.oraclevcn.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=dbname_iad32r.subnetname.vcnname.oraclevcn.com))
  )
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=dbname-scan.subnetname.vcnname.oraclevcn.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=dbname_iad32r.subnetname.vcnname.oraclevcn.com))
  )
)

The connection sample with sqlcl is as follows: 彡 (゚) (゚) command long.

./sql /nolog
CONNECT SYSTEM@jdbc:oracle:thin:@(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbname_iad32r.subnetname.vcnname.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=aysdb121_iad1rn.sub12070931430.vcnname.oraclevcn.com)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbname-scan.subnetname.vcnname.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbname_iad32r.subnetname.vcnname.oraclevcn.com))))
SHOW JDBC

SQLcl: Release 19.1 Production on Wed May 01 06:20:39 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Password? (**********?) ***************
Connected.

-- Database Info --
Database Product Name: Oracle
:
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Driver Major Version: 18
Driver Minor Version: 3
Driver URL: jdbc:oracle:thin:@(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbname_iad32r.subnetname.vcnname.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=aysdb121_iad1rn.sub12070931430.vcnname.oraclevcn.com)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbname-scan.subnetname.vcnname.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbname_iad32r.subnetname.vcnname.oraclevcn.com))))
:

8. Summary

I wonder if it has a bad reputation because I can write something like 7. above (゜) (゜)

This area is part of Oracle MAA (Maximum Availability Architecture). Failover at connection, client-side load balancing, etc. I am aware that this is the case, so it is necessary to make an effort to understand it (゜ ε ゜).

If you understand the simple connection (EZ CONNECT), you should be afraid! Please connect (?) More and more 彡 (^) (^)

Bonus. Sample: Connect to Autonomous DB (ATP / ADW) with sqlcl

For sqlcl, specify the wallet with the set cloudconfig command. The following will be helpful.

Oracle Cloud: I tried SQLcl connection to Autonomous Database https://qiita.com/shirok/items/86355be72a47a840d10e

The JDBC URL after set cloudconfig is as follows, something complicated (゜) (゜)

./sql /nolog
set cloudconfig /home/opc/app/opc/product/18.0.0/client_1/network/admin/Wallet_aysatp01.zip
CONNECT ADMIN@aysatp01_low
SHOW JDBC


SQLcl: Release 19.1 Production on Wed May 01 05:58:28 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Operation is successfully completed.
Operation is successfully completed.
Using temp directory:/tmp/oracle_cloud_config4416493815228189719
Password? (**********?) *************
Connected.

-- Database Info --
Database Product Name: Oracle
:
-- Driver Info --
Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Driver Major Version: 18
Driver Minor Version: 3
Driver URL: jdbc:oracle:thin:@(description= (address=(protocol=tcps)(port=1522)(host=xxxx.xxxx.xxxxxxxx.com))(connect_data=(service_name=xxxxxx_low.xxxx.xxxxxxxx.com))(security=(ssl_server_cert_dn="CN=xxxx.xxxx.xxxxxxxx.com,OU=…,O=…,L=…,ST=…,C=…"))   )
:

Recommended Posts

How to create a JDBC URL (Oracle Database, Thin)
How to create a database for H2 Database anywhere
How to create a method
[Rails] How to create a signed URL for CloudFront
How to make a JDBC driver
[Java] How to create a folder
How to create a Maven repository for 2020
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
[Swift5] How to create a splash screen
[rails] How to create a partial template
[Rails] rails new to create a database with PostgreSQL
[Rails] How to create a graph using lazy_high_charts
How to create pagination for a "kaminari" array
How to create a class that inherits class information
How to create a theme in Liferay 7 / DXP
[1st] How to create a Spring-MVC framework project
How to easily create a pull-down in Rails
[Rails] How to create a Twitter share button
How to create docker-compose
How to create a route directly from the URL you want to specify + α
3. Create a database to access from the web module
How to create a Java environment in just 3 seconds
How to create a Spring Boot project in IntelliJ
[Spring Boot] How to create a project (for beginners)
How to create a data URI (base64) in Java
[Apple Subscription Offer] How to create a promotional offer signature
How to create an application
How to leave a comment
How to insert a video
How to create a form to select a date from the calendar
How to create a placeholder part to use in the IN clause
How to create a service builder portlet in Liferay 7 / DXP
How to create and launch a Dockerfile for Payara Micro
Try connecting to the OCI Database (DBaaS) PDB with Java's JDBC Thin Driver. (Oracle Cloud Infrastructure)
How to add columns to a table
Preparing to create a Rails application
How to use Spring Data JDBC
How to make a Java container
[Rails 6] How to create a dynamic form input screen using cocoon
How to sign a Minecraft MOD
How to create a new Gradle + Java + Jar project in Intellij 2016.03
Create a database in a production environment
How to write a ternary operator
[Swift] How to send a notification
How to make a splash screen
How to make a Jenkins plugin
How to make a Maven project
Try to create a server-client app
How to pass Oracle Java Silver
How to create a web server on an EC2 instance on AWS
How to make a Java array
How to quickly create a reverse proxy that supports HTTPS with Docker
How to create a query using variables in GraphQL [Using Ruby on Rails]
To create a Zip file while grouping database search results in Java
[Docker] How to create a virtual environment for Rails and Nuxt.js apps
How to create a validator that allows only input to any one field
How to create a server executable JAR and WAR with Spring gradle
[Rails] How to create a table, add a column, and change the column type
How to create a convenient method that utilizes generics and functional interfaces
[Xcode] How to add a README.md file
How to execute a contract using web3j