4
PLEASE HELP ME WITH MY CODE??
import java.sql.*;
import java.io.*;
class min_filmdb_rel_mysql {
public static void main (String args [])
throws SQLException, IOException {
// the following statement loads the MySQL jdbc driver
try {
Class.forName ("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println ("Could not load the driver");
}
String user, pass, host;
user = readEntry("userid: ");
pass = readEntry("password: ");
host = readEntry("hostname or ip address: ");
// userid, password and hostname are obtained from the console
Connection conn = DriverManager.getConnection
("jdbc:mysql://"+host+":3306/test", user, pass);
/* JDBC default is to commit each SQL statement as it is sent to the database. Setting autocommmit=false changes the default
behaviour so that transactions have to be committed explicity.
*/
conn.setAutoCommit(false);
// Creating a statement lets us issue commands against the connection.
Statement s = conn.createStatement();
// Creating and populating Customer table
s.executeUpdate("create table IF NOT EXISTS Customer( CustomerID VARCHAR(20) PRIMARY KEY, FirstName VARCHAR(20), Surname VARCHAR(20), Address VARCHAR(30), Post code VARCHAR(7), Telephone No VARCHAR(15), Email VARCHAR(20))");
System.out.println("Created table Customer");
s.executeUpdate("insert into Customer values('George Clooney', 'Male', 'American', Date'1965-12-04','Casualty')");
conn.commit();
System.out.println("Inserted some Customers");
ResultSet result=s.executeQuery("Select * From Customer");
System.out.println("Results: ");
while(result.next()) {
System.out.println(result.getString(1) +" "+ result.getString(2) + " "+result.getString(3) +" "+ result.getString(4)+" "+ result.getString(5) );
// Creating and populating Supplier table
s.executeUpdate("create table IF NOT EXISTS Supplier( SupplierID VARCHAR(20) PRIMARY KEY, SupplierName VARCHAR(20), Address VARCHAR(20), Post code VARCHAR(7), Telephone No VARCHAR(15), Email VARCHAR(20))");
System.out.println("Created table Supplier");
// Creating and populating Product table
s.executeUpdate("create table IF NOT EXISTS Product( ProductID VARCHAR(20) PRIMARY KEY, SupplierID VARCHAR(20) PRIMARY KEY, ProductName VARCHAR(20), Price VARCHAR(20))");
System.out.println("Created table Product");
// Creating and populating Staff table
s.executeUpdate("create table IF NOT EXISTS Staff( StaffID VARCHAR(20) PRIMARY KEY, DeptID VARCHAR(20) PRIMARY KEY, FirstName VARCHAR(20), Surname VARCHAR(20), Address VARCHAR(20), Post code VARCHAR(7), Telephone No VARCHAR(15), Mobile No VARCHAR(11))");
System.out.println("Created table Staff");
// Creating and populating Department table
s.executeUpdate("create table IF NOT EXISTS Department( DeptID VARCHAR(20) PRIMARY KEY, DeptName VARCHAR(20))");
System.out.println("Created table Department");
// Creating and populating Customer_Order table
s.executeUpdate("create table IF NOT EXISTS Customer_Order( CustomerID VARCHAR(20) PRIMARY KEY, OrderID VARCHAR(20) PRIMARY KEY, ProductID VARCHAR(20) PRIMARY KEY, ProductName VARCHAR(20), Qty VARCHAR(10), Price VARCHAR(10), Paid VARCHAR(10))");
System.out.println("Created table Cutomer_Order");
// Creating and populating Supplier_Order table
s.executeUpdate("create table IF NOT EXISTS Supplier_Order( SupplierID VARCHAR(20) PRIMARY KEY, OrderID VARCHAR(20) PRIMARY KEY, ProductID VARCHAR(20) PRIMARY KEY, ProductName VARCHAR(20), Qty VARCHAR(10), Price VARCHAR(10))");
System.out.println("Created table Supplier_Order");
s.executeUpdate("insert into Customer values('George Clooney', 'Male', 'American', Date'1965-12-04','Casualty')");
conn.commit();
System.out.println("Inserted some Customers");
ResultSet result=s.executeQuery("Select * From Customer");
System.out.println("Results: ");
while(result.next()) {
System.out.println(result.getString(1) +" "+ result.getString(2) + " "+result.getString(3) +" "+ result.getString(4)+" "+ result.getString(5) );
}
// We end the transaction and the connection.
conn.commit();
conn.close();
}
//readEntry function -- to read input string
static String readEntry(String prompt) {
try {
StringBuffer buffer = new StringBuffer();
System.out.print(prompt);
System.out.flush();
int c = System.in.read();
while(c != '\n' && c != -1) {
buffer.append((char)c);
c = System.in.read();
}
return buffer.toString().trim();
} catch (IOException e) {
return "";
}
}
class min_filmdb_preparedstatement_mysql {
public static void main (String args [])
throws SQLException, IOException {
// the following statement loads the MySQL jdbc driver
try {
Class.forName ("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println ("Could not load the driver");
}
String user, pass, host, database;
user = readEntry("userid : ");
pass = readEntry("password: ");
host = readEntry("hostname or ip address: ");
database = readEntry("database: ");
// userid, password and hostname are obtained from the console
Connection conn = DriverManager.getConnection
("jdbc:mysql://"+host+":3306/"+database, user, pass);
/* JDBC default is to commit each SQL statement as it is sent to the database. Setting autocommmit=false changes the default
behaviour so that transactions have to be committed explicity.
*/
conn.setAutoCommit(false);
// Creating a statement lets us issue commands against the connection.
Statement s = conn.createStatement();
// Creating and populating Actor table
s.executeUpdate("CREATE TABLE IF NOT EXISTS Actor( ActorName VARCHAR(20) PRIMARY KEY, Sex VARCHAR(6), Nationality VARCHAR(20), DOB DATE, DebutFilmTitle VARCHAR(30))");
System.out.println("Created table Actor");
s.executeUpdate("DELETE FROM Actor WHERE TRUE"); // delete all rows from Actor table
// INSERTING DATA USING Statement OBJECT
s.executeUpdate("INSERT INTO Actor VALUES('George Clooney', 'Male', 'American', Date'1965-12-04','Casualty')");
conn.commit();
System.out.println("Inserted one row in Actor table using Statement class");
/* INSERTING DATA USING PreparedStatement OBJECT
*
* The following code is no better than using a Statement object because the PreparedStatement
* object is being compiled twice.
*
*/
String psq = "INSERT INTO Actor VALUES('Hugh Grant', 'Male', 'British', Date'1966-12-04','Four Weddings')";
PreparedStatement ps = conn.prepareStatement(psq); // create PreparedStatement object
ps.execute(); // execute the prepared statement
psq = "INSERT INTO Actor VALUES('Helen Mirren', 'Female', 'British', Date'1945-07-26','The Queen')";
ps = conn.prepareStatement(psq); // create PreparedStatement object
ps.execute(); // execute the prepared statement
conn.commit();
System.out.println("Inserted two rows in Actor table using PreparedStatement class");
/* A PreparedStatement is more useful when it is compiled with a number
of parameters whose value is assigned before the prepared statement is
executed. In the following declaration, each ? stands for a parameter.
*/
String psq2 = "insert into Actor values(?, ?, ?, ?, ?)";
PreparedStatement ps2 = conn.prepareStatement(psq2);
/* Parameter values are set by various setXXX methods of the
PreparedStatement class.
Note how a string is converted to a Java Date before it is used
in the setDate method. (Is there a simpler way?))
*/
ps2.setString(1, "Clark Gable"); // first ? in ps is a String
ps2.setString(2, "Male"); // second ? in ps is a String
ps2.setString(3, "American"); // third ? in ps is a String
// Next line emphasises that it is java.sql.Date and NOT java.util.Date
java.sql.Date when = new java.sql.Date(0); // No default constructor without an argument
when = new java.sql.Date(0).valueOf("1911-06-23"); //convert string in yyyy-mm-dd format to date
ps2.setDate(4,when); // fourth ? in ps is Date
ps2.setString(5, "Gone with the Wind"); // fifth ? in ps is a String
// Now execute the prepared statement to load data
ps2.execute(); // execute the prepared statement
// Clear the parameter values so that they can be set again.
ps2.clearParameters();
// ESSENTIAL JAVA CODE FOR LOADING DATA FROM A TEXT FILE
// We will reuse ps2 which has been compiled previously.
String line; // Data from text file to be read one line at a time
String[] tokens; // line will be parsed into an array of Strings
System.out.println("Inserting data from text file");
String data_file = "data_files/actor_data.txt";
File inputFile = new File(data_file);
FileReader inf = new FileReader(inputFile);
BufferedReader inb = new BufferedReader(inf);
System.out.println("Ready to read line");
line = inb.readLine(); // read a line
// System.out.println(line);
while ( (line != null) ) {
tokens = line.split(","); // split line into tokens separated by a comma
System.out.println(tokens[0]+" "+tokens[1]+" "+tokens[2]+" "+tokens[3]+" "+tokens[04]+" ");
// At this point one should really trim leading and trailing space characters from the tokens.
ps2.setString(1, tokens[0]); // first ? in ps is a String
ps2.setString(2, tokens[1]); // second ? in ps is a String
ps2.setString(3, tokens[2]); // third ? in ps is a String
when = new java.sql.Date(0).valueOf(tokens[3]); //convert string in yyyy-mm-dd format to date
ps2.setDate(4,when); // fourth ? in ps is Date
ps2.setString(5, tokens[4]); // fifth ? in ps is a String
ps2.execute(); // execute the prepared statement
ps2.clearParameters();
line=inb.readLine(); //read next line
}
conn.commit(); // commit after all data has been inserted
inb.close(); // close the buffered reader
inf.close(); // close the file
System.out.println("Inserted some actors using PreparedStatement");
ResultSet result=s.executeQuery("Select * From Actor");
System.out.println("Results: ");
while(result.next()) {
System.out.println(result.getString(1) +" "+ result.getString(2) + " "+result.getString(3) +" "+ result.getString(4)+" "+ result.getString(5) );
}
// We end the transaction and the connection.
conn.commit();
conn.close();
}
//readEntry function -- to read input string
static String readEntry(String prompt) {
try {
StringBuffer buffer = new StringBuffer();
System.out.print(prompt);
System.out.flush();
int c = System.in.read();
while(c != '\n' && c != -1) {
buffer.append((char)c);
c = System.in.read();
}
return buffer.toString().trim();
} catch (IOException e) {
return "";
}
}
} }
