I am having a bit of trouble. I am trying to multi thread my application where the database is being populated simultaneously from 2 files that are being read. I initially had the application where it would read the file one at a time and populate the database which worked fine. While it was quick, there is always room to make it go quicker.
Code is as follows:
package fedMerger;
//PREREQUISITES: ENSURE THE FOLLOWING ARE NOT DISABLED IN SERVICES MANAGEMENT WHEN RUNNING THIS UTILITY:
//SQL SERVER BROWER
//SQL SERVER
//SQL SERVER VSS WRITER
//BENCHMARK TEST - 11million merged in 77 minutes.
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import java.util.concurrent.TimeUnit;
class Merger extends Thread {
public String mapperValue2="";
public int i;
public int records;
@SuppressWarnings("null")
public void run() {
FedMerger merger = new FedMerger();
try {
BufferedReader agentFile = new BufferedReader(
new FileReader(merger.getDirectory() + merger.getAgentFile()));
Connection connection = null;
PreparedStatement statement = null;
statement = connection.prepareStatement(merger.getQuery());
for (mapperValue2 = agentFile.readLine(); mapperValue2 != null; mapperValue2 = agentFile.readLine()) {
i++;
records++;
if (!mapperValue2.isEmpty() && mapperValue2.length() > 5) {
statement.setString(1, mapperValue2.substring(0, 26));
statement.setString(2, mapperValue2.substring(26, mapperValue2.length()));
statement.addBatch();
} else {// ignore blanks or white spaces
System.out.println("blank found - skipped");
}
if (i == 100000) {// Populating 5000 records at a time
System.out.println("executing Agent - " + records + " records...");
statement.executeBatch();
statement.clearBatch();
i = 0;
}
}
statement.executeBatch();
statement.close();
agentFile.close();
} catch (SQLException | FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public class FedMerger {
private static String directory = "C:\\Users\\xxx\\Desktop\\Files\\";
public String getDirectory() { return directory; }
private static String AgentfileName = "file1.txt";
String getAgentFile() { return AgentfileName; }
private static String otherFileName = "file2.txt";
private static String mapperValue = "";
private static String TimeStampTableName = "TimeStampTable";
public String getTableName() { return TimeStampTableName;}
private static String timeStampColumn = "TIMESTAMP";
String getTimeStampColumnName() { return timeStampColumn; }
private static String remainingDataColumn = "REMAINING";
String getRemainingDataColumn() { return remainingDataColumn; }
private static String user = "sa";
private static String pass = "uwegudUh4";
public static String Timestampquery="";
String getQuery() {return Timestampquery; }
public static void main(String[] args) throws Exception {
Connection conn = null;
// Scanner input = new Scanner(System.in);
// System.out.print("Enter the full directory of where the file is located: ");
// directory = input.nextLine().trim();
// System.out.print("Enter The Agent FED File Name (Include extension): ");
// AgentfileName = input.nextLine().trim();
// System.out.print("Enter The Mapping FED File Name (Include extension): ");
// otherFileName = input.nextLine().trim();
try {// Database setup and file to be read.
BufferedReader timeStampFile = new BufferedReader(new FileReader(directory + otherFileName));
String dbURL = "jdbc:sqlserver://localhost\\SQLExpress;database=TIMESTAMP_ORGANISER;integratedSecurity=true";
long timer = System.currentTimeMillis();
conn = DriverManager.getConnection(dbURL, user, pass);
if (conn != null) {
System.out.println("Connecting to SQL Server...");
System.out.println("Create table & columns...");
String createTimeStampTable = "CREATE TABLE " + TimeStampTableName + "(" + timeStampColumn
+ " varchar(max)," + remainingDataColumn + " varchar(max))";
conn.createStatement().executeUpdate(createTimeStampTable);
System.out.println("Tables & Columns created - Populating data...");
int i = 0;
int records = 0;
Timestampquery = "INSERT INTO " + TimeStampTableName + "(" + timeStampColumn + ","
+ remainingDataColumn + ") VALUES (?,?)";
PreparedStatement ps = conn.prepareStatement(Timestampquery);
Merger populateAgents = new Merger();
populateAgents.run();
// Dump FED info onto SQL
for (mapperValue = timeStampFile.readLine(); mapperValue != null; mapperValue = timeStampFile
.readLine()) {
i++;
records++;
if (!mapperValue.isEmpty() && mapperValue.length() > 5) {
ps.setString(1, mapperValue.substring(0, 26));
ps.setString(2, mapperValue.substring(26, mapperValue.length()));
ps.addBatch();
} else {// ignore blanks or white spaces
System.out.println("blank found - skipped");
}
if (i == 100000) {// Populating 10000 records at a time
System.out.println("executing timestamp - " + records + " records...");
ps.executeBatch();
ps.clearBatch();
i = 0;
}
}
System.out.println("executing " + records + " records...");
ps.executeBatch();
ps.clearBatch();
i = 0;
// Dump AGENT FED info into same SQL
System.out.print("Uploaded to database - Working SQL query");
BufferedWriter writer = new BufferedWriter(new FileWriter(directory + "newfile" + "_MergedFinal.txt"));
// Organise accordingly
String retrieveData = "select " + timeStampColumn + "+" + remainingDataColumn + " as Data from "
+ TimeStampTableName
+ " order by timestamp, case WHEN remaining LIKE '%agentStateEvent%' THEN -3 "
+ "WHEN remaining LIKE '%TerminalConnectionCreated%' THEN -2 " + "ELSE -1 END";
PreparedStatement stmt = conn.prepareStatement(retrieveData);
ResultSet result = null;
result = stmt.executeQuery();
int j = 0;
String results = "";
System.out.println("Data organised, ready to output...");
while (result.next()) {// SQL Query ran - Output data line by
// line
j++;
System.out.println("outputing data - " + j);
results = result.getString("data");
writer.write(results + "\r\n");
writer.flush();
}
writer.write(results + "\r\n");
writer.flush();
writer.close();
System.out.println("Done - View at " + directory + "NewFile_MergedFinal.txt");
conn.createStatement().executeUpdate("DROP TABLE " + TimeStampTableName);
conn.close();
timeStampFile.close();
System.out
.print("Complete - Time taken: " + ((TimeUnit.MILLISECONDS.toMinutes(System.currentTimeMillis())
- TimeUnit.MILLISECONDS.toMinutes(timer))) + " minutes");
}
} finally {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
conn.createStatement().executeUpdate("DROP TABLE " + TimeStampTableName);
}
}
}
}
The problem seems to occur here:
statement = connection.prepareStatement(merger.getQuery());
which throws:
Exception in thread "main" java.lang.NullPointerException at fedMerger.Merger.run(FedMerger.java:36) at fedMerger.FedMerger.main(FedMerger.java:119)
I am unsure if this due to the fact I have more than 1 prepareStatments in place, one in each class. If that is the case, how do I pass in the existing prepareStatment into other classes?
