Java: Setting MySQL environment for JDBC

Current post is on how to set and test the the MySQL Environment for Java Database Connectivity (JDBC) on Windows for first time with example

Following post is split into six sections:

1. Overview of JDBC
2. Steps to install MySQL
3. Steps to install Connector/J
4. Sequence to start MySQL
5. Create MySQL User, Database & Table via mysql prompt
6. Sample Code to test the connectivity

1. Overview of JDBC:

JDBC (Java Database Connectivity) is a standard Java API for database-independent connectivity between the Java and a wide range of databases.

The JDBC library includes APIs for database usage:

• Making a connection to a database
• Creating SQL or MySQL statements
• Executing SQL or MySQL queries on the database
• Viewing & Modifying the result records

JDBC is a specification that provides a complete set of interfaces to allow portable access to an underlying database. 

Java can be used to write different types of executable's, such as:

• Java Applications
• Java Applets
• Java Servlets
• Java ServerPages (JSPs)
• Enterprise JavaBeans (EJBs)

2. MySQL Setup:

MySQL (My Structured Query Language) is widely used open-source relational database management system (RDBMS).

The default port of Mysql is 3306.

One can download MySQL from its official site here.

If you are on windows operating system, it is recommended to download complete installation

On windows operating system, you also need to add to PATH environment variable the complete MySQL installation path

3. Connector/J Setup:

Connector/J is official JDBC driver for MySQL.

It is used in code to redirect the SQL calls to MySQL database.

One can download Connector/J from its official site here.

If you are on windows operating system, it is recommended to unzip in convenient directory location like same as MySQL path.

On windows operating system, you also need to add to CLASSPATH environment variable the complete path.

4. Steps to run MySQL:

Go to MySQL installation path\ bin.

Double click on mysqld.exe - A MySQL daemon process

Start the command prompt and type 'mysql -u root'

5. Create MySQL Database, Table and User:

Create Database:

Create database [IF NOT EXISTS] databasename;

This will be sufficient for initial development. Detailed information on MySQL CREATE DATABASE syntax page

Example:

Create database IF NOT EXISTS MyDB;

This will create a database MyDB if it does not exists already on server

Create Table:

Create table [IF NOT EXISTS] tableename
{
      column1name     column1type;
      column2name     column2type;
}

This will be sufficient for initial development. Detailed information on MySQL CREATE TABLE syntax page

Example:

Create table IF NOT EXISTS students(
id int not null,
firstname varchar (128),
lastname varchar (128),
std int not null
)


This will create a table students with requested columns if it does not exists already on server

Create User with ALL Privilege:

GRANT ALL PRIVILEGES ON DBName.TableName To 'user'@'hostname' IDENTIFIED BY 'password';

GRANT - This is the command used to create users and grant rights to databases, tables, etc.

ALL PRIVILEGES - This tells it the user will have all standard privileges.

Note: This does not include the privilege to use the GRANT command however.

DBName.TableName - This instructions MySQL to apply these rights for the use onto the which database and tables inside it

Note: You can replace it with * for all tables or store routines

TO 'user'@'hostname' - 'user' is the of the user account you are creating.

Note: You must have the single quotes in there.

'hostname' tells MySQL what hosts the user can connect from. For same machine, use localhost

IDENTIFIED BY 'password' - It sets the password for that user.

Detailed information on MySQL GRANT syntax page

Example:

GRANT ALL PRIVILEGES ON MyDB.* To 'joe'@'localhost' IDENTIFIED BY 'mypwd';

Above statement will create a user 'joe' with password 'mypwd' on 'local machine' mysql server and grant all privileges of all the tables in 'MyDB' database.

6. JDBC Testing code:

/*
    Import the required packages
*/

import java.sql.*;

public class MyFirstDBExample
{
   /*
        JDBC driver name and database URL
        It will be different for different databases
    */
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
   static final String DB_URL = "jdbc:mysql://localhost/MyDB";

   /* 
        Database credentials of user we just created
    */
   static final String USER = "joe";
   static final String PASS = "mypwd";
  
   public static void main(String[] args)
   {
       Connection conn = null;
       Statement stmt = null;
       try
       {
            /*
                Register the driver
            */
           
            Class.forName(JDBC_DRIVER);
            System.out.println("Registered the driver successfully!!!");

            /*
                Open a connection
            */
           
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Connected to database successfully!!!"); 

            /*
                Create a Statement
            */
           
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
           
            String sql;
            sql = "SELECT id, first, last,std FROM students";
           
            System.out.println("Executing statement...");
            ResultSet rs = stmt.executeQuery(sql);

            /*
                Extract data from ResultSet
            */
           
            System.out.println("Extracting the Data...");
           
            while(rs.next())
            {        
                int id  = rs.getInt("id");
                String first = rs.getString("first");
                String last = rs.getString("last");
                int std = rs.getInt("std");

               
                System.out.print("ID: " + id);               
                System.out.print(", First Name: " + first);
                System.out.println(", Last Name: " + last);
                System.out.print(", Studies in Standard: " + std);
            }
           
            /*
                Close all the descriptors
            */
           
            System.out.println("Closing the Descriptors...");
           
            rs.close();
            stmt.close();
            conn.close();
           
            System.out.println("Example executed successfully");
       }
       catch(SQLException se)
       {
            /*
                Handle errors for JDBC
            */
           
            se.printStackTrace();
       }
       catch(Exception e)
       {
            /*
                Handle errors for Class.forName
            */
            e.printStackTrace();
       }
       finally
       {
          try
          {
             if(stmt!=null)
                stmt.close();
          }
          catch(SQLException se1)
          {
          }
         
          try
          {
             if(conn!=null)
                conn.close();
          }
          catch(SQLException se2)
          {
             se.printStackTrace();
          }
       }
  
        System.out.println("Have a Nice Day!!!");
    }   
}


Hope this post saved your time.

Appreciate your feedback via comments.

Thanks.
Mehul