In last below few posts we learnt about JDBC basics
What is Java Database Connectivity (JDBC)?
Types of JDBC drivers in java?
JDBC MySQL database Connection example
JDBC database connection using property resource(property file)
In this post we will learn how to perform CRUD(Create,Read,Update and Delete) Operation using Jdbc Statement.
In this example I have used MySQL database but you are free to choose any database as per your choice.
Project structure in eclipse:
SQL Query to create employee_table in MySQL (jdbcdb schema)
(Note:Make sure you have created employee_table in jdbcdb database schema)
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE `employee_table` ( `employee_id` int(11) NOT NULL auto_increment, `employee_name` varchar(60) NOT NULL, `email` varchar(45) NOT NULL, `salary` double default NULL, `date_of_joining` datetime default NULL, `bonus` decimal(10,0) default NULL, PRIMARY KEY (`employee_id`) ) ; |
DBUtil.java class which is responsible to connect with MySQL database.
this util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Now let’s run our ClientTest.java class to create an employee record in database.
(Note:this class has all the methods to perform CRUD operation you have to test every method one by one keeping rest of method calls commented in main method)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
package com.infotech.client; import java.math.BigDecimal; import java.sql.Connection; import java.sql.Date; import java.sql.ResultSet; import java.sql.Statement; import com.infotech.util.DBUtil; public class ClientTest { public static void main(String[] args) { createEmployee(); //readEmployeeById(); //updateEmployeeEmailById(); //deleteEmployeeById(); } private static void deleteEmployeeById() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { String SQLINSERT = "DELETE FROM employee_table WHERE employee_id=2"; int executeUpdate = st.executeUpdate(SQLINSERT); if(executeUpdate ==1){ System.out.println("Employee is deleted.."); } } catch (Exception e) { e.printStackTrace(); } } private static void updateEmployeeEmailById() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { int executeUpdate = st.executeUpdate(SQLINSERT); if(executeUpdate ==1){ System.out.println("Email is updated.."); } } catch (Exception e) { e.printStackTrace(); } } private static void createEmployee() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { String SQLINSERT = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus) " int executeUpdate = st.executeUpdate(SQLINSERT); if(executeUpdate ==1){ System.out.println("Employee is created.."); } } catch (Exception e) { e.printStackTrace(); } } private static void readEmployeeById() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { String SELECTSQL = "SELECT *FROM employee_table WHERE employee_id = 1"; ResultSet rs = st.executeQuery(SELECTSQL); while(rs.next()){ int employeeId = rs.getInt(1); String employeName = rs.getString(2); String email = rs.getString(3); Date doj = rs.getDate(5); BigDecimal bonus = rs.getBigDecimal(6); System.out.println("Employee Id:"+employeeId); System.out.println("Employee Name:"+employeName); System.out.println("Email:"+email); System.out.println("Date of Joining:"+doj); System.out.println("Bonus:"+bonus); } } catch (Exception e) { e.printStackTrace(); } } } |
After running ClientTest.java program you will look below output on eclipse console:
|
1 |
Employee is created.. |
now if you check your database you will find below record in employee_table
Comment all methods calls in main method except readEmployeeById() and If you run ClientTest.java to read a row from database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
package com.infotech.client; import java.math.BigDecimal; import java.sql.Connection; import java.sql.Date; import java.sql.ResultSet; import java.sql.Statement; import com.infotech.util.DBUtil; public class ClientTest { public static void main(String[] args) { //createEmployee(); readEmployeeById(); //updateEmployeeEmailById(); //deleteEmployeeById(); } private static void deleteEmployeeById() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { String SQLINSERT = "DELETE FROM employee_table WHERE employee_id=2"; int executeUpdate = st.executeUpdate(SQLINSERT); if(executeUpdate ==1){ System.out.println("Employee is deleted.."); } } catch (Exception e) { e.printStackTrace(); } } private static void updateEmployeeEmailById() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { int executeUpdate = st.executeUpdate(SQLINSERT); if(executeUpdate ==1){ System.out.println("Email is updated.."); } } catch (Exception e) { e.printStackTrace(); } } private static void createEmployee() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { String SQLINSERT = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus) " int executeUpdate = st.executeUpdate(SQLINSERT); if(executeUpdate ==1){ System.out.println("Employee is created.."); } } catch (Exception e) { e.printStackTrace(); } } private static void readEmployeeById() { try(Connection connection = DBUtil.getConnection();Statement st = connection.createStatement();) { String SELECTSQL = "SELECT *FROM employee_table WHERE employee_id = 1"; ResultSet rs = st.executeQuery(SELECTSQL); while(rs.next()){ int employeeId = rs.getInt(1); String employeName = rs.getString(2); String email = rs.getString(3); Date doj = rs.getDate(5); BigDecimal bonus = rs.getBigDecimal(6); System.out.println("Employee Id:"+employeeId); System.out.println("Employee Name:"+employeName); System.out.println("Email:"+email); System.out.println("Date of Joining:"+doj); System.out.println("Bonus:"+bonus); } } catch (Exception e) { e.printStackTrace(); } } } |
You will get below output on eclipse console:
|
1 2 3 4 5 |
Employee Id:1 Employee Name:Paul Email:paul.cs2009@yahoo.com Date of Joining:2017-05-17 Bonus:400 |
Now I leaving up to you to test how methods updateEmployeeEmailById() and deleteEmployeeById() works in client program.
That’s all about CRUD operation using JDBC Statement.
You May Also Like:
What is Java Database Connectivity (JDBC)?
Types of JDBC drivers in java?
JDBC MySQL database Connection example
JDBC database connection using property resource(property file)
Reading data from database using JDBC Statement
CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement
If you have any feedback or suggestion please feel free to drop in below comment box.