Monday 6 March 2017

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

32.11 (Baby names) Create the following table:
create table Babyname (
year integer,
name varchar(50),
gender char(1),
count integer,
constraint pk
Babyname primary key (year, name, gender)
);
The baby name ranking data was described in Programming Exercise 12.31. Write a program to read data from the following URL and store into the Babyname table.
http://www.cs.armstrong.edu/liang/data/babynamesranking2001.txt ,
...
http://www.cs.armstrong.edu/liang/data/babynamesranking2010.txt .

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

public class Exercise11 {
 HashMap<String, Integer>[][] babyNamesMapArray;
 public static void main(String[] args) throws MalformedURLException, IOException, ClassNotFoundException, SQLException {
  Exercise11 exercise11= new Exercise11();
  exercise11.loadFiles();
 }
         private void loadFiles() throws MalformedURLException, IOException, ClassNotFoundException, SQLException {
   Class.forName("com.mysql.jdbc.Driver");
   Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "root", "root");
   String queryString = "insert into Babyname (year, name, gender, count) values (?, ?, ?, ?);";
            // Get the baby name file from cs.armstrong website
            String baseURL = "http://www.cs.armstrong.edu/liang/data/babynamesranking";
            String[] urls = new String[10];
            String[] years = new String[urls.length];

            for (int i = 0; i < urls.length; i++) {
                years[i] = (2001 + i) + ""; // create the years array
                urls[i] = baseURL + years[i] +".txt";

                try {
                    URL url = new URL(urls[i]);
                    Scanner in = new Scanner(url.openStream());

                    int gender = 0;
                    Integer rank = 0;
                    while (in.hasNext()) {
                        if (gender == 0)
                            rank = in.nextInt();
                        String name = in.next();
                        String popularity = in.next(); // trash for now
                        String sex;
                        if(gender==1)
                        {
       sex="F";
      }else
      {
       sex="M";
      }
      PreparedStatement preparedStatement = connection.prepareStatement(queryString);
      preparedStatement.setString(1, years[i]);
      preparedStatement.setString(2, name);
      preparedStatement.setString(3, sex);
      preparedStatement.setString(4, String.valueOf(rank));
      preparedStatement.executeUpdate();
                        System.out.println(years[i]+"\t"+name+"\t"+sex+"\t"+rank);

                        if (gender == 1)
                            gender = 0;
                        else
                            gender++;
                    }

                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            
        }
}

No comments :

Post a Comment