Help please!! The database CWHDemo on the server at IP address 68.178.216.151has
ID: 3700819 • Letter: H
Question
Help please!!
The database CWHDemo on the server at IP address 68.178.216.151has a table named fall 2014 with information about computer courses offered at College in the Fall 2014
semester.
Your task is to develop a Java application with two methods:
1.a method that queries the database, retrieving and writing in a CSV file the crn, subject , course,section,days and time for all CSCI courses, in order according to the course number. You should be able to open the CSV file in Microsoft Excel.
2.a method that queries the database with a query of your own design based on a question youwill write. The results can be displayed neatly on the console.
Your query might answer a question such asWhat 4 credit courses are available on Tuesday and Thursday? or What sections of OA courses are being offered online? You should make up your own question, write the query to find the answer, and then create a method to get the answer from within a java application.
You can write a single application with methods for each of the two queries.
Here is annotated metadata for the table:
Column
Type
Notes
crn
char(20)
CRN primary key
subject
varchar(5)
CIS, CSCI, or OA
course
varchar(5)
cou
rse number
section
varchar(5)
section number
credits
integer
number of credits
time
varchar(20)
the time the course meets.
days
varchar(8)
the days the course meets: M T W R F S (No Sunday courses.)
term
varchar(5)
15A means the course meets for the
entire 15
-
week term.
7A courses meet for the first half of the term.
7B or 7N courses meet for the second half of the term.
campus
varchar(5)
MAIN, NERC,NWRC, WEST
room
varchar(8)
the room number
enrollment
integer
The data in this column is not real.
It was randomly generated.
All other columns contain real data.
(The data in the table is real data, except for the enrollment, which was randomly generated before
students began registering for the courses.)
Explanation / Answer
ANSWER:
DEVELOPING A JAVA APPLICATION:
import java.sql.*;
import java.io.FileWriter.*;
import java.util.*;
import java.sql.Date;
import java.lang.Object.*;
import java.lang.AutoCloseable.*;
import java.io.Closeable.*;
import java.io.FileOutputStream;
import java.util.*;
public class CWHDemo
{
public static void main(String[] args)
{
writetoCSVfile();
}
public static void writetoCSVfile()
{
Connection connection = null;
Statement stmt = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Connecting to a selected database...");
connection = DriverManager.getConnection("jdbc:odbc:CWHDemo", "", "");
System.out.println(" database connected successfully...");
stmt = connection.createStatement();
String sql = "SELECT * FROM fall2014";
ResultSet rs = stmt.executeQuery(sql);
String fileheader="credit.subject,course,section,days,time";
String filename="example.csv";
FileWriter fw=new FileWriter(filename);
fw.append(fileheader.toString());
while(rs.next())
{
int credit = rs.getInt("crn");
String subject = rs.getString("subject");
String course = rs.getString("course");
String section = rs.getString("section");
Date date = rs.getDate("date");
Time time=rs.getTime("time");
// Writing data to CSV file
fw.append(String.valueOf(credit));
fw.append(subject);
fw.append(course);
fw.append(section);
fw.write(date);
fw.write(time);
}
// Writing data from the CSV file into excel
HSSFWorkbook wb=new HSSFWorkbook();
CreationHelper help=wb.getCreationHelper();
HSSFSheet sheet1=wb.createsheet("SHEET");
CSVReader reader=new CSVReader(new FileReader(filename));
String[] line;
int i=0;
while((line=reader.readNext())!=null)
{
Row r=sheet1.createRow(i++);
for(int j=0;j<line.length;j++)
{
r.createCell(j).setCellValue(help.createString(line[i]));
}
}
FileOutputStream out=new FileOutputStream("new.xls");
wb.write(out);
out.close();
rs.close();
}
catch(SQLException se)
{
System.out.println(se);
}
finally
{
try
{
if(stmt!=null)
connection.close();
}
catch(SQLException se)
{
System.out.println(se);
}
Try
{
if(connection!=null)
connection.close();
}
catch(SQLException se)
{
se.printStackTrace();
}
}
}
public static void display()
{
Connection connection = null;
Statement stmt = null;
Try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Connecting to a selected database...");
connection = DriverManager.getConnection("jdbc:odbc:CWHDemo", "", "");
System.out.println(" database connected successfully...");
stmt = connection.createStatement();
// Query for the question What 4 credit courses are available on Tuesday and Thursday?
String credit = "SELECT course FROM fall2014 where credit=4 and days='tuesday' or days='thursday'";
ResultSet rs = stmt.executeQuery(credit);
System.out.println("course");
while(rs.next())
{
String course = rs.getString("course");
System.out.println(course);
}
// Query for the question What sections of OA courses are being offered online?
String query = "SELECT section FROM fall2014 where course='OA'";
rs=stmt.executeQuery(query);
System.out.println("section");
while(rs.next())
{
String section = rs.getString("section");
System.out.println();
}
rs.close();
}
catch(SQLException se)
{
System.out.println(se);
}
finally
{
try
{
if(stmt!=null)
connection.close();
}
catch(SQLException se)
{
System.out.println(se);
}
try
{
if(connection!=null)
connection.close();
}
catch(SQLException se)
{
se.printStackTrace();
}
}
}
}
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.