How to connect to Oracle database from Java Program using JDBC API is common to need for many Java programmer, though there is a lot of framework available which has simplified JDBC development e.g Hibernate, Spring JdbcTempate and many more, creating Java program to connect to oracle database from plain old Java is still the easiest and quickest method for testing and debugging database connectivity. The database connection program is also a common Java programming exercise in many Java programming courses in schools, colleges, and various training institutes.
We have been exploring some advanced concepts and best practices on JDBC in my previous articles like Why should you use PreparedStatement in Java and 4 JDBC performance tips for Java application, which you may like if you are on a more advanced level. This simple java program is intended for beginners in Java who have just started learning JDBC API.
We have been exploring some advanced concepts and best practices on JDBC in my previous articles like Why should you use PreparedStatement in Java and 4 JDBC performance tips for Java application, which you may like if you are on a more advanced level. This simple java program is intended for beginners in Java who have just started learning JDBC API.
If you like to read tutorials on the database then you may find a difference between truncate and delete, 10 examples of SQL SELECT queries, and how to manage database transactions useful and interesting.
How to connect Oracle Database from Java Program using JDBC - code example
Here is a complete code example of a Java program to connect the Oracle database using JDBC. This Java program will connect to the Oracle database and print the current date from the "dual" system table. By the way before running this Java program to connect the Oracle database make sure your Oracle database server is running and you have JDBC thin driver in your classpaths like ojdbc6.jar or ojdbc6_g.jar.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* Simple Java Program to connect Oracle database by using Oracle JDBC thin driver
* Make sure you have Oracle JDBC thin driver in your classpath before running this program
* @author
*/
public class OracleJdbcExample {
public static void main(String args[]) throws SQLException {
//URL of Oracle database server
String url = "jdbc:oracle:thin:@localhost:1632:DEVROOT32";
//properties for creating connection to Oracle database
Properties props = new Properties();
props.setProperty("user", "scott");
props.setProperty("password", "tiger");
//creating connection to Oracle database using JDBC
Connection conn = DriverManager.getConnection(url,props);
String sql ="select sysdate as current_day from dual";
//creating PreparedStatement object to execute query
PreparedStatement preStatement = conn.prepareStatement(sql);
ResultSet result = preStatement.executeQuery();
while(result.next()){
System.out.println("Current Date from Oracle : " + result.getString("current_day"));
}
System.out.println("done");
}
}
Output:
Current Date from Oracle : 2012-04-12 17:13:49
done
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* Simple Java Program to connect Oracle database by using Oracle JDBC thin driver
* Make sure you have Oracle JDBC thin driver in your classpath before running this program
* @author
*/
public class OracleJdbcExample {
public static void main(String args[]) throws SQLException {
//URL of Oracle database server
String url = "jdbc:oracle:thin:@localhost:1632:DEVROOT32";
//properties for creating connection to Oracle database
Properties props = new Properties();
props.setProperty("user", "scott");
props.setProperty("password", "tiger");
//creating connection to Oracle database using JDBC
Connection conn = DriverManager.getConnection(url,props);
String sql ="select sysdate as current_day from dual";
//creating PreparedStatement object to execute query
PreparedStatement preStatement = conn.prepareStatement(sql);
ResultSet result = preStatement.executeQuery();
while(result.next()){
System.out.println("Current Date from Oracle : " + result.getString("current_day"));
}
System.out.println("done");
}
}
Output:
Current Date from Oracle : 2012-04-12 17:13:49
done
Error and Exception while connecting Oracle Database from Java Program:
1) Invalid Username and Password
Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452)
This Error comes when username and password provided to Java program connecting to Oracle database is not correct.
2)No suitable driver found
Exception in thread "main" java.sql.SQLException: No suitable driver found for
jdbc:oracle:thin:@localhost:1632:DEVROOT32
at java.sql.DriverManager.getConnection(DriverManager.java:602)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
This Error comes when JDBC thin driver for relevant Oracle version is not in Classpath. e.g. ojdbc6.jar or ojdbc6_g.jar (compiled with javac -g with debug information) for Oracle 11g.
Also while reading data from ResultSet ensure that you are using proper column index to avoid Exception in thread "main" java.sql.SQLException: Invalid column index which comes when you use invalid index like zero to access ResultSet on various get and set methods.
In this Java program Example, we have seen how to connect to Oracle database using JDBC thin driver, with a thin driver it's much easier to connect to oracle database as you don’t need to create data sources like you do if you use JDBC ODBC Driver. Let me know if you face any issues while connecting to the Oracle database from Java Program. Another worth noting point is connecting the Oracle database using SSL from Java Program, which may see in another java tutorial.
Other Java Programming tutorial you may like
21 comments :
is there any difference when you connect to Oracle 10g or 11g database ? do I need to change JDBC driver for Oracle 11g datbase?
don't we have to load the driver using Class.forName("driver")
but how set thin driver in classpath
Class.forName("driver") is not needed in Java 6 and higher :)
when i write code of line to register the driver
DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());
i found the error like this
The constructor JdbcOdbcDriver() is not
accessible due to restriction on required library
C:\Program Files\Java\jre6\lib\rt.jar
please help?
Download ojdbc6.jar and import it to the classpath. This would resolve the issue.
I've set the class path for driver in .bashrc but it still says "No suitable driver found"
How to set the classpath to ojdbc6.jar in solaris OS??? People please help me out..
C:\Users\Sarveshwar\Desktop>javac -cp E:\app\Sarveshwar\product\11.2.0\dbhome_1\jdbc\lib\ojdbc5_2.jar;C:\User\Sarveshwar\Desktop OracleJdbcExample
error: Class names, 'OracleJdbcExample', are only accepted if annotation processing is explicitly requested
1 error
C:\Users\Sarveshwar\Desktop>javac -cp E:\app\Sarveshwar\product\11.2.0\dbhome_1\jdbc\lib\ojdbc5_2.jar;C:\User\Sarveshwar\Desktop OracleJdbcExample
error: Class names, 'OracleJdbcExample', are only accepted if annotation processing is explicitly requested
1 error
C:\Users\Sarveshwar\Desktop>javac -cp E:\app\Sarveshwar\product\11.2.0\dbhome_1\jdbc\lib\ojdbc5_2.jar;C:\User\Sarveshwar\Desktop OracleJdbcExample
error: Class names, 'OracleJdbcExample', are only accepted if annotation processing is explicitly requested
1 error
C:\Users\Sarveshwar\Desktop>javac OracleJdbcExample.java
C:\Users\Sarveshwar\Desktop>java OracleJdbcExample
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@localhost:1632:DEVROOT32
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at OracleJdbcExample.main(OracleJdbcExample.java:23)
I am not able connect using JDBC thin driver. It gives me a error ORA-01756 'Quoted string not properly terminated' which does not seem relevant. I am only trying to establish a database connection and not executing any SQL statement. Any thoughts?
import java.lang.Exception;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class java
{
static String url ="jdbc:odbc:vinn";
static Connection con =null;
public static void main(String[] args) throws SQLException {
Connection con =getOracleJDBCConnection();
if(con!=null)
{
System.out.println("got connection");
DatabaseMetaData meta=con.getMetaData();
System.out.println("Driver Name:"+meta.getDriverName());
System.out.println("Driver Version:"+meta.getDriverVersion());
}
else{
System.out.println("could not find connection");
}
}
public static Connection getOracleJDBCConnection()
{
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(java.lang.ClassNotFoundException e)
{
System.err.println("ClassNotFoundException:");
System.err.println(e.getMessage());
}
try{
con=DriverManager.getConnection(url);
}
catch(SQLException ex)
{
System.err.println("SQLException:" + ex.getMessage());
}
return con;
}}
when i compile this program an error is occured i.e. cannot find sysmbol java.lang in the 29th line ....it's a program for testing jdbc connection driver name and its version...
how to resolve this issue....
i m compiling this program in the cmd
If you using eclipse , than change the program properties to jdk 1.6/7
If anyone has any trouble finding ojdbc6.jar e.g. not able to download from internet or internet is not available. Always remember that one copy of this JAR is available when you install Oracle Client or Server in your machine. You can find this JAR in following location :
$ORACLE_HOME/jdbc/lib/ojdbc6.jar
Also JDBC Connection URL for Oracle can take variety of format e.g. it can include Service name or SID. following are some valid combination of Oracle JDBC URL :
jdbc:oracle:thin:user/xxxx@server:port:SID
jdbc:oracle:thin:user/xxxx@:SID
jdbc:oracle:thin:user/xxxx@//server:port/XE
Cheers
Can you please tell me why I am getting error below. When I was run the program I got that error.
package com.java.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseTest {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521:orcle","system","Infy1234");
System.out.println("connected.."+con);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
======================
error:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
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.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at com.infy.DatabaseTest.main(DatabaseTest.java:11)
Please check if host and port is correct. You can try ping/telnet on that host and port from your host and see what it says.
It says "IP address must be specified." Still got same error. host and port are correct.
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcle","system","Infy1234");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcle","system","Infy1234");
The connection is in my localhost. still i am getting the same error. Pleas give a solution.
@Bapan, did the "he Network Adapter could not establish the connection" goes away or it still there. What is the "orcle" in your connection URL. You should also check if any of following format work:
jdbc:oracle:thin:user/xxxx@server:port:SID
jdbc:oracle:thin:user/xxxx@:SID
jdbc:oracle:thin:user/xxxx@//server:port/XE
even i am set classpath to ojdbc6,i am getting the error sun.jdbc.odbc.JdbcOdbcDriver not foun
In Java8, How to connect the database now, once JBDC ODBC driver is removed.
can you help me my code was successfully run but not display output on command prompt
what i can do?plz help me
import java.sql.*;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
class OracleCon
{
public static void main(String args[])
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@elcot-pc:1521:system","system","tiger");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
Post a Comment