JDBC with mySQL


The purpose of this Post is to get you beyond the initial hurdles involved in:

  • Use mySQL database for use with JDBC.
  • Writing and testing your first JDBC programs to administer the database and to manipulate the data stored in the MySQL database.

* What is JDBC:

JDBC technology is an API (included in both J2SE and J2EE) that provides cross-DBMS connectivity to a wide range of SQL databases and access to other tabular data sources, such as spreadsheets or flat files.

*  Connecting Java and mySQL

For Connecting Java and mySQL database you will need a Connector called “MySQL Connector/J” :

Download the Connector

What is MySQL Connector/J?

For those who don’t know, let me begin by explaining the purpose of MySQL Connector/J.

The JDBC API is designed to make it possible for you to write a single Java program and to use it to manipulate the data in a variety of different SQL database servers without a requirement to modify and/or recompile the program.  In order to do this, it is necessary for you to:

  • Inform the Java program as to the URL of the database server.  You can accomplish this with input data when you start the program.
  • Provide the Java program with a programming interface to the specific database server that you intend to use.  Assuming that the programming interface has been installed on your computer, you can also accomplish this with input data when you run the program.
Installing MySQL Connector/J :  First Extract the Connector/J downloaded archive.

It consists of many files. The file which is most imp for us is the jar file. May be named as:   “mysql-connector-java-5.1.12-bin.jar”

5.1.12 is the version, can be different for you. Depends on the version of Connector you download.

Now to install the Connector you will have 2 methods:

1. The following statement appears in the connector documentation Section 2.2.1. entitled Setting the CLASSPATH

copy the “com” and “org” subdirectories and all of their contents to anywhere you like, and put the directory holding the “com” and “org” subdirectories in your classpath

2. Second Method:

put mysql-connector-java-[version]-bin.jar in your classpath, either by adding the FULL path to it to your CLASSPATH environment variable, or by copying the .jar file to $JAVA_HOME/jre/lib/ext.

I will like to tell you the best method that worked for me and which is most uncomplicated is: Copy the downloaded Connector .jar file into /java/jre/lib/ext    folder. This easier rather typing commands or setting the classpath for different .jar files you need to run you program.

Note:  bfore running java programs on Windows Command prompt just do the following 2 steps and you can run the program from any directoy.

  • set path= “path of jdk/bin folder”    (without quotes) the path may be like(for Windows users)

C:\Program Files\Java\jdk1.5.0_14\bin

  • set classpath=%classpath%;.;

3. Lets check the connectivity using the Java Code:

Consider here the following things in the code:

  • database_name= The database you have made in mySQL you have to use
  • Database_password= Password of “root” in mySQL, you are using mySQL database as a ‘root’
  • The table you are using is “person” which has columns “name” and “age”
  • In the following code you take the name of person as command-line argument and use it to run the query
  • The command line argument is name of person you have in your database 🙂
  • Steps to run the following java code:
  • Save the following code and name the file as “example.java”
  • Run command on command prompt (Compile the code):    javac example.java
  • Execute the compiled File (Hope there are no Errors):  java example argument  (for example:    java example aniket      )


//Import Packages

import java.sql.*;
import java.lang.*;
public class example{
Connection con = null;
Statement smnt;
ResultSet rs;
String querystring;
public static void main(String[] args){
try{
final String DRIVER,DATABASE,USER,PASS;
DRIVER="com.mysql.jdbc.Driver";
DATABASE="jdbc:mysql:///database_name";
USER="root";
PASS="Database_password";

Class.forName(DRIVER).newInstance();
con = DriverManager.getConnection(DATABASE,USER,PASS);
if(!con.isClosed()){
System.out.println("MySQL server connected...");
smnt= con.createStatement();
querystring= "select age from person where name=\"" + args[0] + "\"";
rs = smnt.executeQuery(querystring);
if(rs.next()){
System.out.println("The age is" + rs.getString("age"));
}
rs.close();
con.close();
}
}//try Block Ends here
catch(Exception e) {System.err.println("Exception: " + e.getMessage());}
}//main ends here
}//class Ends

So the code is as above..
Here the post ends.. Hope this works for you..
While writing this post, I considered that you have Java(JDK) and mySQL configured on your computer.
Else this will not work for you.
Cheers… 🙂
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s