Task - JDBC (2/2) - CRUD Operation on Worker Table Using Model, DAO, & Singleton #59
akash-coded
started this conversation in
Tasks
Replies: 40 comments 16 replies
-
1package util;
import java.sql.*;
// Sigleton Pattern
public class DatabaseConnection {
private static final String USERNAME = "root";
private static final String PASSWORD = "******";
private static final String URL = "jdbc:mysql://localhost:3306/my_org";
private static Connection connection = null;
private DatabaseConnection() {
}
public static Connection getConnection() throws SQLException {
if (connection == null) {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
}
return connection;
}
public static void closeConnection() throws SQLException {
if (connection != null)
connection.close();
}
}2package model;
import java.util.Date;
public class Worker implements Comparable<Worker> {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
@Override
public int compareTo(Worker other) {
return this.workerId - other.workerId;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((email == null) ? 0 : email.hashCode()) + workerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email))
return false;
if (workerId != other.workerId)
return false;
return true;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = new Date();
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
this.joiningDate = new Date();
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}3package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.text.SimpleDateFormat;
import model.Worker;
import java.sql.Statement;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws SQLException {
this.connection = DatabaseConnection.getConnection();
}
public int add(Worker worker) throws SQLException {
int workerId = worker.getWorkerId();
String firstName = worker.getFirstName();
String lastName = worker.getLastName();
int salary = worker.getSalary();
Date date = worker.getJoiningDate();
String department = worker.getDepartment();
String email = worker.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joiningDate = sdf.format(date);
String query = String.format("INSERT INTO worker VALUES(%d,'%s','%s',%d,'%s','%s','%s');", workerId,
firstName,
lastName, salary, joiningDate, department, email);
try (Statement statement = connection.createStatement()) {
return statement.executeUpdate(query);
}
}
public void delete(int workerId) throws SQLException {
String query = "DELETE FROM Worker WHERE WORKER_ID = ?";
try (PreparedStatement ps = connection.prepareStatement(query)) {
ps.setInt(1, workerId);
int res = ps.executeUpdate();
System.out.println(res + " row is deleted!!!");
}
}
public Worker getWorker(int workerId) throws SQLException {
String query = "SELECT * FROM worker WHERE worker_id=?";
Worker res = null;
try (PreparedStatement ps = connection.prepareStatement(query);) {
ps.setInt(1, workerId);
ResultSet result = ps.executeQuery();
while (result.next()) {
int workerId1 = result.getInt("worker_id");
String firstName = result.getString("first_name");
String lastName = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
String email = result.getString("email");
res = new Worker(workerId1, firstName, lastName, salary, date, department, email);
}
}
return res;
}
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet result = statement.executeQuery(query);
while (result.next()) {
int workerId = result.getInt("worker_id");
String firstName = result.getString("first_name");
String lastName = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
String email = result.getString("email");
list.add(new Worker(workerId, firstName, lastName, salary, date, department, email));
}
}
return list;
}
public void update(Worker emp) throws SQLException {
int workerId = emp.getWorkerId();
String firstName = emp.getFirstName();
String lastName = emp.getLastName();
int salary = emp.getSalary();
Date date = emp.getJoiningDate();
String department = emp.getDepartment();
String email = emp.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joiningDate = sdf.format(date);
String query = String.format("""
UPDATE worker SET
worker_id =%d,
first_name = '%s',
last_name ='%s',
salary =%d,
joining_date = '%s',
department = '%s',
email = '%s'
WHERE worker_id = %d """, workerId,
firstName,
lastName, salary, joiningDate, department, email, workerId);
try (Statement statement = connection.createStatement()) {
int res = statement.executeUpdate(query);
System.out.println(res + " row get Updated");
}
}
}5package main;
import util.DatabaseConnection;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import java.util.Date;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker = new Worker(9, "karan", "k.seth@my_org.in");
System.out.println(workerDao.add(worker) + " row is added!!!");
Worker emp = new Worker(9, "Karan", "Seth", 50000, new Date(), "new",
"k.seth1r@my_org.in");
workerDao.update(emp);
List<Worker> list = workerDao.getWorkers();
for (Worker workers : list) {
System.out.println(workers);
}
System.out.println(workerDao.getWorker(1));
workerDao.delete(9);
DatabaseConnection.closeConnection();
}
}
|
Beta Was this translation helpful? Give feedback.
2 replies
-
|
There is a trailing curly brace outside the code formatting |
Beta Was this translation helpful? Give feedback.
-
|
done sir |
Beta Was this translation helpful? Give feedback.
-
12345 |
Beta Was this translation helpful? Give feedback.
3 replies
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and datatype should match. Doesn't have to be identical. |
Beta Was this translation helpful? Give feedback.
-
|
Got it sir |
Beta Was this translation helpful? Give feedback.
-
|
And, while adding headings in markdown, there ought to be a space in between the hash(#) and the heading. |
Beta Was this translation helpful? Give feedback.
-
1package util;
import java.sql.*;
public class DatabaseConnection {
private static final String url = "jdbc:mysql://localhost:3307/myorg";
private static final String username = "root";
private static final String password = "password";
private static Connection connection = null;
private DatabaseConnection() {
}
public static Connection getConnection() throws SQLException {
if (connection == null) {
connection = DriverManager.getConnection(url, username, password);
/* Fetching some DB metadata */
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
}
return connection;
}
public static void closeConnection() throws SQLException {
connection.close();
}
}2package model;
import java.sql.Date;
public class Worker implements Comparable<Worker> {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public int getworkerId() {
return workerId;
}
public void setworkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getjoiningDate() {
return joiningDate;
}
public void setjoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + workerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (workerId != other.workerId)
return false;
return true;
}
@Override
public int compareTo(Worker o) {
return this.workerId - o.workerId;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}3package dao;
import java.sql.*;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4package dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection cn;
public WorkerDAOImplementation() throws SQLException {
cn = DatabaseConnection.getConnection();
}
public int add(Worker worker) throws SQLException {
String insertSql = """
INSERT INTO Worker (
WORKER_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
JOINING_DATE,
DEPARTMENT,
email)
VALUES
(?,?,?,?,?,?,? )""";
PreparedStatement pStatement = cn.prepareStatement(insertSql);
pStatement.setInt(1, worker.getworkerId());
pStatement.setString(2, worker.getfirstName());
pStatement.setString(3, worker.getlastName());
pStatement.setInt(4, worker.getSalary());
pStatement.setDate(5, worker.getjoiningDate());
pStatement.setString(6, worker.getDepartment());
pStatement.setString(7, worker.getEmail());
int rowsInserted = pStatement.executeUpdate();
return rowsInserted;
}
public void delete(int workerId) throws SQLException {
String deleteSql = "DELETE FROM Worker WHERE WORKER_ID = ?";
PreparedStatement pStatement = cn.prepareStatement(deleteSql);
pStatement.setInt(1, workerId);
int r = pStatement.executeUpdate();
System.out.println(r);
}
public Worker getWorker(int workerId) throws SQLException {
String selectSql = "SELECT * FROM WORKER WHERE WORKER_ID=?";
PreparedStatement ps = cn.prepareStatement(selectSql);
ps.setInt(1, workerId);
ResultSet rs = ps.executeQuery();
rs.next();
return new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getString(6),
rs.getString(7));
}
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = cn.createStatement()) {
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
list.add(new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5),
rs.getString(6),
rs.getString(7)));
}
return list;
}
}
public void update(Worker emp) throws SQLException {
String updateQuery = """
update worker
set worker_id=?,
first_name=?,
last_name=?,
salary=?,
joining_date=?,
department=?,
email=?
where worker_id=?""";
try (PreparedStatement ps = cn.prepareStatement(updateQuery)) {
ps.setInt(1, emp.getworkerId());
ps.setString(2, emp.getfirstName());
ps.setString(3, emp.getlastName());
ps.setInt(4, emp.getSalary());
ps.setDate(5, emp.getjoiningDate());
ps.setString(6, emp.getDepartment());
ps.setString(7, emp.getEmail());
ps.setInt(8, emp.getworkerId());
int r = ps.executeUpdate();
System.out.println(r);
}
}
}5import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DatabaseConnection;
import java.sql.*;
import java.util.List;
public class App {
public static void main(String[] args) throws Exception {
Worker obj = new Worker(10, "khyati", "tripathi", 50000, Date.valueOf("2022-03-21"), "new",
"k.tripathi@mail.in");
WorkerDAO workerDao = new WorkerDAOImplementation();
System.out.println(workerDao.add(obj));
workerDao.delete(22);
System.out.println(workerDao.getWorker(1));
workerDao.getWorkers();
List<Worker> list = workerDao.getWorkers();
for (Worker workers : list) {
System.out.println(workers);
}
Worker emp = new Worker(10, "new", "user", 50000, Date.valueOf("2022-03-21"), "new", "n.user@mail.in");
workerDao.update(emp);
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and datatype should match. Doesn't have to be identical. |
Beta Was this translation helpful? Give feedback.
-
|
1 package util;
import java.sql.*;
public class DatabaseConnection {
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private static final String username = "root";
private static final String password = "root";
private static Connection connection=null;
private DatabaseConnection() {
}
public static Connection getConnection() throws ClassNotFoundException{
if(connection == null){
try (Connection connection = DriverManager.getConnection(url, username, password)) {
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
return connection;
}catch(Exception e){
System.out.println(e);
}
}
return connection;
}
public static void closeConnection() throws SQLException {
connection.close();
}
}2 package model;
import java.sql.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}
3 package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4 package dao;
import java.sql.*;
import java.util.*;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws ClassNotFoundException {
connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String query = "INSERT INTO Worker (WORKER_ID,FIRST_NAME,LAST_NAME,SALARY,JOINING_DATE,DEPARTMENT,email) VALUES (?,?,?,?,?,?,? )";
PreparedStatement ps = connection.prepareStatement(query);
ps.setInt(1, worker.getWorkerId());
ps.setString(2, worker.getFirstName());
ps.setString(3, worker.getLastName());
ps.setInt(4, worker.getSalary());
ps.setDate(5, worker.getJoiningDate());
ps.setString(6, worker.getDepartment());
ps.setString(7, worker.getEmail());
return ps.executeUpdate();
}
@Override
public void delete(int workerId) throws SQLException {
String query = "DELETE FROM Worker WHERE WORKER_ID = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, workerId);
int rows = preparedStatement.executeUpdate();
System.out.println(rows);
}
@Override
public Worker getWorker(int workerId) throws SQLException {
String query = "SELECT * FROM WORKER WHERE WORKER_ID=?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, workerId);
ResultSet rs = preparedStatement.executeQuery();
rs.next();
return new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getString(6),
rs.getString(7));
}
@Override
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet resultSet = statement.executeQuery(query);
while (resultSet.next()) {
list.add(new Worker(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getInt(4), resultSet.getDate(5),
resultSet.getString(6),
resultSet.getString(7)));
}
return list;
}
}
@Override
public void update(Worker emp) throws SQLException {
String updateQuery = "UPDATE TABLE Worker SET worker_id =?,first_name = ?,last_name =?,salary =?,joining_date = ?,department = ?,email = ? WHERE worker_id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
preparedStatement.setInt(1, emp.getWorkerId());
preparedStatement.setString(2, emp.getFirstName());
preparedStatement.setString(3, emp.getLastName());
preparedStatement.setInt(4, emp.getSalary());
preparedStatement.setDate(5, emp.getJoiningDate());
preparedStatement.setString(6, emp.getDepartment());
preparedStatement.setString(7, emp.getEmail());
preparedStatement.setInt(8, emp.getWorkerId());
int rows = preparedStatement.executeUpdate();
System.out.println(rows);
}
}5 package main;
import java.sql.*;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DatabaseConnection;
public class App {
public static void main(String[] args) throws Exception{
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker = new Worker(10, "Deepika", "Vuddagiri", 5000, Date.valueOf("2022-03-21"), "Admin","deepika.v@gmail.com");
System.out.println(workerDao.add(worker));
workerDao.delete(10);
System.out.println(workerDao.getWorker(1));
List<Worker> list = workerDao.getWorkers();
list.forEach(System.out::println);
Worker obj = new Worker(10, "new", "user", 50000, Date.valueOf("2022-03-21"), "new", "user.new@gmail.in");
workerDao.update(obj);
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and datatype should match. Doesn't have to be identical. |
Beta Was this translation helpful? Give feedback.
-
1package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
static final String url = "jdbc:mysql://localhost:3307/my_org";
static final String username = "root";
static final String password = "password";
public static Connection getConnection() throws UnsupportedOperationException, SQLException{
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("Connected to :"+ conn.getMetaData().getDatabaseProductName());
return conn;
}
}2package model;
import java.sql.Date;
public class Worker {
int WORKER_ID;
String FIRST_NAME;
String LAST_NAME;
int SALARY;
Date JOINING_DATE;
String DEPARTMENT;
public Worker(int wORKER_ID) {
WORKER_ID = wORKER_ID;
}
public Worker(int wORKER_ID, String fIRST_NAME, String lAST_NAME, int sALARY, Date jOINING_DATE,
String dEPARTMENT) {
WORKER_ID = wORKER_ID;
FIRST_NAME = fIRST_NAME;
LAST_NAME = lAST_NAME;
SALARY = sALARY;
JOINING_DATE = jOINING_DATE;
DEPARTMENT = dEPARTMENT;
}
public int getWORKER_ID() {
return WORKER_ID;
}
public void setWORKER_ID(int wORKER_ID) {
WORKER_ID = wORKER_ID;
}
public String getFIRST_NAME() {
return FIRST_NAME;
}
public void setFIRST_NAME(String fIRST_NAME) {
FIRST_NAME = fIRST_NAME;
}
public String getLAST_NAME() {
return LAST_NAME;
}
public void setLAST_NAME(String lAST_NAME) {
LAST_NAME = lAST_NAME;
}
public int getSALARY() {
return SALARY;
}
public void setSALARY(int sALARY) {
SALARY = sALARY;
}
public Date getJOINING_DATE() {
return JOINING_DATE;
}
public void setJOINING_DATE(Date jOINING_DATE) {
JOINING_DATE = jOINING_DATE;
}
public String getDEPARTMENT() {
return DEPARTMENT;
}
public void setDEPARTMENT(String dEPARTMENT) {
DEPARTMENT = dEPARTMENT;
}
@Override
public String toString() {
return "WORKER_ID: "+WORKER_ID+", FIRST_NAME: "+FIRST_NAME+", LAST_NAME: "+LAST_NAME+
", SALARY: "+SALARY+", JOINING_DATE: "+JOINING_DATE+", DEPARTMENT: "+DEPARTMENT;
}
}3package dao;
import model.Worker;
import java.sql.SQLException;
import java.util.List;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4package dao;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO{
private static Connection connection;
public WorkerDAOImplementation() throws SQLException {
DatabaseConnection d = new DatabaseConnection();
connection = d.getConn();
}
public int add(Worker worker) throws SQLException{
String insert = """
INSERT INTO worker (
WORKER_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
JOINING_DATE,
DEPARTMENT)
VALUES (?,?,?,?,?,?)
""";
PreparedStatement statement = connection.prepareStatement(insert);
statement.setInt(1, worker.getWORKER_ID());
statement.setString(2, worker.getFIRST_NAME());
statement.setString(3, worker.getLAST_NAME());
statement.setInt(4, worker.getSALARY());
statement.setDate(5, worker.getJOINING_DATE());
statement.setString(6, worker.getDEPARTMENT());
int rowsInserted = statement.executeUpdate();
return rowsInserted;
}
public void delete(int workerId) throws SQLException{
String deleteStatement = """
DELETE FROM worker WHERE WORKER_ID = '"+workerId'
""";
PreparedStatement statement = connection.prepareStatement(deleteStatement);
int rowsDeleted = statement.executeUpdate();
System.out.println(rowsDeleted+" rows deleted ");
}
public Worker getWorker(int workerId) throws SQLException {
String get_worker = "SELECT * FROM worker WHERE worker_id=?";
Worker w;
try{
PreparedStatement prepstmt = connection.prepareStatement(get_worker);
prepstmt.setInt(1, workerId);
ResultSet result = prepstmt.executeQuery();
while (result.next()) {
int worker_id = result.getInt("WORKER_ID");
String first_name = result.getString("FIRST_NAME");
String last_name = result.getString("LAST_NAME");
int salary = result.getInt("SALARY");
Date date = result.getDate("JOINING_DATE");
String department = result.getString("DEPARTMENT");
w = new Worker(worker_id, first_name, last_name, salary, date, department);
}
}catch(Exception e){
e.getMessage();
}finally{
return w;
}
}
public List<Worker> getWorkers() throws SQLException {
String str = "SELECT * FROM worker";
List<Worker> workerlist = new ArrayList<>();
try {
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(str);
while (result.next()) {
int worker_id = result.getInt("worker_id");
String first_name = result.getString("first_name");
String last_name = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
workerlist.add(new Worker(worker_id, first_name, last_name, salary, date, department));
}
connection.close();
}catch(SQLException e){
e.getMessage();
}
return workerlist;
}
public void update(Worker worker) throws SQLException {
int workerId = worker.getWORKER_ID();
String firstName = worker.getFIRST_NAME();
String lastName = worker.getLAST_NAME();
int salary = worker.getSALARY();
Date date = worker.getJOINING_DATE();
String department = worker.getDEPARTMENT();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joiningDate = sdf.format(date);
String query = String.format("""
UPDATE worker SET
WORKER_ID =%d,
FIRST_NAME = '%s',
LAST_NAME ='%s',
SALARY =%d,
JOINING_DATE = '%s',
DEPARTMENT = '%s'
WHERE WORKER_ID = %d """, workerId,
firstName,
lastName, salary, joiningDate, department, workerId);
try (Statement stmt = connection.createStatement()) {
int res = stmt.executeUpdate(query);
System.out.println(res+" rows updated");
}
}
}5import java.util.Date;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DatabaseConnection;
public class App {
static Date NOW(){
return new Date();
}
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker = new Worker(101, "ABC", "PQR", 45000, NOW(), "ECE");
System.out.println(workerDao.add(worker) + " row is added!!!");
workerDao.update(worker);
List<Worker> list = workerDao.getWorkers();
for (Worker ws : list) {
System.out.println(ws);
}
System.out.println(workerDao.getWorker(101));
workerDao.delete(101);
System.out.println(workerDao.getWorker(101));
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and datatype should match. Doesn't have to be identical. |
Beta Was this translation helpful? Give feedback.
-
1package util;
import java.sql.Connection;
import java.sql.DriverManager;
public class DatabaseConnection {
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
private static final String URL = "jdbc:mysql://localhost:3306/my_org";
private static Connection connection = null;
private DatabaseConnection() {}
if (connection!=null) return connection;
public static Connection getConnection() {
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: " + connection.getMetaData().getDatabaseProductVersion());
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void closeConnection(Connection conn){
try {
if(conn!=null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}2package model;
import java.sql.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName + ", joiningDate="
+ joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId=" + workerId + "]";
}
}
3package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker worker)
throws SQLException;
}4package dao;
import java.sql.*;
import java.util.*;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection = null;
public WorkerDAOImplementation() throws ClassNotFoundException, SQLException {
connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String query = """
INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT, EMAIL)
VALUES
(?,?,?,?,?,?,? )""";
PreparedStatement ps = connection.prepareStatement(query);
ps.setInt(1, worker.getWorkerId());
ps.setString(2, worker.getFirstName());
ps.setString(3, worker.getLastName());
ps.setInt(4, worker.getSalary());
ps.setDate(5, worker.getJoiningDate());
ps.setString(6, worker.getDepartment());
ps.setString(7, worker.getEmail());
return ps.executeUpdate();
}
@Override
public void delete(int workerId) throws SQLException {
PreparedStatement ps = connection.prepareStatement("DELETE FROM worker WHERE worker_id = ?");
ps.setInt(1, workerId);
ps.executeUpdate();
}
@Override
public Worker getWorker(int workerId) throws SQLException {
PreparedStatement ps = connection.prepareStatement("SELECT * FROM worker WHERE worker_id = ?");
ps.setInt(1, workerId);
ResultSet rs = ps.executeQuery();
rs.next();//to move cursor to first row
return new Worker(rs.getInt("worker_id"), rs.getString("first_name"), rs.getString("last_name"),
rs.getInt("salary"), rs.getDate("joining_date"), rs.getString("department"), rs.getString("email"));
}
@Override
public List<Worker> getWorkers() throws SQLException {
List<Worker> workers = new ArrayList<>();
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM worker");
while(rs.next())
workers.add(new Worker(rs.getInt("worker_id"), rs.getString("first_name"), rs.getString("last_name"),
rs.getInt("salary"), rs.getDate("joining_date"), rs.getString("department"), rs.getString("email")));
return workers;
}
@Override
public void update(Worker worker) throws SQLException {
String updateQuery = """
UPDATE Worker SET
worker_id =?, first_name = ?, last_name =?, salary =?,
joining_date = ?, DEPARTMENT = ?, email = ?
WHERE worker_id = ?""";
PreparedStatement ps = connection.prepareStatement(updateQuery);
ps.setInt(1, worker.getWorkerId());
ps.setString(2, worker.getFirstName());
ps.setString(3, worker.getLastName());
ps.setInt(4, worker.getSalary());
ps.setDate(5, worker.getJoiningDate());
ps.setString(6, worker.getDepartment());
ps.setString(7, worker.getEmail());
ps.setInt(8, worker.getWorkerId());
//System.out.println(ps.toString());
ps.executeUpdate();
}
}5package main;
import java.sql.Date;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
WorkerDAO workerDao = new WorkerDAOImplementation();
workerDao.add(new Worker(101, "Deepak", "dd@gmail.com"));
workerDao.delete(2);
System.out.println( workerDao.getWorker(4));
System.out.println(workerDao.getWorkers());
workerDao.update(new Worker(5, "modifiedName", "new@email.in"));
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
1package util;
import java.sql.*;
public class DatabaseConnection {
private static Connection connection = null;
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private static final String username = "root";
private static final String password = "********";
public static Connection getConnection() {
if (connection == null) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
} catch (Exception e) {
e.printStackTrace();
}
}
return connection;
}
}
2package model;
import java.sql.Date;
public class Worker implements Comparable<Worker> {
private int WorkerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int WorkerId, String firstName, String email) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.email = email;
}
public Worker(int WorkerId, String firstName, String email, Date joiningDate) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.email = email;
this.joiningDate = joiningDate;
}
public Worker(int WorkerId, String firstName, String lastName, int salary, String department, String email) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.department = department;
this.email = email;
}
public Worker(int WorkerId, String firstName, String lastName, int salary, Date joiningDate,
String department, String email) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return WorkerId;
}
public void setWorkerId(int WorkerId) {
this.WorkerId = WorkerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getjoiningDate() {
return joiningDate;
}
public void setjoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + WorkerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email))
return false;
if (WorkerId != other.WorkerId)
return false;
return true;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", WorkerId="
+ WorkerId + "]";
}
@Override
public int compareTo(Worker w) {
return this.getWorkerId() - w.getWorkerId();
}
}
3package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}
4package dao;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws SQLException {
this.connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
int workerId = worker.getWorkerId();
String firstName = worker.getfirstName();
String lastName = worker.getlastName();
int salary = worker.getSalary();
Date date = worker.getjoiningDate();
String department = worker.getDepartment();
String email = worker.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String joiningDate = sdf.format(date);
String query1 = "INSERT INTO worker VALUES( " + workerId + firstName + lastName + salary + joiningDate
+ department + email + ")";
try (Statement statement = connection.createStatement()) {
return statement.executeUpdate(query1);
}
}
@Override
public void delete(int workerId) throws SQLException {
String query2 = "DELETE FROM worker WHERE worker_id = " + workerId;
try (Statement statement = connection.createStatement()) {
int rowDeleted = statement.executeUpdate(query2);
System.out.println(rowDeleted + " row deleted");
}
}
public Worker getWorker(int workerId) throws SQLException {
Worker w = null;
String query3 = "select * from worker where WORKER_ID=?";
try (PreparedStatement statement = connection.prepareStatement(query3)) {
statement.setInt(1, workerId);
ResultSet res = statement.executeQuery();
while (res.next()) {
w = new Worker(res.getInt(1), res.getString(2), res.getString(3), res.getInt(4), res.getDate(5),
res.getString(6), res.getString(7));
}
} catch (SQLException e) {
e.printStackTrace();
}
return w;
}
@Override
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> workerList = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet res = statement.executeQuery(query);
while (res.next()) {
int workerId = res.getInt("worker_id");
String firstName = res.getString("first_name");
String lastName = res.getString("last_name");
int salary = res.getInt("salary");
Date date = res.getDate("joining_date");
String department = res.getString("department");
String email = res.getString("email");
workerList.add(new Worker(workerId, firstName, lastName, salary, date, department, email));
}
}
return workerList;
}
@Override
public void update(Worker worker) throws SQLException {
String updateQuery = "update worker set first_name=?,last_name=?,salary=?,joining_date=?,department=?,email=? where worker_id=?";
try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
statement.setString(1, worker.getfirstName());
statement.setString(2, worker.getlastName());
statement.setInt(3, worker.getSalary());
statement.setDate(4, worker.getjoiningDate());
statement.setString(5, worker.getDepartment());
statement.setString(6, worker.getEmail());
statement.setInt(7, worker.getWorkerId());
int rows = statement.executeUpdate();
System.out.println(rows + " worker table updated");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5package main;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker1 = new Worker(10, "Shubhi", "s.giri@my_org.in");
System.out.println(workerDao.add(worker1));
workerDao.delete(10);
List<Worker> list = workerDao.getWorkers();
list.forEach(System.out::println);
System.out.println(workerDao.getWorker(2));
Worker worker = new Worker(1, "Aadya", "a.agarwal@my_org.in");
workerDao.update(worker);
System.out.println(workerDao.getWorker(1));
}
}
|
Beta Was this translation helpful? Give feedback.
2 replies
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and datatype should match. Doesn't have to be identical. |
Beta Was this translation helpful? Give feedback.
-
|
Done sir. Updated |
Beta Was this translation helpful? Give feedback.
-
1package util;
import java.sql.*;
public class DatabaseConnection {
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private static final String username = "root";
private static final String password = "root";
private static Connection connection=null;
private DatabaseConnection(){}
public static Connection getConnection() throws SQLException, ClassNotFoundException {
if (connection == null)
{
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
}
return connection;
}
public static void closeConnection() throws SQLException
{
if(connection != null)
connection.close();
}
}2package model;
import java.sql.Date;
public class Worker implements Comparable<Worker> {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((email == null) ? 0 : email.hashCode());
result = prime * result + workerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email))
return false;
if (workerId != other.workerId)
return false;
return true;
}
@Override
public int compareTo(Worker o) {
return this.workerId-o.workerId;
}
@Override
public String toString() {
return "Worker [workerId=" + workerId + ", firstName=" + firstName + ", lastName=" + lastName + ", joiningDate="
+ joiningDate + ", salary=" + salary + ", department=" + department + ", email=" + email + "]";
}
}3package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4package dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection conn;
public WorkerDAOImplementation() throws SQLException, ClassNotFoundException {
this.conn = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
PreparedStatement ps = conn.prepareStatement("insert into worker values(?,?,?,?,?,?,?)");
ps.setInt(1, worker.getWorkerId());
ps.setString(2, worker.getFirstName());
ps.setString(3, worker.getLastName());
ps.setInt(4, worker.getSalary());
ps.setDate(5, worker.getJoiningDate());
ps.setString(6, worker.getDepartment());
ps.setString(7, worker.getEmail());
return ps.executeUpdate();
}
@Override
public void delete(int workerId) throws SQLException {
PreparedStatement ps = conn.prepareStatement("delete from worker where worker_id=?");
ps.setInt(1, workerId);
int rowsDeleted = ps.executeUpdate();
System.out.println("Rows deleted: " + rowsDeleted);
}
@Override
public Worker getWorker(int workerId) throws SQLException {
PreparedStatement ps = conn.prepareStatement("select * from worker where worker_id=?");
ps.setInt(1,workerId);
ResultSet rs = ps.executeQuery();
rs.next();
return new Worker(rs.getInt(1),rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getString(6),
rs.getString(7));
}
@Override
public List<Worker> getWorkers() throws SQLException {
List<Worker> workersList = new ArrayList<>();
PreparedStatement ps = conn.prepareStatement("select * from worker");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
workersList.add(new Worker(rs.getInt(1),rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getString(6),
rs.getString(7)));
}
return workersList;
}
@Override
public void update(Worker emp) throws SQLException {
PreparedStatement ps = conn.prepareStatement(
"update worker SET worker_id =?,first_name = ?,last_name =?,salary =?,joining_date = ?,department = ?,email = ? WHERE worker_id = ?");
ps.setInt(1, emp.getWorkerId());
ps.setString(2, emp.getFirstName());
ps.setString(3, emp.getLastName());
ps.setInt(4, emp.getSalary());
ps.setDate(5, emp.getJoiningDate());
ps.setString(6, emp.getDepartment());
ps.setString(7, emp.getEmail());
ps.setInt(8, emp.getWorkerId());
int rowsUpdated = ps.executeUpdate();
System.out.println(rowsUpdated + " row updated");
}
}5package main;
import java.sql.Date;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DatabaseConnection;
public class App {
public static void main(String[] args) throws Exception {
WorkerDAO workerDAOObj = new WorkerDAOImplementation();
Worker workerObj = new Worker(10, "Aiswarya", "Seepana", 30000, Date.valueOf("2022-03-21"), "Account",
"a.seepana@my_org.in");
Worker updateEmp= new Worker(9, "Sridevi", "Yamala", 50000, Date.valueOf("2022-03-22"), "Account",
"s.yamala@my_org.in");
System.out.println(workerDAOObj.add(workerObj) + " record added");
workerDAOObj.delete(10);
System.out.println(workerDAOObj.getWorker(9));
System.out.println("Emp List");
List<Worker> list = workerDAOObj.getWorkers();
list.forEach(System.out::println);
workerDAOObj.update(updateEmp);
System.out.println(workerObj.compareTo(updateEmp));
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and datatype should match. Doesn't have to be identical. |
Beta Was this translation helpful? Give feedback.
-
|
##1 |
Beta Was this translation helpful? Give feedback.
0 replies
-
1.Create database connection file and get connection instanceclass DatabaseConnection
{
final String url = "jdbc:mysql://localhost:3307/my_org";
final String userName = "root";
final String password = "Kamini@1";
DatabaseConnection()
{
}
public static Connection getConnection()
{
try{
DatabaseConnection dc=new DatabaseConnection();
Connection cn= DriverManager.getConnection(dc.url,dc.userName , dc.password);
System.out.println("Database server is "+cn.getMetaData().getDatabaseProductName()+" \n and version is "+cn.getMetaData().getDatabaseProductVersion());
return cn;
}
catch(Exception e)
{
System.out.println(e);
return null;
}
}
}2.Create worker model class ->POJO classpackage model;
import java.util.Date;
public class Worker {
int workerId;
String firstName;
String lastName;
int salary;
Date joiningDate;
String department;
String email;
public Worker(int workerId,String fname,String email)
{
this.workerId=workerId;
this.firstName=fname;
this.email=email;
this.joiningDate=new Date();
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId =workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department,
String email) {
this.workerId =workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = new Date();
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joining_date=" + joiningDate + ", last_name=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}3.WorkerDAO filepackage dao;
import model.Worker;
import java.util.List;
import java.sql.SQLException;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4. imlement worker dao functionalitiespackage dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import util.DatabaseConnection;
import model.Worker;
public class WorkerDAOImplementation implements WorkerDAO {
Connection cn;
public WorkerDAOImplementation() throws SQLException{
this.cn = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
int workerId = worker.getWorkerId();
String firstName = worker.getFirstName();
String lastName = worker.getLastName();
int salary = worker.getSalary();
Date date = worker.getJoiningDate();
String department = worker.getDepartment();
String email = worker.getEmail();
String joiningDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
String query = String.format("INSERT INTO my_worker VALUES(%d,'%s','%s',%d,'%s','%s','%s');", workerId,
firstName,
lastName, salary, joiningDate, department, email);
try (Statement statement = cn.createStatement()) {
return statement.executeUpdate(query);
}
}
@Override
public void delete(int workerId) throws SQLException {
String query = "Delete from my_worker where worker_id=" + workerId;
try (Statement smt = this.cn.createStatement();) {
System.out.println(smt.executeUpdate(query)+" deleted successfully");
} catch (Exception e) {
System.out.println(e);
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
String query = "Select * from my_worker where worker_id=" + workerId;
try (Statement smt = this.cn.createStatement();) {
ResultSet rs = smt.executeQuery(query);
while(rs.next())
{
int id = rs.getInt("worker_id");
System.out.println("@@");
String fname = rs.getString("first_name");
String lname = rs.getString("last_name");
int salary = rs.getInt("salary");
Date date = rs.getDate("joining_date");
String dept = rs.getString("department");
String email = rs.getString("email");
return new Worker(id, fname, lname, salary, date, dept, email);
}
return null;
}
}
@Override
public List<Worker> getWorkers() throws SQLException {
List<Worker> workersList = new ArrayList<>();
String query = "Select * from my_worker";
try(Statement smt = this.cn.createStatement();)
{
ResultSet rs = smt.executeQuery(query);
while (rs.next()) {
int id = rs.getInt("worker_id");
String fname = rs.getString("first_name");
String lname = rs.getString("last_name");
int salary = rs.getInt("salary");
Date date = rs.getDate("joining_date");
String dept = rs.getString("department");
String email = rs.getString("email");
Worker obj = new Worker(id, fname, lname, salary, date, dept, email);
workersList.add(obj);
}
return workersList;
}
catch(Exception e)
{
System.out.println(e);
return workersList;
}
}
@Override
public void update(Worker emp) throws SQLException {
int id = emp.getWorkerId();
String fname = emp.getFirstName();
String lname = emp.getLastName();
int salary = emp.getSalary();
Date date = emp.getJoiningDate();
String joiningDate=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
String dept = emp.getDepartment();
String email = emp.getEmail();
try (Statement smt = cn.createStatement()) {
String query=String.format("Update my_worker set worker_id=%d,first_name='%s',last_name='%s',salary=%d,joining_date='%s',department='%s',email='%s' where worker_id=%d", id,fname,lname,salary,joiningDate,dept,email,id);
int x = smt.executeUpdate(query);
System.out.println(x + " rows Updated");
}
}
}5 Driver class and demonstrate CRUD operationspackage main;
import model.Worker;
import dao.WorkerDAOImplementation;
import util.DatabaseConnection;
import java.util.Date;
import dao.WorkerDAO;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker w1=new Worker(78, "Kamini", "Jha", 1200000, new Date(), "IT", "k@gmail.com");
Worker w2=new Worker(12, "Akansha", "Singh", 123456, "Civil", "Aka@gmail.com");
Worker w3=new Worker(123, "Kanak", "kanak@gmail.com");
//************add workers
System.out.println( workerDao.add(w1)+" row added successfully");
System.out.println( workerDao.add(w2)+" row added successfully");
System.out.println( workerDao.add(w3)+" row added successfully");
// deleting worker
workerDao.delete(12);
// get worker
System.out.println(workerDao.getWorker(123));
// get list of working workers there
System.out.println(workerDao.getWorkers());
//update worker
workerDao.update(new Worker(78, "kamini", "jha", 12345677, new Date(), "HR", "email@gmail.com"));
//close connection
DatabaseConnection.getConnection().close();
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
You can name the data members as per Java naming conventions (aka camelCase). The name and data type should match. Doesn't have to be identical. Add code highlighting. |
Beta Was this translation helpful? Give feedback.
-
Subtask 1.package util;
import java.sql.*;
public class DatabaseConnection {
private DatabaseConnection() {
}
private static final String url = "jdbc:mysql://localhost:3307/org";
private static final String username = "root";
private static final String password = "satyasri";
public static Connection getConnection() throws SQLException {
try (Connection conn = DriverManager.getConnection(url, username, password)) {
System.out.println("Connected to Database server" + conn.getMetaData().getDatabaseProductName() + "version"
+ conn.getMetaData().getDatabaseProductVersion());
return conn;
} catch (SQLException e) {
System.out.println(e);
return null;
}
}
}Subtask 2.package model;
import java.sql.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private double salary;
private Date joining_date;
private String department;
private String email;
```java
public int getWorker_id() {
return workerId;
}
public void setWorker_id(int workerId) {
this.workerId = workerId;
}
public String getFirst_name() {
return firstName;
}
public void setFirst_name(String firstName) {
this.firstName = firstName;
}
public String getlastName() {
return lastName;
}
public void setLast_name(String lastName) {
this.lastName = lastName;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Date getJoining_date() {
return joining_date;
}
public void setJoining_date(Date joining_date) {
this.joining_date = joining_date;
}
public String getDepartmentT() {
return department;
}
public void setDepartmentT(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Worker(int workerId, String email) {
this.workerId = workerId;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, double salary, Date joining_date, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joining_date = joining_date;
this.department = department;
this.email = email;
}
@Override
public String toString() {
return "Worker [departmentT=" + department + ", email=" + email + ", first_name=" + firstName
+ ", joining_date=" + joining_date + ", last_name=" + lastName + ", salary=" + salary + ", worker_id="
+ workerId + "]";
}
}
### Subtask 3.
``package dao;`
```java
import java.sql.*;
import model.Worker;
import java.util.*
```;
public interface Workerdao {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}
}
SUBTASK 4.
package dao;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
public class Workerimplementation implements Workerdao {
Connection connection = null;
public Workerimplementation(Connection con) {
this.connection = con;
}
public int add(Worker worker)
throws SQLException {
String query = """
INSERT INTO Worker (
WORKER_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
JOINING_DATE,
DEPARTMENT,
email)
VALUES
(?,?,?,?,?,?,? )""";
PreparedStatement ps = connection.prepareStatement(query);
ps.setInt(1, worker.getWorker_id());
ps.setString(2, worker.getFirst_name());
ps.setString(3, worker.getlastName());
ps.setDouble(4, worker.getSalary());
ps.setDate(5, worker.getJoining_date());
ps.setString(6, worker.getDepartmentT());
ps.setString(7, worker.getEmail());
return ps.executeUpdate();
}
public void delete(int workerId)
throws SQLException {
String query = String.format("DELETE FROM worker WHERE worker_id=%d", workerId);
try (PreparedStatement statement = connection.prepareStatement(query)) {
int k = statement.executeUpdate(query);
System.out.println(k + " records deleted");
}
}
public Worker getWorker(int workerId)
throws SQLException {
String query = "SELECT * FROM worker WHERE worker_id=?";
Worker res = null;
try (PreparedStatement ps = connection.prepareStatement(query);) {
ps.setInt(1, workerId);
ResultSet result = ps.executeQuery();
while (result.next()) {
int workerId1 = result.getInt("worker_id");
String firstName = result.getString("first_name");
String lastName = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
String email = result.getString("email");
res = new Worker(workerId1, firstName, lastName, salary, date, department, email);
}
}
return res;
}
public List<Worker> getWorkers()
throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (PreparedStatement st = connection.prepareStatement(query)) {
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
int worker_id = rs.getInt("worker_id");
String first_name = rs.getString("first_name");
String last_name = rs.getString("last_name");
int salary = rs.getInt("salary");
Date date = rs.getDate("joining_date");
String department = rs.getString("department");
String email = rs.getString("email");
list.add(new Worker(worker_id, first_name, last_name, salary, date, department, email));
}
}
return list;
}
public void update(Worker emp)
throws SQLException {
int workerId = emp.getWorker_id();
String firstName = emp.getFirst_name();
String lastName = emp.getlastName();
double salary = emp.getSalary();
Date date = emp.getJoining_date();
String department = emp.getDepartmentT();
String email = emp.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joiningDate = sdf.format(date);
String query = String.format("""
UPDATE worker SET
worker_id =%d,
first_name = '%s',
last_name ='%s',
salary =%d,
joining_date = '%s',
department = '%s',
email = '%s'
WHERE worker_id = %d """, workerId,
firstName,
lastName, salary, joiningDate, department, email, workerId);
try (Statement statement = connection.createStatement()) {
int res = statement.executeUpdate(query);
System.out.println(res + " row get Updated");
}
}
}
`
SUBTASK 5.
package main;
import util.DatabaseConnection;
import java.sql.*;
import dao.Workerdao;
import dao.Workerimplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
try (Connection con = DatabaseConnection.getConnection()) {
Workerdao workerDao = new Workerimplementation(con);
Worker w = new Worker(15, "Batchu", "satyasri", 40000, new Date(System.currentTimeMillis()), "ADMIN",
"batchu.satyasri@my_org.in");
System.out.println(workerDao.add(w));
workerDao.delete(15);
System.out.println(workerDao.getWorker(5));
}
catch (SQLException e) {
System.out.println(e);
}
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
Good job on the variable naimg. Add heading with the subtask numbering before the code. Rectify the code formatting and highlighting on GitHub |
Beta Was this translation helpful? Give feedback.
-
|
1 package util;
import java.sql.*;
public class DatabaseConnection {
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private static final String username = "root";
private static final String password = "*****";
private static Connection connection = null;
private DatabaseConnection() {
}
public static Connection getConnection() {
if (connection == null) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
} catch (SQLException | ClassNotFoundException e) {
System.out.println(e);
}
}
return connection;
}
public static void closeConnection() {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
System.out.println(e);
}
}
}
}2 package model;
import java.sql.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private double salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String lastName, double salary, Date joiningDate,
String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = new Date(new java.util.Date().getTime());
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String email) {
this.workerId = workerId;
this.email = email;
this.joiningDate = new Date(new java.util.Date().getTime());
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 Double getSalary() {
return salary;
}
public void setSalary(Double salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName + ", joiningDate="
+ joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId=" + workerId + "]";
}
}3 package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker) throws SQLException;
public void delete(int workerId) throws SQLException;
public Worker getWorker(int workerId) throws SQLException;
public List<Worker> getWorkers() throws SQLException;
public int update(Worker emp) throws SQLException;
}4 package dao;
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 model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplemenation implements WorkerDAO {
private static Connection connection;
public WorkerDAOImplemenation() {
connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String format = """
INSERT INTO Worker VALUES (?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement prep = connection.prepareStatement(format)) {
prep.setInt(1, worker.getWorkerId());
prep.setString(2, worker.getFirstName());
prep.setString(3, worker.getLastName());
prep.setDouble(4, worker.getSalary());
prep.setDate(5, worker.getJoiningDate());
prep.setString(6, worker.getDepartment());
prep.setString(7, worker.getEmail());
return prep.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
}
return 0;
}
@Override
public void delete(int workerId) throws SQLException {
String format = """
DELETE FROM Worker where worker_id = ?
""";
try (PreparedStatement prep = connection.prepareStatement(format)) {
prep.setInt(1, workerId);
prep.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
String format = """
SELECT * FROM Worker
WHERE Worker_id = ?
""";
try (PreparedStatement prep = connection.prepareStatement(format)) {
prep.setInt(1, workerId);
ResultSet res = prep.executeQuery();
res.next();
return new Worker(res.getInt(1), res.getString(2), res.getString(3), res.getDouble(4), res.getDate(5),
res.getString(6), res.getString(7));
} catch (
SQLException e) {
System.out.println(e);
}
return null;
}
@Override
public List<Worker> getWorkers() throws SQLException {
List<Worker> list = new ArrayList<>();
String format = """
SELECT * FROM Worker
""";
try (PreparedStatement prep = connection.prepareStatement(format)) {
ResultSet res = prep.executeQuery();
while (res.next()) {
list.add(new Worker(res.getInt(1), res.getString(2), res.getString(3), res.getDouble(4), res.getDate(5),
res.getString(6), res.getString(7)));
}
return list;
} catch (SQLException e) {
System.out.println(e);
}
return null;
}
@Override
public void update(Worker emp) throws SQLException {
String format = """
UPDATE Worker
SET
worker_id = ?,
first_name = ?,
last_name = ?,
salary = ?,
joining_date = ?,
department = ?,
email = ?
WHERE Worker_id = ?
""";
try (PreparedStatement prep = connection.prepareStatement(format)) {
prep.setInt(1, emp.getWorkerId());
prep.setString(2, emp.getFirstName());
prep.setString(3, emp.getLastName());
prep.setDouble(4, emp.getSalary());
prep.setDate(5, emp.getJoiningDate());
prep.setString(6, emp.getDepartment());
prep.setString(7, emp.getEmail());
prep.setInt(8, emp.getWorkerId());
return prep.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
}
return 0;
}
}5 import java.util.Date;
import java.util.List;
import dao.WorkerDAOImplemenation;
import model.Worker;
import util.DatabaseConnection;
public class App {
public static void main(String[] args) throws Exception {
WorkerDAOImplemenation daoImplemenation = new WorkerDAOImplemenation();
Worker worker = new Worker(9, "Shreiya", "Randive", 1520512.0, new java.sql.Date(new Date().getTime()),
"Software",
"shreiy@gmail.com");
int rowsAffected = daoImplemenation.add(worker);
System.out.println(rowsAffected + " row(s) affected");
daoImplemenation.delete(9);
Worker worker2 = daoImplemenation.getWorker(1);
System.out.println(worker2);
List<Worker> list = daoImplemenation.getWorkers();
System.out.println(list);
int rowsUpdated = daoImplemenation
.update(new Worker(9, "Ashish", "Randive", 1520512.0, new java.sql.Date(new Date().getTime()),
"Software",
"shreiy@gmail.com"));
System.out.println(rowsUpdated + " row(s) affected");
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
updated @akash-coded |
Beta Was this translation helpful? Give feedback.
-
|
1- package util;
import java.sql.*;
public class DatabaseConnection {
static final String url = "jdbc:mysql://localhost:3307/my_org";
static final String username = "root";
static final String password = "password";
public static Connection getConnection() throws ClassNotFoundException{
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection connection = DriverManager.getConnection(url, username, password)) {
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
return connection;
}catch(Exception e){
System.out.println(e);
}
return null;
}
}2- package model;
import java.sql.Date;
public class Worker {
private int WorkerID;
private String firstName;
private String lastName;
private String salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerID, String firstName, Date joiningDate) {
WorkerID = workerID;
this.firstName = firstName;
this.joiningDate = joiningDate;
}
public Worker(int workerID, String lastName) {
WorkerID = workerID;
this.lastName = lastName;
}
public Worker(int workerID, String firstName, String lastName, String salary, String department, String email) {
WorkerID = workerID;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.department = department;
this.email = email;
}
public Worker(int WorkerID, String firstName, String lastName, String salary, Date joiningDate,
String department, String email) {
this.WorkerID = WorkerID;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerID() {
return WorkerID;
}
public void setWorkerID(int WorkerID) {
this.WorkerID = WorkerID;
}
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 getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public Date getjoiningDate() {
return joiningDate;
}
public void setjoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", WorkerID="
+ WorkerID + "]";
}
}3- package dao;
import model.Worker;
import java.sql.*;
import java.util.List;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4- |
Beta Was this translation helpful? Give feedback.
0 replies
-
package util;
import java.sql.*;
public class DatabaseConnection {
static final String password = "password";
static final String username = "root";
static final String url = "jdbc:mysql://localhost:3306/my_org";
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
return connection;
}
}package model;
import java.sql.Date;
public class Worker {
int WORKER_ID;
String FIRST_NAME;
String LAST_NAME;
int SALARY;
Date JOINING_DATE;
String DEPARTMENT;
public Worker(int WORKER_ID) {
WORKER_ID = WORKER_ID;
}
public Worker(int WORKER_ID, String FIRST_NAME, String LAST_NAME, int SALARY, Date JOINING_DATE, String DEPARTMENT) {
this.WORKER_ID = WORKER_ID;
this.FIRST_NAME = FIRST_NAME;
this.LAST_NAME = LAST_NAME;
this.SALARY = SALARY;
this.JOINING_DATE = JOINING_DATE;
this.DEPARTMENT = DEPARTMENT;
}
public int getWORKER_ID() {
return WORKER_ID;
}
public void setWORKER_ID(int WORKER_ID) {
this.WORKER_ID = WORKER_ID;
}
public String getFIRST_NAME() {
return FIRST_NAME;
}
public void setFIRST_NAME(String FIRST_NAME) {
this.FIRST_NAME = FIRST_NAME;
}
public String getLAST_NAME() {
return LAST_NAME;
}
public void setLAST_NAME(String LAST_NAME) {
this.LAST_NAME = LAST_NAME;
}
public int getSALARY() {
return SALARY;
}
public void setSALARY(int SALARY) {
this.SALARY = SALARY;
}
public Date getJOINING_DATE() {
return JOINING_DATE;
}
public void setJOINING_DATE(Date JOINING_DATE) {
this.JOINING_DATE = JOINING_DATE;
}
public String getDEPARTMENT() {
return DEPARTMENT;
}
public void setDEPARTMENT(String DEPARTMENT) {
this.DEPARTMENT = DEPARTMENT;
}
@Override
public String toString() {
return "WORKER_ID: "+ WORKER_ID + ", FIRST_NAME: " + FIRST_NAME + ", LAST_NAME: " + LAST_NAME + ", SALARY: " + SALARY + ", JOINING_DATE: " + JOINING_DATE + ", DEPARTMENT: " + DEPARTMENT;
}
}package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
package util;
import java.sql.Connection;
import java.sql.*;
public class DBConnection {
private static final String user = "root";
private static final String password = "password";
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private DBConnection() {
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println("Connected to"
+ connection.getMetaData().getDatabaseProductName()
+ "version " + connection.getMetaData().getDatabaseProductVersion());
return connection;
}
}package model;
import java.util.Date;
public class Worker {
private int worker_id;
private String first_name;
private String last_name;
private int salary;
private Date joining_date;
private String department;
private String email;
public Worker(int worker_id, String first_name, String last_name, int salary, Date joining_date, String department,
String email) {
this.worker_id = worker_id;
this.first_name = first_name;
this.last_name = last_name;
this.salary = salary;
this.joining_date = joining_date;
this.department = department;
this.email = email;
}
public Worker(int worker_id, String email) {
this.worker_id = worker_id;
this.email = email;
this.joining_date = new Date();
}
public int getWorker_id() {
return worker_id;
}
public void setWorker_id(int worker_id) {
this.worker_id = worker_id;
}
public String getFirst_name() {
return first_name;
}
public void setFirst_name(String first_name) {
this.first_name = first_name;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String last_name) {
this.last_name = last_name;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoining_date() {
return joining_date;
}
public void setJoining_date(Date joining_date) {
this.joining_date = joining_date;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", first_name=" + first_name
+ ", joining_date=" + joining_date + ", last_name=" + last_name + ", salary=" + salary + ", worker_id="
+ worker_id + "]";
}
}package dao;
import model.Worker;
import java.sql.*;
import java.util.List;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}package dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
import util.DBConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws SQLException {
connection = DBConnection.getConnection();
}
public int addWorker(Worker worker) throws SQLException {
String insertSql = """
INSERT INTO Worker(
WORKER_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
JOINING_DATE,
DEPARTMENT,
email
)
VALUES
(?,?,?,?,?,?,? )
""";
PreparedStatement ps = connection.prepareStatement(query);
ps.setInt(1, worker.getWorker_id());
ps.setString(2, worker.getFirst_name());
ps.setString(3, worker.getLast_name());
ps.setInt(4, worker.getSalary());
ps.setJoining_date(5, worker.getJoining_date());
ps.setString(6, worker.getDepartment());
ps.setString(7, worker.getEmail());
return ps.executeUpdate();
}
@Override
public void delete(int workerId) throws SQLException {
String query = "DELETE FROM Worker WHERE WORKER_ID = ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, workerId);
int rows = preparedStatement.executeUpdate();
System.out.println(rows);
}
@Override
public Worker getWorker(int workerId) throws SQLException {
String query = "SELECT * FROM WORKER WHERE WORKER_ID=?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, workerId);
ResultSet rs = preparedStatement.executeQuery();
rs.next();
return new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getString(6),
rs.getString(7));
}
@Override
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet resultSet = statement.executeQuery(query);
while (resultSet.next()) {
list.add(new Worker(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getInt(4), resultSet.getDate(5),
resultSet.getString(6),
resultSet.getString(7)));
}
return list;
}
}
@Override
public void update(Worker emp) throws SQLException {
String updateQuery = "UPDATE TABLE Worker SET worker_id =?,first_name = ?,last_name =?,salary =?,joining_date = ?,department = ?,email = ? WHERE worker_id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
preparedStatement.setInt(1, emp.getWorker_id());
preparedStatement.setString(2, emp.getFirst_name());
preparedStatement.setString(3, emp.getLast_name());
preparedStatement.setInt(4, emp.getSalary());
preparedStatement.setDate(5, emp.getJoining_date());
preparedStatement.setString(6, emp.getDepartment());
preparedStatement.setString(7, emp.getEmail());
preparedStatement.setInt(8, emp.getWorker_id());
int rows = preparedStatement.executeUpdate();
System.out.println(rows);
}
}package main;
import java.sql.*;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DBConnection;
public class App {
public static void main(String[] args) throws Exception{
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker = new Worker(10, "Vignesh", "Vishwa",10000 , Date.valueOf("2022-03-21"), "Admin","vignesh.v@gmail.com");
System.out.println(workerDao.add(worker));
workerDao.delete(10);
System.out.println(workerDao.getWorker(1));
List<Worker> list = workerDao.getWorkers();
list.forEach(System.out::println);
Worker obj = new Worker(10, "new", "user", 50000, Date.valueOf("2022-03-21"), "new", "user.new@gmail.in");
workerDao.update(obj);
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
package util; import java.sql.*; 1.public class DatabaseConnect { } 2package model; import java.util.Date; public class Worker { public Worker(int worker_id, String email) { } 3.package dao; import java.sql.SQLException; import model.Worker; public interface WorkerDAO { } 4.package dao; import java.sql.Connection; public class WorkerDAOImplementation implements WorkerDAO { } 5.package main; import util.DatabaseConnection; import dao.WorkerDAO; public class App { |
Beta Was this translation helpful? Give feedback.
0 replies
-
1.package util;
import java.sql.*;
public class DatabaseConnection {
private static final String url = "jdbc:mysql://localhost:3306/myOrg";
private static final String username = "root";
private static final String password = "HanumanJii";
private static Connection connection = null;
public static Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
if (connection == null) {
connection = DriverManager.getConnection(url, username, password);
}
System.out.println(
"Connected to database server " + connection.getMetaData().getDatabaseProductName() + " version "
+ connection.getMetaData().getDatabaseProductVersion());
return connection;
}
public static void closeConnection() throws SQLException
{
if(connection!=null)
connection.close();
}
}
2.package model;
import java.sql.Date;
public class Worker {
private int worker_id;
private String first_name;
private String last_name;
private int salary;
private Date joining_date;
private String department;
private String email;
public Worker(int worker_id, String first_name, String last_name, int salary, Date joining_date, String department,
String email) {
this.worker_id = worker_id;
this.first_name = first_name;
this.last_name = last_name;
this.salary = salary;
this.joining_date = joining_date;
this.department = department;
this.email = email;
}
public int getWorker_id() {
return worker_id;
}
public void setWorker_id(int worker_id) {
this.worker_id = worker_id;
}
public String getFirst_name() {
return first_name;
}
public void setFirst_name(String first_name) {
this.first_name = first_name;
}
public String getLast_name() {
return last_name;
}
public void setLast_name(String last_name) {
this.last_name = last_name;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoining_date() {
return joining_date;
}
public void setJoining_date(Date joining_date) {
this.joining_date = joining_date;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", first_name=" + first_name
+ ", joining_date=" + joining_date + ", last_name=" + last_name + ", salary=" + salary + ", worker_id="
+ worker_id + "]";
}
public Worker(int worker_id, String email) {
this.worker_id = worker_id;
this.email = email;
}
}3.package dao;
import model.Worker;
import java.sql.SQLException;
import java.util.List;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4.package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import util.DatabaseConnection;
import java.sql.Statement;
import model.Worker;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws ClassNotFoundException, SQLException {
this.connection = DatabaseConnection.getConnection();
}
public int add(Worker worker) throws SQLException {
String addSql = """
insert into worker(worker_id,first_name,last_name
,salary,joining_date,department,email) values(?,?,?,?,?,?,?);
""";
PreparedStatement prepStatement = connection.prepareStatement(addSql);
prepStatement.setInt(1, worker.getWorker_id());
prepStatement.setString(2, worker.getFirst_name());
prepStatement.setString(3, worker.getLast_name());
prepStatement.setInt(4, worker.getSalary());
prepStatement.setDate(5, worker.getJoining_date());
prepStatement.setString(6, worker.getDepartment());
prepStatement.setString(7, worker.getEmail());
int rowsInserted = prepStatement.executeUpdate();
System.out.println("Added a row");
return rowsInserted;
}
public void delete(int workerId) throws SQLException {
String deleteSql = "delete from worker where worker_id = ?";
PreparedStatement prepStatement = connection.prepareStatement(deleteSql);
prepStatement.setInt(1, workerId);
int rowsDeleted = prepStatement.executeUpdate();
System.out.println(rowsDeleted + " rows were deleted ");
}
public Worker getWorker(int workerId) throws SQLException {
String getSql = "select * from worker where worker_id = ?";
PreparedStatement ps = connection.prepareStatement(getSql);
ps.setInt(1, workerId);
ResultSet rs = ps.executeQuery();
rs.next();
return new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5), rs.getString(6),
rs.getString(7));
}
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
list.add(new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDate(5),
rs.getString(6),
rs.getString(7)));
}
return list;
}
}
public void update(Worker emp) throws SQLException {
String updateQuery = """
UPDATE Worker SET
worker_id =?,
first_name = ?,
last_name =?,
salary =?,
joining_date = ?,
deapartment = ?,
email = ?
WHERE worker_id = ?""";
PreparedStatement ps = connection.prepareStatement(updateQuery);
ps.setInt(1, emp.getWorker_id());
ps.setString(2, emp.getFirst_name());
ps.setString(3, emp.getLast_name());
ps.setInt(4, emp.getSalary());
ps.setDate(5, emp.getJoining_date());
ps.setString(6, emp.getDepartment());
ps.setString(7, emp.getEmail());
ps.setInt(8, emp.getWorker_id());
int rowsUpdated = ps.executeUpdate();
System.out.println("Rows updated are "+ rowsUpdated);
}
}5.import java.sql.*;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DatabaseConnection;
import java.sql.Date;
public class App {
public static void main(String[] args) throws Exception {
WorkerDAO workerDao = new WorkerDAOImplementation();
// create
Worker worker = new Worker(21);
workerDao.add(worker);
// read
System.out.println(workerDao.getWorker(1));
System.out.println();
List<Worker> listOfWorkers = workerDao.getWorkers();
for (Worker workers : listOfWorkers) {
System.out.println(workers);
}
// update
Worker emp = new Worker(3, "Ujjwal", "Gupta", 70000, Date.valueOf("2022-03-22"), "IT",
"u.gupta@my_myorg.in");
workerDao.update(emp);
// delete
workerDao.delete(20);
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
1 import java.sql.*;
public class DatabaseConnection {
private DatabaseConnection() {}
static final String url = "jdbc:mysql://localhost:3306/org";
static final String username = "ShowBot";
static final String password = "XXXXXXX";
public static Connection getConnection() throws SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection conn = DriverManager.getConnection(url, username, password)) {
System.out.println("Connected to Database server"
+ conn.getMetaData().getDatabaseProductName() + "version"
+ conn.getMetaData().getDatabaseProductVersion());
return conn;
}
catch (SQLException e)
{
e.printStackTrace();
return null;
}
}
}2 import java.sql.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int worker_id) {this.workerId = worker_id;}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorker_id(int workerId) {
this.workerId = workerId;
}
public String getFirstName() {
return firstName;
}
public void setFirst_name(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
1- public class DatabaseConnection { } 2- import java.util.Date; public class Worker { } |
Beta Was this translation helpful? Give feedback.
0 replies
-
import java.sql.*;
public class DatabaseConnection {
static final String url = "jdbc:mysql://localhost:3306/myorg";
static final String username = "root";
static final String password = "password";
public static Connection getConnection() throws ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection connection = DriverManager.getConnection(url, username, password)){
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
return connection;
}
catch(Exception e){
System.out.println(e);
return null;
}
}
}import java.sql.*;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName + ", joiningDate="
+ joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId=" + workerId + "]";
}
}import java.sql.*;
import java.util.*;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
1package util;
import java.sql.*;
import javax.xml.crypto.Data;
public class DatabaseConnection {
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private static final String username = "root";
private static final String password = "root";
private static Connection connection = null;
private DatabaseConnection(){
}
public static Connection getConnection() throws ClassNotFoundException, SQLException{
if(connection == null){
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
}
return connection;
}
public static void closeConnection() throws SQLException{
connection.close();
}
}2package model;
import java.sql.Date;
public class Worker implements Comparable<Worker>{
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Worker(int workerId) {
this.workerId = workerId;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName + ", joiningDate="
+ joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId=" + workerId + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + workerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (workerId != other.workerId)
return false;
return true;
}
@Override
public int compareTo(Worker o) {
return this.workerId - o.workerId;
}
}3package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker worker)
throws SQLException;
}4package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Predicate;
import com.mysql.cj.xdevapi.PreparableStatement;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws ClassNotFoundException, SQLException {
connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String add = "INSERT INTO Worker VALUES (?,?,?,?,?,?,?)";
int rowsEffected = 0;
try (PreparedStatement statement = connection.prepareStatement(add);) {
statement.setInt(1, worker.getWorkerId());
statement.setString(2, worker.getFirstName());
statement.setString(3, worker.getLastName());
statement.setInt(4, worker.getSalary());
statement.setDate(5, worker.getJoiningDate());
statement.setString(6, worker.getDepartment());
statement.setString(7, worker.getEmail());
rowsEffected = statement.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
}
return rowsEffected;
}
@Override
public void delete(int workerId) throws SQLException {
// TODO Auto-generated method stub
String delete = "DELETE FROM worker WHERE worker_id = ?";
try (PreparedStatement statement = connection.prepareStatement(delete)) {
statement.setInt(1, workerId);
int rowsDeleted = statement.executeUpdate();
System.out.println(rowsDeleted + " row deleted");
} catch (SQLException e) {
System.out.println(e);
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
Worker worker = null;
String getData = "SELECT * FROM worker WHERE worker_id = ?";
try (PreparedStatement statement = connection.prepareStatement(getData)) {
statement.setInt(1, workerId);
ResultSet result = statement.executeQuery();
while (result.next()) {
worker = new Worker(result.getInt(1), result.getString(2), result.getString(3), result.getInt(4),
result.getDate(5), result.getString(6), result.getString(7));
}
} catch (SQLException e) {
System.out.println(e);
}
return worker;
}
@Override
public List<Worker> getWorkers() throws SQLException {
List<Worker> workers = new ArrayList<Worker>();
String allData = "SELECT * FROM worker";
try (Statement statement = connection.createStatement()) {
ResultSet result = statement.executeQuery(allData);
while (result.next()) {
workers.add(new Worker(result.getInt(1), result.getString(2), result.getString(3), result.getInt(4),
result.getDate(5), result.getString(6), result.getString(7)));
}
} catch (Exception e) {
System.out.println(e);
}
return workers;
}
@Override
public void update(Worker worker) throws SQLException {
String update = "UPDATE worker SET first_name=?,last_name=?,salary=?,joining_date=?,department=?,email=? where worker_id=?";
try (PreparedStatement statement = connection.prepareStatement(update)) {
statement.setString(1, worker.getFirstName());
statement.setString(2, worker.getLastName());
statement.setInt(3, worker.getSalary());
statement.setDate(4, worker.getJoiningDate());
statement.setString(5, worker.getDepartment());
statement.setString(6, worker.getEmail());
statement.setInt(7, worker.getWorkerId());
int rowsUpdated = statement.executeUpdate();
System.out.println(rowsUpdated + " rows updated");
} catch (SQLException e) {
System.out.println(e);
}
}
}5package main;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import util.DatabaseConnection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
public class App {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker1 = new Worker(9, "Pavan", "Arvapally", 5000, Date.valueOf("2022-03-21"), "Admin","pavan.a@gmail.com");
Worker worker2 = new Worker(10, "Pavan", "Arvapally", 5000, Date.valueOf("2022-03-21"), "Admin","pavan.a@gmail.com");
System.out.println(workerDao.add(worker1));
System.out.println(workerDao.add(worker2));
workerDao.delete(10);
System.out.println(workerDao.getWorker(1));
Worker obj = new Worker(9, "new", "user", 50000, Date.valueOf("2022-03-21"), "new", "user.new@gmail.in");
workerDao.update(obj);
List<Worker> list = workerDao.getWorkers();
list.forEach(System.out::println);
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
1-- Database Connection package Util;
import java.sql.*;;
public class DatabaseConnection {
private static Connection con = null;
static final String url = "jdbc:mysql://localhost:3307/myorg";
static final String user = "root";
static final String pass = "grv123";
public static Connection getConnection() {
if (con == null) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
con = DriverManager.getConnection(url, user, pass);
System.out.println("Connected to database server "
+ con.getMetaData().getDatabaseProductName()
+ " version: "
+ con.getMetaData().getDatabaseProductVersion());
} catch (Exception e) {
e.printStackTrace();
}
}
return con;
}
}2--- worker model package Model;
import java.sql.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private String salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId) {
this.workerId = workerId;
}
public Worker(int workerId, String firstName, String lastName, String salary, Date joiningDate,
String department, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getworkerId() {
return workerId;
}
public void setworkerId(int workerId) {
this.workerId = workerId;
}
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 getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public Date getjoiningDate() {
return joiningDate;
}
public void setjoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}3--Dao Implementation package dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import Model.Worker;
import Util.DatabaseConnection;
public class WorkerDaoImplementation implements WorkerDao {
Connection connection;
public WorkerDaoImplementation() {
this.connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String addQuery = "insert into worker values(?,?,?,?,?,?,?)";
int rowsAffected = 0;
try (PreparedStatement statement = connection.prepareStatement(addQuery)) {
statement.setInt(1, worker.getworkerId());
statement.setString(2, worker.getfirstName());
statement.setString(3, worker.getlastName());
statement.setString(4, worker.getSalary());
statement.setDate(5, worker.getjoiningDate());
statement.setString(6, worker.getDepartment());
statement.setString(7, worker.getEmail());
rowsAffected = statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return rowsAffected;
}
@Override
public void delete(int workerId) throws SQLException {
String delQuery = "delete from worker where WORKER_ID=?";
try (PreparedStatement statement = connection.prepareStatement(delQuery)) {
statement.setInt(1, workerId);
int rows = statement.executeUpdate();
System.out.println(rows + "worker deleted");
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
Worker worker = null;
String getQuery = "select * from worker where WORKER_ID=?";
try (PreparedStatement statement = connection.prepareStatement(getQuery)) {
statement.setInt(1, workerId);
ResultSet rs = statement.executeQuery();
while (rs.next()) {
worker = new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getDate(5),
rs.getString(6), rs.getString(7));
}
} catch (SQLException e) {
e.printStackTrace();
}
return worker;
}
@Override
public List<Worker> getWorkers() throws SQLException {
List<Worker> list = new ArrayList<>();
String getQuery = "select * from worker";
try (Statement statement = connection.createStatement()) {
ResultSet rs = statement.executeQuery(getQuery);
while (rs.next()) {
list.add(new Worker(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getDate(5),
rs.getString(6), rs.getString(7)));
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Override
public void update(Worker emp) throws SQLException {
String updateQuery = "update worker set first_name=?,last_name=?,salary=?,joining_date=?,department=?,email=? where worker_id=?";
try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
statement.setString(1, emp.getfirstName());
statement.setString(2, emp.getlastName());
statement.setString(3, emp.getSalary());
statement.setDate(4, emp.getjoiningDate());
statement.setString(5, emp.getDepartment());
statement.setString(6, emp.getEmail());
statement.setInt(7, emp.getworkerId());
int rows = statement.executeUpdate();
System.out.println(rows + " worker updated");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
4-- App.java import java.sql.*;
import java.util.Arrays;
import java.util.List;
import Model.Worker;
import dao.*;
public class App {
public static void main(String[] args) throws Exception {
WorkerDao dao = new WorkerDaoImplementation();
int rows = dao.add(new Worker(10));
System.out.println(rows);
List<Worker> ls = dao.getWorkers();
ls.forEach(System.out::println);
dao.delete(9);
List<Worker> ls = dao.getWorkers();
ls.forEach(System.out::println);
System.out.println(dao.getWorker(1));
Worker worker = new Worker(1);
dao.update(worker);
System.out.println(dao.getWorker(1));
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
package util; import java.sql.Connection; public class DatabaseConnection { } package model; import java.sql.*; } import java.sql.SQLException; import model.Worker; public interface WorkerDAO { } |
Beta Was this translation helpful? Give feedback.
0 replies
-
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
static final String url = "jdbc:mysql://localhost:3306/myOrg";
static final String username = "root";
static final String password = "abhiraj2k2000";
static Connection connection;
private DatabaseConnection() {
}
public static Connection getConnection() throws SQLException, ClassNotFoundException {
if (connection != null) {
return connection;
}
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to Database");
return connection;
}
public static void closeConnection() throws SQLException {
if (connection != null) {
connection.close();
}
}
}package model;
import java.util.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private String salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String email) {
this.workerId = workerId;
this.email = email;
this.joiningDate = new Date();
}
public Worker(int workerId, String firstName, String lastName, String salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "FIRSTNAME is " + firstName + "\n"
+ "LAST NAME is " + lastName + "\n" + "JOINING DATE is " + joiningDate + "\n"
+ "WORKER ID is " + workerId + "\n" + "DEPARTMENT is " + department + "\n" + "EMAIL is " + email
+ "\n" + "SALARY is " + salary + "\n";
}
}package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker) throws SQLException;
public void delete(int workerId) throws SQLException;
public Worker getWorker(int workerId) throws SQLException;
public List<Worker> getWorkers() throws SQLException;
public void update(Worker emp) throws SQLException;
}package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
// import java.text.ParseException;
// import java.text.SimpleDateFormat;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws SQLException, ClassNotFoundException {
this.connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String sql = """
INSERT INTO worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT, EMAIL)
VALUES (?,?,?,?,?,?,?)""";
try (PreparedStatement ps = this.connection.prepareStatement(sql);) {
ps.setInt(1, worker.getWorkerId());
ps.setString(2, worker.getFirstName());
ps.setString(3, worker.getLastName());
ps.setString(4, worker.getSalary());
ps.setObject(5, worker.getJoiningDate());
ps.setString(6, worker.getDepartment());
ps.setString(7, worker.getEmail());
return ps.executeUpdate();
} catch (SQLException e) {
throw e;
}
}
@Override
public void delete(int workerId) throws SQLException {
String sql = """
DELETE FROM worker
WHERE
WORKER_ID = ? """;
try (PreparedStatement ps = this.connection.prepareStatement(sql);) {
ps.setInt(1, workerId);
ps.executeUpdate();
} catch (SQLException e) {
throw e;
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
String sql = "SELECT * FROM worker WHERE WORKER_ID = ?";
try (PreparedStatement ps = this.connection.prepareStatement(sql);) {
ps.setInt(1, workerId);
ResultSet rs = ps.executeQuery();
if (!rs.isBeforeFirst()) {
throw new SQLException("RECORD NOT FOUND");
}
// Date d = new SimpleDateFormat("dd/MM/yyyy").parse((String)
// rs.getObject("JOINING_DATE"));
// Not implemented Date
rs.next();
return this.getWorkerFromParams(rs.getInt("WORKER_ID"), rs.getString("FIRST_NAME"),
rs.getString("LAST_NAME"),
rs.getString("SALARY"), new Date(), rs.getString("DEPARTMENT"),
rs.getString("EMAIL"));
} catch (SQLException e) {
throw e;
}
}
@Override
public List<Worker> getWorkers() throws SQLException {
String sql = "SELECT * FROM worker";
try (PreparedStatement ps = this.connection.prepareStatement(sql)) {
List<Worker> workerList = new ArrayList();
ResultSet rs = ps.executeQuery();
while (rs.next()) {
workerList.add(this.getWorkerFromParams(rs.getInt("WORKER_ID"), rs.getString("FIRST_NAME"),
rs.getString("LAST_NAME"),
rs.getString("SALARY"), new Date(), rs.getString("DEPARTMENT"),
rs.getString("EMAIL")));
}
return workerList;
} catch (SQLException e) {
throw e;
}
}
@Override
public void update(Worker emp) throws SQLException {
String sql = """
UPDATE worker
SET
FIRST_NAME = ?,
LAST_NAME = ?,
SALARY = ?,
JOINING_DATE = ?,
DEPARTMENT = ?,
EMAIL = ?
WHERE
WORKER_ID = ?""";
try (PreparedStatement ps = this.connection.prepareStatement(sql);) {
ps.setString(1, emp.getFirstName());
ps.setString(2, emp.getLastName());
ps.setString(3, emp.getSalary());
ps.setObject(4, new Date());
ps.setString(5, emp.getDepartment());
ps.setString(6, emp.getEmail());
ps.setInt(7, emp.getWorkerId());
ps.executeUpdate();
} catch (SQLException e) {
throw e;
}
}
private Worker getWorkerFromParams(int workerId, String firstName, String lastName, String salary,
Date joiningDate, String department, String email) {
return new Worker(workerId, firstName, lastName, salary, joiningDate, department, email);
}
}package main;
import java.util.ArrayList;
import java.util.List;
import dao.WorkerDAOImplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
WorkerDAOImplementation wDao = new WorkerDAOImplementation();
List<Worker> workerList = wDao.getWorkers();
workerList.forEach(System.out::println);
Worker worker = wDao.getWorker(1);
System.out.println(worker);
worker.setWorkerId(2);
wDao.update(worker);
System.out.println(wDao.getWorker(2));
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Subtask 1: package util;
import java.sql.*;
import java.util.Date;
public class DatabaseConnection{
private static final String USERNAME = "root";
private static final String PASSWORD = "admin";
private static final String URL = "jdbc:mysql://localhost:3307/my_org";
private static Connection connection;
private DatabaseConnection() {
}
public static Connection getConnection() throws SQLException {
if (connection == null) {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
}
return connection;
}
public static void close() throws SQLException {
if (connection != null)
connection.close();
}
}Subtask 2: package model;
import java.sql.*;
import java.util.Date;
public class Worker {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String lastName, int salary, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = new Date();
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
this.joiningDate = new Date();
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getworkerId() {
return workerId;
}
public void setworkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getjoiningDate() {
return joiningDate;
}
public void setjoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}Subtask 3: package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}Subtask 4: package dao;
import util.DatabaseConnection;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws SQLException {
this.connection = DatabaseConnection.getConnection();
}
public int add(Worker worker) throws SQLException {
int worker_id = worker.getworkerId();
String first_name = worker.getfirstName();
String last_name = worker.getlastName();
int salary = worker.getSalary();
Date date = (Date) worker.getjoiningDate();
String department = worker.getDepartment();
String email = worker.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joining_date = sdf.format(date);
String query = String.format("INSERT INTO worker VALUES(%d,'%s','%s',%d,'%s','%s','%s');", worker_id,
first_name,
last_name, salary, joining_date, department, email);
try (Statement statement = connection.createStatement()) {
return statement.executeUpdate(query);
}
}
public void delete(int workerId) throws SQLException {
String query = String.format("DELETE FROM worker WHERE worker_id=%d", workerId);
try (Statement statement = connection.createStatement()) {
statement.executeUpdate(query);
}
}
public Worker getWorker(int workerId) throws SQLException {
String query = "SELECT * FROM worker WHERE worker_id=?";
Worker res = null;
try (PreparedStatement ps = connection.prepareStatement(query);) {
ps.setInt(1, workerId);
ResultSet result = ps.executeQuery();
while (result.next()) {
int workerId1 = result.getInt("worker_id");
String firstName = result.getString("first_name");
String lastName = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
String email = result.getString("email");
res = new Worker(workerId1, firstName, lastName, salary, date, department, email);
}
}
return res;
}
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet result = statement.executeQuery(query);
while (result.next()) {
int worker_id = result.getInt("worker_id");
String first_name = result.getString("first_name");
String last_name = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
String email = result.getString("email");
list.add(new Worker(worker_id, first_name, last_name, salary, date, department, email));
}
}
return list;
}
public void update(Worker emp) throws SQLException {
int workerId = emp.getworkerId();
String firstName = emp.getfirstName();
String lastName = emp.getlastName();
int salary = emp.getSalary();
Date date = emp.getjoiningDate();
String department = emp.getDepartment();
String email = emp.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joiningDate = sdf.format(date);
String query = String.format("""
UPDATE worker SET
worker_id =%d,
first_name = '%s',
last_name ='%s',
salary =%d,
joining_date = '%s',
department = '%s',
email = '%s'
WHERE worker_id = %d """, workerId,
firstName,
lastName, salary, joiningDate, department, email, workerId);
try (Statement statement = connection.createStatement()) {
int res = statement.executeUpdate(query);
System.out.println(res + " row get Updated");
}
}
}Subtask 5: package main;
import util.DatabaseConnection;
import java.sql.*;
import java.util.List;
import java.util.Date;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker = new Worker(9,"rohit","rohit@my_org.in");
System.out.println(workerDao.add(worker));
workerDao.delete(9);
List<Worker> list = workerDao.getWorkers();
for (Worker workers : list) {
System.out.println(workers);
}
DatabaseConnection.close();
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Beta Was this translation helpful? Give feedback.
0 replies
-
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection{
private static final String URL = "jdbc:mysql://localhost:3307/my_org";
private static final String USER = "root";
private static final String PASS = "password";
private static Connection conn = null;
private DatabaseConnection() {
}
public static Connection getConnection() throws SQLException {
if(conn == null) {
conn = DriverManager.getConnection(URL,USER,PASS);
System.out.println("Connected to database server "
+ conn.getMetaData().getDatabaseProductName()
+ " version: "
+ conn.getMetaData().getDatabaseProductVersion());
}
return conn;
}
public static void closeConnection() throws SQLException {
if(conn != null)
conn.close();
}
}
package model;
import java.util.Date;
public class Worker implements Comparable<Worker> {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
@Override
public int compareTo(Worker other) {
return workerId - other.workerId;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((email == null) ? 0 : email.hashCode()) + workerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email) || workerId != other.workerId)
return false;
return true;
}
public Worker(int workerId, String firstName, String lastName, int salary, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = new Date();
this.department = department;
this.email = email;
}
public Worker(int workerId, String firstName, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.email = email;
this.joiningDate = new Date();
}
public Worker(int workerId, String firstName, Date joiningDate, String email) {
this.workerId = workerId;
this.firstName = firstName;
this.joiningDate = joiningDate;
this.email = email;
}
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", workerId="
+ workerId + "]";
}
}
package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
// CRUD method headers
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker worker)
throws SQLException;
}
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO{
Connection conn;
public WorkerDAOImplementation() throws SQLException {
conn = DatabaseConnection.getConnection();
}
@Override
public int add(Worker w) throws SQLException {
int workerId = w.getWorkerId();
String firstName = w.getFirstName();
String lastName = w.getLastName();
int salary = w.getSalary();
Date date = w.getJoiningDate();
String department = w.getDepartment();
String email = w.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String joiningDate = sdf.format(date);
String sql = String.format("INSERT INTO worker VALUES(%d,'%s','%s',%d,'%s','%s','%s');",
workerId,
firstName,
lastName,
salary,
joiningDate,
department,
email);
try (Statement statement = conn.createStatement()) {
return statement.executeUpdate(sql);
}
}
@Override
public void delete(int workerId) throws SQLException {
String sql = "DELETE FROM Worker WHERE WORKER_ID = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, workerId);
int res = ps.executeUpdate();
System.out.println(res + " row/s affected.");
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
String sql = "SELECT worker_id,first_name,last_name,salary,joining_date,department,email FROM worker WHERE worker_id = ?";
Worker wkr = null;
try (PreparedStatement ps = conn.prepareStatement(sql);) {
ps.setInt(1, workerId);
ResultSet r = ps.executeQuery();
while (r.next()) {
wkr = new Worker(r.getInt("worker_id"),r.getString("first_name"), r.getString("last_name"), r.getInt("salary"), r.getDate("joining_date"),r.getString("department"), r.getString("email"));
}
}
return wkr;
}
@Override
public List<Worker> getWorkers() throws SQLException {
String sql = "SELECT worker_id,first_name,last_name,salary,joining_date,department,email FROM worker";
try(Statement st = conn.createStatement()){
List<Worker> wkr = new ArrayList<>();
ResultSet rs = st.executeQuery(sql);
while(rs.next()) {
wkr.add(new Worker(rs.getInt("worker_id"),rs.getString("first_name"),rs.getString("last_name"),
rs.getInt("salary"),rs.getDate("joining_date"),rs.getString("department"),rs.getString("email")));
}
return wkr;
}
}
@Override
public void update(Worker w) throws SQLException {
int workerId = w.getWorkerId();
String firstName = w.getFirstName();
String lastName = w.getLastName();
int salary = w.getSalary();
Date date = w.getJoiningDate();
String department = w.getDepartment();
String email = w.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String joiningDate = sdf.format(date);
String query = String.format("""
UPDATE worker SET worker_id =%d, first_name = '%s', last_name ='%s', salary =%d, joining_date = '%s', department = '%s',
email = '%s' WHERE worker_id = %d """,
workerId,
firstName,
lastName,
salary,
joiningDate,
department,
email,
workerId);
try (Statement statement = conn.createStatement()) {
int res = statement.executeUpdate(query);
System.out.println(res + " row get affected");
}
}
}
package main;
import util.DatabaseConnection;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
import java.util.Date;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
/**
* Adding in table
*/
System.out.println(" ----- ADDING IN TABLE -----\n");
Worker worker = new Worker(21, "Ashwesh", "a.god@my_org.in");
System.out.println(workerDao.add(worker) + " row is affected.");
/**
* Update in table
*/
System.out.println(" ----- UPDATING IN TABLE -----\n");
Worker wkr = new Worker(21, "OnePunchMan", "OPM", 420609, new Date(), "Account",
"a.god@my_org.in");
workerDao.update(wkr);
/**
* Get one worker details
*/
System.out.println(" ----- GET IN TABLE -----\n");
System.out.println(workerDao.getWorker(21));
/**
* Get workers details
*/
System.out.println(" ----- GET ALL TABLE -----\n");
List<Worker> list = workerDao.getWorkers();
for (Worker workers : list) {
System.out.println(workers);
}
/**
* Delete a worker
*/
System.out.println(" ----- DELETE IN TABLE -----\n");
workerDao.delete(21);
DatabaseConnection.closeConnection();
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
Beta Was this translation helpful? Give feedback.
0 replies
-
1package util;
import java.sql.*;
public class DatabaseConnection {
private static final String DB_URL = "jdbc:mysql://localhost:3307/my_org";
private static final String USER = "root";
private static final String PASS = "123654";
private DatabaseConnection() {
}
public static Connection getConnection() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected to database server " + conn.getMetaData().getDatabaseProductName() + " version: "
+ conn.getMetaData().getDatabaseProductVersion());
return conn;
}
}2package model;
import java.sql.Date;
public class Worker implements Comparable<Worker> {
private int workerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int workerId, String firstName, String lastName, int salary, Date joiningDate, String department,
String email) {
this.workerId = workerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public Worker(int workerId, String email) {
this.workerId = workerId;
this.email = email;
long millis = System.currentTimeMillis();
this.joiningDate = new java.sql.Date(millis);
}
public int getWorkerId() {
return workerId;
}
public void setWorkerId(int workerId) {
this.workerId = workerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getJoiningDate() {
return joiningDate;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "Worker [workerId = " + workerId + "\t firstName = " + firstName + "\t lastName = " + lastName
+ "\t joiningDate = " + joiningDate + "\t salary = " + salary + "\t department = " + department
+ "\t email = " + email + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((email == null) ? 0 : email.hashCode());
result = prime * result + workerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email))
return false;
if (workerId != other.workerId)
return false;
return true;
}
@Override
public int compareTo(Worker obj) {
return this.workerId - obj.workerId;
}
}3package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public int delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}4package dao;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection conn;
public WorkerDAOImplementation() throws Exception {
this.conn = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
String insertQuery = "INSERT INTO Worker VALUES(?,?,?,?,?,?,? )";
PreparedStatement pstmt = conn.prepareStatement(insertQuery);
pstmt.setInt(1, worker.getWorkerId());
pstmt.setString(2, worker.getFirstName());
pstmt.setString(3, worker.getLastName());
pstmt.setInt(4, worker.getSalary());
pstmt.setDate(5, worker.getJoiningDate());
pstmt.setString(6, worker.getDepartment());
pstmt.setString(7, worker.getEmail());
return pstmt.executeUpdate();
}
@Override
public int delete(int workerId) throws SQLException {
String deleteQuery = "DELETE FROM worker WHERE worker_id = " + workerId;
try (Statement statement = conn.createStatement()) {
return statement.executeUpdate(deleteQuery);
}
}
@Override
public Worker getWorker(int workerId) throws SQLException {
return new Worker(workerId, "xd");
}
@Override
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> list = new ArrayList<>();
try (Statement statement = conn.createStatement()) {
ResultSet result = statement.executeQuery(query);
while (result.next()) {
int workerId = result.getInt("worker_id");
String firstName = result.getString("first_name");
String lastName = result.getString("last_name");
int salary = result.getInt("salary");
Date date = result.getDate("joining_date");
String department = result.getString("department");
String email = result.getString("email");
list.add(new Worker(workerId, firstName, lastName, salary, date, department, email));
}
}
return list;
}
@Override
public void update(Worker emp) throws SQLException {
String updateQuery = """
UPDATE TABLE Worker SET
worker_id =?,
first_name = ?,
last_name =?,
salary =?,
joining_date = ?,
deapartment = ?,
email = ?
WHERE worker_id = ?""";
PreparedStatement pstmt = conn.prepareStatement(updateQuery);
pstmt.setInt(1, emp.getWorkerId());
pstmt.setString(2, emp.getFirstName());
pstmt.setString(3, emp.getLastName());
pstmt.setInt(4, emp.getSalary());
pstmt.setDate(5, emp.getJoiningDate());
pstmt.setString(6, emp.getDepartment());
pstmt.setString(7, emp.getEmail());
pstmt.setInt(8, emp.getWorkerId());
pstmt.executeUpdate();
}
}5import java.sql.*;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker = new Worker(9, "Rin", "Okumura", 50000, Date.valueOf("2022-03-21"), "new",
"r.okumara@my_org.in");
System.out.println(workerDao.add(worker));
List<Worker> list = workerDao.getWorkers();
for (Worker workers : list) {
System.out.println(workers);
}
workerDao.delete(9);
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
-
**1.**
package util;
import java.sql.*;
public class DatabaseConnection {
private static Connection connection = null;
private static final String url = "jdbc:mysql://localhost:3306/my_org";
private static final String username = "root";
private static final String password = "********";
public static Connection getConnection() {
if (connection == null) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to database server "
+ connection.getMetaData().getDatabaseProductName()
+ " version: "
+ connection.getMetaData().getDatabaseProductVersion());
} catch (Exception e) {
e.printStackTrace();
}
}
return connection;
}
}
**2**
package model;
import java.sql.Date;
public class Worker implements Comparable<Worker> {
private int WorkerId;
private String firstName;
private String lastName;
private int salary;
private Date joiningDate;
private String department;
private String email;
public Worker(int WorkerId, String firstName, String email) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.email = email;
}
public Worker(int WorkerId, String firstName, String email, Date joiningDate) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.email = email;
this.joiningDate = joiningDate;
}
public Worker(int WorkerId, String firstName, String lastName, int salary, String department, String email) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.department = department;
this.email = email;
}
public Worker(int WorkerId, String firstName, String lastName, int salary, Date joiningDate,
String department, String email) {
this.WorkerId = WorkerId;
this.firstName = firstName;
this.lastName = lastName;
this.salary = salary;
this.joiningDate = joiningDate;
this.department = department;
this.email = email;
}
public int getWorkerId() {
return WorkerId;
}
public void setWorkerId(int WorkerId) {
this.WorkerId = WorkerId;
}
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 int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Date getjoiningDate() {
return joiningDate;
}
public void setjoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + WorkerId;
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Worker other = (Worker) obj;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email))
return false;
if (WorkerId != other.WorkerId)
return false;
return true;
}
@Override
public String toString() {
return "Worker [department=" + department + ", email=" + email + ", firstName=" + firstName
+ ", joiningDate=" + joiningDate + ", lastName=" + lastName + ", salary=" + salary + ", WorkerId="
+ WorkerId + "]";
}
@Override
public int compareTo(Worker w) {
return this.getWorkerId() - w.getWorkerId();
}
}
**3**
package dao;
import java.sql.SQLException;
import java.util.List;
import model.Worker;
public interface WorkerDAO {
public int add(Worker worker)
throws SQLException;
public void delete(int workerId)
throws SQLException;
public Worker getWorker(int workerId)
throws SQLException;
public List<Worker> getWorkers()
throws SQLException;
public void update(Worker emp)
throws SQLException;
}
**4**
package dao;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import model.Worker;
import util.DatabaseConnection;
public class WorkerDAOImplementation implements WorkerDAO {
Connection connection;
public WorkerDAOImplementation() throws SQLException {
this.connection = DatabaseConnection.getConnection();
}
@Override
public int add(Worker worker) throws SQLException {
int workerId = worker.getWorkerId();
String firstName = worker.getfirstName();
String lastName = worker.getlastName();
int salary = worker.getSalary();
Date date = worker.getjoiningDate();
String department = worker.getDepartment();
String email = worker.getEmail();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String joiningDate = sdf.format(date);
String query1 = "INSERT INTO worker VALUES( " + workerId + firstName + lastName + salary + joiningDate
+ department + email + ")";
try (Statement statement = connection.createStatement()) {
return statement.executeUpdate(query1);
}
}
@Override
public void delete(int workerId) throws SQLException {
String query2 = "DELETE FROM worker WHERE worker_id = " + workerId;
try (Statement statement = connection.createStatement()) {
int rowDeleted = statement.executeUpdate(query2);
System.out.println(rowDeleted + " row deleted");
}
}
public Worker getWorker(int workerId) throws SQLException {
Worker w = null;
String query3 = "select * from worker where WORKER_ID=?";
try (PreparedStatement statement = connection.prepareStatement(query3)) {
statement.setInt(1, workerId);
ResultSet res = statement.executeQuery();
while (res.next()) {
w = new Worker(res.getInt(1), res.getString(2), res.getString(3), res.getInt(4), res.getDate(5),
res.getString(6), res.getString(7));
}
} catch (SQLException e) {
e.printStackTrace();
}
return w;
}
@Override
public List<Worker> getWorkers() throws SQLException {
String query = "SELECT * FROM worker";
List<Worker> workerList = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
ResultSet res = statement.executeQuery(query);
while (res.next()) {
int workerId = res.getInt("worker_id");
String firstName = res.getString("first_name");
String lastName = res.getString("last_name");
int salary = res.getInt("salary");
Date date = res.getDate("joining_date");
String department = res.getString("department");
String email = res.getString("email");
workerList.add(new Worker(workerId, firstName, lastName, salary, date, department, email));
}
}
return workerList;
}
@Override
public void update(Worker worker) throws SQLException {
String updateQuery = "update worker set first_name=?,last_name=?,salary=?,joining_date=?,department=?,email=? where worker_id=?";
try (PreparedStatement statement = connection.prepareStatement(updateQuery)) {
statement.setString(1, worker.getfirstName());
statement.setString(2, worker.getlastName());
statement.setInt(3, worker.getSalary());
statement.setDate(4, worker.getjoiningDate());
statement.setString(5, worker.getDepartment());
statement.setString(6, worker.getEmail());
statement.setInt(7, worker.getWorkerId());
int rows = statement.executeUpdate();
System.out.println(rows + " worker table updated");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
**5**
package main;
import java.util.List;
import dao.WorkerDAO;
import dao.WorkerDAOImplementation;
import model.Worker;
public class App {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
WorkerDAO workerDao = new WorkerDAOImplementation();
Worker worker1 = new Worker(10, "Divika", "d.agrawal@my_org.in");
System.out.println(workerDao.add(worker1));
workerDao.delete(10);
List<Worker> list = workerDao.getWorkers();
list.forEach(System.out::println);
System.out.println(workerDao.getWorker(2));
Worker worker = new Worker(1, "Anmol", "a.goel@my_org.in");
workerDao.update(worker);
System.out.println(workerDao.getWorker(1));
}
} |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
and others
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
-
JDBC Task - 2
Continuation of JDBC Task-1 (Discussion #58)
DB Schema
(To be updated)
Project Structure
(Note - Ignore the
com.jdbc.in the package naming for now)Operations to Perform using JDBC
Create a JDBC application to establish a connection with the
my_orgdatabase. Define a utility class calledDatabaseConnectionin theutilpackage undersrc, which hasurl,username, andpasswordfinal data members. Using these properties, get aConnectioninstance by calling a static function namedgetConnectionof the utility class. It should also print the details of the DB connection whenever a new connection is established. Also, include a static methodcloseConnectionwhich will act as a wrapper forconnection.close().The connection logic should conform to the Singleton design pattern, i.e., only one active connection instance will exist at a time and will be reused by all trying to perform DB operation. (Hint - use static data member and private constructor)
Create a model class named
Workerin themodelpackage under thesrcfolder. It should contain data members for each column of theWorkertable. The names and types of the data members should match those of the columns in the table. Include public getters and setters as well. Override thetoString()method to print the details of a worker. The non-nullable fields of theWorkertable become the important/mandatory fields of theWorkerPOJO class. Therefore, you must include two parameterized constructors, one with the mandatory fields only, and another with all the fields. Additionally, implement theComparableinterface and override thecompareTo()method based on the important fields.Define an interface
WorkerDAOindaoundersrcas follows:Implement the above interface in a concrete class called
WorkerDAOImplementationin thedaopackage under thesrcfolder, and override the abstract methods to perform CRUD operation on theWorkertable. Use theDatabaseConnectionclass defined in subtask (1) inside the constructor to obtain aConnectioninstance.Create the driver class
Appin the packagemainundersrc. Demonstrate all the CRUD operations on theWorkertable. Include the lineWorkerDAO workerDao = new WorkerDAOImplementation();Note
Beta Was this translation helpful? Give feedback.
All reactions