Data Access and Manipulation Using JDBC

The following code describes Data Access and Manipulation Using JDBC.

To begin with, first, have a look at the problem statement here.

https://www.programmingempire.com/a-mini-project-on-jdbc-operation/

https://www.programmingempire.com/jdbc-operations—insert-records-and-display/

Also, the Database Connectivity class DBUtil and the UserBean class are given here.

https://www.programmingempire.com/implementing-database-operations-using-jdbc/

So, we begin with creating the ExamDAO class. Basically, ExamDAO class provides database access. Hence, here we use statements for running queries. The given problem specifies the total 11 methods. So, we need to implement these 11 methods in the ExamDAO class.

In fact, the constructor creates the Connection object. The following codemonstrates it.

Define the Constructor

        public ExamsDAO()
	{
		con=DBUtil.getDBConnection();
		
	}

Get User Type

Basically, this function return the user type for a specific user id.

public String getUserType(String userID)
	{
		String type="";
		try {
			String query="select * from exam";
			pst=con.prepareStatement(query);			
			ResultSet rs=pst.executeQuery();			
			while(rs.next())
			{
				String id=rs.getString(1);
				if(id.equals(userID))
				{
					type=rs.getString(3);
					break;
				}	
				
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		return type;
	}

Get Incorrect Attempts

Similarly, we can get a specific message for number of incorrect attempts for a specific user id. The following code shows it.

public String getIncorrectAttempts(String userID)
	{
		int att=0;
		String msg="";
		try {
			String query="select * from exam where user_id=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  userID);
			rs=pst.executeQuery();
			if(rs.next())
			{
				att=rs.getInt(4);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		if(att==0)
			msg="No Incorrect Attempt";
		else
			if(att==1)
				msg="One Time";
			else
				msg="Incorrect Attempt Exceeded";
		return msg;
	}

Changing the User Type

Likewise, we can change the user type to Admin.

public String changeUserType(String userID)
	{
		int res=0;
		String msg="";
		try {
			String query="Update exam set user_type='Admin' where user_id=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  userID);
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Update Success";
			}
			else
			{
				msg="Update Failed";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;		
	}

Getting the Lock Status

Similarly, we get the count of records where lock status is 0.

public int getLockStatus()
	{
		int count=0;
		try {
			String query="select count(*) as cnt from exam where lock_status=0";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		return count;		
	}

Chanhing the Name

In order to change the username, we need to run the update statement.

public String changeName(String userID, String newName)
	{
		int res=0;
		String msg="";
		try {
			String query="Update exam set user_name=? where user_id=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  newName);
			pst.setString(2,  userID);
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Success";
			}
			else
			{
				msg="Failed";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;				
	}

Change the Password

Similarly, we can change the password.

public String changePassword(String password)
	{
		// Change the password for all users with user type Admin
		// Returns 'Changed' on sucess and 0 on failure
		int res=0;
		String msg="";
		try {
			String query="Update exam set password=? where user_type='Admin'";
			pst=con.prepareStatement(query);
			pst.setString(1,  password);
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Changed";
			}
			else
			{
				msg="0";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;		
	}

Add a Record

Similarly, we need to run the insert statement to add a record.

public String addUser_1(UserBean userBean)
	{
		int res=0;
		String msg="";
		try {
			String query="insert into exam(user_id, password, user_type, incorrect_attempts, lock_status, user_name) values(?,?,?,?,?,?)";
			pst=con.prepareStatement(query);
			pst.setString(1,  userBean.getUserID());
			pst.setString(2,  userBean.getPassword());
			pst.setString(3,  userBean.getUserType());
			pst.setInt(4,  userBean.getIncorrectAttempts());
			pst.setInt(5,  userBean.getLockStatus());			
			pst.setString(6,  userBean.getUserName());
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Success";
			}
			else
			{
				msg="Fail";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;				
	}

Add a Record for Lock Status 0

Another way of adding a record. When lock status is 0, record is added. Otherwise not.

public String addUser_2(UserBean userBean)
	{
		int res=0;
		String msg="Fail";
		if(userBean.getLockStatus()==0) {
		try {
			String query="insert into exam(user_id, password, user_type, incorrect_attempts, lock_status, user_name) values(?,?,?,?,?,?)";
			pst=con.prepareStatement(query);
			pst.setString(1,  userBean.getUserID());
			pst.setString(2,  userBean.getPassword());
			pst.setString(3,  userBean.getUserType());
			pst.setInt(4,  userBean.getIncorrectAttempts());
			pst.setInt(5,  userBean.getLockStatus());			
			pst.setString(6,  userBean.getUserName());
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Success";
			}
			else
			{
				msg="Fail";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		}
		return msg;		
	}

Get Users by the User Type

Basically, this method returns an ArrayList for a specific user type. For example, you can specify the user types as student or Admin.

public ArrayList<UserBean> getUsers(String userType)
	{
		ArrayList<UserBean> list=new ArrayList<UserBean>();
		try {
			String query="select * from exam where user_type=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  userType);
			rs=pst.executeQuery();
			while(rs.next())
			{
				UserBean userBean=new UserBean();
				userBean.setUserID(rs.getString(1));
				userBean.setPassword(rs.getString(2));
				userBean.setUserType(rs.getString(3));
				userBean.setIncorrectAttempts(rs.getInt(4));
				userBean.setLockStatus(rs.getInt(5));
				userBean.setUserName(rs.getString(6));
				list.add(userBean);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}				
		return list;
	}

Fetching All Records

Similarly, we can get all records. The following code shows it.

public ArrayList<UserBean> storeAllRecords()
	{
		ArrayList<UserBean> list=new ArrayList<UserBean>();
		try {
			String query="select * from exam";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			while(rs.next())
			{
				UserBean userBean=new UserBean();
				userBean.setUserID(rs.getString(1));
				userBean.setPassword(rs.getString(2));
				userBean.setUserType(rs.getString(3));
				userBean.setIncorrectAttempts(rs.getInt(4));
				userBean.setLockStatus(rs.getInt(5));
				userBean.setUserName(rs.getString(6));
				list.add(userBean);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}				
		return list;		
	}

Get User Names

Basically, this method returns an array of strings containing user names.

public String[] getNames()
	{
		int count=0;
		try {
			String query="select count(*) as cnt from exam";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		String[] names=new String[count];
		int i=0;
		try {
			String query="select * from exam";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			while(rs.next())
			{
				names[i]=rs.getString(6);
				i++;
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}						
		
		return names;
	}
}

The Complete Code

package com.programmingempire.exams.dao;
import com.programmingempire.exams.bean.*;
import com.programmingempire.exams.util.*;
import java.sql.*;
import java.util.*;
public class ExamsDAO {
	Connection con;
	PreparedStatement pst;
	ResultSet rs;
	public ExamsDAO()
	{
		con=DBUtil.getDBConnection();
		
	}
	public String getUserType(String userID)
	{
		String type="";
		try {
			String query="select * from exam";
			pst=con.prepareStatement(query);			
			ResultSet rs=pst.executeQuery();			
			while(rs.next())
			{
				String id=rs.getString(1);
				if(id.equals(userID))
				{
					type=rs.getString(3);
					break;
				}	
				
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		return type;
	}
	public String getIncorrectAttempts(String userID)
	{
		int att=0;
		String msg="";
		try {
			String query="select * from exam where user_id=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  userID);
			rs=pst.executeQuery();
			if(rs.next())
			{
				att=rs.getInt(4);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		if(att==0)
			msg="No Incorrect Attempt";
		else
			if(att==1)
				msg="One Time";
			else
				msg="Incorrect Attempt Exceeded";
		return msg;
	}
	public String changeUserType(String userID)
	{
		int res=0;
		String msg="";
		try {
			String query="Update exam set user_type='Admin' where user_id=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  userID);
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Update Success";
			}
			else
			{
				msg="Update Failed";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;		
	}
	public int getLockStatus()
	{
		int count=0;
		try {
			String query="select count(*) as cnt from exam where lock_status=0";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		return count;		
	}
	public String changeName(String userID, String newName)
	{
		int res=0;
		String msg="";
		try {
			String query="Update exam set user_name=? where user_id=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  newName);
			pst.setString(2,  userID);
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Success";
			}
			else
			{
				msg="Failed";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;				
	}
	public String changePassword(String password)
	{
		// Change the password for all users with user type Admin
		// Returns 'Changed' on sucess and 0 on failure
		int res=0;
		String msg="";
		try {
			String query="Update exam set password=? where user_type='Admin'";
			pst=con.prepareStatement(query);
			pst.setString(1,  password);
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Changed";
			}
			else
			{
				msg="0";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;		
	}
	public String addUser_1(UserBean userBean)
	{
		int res=0;
		String msg="";
		try {
			String query="insert into exam(user_id, password, user_type, incorrect_attempts, lock_status, user_name) values(?,?,?,?,?,?)";
			pst=con.prepareStatement(query);
			pst.setString(1,  userBean.getUserID());
			pst.setString(2,  userBean.getPassword());
			pst.setString(3,  userBean.getUserType());
			pst.setInt(4,  userBean.getIncorrectAttempts());
			pst.setInt(5,  userBean.getLockStatus());			
			pst.setString(6,  userBean.getUserName());
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Success";
			}
			else
			{
				msg="Fail";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}		
		return msg;				
	}
	public String addUser_2(UserBean userBean)
	{
		int res=0;
		String msg="Fail";
		if(userBean.getLockStatus()==0) {
		try {
			String query="insert into exam(user_id, password, user_type, incorrect_attempts, lock_status, user_name) values(?,?,?,?,?,?)";
			pst=con.prepareStatement(query);
			pst.setString(1,  userBean.getUserID());
			pst.setString(2,  userBean.getPassword());
			pst.setString(3,  userBean.getUserType());
			pst.setInt(4,  userBean.getIncorrectAttempts());
			pst.setInt(5,  userBean.getLockStatus());			
			pst.setString(6,  userBean.getUserName());
			res=pst.executeUpdate();
			if(res>0)
			{
				msg="Success";
			}
			else
			{
				msg="Fail";
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		}
		return msg;		
	}
	public ArrayList<UserBean> getUsers(String userType)
	{
		ArrayList<UserBean> list=new ArrayList<UserBean>();
		try {
			String query="select * from exam where user_type=?";
			pst=con.prepareStatement(query);
			pst.setString(1,  userType);
			rs=pst.executeQuery();
			while(rs.next())
			{
				UserBean userBean=new UserBean();
				userBean.setUserID(rs.getString(1));
				userBean.setPassword(rs.getString(2));
				userBean.setUserType(rs.getString(3));
				userBean.setIncorrectAttempts(rs.getInt(4));
				userBean.setLockStatus(rs.getInt(5));
				userBean.setUserName(rs.getString(6));
				list.add(userBean);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}				
		return list;
	}
	public ArrayList<UserBean> storeAllRecords()
	{
		ArrayList<UserBean> list=new ArrayList<UserBean>();
		try {
			String query="select * from exam";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			while(rs.next())
			{
				UserBean userBean=new UserBean();
				userBean.setUserID(rs.getString(1));
				userBean.setPassword(rs.getString(2));
				userBean.setUserType(rs.getString(3));
				userBean.setIncorrectAttempts(rs.getInt(4));
				userBean.setLockStatus(rs.getInt(5));
				userBean.setUserName(rs.getString(6));
				list.add(userBean);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}				
		return list;		
	}
	public String[] getNames()
	{
		int count=0;
		try {
			String query="select count(*) as cnt from exam";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}
		String[] names=new String[count];
		int i=0;
		try {
			String query="select * from exam";
			pst=con.prepareStatement(query);
			rs=pst.executeQuery();
			while(rs.next())
			{
				names[i]=rs.getString(6);
				i++;
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
		}						
		
		return names;
	}
}

Testing the Data Access and Manipulation Using JDBC

Finally, we create the ExamMain class. This class contains the main() method. In fact, we need to include test cases for each method that the ExamDAO class defines. So, we create an object of Exam DAO class in the main() method. Using this object, we call methods of the ExamDAO class. The following code shows it.

package com.programmingempire.exams.work;
import com.programmingempire.exams.dao.*;
import com.programmingempire.exams.bean.*;
import java.util.*;
public class ExamsMain {
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ExamsDAO dao=new ExamsDAO();
		
        // Test Case 1
		System.out.println(dao.getUserType("1102"));
        // Test Case 2
		System.out.println(dao.getIncorrectAttempts("1105"));	
		System.out.println(dao.getIncorrectAttempts("1108"));	
		System.out.println(dao.getIncorrectAttempts("1109"));	
        // Test Case 3        
		System.out.println(dao.changeUserType("1103"));
		System.out.println(dao.changeUserType("1106"));
		
		// Test Case 4
		System.out.println(dao.getLockStatus());
		
        // Test Case 5
		System.out.println(dao.changeName("1101", "Avinash"));
		System.out.println(dao.changeName("1107", "Madhu"));		
        // Test Case 6
		System.out.println(dao.changePassword("3322##++@@!77"));
		
        // Test Case 7
		UserBean userBean=new UserBean();
		userBean.setUserID("1110");
		userBean.setUserName("Kim");
		userBean.setUserType("student");
		userBean.setPassword("44####!55123");
		userBean.setIncorrectAttempts(2);
		userBean.setLockStatus(1);
		System.out.println(dao.addUser_1(userBean));
		
        // Test Case 8
		userBean=new UserBean();
		userBean.setUserID("1113");
		userBean.setUserName("Borisnn");
		userBean.setUserType("student");
		userBean.setPassword("44####!55123");
		userBean.setIncorrectAttempts(2);
		userBean.setLockStatus(1);
		System.out.println(dao.addUser_2(userBean));
		
		userBean=new UserBean();
		userBean.setUserID("1114");
		userBean.setUserName("Johnas");
		userBean.setUserType("student");
		userBean.setPassword("44####!55123");
		userBean.setIncorrectAttempts(0);
		userBean.setLockStatus(0);
		System.out.println(dao.addUser_2(userBean));
		
        // Test Case 9
		ArrayList<UserBean> list=dao.getUsers("student");
		Iterator it=list.iterator();
		while(it.hasNext())
		{
			UserBean ob=(UserBean)it.next();
			System.out.println(ob.getUserID()+" "+ob.getUserName()+" "+ob.getUserType()+" "+ob.getPassword()+" "+ob.getIncorrectAttempts()+" "+ob.getLockStatus());
		}
		
		list=dao.getUsers("Admin");
		it=list.iterator();
		while(it.hasNext())
		{
			UserBean ob=(UserBean)it.next();
			System.out.println(ob.getUserID()+" "+ob.getUserName()+" "+ob.getUserType()+" "+ob.getPassword()+" "+ob.getIncorrectAttempts()+" "+ob.getLockStatus());
		}		
        // Test Case 10
		list=dao.storeAllRecords();
		it=list.iterator();
		while(it.hasNext())
		{
			UserBean ob=(UserBean)it.next();
			System.out.println(ob.getUserID()+" "+ob.getUserName()+" "+ob.getUserType()+" "+ob.getPassword()+" "+ob.getIncorrectAttempts()+" "+ob.getLockStatus());
		}	
		
		
        // Test Case 11
		String[] names=dao.getNames();
		for(String str: names)
		{
			System.out.println(str);
		}
	}
}

---

Further Reading

Understanding Enterprise Java Beans

Java Practice Exercise

• AI
• Android
• Angular
• ASP.NET
• Augmented Reality
• AWS
• Bioinformatics
• Biometrics
• Blockchain
• Bootstrap
• C
• C#
• C++
• Cloud Computing
• Competitions
• Courses
• CSS
• Cyber Security
• Data Science
• Data Structures and Algorithms
• Data Visualization
• Datafication
• Deep Learning
• DevOps
• Digital Forensic
• Digital Trust
• Digital Twins
• Django
• Docker
• Dot Net Framework
• Drones
• Elasticsearch
• ES6
• Extended Reality
• Flutter and Dart
• Full Stack Development
• Git
• Go
• HTML
• Image Processing
• IoT
• IT
• Java
• JavaScript
• Kotlin
• Latex
• Machine Learning
• MEAN Stack
• MERN Stack
• Microservices
• MongoDB
• NodeJS
• PHP
• Power Bi
• Projects
• Python
• Quantum Computing
• React
• Robotics
• Rust
• Scratch 3.0
• Shell Script
• Smart City
• Software
• Solidity
• SQL
• SQLite
• Tecgnology
• Tkinter
• TypeScript
• VB.NET
• Virtual Reality
• Web Designing
• WebAssembly
• XML

Princites