JDBC with MYSQL

What is Data?

1. Data is a collection of a distinct small unit of information.
2. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.
3. In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable.

What is a Database?

• A database is an organized collection of data, so that it can be easily accessed and managed. You can organize data into tables, rows, columns, and index it to make it easier to find relevant information.
• Database handlers create a database in such a way that only one set of software programs provides access to data to all the users.
• The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.

Problems with not using a database

1.Size of Data.

2. Ease of Updating Data.

3. Accuracy.

4. Security.

5. Redundancy.

6. Incomplete Data .

Need for Database !!

1. Processing Queries and Object Management.

2. Controlling redundancy and inconsistency.

3. Efficient memory management and indexing.

4. Concurrency control and transaction management.

5. Access Control and ease in accessing data.

File System vs Database

What is JDBC?

JDBC is a standard Java API for database independent connectivity between the java programming language and a wide range of databases. We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we can save, update, delete and fetch data from the database.

JDBC Database connectivity

There are 5 steps to connect any java application with the database using JDBC.

1. Register the Driver class

2. Create connection

3. Create statement

4. Execute queries

5. Close connection

Import JDBC Packages

The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code.

To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your

source code −

import java.sql.* ;  // for standard JDBC programs
import java.math.* ; // for BigDecimal and BigInteger support

Register JDBC Driver

You must register the driver in your program before you use it.

Registering the driver is the process by which the Oracle driver’s class file is loaded into the memory, so it can be utilized as an implementation of the JDBC interfaces.

You need to do this registration only once in your program. You can register a driver in one of two ways.

Approach I — Class.forName()

1. The most common approach to register a driver is to use Java’s Class.forName() method, to dynamically load the driver’s class file into memory, which automatically registers it.
2. This method is preferable because it allows you to make the driver registration configurable and portable.

The following example uses Class.forName( ) to register the Oracle driver −

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}

You can use getInstance() method to work around noncompliant JVMs, but then you’ll have to code for two extra Exceptions as follows −

try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
catch(IllegalAccessException ex) {
System.out.println("Error: access problem while loading!");
System.exit(2);
catch(InstantiationException ex) {
System.out.println("Error: unable to instantiate driver!");
System.exit(3);
}

Approach II — DriverManager.registerDriver()

The second approach you can use to register a driver, is to use the static DriverManager.registerDriver() method.

You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

The following example uses registerDriver() to register the Oracle driver −

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);
}

Database URL Formulation

After you’ve loaded the driver, you can establish a connection using the DriverManager.getConnection() method. For easy reference, let me list the three overloaded DriverManager.getConnection() methods −

1. getConnection(String url)
2. getConnection(String url, Properties prop)
3. getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your database.

Formulating a database URL is where most of the problems associated with establishing a connection occurs.

Following table lists down the popular JDBC driver names and database URL.

Using Only a Database URL

A second form of the DriverManager.getConnection( ) method requires only a database URL −

DriverManager.getConnection(String url);

However, in this case, the database URL includes the username and password and has the following general form −

jdbc:oracle:driver:username/password@database

So, the above connection can be created as follows −

String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";
Connection conn = DriverManager.getConnection(URL);

Using a Database URL and a Properties Object

A third form of the DriverManager.getConnection( ) method requires a database URL and a Properties object −

DriverManager.getConnection(String url, Properties info);

A Properties object holds a set of keyword-value pairs. It is used to pass driver properties to the driver during a call to the getConnection() method.

To make the same connection made by the previous examples, use the following code −

import java.util.*;
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );
Connection conn = DriverManager.getConnection(URL, info);

Closing JDBC Connections

At the end of your JDBC program, it is required explicitly to close all the connections to the database to end each database session. However, if you forget, Java’s garbage collector will close the connection when it cleans up stale objects.

Relying on the garbage collection, especially in database programming, is a very poor programming practice. You should make a habit of always closing the connection with the close() method associated with the connection object.

To ensure that a connection is closed, you could provide a ‘finally’ block in your code. A finally block always executes, regardless of whether an exception occurs or not.

To close the above opened connection, you should call close() method as follows −

conn.close();

Explicitly closing a connection conserves DBMS resources, which will make your database administrator happy.

CRUD operations

C- CREATE
R- READ
U- UPDATE
D- DELETE

These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language.
Although the target database system is MySQL, the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems.

Creating JDBC Application

There are following six steps involved in building a JDBC application −

1. Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
2. Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.
3. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to the database.
4. Extract data from result set − Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.
5. Clean up the environment − Requires explicitly closing all database resources versus relying on the JVM’s garbage collection.

Hands On Example 1

To connect a Java application with the MySQL database, we need to follow 5 following steps.

In this example we are using MySQL as the database. So we need to know following information's for the MySQL database:

a) Driver class:

The driver class for the MySQL database is com.mysql.jdbc.Driver.

b) Connection URL:

