CRUD-operations
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.