Thursday, June 25, 2009

Connecting Java to MySql database

This is the first time im trying to connect MySql from Java.I knew very little about MySql.Previously i have used Oracle,MS Access and Sql Server.Following are the things i have done .

1. First i have created a database with a name 'sample'

mysql>create database sample;
mysql>use sample;

2.create a table 'first' with the following fields.

mysql>create table first(id int,name varchar(20));
enter some values, in my case
>insert into first values(1,'Lara');
>insert into first values(2,'Sachin');

Now your database/table is ready.
Im in 'root' user and my password is 'password'.
Before start writing Java code, first get the MySql Connector/J . I have downloaded it from MySql site .Click here
Unzip the folder mysql-connector-java-5.1.7 and check for the jar file mysql-connector-java-5.1.7-bin.jar.Now set this jar file in the classpath.

Setting ClassPath:
Go to Environment Variables in the System Properties.
Select CLASSPATH and click edit.(if there is no classpath variable create one)
in my case i already set one path so what i did was i added a ; and given my new path.

.;C:\Program Files\Java\jre6\lib\ext\QTJava.zip;C:\mysql-connector-java-5.1.7\mysql-connector-java-5.1.7-bin.jar

classpath have been set and now we can write the java code.Below is the sample java code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;
public class SampleMysql
{
public static void main(String[] args)
{
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn =DriverManager.getConnection("jdbc:mysql://localhost/sample?" +"user=root&password=password");
System.out.println("Connection established...");
Statement stmt = conn.createStatement();
ResultSet rs =stmt.executeQuery("Select id,data from first");
while(rs.next())
{
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println(" "+id+" "+name);
}
}
catch (Exception ex) {
System.out.println("SQLException: " + ex.getMessage()); }
}
}

After executing the output was:
1 Lara
2 Sachin