Java Database Connectivity (JDBC) with MySQL

JDBC -Java Database Connectivity 

As the name suggests  It is an  API that enables us to connect to our databases using java. Using this we can connect, add, update, delete, select -tables,columns,rows,databases i.e. In short we can do everything, we want to do with our databases using java.

Now, I won't much talk about the theory, I will help you to understand - how you can apply it. But don't worry I assure you that everything I  have shown here will give you complete understanding about JDBC and after reading this blog you will be able to implement it in any of your projects.

So let's begin this.

Get the driver for MySQL :

To start with MySQL  you will first need a jdbc driver for MySQL, it is called as 'MySQL connector ' .
You can find it on this link:  MySQL JDBC Connector
  • Choose the connector of your MySQL version.
  • And choose OS  'platform-independent'.
During the MySQL installation, it can be installed together but I prefer to use it separately.

So you will download a zip file (for windows) then unzip it and when you will see inside the folder, there is a jar file(MySQL-connector-java-8.0.20.jar for 8.0.20 version) so all you need is that jar file. Just copy and paste that jar file in the folder where you have your other jars of the project or you can put it anywhere you want, but keep in mind while executing your program make sure that you have added that jar file to your classpath.

So now we have a driver and we are all set to begin our first program.
Prerequisite :
  • Java installed on your PC.
  • MySQL community version installed on your PC.
  • The driver which we have downloaded.
Let's begin,
Let's see the steps to write JDBC code.

1)Load the driver

First, We will load the driver for this we will write this line:

Class.forName("com.mysql.jdbc.Driver");

It is fixed, you have to load it first, everywhere you want to write JDBC code of MySQL.
String in a double quote will be different for different database drivers. Above one is for MySQL.

2)Connect to the database

To get the connection to the database you will have to write this: 

Connection c=DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","username","password");  

By this line, you will get a reference of connection to the database.
Just mug up it and keep these points in mind.
  • localhost: host on which your MySQL server is running if it's different then specify the IP of that system.
  • 3306: Port number on which MySQL server is running. If it's different then specify your MySQL server's port number.
  • /myDB : The database in my MySQL server to which we want to connect. Specify your database name here.
  • "username": Specify username of your database.
  • "password": Specify password of your database.

We have done fixed things. These two lines of code will be written everywhere we are using JDBC. Now let's write some code examples.

In Example 1 and 2 we will learn different ways to fire DATA MANIPULATION LANGUAGE commands using java.
e.g. insert, update, delete.  

EXAMPLE-1 

import java.sql.*;
class Example1
{
    public static void main(String gg[])
    { 
        try
        {
            Class.forName("com.mysql.jdbc.Driver");  
            Connection c=DriverManager.getConnection("jdbc:mysql://localhost:3306/storedb","root","root");  
            
            //We have been now connected to the database storedb.
            
            //To fire the insert command we will create the reference of Statement. 
            Statement s=c.createStatement();
            //Passed the sql command to the executeUpdate() method of Statement class.
            //This will execute our statement.
            // We are inserting new data in a table of storedb database .
            s.executeUpdate("insert into employee (id,name,salary) values (1,'ABC',100000000)");
            //close the statement.
            s.close();

            //When we want to again fire the sql command we will have to get the reference of statement.
            s=c.createStatement();
            int id=2;
            String name="PQR";
            int salary=100000000;
            //here we are creating a string of sql command and we will pass this to the executeUpdate() method.
            String sql="insert into employee (id,name,salary) values ("+id+",'"+name+"',"+salary+")";
            System.out.printf("The following SQL Statement will be fired\n[%s]\n",sql);
            //Passed the sql string
            s.executeUpdate(sql);
            s.close();
            //closed the connection with storedb database.
            c.close();
            System.out.println("Record inserted");
        }catch(SQLException sqlException)
        {
            System.out.println(sqlException);
        } catch(ClassNotFoundException cnfe)
        {
            System.out.println(cnfe);
        }
    }
}


In Example-1 we have used the executeUpdate() method of Statement class. 
We will always use this method when we want to fire SQL statements which makes any change to the table data of the connected databases.
In the executeUpdate method, we can use various insert, delete, update etc commands.
To create a sql statement with dynamic data which comes from other source and stored in a variable we had to create a string with the concatenation of variable values.
In the next example we will see that without concatenation also we can make a dynamic sql statement. 


EXAMPLE -2 

import java.sql.*;
class Example2
{
    public static void main(String data[])
    { 
        try
        {
            int id=3;
            String name="RAM";
            int salary=100000000;
            //Load the driver
            Class.forName("com.mysql.jdbc.Driver");  
            //get connection to the database
            Connection c=DriverManager.getConnection("jdbc:mysql://localhost:3306/storedb","root","password");  

            //Here instead of getting reference of statement, we will prepare the statement.
            //We took reference of PereparedStatement.
            //Passed the sql statement to prepareStatement() method of Connection class.
            //We have added the question mark where we want to get data dynamically in the sql statement.
            PreparedStatement ps=c.prepareStatement("insert into employee (id,name,salary) values(?,?,?)");
            
            //Below setInt method will set the value of id at the place of 1st question mark.
            //We have passed it two arguments 
            //FIRST-Count of question mark starting from one
            //SECOND- the value we want to insert at that question mark
            ps.setInt(1,id);
            //Similarly To setString we passed 2 and name means at second question mark put the value of name.
            ps.setString(2,name);
            ps.setInt(3,salary);

            //We called executeUpdate of preparedStatement class to execute sql statement here we willnot pass string
            //because we have already done it in prepareStatement.
            ps.executeUpdate();
            ps.close();
            c.close();
            System.out.println("Record inserted");
        }catch(SQLException sqlException)
        {
            System.out.println(sqlException);
        } catch(ClassNotFoundException cnfe)
        {
            System.out.println(cnfe);
        }
    }
}

