In this article, we will perform Create, Read, Update and Delete operations on a MySQL database using JDBC.
We have created Employee table in the database, with the following structure :
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) |
First, we need to setup connectivity from Java code to the database using JDBC.
Refer the following article for the same :
Connecting to Database using JDBC
For performing JDBC operations, we will create a Statement object from the Connection object as follows :
|
1 2 3 4 |
Statement stmt = conn.createStatement(); |
For Insert, Update and Delete operations, we can call Statement.executeUpdate() with required sql string as follows :
|
1 2 3 4 5 6 |
int rows = stmt.executeUpdate("insert into employee(age,name) values(23,'James')"); rows= stmt.executeUpdate("Update employee set age=31 where name='James'"); rows = stmt.executeUpdate("delete from employee where name = 'James'"); |
For Select operation, we will use executeQuery() method of Statement object.
This will provide us a ResultSet of records and we need to loop through them to find individual data.
|
1 2 3 4 5 6 7 |
ResultSet rs = stmt.executeQuery("Select * from employee"); while(rs.next()){ System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age")); } |
Here is the complete program :
|
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 |
package com.topjavatutorial.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class ExampleCRUD_JDBCOperations { public static void main(String[] args) throws SQLException { String url ="jdbc:mysql://localhost:3306/TestDatabase"; //update connection string String user = "user";//add your db user id here String password = "password";//add your db password here Connection conn = DriverManager.getConnection(url, user, password); System.out.println("Successfully connected"); //insert employee record into database Statement stmt = conn.createStatement(); int rows = stmt.executeUpdate("insert into employee(age,name) values(23,'James')"); System.out.println("Rows inserted = "+ rows); //update employee record rows= stmt.executeUpdate("Update employee set age=31 where name='James'"); System.out.println("Rows updated = "+ rows); //read employee records ResultSet rs = stmt.executeQuery("Select * from employee"); while(rs.next()){ System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age")); } //delete employee record rows = stmt.executeUpdate("delete from employee where name = 'James'"); System.out.println("Rows deleted = "+ rows); } } |
Output
Successfully connected
Rows inserted = 1
Rows updated = 1
Emp Id : 8, Name : John Doe, Age : 21
Emp Id : 10, Name : James, Age : 31
Rows deleted = 1
© 2016 – 2017, www.topjavatutorial.com. All rights reserved. On republishing this post, you must provide link to original post