Thursday, March 19, 2009

JAVA connection with MySQL using JDBC in Eclipse

We will discuss JAVA-MySQL connection using JDBC (Java Database Connectivity)


JDBC is a standard API that allows JAVA programs to access databases. The JDBC API is a collection of interfaces and classes written in the JAVA Programming Language. We can write applications that connect to databases, send SQL queries, and get the results using these standard interfaces and classes.

A JDBC driver implements these interfaces and classes for a particular Database Management System. A Java Code loads the specified driver for a particular DBMS before it establish a connection with a database.

Download JDBC Driver:

First of all you have to download JDBC driver (usually named mysql-connector-java-version).
For Example:

Extract downloaded zip file and copy extracted folder to your plugins folder in Eclipse Directory.
For Example: C:\Eclipse\plugins\mysql-connector-java-version

Import JDBC Driver in Eclipse.

1. Right Click on your Project in Eclipse -> Select Properties.
2. Select Java Build Path->selcect Libraries tab->press Add External JARs button.
3. Select mysql-connector-java-version-bin file from downloaded mysql-connector-java-version folder.

Done. Now you are ready to establish JAVA-MySQL connection.

JAVA Code Example:

For Example we have a database named “riksof” with a table employees(id,name,email,designation) where “id” is primary key and auto_increment.

/*Define a connection url*/
String host = "localhost";
String db = "riksof";
String tb = "employees";
String db_user = "dbuser";
String db_pw = "dbpassword";

String db_url = "jdbc:mysql://"+host+":3306/"+db+"?user="+db_user+"&password="+db_pw;

Connection conn = null;
Statement stmt = null;
String query = "";
ResultSet result = null;
PreparedStatement ps = null;

/* Load driver and Establish a connection */
conn = DriverManager.getConnection (db_url , db_user , db_pw);
stmt = conn.createStatement();
}catch(Exception connectionExp){
System.out.println("SQL Connection not Established: " + connectionExp.getMessage());

}catch(Exception driverExp){
System.out.println("Driver not Found : " + driverExp.getMessage());

/* Now Display all records */
query = "SELECT * FROM " + tb;
result = stmt.executeQuery(query);

catch(Exception sqlExp){
System.out.println("SQL Exception: " + sqlExp);

/*Insert a single record*/
query = "INSERT INTO employees(name,email,designation) VALUES('Name','Email','Designation')";

System.out.println("Record Stored.");
catch (SQLException insertExp) {
System.out.println("SQLException Caught: " + insertExp.getMessage());

No comments: