Jdbc oracle строка подключения

OracleВ® Database JDBC Java API Reference, Release 21c

Connect using the Oracle JDBC driver

Following code snippet shows how to connect to the Oracle Database using oracle.jdbc.pool.OracleDataSource

URL Formats

The Oracle JDBC Driver supports the following URL formats.

EZConnect Format

In EZConnect format Hostname, Port and Service Name can be configured using a simple syntax.
Since 19c, the connection properties can be added at the end of the URL.
The syntax uses ‘?’ to indicate start of conection properties and ‘&’ as a delimiter between each property.

Syntax :

Examples:

• jdbc:oracle:thin:@mydbhost:1521/mydbservice
• jdbc:oracle:thin:@tcp://mydbhost:1521/mydbservice
• jdbc:oracle:thin:@tcp://mydbhost1,mydbhost2:1521/mydbservice
• jdbc:oracle:thin:@tcp://mydbhost1:5521,mydbhost2:1521/mydbservice
• jdbc:oracle:thin:@tcp://mydbhost1:5521/mydbservice:dedicated
• jdbc:oracle:thin:@mydbhost1:5521/mydbservice?oracle.net.httpsProxyHost=myproxy&oracle.net.httpsProxyPort=80
• jdbc:oracle:thin:@tcps://mydbhost1:5521/mydbservice?wallet_location=/work/wallet
• jdbc:oracle:thin:@tcps://mydbhost1:5521/mydbservice?wallet_location=/work/wallet&ssl_server_cert_dn=»Server DN»

TNS URL Format

This format is more structured way of specifying the connect options and more descriptive.

Syntax :

) (HOST= )(PORT= )) (CONNECT_DATA=(SERVICE_NAME= ))

Examples:

• jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=mydbhost)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=mydbservice))
• jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=5221))) (CONNECT_DATA=(SERVICE_NAME=orcl)))
• jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS)(PORT=1522)(HOST=myhost)) (CONNECT_DATA=(SERVICE_NAME=dbservicename)) (SECURITY=(ssl_server_cert_dn=»CN=testcert.test.com, O=org,L=somecity,ST=state,C=US»)))

TNS Alias Format

To use the alias configured in the tnsnames.ora, use the below format. The location of the tnsnames.ora file can be configured using TNS_ADMIN parameter.

Syntax :

Examples:

• jdbc:oracle:thin:@prod_db?TNS_ADMIN=/work/tns/
• jdbc:oracle:thin:@inst1?TNS_ADMIN=/work/tns/

Copyright В© 2008, 2022, Oracle and/or its affiliates.

Источник

Establishing a Connection

First, you need to establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. Typically, a JDBC application connects to a target data source using one of two classes:

DriverManager : This fully implemented class connects an application to a data source, which is specified by a database URL. When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within the class path. Note that your application must manually load any JDBC drivers prior to version 4.0.

DataSource : This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object’s properties are set so that it represents a particular data source. See Connecting with DataSource Objects for more information. For more information about developing applications with the DataSource class, see the latest The Java EE Tutorial.

Note: The samples in this tutorial use the DriverManager class instead of the DataSource class because it is easier to use and the samples do not require the features of the DataSource class.

This page covers the following topics:

Using the DriverManager Class

Connecting to your DBMS with the DriverManager class involves calling the method DriverManager.getConnection . The following method, JDBCTutorialUtilities.getConnection , establishes a database connection:

The method DriverManager.getConnection establishes a database connection. This method requires a database URL, which varies depending on your DBMS. The following are some examples of database URLs:

MySQL: jdbc:mysql://localhost:3306/ , where localhost is the name of the server hosting your database, and 3306 is the port number

Java DB: jdbc:derby:testdb;create=true , where testdb is the name of the database to connect to, and create=true instructs the DBMS to create the database.

Note: This URL establishes a database connection with the Java DB Embedded Driver. Java DB also includes a Network Client Driver, which uses a different URL.

This method specifies the user name and password required to access the DBMS with a Properties object.

Note:

Typically, in the database URL, you also specify the name of an existing database to which you want to connect. For example, the URL jdbc:mysql://localhost:3306/mysql represents the database URL for the MySQL database named mysql . The samples in this tutorial use a URL that does not specify a specific database because the samples create a new database.

In previous versions of JDBC, to obtain a connection, you first had to initialize your JDBC driver by calling the method Class.forName . This methods required an object of type java.sql.Driver . Each JDBC driver contains one or more classes that implements the interface java.sql.Driver . The drivers for Java DB are org.apache.derby.jdbc.EmbeddedDriver and org.apache.derby.jdbc.ClientDriver , and the one for MySQL Connector/J is com.mysql.cj.jdbc.Driver . See the documentation of your DBMS driver to obtain the name of the class that implements the interface java.sql.Driver .

