Need help regarding executeUpdate() for my database
i am trying to update my database and set the records to false... althought it can execute, my records are still not changed according.. help appreciated !!
import java.io.*;
public class Index {
private static String mTitle = "";
public Index() {
}
public static void main(String[] args)throws IOException {
// TODO code application logic here
System.out.println("===Start===");
Dbc dbc = new Dbc();
if(dbc.setCon()==0)
System.out.println("Database load successful...");
else{
System.out.println("Database load unsuccessful...");
System.exit(1);
}
BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
while(mTitle.length()<1 | mTitle==""){
System.out.print("Enter movie title: ");
mTitle = input.readLine();
if(mTitle.length() > 0 && mTitle!=""){
//System.out.println("You have entered: \"" + mTitle + "\"");
System.out.println("Searching database...");
int searchStatus = 0;
searchStatus = dbc.readData(mTitle);
if(searchStatus==0 && mTitle!=""){
mTitle = "";
System.out.println("Movie title not found...\n");
}else if(searchStatus==2 || searchStatus==3){
mTitle = "";
}
}else
System.out.println("Please enter a valid movie title...\n");
}
System.out.println("===End===");
}
}
import java.sql.*;
import java.io.*;
public class Dbc{
private Connection con;
private boolean status;
final static private String _driver = "sun.jdbc.odbc.JdbcOdbcDriver";
final static private String dsn = "database";
final static private String _user = "";
final static private String _pass = "";
final static private String _url = "jdbc:odbc:";
//Establish database connection
public int setCon(){
// load the database driver
try {
Class.forName(_driver);
}
catch (Exception e) {
System.out.println("Load driver error");
System.exit(1);
}
//after loading the driver, establish a connection
try {
String s = _url + dsn;
con = DriverManager.getConnection(s, _user, _pass);
//System.out.println("connection is " + s);
}
catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
return 0;
}
public int readData(String title)throws IOException{
ResultSet mTitle = null;
ResultSet cost = null;
ResultSet loanStatus = null;
ResultSet mId = null;
ResultSet ctg = null;
try{
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();
Statement stmt3 = con.createStatement();
Statement stmt4 = con.createStatement();
Statement stmt5 = con.createStatement();
Statement stmt6 = con.createStatement();
mTitle = stmt1.executeQuery("SELECT MovieTitle FROM Table1 WHERE MovieTitle LIKE '%" + title + "%'");
cost = stmt2.executeQuery("SELECT Cost FROM Table1 WHERE MovieTitle LIKE '%" + title + "%'");
loanStatus = stmt3.executeQuery("SELECT LoanStatus FROM Table1 WHERE MovieTitle LIKE '%" + title + "%'");
mId = stmt4.executeQuery("SELECT CatalogueNo FROM Table1 WHERE MovieTitle LIKE '%" + title + "%'");
ctg = stmt5.executeQuery("SELECT Category FROM Table1 WHERE MovieTitle LIKE '%" + title + "%'");
int num = 0;
while(mTitle.next() && cost.next() && loanStatus.next() && mId.next() && ctg.next()){
status = loanStatus.getBoolean("LoanStatus");
System.out.println("=== Listing " + (num+1) + " ===");
System.out.println("Movie title: " + mTitle.getString("MovieTitle"));
System.out.println("Movie ID: " + mId.getString("CatalogueNo"));
System.out.println("Category: " + ctg.getString("Category"));
System.out.println("Rental price per day: SGD$" + cost.getInt("Cost"));
System.out.println("Availability: " + status);
System.out.println("===========================\n");
//stmt6.executeUpdate("UPDATE Table1 SET LoanStatus=false WHERE MovieTitle LIKE '%" + title + "%'");
num++;
}
/*stmt1.close();
stmt2.close();
stmt3.close();
stmt4.close();
stmt5.close();*/
if(num>0){
BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
if(num==1){
String string = "";
char choice;
while(string.equals("")){
System.out.print("Do you want to rent this movie? <Y/N> ");
string = input.readLine();
string = string.toLowerCase();
if(string.length() == 1 && string.charAt(0) == 'y'){if(status){
stmt6.executeUpdate("UPDATE Table1 SET LoanStatus=false WHERE MovieTitle LIKE '%" + title + "%'");
}else{
System.out.println("Sorry, DVD unavailable...");
return 2;
}
}else if(string.length() == 1 && string.charAt(0) == 'n'){
return 2;
}else{
string = "";
}
}
}else if(num>1){
return 3;
}
return 1;
}
}
catch(java.sql.SQLException e){
System.out.println(e.toString());
}
return 0;
}
}
