In this post, we will see how to connect Java application with MySQL database.
JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.
JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.
Technologies used
We use below technologies in this post:
- JDK - 1.8 or later
- MySQL - 5.7.12
- IDE - Eclipse Neon
- JDBC - 4.2
Fundamental Steps in JDBC
The fundamental steps involved in the process of connecting to a database and executing a query consist of the following:- Import JDBC packages
- Load and register the JDBC driver // This step is not required in Java 6 and in JDBC 4.0
- Open a connection to the database.
- Create a statement object to perform a query.
- Execute the statement object and return a query resultset.
- Process the resultset.
- Close the resultset and statement objects. // This step is not required because we use a try-with-resource statement to auto-close the resources
- Close the connection. // This step is not required because we use a try-with-resource statement to auto-close the resources
From the above steps, we actually require below five steps to connect a Java application to the database (example: MySQL):
- Import JDBC packages
- Open a connection to the database.
- Create a statement object to perform a query.
- Execute the statement object and return a query resultset.
- Process the resultset.
Key points
- From JDBC 4.0, we don't need to include 'Class.forName()' in our code to load JDBC driver. JDBC 4.0 drivers that are found in your classpath are automatically loaded.
- We have used try-with-resources statements to automatically close JDBC resources.
Download MySQL JDBC Driver
Download and add MySQL JDBC driver to your project classpath. Here is the link: https://www.javaguides.net/2019/11/mysql-connector-java-maven-dependency.html
Database Setup
Create a database with the following command:
create database mysql_demo;
In this example, we will create a users table using SQL script:
create table users(
id int(3) primary key,
name varchar(20),
email varchar(20),
country varchar(20),
password varchar(20)
);
Here is the insert SQL script:
INSERT INTO Users VALUES (1, 'Pramod', 'pramod@gmail.com', 'India', '123'),
(2, 'Deepa', 'deepa@gmail.com', 'India', '123'),
(3, 'Tom', 'top@gmail.com', 'India', '123');
Java program to connect to MySQL database and retrieve records from database using JDBC API
Let's put all the steps together and here is the complete example with output:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Select Statement JDBC Example
* @author Ramesh Fadatare
*
*/
public class SelectStatementExample {
private static final String QUERY = "select id,name,email,country,password from Users";
public static void main(String[] args) {
// using try-with-resources to avoid closing resources (boilerplate code)
// Step 1: Establishing a Connection
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");
// Step 2:Create a statement using connection object
Statement stmt = connection.createStatement();
// Step 3: Execute the query or update query
ResultSet rs = stmt.executeQuery(QUERY)) {
// Step 4: Process the ResultSet object.
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
String country = rs.getString("country");
String password = rs.getString("password");
System.out.println(id + "," + name + "," + email + "," + country + "," + password);
}
} catch (SQLException e) {
printSQLException(e);
}
// Step 4: try-with-resource statement will auto close the connection.
}
public static void printSQLException(SQLException ex) {
for (Throwable e: ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
Output:
1,Ram,tony@gmail.com,US,secret
3,Pramod,pramod@gmail.com,India,123
4,Deepa,deepa@gmail.com,India,123
5,Tom,top@gmail.com,India,123
Related posts
Example to create a table using a Statement interface.
Example to insert multiple records in a table using Statement interface.
Example to update a record in a table using Statement interface.
Example to retrieve records from a table using Statement interface.
Example to delete a record from a table using a Statement interface.
Example to insert records in a batch process via Statement interface.
Example to update records in a batch process via Statement interface.
Example to insert a record in a table using the PreparedStatement interface.
Example to update a record in a table using the PreparedStatement interface.
Example to retrieve records from a table using the PreparedStatement interface.
Example to pass a list of values to IN clause using PreparedStatement interface.
Example to insert records in a batch process via PreparedStatement interface.
Example to update records in a batch process via PreparedStatement interface.
Example to insert multiple records in a table using Statement interface.
Example to update a record in a table using Statement interface.
Example to retrieve records from a table using Statement interface.
Example to delete a record from a table using a Statement interface.
Example to insert records in a batch process via Statement interface.
Example to update records in a batch process via Statement interface.
Example to insert a record in a table using the PreparedStatement interface.
Example to update a record in a table using the PreparedStatement interface.
Example to retrieve records from a table using the PreparedStatement interface.
Example to pass a list of values to IN clause using PreparedStatement interface.
Example to insert records in a batch process via PreparedStatement interface.
Example to update records in a batch process via PreparedStatement interface.
Comments
Post a Comment
Leave Comment