Create a new access database called BookStore with one table called Books. Add t
ID: 3741064 • Letter: C
Question
Create a new access database called BookStore with one table called Books. Add the columns ISBN,Title,PublishDate, and Price to theBooks table. Add a few records to the Books table. Create a new connection class called CustomConnection that connects to your BookStore database. The new class should have two methods—one method for opening an ADO Connection object and a second method for closing the ADO Connection object. The method that opens a database connection should take a single string argument, which represents the path and filename of the database.
Explanation / Answer
Dear,
scripts:
create table Books( ISBN varchar2(20),Title varchar2(20),PublishDate varchar2(20) ,Price varchar2(20));
commit;
program:
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
public class CustomConnection {
public static String query = "";
static PreparedStatement pstmt = null;
static Statement stmt = null;
static ResultSet rs = null;
static Connection con = null;
public static Connection openConnection(String path) throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Properties prop = new Properties();
InputStream input = null;
try {
input = new FileInputStream(path);
// load a properties file
prop.load(input);
// get the property value and print it out
System.out.println("====Reading from property file starts======");
System.out.println(prop.getProperty("Url"));
System.out.println(prop.getProperty("USENAME"));
System.out.println(prop.getProperty("PASSWORD"));
System.out.println("====Reading from property file ends======");
System.out.println("====Connection opens======");
con = DriverManager.getConnection(prop.getProperty("Url"), prop.getProperty("USENAME"), prop.getProperty("PASSWORD"));
} catch (IOException ex) {
ex.printStackTrace();
} finally {
if (input != null) {
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return con;
}
public static void main(String[] args) throws Exception {
con=openConnection("src/config.properties");
boolean boo=true;
do{
Scanner in = new Scanner(System.in);
// Display the menu
System.out.println("1 insert data into Products");
System.out.println("2 insert data into Suppliers");
System.out.println("3 Quit");
System.out.println("Please enter your choice:");
//Get user's choice
int choice=in.nextInt();
//Display the title of the chosen module
switch (choice) {
case 1: insertBooks();
break;
case 2: selectBooks();
break;
case 3: System.exit(0);
default: boo=false;System.out.println("Invalid choice");
}//end of switch
}while(boo);
}//end of main method
// selecting records from table
public static void selectBooks() {
try {
stmt = con.createStatement();
rs = stmt.executeQuery("select * from Books");
System.out.println(" ");
while (rs.next()) {
System.out.println("Book ISBN : "
+ rs.getInt(1));
System.out.println("Book Title : "
+ rs.getString(2));
System.out.println("Book PublishDate : "
+ rs.getString(3));
System.out.println("Book Price : "
+ rs.getString(4));
System.out.println(" ");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeConnection();
}
}// method selectCustomers ends
public static void insertBooks(){
try {
Scanner inputs=new Scanner(System.in);
pstmt = con.prepareStatement("INSERT INTO Books(ISBN,Title,PublishDate,Price)values(?,?,?,?)");
System.out.println("Enter ISBN :");
pstmt.setString(1, inputs.next() );
System.out.println("Enter Title :");
pstmt.setString(2, inputs.next() );
System.out.println("Enter PublishDate :");
pstmt.setString(3, inputs.next() );
System.out.println("Enter Price :");
pstmt.setString(4, inputs.next() );
int count = pstmt.executeUpdate();
}catch(SQLException ex){
}finally{
closeConnection();
}
}
public static void closeConnection(){
try {
stmt.close();
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}//class ends
OUTPUT :
====Reading from property file starts======
jdbc:oracle:thin:@localhost:1521:orcl
scott
tiger
====Reading from property file ends======
====Connection opens======
1 insert data into Products
2 insert data into Suppliers
3 Quit
Please enter your choice:
1
====Reading from property file starts======
jdbc:oracle:thin:@localhost:1521:orcl
scott
tiger
====Reading from property file ends======
====Connection opens======
Enter ISBN :
1001
Enter Title :
COMPUTERS
Enter PublishDate :
12/03/2018
Enter Price :
10000
1 insert data into Products
2 insert data into Suppliers
3 Quit
Please enter your choice:
2
====Reading from property file starts======
jdbc:oracle:thin:@localhost:1521:orcl
scott
tiger
====Reading from property file ends======
====Connection opens======
Book ISBN : 101
Book Title : COMPUTERS
Book PublishDate : 12/03/2018
Book Price : 1000
Book ISBN : 1001
Book Title : COMPUTERS
Book PublishDate : 12/03/2018
Book Price : 10000
Book ISBN : 1001
Book Title : COMPUTERS
Book PublishDate : 12/03/2018
Book Price : 10000
Book ISBN : 1001
Book Title : COMPUTERS
Book PublishDate : 12/03/2018
Book Price : 10000
1 insert data into Products
2 insert data into Suppliers
3 Quit
Please enter your choice:
3
Thank You.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.