Talking to the Database

Let use see the simplest way to connect our Java program to MySql database. For running this program, following are required.

1) MySql Database installed.

2) MySql-Connector-Java.jar file [  to be placed in the same folder of your program ]

Inorder to make your Java program communicate with any database, remember the following points

• Load the Driver Class [ Class.forName(" fully qualified name of the database driver  ")].
• Get a connection object from the DriverManager providing databaseName/username/password.
• Create statement for query using the connection object
• Execute the query using the statement object

That's it, now let us get into the code. This code explains all the four basic operations of Database queries.

•  Insertion /  Updation / Deletion / Selection of records from a database table.

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.Statement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JavaMysql

{

public Connection getDBConnection() throws SQLException,ClassNotFoundException

{

Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/MyDatabase","root","pass123");

 return connection;

}

public static void main(String args[])

{

JavaMysql jm = new JavaMysql();

Connection con = null;

Statement stmt = null;

ResultSet resultSet = null;

try

{

con = jm.getDBConnection();

stmt = con.createStatement();

int i=0;

// inserting a new row into student table

 System.out.println("------------------------ About to insert ------------------------");

 stmt.executeUpdate("insert into student values('Michael',1)");

 stmt.executeUpdate("insert into student values('John',2)");

 System.out.println(" 2 Rows inserted ");

 // selecting rows and printing as output

 System.out.println("------------------------ Before updation and Deletion -----------");

 resultSet = stmt.executeQuery("select * from student");

 while(resultSet.next())

 {

  i++;

 System.out.println("Row " + i + " contains " + resultSet.getString("name") + " with roll no " + resultSet.getString("rollno"));

  }

 // updating an existing row

 System.out.println("------------------------ About to update ------------------------");

 int no_of_rows_updated = stmt.executeUpdate("update student set rollno=10 where rollno=2 ");

 System.out.println("No of rows updated are " + no_of_rows_updated);

 // deleting a row

 System.out.println("------------------------ About to delete ------------------------");

 int no_of_rows_deleted = stmt.executeUpdate("delete from student where rollno=1");

 System.out.println("No os rows deleted are " + no_of_rows_deleted);

 // viewing the output again

 System.out.println("------------------------ After updation and Deletion ------------");

 resultSet = stmt.executeQuery("select * from student");

 i=0;

       

 while(resultSet.next())

{

i++;

System.out.println("Row " + i + " contains " + resultSet.getString("name") + " with roll no " + resultSet.getString("rollno"));

}

}

catch(Exception e)

{

e.printStackTrace();

}

}

}

Please note :

you must use different functions to perform different operations

1) executeUpdate - insert,delete,update

2) executeQuery - select