Blog Archive

Java connect to MySQL database example using JDBC

In this tutorial, you will learn how to connect the MySQL database with the Java. First we create an instance of the Java MySQL driver and then we use our authentication credentials to establish a connection to the MySQL database. After establishing a connection we can access or retrieve data form MySQL database.

Download the MySQL Connector/J is the official JDBC driver for MySQL.

Here is the link ... http://dev.mysql.com/downloads/connector/j/

Create the connection properties file MySqlConnection.properties

//MySql Server URL 
MySQLServerURL=jdbc:mysql://localhost:3306
//MySql Database Name
MySQLdatabaseName=world
//MySql User Id
userId=root
//MySql Password
password=mysql

Java program to connect to the MySQL database GetMySQLData.java

package com.as400samplecode;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;

public class GetMySQLData {

    static Properties props;

    public static void main(String[] args) {

        String propertiesFileName = "";
        String dbFileName = "";
        GetMySQLData getMySQLData = new GetMySQLData();

        if (args.length < 2)
        {
            System.err.println("Usage: java "+ getMySQLData.getClass().getName()+
            " Properties_File_Name Db_Filename");
            System.exit(1);
        }

        propertiesFileName = args[0].trim();
        dbFileName = args[1].trim();
        getMySQLData.getFileInfo(propertiesFileName, dbFileName);     

    }

    private void getFileInfo(String propertiesFileName, String dbFileName){

        //declare the JDBC objects
        Connection SqlConn = null;
        PreparedStatement SqlStmt = null;
        ResultSet SqlRs = null;

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/" + propertiesFileName.trim()));

            // SQL Server connection string.
            String connectionUrl = 
                props.getProperty("MySQLServerURL").trim() + "/" +
                props.getProperty("MySQLdatabaseName").trim();

            // Establish the connection to MySQL Server.
            Class.forName("com.mysql.jdbc.Driver").newInstance();;
            SqlConn = DriverManager.getConnection(connectionUrl,props.getProperty("userId").trim(),props.getProperty("password").trim());

            // Create and execute an SQL statement that returns data from SQL Server
            String sql = "SELECT * from " + dbFileName;
            SqlStmt = SqlConn.prepareStatement(sql);
            SqlRs = SqlStmt.executeQuery();

            // Get MetaData for SQL table
            ResultSetMetaData metaData = SqlRs.getMetaData();
            int colCount = metaData.getColumnCount();

            // Iterate through the MySql server data in the result set
            while (SqlRs.next()) {
                System.out.println("Start New Row ------>");
                for (int i = 0; i < colCount; i++) {
                    System.out.println(metaData.getColumnName(i + 1) + ": " + SqlRs.getString(i+1));
                    System.out.println(metaData.getColumnTypeName(i + 1));
                }    
            }
        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (SqlRs != null) try { SqlRs.close(); } catch(Exception e) {}
            if (SqlStmt != null) try { SqlStmt.close(); } catch(Exception e) {}
            if (SqlConn != null) try { SqlConn.close(); } catch(Exception e) {}
        } 
    }

}

1 comment :