I have a program that is reading a text file and inserting certain specifics into a SQLite DB. Each line on the text file starts with a V, D, A, N, T, I, or O. For example, a line would be
I,Sam,Jones,,123,1,Liz Jones,334-555-1234,INS123,INS Company, Smith, Broken Arm,1-1-2019, 1-3-2019
The issue I am having is reading the first line of the text file in my main method and the result is my else statement print line. I am storing the first character in char c and, when debugging, I can see the first "I" is being stored as "c = \uFEF". Every subsequent line works as designed, and I cannot figure out why.
I have tried initializing the variable first and then setting its value, I have tried using Scanner instead of BufferedReader. I have tried inserting a blank line at the beginning of the file.
import java.sql.*;
import java.io.*;
public class InsertData {
private static Connection connect(){
String url = "jdbc:sqlite:C://sqlite/Hospital.db";
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
conn.createStatement().executeUpdate("PRAGMA foreign_keys = ON;");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
public static void main(String[] args) throws IOException {
connect();
String patientFile = "Person.txt";
String treatmentFile = "treatment.txt";
String additionalDoctorsFile = "additional_doctors.txt";
try {
BufferedReader br = new BufferedReader(new FileReader(patientFile));
String line;
while((line = br.readLine()) != null) {
char c;
c = line.charAt(0);
if ( c == 'V') {
System.out.println("Inserting Volunteer to DB:\n" + line + "\n");
insertVolunteer(line);
}
else if (c == 'I') {
System.out.println("Inserting Inpatients to DB:\n" + line + "\n");
insertPatient(line);
insertAdmittedPatient(line);
}
else if (c == 'O') {
System.out.println("Inserting Outpatients to DB:\n" + line + "\n");
insertPatient(line);
}
else if (c == 'D') {
System.out.println("Inserting Doctor to DB:\n" + line + "\n");
insertDoctor(line);
}
else if (c == 'A') {
System.out.println("Inserting Administrator to DB:\n" + line + "\n");
insertAdministrator(line);
}
else if (c == 'N') {
System.out.println("Inserting Nurse to DB:\n" + line + "\n");
insertNurse(line);
}
else if (c == 'T') {
System.out.println("Inserting Technician to DB:\n" + line + "\n");
insertTechnician(line);
}
else {
System.out.println("Person input entered incorrectly formatted: " + line + "\n" +
"Record not being written to DB!\n");
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
finally {
BufferedReader br = new BufferedReader(new FileReader(treatmentFile));
String line;
while((line = br.readLine()) != null) {
String[] str = line.trim().split(",");
String sql = "INSERT INTO Treatment(patientLastname, doctorLastname, treatmentType, treatmentName, timestamp) VALUES (?,?,?,?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,str[0]);
ps.setString(2, str[1]);
char t = str[2].trim().charAt(0);
if (t == 'M') {
ps.setString(3, "Medication");
}
else if (t == 'P') {
ps.setString(3, "Procedure");
}
else {
ps.setString(3, "N/A");
System.out.println("Treatment is neither a Procedure or Medication, unable to write to DB");
}
ps.setString(4, str[3]);
ps.setString(5, str[4]);
ps.executeUpdate();
ps.close();
System.out.println("Inserting Treatments to DB:\n" + line + "\n");
}
catch (SQLException e) {
e.printStackTrace();
}
}
BufferedReader br2 = new BufferedReader(new FileReader(additionalDoctorsFile));
String line2;
while((line2 = br2.readLine()) != null) {
String[] str2 = line2.trim().split(",");
String sql = "INSERT INTO AdditionalDoctors(firstname, lastname) VALUES (?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,str2[0]);
ps.setString(2, str2[1]);
ps.executeUpdate();
ps.close();
System.out.println("Inserting Additional Doctors to DB:\n" + line2 + "\n");
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
System.out.println("All data inserted!");
}
public static void insertAdmittedPatient (String lineIn) {
String admittedPatient = lineIn;
String[] str = admittedPatient.trim().split(",");
String sql = "INSERT INTO AdmittedPatient(patientid, firstname, lastname, doctorLastname, admissionDate, dischargeDate) VALUES (?,?,?,?,?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,str[4]);
ps.setString(2, str[1]);
ps.setString(3, str[2]);
ps.setString(4, str[10]);
ps.setString(5, str[12]);
ps.setString(6, str[13]);
ps.executeUpdate();
ps.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertPatient (String lineIn) {
String inPatient = lineIn;
if (inPatient.charAt(0) == 'I') {
String[] str = inPatient.trim().split(",");
int arr = str.length;
String sql = "INSERT INTO Patient(patientid, firstname, lastname, roomNumber, insurance, insuranceNumber, " +
"emergencyContact, emergencyPhone, diagnosis, treatment, admitDate, dischargeDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,str[4]);
ps.setString(2, str[1]);
ps.setString(3, str[2]);
int room = Integer.parseInt(str[5]);
if ((room >= 1) && (room <= 20)) {
ps.setString(4, str[5]);
}
else {
System.out.println("Invalid room number. Must be between 1-20\n" +
"Inserting 'Invalid' to DB");
ps.setString(4, "Invalid");
}
ps.setString(5,str[9]);
ps.setString(6, str[8]);
ps.setString(7,str[6]);
ps.setString(8, str[7]);
ps.setString(9, str[11]);
ps.setString(10, "inpatient");
ps.setString(11, str[12]);
ps.setString(12, str[13]);
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
else {
String[] str = inPatient.trim().split(",");
String sql = "INSERT INTO Patient(patientid, firstname, lastname, roomNumber, insurance, insuranceNumber, " +
"emergencyContact, emergencyPhone, diagnosis, treatment, admitDate, dischargeDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,str[4]);
ps.setString(2, str[1]);
ps.setString(3, str[2]);
ps.setString(4, "N/A");
ps.setString(5,"N/A");
ps.setString(6, "N/A");
ps.setString(7,"N/A");
ps.setString(8, "N/A");
ps.setString(9, "N/A");
ps.setString(10, "outpatient");
ps.setString(11, "n/a");
ps.setString(12, "n/a");
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void insertVolunteer(String lineIn) {
String volunteer = lineIn;
String[] str = volunteer.trim().split(",");
String sql = "INSERT INTO Volunteer(firstname, lastname) VALUES (?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, str[1]);
ps.setString(2, str[2]);
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertDoctor(String lineIn) {
String doctor = lineIn;
String[] str = doctor.trim().split(",");
String sql = "INSERT INTO Doctor(firstname, lastname, consultantPriveleges, admittingPriveleges) VALUES (?,?,?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, str[1]);
ps.setString(2, str[2]);
if (str[3].charAt(0) == 'A') {
ps.setString(3, "False");
ps.setString(4, "True");
}
else {
ps.setString(3, "True");
ps.setString(4, "False");
}
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertAdministrator(String lineIn) {
String admin = lineIn;
String[] str = admin.trim().split(",");
String sql = "INSERT INTO Administrator(firstname, lastname) VALUES (?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, str[1]);
ps.setString(2, str[2]);
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertNurse(String lineIn) {
String admin = lineIn;
String[] str = admin.trim().split(",");
String sql = "INSERT INTO Nurse(firstname, lastname) VALUES (?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, str[1]);
ps.setString(2, str[2]);
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertTechnician(String lineIn) {
String admin = lineIn;
String[] str = admin.trim().split(",");
String sql = "INSERT INTO Technician(firstname, lastname) VALUES (?,?);";
try (Connection conn = connect()) {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, str[1]);
ps.setString(2, str[2]);
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
And the first couple lines in my Person.txt file:
I,Tiera,Keeling,,5FVEJN66YMK4HT0RBEG1AWSUXJZ0FR,1,Alexa Keeling,729-811-8718,Aetna,AETOXJ989E9ZQ,Neva,Lead Poisoning,06-05-2019,06-10-2019
I,Miner,Jigalev,,P9VA7GJQ7E0MUF5XZ33EYYEM2LRZXZ,1,Micky Jigalev,843-545-3757,Red Cross,REDJW18A5IN6L,Jonathon,Ghonorrea,12-06-2010,12-09-2010
I,Briana,Fortier,,EM4QQ43Z8NNA1HPWWT34KJEEP8X8GF,1,Adlai Fortier,306-826-5478,Red Cross,REDLX0UX1W80F,Fernanda,Sprained Ankle,04-20-2012,04-21-2012
I,Yaritza,Aglinskas,,GKMDBWICY4Z32K58EQDWWZ0N88G3A0,1,Maryam Aglinskas,194-887-3283,Blue Shield,BLURZQL71DYX3,Sean,UTI,07-06-2010,07-10-2010
I,Latesha,Guadarrama,,MYRQ4VZU6ZGRD5NKPR3UWUCTYH3WZA,1,Tristen Guadarrama,237-775-1416,Red Cross,REDJKQ5UKF4FJ,Maurine,Poison Ivy,03-04-2018,03-14-2018
I,Jeffrey,Kane,,URC9D8OKUD1O6HEL19JW32AK7HO99O,1,Christen Kane,723-052-0159,Red Cross,REDSO7A2XJAEP,Mariel,Sinus Infection,07-15-2010,07-18-2010
I,Edd,Motoori,,AE2I2EOEHDXP436BCQN5CNOBYFVNJ6,1,Gilmer Motoori,371-640-2423,Aetna,AET5AZ7BEW2VX,Sincere,Dislocated Shoulder,03-20-2013,04-09-2013
I,Tiera,Keeling,,5FVEJN66YMK4HT0RBEG1AWSUXJZ0FR,1,Alexa Keeling,729-811-8718,Aetna,AETOXJ989E9ZQ,Neva,Lead Poisoning,06-05-2019,06-10-2019
I,Clive,Hiraoka,,0B18WK7PGCZK1C1NP1M9EAL55QABGG,1,Coby Hiraoka,840-607-8495,Aetna,AETXVFTRXQP2I,Corrie,Pulmonary Edema,05-28-2015,06-05-2015
I,Leoma,Adamo,,OFK9WMFEDJL1VB4PHSZ7S0FVE8DZ80,1,Tawny Adamo,730-337-9384,Aetna,AETLS5R8N8BDH,Annabel,Gun Shot,05-23-2013,06-13-2013
I,Melvin,Shahmametiev,,D82CEVQOULDEVDZCIXR82I2XXRXSPS,1,Ewin Shahmametiev,363-415-5748,Aetna,AETH6EEJU569K,Fernanda,Shark Bite,10-12-2012,10-14-2012
I,Florine,Evstifeev,,C5JFC4ZR6B7FOZ2MJF9MYGDVKY206X,1,Che Evstifeev,921-725-5356,Red Cross,REDY2CM6U6RWG,Maurine,Glaucoma,11-26-2016,12-11-2016
I,Richard,Nesvetaev,,NWEKL2CC1XNT79JDDFW8U2L3W8ERUQ,1,Malissa Nesvetaev,131-044-3236,Red Cross,REDWXUDH1EKRL,Annabel,Broken Leg,09-09-2013,09-24-2013
I,Cristen,Mihin,,XDBKTIYYSS6KNJ330NIX6HP3AZ4CTI,1,Mustafa Mihin,510-580-9174,Aetna,AET4FB0B0PZYB,Orvel,Trichinosis,05-16-2018,06-06-2018
I,Willard,Chauhan,,G8N1EF72MTWNY1JX52FAD5XVMEA1PR,1,Donia Chauhan,515-886-1297,Orange Cross,ORAX39RLBR93E,Warner,Heat Exhaustion,08-18-2018,09-06-2018
I,Georgeanna,Dover,,HDNDBYK9NUW349P9PBDGS8IGSTJW3Q,1,Hal Dover,433-308-4147,Green Cross,GREFYQQV2BIFZ,Mariann,Sun Burn,09-14-2012,10-03-2012
I,Deirdre,Woods,,ABP26C1H9UJV8136OO0Q0RL8DMJA2F,2,Aline Woods,708-426-3481,Red Cross,REDODODAIBLG7,Skip,Swine Flu,06-30-2018,07-20-2018
I,Micaela,Rapp,,EYRR3C2FOQV8S4BG0U7C6GQREMEKHS,1,Brea Rapp,413-458-6973,Aetna,AETKRGM4BEH85,Cayla,Tennis Elbow,11-05-2018,11-16-2018
I,Daren,Mao,,413CKEOQIGWLQXMMOMRJ3F0V4RH0I0,1,Kellen Mao,648-648-9784,Orange Cross,ORAQQE2VH9TQL,Suzie,Vitamin D Deficiency,06-24-2019,07-08-2019
I,Rickie,Kui,,5IWJ8147Z0MU8WZPPFLFLJCH5QIJC3,1,Kaylee Kui,784-354-0081,Green Cross,GREYLIUI532S5,Maurine,Depression,01-16-2018,01-17-2018
I,Dominic,Hagurov,,L55XCTOV9LTCIM36UWZ51AY7QI7O6W,1,Dwan Hagurov,969-614-8234,Aetna,AET0RKCRVCEHY,Skip,Broken Foot,04-19-2019,05-01-2019
I,Chantel,Blumenthal,,HZP1Z7H027CBMO9SXFJH7Y1ORTKDRM,1,Leda Blumenthal,971-142-8030,Orange Cross,ORAL2LG9ELABS,Fernanda,Ebola,02-10-2016,02-22-2016
I,Lettie,Eustis,,PHY0ECLVWHRP56OR5FWOGA8WPQIDAR,2,Kendrick Eustis,320-447-8781,Red Cross,REDN4V5IGOTVI,Neva,Altitude Sickness,05-29-2018,06-01-2018
O,Benjamen,Otmahov,,4I5GD8O068NFT8WQHM2YDA1G7SSIEC,,,,,,,,,
O,Leanna,Abramo,,LB2OAO9WAZBROTAZGLSZW555C3FWQ6,,,,,,,,,
O,Chauncy,Masih,,HYGDWYV9MDF63YMMU1NEM3CTAT8FVR,,,,,,,,,
O,Philomena,Zhmaev,,VZ5LF33XYGEN0JW2YWPW0V60XASX24,,,,,,,,,
O,Whit,Samuel,,DKTM0E0X9YHS56MWZ7OVYQGXNGLMP6,,,,,,,,,
O,Tyron,Niftrik,,GE7H7C0WRFSOLRMVJI7W6L5JE0KIRN,,,,,,,,,
O,Kasie,Georgeakopoulos,,HKXZ48V06NQPCU0BCZ7COLZJO7BY14,,,,,,,,,
O,Emmalee,Rapson,,JDXQM0I50NF36QQH6CG3DT3WTWEJRF,,,,,,,,,
O,Abigayle,Bertolini,,DRD3JQA7JXU8ZMT8HXOC43YVS1BE1P,,,,,,,,,
O,Marlene,Persov,,WDY33S66HNSZ4FJQSU2DE9CSM8ORBZ,,,,,,,,,
O,Reason,Nield,,L461W9IA3SP07FTG1UD55P3VKCAOEP,,,,,,,,,
O,Edson,Ivanov,,WI7OS8E6AGLR2CMVB0IJ1LNXGSK7IV,,,,,,,,,
O,Catharine,Chalupka,,YNJK0SVT7EC6EBQ7IWJ7AICMH86LG3,,,,,,,,,
O,Codey,Heywood,,UR0MAT4KTJ6K9R2K78RIS4TPF4GTT9,,,,,,,,,
O,Shawnna,Dopiro,,BD3XFNV8L2L1971F6QOSAUVVGCB6CY,,,,,,,,,
O,Shelton,Ruzsky,,OYQIYQEG8K2BVQG5AOQB8AP0R50K1O,,,,,,,,,
O,Coby,Oesterreicher,,7DNEEAWMZ9OHAT28RDUCOP2W3HH4AT,,,,,,,,,
O,Frieda,Karteshkin,,0D1RMQ7DQ6XC4JIEXT0FJK94LSW8OZ,,,,,,,,,
O,Mercedes,Abolins,,6RBHYMWZ3F87Y5HJH2B32NLEJMDU9B,,,,,,,,,
O,Suzanna,Rohlin,,M3WOZ4OO76F6ROG1D9C1498T7JCJV5,,,,,,,,,
O,Hilton,Aldworth,,W86SNM0P1CQDFCLZICGG1HQZRC1J7A,,,,,,,,,
O,Elfie,Judovich,,939GD4FLO9GRD0VN5KFMYFN8HO5G85,,,,,,,,,
O,Mintie,Yaimov,,NC628KXAW0PGE3C90VPPC6M68JE09H,,,,,,,,,
O,Clemma,Tovey,,HC1WYISJUQM08G6R703PYX1H31YEEM,,,,,,,,,
O,Johnpaul,Wizner,,SL4K5X73H28KK1LM07HL1AX3TD1CVK,,,,,,,,,
O,Barbara,Adrianov,,29OOCH5EE4WPWGMTV5BU322REOY73H,,,,,,,,,
O,Tobias,Dunne,,S8W38JEZMENNSL7XTWZF53636RKMI1,,,,,,,,,
O,Harding,Halatnikov,,6Q5D4243VISMIBG44WQSF88O8HRT8F,,,,,,,,,
O,Torrey,Kalitkin,,LRS7SRML51U05MLWRXSG8ZA9M959J8,,,,,,,,,
O,Demian,Dillon,,2Q1T1EMN4DFEGL90EKSJ46L11GFAH4,,,,,,,,,
O,Aubrey,Brooke,,ZFPUZZUR0XN5C1CD05FUGQAPW0RGJK,,,,,,,,,
