Loading

CRUD Operations using JDBC

  • CRUD means Create, Read/Fetch, Update, and Delete.
  • These are the database operations performed using Java applications.

To perform database operations we need to write the SQL queries through Java application. Some of the queries frequently used to perform operations are as follows :

  • To insert a record in a table.
"insert into tableName values( ? ,? )" ; 
  • To get any record from the table we use this query.
"select * from tableName where columnName = ?" ;
  • To delete any record from the table this query is used.
"delete from tableName where columnName = ?";
  • To update/modify the existing record in the table.
"update tableName set columnName = ? where columnName = ?";
  • To fetch/retrieve all the records from the table.
"select * from tableName";

Creating a table through JDBC

Example :

package com.quipoin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {
	public static void main(String[] args) {

		String url = "jdbc:mysql://localhost:3306/quipodb";
		String username="root";
		String password ="Naveen@1998";

		try {
			//load driver 
			Class.forName("com.mysql.cj.jdbc.Driver");
			//establish connection
			Connection con=DriverManager.getConnection(url, username, password);
			//prepare statement
			Statement st=con.createStatement();
			//execute query
			st.executeUpdate("create table employee(eno int(5) primary key,"
					+ "ename varchar(25),"
					+ "dname varchar(10),"
					+ "sal double(7,2))");
			//close connection
			st.close();
			con.close();
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		System.out.println("Table created succesfully!!");
	}
}

Output :

Table created succesfully!!

Note:

  • Execute the code go to MySql workbench and check whether the table is created or not.

Inserting the values in the table(dynamic insertion)

Example: Here we have taken the Employee class for the parameters we want in the table of the database in which we further insert the values.

package com.quipoin;

public class Employee {
	private int eno;
	private String ename;
	private String  dname;
	private double sal;
	
	public int getEno() {
		return eno;
	}
	public void setEno(int eno) {
		this.eno = eno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
}

After that, we load the Driver class establish a connection between Java and the database using JDBC and execute the statement or query for the desired CRUD operations.

package com.quipoin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ToConnect {
	public void insert(Employee emp) {
		String url="jdbc:mysql://localhost:3306/quipodb";
		String user="root";
		String password="Naveen@1998";
		String sql="insert into employee values(?,?,?,?)";

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");

			Connection con=DriverManager.getConnection(url, user, password);

			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, emp.getEno());
			ps.setString(2, emp.getEname());
			ps.setString(3, emp.getDname());
			ps.setDouble(4, emp.getSal());

			ps.execute();
			con.close();
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Data Inserted Successfully");
	}
}

At last, we set the values of parameters to be inserted in the table in the database and call the method for insertion. 

package com.quipoin;

public class Runner {
	public static void main(String[] args) {

		Employee emp=new Employee();
		emp.setEno(101);
		emp.setEname("Prasad");
		emp.setDname("Accounting");
		emp.setSal(25300.00);

		ToConnect tc=new ToConnect();
		tc.insert(emp);
	}	
}

Output:

Data Inserted Successfully!!

Note:

  • After executing the code verify MySQL workbench whether the data/record has been inserted or not.

Retrieve or Fetch the values from the table

  • After inserting data into the table we need to retrieve/ fetch it.

Example :

package com.quipoin;

public class Employee {
	private int eno;
	private String ename;
	private String  dname;
	private double sal;

	public int getEno() {
		return eno;
	}
	public void setEno(int eno) {
		this.eno = eno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
}
package com.quipoin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SelectAll {
	public void select(Employee emp) {
		String url="jdbc:mysql://localhost:3306/quipodb ";
		String user="root";
		String password="Naveen@1998";

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");

			Connection con=DriverManager.getConnection(url, user, password);
			PreparedStatement ps=con.prepareStatement("select * from employee");
			ResultSet rs=ps.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getDouble(4));
			}

		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("--------------------------------");
		System.out.println("Data fetched successfully!!");
	}
}
package com.quipoin;

public class Runner {
	public static void main(String[] args) {
		Employee emp=new Employee();

		System.out.println("eno\tename\tdname\tsal");
		System.out.println("--------------------------------");

		SelectAll s=new SelectAll();
		s.select(emp);
	}
}

Output:

eno	ename	dname	sal
--------------------------------
101	Prasad	Account	25300.0
102	Rakesh	Testing	35000.0
104	Bharath	Sales	25000.0
105	Nagaraj	Sales	45000.0
--------------------------------
Data fetched successfully!!

Delete the values in the table

  • When we want to delete/remove the unwanted records from the table we use the delete query.

Example :

package com.quipoin;

public class Employee {
	private int eno;
	private String ename;
	private String  dname;
	private double sal;

	public int getEno() {
		return eno;
	}
	public void setEno(int eno) {
		this.eno = eno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public double getSal() {
		return sal;
	}
	public void setSal(double sal) {
		this.sal = sal;
	}
}
package com.quipoin;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ToDelete {
	public void delete(int id) {
		String url="jdbc:mysql://localhost:3306/quipodb";
		String user="root";
		String password="Naveen@1998";

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");

			Connection conn=DriverManager.getConnection(url, user, password);

			PreparedStatement ps=conn.prepareStatement("delete from employee where eno=?");//delete
			ps.setInt(1, id);

			ps.execute();
			conn.close();

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		System.out.println("data removed successfully!!");
	}
}
package com.quipoin;

public class Runner {
	public static void main(String[] args) {

		Employee emp=new Employee();

		ToDelete d=new ToDelete();
		d.delete(105);
	}
}

Output:

data removed successfully!!
  • After executing the code successfully check the table whether the data is removed or not.