Java Database Connectivity with MySQL

Java Database Connectivity with MySQL

This tutorial is all about learning the Java Database Connectivity with MySQL or in other words, the procedure to connect java application with the MySQL database. In order to connect the Java application with the MySQL database there is a need to follow the below mentioned 5 steps that are designed to get the maximum efficiency. In this tutorial there is a use of an example for explaining the concept and here, MySQL database has been used as the database. Therefore, there is a need to know the following mentioned information for the MySQL database:

• Driver class: The driver class that is needed for the MySQL database is com.mysql.jdbc.Driver.
• Connection URL: The connection URL that is generally used for the MySQL database is jdbc:mysql://localhost:3306/phptpoint where jdbc is known as the API, MySQL is the main database, localhost is generally the server name that is responsible for running the MySQL, The user can use IP address, 3306 is the port number and phptpoint is the database name. The programmers can use any database just they have to replace the database name from phptpoint to the name of their database.
• Username: The default username that is generally used for the MySQL database is ‘root’.
• Password: This is basically the password that is given by the user at the time of installing the MySQL database. In this example the password that is going to be used is root.

Create a Database

There is need to create a table in the MySQL database in order to establish the connection. But before creating the table the first priority is to create a database that is necessary. Here is the SQL query that is generally used to create a database that is depicted below:

create database phptpoint;  
use phptpoint;  
create table student(id int(10),name varchar(40),age int(3));  

Example to Connect Java Application with mysql database

Here is an example that is depicting the process to connecting the Java application with MySQL database. In this very example, the name of the database is phptpoint and root is both the username and password both. Observe this example to get an understanding of the process:

import java.sql.*;

class MysqlConnection
{
  
public static void main (String args[])
  {
    
try
    {
      
Class.forName ("com.mysql.jdbc.Driver");
      
Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/phptpoint","root", "root");
      
//here sonoo is database name, root is username and password  
	Statement stmt = con.createStatement ();
      
ResultSet rs = stmt.executeQuery ("select * from student");
      
while (rs.next ())
	
System.out.println (rs.getInt (1) + "  " + rs.getString (2) + "  " + rs.getString (3));
      
con.close ();
    
}
    catch (Exception e)
    {
      System.out.println (e);
    }
  
}

}

There is another requirement that is on the list that is basically to connect to the java application with the MySQL database mysqlconnector.jar file is required to be loaded.

Two ways to load the jar file:

There are generally two methods that are used in order to load the jar file that are depicted below:

• paste the mysqlconnector.jar file in jre/lib/ext folder
• set classpath

1. Paste the mysqlconnector.jar file in JRE/lib/ext folder

The programmer have to download the mysqlconnector.jar file from the web and then paste this file in the jre/lib/ext folder.

2. set classpath:

There are basically two ways that are used to set the classpath that are depicted below:

• Temporary
• Permanent

1. Setting the temporary classpath

The programmer or the user have to open the command prompt and write the following command in it that is mentioned below:

C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;  

2. Setting the permanent classpath

Now the other way is to set the permanent classpath and the procedure is the programmer have to go to the environment variable and then click on the new tab. In the variable name, the user have to write classpath and in the variable value the user have to paste the path to mysqlconnector.jar file just by appending mysqlconnector.jar;.; as C:\folder\mysql-connector-java-5.0.8-bin.jar;.; this very path.