MySQL Database Connection With Examples

---

Introduction

---

MySQL is a popular open source database which can be used free of charge. MySQL is a relational database. Web applications can use this database to store persistence data.

This post explains how to set up Java applications to communicate with the MySQL Database. In order to write first JDBC program to connect MySQL Database Server we need some software already installed and running in your system.

For this tutorial, we need to have several libraries installed. We need to install mysql-server and mysql-client packages. The first package has the MySQL server and the second one contains, among others, the mysql monitor tool. We need to install the JDK, Java Development Kit, for compiling and running Java programs. Finally, we need the MySQL Connector/J driver.

To connect to MySQL from Java, you have to use the JDBC driver from MySQL. The MySQL JDBC driver is called MySQL Connector/J. You find the latest MySQL JDBC driver under the following URL: http://dev.mysql.com/downloads/connector/j.

Pictorial Representation of Java Connection to MySQL Server:

---

Java JDBC connection example

---

Code snippets to use a JDBC driver to connect a MySQL database.

Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
conn.close();

Sample Examples1:

package com.knowledgewala.database.mysql;

import java.sql.DriverManager;
 import java.sql.Connection;
 import java.sql.SQLException;
 import java.util.logging.Logger;

/**
  * This class helps to connect MySql Database Server.
  * @author dknitk
  *
  */
 public class KWMySqlJDBC1 {

    //Instantiate Logger Object
     private static final Logger LOGGER = Logger.getLogger(KWMySqlJDBC1.class
             .getName());

    /**
      * This helps to start execution of the program.
      * 
      * @param argv
      */
     public static void main(String[] argv) {

        LOGGER.info("-------- MySQL JDBC Connection Sample Example ------------");

        try {
             //Load Drivers
             Class.forName("com.mysql.jdbc.Driver");
         } catch (ClassNotFoundException e) {
             LOGGER.info("Where is your MySQL JDBC Driver?");
             e.printStackTrace();
             return;
         }

        LOGGER.info("MySQL JDBC Driver Registered!");
         Connection connection = null;

        try {
             //Call method to establish database connection
             connection = DriverManager
                     .getConnection("jdbc:mysql://localhost:3306/mkyongcom",
                             "root", "password");

        } catch (SQLException e) {
             LOGGER.info("Connection Failed! Check output console");
             e.printStackTrace();
             return;
         }

        if (connection != null) {
             LOGGER.info("You made it, take control your database now!");
         } else {
             LOGGER.info("Failed to make connection!");
         }
     }
 }

Sample Example2:

 

package com.knowledgewala.database.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Logger;

/**
 * This class helps to connect program to MySql Database Server.
 * 
 * @author dknitk
 *
 */
public class KWMySqlJDBC2 {

    // Instantiate Logger Object
    private static final Logger LOGGER = Logger.getLogger(KWMySqlJDBC1.class
            .getName());
    // Initialize database constants
    private static final String DATABASE_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/database_name";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    private static final String MAX_POOL = "250";

    // init connection object
    private Connection connection;
    // init properties object
    private Properties properties;

    // create properties
    private Properties getProperties() {
        if (properties == null) {
            properties = new Properties();
            properties.setProperty("user", USERNAME);
            properties.setProperty("password", PASSWORD);
            properties.setProperty("MaxPooledStatements", MAX_POOL);
        }
        return properties;
    }

    // connect database
    public Connection connect() {
        if (connection == null) {
            try {
                Class.forName(DATABASE_DRIVER);
                connection = DriverManager.getConnection(DATABASE_URL,
                        getProperties());
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    // disconnect database
    public void disconnect() {
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Sample Example with Query Execution:

package com.knowledgewala.database.mysql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;
/**
 * This class helps to connect MySql Database Server and execute query. 
 * @author dknitk
 *
 */
public class KWMySqlJDBC3
{
    // Instantiate Logger Object
        private static final Logger LOGGER = Logger.getLogger(KWMySqlJDBC1.class
                .getName());
  /* static block is executed when a class is loaded into memory 
   * this block loads MySQL's JDBC driver
   */
  static
  {
    try
    {
      // loads com.mysql.jdbc.Driver into memory
      Class.forName("com.mysql.jdbc.Driver");
    } 
    catch (ClassNotFoundException cnf) 
    {
        LOGGER.info("Driver could not be loaded: " + cnf);
    }
  }
 
  public static void main(String[] args)
  {
    String connectionUrl = "jdbc:mysql://localhost:3306/EXPDB";
    String dbUser = "root";
    String dbPwd = "mysql";
    Connection conn;
    ResultSet rs;
    String queryString = "SELECT ID, NAME FROM EXPTABLE";
 
    try
    {
      conn = DriverManager.getConnection(connectionUrl, dbUser, dbPwd);
      Statement stmt = conn.createStatement();
 
      // INSERT A RECORD
      stmt.executeUpdate("INSERT INTO EXPTABLE (NAME) VALUES (\"TINU K\")");
 
      // SELECT ALL RECORDS FROM EXPTABLE
      rs = stmt.executeQuery(queryString);
 
      LOGGER.info("ID \tNAME");
      LOGGER.info("============");
      while(rs.next())
      {
        LOGGER.info(rs.getInt("id") + ".\t" + rs.getString("name"));
        LOGGER.info("\n");
      }
      if (conn != null)
      {
        conn.close();
        conn = null;
      }
    }
    catch (SQLException sqle) 
    {
        LOGGER.info("SQL Exception thrown: " + sqle);
    }
  }
}

In order to execute above programs you have to include MySQL JDBC driver JAR in Java’s classpath. We used mysql-connector-java-5.1.13-bin.jar JDBC driver, it maybe a different version in your case. You can execute programs using either eclipse or command prompt.