➤ Intro to JDBC
➤ JDBC vs ODBC
➤ JDBC API
➤ JDBC Driver
Connect to Database
➤ Oracle Connection
➤ MySQL Connection
➤ Using Eclipse IDE
➤ Coding Standards
➤ Close JDBC Object
➤ Java.sql Package
➤ DriverManager Class
➤ Get Connection
➤ Statement Interface
➤ Statement Example
➤ Create a database
➤ Types of statements
➤ Statement vs PreParedStatement
➤ Insert record
➤ Update record
➤ Select record
➤ Create a table
➤ More Examples
➤ Surrogate Key
➤ ResultSetMetaData & ParameterMetaData
To develop a JDBC application, the most important part is to get connection. In this post, we will discuss how to create or get a JDBC connection. We will see the same codes to get a JDBC connection with different database software.
A Connection object represents a connection with a database. When we connect to a database by using a getConnection() method, we create a Connection object, which represents the connection to the database. An application may have one or more connections with a single database or many connections with the different databases also.
To get/create a JDBC connection in java, firstly we need to establish a connection with the database. This is done by using the method DriverManager.getConnection() method. This method takes a string containing a URL.
The DriverManager class in Java attempts to locate a driver that can connect to the database represented by the string URL. Whenever the getConnection() method is called then the DriverManager class checks the list of all registered Driver classes that can connect to the database specified in the URL.
Overloaded Methods in JDBC to get Connection
There are three forms of DriverManager.getConnection(), those are:-
- getConnection(String url):- it tries to establish the connection to a given database URL. This method is used to establish the Connection of single-user database software like ms-access.
- getConnection(String url, String user, String password):- It tries to establish the connection to a given database URL. It is used to establish the Connection of multiuser database software like Oracle, Sybase.
- getConnection(String url, Properties props):- It tries to establish the connection to a given database URL and its properties.
All these three methods return the Connection object, and it throws SQLException and SQLTimeoutException.
SQLException – It raises if a database access error occurs or the url is null.
SQLTimeoutException – It raises when the driver has determined that the timeout value specified by the setLoginTimeout method has been exceeded and has at least tried to cancel the current database connection attempt.
The protocol is a set of rules to follow by two parties who want to participate in the communication. There are two types of protocols.
1) Network-level protocol:- It defines the set of rules to get communication between two physical computers. Example:- TCP/IP
2) Application-level protocol:- It defines a set of rules to get communication between two software applications or two software services. Example:- HTTP, jdbc:oracle
Port number: All software installed on our computer will run on various software ports managed by operating system. Every software port is identified with port number. In Windows operating system total 65535 software ports are available, in that 1 to 1024 software port numbers reserved for operating system services, so all externally installed softwares use remaining software port number between 1025 to 65535.
Socket is communication end point. Socket number = <ip address of computer/hostname of computer> + <port number of software service>
Difference between physical database software and logical database software?
The logical database is a logical partition of physical database software, every logical database contains separate database tables, PL/SQL procedure, functions, view and etc. In one physical database, we can create multiple logical databases as per requirement. In Oracle, every logical database is identified with its sid (service id). We always get 1 default logical database along with oracle database software installation.
If a software company having 10 projects using the Oracle database software then the Oracle database software will be installed only for 1 time in a common machine but multiple logical databases will be created in that physical database software on 1 per-project basis.
Sample code to get connection with database software
Get the connection in Oracle database using Oracle thin driver,
// establish the connection Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:knowprogram", "username", "password"); // place your own url, username, password
Also See:- Oracle Database JDBC Connection with Example
How to create MySQL database connection in Java,
// establish the connection Connection con = DriverManager.getConnection( "jdbc:mysql:///dbname","username","password"); // place your own url, username, password
How to create PostgreSQL database connection in Java,
// Establish the connection Connection con = DriverManager.getConnection( "jdbc:postgresql:dbname","username","password"); // place your own url, username, password
PostgreSQL database connection with in Java
How getConnection() return JDBC Connection object
When java.sql.Connection is an interface then how can we say that DriverManager.getConnection() return JDBC Connection object?
The JDBC Connection object is not the object of java.sql.Connection(I). It is the object of underlying JDBC driver software supplied Java class that implements java.sql.Connection(I) directly or indirectly. This class name changes based on the JDBC driver we use so we never specify this class in our Java application.
We can get the class name using the getClass() method. The JDBC Connection object class name for Oracle thin driver is:
// Establish the connection Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:knowprogram", "username", "password"); // display class name of con object System.out.println(con.getClass());
Output:- class oracle.jdbc.driver.T4CConnection
Interface reference variable can refer its implementation class object. DriverManager.getConnection() internally creates and returns implementation class object of java.sql.Connection interface. So, we can refer that object using java.sql.Connection(I) reference variable. Another example:-
- Clonable object means it is the object of the Java class that implements java.lang.Clonable interface.
- A Serializable object means it is the object of the Java class that implements java.io.Serializable interface.
- Connection object means it is the object of the Java class that implements java.sql.Connection interface.
Important points on method return types:
- If the method return type is an interface, then the method returns one implementation class object of that interface.
- When the method return type is an abstract class, then the method returns one subclass object of that abstract class.
- If the method return type is a concrete class, then the method can return either that concrete class object or one of its subclass objects.
Use of Connection objects
We can use the Connection object for the following things:
1. It creates the Statement, PreparedStatement and CallableStatement objects for executing the SQL statements.
2. It helps us to commit or roll back a JDBC transcation.
3. If we want to know about the database or data source to which we are connected then the Connection object gathers information about the database or data source by the use of DatabaseMetaData.
4. It helps us to close the data source. The Connection.isClosed() method returns true only if the Connection.close() has been called. This method is used to close all the connections.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!
- JDBC Statement interface
- JDBC statement example
- Different types of statements in JDBC
- JDBC PreparedStatement interface
- Statement vs PreParedStatement
- Insert record using PreparedStatement in Java
- Update record using Java PreparedStatement
- Select record using PreparedStatement in Java
- JDBC create table using statements
- Preparedstatement Example
- JDBC program to insert with Surrogate Key