Create a simple Java Console application that connects with the MYSQL database by JDBC and performs (CRUD) Create, Read, Delete, and Update Operations.
Task Description:
We are going to create a user database table that contains user_id, FirstName, LastName, Email, and PhoneNumber. This table will be connected with our Java console application to Add a new user, View the list of all the users, Update any user and Delete a user.
Steps:
Create a Database with the name “test” and a user table with the column name as id, firstName, lastName, Email, and phoneNumber.
Create a Maven Project.
Create Packages
- practice.bean
- practice.main
- practice.model
Add Dependencies for MYSQL.
Below is the required maven dependencies that will required to communicate with database
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.21</version> </dependency>
Create User Bean
This user bean will help us to get and set the object value at run time.
package practice.bean;
public class User {
private long id;
String firstName;
String lastName;
String email;
String phoneNumber;
// Getters and Setter.........
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
@Override
public String toString() {
return "User [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email
+ ", phoneNumber=" + phoneNumber + "]";
}
}
Create a Database connection class
This is a connection class and return and database connection.e
package practice.model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MyDbConnection {
public static Connection con = null;
public static Connection getCon() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
}
Create Curd options
package practice.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.mysql.cj.protocol.Resultset;
import com.mysql.cj.xdevapi.Result;
import practice.bean.User;
public class UserModel {
// Add User...........
public long addUser(User user) {
long pk = 0;
Connection con = MyDbConnection.getCon();
try {
PreparedStatement stmt = con.prepareStatement("insert into user values (?,?,?,?,?)");
stmt.setLong(1, user.getId());
stmt.setString(2, user.getFirstName());
stmt.setString(3, user.getLastName());
stmt.setString(4, user.getEmail());
stmt.setString(5, user.getPhoneNumber());
pk = stmt.executeUpdate();
System.out.println("Record Is added into database"+pk);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return pk;
}
// Get User List......
public List<User> list(){
List<User> userList = new ArrayList<User>();
Connection con = MyDbConnection.getCon();
try {
PreparedStatement stmt = con.prepareStatement("select * from user");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getLong(1));
user.setFirstName(rs.getString(2));
user.setLastName(rs.getString(3));
user.setEmail(rs.getString(4));
user.setPhoneNumber(rs.getString(4));
userList.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return userList;
}
//Update user...
public User update(User user, long id) {
//User user = new User();
Connection con = MyDbConnection.getCon();
try {
PreparedStatement stmt = con.prepareStatement("update user set firstName=?, lastName=?, email=?, phoneNumber=? where id=?");
stmt.setString(1, user.getFirstName());
stmt.setString(2, user.getLastName());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getPhoneNumber());
stmt.setLong(5, id);
stmt.executeUpdate();
System.out.println("Data is updated...");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return user;
}
public void delete(long id) {
Connection con = MyDbConnection.getCon();
try {
PreparedStatement stmt = con.prepareStatement("delete from user where id = ?");
stmt.setLong(1, id);
stmt.executeUpdate();
System.out.println("Record is deleted.....");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Add User:
package practice.main;
import java.util.List;
import java.util.Scanner;
import practice.bean.User;
import practice.model.UserModel;
public class Main {
public static void main(String[] args) {
// TODO Auto-generated method stub
//Add User........
Scanner sc = new Scanner(System.in);
System.out.println("Enter User ID...");
long id = sc.nextInt();
System.out.println("Enter First Name...");
String firstName = sc.next();
System.out.println("Enter Last Name...");
String lastName = sc.next();
System.out.println("Enter Email...");
String email = sc.next();
System.out.println("Enter Phone Number...");
String phoneNumber = sc.next();
User user = new User();
user.setId(id);
user.setFirstName(firstName);
user.setLastName(lastName);
user.setEmail(email);
user.setPhoneNumber(phoneNumber);
UserModel userModel = new UserModel();
userModel.addUser(user);
}
}
Get User List
package practice.main;
import java.util.List;
import java.util.Scanner;
import practice.bean.User;
import practice.model.UserModel;
public class Main {
public static void main(String[] args) {
// TODO Auto-generated method stub
// Get the list of User....
List<User> list = userModel.list();
list.forEach((user) -> {
System.out.println(user.getId()+ " " + user.getFirstName());
});
}
}
Update User:
package practice.main;
import java.util.List;
import java.util.Scanner;
import practice.bean.User;
import practice.model.UserModel;
public class Main {
public static void main(String[] args) {
// TODO Auto-generated method stub
//Add User........
Scanner sc = new Scanner(System.in);
System.out.println("Enter User ID...");
long id = sc.nextInt();
System.out.println("Enter First Name...");
String firstName = sc.next();
System.out.println("Enter Last Name...");
String lastName = sc.next();
System.out.println("Enter Email...");
String email = sc.next();
System.out.println("Enter Phone Number...");
String phoneNumber = sc.next();
User user = new User();
user.setId(id);
user.setFirstName(firstName);
user.setLastName(lastName);
user.setEmail(email);
user.setPhoneNumber(phoneNumber);
UserModel userModel = new UserModel();
// Update user
//userModel.update(user, id);
}
}
Delete User:
package practice.main;
import java.util.Scanner;
import practice.model.UserModel;
public class DeleteUser {
public static void main(String[] args) {
// TODO Auto-generated method stub
Scanner sc = new Scanner(System.in);
System.out.println("Enter user Id that you want to delete...");
long id = sc.nextLong();
UserModel model = new UserModel();
model.delete(id);
System.out.println("Requested User is deleted..");
}
}
