JDBC and MySQL

Here is a quick reference to connect mysql database with java
in linux environment.

INSTALLATION:

Hope jdk, mysql server and client are already installed on your box.
then install the libmysql-java

sudo apt-get install libmysql-java

it will install the connector in your box or download the jar file manually from

http://dev.mysql.com/downloads/connector/j/

if mysql-connector-java.jar is located in the /usr/share/java
then set the classpath

CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector-java.jar
export CLASSPATH

to make this CLASSPATH persistent add them to .bashrc file

now check with

echo $CLASSPATH

it will show similar to this :/usr/share/java/mysql-connector-java.jar

MYSQL PART

Create a user with all privileges by doing these things

at the mysql> prompt

USE mysql;

create a user mani with all privileges by

create user 'mani'@'localhost' identified by 'mani';
grant select, insert, update, delete, create, drop, references,
execute on *.* to 'mani'@'mani';

then quit the mysql by

exit;

now login with

mysql -h 127.0.0.1 -u mani -p

it will prompt for the password and enter the password we created. that is same as user

now start to create a table with all the things you needed.

JAVA PART:

database url is jdbc:mysql://localhost/dbname

dbname is the database name we want to connect.

//java program to connect mysql and query the student table
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class Student {
 //database url
 static final String DB_URL="jdbc:mysql://localhost/test";
 
 //main method
 public static void main(String[] args) {
  Connection connection = null; //connection manage
  Statement statement = null;
  ResultSet resultSet = null;
  
  try {
  //connecting with db
  connection = DriverManager.getConnection(DB_URL,"mani","mani");
  
  //statement 
  statement = connection.createStatement();
  //execute statement
  resultSet = statement.executeQuery(
   "SELECT name,age,grade FROM student");
 
  //process query results
  ResultSetMetaData metaData = resultSet.getMetaData();
  int numColumns = metaData.getColumnCount();
  System.out.println("Student information table: ");
  
  for(int i=1;i<=numColumns;i++) 
   System.out.printf("%-8s\t",metaData.getColumnName(i));
  System.out.println();
  System.out.println();
  while(resultSet.next())
  {
   for(int i=1;i<=numColumns;i++)
    System.out.printf("%-8s\t",resultSet.getObject(i));
   System.out.println();
  } //end while
  } //end try
  catch(SQLException sqlexception) {
   sqlexception.printStackTrace();
  } //end catch
  
  finally
  {
   try
   {
    resultSet.close();
    statement.close();
    connection.close();
   } catch(Exception exception) {
    exception.printStackTrace();
   } //end catch
  } //end finally
 } //end main
} //end class

that's it happy hacking..