PreparedStatement
The PreparedStatement interface enables you to perform Database operations by obtaining parameters at run-time.
Write a program to insert and display user details in the database using PreparedStatement.
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 methods
Method name & Description
1> public void insertDetails(User u)
This method accepts a User object as an argument and
inserts the details in the user table.
2> public List<User> getAllUser()
This method retrieves all user details in the ascending order of id from the user table,
stores it in the list of user objects, and returns the 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. |
Consider the driver class called Main. In the main method, accept user details from the console and call appropriate methods in the UserDAO class.
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
Note:
- While retrieving data from table, it should be in the sorted order by id.
- Use PreparedStatement to insert and retrieve data.
- Use System.out.format("%-5s %-10s %-15s %-10s %s\n","Id","Name","Contact Detail","Username","Password") to display data in table format
Sample Input and Output:
[All text in bold corresponds to the input and rest corresponds to the output]
Enter the user detail in CSV format
Antony,9873216540,Antonie,an@987
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
6 Antony 9873216540 Antonie an@987
Problem Requirements:
Java
| Keyword | Min Count | Max Count |
| PreparedStatement | 1 | -
|
PROGRAM:-
@MRProgrammer
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(String name,String contactDetail,String username,String password){
this.name = name;
this.contactDetail = contactDetail;
this.username = username;
this.password = password;
}
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(){
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.sql.*;
import java.util.ArrayList;
import java.util.List;
public class UserDAO {
public List<User> getAllUser() throws Exception{
List<User> userList = new ArrayList<User>();
try{
Connection con = DBConnection.getConnection();
String query = "Select * from \"user\" order by id";
PreparedStatement st = con.prepareStatement(query);
ResultSet rs = null;
rs = st.executeQuery();
while(rs.next()){
Long id = (long)rs.getInt(1);
String name = rs.getString(2);
String contactDetail = rs.getString(3);
String username = rs.getString(4);
String password = rs.getString(5);
User u = new User(id, name, contactDetail, username, password);
userList.add(u);
}
}catch(Exception e){
System.out.println(e);
}
return userList;
}
public void insertDetails(User user) throws Exception{
try{
Connection con = null;
con = DBConnection.getConnection();
String query = "insert into \"user\"(name, contact_detail, username, password) values(?,?,?,?)";
PreparedStatement stm = con.prepareStatement(query);
stm.setString(1,user.getName());
stm.setString(2,user.getContactDetail());
stm.setString(3,user.getUsername());
stm.setString(4,user.getPassword());
stm.executeQuery();
}catch(Exception e){
System.out.println(e);
}
}
}
import java.util.ArrayList;
import java.util.*;
public class Main {
public static void main(String[] args) throws Exception{
Scanner sc = new Scanner(System.in);
System.out.println("Enter the user detail in CSV format");
String str = sc.next();
String inputs[] = str.split(",");
User obj = new User(inputs[0],inputs[1],inputs[2],inputs[3]);
UserDAO user = new UserDAO();
user.insertDetails(obj);
List<User> alist = user.getAllUser();
System.out.format("%-5s %-10s %-15s %-10s %s\n","Id","Name","Contact Detail","Username","Password");
for(User u:alist){
System.out.format("%-5s %-10s %-15s %-10s %s\n",u.getId(),u.getName(),u.getContactDetail(),u.getUsername(),u.getPassword());
}
}
}
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....😊
