public static ArrayList<Client> getSimilarNames(String namePart)
throws ClassNotFoundException, SQLException {
try {
readWriteLock.readLock().lock();
Connection conn = DBConnection.getDBConnection().getConnection();
String sql = "Select * From client where ClientName like '" + namePart + "%' order by NIC ";
ResultSet rst = DBHandler.getData(conn, sql);
ArrayList<Client> clientList = new ArrayList<>();
while (rst.next()) {
int marriedStatus = 1;
boolean aBoolean = rst.getBoolean("MarriedStatus");
if (!aBoolean) {
marriedStatus = 0;
}
Client client =
new Client(
rst.getInt("regNo"),
rst.getString("NIC"),
rst.getString("ClientName"),
rst.getString("Birthday"),
rst.getString("Telephone"),
rst.getString("Address"),
rst.getDouble("AnnualIncome"),
rst.getInt("GrantOwnershipPosition"),
rst.getInt("PermitOwnershipPosition"),
marriedStatus,
rst.getInt("NumberOfMarriedSons"),
rst.getInt("NumberOfUnmarriedSons"));
clientList.add(client);
}
return clientList;
} finally {
readWriteLock.readLock().unlock();
}
}
public static Client searchClient(String NIC) throws ClassNotFoundException, SQLException {
try {
readWriteLock.readLock().lock();
Connection conn = DBConnection.getDBConnection().getConnection();
String sql = "Select * from Client where NIC='" + NIC + "'";
ResultSet rst = DBHandler.getData(conn, sql);
if (rst.next()) {
int marriedStatus = 1;
boolean aBoolean = rst.getBoolean("MarriedStatus");
if (!aBoolean) {
marriedStatus = 0;
}
Client client =
new Client(
rst.getInt("RegNo"),
rst.getString("NIC"),
rst.getString("ClientName"),
rst.getString("Birthday"),
rst.getString("Telephone"),
rst.getString("Address"),
rst.getDouble("AnnualIncome"),
rst.getInt("GrantOwnershipPosition"),
rst.getInt("PermitOwnershipPosition"),
marriedStatus,
rst.getInt("NumberOfMarriedSons"),
rst.getInt("NumberOfUnmarriedSons"));
return client;
} else {
return null;
}
} finally {
readWriteLock.readLock().unlock();
}
}
public static int updateClient(Client client) throws ClassNotFoundException, SQLException {
try {
readWriteLock.writeLock().lock();
Connection conn = DBConnection.getDBConnection().getConnection();
String sql =
"Update Client Set clientName='"
+ client.getClientName()
+ "', Birthday='"
+ client.getBirthday()
+ "',Telephone='"
+ client.getTelephone()
+ "', Address='"
+ client.getAddress()
+ "', AnnualIncome='"
+ client.getAnnualIncome()
+ "', GrantOwnershipPosition='"
+ client.getGrantOwnershipPosition()
+ "',PermitOwnershipPosition='"
+ client.getPermitOwnershipPosition()
+ "',MarriedStatus='"
+ client.isMarried()
+ "',NumberOfMarriedSons='"
+ client.getNumberOfMarriedSons()
+ "',NumberOfUnmarriedSons='"
+ client.getNumberOfUnmarriedSons()
+ "' Where NIC ='"
+ client.getNIC()
+ "'";
int res = DBHandler.setData(conn, sql);
return res;
} finally {
readWriteLock.writeLock().unlock();
}
}
public static boolean DeleteNominatedSuccessor(String nic)
throws ClassNotFoundException, SQLException {
Connection conn = DBConnection.getDBConnection().getConnection();
String sql = "Delete from nominatedSuccessor where NIC_S = '" + nic + "'";
int returnDelete = DBHandler.setData(conn, sql);
return returnDelete > 0;
}
public static boolean updateNiminateSuccessor(NominatedSuccessor nominateSuccessor)
throws ClassNotFoundException, SQLException {
Connection conn = DBConnection.getDBConnection().getConnection();
String sql =
"Update nominatedsuccessor Set Name='"
+ nominateSuccessor.getName()
+ "', Address='"
+ nominateSuccessor.getAddress()
+ "' Where NIC_S ='"
+ nominateSuccessor.getNIC_S()
+ "'";
int res = DBHandler.setData(conn, sql);
return res > 0;
}
public static NominatedSuccessor searchNominateSuccessor(String NICS)
throws ClassNotFoundException, SQLException {
Connection conn = DBConnection.getDBConnection().getConnection();
String sql = "Select * from NominatedSuccessor where NIC_S='" + NICS + "'";
ResultSet rst = DBHandler.getData(conn, sql);
if (rst.next()) {
NominatedSuccessor nominateSuccessor =
new NominatedSuccessor(
rst.getString("NIC_S"), rst.getString("Name"), rst.getString("Address"));
return nominateSuccessor;
} else {
return null;
}
}
public static boolean addNewNominateSuccessor(NominatedSuccessor NOS)
throws ClassNotFoundException, SQLException {
Connection conn = DBConnection.getDBConnection().getConnection();
String sql =
"Insert into nominatedsuccessor Values('"
+ NOS.getName()
+ "','"
+ NOS.getNIC_S()
+ "','"
+ NOS.getAddress()
+ "')";
int returnValue = DBHandler.setData(conn, sql);
return returnValue > 0;
}
public static int getnextOwnershiPositionGrant(String grantNumber)
throws ClassNotFoundException, SQLException {
try {
readWriteLock.readLock().lock();
Connection conn = DBConnection.getDBConnection().getConnection();
String sql =
"Select GrantOwnershipPosition From Client natural join grant1 where grantNumber ='"
+ grantNumber
+ "' order by GrantOwnershipPosition Desc limit 1";
ResultSet rst = DBHandler.getData(conn, sql);
int position = 1;
if (rst.next()) {
position = rst.getInt("GrantOwnershipPosition") + 1;
}
return position;
} finally {
readWriteLock.readLock().unlock();
}
}
public static boolean addNewClient(Client client) throws ClassNotFoundException, SQLException {
try {
readWriteLock.writeLock().lock();
Connection conn = DBConnection.getDBConnection().getConnection();
String sql =
"Insert into Client Values('"
+ client.getRegNo()
+ "','"
+ client.getNIC()
+ "','"
+ client.getClientName()
+ "','"
+ client.getBirthday()
+ "','"
+ client.getTelephone()
+ "','"
+ client.getAddress()
+ "','"
+ client.getAnnualIncome()
+ "','"
+ client.getGrantOwnershipPosition()
+ "','"
+ client.getPermitOwnershipPosition()
+ "','"
+ client.isMarried()
+ "','"
+ client.getNumberOfMarriedSons()
+ "','"
+ client.getNumberOfUnmarriedSons()
+ "')";
int returnValue = DBHandler.setData(conn, sql);
return returnValue > 0;
} finally {
readWriteLock.writeLock().unlock();
}
}