Any JDBC 4.0 drivers that are found in your class path are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName .)

The method returns a Connection object, which represents a connection with the DBMS or a specific database. Query the database through this object.

Specifying Database Connection URLs

A database connection URL is a string that your DBMS JDBC driver uses to connect to a database. It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties. The exact syntax of a database connection URL is specified by your DBMS.

Java DB Database Connection URLs

The following is the database connection URL syntax for Java DB:

• subsubprotocol specifies where Java DB should search for the database, either in a directory, in memory, in a class path, or in a JAR file. It is typically omitted.
• databaseName is the name of the database to connect to.
• attribute=value represents an optional, semicolon-separated list of attributes. These attributes enable you to instruct Java DB to perform various tasks, including the following:
  • Create the database specified in the connection URL.
  • Encrypt the database specified in the connection URL.
  • Specify directories to store logging and trace information.
  • Specify a user name and password to connect to the database.

See Java DB Developer’s Guide and Java DB Reference Manual from Java DB Technical Documentation for more information.

MySQL Connector/J Database URL

The following is the database connection URL syntax for MySQL Connector/J:

Источник

Подключение Connection

Для соединения с БД необходимо использовать класс Connection пакета java.sql. После установления соединения можно выполнять различные SQL-запросы и получать результаты их обработки сервером. Приложение может открыть одно или несколько соединений с одной или несколькими различными СУБД.

Открытие соединения, getConnection

Стандартный способ установления соединения — это вызов метода getConnection класса DriverManager. Методу getConnection необходимо передать строку URL (Uniform Resource Locator). Стандарт подключения к серверу базы данных позволяет использовать следующие методы getConnection с параметрами:

При использовании первого варианта, все параметры подключения указываются в url.

В следующей таблице представлен JDBC driver и синтаксис строки URL для подключения к СУБД.

СУБД	Драйвер JDBC	URL
Oracle	oracle.jdbc.OracleDriver	jdbc:oracle:oci[OCI_VERSION]:@[HOST_NAME]
Oracle	oracle.jdbc.OracleDriver	jdbc:oracle:thin:@ [HOST_NAME]:[PORT_NUMBER]:[DATABASE_NAME]
MSSQL	com.microsoft.jdbc.sqlserver.SQLServerDriver	jdbc:microsoft:sqlserver: //[HOST_NAME]:[PORT_NUMBER]
PostgreSQL	org.postgresql.Driver	jdbc:postgresql: //[HOST_NAME]:[PORT_NUMBER] /[DATABASE_NAME]
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://[HOST_NAME]:[PORT_NUMBER] /[DATABASE_NAME]

Подключение к СУБД Oracle, jdbc oracle

К серверу БД Oracle можно подключиться как через тонкий клиент, так и через толстый.

Пример подключения через тонкий клиент:

При подключении через толстый клиент, host и port не указываются :

Подключение к СУБД MS SQL, jdbc sql

Строка URL-адреса подключения имеет следующий вид:

• jdbc:sqlserver:// — обязательно, подпротокол.
• serverName — необязательно, адресом сервера, с которым выполняется соединение. Это может быть DNS, IP-адрес, локальный узел или 127.0.0.1 локального компьютера. Имя сервера необходимо указать в коллекции свойств, если оно не указано в URL-адресе соединения.
• instanceName — необязательно, наименование базы данных сервера, с которым выполняется соединение. Подключение выполняется к базе данных по умолчанию, если не указана другая.
• portNumber — необязательно, порт, который слушает сервер. Значение по умолчанию — 1433. Если соединение выполняется с портом по умолчанию, то в URL-адресе необязательно указывать порт.

Дополнительные свойства указывается в формате ключ=значение.

Примеры установления соединения с сервером БД

Подключение к СУБД PostgreSQL, jdbc postgresql

Для подключения к серверу БД PostgreSQL необходимо использовать один из следующих URL:

host — имя сервера, по умолчанию «localhost».

port — порт, который слушает сервер. По умолчанию используется порт 5432.

database — наименование базы данных. По умолчанию используется база данных postgres.

Примеры jdbc postgresql :

Подключение к СУБД MySQL, jdbc mysql

Пример динамической загрузки JDBC-драйвера и подключение к серверу рассмотрен здесь.

Источник

Register the JDBC drivers

