import java.sql.*;
import java.io.*;
import java.sql.PreparedStatement;
import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;
class shipment_prac3_readtxtfile {
public static void main (String args []) throws SQLException, IOException {
// the following statement loads the Oracle jdbc driver
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println ("Could not load the driver");
}
String user, pass, host,servicename;
user = readEntry("userid : ");
pass = readEntry("password: ");
host = readEntry("hostname or ip address: ");
/* userid, password and hostname are obtained from the console
* It is assumed that the service name is XE and the databse server
* listens on the default port 1521.
*/
servicename="orcl.ijgj9.infj.ulst.ac.uk";
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:"+user+"/"+pass+"@"+host+":1521/"+servicename);
/* 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 Shipment table
s.executeUpdate("drop table shipment");
s.executeUpdate("create table Shipment(CoffeeName VARCHAR(20), SuppID VARCHAR(7), DateRecd Date, UnitPrice INT, Quantity INT, TotalPrice INT, PRIMARY KEY(CoffeeName, SuppID, DateRecd))");
System.out.println("Created table Shipment");
s.executeUpdate("insert into Shipment values('Java', 'S101', Date '2009-02-24', 20, 130, 2600)");
s.executeUpdate("insert into Shipment values('Kenco', 'S106', Date '2009-02-23', 40, 100, 4000)");
s.executeUpdate("insert into Shipment values('Vella', 'S103', Date '2009-02-23', 10, 190, 1900)");
s.executeUpdate("insert into Shipment values('Java', 'S101', Date '2009-02-21', 70, 90, 6300)");
s.executeUpdate("insert into Shipment values('Java', 'S101', Date '2009-02-20', 100, 66, 6600)");
s.executeUpdate("insert into Shipment values('Denvenna', 'S101', Date '2009-03-01', 28, 200, 5600)");
s.executeUpdate("insert into Shipment values('Eden', 'S102', Date '2009-02-13', 89, 300, 26700)");
/* s.executeUpdate("insert into Shipment values('Fenti' 'S104', Date '2009-01-19', 15, 45, 675)");
s.executeUpdate("insert into Shipment values('Otus', 'S105', Date '2009-02-28', 33, 123, 4059)");
s.executeUpdate("insert into Shipment values('Lenvu', 'S101', Date '2009-02-05', 34, 220, 7480)");
*/
//Essential Java code for loading data from a text file
String psq = "insert into Shipment values(?, ?, ?, ?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(psq);
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 = "/home/pinate/shipments";
File inputFile = new File(data_file);
FileReader inf = new FileReader(inputFile);
BufferedReader inb = new BufferedReader(inf);
//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
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[4]+" "+tokens[5]+" ");
ps.setString(1, tokens[0]); //first ? in ps is a String
ps.setString(2, tokens[1]); //second ? in ps is a String
when = new java.sql.Date(0).valueOf(tokens[2]); //convert string yyyy-mm-dd format to date
ps.setDate(3, when); //third ? in ps is a Date
ps.setInt(4, tokens[3]); //fourth ? in ps is an Integer
ps.setInt(5, tokens[4]); //fifth ? in ps is an Integer
ps.setInt(6, tokens[5]); //sixth ? in ps is an Integer
ps.execute(); //execute the prepared statement
ps.clearParameters();
line = inb.readLine(); //read next line
}
ResultSet result1a = s.executeQuery("Select * From Shipment");
System.out.println("Shipment Results: ");
while(result1a.next()) {
System.out.println(result1a.getString(1) +" "+ result1a.getString(2) + " "+result1a.getString(3) +" "+ result1a.getString(4)+" "+ result1a.getString(5)+" "+ result1a.getString(6) );
}
conn.commit(); //commit after all data has been inserted
inb.close(); //close the buffered reader
inf.close(); //close the file
}
//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 "";
}
}
} |
JDBC Prepared Statement
Linear Mode
