Follow the steps below to have a basic program that would connect to your MySQL DB:
1. First of all make sure that your MySQL installation is proper and the DB services are up and running fine. (We are assuming that you’ve setup your DB on localhost. This example, however, is not limited to this and would work fine as well for a networked DB connection.) You can do so by trying to connect to the DB via your MySQL workbench or via the command-line interface/ client provided by the MySQL installation. (our current installation is the MySQL server version 5.6)
2. The official JDBC driver for MySQL is called the Connector/J. Download (if you don’t already have it) the MySQL connector jar for these required drivers and connection management classes for My-SQL. For this particular example we are using the mysql-connector-java-5.1.24-bin.jar. download link for this is:http://www.mysql.com/downloads/connector/j/
3. The sample code for testing the connectivity from java code is below:
package com;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class MySqlTest {
public static void main(String[] args) {
System.out.println("MySQL Connect Example.");
Connection conn = null;
// this is the URL to your database server with port
String url = "jdbc:mysql://localhost:3306/";
// name of the database schema to connect to
// the “world” database used here is provided as
// an example with MySQL installation
String dbName = "world";
// class name of the Driver class
String driver = "com.mysql.jdbc.Driver";
// Database user name
String userName = "root";
// DB password for above user
String password = "pwd@root";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,userName,password);
System.out.println("Connected to the database");
// A sample String query.
String query = “SELECT COUNT(1) FROM CITY";
CallableStatement cs = conn.prepareCall(query);
ResultSet rs = cs.executeQuery();
rs.next();
int result = rs.getInt(1);
conn.close();
System.out.println("Disconnected from database " + result);
} catch (Exception e) {
e.printStackTrace();
}
}
}
As you can see, this is simple main class that can be compiled and executed as a simple Java application in Eclipse or compiled (javac -cp <classpath with mysql connector jar in it> <your .java file name>) and run (java -cp <classpath with mysql connector jar in it > <your class name>) through command line.
If all works well, you should be able to an output similar to below:
MySQL Connect Example.
Connected to the database
Disconnected from database 4079
The count obtained above may differ in your case. Any result, however, would ensure that you are now able to connect to the database via your Java code.
Connecting to IBM DB2:
To use the same code above for connecting to IBM DB2 database, simply change the following:
URL: “jdbc:db2://localhost:<port number>/”;
dbName: “<your database name>”;
driver: “com.ibm.db2.jcc.DB2Driver”;
and set the appropriate username and passwords.
Also, there should be three jars to be added to the classpath:
1. db2jcc.jar
2. db2jcc_javax.jar
3. db2jcc_license_cu.jar
This sample example would come in handy when we explain the procedure to integrate the same in “Spring framework”. Till then, happy coding J !!!