To access a database from a Java application, you must first provide the code to register your installed driver with your program. You do this with the static registerDriver() method of the java.sql.DriverManager class. This class provides a basic service for managing a set of JDBC drivers. The registerDriver() method takes as input a «driver» class, that is, a class that implements the java.sql.Driver interface, as is the case with OracleDriver .

Note: Alternatively, you can use the forName() method of the java.lang.Class class to load the JDBC drivers directly. For example: Class.forName («oracle.jdbc.OracleDriver»); . However, this method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines.

You register the driver only once in your Java application.

Open a Connection to a database

Once you have registered the driver, you can open a connection to the database with the static getConnection() method of the java.sql.DriverManager class. The type of the object returned is java.sql.Connection .

Understanding the Forms of getConnection()

Specifying a Databse URL, User Name, and Password

The following signature takes the URL, user name, and password as separate parameters:

getConnection(String URL, String user, String password);

Where the URL is of the form:
jdbc:oracle: :@

The following example connects user scott with password tiger to a database with SID orcl through port 1521 of host myhost , using the Thin driver.

Connection conn = DriverManager.getConnection
(«jdbc:oracle:thin:@myhost:1521:orcl», «scott», «tiger»);

Specifying a Databse URL That Includes User Name and Password

The following signature takes the URL, user name, and password all as part of a URL parameter:

Where the URL is of the form:
jdbc:oracle: : /

The following example connects user scott with password tiger to a database on host myhost using the OCI driver. In this case, however, the URL includes the userid and password, and is the only input parameter.

Connection conn = DriverManager.getConnection
(«jdbc:oracle:oci8:scott/tiger@myhost);

If you want to connect with the Thin driver, you must specify the port number and SID. For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521, and the SID (system identifier) is orcl :

Connection conn = DriverManager.getConnection
(«jdbc:oracle:thin:scott/tiger@myhost:1521:orcl);

Specifying a Database URL and Properties Object

The following signature takes a URL, together with a properties object that specifies user name and password (perhaps among other things):

getConnection(String URL, Properties info);

Where the URL is of the form:
jdbc:oracle: :@

In addition to the URL, use an object of the standard Java Properties class as input. For example:

java.util.Properties info = new java.util.Properties();
info.put («user», «scott»);
info.put ( «password», «tiger» );
info.put («defaultRowPrefetch»,»15″);
getConnection («jdbc:oracle:oci8:@»,info);

The table below lists the connection properties that Oracle JDBC drivers support.

Connection Properties Recognized by Oracle JDBC Drivers

Источник

Register the JDBC drivers

The JDBC driver registration is automatically done via the Java Standard Edition Service Provider mechanism introduced in JDK6 . Oracle JDBC driver implements this feature and it is automatically registered if the Oracle JDBC driver jar is present in the classpath.

Open a Connection to a database

You can open a connection to the database with the static getConnection() method of the java.sql.DriverManager class. The type of the object returned is java.sql.Connection .

Understanding the Forms of getConnection()

Specifying a Databse URL, User Name, and Password

The following signature takes the URL, user name, and password as separate parameters:

getConnection(String URL, String user, String password);

Specifying a Database URL and Properties Object

The following signature takes a URL, together with a properties object that specifies user name and password (perhaps among other things):

getConnection(String URL, Properties info);

Where the URL is of the form:
jdbc:oracle: :@

In addition to the URL, use an object of the standard Java Properties class as input. For example:

java.util.Properties info = new java.util.Properties();
info.put («user», «scott»);
info.put («password»,»tiger»);
info.put («defaultRowPrefetch»,»15″);
getConnection («jdbc:oracle:thin:@»,info);

URL Formats

Oracle JDBC Thin Driver supports the following URL formats to establish a connection with an Oracle Database. Use any of the below format to establish connection to the Oracle Database.

EZConnect

As the name suggests the EZConnect URL is the easiest option to connect to Oracle.

Format :

In the above format, the parts that are enclosed in square brackets are optional. TCP and TCPS are supported protocols. Specifying the protocol is optional and the default value is TCP. The URL must contain at least one database host name. Multiple host names are separated by a comma. The host name is followed by an optional port number. If multiple hosts share the same port number then the port number can be specified at the end of the host names list. The default port value is 1521 . Host and port information is followed by the database service name which is in turn followed by the optional server mode and the database instance name. The supported server modes are dedicated|shared|pooled .

An EZConnect URL can have an optional list of name-value pairs as parameters after the ‘?’ delimiter. Name and value are separated by an ‘=’ and multiple properties are separated by an ‘&’.
The Thin driver creates a TNS URL using the information provided in the EZConnect format. While resolving to the TNS format, a name-value pair specified in the EZConnect format is resolved to a TNS URL parameter. If a name-value pair can not be resolved to a TNS URL parameter then it is handled as a connection property. The list of supported name value pairs are as follows

