This example shows how to connect to, and query, a MySQL database in Java using JDBC.

The example will attempt to implement the singleton design pattern. The singleton design pattern restricts the instantiation of a class to one object.

First get a copy of the MySQL JDBC driver at: http://www.mysql.com/downloads/connector/j/

Extract the driver anywhere, and add the JDBC jar file to your classpath. For example, if you extract to c:\dev and are using version mysql-connector-java-5.1.15, set your classpath to c:\mysql-connector-java-5.1.15\mysql-connector-java-5.1.15-bin.jar.

If you are using Eclipse, you may need to add the jar to your project by right clicking your project folder and selecting: Build Path - External Buildpath.

This example will use the 'world.sql' sample database provided by http:dev.myql.com/doc

TestDB.java - This class testing our singleton implementation of JDBC by getting a connection, making a simple query, and then iterating through the results. We then get another connection and do a simple test to verify that our singleton is function properly by only allowing access to one instance. 

import java.sql.*;
public class TestDB {     
    public static void main(String args[]) 
        throws Exception{      		
    	//Always returns the same instance
		Connection connection = DBConnect.getConnection();			
		ResultSet rows = null;					
		Statement s = connection.createStatement();
		String select = "select * from city limit 20";			
		rows = s.executeQuery(select);		
		while(rows.next()){
			System.out.println(rows.getString("Name"));			
		}
		//Get connection again, still same instance
		Connection connection2 = DBConnect.getConnection();
		if(connection2 == connection){
			System.out.println("Same Instance");
		}		
    }    
}

DBConnect.java - This class has a static member that holds our database connection. When the static getConnection method is called, we test to see if the connection has been instantiated yet, and if it has not, we create it using a private method. Either way, the connection returned is always the same instance. 

import java.sql.*;
public class DBConnect{	
	//Static instance of connection, only one will ever exist
	private static Connection connection = null;		
	//Returns single instance of connection
	public static Connection getConnection(){		
		//If instance has not been created yet, create it
		if(DBConnect.connection == null){
			initConnection();
		}
		return DBConnect.connection;
	}	
	//Gets JDBC connection instance
	private static void initConnection(){			
		try{		
			Class.forName("com.mysql.jdbc.Driver");		
			
			String url = "jdbc:mysql://localhost/world";
			String user = "root";
			String pw = "root";
			DBConnect.connection =
                         DriverManager.getConnection(url, user, pw);		
		}
		catch (ClassNotFoundException e){		
			System.out.println(e.getMessage());
			System.exit(0);
		}
		catch (SQLException e){			
			System.out.println(e.getMessage());
			System.exit(0);
		}
		catch (Exception e){		
		}		
	}
}