JDBC DataSource Connection MySQL Example

In this article, we will show you how to create and use a DataSource object to get a connection to your data source without using JNDI services.

JAR dependencies

Download the MySQL JDBC driver from https://dev.mysql.com/downloads/connector/j/ and add to build path of your project.

Technologies used

  1. JDK - 1.8 or later
  2. MySQL - 5.7.12
  3. IDE - Eclipse Neon
  4. JDBC API - 4.2

JDBC DataSource + MySQL example

The following example demonstrates how to get a connection from MySQL DataSource. Also, we will create an employee table using Statement interface.
package com.javaguides.jdbc.statement.examples.packages;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

/**
 * JDBC MysqlDataSource Example
 * @author Ramesh Fadatare
 *
 */
public class DataSourceExample {
    private static final String createTableSQL = "create table employee (\r\n" + "  id  int(3) primary key,\r\n" +
        "  name varchar(20),\r\n" + "  email varchar(20),\r\n" + "  country varchar(20),\r\n" +
        "  password varchar(20)\r\n" + "  );";

    private static DataSource getMySQLDatasource() {
        MysqlDataSource dataSource = new MysqlDataSource();

        // Set dataSource Properties
        dataSource.setServerName("localhost");
        dataSource.setPortNumber(3306);
        dataSource.setDatabaseName("mysql_database");
        dataSource.setUser("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    public static void main(String[] argv) throws SQLException {
        DataSourceExample dataSourceExample = new DataSourceExample();
        dataSourceExample.createTable();
    }

    public void createTable() throws SQLException {

        System.out.println(createTableSQL);
        // Step 1: Establishing a Connection
        try (Connection connection = getMySQLDatasource().getConnection();
            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            statement.execute(createTableSQL);
        } catch (SQLException e) {

            // print SQL exception information
            printSQLException(e);
        }

        // Step 4: try-with-resource statement will auto close the connection.
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                // e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}
Output:
create table employee (
     id  int(3) primary key,
     name varchar(20),
     email varchar(20),
     country varchar(20),
     password varchar(20)
  );

Comments