- Record Insertion
- Record Updation
- Record Deletion
- Viewing of the Records
In this example, we will use MySQL database. hence we need the following mysql-connector.jar file before we continue
First we will create a table called students in our mysql database TestDB.
Execute the following queries to create Database and Table .
mysql> create database TestDB;
mysql> use TestDB;
mysql> create table students(id int primary key,firstName varchar(25) not null,lastName varchar(25) not null,email varchar(75) not null unique);
JDBCConnection.java
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCConnection
{
// This method is used to make the connection with mysql database
public Connection getConnection()
{
Connection con = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/TestDB", "root", "sa1@#$");
}
catch(Exception e)
{
System.out.println("Connection Failure");
e.printStackTrace();
}
return con;
}
// This method is used to insert a row in students table
public void insertRow(int Id,String firstName,String lastName,String email)
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
try
{
stmt = connection.createStatement();
stmt.executeUpdate("insert into STUDENTS values("+ Id +",'"+ firstName +"','"+ lastName +"','"+ email +"')");
System.out.println("Student inserted successfully");
}
catch(Exception e)
{
System.out.println("Student insertion failure");
e.printStackTrace();
}
}
// This method is used to update an existing row in students table
public void updateRow(int Id,String firstName,String lastName,String email)
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
try
{
stmt = connection.createStatement();
int no_of_rows_updated = stmt.executeUpdate("update STUDENTS set firstName='"+ firstName +"',lastName='"+ lastName +"',email='"+ email +"' where id="+Id);
if(no_of_rows_updated > 0)
{
System.out.println("Student updated successfully");
}
else
{
System.out.println("Student does not exist, provide proper Id");
}
}
catch(Exception e)
{
System.out.println("Student updation failure");
e.printStackTrace();
}
}
// This method is used to delete a row in students table
public void deleteRow(int Id)
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
try
{
stmt = connection.createStatement();
int no_of_rows_deleted = stmt.executeUpdate("delete from STUDENTS where id="+Id);
if(no_of_rows_deleted > 0)
{
System.out.println("Student removed successfully");
}
else
{
System.out.println("Student does not exist, provide proper Id");
}
}
catch(Exception e)
{
System.out.println("Student deletion failure");
e.printStackTrace();
}
}
// This method is used to display all the rows of students table
public void displayRows()
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = connection.createStatement();
rs = stmt.executeQuery("select * from STUDENTS");
while(rs.next())
{
System.out.println("Id:" + rs.getString("id")+"\t FirstName:"+ rs.getString("firstName")+"\t LastName:"+ rs.getString("lastName"));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
public static void main(String args[])
{
// To insert values uncomment the below lines
//new JDBCConnection().insertRow(1, "John", "McKenzie", "john@abc.com");
//new JDBCConnection().insertRow(2, "Brian", "Zaranyika", "brian@abc.com");
// To update values uncomment the below line
//new JDBCConnection().updateRow(1, "Johny", "Bravo", "john@abc.com");
// To delete values uncomment the below line
//new JDBCConnection().deleteRow(2);
// This line displays all the rows in the table
new JDBCConnection().displayRows();
}
}
First we will create a table called students in our mysql database TestDB.
Execute the following queries to create Database and Table .
mysql> create database TestDB;
mysql> use TestDB;
mysql> create table students(id int primary key,firstName varchar(25) not null,lastName varchar(25) not null,email varchar(75) not null unique);
JDBCConnection.java
package com.test.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCConnection
{
// This method is used to make the connection with mysql database
public Connection getConnection()
{
Connection con = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/TestDB", "root", "sa1@#$");
}
catch(Exception e)
{
System.out.println("Connection Failure");
e.printStackTrace();
}
return con;
}
// This method is used to insert a row in students table
public void insertRow(int Id,String firstName,String lastName,String email)
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
try
{
stmt = connection.createStatement();
stmt.executeUpdate("insert into STUDENTS values("+ Id +",'"+ firstName +"','"+ lastName +"','"+ email +"')");
System.out.println("Student inserted successfully");
}
catch(Exception e)
{
System.out.println("Student insertion failure");
e.printStackTrace();
}
}
// This method is used to update an existing row in students table
public void updateRow(int Id,String firstName,String lastName,String email)
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
try
{
stmt = connection.createStatement();
int no_of_rows_updated = stmt.executeUpdate("update STUDENTS set firstName='"+ firstName +"',lastName='"+ lastName +"',email='"+ email +"' where id="+Id);
if(no_of_rows_updated > 0)
{
System.out.println("Student updated successfully");
}
else
{
System.out.println("Student does not exist, provide proper Id");
}
}
catch(Exception e)
{
System.out.println("Student updation failure");
e.printStackTrace();
}
}
// This method is used to delete a row in students table
public void deleteRow(int Id)
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
try
{
stmt = connection.createStatement();
int no_of_rows_deleted = stmt.executeUpdate("delete from STUDENTS where id="+Id);
if(no_of_rows_deleted > 0)
{
System.out.println("Student removed successfully");
}
else
{
System.out.println("Student does not exist, provide proper Id");
}
}
catch(Exception e)
{
System.out.println("Student deletion failure");
e.printStackTrace();
}
}
// This method is used to display all the rows of students table
public void displayRows()
{
Connection connection = new JDBCConnection().getConnection();
Statement stmt = null;
ResultSet rs = null;
try
{
stmt = connection.createStatement();
rs = stmt.executeQuery("select * from STUDENTS");
while(rs.next())
{
System.out.println("Id:" + rs.getString("id")+"\t FirstName:"+ rs.getString("firstName")+"\t LastName:"+ rs.getString("lastName"));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
public static void main(String args[])
{
// To insert values uncomment the below lines
//new JDBCConnection().insertRow(1, "John", "McKenzie", "john@abc.com");
//new JDBCConnection().insertRow(2, "Brian", "Zaranyika", "brian@abc.com");
// To update values uncomment the below line
//new JDBCConnection().updateRow(1, "Johny", "Bravo", "john@abc.com");
// To delete values uncomment the below line
//new JDBCConnection().deleteRow(2);
// This line displays all the rows in the table
new JDBCConnection().displayRows();
}
}