Java – JDBC Connection Example (MySQL)

It’s very strange if you are still using JDBC in your project for database access because there are lot’s of powerful alternatives like hibernate and iBatis. But it is important to learn basics and it requires learning JDBC first.

JDBC-Icon

In this post, I am giving an example of making a connection with database using MySQL Driver. Read more about types of JDBC drivers.

Handling a connection requires following steps:

1) Load the driver
2) Open database connection
3) Close database connection

Let’s follow above steps in code:

1) Load JDBC driver

The easiest way to do this is to use Class.forName() on the class that implements the java.sql.Driver interface. With MySQL Connector/J, the name of this class is com.mysql.jdbc.Driver. With this method, you could use an external configuration file to supply the driver class name and driver parameters to use when connecting to a database.

	Class.forName("com.mysql.jdbc.Driver");
As part of its initialization, the DriverManager class will attempt to load the driver classes referenced in the “jdbc.drivers” system property. This allows a user to customize the JDBC Drivers used by their applications.

2) Open database connection

After the driver has been registered with the DriverManager, you can obtain a Connection instance that is connected to a particular database by calling DriverManager.getConnection():

	Connection connection = DriverManager
		.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");

Once a Connection is established, it can be used to create Statement and PreparedStatement objects, as well as retrieve metadata about the database.

3) Close database connection

This step is as much important as opening a connection. Any connection left open is waste of resource and lead to various exceptions.

try 
{
	if(connection != null)
		connection.close();
	System.out.println("Connection closed !!");
} catch (SQLException e) {
	e.printStackTrace();
}

Complete JDBC Connection Example

Let’s see the whole thing working in an example below:

package com.howtodoinjava.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionDemo {
	public static void main(String[] argv) {

		System.out.println("-------- MySQL JDBC Connection Demo ------------");
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} 
		catch (ClassNotFoundException e) {
			System.out.println("MySQL JDBC Driver not found !!");
			return;
		}
		System.out.println("MySQL JDBC Driver Registered!");
		Connection connection = null;
		try {
			connection = DriverManager
				.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");
			System.out.println("SQL Connection to database established!");

		} catch (SQLException e) {
			System.out.println("Connection Failed! Check output console");
			return;
		} finally {
			try 
			{
				if(connection != null)
					connection.close();
				System.out.println("Connection closed !!");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Output:

-------- MySQL JDBC Connection Demo ------------
MySQL JDBC Driver Registered!
SQL Connection to database established!
Connection closed !!

That’s all for this topic. Drop a comment if something needs more explanation.

Happy Learning !!

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

9 thoughts on “Java – JDBC Connection Example (MySQL)”

  1. The code would gain a little if you use try-with-resource:

    try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password")) {
        System.out.println("SQL Connection to database established!");
        // do some real stuff
    }
    

    It also gain a bit more if you fetch database connection uri and credentials from a properties file.

    This way, problems are handled and you don’t need to clean anything up, as the JVM will do this.

  2. hello i am writing a similar code to connect to jdbc but i am getting the following exception:
    Exception in thread “main” java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter

    Here is my code

    package project;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class DB 
    {
    	private static final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    	private static final String jdbcURL = "jdbc:sqlserver://localhost:1433;instanceName=MYSERVER;
    						databasename=ExamsDB;integratedSecurity=true;";
    	public static void main(String[] args) 
    	{
    	   // System.setProperty("javax.xml.bind.JAXBContextFactory", "com.sun.xml.bind.v2.ContextFactory");
    		System.out.println("Program started");
    
    	    try
    	    {
    	     	Class.forName(jdbcDriver);
    	       	System.out.println("JDBC driver loaded");
    	    }
    	    catch (Exception err)
    	    {
    	       System.err.println("Error loading JDBC driver");
    	       err.printStackTrace(System.err);
    	       System.exit(0);
    	    }
    	    
    	    
    	  	Connection databaseConnection= null;
    	    try
    	    {
    	      //Connect to the database
    	      databaseConnection = DriverManager.getConnection(jdbcURL);
    	      System.out.println("Connected to the database");
    	    
    	      //declare the statement object
    	      Statement sqlStatement = databaseConnection.createStatement();
    
    	      //declare the result set    
    	      ResultSet rs = null;
    	  
    	      //Build the query string, making sure to use column aliases
    	      String queryString="select * from dbo.users";
    	      
    
    	      //print the query string to the screen
    	      System.out.println("\nQuery string:");
    	      System.out.println(queryString);
    	      
    	      //execute the query
    	      rs=sqlStatement.executeQuery(queryString);
    	      
    	      //print a header row
    	      System.out.println("\nParentOrganizationName\t| \tOrganizationName\t|\tCurrencyName");
    	      System.out.println("----------------------\t|\t ----------------\t|\t------------");
    	      
    	      //loop through the result set and call method to print the result set row
    	      while (rs.next())
    	      {
    	        printResultSetRow(rs);
    	      }    
    	      
    	      //close the result set
    	      rs.close();
    	      System.out.println("Closing database connection");
    
    	      //close the database connection
    	      databaseConnection.close();
    	    }
    	    catch (SQLException err)
    	    {
    	       System.err.println("Error connecting to the database");
    	       err.printStackTrace(System.err);
    	       System.exit(0);
    	    }
    	    System.out.println("Program finished");
    	}
    	public static void printResultSetRow(ResultSet rs) throws SQLException
    	{
    		//Use the column name alias as specified in the above query
    		String OrganizationName= rs.getString("OrganizationName");
    		String ParentOrganizationName= rs.getString("ParentOrganizationName");
    		String CurrencyName= rs.getString("CurrencyName");
    		System.out.println(ParentOrganizationName+"\t|\t"+ OrganizationName + "\t|\t" + CurrencyName);  
    	}
    }
     
  3. Thanks for the tutorial. Even though it is a very basic thing but a MUST know for all using Advanced frameworks like Hibernate, iBatis etc.

Comments are closed.

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.