Good day . I've the following code which i'm trying to make work . I have to read the content of a text file , which contains questions and save it to the database testsystem .
The main problem that i'm facing is that it's not inserted in the database. and i'm not sure whether it is reading the textfile or not.
any help will be glady appreciated
JFrame and main
public class StudentTestSystems {
public static void main(String[] args) {
try {
Lecturer panel = new Lecturer();
panel.setVisible(true);
panel.setLocationRelativeTo(null);
panel.setResizable(false);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
Lecturer class
public class Lecturer extends JFrame implements ActionListener {
Font font = null;
private JTextField fileField;
private JButton uploadBtn;
private JLabel message;
private JPanel panel;
public Lecturer() {
super("LECTURE MENU");
this.setSize(500, 270);
this.setResizable(false);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
font = new Font("Times New Roman", Font.BOLD, 14);
panel = new JPanel();
panel.setLayout(null);
panel.setOpaque(true);
fileField = new JTextField();
fileField.setFont(font);
fileField.setBounds(130, 110, 200, 25);
Font font2 = new Font("Times New Roman", Font.BOLD + Font.ITALIC, 12);
message = new JLabel("Type in the file name with it's extension. eg: File.txt");
message.setFont(font2);
message.setBounds(130, 140, 280, 25);
message.setOpaque(true);
uploadBtn = new JButton("UPLOAD");
uploadBtn.setFont(font);
uploadBtn.setBounds(340, 110, 95, 21);
panel.add(fileField);
panel.add(uploadBtn);
panel.add(message);
add(panel);
/////// register event
uploadBtn.addActionListener(this);
exit.addActionListener(this);
}
public void actionPerformed(ActionEvent ev) {
String file = fileField.getText();
SetGetQuestionFileName setGet = new SetGetQuestionFileName(file);
FileInputStream fis = null;
PreparedStatement preparedStmt = null;
try {
String myUrl = "jdbc:mysql://localhost:3306/testsystem";
Connection conn = DriverManager.getConnection(myUrl, "root", "cput");
if (ev.getActionCommand().equals("UPLOAD")) {
File filePathString = new File(file);
fis = new FileInputStream(filePathString);
if (fileField.getText().length() == 0) {
JOptionPane.showMessageDialog(null, "File field can't be empty. Try again", "ERROR", JOptionPane.INFORMATION_MESSAGE);
} else {
if (filePathString.exists() && filePathString.isFile()) {
try {
// the mysql insert statement
String query = " insert into users (category_questions, questions, quest_opt_a, , quest_opt_b, , quest_opt_c, quest_opt_d, correct_option_answer )"
+ " values (?, ?, ?, ?, ?)";
// create the mysql insert preparedstatement
preparedStmt = conn.prepareStatement(query);
preparedStmt.setString(1, "JDBC");
preparedStmt.setString(2, fileField.getText());
preparedStmt.setAsciiStream(3, fis, (int) filePathString.length());
//preparedStmt.setString (3, "a");
preparedStmt.setString(4, "b");
preparedStmt.setString(5, "c");
preparedStmt.setString(6, "d");
preparedStmt.setString(7, "a");
preparedStmt.executeUpdate();
// execute the preparedstatement
preparedStmt.executeUpdate();
// myfile.closing();
conn.close();
JOptionPane.showMessageDialog(null, "File successfuly uploaded", "INFORMATION", JOptionPane.INFORMATION_MESSAGE);
fileField.setText("");
} catch (Exception ex) {
} finally {
preparedStmt.close();
fis.close();
conn.close();
}
} else {
JOptionPane.showMessageDialog(null, "File coudn't be found. Do check file name.", "ERROR", JOptionPane.INFORMATION_MESSAGE);
}
}
}
} catch (Exception ex) {
}
if (ev.getActionCommand().equals("LOGOUT")) {
System.exit(0);
}
}
}
SetQuestionFileName class
public class SetGetQuestionFileName {
String myfile;
public SetGetQuestionFileName(String file) {
setFileName(file);
}
public void setFileName(String file) {
myfile = file;
}
public String getFileName() {
return myfile;
}
}
Here a short version on how you can read a file (line by line):
Scanner scanner = new Scanner(filename);
while (scanner.hasNextLine()) {
String line = scanner.nextLine();
}
This is your query. There where too many , or empty column names and the number of columns and ? does not match:
String query = " insert into users (
category_questions,
questions,
quest_opt_a,
, // empty column name
quest_opt_b,
, // empty column name
quest_opt_c,
quest_opt_d,
correct_option_answer
)" + " values (?, ?, ?, ?, ?)"; // you have 7 columns (without the empty ones) but only 5 ?
Here a reformatted version of your query. I just removed the , and added 2 ?
String query = "INSERT INTO users (
category_questions,
questions,
quest_opt_a,
quest_opt_b,
quest_opt_c,
quest_opt_d,
correct_option_answer
) VALUES (?, ?, ?, ?, ?, ?, ?)";
Now I am trying to understand the values you want to add:
preparedStmt.setString(1, "JDBC"); // so JDBC is your category ?
preparedStmt.setString(2, fileField.getText()); // I guess this should be the question?
preparedStmt.setString(3, "a"); // according to the column names this needs to be option A
preparedStmt.setString(4, "b"); // option b
preparedStmt.setString(5, "c"); // option c
preparedStmt.setString(6, "d"); // option d
preparedStmt.setString(7, "a"); // answer
So far so good. If I interpreted the column names correctly, this should work. The only thing left is now to get the real question text for column 2 (from the file).
I don't know how your file looks like, so I can just give you an example:
textfile in this format: question? answerA, answerB, answerC, answerD, correctAnswer
-------------------------------------------------------------------------------------
What is the color of the sun? pink, blue, yellow, green, c
What is the smallest number? 3, 1, 7, 200, b
And this would be how you could read the file and fill the database all at once:
// prepare the query
String query = "INSERT INTO users (
category_questions,
questions,
quest_opt_a,
quest_opt_b,
quest_opt_c,
quest_opt_d,
correct_option_answer
) VALUES (?, ?, ?, ?, ?, ?, ?)";
// load the file
Scanner scanner = new Scanner(filename);
// read the file line by line
while (scanner.hasNextLine()) {
String line = scanner.nextLine();
String[] questionAnswer = line.split("?")[ // line.split["?"] will split the line into two strings, right at the ? and put those two strings into an array.
String question = questionAnswer[0] // so [0] will access the first element in that array - the question
String[] answers = questionAnswer[1].split(","); // now we split the string after the ? into many strings divided by comma
preparedStmt.setString(1, "JDBC");
preparedStmt.setString(2, question);
preparedStmt.setString(3, answers[0]);
preparedStmt.setString(4, answers[1]);
preparedStmt.setString(5, answers[2]);
preparedStmt.setString(6, answers[3]);
preparedStmt.setString(7, answers[4]);
preparedStmt.executeUpdate();
}
There are many ways to read the line from your text file and split it into question and answer. It depends on the file you read from and how it is formatted.
FIY: you execute preparedStmt.executeUpdate(); twice at the end :)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments