Attached Files: nycolleges.csv (767 B) Create a Web application that lists NY Co

Question

Attached Files: nycolleges.csv (767 B) Create a Web application that lists NY Colleges from all the five New York boroughs: Manhattan, Bronx, Queens, Brooklyn, and Staten Island. The user selects a borough and the list of colleges of that borough is displayed. The given csv file, nycolleges.csv, contains 3 fields: ID, zip code, and college name. You can find out the borough a college belongs to by looking at the first three digits of the college: Manhattan: 100XX Staten Island: 103XX Bronx: 104 Brooklyn: 112XX Queens: 110XX, 111XX, 113XX, 114XX, 116XX Import the data from the csv file to a MySQL database (be aware that the first line of the file has the fields' names). Create a GUI interface where the user can select one of five NY boroughs. The output should be a list of college(s) that share the same 3-digit area code

Explanation / Answer

I will provide the starter code from which the whole application can be built.

To import CSV data to MySQL, please go to MySQL Database prompt.

Steps:
1. Create a Database by name nycolleges.

   CREATE TABLE nycolleges (
       sId INT NOT NULL AUTO_INCREMENT,
       zCode INT NOT NULL,
       cName VARCHAR(255) NOT NULL,
       PRIMARY KEY (id)
   );
  
2. To import the data from nycolleges.csv into nycolleges table after ignoring the first row ->
  
   LOAD DATA INFILE 'c:/tmp/nycolleges.csv'
   INTO TABLE nycolleges
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY ' '
   IGNORE 1 ROWS;
  
3. To check if the import is proper, execute
   SELECT * FROM nycolleges
  
   This will list down all the information in nycolleges database.
  
Once the Database is ready, we have to connect to MySQL from our code.
Java code for the same is as below.

   import java.sql.*;
   class MysqlCon{
       public static void main(String args[]){
       try{
           Class.forName("com.mysql.jdbc.Driver");
          
           // Here we provide the Database name along with credentials.
           Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/nycolleges","root","root");
          
           Statement stmt=con.createStatement();
           ResultSet rs=stmt.executeQuery("select * from nycolleges");
           while(rs.next())
           System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
           con.close();
       }catch(Exception e){ System.out.println(e);}
       }
   }

Now, we are ready to create a GUI to accept the data.
Use Java JFrame/Swing to create UI and write some logic to access the data as per requirement.  

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.