Connecting Java program to MySql database

Connection of Java program with MySql database

For connecting a Java program to MySql, we need the following software :
1) Jdk (any version)
2) MySql
3) MySql Driver (mysql-connector-java-version-bin.jar  ex : mysql-connector-java-5.1.21-bin.jar)
   
Download the  mysql-connector-java-5.1.21-bin.jar from the link shown below :
http://www.ziddu.com/download/21414370/mysql_connector_java_5.1.21_bin.jar.html


Few steps in MySql :  After installing MySql, perform the following steps to create a new database and connect to it as the database name is required in Java program.


i) mysql >create database <databasename>;                        ----> Creating new database
ii) mysql >connect <databasename>;                                     ----> Connect to the database

Now create table using sql commands.

iii) mysql >show databases;                                         ----> Shows all the databases
iv) mysql >show tables;                                                ----> Shows all the tables present in that databases


Now create table using sql commands and insert values in it and commit.

Set CLASSPATH of MySql Driver :
Set the classpath  i.e the location of mysql-connector-java-5.1.21-bin.jar in the environment variable named CLASSPATH .

However the Java Program shown below creates the table table2 from the program itself and inserts values in it. If we don't want to create the table in the program and like to use the table table2 already created in database, then skip the 7th line of the program containing the create table statement.          
In the following program,
         table name = table2    (table to be connected)
         username = root         (default username of MySql database)
         password = root        (password is set by user during installation)
         database = frndz        (database name within which the table to be connected lies)

Java Program :


import java.sql.*;
public class DBCmysql2
{public static void main(String args[]) throws Exception
{Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/frndz","root","root");
Statement st= con.createStatement();
int i1= st.executeUpdate("create table table2(name varchar(12), roll_no int(10))");    //create the table
int i2=st.executeUpdate("insert into table2 values('mayank',1009007)");                   //insertion of values
int i3=st.executeUpdate("insert into table2 values('vikash',1009014)");                     //insertion of values
ResultSet rs = st.executeQuery("select * from table2");
while(rs.next())
System.out.println(rs.getString(1)+"    "+rs.getString(2));                               //printing table data on console
rs.close();
st.close();
con.close();
}
}


   
Note :  In case of database like MySql, we need to download the jar file containing Driver class (mysql-connector-java-version-bin.jar) separately and then set its classpath as it is not supplied along with the MySql database software.