TNS URL Parameter:

Parameter Name	Description
ssl_server_dn_match	Specifies if DN matching is to be enforced. Applicable only for TCPS connection and the default is ON.
ssl_server_cert_dn	Specifies the DN of the database server.
https_proxy	Specifies proxy host to be used while establishing connection to the Oracle Database.
https_proxy_port	Specifies proxy port to be used while establishing connection to the Oracle Database.
enable	The keepalive feature on the supported TCP transports can be enabled for a client by specifying enable=broken
failover	To enable or disable connect-time failover for multiple protocol addresses. Supported values on | off
load_balance	To enable or disable client load balancing for multiple protocol addresses. Supported values on | off
recv_buf_size	To specify, in bytes, the buffer space for receive operations of sessions
send_buf_size	To specify, in bytes, the buffer space for send operations of sessions.
sdu	To instruct JavaNet to optimize the transfer rate of data packets being sent across the network with a specified session data unit (SDU) size in bytes.
source_route	To enable routing through multiple protocol addresses. Supported values on | off
retry_count	To specify the number of times an ADDRESS list is traversed before the connection attempt is terminated. The default value is 0.
retry_delay	To specify the delay in seconds between subsequent retries for a connection. This parameter works in conjunction with retry_count parameter.
service_tag	To specify the value of service tag. While establishing connection to the Oracle Database, the configured value is sent as part of connect data.
pool_connection_class	Use this parameter to explicitly name the connection class for DRCP connection.
pool_purity	Use this parameter to specify if an application needs a new session that is not tainted with any prior session state or to reuse a previous session. Supported values are NEW | SELF .

Connection Property Name-Value pairs:

Parameter Name	Description
wallet_location	Specifies the location of the wallet file. More Info..
ssl_ciphers	Use this to specify the set of cipher suites to be used while SSL protocol negotiation. More Info..
connect_timeout	To specify the timeout duration in seconds for a client to establish an Oracle Net session to an Oracle database. More Info..
transport_connect_timeout	To specify the transportation timeout duration in seconds for a client to establish an network connection to an Oracle Database. More Info..
encryption_client	Specifies the encryption level supported by client. Supported values are accepted | rejected | requested | required More Info..
encryption_types_client	Specifies the list of encryption algorithms supported by client. Supported algorithms are AES128, AES192, AES256, RC4_40, RC4_56, RC4_128, RC4_256, DES, DES40, 3DES112, 3DES168. More Info..
crypto_checksum_client	Specifies the data integrity level supported by client. Supported values are accepted | rejected | requested | required More Info..
crypto_checksum_types_client	Specifies the list of data integrity algorithms supported by client. Supported algorithms are SHA256, SHA384, SHA512, SHA1, MD5 More Info..

Other connection properties specified in oracle.jdbc.OracleConnection can also be configured through EZConnect format. Please use the value of the connection property constant name declared in oracle.jdbc.OracleConnection for configuring the property. Connection property constant names start with CONNECTION_PROPERTY_ .

Examples :

• jdbc:oracle:thin:@//salesserver1:1521/sales.us.example.com
• jdbc:oracle:thin:@//salesserver1,salesserver2:1521/sales.us.example.com
• jdbc:oracle:thin:@//salesserver1:1521,salesserver2,salesserver3:1522/sales.us.example.com
• jdbc:oracle:thin:@tcps://salesserver1:1521/sales.us.example.com oracle.net.tns_admin . Any environmental variables in the specified TNS ADMIN path will be resolved automatically.

Format :

Examples :

• jdbc:oracle:thin:@inst1
• jdbc:oracle:thin:@inst1?TNS_ADMIN=/work/tnsadmin/
• jdbc:oracle:thin:@inst1?TNS_ADMIN=$ORACLE_HOME/tnsadmin/
• jdbc:oracle:thin:@inst1?TNS_ADMIN=%ORACLE_HOME%/tnsadmin/

The JDBC Thin driver can retrive the TNS URL from an LDAP server. Use the following format for configuring the LDAP server details. JDBC Thin Driver uses the value of the orclnetdescstring attribute under the specified DN. If the LDAP connection is established using TCPS (ldaps) connection then the wallet / keystore properties can be specified through connection properties. Please see the LDAP connection properties in the below table. Optionally you can configure LDAP authentication details through connection properties or through wallet secret store.

