Connect to Oracle DB via JDBC driver
A JDBC example to show you how to connect to a Oracle database with a JDBC driver.
Tested with:
- Java 8
- Oracle database 19c
- Oracle JDBC driver for Java 8,
ojdbc8.jar
1. Download Oracle JDBC Driver
Visit Oracle database website and download the Oracle JDBC Driver.
2. JDBC Connection
Note
Find your Oracle SID in
Find your Oracle SID in
{ORACLE_HOME}/network/admin/tnsnames.ora
to avoid the popular ORA-12505, TNS:listener does not currently know of SID
2.1 Make a connection to the Oracle database.
JDBCExample.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCExample {
public static void main(String[] args) {
// https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html#package.description
// auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName
// register JDBC driver, optional since java 1.6
/*try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}*/
// Oracle SID = orcl , find yours in tnsname.ora
try (Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "system", "Password123")) {
if (conn != null) {
System.out.println("Connected to the database!");
} else {
System.out.println("Failed to make connection!");
}
} catch (SQLException e) {
System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
Compile and run:
C:\test> javac JDBCExample.java
C:\test> java JDBCExample
SQL State: 08001
No suitable driver found for jdbc:oracle:thin:@localhost:1521:orcl
2.2 Assume ojdbc8.jar
and JDBCExample.java
are stored in c:\test
together. Define a -cp
option to load everything together:
> java -cp "c:\test\ojdbc8.jar;c:\test" JDBCExample
Connected to the database!
3. Maven Project
3.1 Sorry, due to Oracle license restriction, the Oracle JDBC driver is NOT available in the Maven central repository. Follow this guide to add it – How to add Oracle JDBC driver in your Maven local repository
3.2 Alternatively, defined a system scope to find the .jar
file with a specified system path.
pom.xml
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc</artifactId>
<version>8</version>
<scope>system</scope>
<systemPath>d:/projects/ojdbc8.jar</systemPath>
</dependency>
Download Source Code
$ git clone https://github.com/mkyong/java-jdbc.git
https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8/21.3.0.0
Driver in maven now
Very simple and easy to understand!
Thank you!
Wow – I’m impressed that noone before me found out that in the complete example there is no closing of the connection using the connection.close(); as described in the first example…
Four years….
I am getting following error:
C:\oracle\product>java -cp C:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar;C:\oracle\product\11.2.0\client_1\jdbc\lib oraclejdbc
Error: Could not find or load main class oraclejdbc
plz explain in detail
Assume everything in
c:\test
java -cp "c:\test\ojdbc8.jar;c:\test" JDBCExample
Excellent tutorial, which should change if I use JDK 11
Life saver, thanks, upgraded to 19c, and suddenly my connection did not work. Did not realise no longer need to load the driver and also the conn string changed from /SID to :SID.
Is it possible to save the connection string/user name/password into a file, similar to application.properties and read them back in the program? Thanks!
I’m getting following error:
Exception in thread “main” java.lang.VerifyError: Bad return type
Exception Details:
Location:
oracle/jdbc/driver/T4C8TTIBlob.createTemporaryLob(Ljava/sql/Connection;ZI)Loracle/sql/Datum; @152: areturn
Reason:
Type ‘oracle/sql/BLOB’ (current frame, stack[0]) is not assignable to ‘oracle/sql/Datum’ (from method signature)
thanks:)
Hi mkyong,
I used ojdbc8.jar to get database status but it suddenly stopped working for oracle 9i one day. Now, it is working with ojdbc14.jar. What could be the reasons?
I had this error 🙁
Oracle JDBC Driver Registered!
Exception in thread “main” java.lang.NullPointerException
at oracle.net.jndi.JndiAttrs.getAttrs(JndiAttrs.java:215)
at oracle.net.resolver.AddrResolution.(AddrResolution.java:237)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:233)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at OracleJDBCExample.main(OracleJDBCExample.java:29)
Can you please help?
Hello!
I’m constantly getting NullPointerException at string “connection = DriverManager.getConnection(…”. Checked everything (url, sid, login, password) and still get this exception. Could you please advise what could be the problem?
Stack is given below:
Exception in thread “main” java.lang.NullPointerException
at java.base/java.lang.String.(String.java:250)
at oracle.sql.CharacterSet.AL32UTF8ToString(CharacterSet.java:1517)
at oracle.jdbc.driver.DBConversion.CharBytesToString(DBConversion.java:589)
at oracle.jdbc.driver.DBConversion.CharBytesToString(DBConversion.java:542)
at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOauth(T4CTTIoauthenticate.java:816)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:362)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:414)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at testBaseConnection.main(testBaseConnection.java:21)
Thank you in advance!
Found reason. It was ojdbc driver, it was ojdbc14, when instruction clearly stated to use ojdbc 6 or 7.
Thank you for this instruction.
Thanks. Worked in the first attempt.
How jdbc register driver will work if static blocks are not getting executed?
thanks
Very easy and success!
Thank you.
Hello Mkyong!
First I had the “ORA-12505, TNS:listener does not currently know of SID given in connect descriptor” problem.
Then I replaced the “xe” by the correct “service name” that was in the tnsnames.ora
Thanks for the example, it worked fine for me!
Check
tnsname.ora
to find your Oracle SID for the connection.https://www.mkyong.com/jdbc/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descriptor/
Hi Mkyong
Can you please guide me how to use TNSNAMES.ORA file Java JDBC Connection example above?
Regards
-Haris
i’m having a question for this code….
if we want to connect to Oracle 11g database,
from what you just wrote on the code above,…
it says : “org.postgresql.Driver” -> why we wrote postgresql instead of Oracle 11g?
Very useful. Thank you! Simple, working, clear.
If we create multiple instance of the connection class which includes same URL, Username and password. When we close the connection, will all connections created using same username and password will also be closed?
Thank’s a lot. I have done it.
Thanks for easy to imbibe page.
Please guide,
is it possible to create a persistent Database connection and use it for all database access for all sessions in web application as separate connection may cause exhausting number of processes in oracle.
How can we specify the address of Jdbc
my jdbc is located in oracle
please give me reply
thanks it worked !
here is is my output
run:
——– Oracle JDBC Connection Testing ——
Oracle JDBC Driver Registered!
You made it, take control your database now!
BUILD SUCCESSFUL (total time: 0 seconds)
hi, above code was useful to get connection. thanks
Thanks, it’s very nice tutor.
But why using “PostgreSQL” driver for Oracle connection?
>>
Class.forName(“org.postgresql.Driver”);
//…
>>
Sorry, typo, fixed.
i’m trying to connect oracle database by ojdbc14.jar where should i save it and how to run this..;;
bcause i’m finding a error that is claasnotfound..
pls help me..
right click on computer and go to properties and go to advanced system settings and go to environment variables and click on new
and variable name classpath and in variable path E:oracleproduct10.2.0db_1jdbclibojdbc14.jar;
then prss ok its connected to database
Nice simple post for quick reference!
Thanks a tonnnne Mr.Mkyong. I never thought oracle odbc connectivity would be this simple.. Thanks again..:-)
That’s JDBC, not ODBC. I know, “OJDBC” confuses me some times also 🙂