Select Statement
Write a program to retrieve all the records present in the table “user” and display those records in the specified format using the SELECT statement.
Strictly adhere to the Object-Oriented specifications given in the problem statement. All class names, attribute names and method names should be the same as specified in the problem statement.
Consider the class User with the following private attributes
| Attributes | Datatype |
| id | Long |
| name | String |
| contactDetail | String |
| username | String |
| password | String |
The methods for getters, setters and constructors are given in the template code.
Consider the class UserDAO and define the following method
Method & Description
public List<User> getAllUsers()
This function finds all records of the User table and returns the User objects in a List.
Consider the class DBConnection and define the following static method.
| Method | Description |
| static Connection getConnection() | This method uses "oracle.properties" file as a resource file and
gets a connection object for the MySQL database and returns the connection object. |
Create a driver class Main to display a List of objects in the given format.
Note :
- Use System.out.format("%-5s %-5s %-15s %-10s %s\n","Id","Name","Contact Detail","Username","Password") to display user details.
Table properties:
CREATE TABLE “user”(
id number(19) NOT NULL,
name VARCHAR2(45) NOT NULL,
contact_detail VARCHAR2(45) NOT NULL,
username VARCHAR2(45) NOT NULL,
password VARCHAR2(45) NOT NULL,
PRIMARY KEY (id));
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
Use the following code snippet to establish DBConnection:
import java.util.ResourceBundle;
ResourceBundle rb = ResourceBundle.getBundle("oracle");
String url = rb.getString("db.url");
String username = rb.getString("db.username");
String password = rb.getString("db.password");
oracle.properties:
db.url = jdbc:oracle:thin:@localhost:1521:xe
db.username = root
db.password = student
Download the oracle jar file in the below link.Oracle jar
Sample Input and Output:
| Id | Name | Contact Detail | Username | Password |
| 1 | John | 9876543210 | johny | 12345 |
| 2 | Peter | 9873216540 | peterey | pet123 |
| 3 | Adam | 9871236504 | adamanta | ad@123 |
| 4 | Linda | 8794561320 | lindahere | abcd |
| 5 | Tony | 7894561230 | tonii | abc123 |
PROGRAM:-
@MRProgrammer89
CREATE CLASS < User > :
public class User {
private Long id;
private String name;
private String contactDetail;
private String username;
private String password;
public User(){}
public User(Long id,String name,String contactDetail,String username,String password){
this.id = id;
this.name = name;
this.contactDetail = contactDetail;
this.username = username;
this.password = password;
}
public Long getId(){
return id;
}
public void setId(Long id){
this.id = id;
}
public String getName(){
return name;
}
public void setname(String name){
this.name = name;
}
public String getContactDetail(){
return contactDetail;
}
public void setContactDetail(String contactDetail){
this.contactDetail = contactDetail;
}
public String getUsername(){
return username;
}
public void setUsername(String username){
this.username = username;
}
public String getPassword(){
return password;
}
public void setPassword(String password){
this.password = password;
}
}
CREATE CLASS < UserDAO > :
import java.util.*;
import java.sql.*;
public class UserDAO {
public List<User> getAllUsers(){
List<User> userList = new ArrayList<User>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try{
con = DBConnection.getConnection();
st = con.createStatement();
String query = "Select * from \"user\" ";
rs = st.executeQuery(query);
while(rs.next()){
long id = rs.getInt(1);
String name = rs.getString(2);
String contactDetail = rs.getString(3);
String username = rs.getString(4);
String password = rs.getString(5);
User itd = new User(id , name,contactDetail, username, password);
userList.add(itd);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{ try{
rs.close();
st.close();
con.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
return userList;
}
}
}
CREATE CLASS < Main > :
import java.util.*;
public class Main {
public static void main(String args[]) throws Exception{
UserDAO itd=new UserDAO();
List<User> alist = itd.getAllUsers();
System.out.printf("%-5s %-5s %-15s %-10s %s\n","Id","Name","Contact Detail","Username","Password");
for(User X :alist)
{
long id = X.getId();
String name =X.getName();
String contactDetail=X.getContactDetail();
String username=X.getUsername();
String password=X.getPassword();
System.out.printf("%d %s %s %s %s\n",id,name,contactDetail,username,password);
}
}
}
CREATE CLASS < DBConnection> :
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class DBConnection {
public static Connection getConnection() throws ClassNotFoundException, SQLException {
ResourceBundle rb = ResourceBundle.getBundle("oracle");
String url = rb.getString("db.url");
String username = rb.getString("db.username");
String password = rb.getString("db.password");
Connection con = DriverManager.getConnection(url,username,password);
return con;
}
}
MOTIVATION :
“The way we spend our time defines who we are ”
Although we may not see each other as often as we’d like, distance is no match for the bond that we share. Thank you for coming to visit. It was fantastic to catch up.
VISITE MORE BLOGS
THANK YOU....😊