Property Name	Description
oracle.net.ldap.ssl.walletLocation	Use this property to specify the wallet location. The driver will use this wallet while SSL negotiation with LDAP server. More Info..
oracle.net.ldap.ssl.walletPassword	Use this property to specify the password of the wallet file which will be used while SSL negotiation with LDAP Server. More Info..
oracle.net.ldap.security.authentication	Specifies the authentication mechanism to be used by the LDAP service provider in the JDK. Supported values none | simple . More Info..
oracle.net.ldap.security.principal	Use this property to specify the value of the username(DN) which will be used while authenticating with the LDAP server. This property can also be configured via wallet secret store entry oracle.ldap.client.dn More Info..
oracle.net.ldap.security.credentials	Use this property to configure the password which will be used while authenticating with the LDAP server. This property can also be configured via wallet secret store entry oracle.ldap.client.password More Info..

Format :

Examples :

• jdbc:oracle:thin:@ldap://myldapserver:2636/cn=orcl,cn=OracleContext,dc=example,dc=com
• jdbc:oracle:thin:@ldaps://myldapserver:1636/cn=orcl,cn=OracleContext,dc=example,dc=com

Setting JDBC Connection Properties

oracle.jdbc.OracleConnection contains constant fields for almost all the properties supported by the JDBC Driver. The name of the constants start with CONNECTION_PROPERTY_ . In Java code, the constants fields can be used directly. Use the String value of a constant field to configure the property through a URL or through a properties file. The following are the different methods of configuring JDBC connection properties.

While using the EZConnect URL format, the connection properties can be configured via URL. After the ‘?’ delimiter, Easy Connect supports a list of parameters as name-value pairs which can be optionally specified. The name and value of a connection property is separated by an ‘=’ and multiple properties are separated by an ‘&’

Examples :

• jdbc:oracle:thin:@//salesserver1:1521/sales.us.example.com?oracle.net.networkCompression=on jdbc:oracle:thin:@//salesserver1:1521/sales.us.example.com?oracle.net.networkCompression=on&oracle.net.networkCompressionThreshold=1024

Properties File

Connection properties can also be configured via a properties file named ojdbc.properties . This properties file should be present under the TNS ADMIN directory. The TNS ADMIN directory can be configured through the URL.

Examples :

• jdbc:oracle:thin:@//salesserver1:1521/sales.us.example.com?TNS_ADMIN=$ORACLE_HOME/tnsadmin/
• jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com))?TNS_ADMIN=$ORACLE_HOME/tnsadmin/

For more information, see

System Properties

Connection properties can be configured through System properties as well. The below example shows how to configure a system property for a standalone Java application.

java -Doracle.net.networkCompression=on MyApp

Configuring Credentials

The JDBC Driver supports multiple authentication mechanisms:

• Username and Password
• TCPS
• Kerberos
• Radius

Username and Password

A username and password pair is used to authenticate to the Oracle Database Server. The username and password are specified using the following options.

Driver Manager API :
java.sql.DriverManager.getConnection(String url, String user, String password)
Connection Properties :
Use the connection properties userand passwordfor configuring the username and password respectively.
URL :
A username and password can be configured through URL.
Example: jdbc:oracle:thin:myuser/mypassword@//salesserver1:1521/sales.us.example.com
Wallet :
With a wallet location configured using the connection property oracle.net.wallet_location the JDBC Thin driver retrives the username and password from the wallet. Please refer to Oracle documentation on how to store the username and password to the wallet file.

The JDBC Driver can authenticate to the Oracle Database using a certificate from a wallet / keystore. For this, the Oracle Database Server has to be configured to support the TCPS authentication. And, the JDBC driver must be configured with the connection property oracle.net.authentication_services = (TCPS) along with wallet / keystore properties.

Kerberos

The JDBC Driver can authenticate to the Oracle Database using Kerberos. For this, the Oracle Database Server has to be configured to support Kerberos authentication. Please refer to Oracle documentation on how to configure the Oracle Database for Kerberos authentication. Configure the following connection properties to enable Kerberos authentication in the JDBC Driver. oracle.net.authentication_services=(KERBEROS5)
oracle.net.kerberos5_mutual_authentication=true
oracle.net.kerberos5_cc_name=

Additional configuration can be specified in a configuration file indicated by the system property java.security.krb5.conf . Please refer to JDK documentation for more information.

RADIUS

The JDBC Driver can authenticate to the Oracle Database using RADIUS. For this, the Oracle Database Server has to be configured to support RADIUS authentication. Please refer to Oracle documentation on how to configure the Oracle Database for RADIUS authentication. Configure the following connection properties to enable RADIUS authentication in the JDBC driver.

Источник