CRUD OPERTAIONS USING JDBC(MYSQL)
Prerequisites
1:JDK (download JDK 7).
2:MySQL (download MySQL Community Server 5.6.12). You may also want to
download MySQL Workbench - a graphical tool for working with MySQL
databases.
3:JDBC Driver for MySQL (download MySQL Connector/J 5.1.25).Based on your
Database
4:Eclipse -Java Application
Java Program on CRUD operations:
package jdbc;
import java.sql.*;
// Step 1: Import packages
public class JDBC_Program {
public static void main(String args[])
{String dbURL = "jdbc:mysql://localhost:3306/student";
String username = "root";
String password = "";
try {
// Step 2: Loading driver class
Connection conn = DriverManager.getConnection(dbURL, username,
password);
// Step 3: Create connection object
Statement s = conn.createStatement();
// Step 4: Create a statement
//Creation of table
String sql1= "CREATE TABLE STUDENT(STUDENTID
VARCHAR(10) ,NAME VARCHAR(20))";
//Alter table
String sql0="Alter table student add DEPARTMENT VARCHAR(10)";
//Insertion of data to table
String sql2 = "INSERT INTO STUDENT
VALUES('S101','SRIKANTH','CSE')";
String sql3 = "INSERT INTO STUDENT
VALUES('S102','MAHESH','CSE')";
String sql4 = "INSERT INTO STUDENT
VALUES('S103','MOHAN','AI')";
String sql5 = "INSERT INTO STUDENT
VALUES('S104','VISHNU','DS')";
String sql6 = "INSERT INTO STUDENT
VALUES('S105','GANESH','IOT')";
s.addBatch(sql1);
s.addBatch(sql0);
s.addBatch(sql2);
s.addBatch(sql3);
s.addBatch(sql4);
s.addBatch(sql5);
s.addBatch(sql6);
s.executeBatch();
// Step 5: Create a resultset
// Step 6: Process the results
ResultSet rs = s.executeQuery("Select * from Student");
System.out.println("-------------------------------------------------------");
System.out.println("StudentID\tName");
System.out.println("-------------------------------------------------------");
System.out.println("Creation of table is done sucessfully!\n\n");
System.out.println("-------------------------------------------------------");
System.out.println("StudentID\tName\t\tDepartment");
System.out.println("-------------------------------------------------------");
System.out.println("Alteration of table is done sucessfully!\n\n");
System.out.println("-------------------------------------------------------");
System.out.println("StudentID\tName\t\tDepartment");
System.out.println("-------------------------------------------------------");
while (rs.next()) { System.out.println(rs.getString(1) + "\t\t"+
rs.getString(2)+ "\t\t"+ rs.getString(3));}
System.out.println("Insertion is done sucessfully!\n\n");
Statement s1= conn.createStatement();
String sql7 = "update student set Name='UMAHESH' where
StudentID='S102'";
s1.execute(sql7);
ResultSet rs1 = s1.executeQuery("Select * from Student");
System.out.println("-------------------------------------------------------");
System.out.println("StudentID\tName\t\tDepartment");
System.out.println("-------------------------------------------------------");
while (rs1.next()) { System.out.println(rs.getString(1) + "\t\t"+
rs.getString(2)+ "\t\t"+ rs.getString(3));}
System.out.println("Updation is done sucessfully!\n\n");
Statement s2= conn.createStatement();
String sql8 = "Delete from student where studentid='S105'";
s2.execute(sql8);
ResultSet rs2 = s.executeQuery("Select * from Student");
System.out.println("-------------------------------------------------------");
System.out.println("StudentID\tName\t\tDepartment");
System.out.println("-------------------------------------------------------");
while (rs2.next()) { System.out.println(rs.getString(1) + "\t\t"+
rs.getString(2)+ "\t\t"+ rs.getString(3));}
System.out.println("Deletion is done sucessfully!\n\n");
// Step 7: Close the connection
}
// Catch block to handle exceptions
catch (Exception e) { System.out.println(e);}
}
}
Output:
