Let’s try the PreparedStatement interface in this problem. The PreparedStatement interface enables you to perform Database operations by obtaining parameters at run-time. Let's have practice in PreparedStatement in the following exercise.
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.
| Attributes | Datatype |
| id | Long |
| name | String |
| contactDetail | String |
| username | String |
| password | String |
| Method name | Description |
| public void insertDetails(User u) | This method accepts a User object as an argument and inserts the details in the user table. |
| public displayUser() | This method retrieves all user details in the ascending order of id from the user table, displays the user details in the given format. |
Create a driver class called Main. In the main method, accept user details from the user 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
while retrieving data from table sort by id.
Use PreparedStatement for queries.
System.out.format("%-5s %-10s %-15s %-10s %s\n","Id","Name","Contact Detail","Username","Password");
[All text in bold corresponds to the input and rest corresponds to the output]
Problem Requirements:
Java
| Keyword | Min Count | Max Count |
| PreparedStatement | 1 | - |
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;
}
}
import java.util.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class UserDAO {
Connection con = null;
Statement st = null;
ResultSet rs = null;
public void insertDetails(User u)throws ClassNotFoundException, SQLException{
try{
con = DbConnection.getConnection();
String query = "insert into \"user\"(name, contact_detail, username, password) values(?,?,?,?)";
PreparedStatement stm = con.prepareStatement(query);
stm.setString(1,u.getName());
stm.setString(2,u.getContactDetail());
stm.setString(3,u.getUsername());
stm.setString(4,u.getPassword());
stm.executeQuery();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
public void displayUser()throws ClassNotFoundException, SQLException{
try{
Connection con = DbConnection.getConnection();
String query = "Select * from \"user\" order by id";
PreparedStatement st = con.prepareStatement(query);
ResultSet rs = null;
rs = st.executeQuery();
System.out.format("%-5s %-10s %-15s %-10s %s\n","Id","Name","Contact Detail","Username","Password");
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);
System.out.format("%-5s %-10s %-15s %-10s %s\n",id,name,contactDetail,username,password);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
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;
}
}
import java.util.Scanner;
//import java.io.*;
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 u = new User(inputs[0],inputs[1],inputs[2],inputs[3]);
UserDAO user = new UserDAO();
user.insertDetails(u);
user.displayUser();
}
}
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
ty
ReplyDelete