Monday 6 March 2017

Chapter 32 Exercise 7, Introduction to Java Programming, Tenth Edition Y. Daniel LiangY.

32.7 (Populate Quiz table) Create a table named Quiz as follows:
create table Quiz(
questionId int,
question varchar(4000),
choicea varchar(1000),
choiceb varchar(1000),
choicec varchar(1000),
choiced varchar(1000),
answer varchar(5));
The Quiz table stores multiple-choice questions. Suppose the multiple-choice
questions are stored in a text file accessible from www.cs.armstrong.edu/liang/data/Quiz.txt in the following format:
1. question1
a. choice a
b. choice b
c. choice c
d. choice d
Answer:cd
2. question2
a. choice a
b. choice b
c. choice c
d. choice d
Answer:a
...
Write a program that reads the data from the file and populate it into the Quiz
table.


import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Exercise07 {

 public static void main(String[] args) throws MalformedURLException, IOException, ClassNotFoundException, SQLException {
  Scanner input = new Scanner(new URL("http://www.cs.armstrong.edu/liang/data/Quiz.txt").openStream());
  
  Class.forName("com.mysql.jdbc.Driver");
  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "root", "root");
  String queryString = "insert into Quiz (questionId, question, choicea, choiceb, choicec, choiced, answer) values (?, ?, ?, ?, ?, ?, ?);";
  
  while(input.hasNextLine()) {
   PreparedStatement preparedStatement = connection.prepareStatement(queryString);
   
   String nextLine = input.nextLine();
   if(nextLine.equals("")) {
    continue;
   }
   preparedStatement.setString(1, nextLine.substring(0, nextLine.indexOf(".")));
   preparedStatement.setString(2, nextLine.substring(nextLine.indexOf(".") + 2));
   
   do {
    nextLine = input.nextLine();    
   } while((nextLine.length() < 2)||(!nextLine.substring(0, 2).equals("a.")));
   
   preparedStatement.setString(3, nextLine.substring(3));
   preparedStatement.setString(4, input.nextLine().substring(3));
   preparedStatement.setString(5, input.nextLine().substring(3));
   preparedStatement.setString(6, input.nextLine().substring(3));
   preparedStatement.setString(7, input.nextLine().substring(7));
   preparedStatement.executeUpdate();
  }
  input.close();
 }

}

No comments :

Post a Comment