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, 彡 (ﾟ) (ﾟ)

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=…"))   )
:

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