In the above Example-2  we have used PreparedStatement class. It is better than the first example where we had to parse the string.
For prepareStaement method where the data will dynamically arrive we will place the question mark and there are methods like setInt(), setString() etc in  the PreparedStatement class which enables us to put data in SQL statement. 
If the data type of a column of the table is int then we will use setInt() method.
If the data type column is char array, varchar or any string kind then we will use setString method to put data at that question mark location.
To find more information about the methods click here.

Till now we know how to fire DML commands using JDBC.
Yess, We can manipulate table data using java.



Wait, Let's do one more example to understand how we can fire DQL (DATA QUERY LANGUAGE) command.

EXAMPLE-3 

import java.sql.*;
class Example4
{
    public static void main(String gg[])
    { 
        try
        {
            //Load the driver
            Class.forName("com.mysql.jdbc.Driver");  
            //Get connection to database
            Connection c=DriverManager.getConnection("jdbc:mysql://localhost:3306/storedb","root","password");  
            //We can use the preparedStatement also instead of this.
            Statement s=c.createStatement();
            //Declared ResultSet reference variable
            ResultSet r;
            int id;
            String name;
            int salary;
            
            //For DQL command we will call the function executeQuery 
            //it will return the reference of ResultSet object
            //the ResultSet will contain the data fetched from the 'select' query.
            r=s.executeQuery("select * from employee");
            //Iterate on resultSet to get the fetched rows.
            //the next method will move on  the rows that have been fetched and return true/false
            while(r.next())
            { 
                //Using getInt, getString etc method we can get the values of different column of the selected row.
                id=r.getInt("id");
                name=r.getString("name").trim();
                salary=r.getInt("salary");
                System.out.printf("id : %10d, name : %-30s, salary : %20s \n",id,name,salary);
                //Now we have got the information of columns at one row
                //The next() method will internally move to the next row and provide the data of that row.
            } 
            r.close();
            s.close();
            c.close();
        }catch(SQLException sqlException)
        {
            System.out.println(sqlException);
        } 
        catch(ClassNotFoundException cnfe)
        {
            System.out.println(cnfe);
        }
    }
}

In the Example-3 we have used the method executeQuery() and passed our sql query to fetch the selected rows of the table in ResultSet object.
Now we will iterate and get the data on each row.
So by this method, we have done or job to show data in your project.

There are still many things you should know but let it be for later blogs. For now, you have pretty good knowledge to get started.

Thanks for reading 
Happy coding😊😊


Comments

Post a Comment

Popular posts from this blog

PROJECT 1 DerbyDB Creator -SQL scripting tool for Apache Derby

Image
'DerbyDB Creator' is a SQL scripting tool with Graphical User Interface for Apache derby databases.  Apache derby is an RDBMS (Relational Database Management System)  developed by the  Apache Software Foundation  that can be embedded in  Java  programs.   The core of the technology, Derby's database engine, is a full-functioned relationally embedded database-engine, supporting JDBC  and SQL  as programming APIs. Before we move further Have a look at this video which shows the functioning of DerbyDB Creator 1) What is DerbyDB Creator? DerbyDB Creator as a Graphical User Interface to access databases of apache derby and modify the databases by providing various SQL commands for the apache derby database. It is a replacement for 'ij' command-line tool of Apache org. This application has been created using JDBC(Java Database Connectivity) and the interface is cr...
Read more

PROJECT-2 Picture Modifier

Image
Picture Modifier Picture Modifier is a desktop application created using java. It is an image editor with various features to edit an image. This application has been made using inbuilt libraries of java and its user interface has been made using java swing and Java AWT. Before we move further here is the video which shows how this application works. Features of this Application: It can modify RGB (Red, blue, green) components of an image by sliding the bar of the intensity of each component. It can modify the contrast and brightness of the image. Using this we can zoom in and zoom out to resize the image and we can rotate an image by 360 degrees. There are various filters available for the image like Grayscale, negative image, pencil art, dot portrait, etc. How to install this application: Go to this GitHub repository:  Picture Editor - gitHub   Download the zip file of this and extract it to your desired folder. In the classes folder, there is a file named opencv_java420.ra...
Read more

Java Data Base Connectivity (JDBC) with MongoDB

Lets first start with some basics of MongoDB. What is MongoDB? MongoDB is document-oriented NoSQL database. This is a collection of documents where data is stored in the form documents, unlike old SQL systems where data is stored in the form of tables. Here each document consists of the key-value pairs which are the building block of the NoSQL database. The data is stored in documents in the key-value pairs.  example: { "first_name":"Ambarish", "last_name" :"Dashora" } So basically the data is stored in the JSON format called documents in MongoDB. Now we are here to connect, create, access, and modify collections and documents of MongoDB using java. So let's get started. First of all, we will learn how to set up a MongoDB client driver for java. I am assuming that you have installed MongoDB on your system. If not then download and install it from this link:   MongoDB . Get M...
Read more