How To Connect To A Database in Java? — JDBC Tutorial
Java, being one of the most prominent programming languages, provides extensive support to databases. It helps us to connect to various databases through JDBC (Java Database Connectivity). In this article, I will tell you how to connect to a database and execute queries using JDBC.
Below topics are covered in this article:
- Introduction to JDBC
- Common JDBC Components
- Steps to create JDBC Application
- JDBC Connections
Introduction to JDBC
JDBC is one of the standard Java API for database-independent connectivity the This API lets you encode the access request statements, in Java programming language and a wide range of databases. Structured Query Language (SQL). This mainly involves opening a connection, creating a SQL Database, executing SQL queries, and then arriving at the output.
JDBC API can be used to access tabular data stored in any relational database. With this, you can update, save, fetch and delete the data from the databases. It is similar to the Open Database Connectivity (ODBC) provided by Microsoft.
For a better understanding of the working of JDBC, let’s dive deeper into the topic and understand the architecture that lies behind Java Database Connectivity.
Common JDBC Components
The JDBC API provides the following interfaces and classes −
- DriverManager: This is mainly used to manage a list of database drivers. The driver that recognizes a certain subprotocol will be used to establish a database connection.
- The Driver is an interface that handles the communications with the database server. It also abstracts the details that are associated while working with Driver objects.
- A Connection is an interface that consists of all the methods required to connect to a database. The connection object deals with the communication functions of the database. context.
Now let’s move on to the next topic and look at the steps required to create a JDBC Application.
Steps to create JDBC Application
In order to create a JDBC Application, you need to follow a few steps. Let’s see what are they.
- Import the packages: You need to include all the packages that contain the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
- Register the JDBC driver: Here you have to initialize a driver so that you can open a communication channel with the database.
- Open a connection: Here, you can use the getConnection() method to create a Connection object, which represents a physical connection with the database.
- Execute a query: This actually requires using an object of type Statement for building and submitting an SQL statement to the database.
- Extract data from the result set: It is suggested that you use the appropriate getXXX() method to retrieve the data from the result set.
- Clean up the environment: Here, it is essential to explicitly close all database resources versus relying on the JVM’s garbage collection.
Now as you have seen various steps involved to create a JDBC Application, let’s see an example code to create a database and establish a connection.
package Edureka;
import java.sql.*;
import java.sql.DriverManager;
public class Example {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/emp";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.cj.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,"root","");
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
//STEP 6: Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close()
}catch(SQLException se2){
}// nothing can be done
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
} // end ExampleAbove code creates a table in your localhost database. To insert the values in the created database, you can refer to the below code. I will be writing the code only for step 4. Rest of the code remains the same as above.
//STEP 4: Execute a query
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
String sql = "CREATE TABLE EMPLOYEES " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
System.out.println("Inserting records into the table...");
stmt =conn.createStatement();
String sql ="INSERT INTO Employees VALUES (100, 'Kriss', 'Kurian', 18)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Employees VALUES (101, 'Enrique', 'John', 25)";
stmt.executeUpdate(sql);
sql= "INSERT INTO Employees (102, 'Taylor', 'Swift', 30)";
stmt.executeUpdate(sql);
sql= "INSERT INTO Employees VALUES(103, 'Linkin', 'Park', 28)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");So this is how you can establish a connection to the database and insert values in the tables. Now let’s move further and understand various JDBC Driver Types
JDBC Driver Types
JDBC drivers are used to implementing the defined interfaces in the JDBC API, for interacting with the database server. Essentially, a 1. Establishes a connection with a data source. JDBC driver does three things and they are as follows:
2. It will send queries and update statements to the data source.
3. Finally, it processes the results.
For example, JDBC drivers help you to open a database connection to interact with it by sending SQL or database commands. If you wish to know more about types of JDBC drivers, you can refer to this article on JDBC Drivers.
Now let’s move further and understand JDBC Connections.
JDBC Connections
- Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code.
- Register JDBC Driver: In this step, JVM to loads the desired driver implementation into memory so that it can fulfill the JDBC requests. There are 2 approaches to register a driver.
- The most suitable approach to register a driver is to use Java’s forName() method to dynamically load the driver’s class file into memory, which automatically registers it. This method is suitable as it allows you to make the driver registration configurable and portable. Take a look at the below code:
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex)
System.out.println("Error: unable to load driver class!");
System.exit(1);
}- The second approach you can use to register a driver is to use the static registerDriver()method.
try {
Driver myDriver = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex)
{
System.out.println("Error: unable to load driver class!");
System.exit(1);
}- You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft. Here each form requires a database URL.
- Database URL Formulation: URL Formulation is necessary to create a properly formatted address that points to the database to which you want to connect. Once you loaded the driver, you can establish a connection using the DriverManager.getConnection() method. DriverManager.getConnection() methods are−
- getConnection(String url)
- getConnection(String url, Properties prop)
- getConnection(String url, String user, String password)
- Create a connection object
You can create a connection using the database URL, username, and password and also using properties object.
- Close
Finally, to end the database session, you need to close all the database connections. However, if you forget, Java’s garbage collector will close the connection when it cleans up stale objects.
conn.close(); // Used to close the connectionThat was all about Java Database Connectivity. If you wish to know more about JDBC, you can refer to this article on Advanced Java Tutorial. This brings us to the end of the article on how to connect to a database. I hope I have thrown some light on your knowledge of JDBC.
If you wish to check out more articles on the market’s most trending technologies like Artificial Intelligence, Python, Ethical Hacking, then you can refer to Edureka’s official site.
Do look out for other articles in this series that will explain the various other aspects of Java.
1. Object Oriented Programming
5. Java String
6. Java Array
8. Java Threads
9. Introduction to Java Servlets
11. Exception Handling in Java
12. Java Tutorial
14. Java Programs
15. Kotlin vs Java
16. Dependency Injection Using Spring Boot
22. Socket Programming In Java
25. Library Management System Project in Java
26. Trees in Java
28. Top Data Structures & Algorithms in Java
30. Top 55 Servlet Interview Questions
34. Java Collections Interview Questions and Answers
35. How to Handle Deadlock in Java?
36. Top 50 Java Collections Interview Questions You Need to Know
37. What is the concept of String Pool in Java?
38. What is the difference between C, C++, and Java?
39. Palindrome in Java- How to check a number or string?
40. Top MVC Interview Questions and Answers You Need to Know
41. Top 10 Applications of Java Programming Language
42. Deadlock in Java
43. Square and Square Root in Java
Originally published at https://www.edureka.co on July 12, 2019.