The connection URL for the MySQL database is jdbc:mysql://localhost:3308/employee where jdbc is the API, MySQL is the database, localhost is the server name on which MySQL is running, we may also use IP address, 3308 is the port number and employee is the database name. We may use any database, in such case, we need to replace the employee with our database name.

c) Username:

The default username for the MySQL database is root.

d) Password:

It is the password given by the user at the time of installing the MySQL database. In this example, we are going to use root as the password.

Let’s first create a table in the MySQL database, but before creating a table, we need to create and connect a database first with JDBC.

Code:

package gd2;
import java.sql.*;
import javax.swing.JOptionPane;
public class GD2 {
public static void main(String[] args) {
Connection conn;
Statement stmt;
try{
//Load and register JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Establish connection with database
System.out.println("Trying to connect with Database...");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/", "root", "");
if(conn.isClosed()){
System.out.println("Connection is closed.");
}
else{
System.out.println("Connection Established Successfully");
}
stmt = conn.createStatement();
System.out.println("Trying to Create Database...");
String sql = "CREATE DATABASE employee";
stmt.executeUpdate(sql);
System.out.println("Database created successfully.");

//close connections
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}
catch(Exception se){
se.printStackTrace();
}
}
}

To connect java application with the mysql database, mysqlconnector.jar file is required to be loaded.

Example to Create a table with MySQL database

In this example, employee is the database name, root is the username and password both.

package gd2;
import java.sql.*;
public class table {
public static void main(String[] args){
Connection conn;
Statement stmt;
try{
//Load and register JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Establish connection with database
System.out.println("Trying to connect with Database...");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/employee", "root", "");
if(conn.isClosed()){
System.out.println("Connection is closed.");
}
else{
System.out.println("Connection Established Successfully");
}
System.out.println("Trying to create table...");
stmt = conn.createStatement();
String sql = "CREATE TABLE employee(employeeID INT NOT NULL auto_increment, empname varchar(30), empage varchar(30), empsallary varchar(30), PRIMARY KEY(employeeID))";
stmt.executeUpdate(sql);
//close connections
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}
catch(Exception se){
se.printStackTrace();
}
}
}

The above example is to create an employee table.

Inserting the values in the employee table

package gd2;
import java.sql.*;
import javax.swing.JOptionPane;
public class insert {
public static void main(String[] args){
Connection conn;
Statement stmt;
try{
//Load and register JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Establish connection with database
System.out.println("Trying to connect with Database...");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/employee", "root", "");
if(conn.isClosed()){
System.out.println("Connection is closed.");
}
else{
System.out.println("Connection Established Successfully");
}
System.out.println("Trying to Insert values into table...");
stmt = conn.createStatement();
String sql = "INSERT INTO employee(empname,empage,empsallary) VALUES('ved','25','50000')";
int i = stmt.executeUpdate(sql);
System.out.println(i);
if (i > 0){
System.out.println("Data inserted successfully.");
}
else{
System.out.println("Unable to insert data.");
}
//close connections
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}
catch(Exception se){
se.printStackTrace();
}
}
}

Displaying the values of the employee table.

System.out.println("Trying to display data");
stmt = conn.createStatement();
String sql = "SELECT * FROM employee";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("employeeID")+" : "+rs.getString("empname")+" , "+rs.getString("empage")+" , "+rs.getString("empsallary"));
}

Difference between execute, executeQuery and executeUpdate in JDBC.

Execute method can be used with any type of SQL statements and it returns a Boolean. A true indicates that the execute method returned a result set object which can be retrieved using getResultSet method. false indicates that the query returned an int value or void. execute method can run both select and insert/update statements.

executeQuery method execute statements that returns a result set by fetching some data from the database. It executes only select statements.

executeUpdate method execute sql statements that insert/update/delete data at the database. This method return int value representing number of records affected; Returns 0 if the query returns nothing. The method accepts only non-select statements.

JDBC Driver Types

There are four types of JDBC drivers:

1. JDBC-ODBC Bridge plus ODBC driver − also called Type 1 calls native code of the locally available ODBC driver.
2. Native-API, partly Java driver − also called Type 2 calls database vendor native library on a client side. This code then talks to the database over the network.
3. JDBC-Net, pure Java driver − also called Type 3 the pure-java driver that talks with the server-side middleware that then talks to the database.
4. Native-protocol, pure Java driver − also called Type 4 the pure-java driver that uses database native protocol.

Applications of JDBC

1. Aadhar card portal or center which is established in different cities.
2. Searching for a movie according to your desired date.
3. Railway reservation system.
4. Online shopping.
5. Employee Registration form.
6. Industrial application.

Conclusion

1. JDBC provides API or Protocol to interact with different databases.With the help of JDBC driver we can connect with different types of databases.
2. This technology is an API for the java programming language that defines how a client may access a database.
3. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases.
4. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the JVM host environment.

THANK YOU

Blog Created/ Contributed by:

Vedant Kolhe

Jay Mahamuni

Junaid Mujawar

Swaroom Muley

Satyam Muluk