I'm doing some SQL through Java, and I'm hitting a snag with a purely Java part. I'm using filewriter to create and write to a text file, and it successfully creates the file. I can see it in the directory. There are no errors or warnings.
However, the file is empty.
Here is a block of code from the program:
try{
FileWriter x = new FileWriter("file.txt");
rs = stmt.executeQuery("SELECT Emp_Name FROM Employees");
while(rs.next()){
String em = rs.getString("Emp_Name");
x.write("\t" + em);
} catch (IOException e){
e.printStackTrace();
}
}
Here is the entire code.
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
/*
* this program creates a table "users", inserts a row, retrieves the row and
* print it to the console
* you have to provide the database name, the user id and the password
* the database resides on cs1.utm.edu
*/
public class MySQLProgram {
public static void main(String[] args) {
try {
// load the driver
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception ex) {
System.out.println("Class not found exception: " + ex.getMessage());
}
Connection conn = null;
try {
// get the connection
conn = DriverManager.getConnection(
// LINE REMOVED
Statement stmt = null;
ResultSet rs = null;
ResultSet rt = null;
ResultSet ru = null;
ResultSet rv = null;
// create a statement
stmt = conn.createStatement();
stmt.executeUpdate("use USER");
// executes the statement --creates the table
stmt.executeUpdate("CREATE TABLE Department(Dep_Code varchar(6)," + "Dep_Name varchar(55)," + "Phone_Ext int," + "PRIMARY KEY(Dep_Code))");
stmt.executeUpdate("CREATE TABLE Employee(Emp_ID varchar(15)," + "Emp_Name varchar(20) NOT NULL," + "Salary int NOT NULL," + "MGR_ID varchar(10)," + "Dep_Code varchar(5) NOT NULL," + "PRIMARY KEY(Emp_ID)," + "FOREIGN KEY(Dep_Code) REFERENCES Department(Dep_Code))");
stmt.executeUpdate("CREATE TABLE Project(Proj_Num varchar(12) NOT NULL," + "Proj_Name varchar(30) NOT NULL," + "Fee int NOT NULL," + "Due_Date DATE NOT NULL," + "PRIMARY KEY(Proj_Num))");
stmt.executeUpdate("CREATE TABLE Assignment(Emp_ID varchar(10)," + "Proj_Num varchar(10) NOT NULL," + "Hours int NOT NULL," + "PRIMARY KEY(Emp_ID, Proj_Num)," + "FOREIGN KEY(Emp_ID) REFERENCES Employee(Emp_ID)," + "FOREIGN KEY(Proj_Num) REFERENCES Project(Proj_Num))");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Department.dat' INTO TABLE Department FIELDS TERMINATED BY ','");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Employee.dat' INTO TABLE Employee FIELDS TERMINATED BY ','");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Project.dat' INTO TABLE Project FIELDS TERMINATED BY ','");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Assignment.dat' INTO TABLE Assignment FIELDS TERMINATED BY ','");
System.out.println("Display all results:");
try {
FileWriter x = new FileWriter("USER.txt");
rs = stmt.executeQuery("SELECT Proj_Name, Proj_Num FROM Project");
while (rs.next()) {
String em = rs.getString("Proj_Name");
String me = rs.getString("Proj_Num");
System.out.println("\t" + em + ",\t" + me);
x.write("\t" + em + ",\t" + me);
}// end for loop
rt = stmt.executeQuery("SELECT Emp_ID, Emp_Name FROM Employee WHERE MGR_ID = 'e001' ");
while (rt.next()) {
String Nem = rt.getString("Emp_ID");
String Nme = rt.getString("Emp_Name");
System.out.println("\t" + Nem + ",\t" + Nme);
x.write("\t" + Nem + ",\t" + Nme);
}
ru = stmt.executeQuery("SELECT e.Emp_ID, e.Emp_Name FROM Employee e INNER JOIN Assignment a ON e.Emp_ID = a.Emp_ID GROUP BY e.Emp_ID HAVING COUNT(e.Emp_ID) > 1");
while (ru.next()) {
String gem = ru.getString("Emp_Name");
String gme = ru.getString("Emp_ID");
System.out.println("\t" + gem + ",\t" + gme);
x.write("\t" + gem + ",\t" + gme);
}// end for loop
stmt.executeUpdate("UPDATE Employee, Assignment SET Salary = Salary + 600 WHERE Employee.Emp_ID = Assignment.Emp_ID AND Assignment.Hours > 16");
rv = stmt.executeQuery("SELECT Salary FROM Employee");
while (rv.next()) {
String jem = rv.getString("Salary");
System.out.println("\t" + jem);
x.write("\t" + jem);
}// end for loop
}catch (IOException e){
e.printStackTrace();
}
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
}
Use flush() method to complete the writing also once done with writing close the writer in finally block.
I have updated the code with flush and also used finally clause to close the writer.
Also You need to open the FileWriter in append mode to write the data from other queries.
i.e. x = new FileWriter("USER.txt", true);
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
/*
* this program creates a table "users", inserts a row, retrieves the row and
* print it to the console
* you have to provide the database name, the user id and the password
* the database resides on cs1.utm.edu
*/
public class MySQLProgram {
public static void main(String[] args) {
try {
// load the driver
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception ex) {
System.out.println("Class not found exception: " + ex.getMessage());
}
Connection conn = null;
try {
// get the connection
conn = DriverManager.getConnection(
// LINE REMOVED
Statement stmt = null;
ResultSet rs = null;
ResultSet rt = null;
ResultSet ru = null;
ResultSet rv = null;
// create a statement
stmt = conn.createStatement();
stmt.executeUpdate("use USER");
// executes the statement --creates the table
stmt.executeUpdate("CREATE TABLE Department(Dep_Code varchar(6)," + "Dep_Name varchar(55)," + "Phone_Ext int," + "PRIMARY KEY(Dep_Code))");
stmt.executeUpdate("CREATE TABLE Employee(Emp_ID varchar(15)," + "Emp_Name varchar(20) NOT NULL," + "Salary int NOT NULL," + "MGR_ID varchar(10)," + "Dep_Code varchar(5) NOT NULL," + "PRIMARY KEY(Emp_ID)," + "FOREIGN KEY(Dep_Code) REFERENCES Department(Dep_Code))");
stmt.executeUpdate("CREATE TABLE Project(Proj_Num varchar(12) NOT NULL," + "Proj_Name varchar(30) NOT NULL," + "Fee int NOT NULL," + "Due_Date DATE NOT NULL," + "PRIMARY KEY(Proj_Num))");
stmt.executeUpdate("CREATE TABLE Assignment(Emp_ID varchar(10)," + "Proj_Num varchar(10) NOT NULL," + "Hours int NOT NULL," + "PRIMARY KEY(Emp_ID, Proj_Num)," + "FOREIGN KEY(Emp_ID) REFERENCES Employee(Emp_ID)," + "FOREIGN KEY(Proj_Num) REFERENCES Project(Proj_Num))");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Department.dat' INTO TABLE Department FIELDS TERMINATED BY ','");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Employee.dat' INTO TABLE Employee FIELDS TERMINATED BY ','");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Project.dat' INTO TABLE Project FIELDS TERMINATED BY ','");
stmt.executeUpdate(" LOAD DATA LOCAL INFILE 'Assignment.dat' INTO TABLE Assignment FIELDS TERMINATED BY ','");
System.out.println("Display all results:");
FileWriter x = null;
try {
x = new FileWriter("USER.txt", true);
rs = stmt.executeQuery("SELECT Proj_Name, Proj_Num FROM Project");
while (rs.next()) {
String em = rs.getString("Proj_Name");
String me = rs.getString("Proj_Num");
System.out.println("\t" + em + ",\t" + me);
x.write("\t" + em + ",\t" + me);
}// end for loop
x.flush();
rt = stmt.executeQuery("SELECT Emp_ID, Emp_Name FROM Employee WHERE MGR_ID = 'e001' ");
while (rt.next()) {
String Nem = rt.getString("Emp_ID");
String Nme = rt.getString("Emp_Name");
System.out.println("\t" + Nem + ",\t" + Nme);
x.write("\t" + Nem + ",\t" + Nme);
}
x.flush();
ru = stmt.executeQuery("SELECT e.Emp_ID, e.Emp_Name FROM Employee e INNER JOIN Assignment a ON e.Emp_ID = a.Emp_ID GROUP BY e.Emp_ID HAVING COUNT(e.Emp_ID) > 1");
while (ru.next()) {
String gem = ru.getString("Emp_Name");
String gme = ru.getString("Emp_ID");
System.out.println("\t" + gem + ",\t" + gme);
x.write("\t" + gem + ",\t" + gme);
}// end for loop
x.flush();
stmt.executeUpdate("UPDATE Employee, Assignment SET Salary = Salary + 600 WHERE Employee.Emp_ID = Assignment.Emp_ID AND Assignment.Hours > 16");
rv = stmt.executeQuery("SELECT Salary FROM Employee");
while (rv.next()) {
String jem = rv.getString("Salary");
System.out.println("\t" + jem);
x.write("\t" + jem);
}// end for loop
x.flush();
}catch (IOException e){
e.printStackTrace();
}finally {
if(x != null){
try {
x.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
}