The database CWHDemo on the server at IP address 68.178.216.151 has a table name
ID: 653678 • Letter: T
Question
The database CWHDemo on the server at IP address 68.178.216.151 has a table named fall2014 with information about computer courses offered at College in the Fall 2014 semester.
(Use this driver; "com.mysql.jdbc.Driver")
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 you will write. The results can be displayed neatly on the console.
Your query might answer a question such as What 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.
Explanation / Answer
Answer
click to store the database dump in csv file
-------------------------------------------------------------------------------------------------------------------------------
package com.xbrl.dao;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import jxl.write.Label;
import com.utility.HibernateUtil;
public class DB2CSV {
/**
* @param args
*/
public static void main(String[] args) {
//usual database connection part
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "db_name";
String driver = "com.mysql.jdbc.Driver";
String user = "username";
String pass = "password";
FileWriter fw ;
try{
Class.forName(driver);
con = DriverManager.getConnection(url+db, user, pass);
Statement st = con.createStatement();
//this query gets all the tables in your database(put your db name in the query)
ResultSet res = st.executeQuery("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' ");
//Preparing List of table Names
List <String> tableNameList = new ArrayList<String>();
while(res.next())
{
tableNameList.add(res.getString(1));
}
//path to the folder where you will save your csv files
String filename = "D:/db2csv/";
//star iterating on each table to fetch its data and save in a .csv file
for(String tableName:tableNameList)
{
int k=0;
int j=1;
System.out.println(tableName);
List<String> columnsNameList = new ArrayList<String>();
//select all data from table
res = st.executeQuery("select * from xcms."+tableName);
//colunm count is necessay as the tables are dynamic and we need to figure out the numbers of columns
int colunmCount = getColumnCount(res);
try {
fw = new FileWriter(filename+""+tableName+".csv");
//this loop is used to add column names at the top of file , if you do not need it just comment this loop
for(int i=1 ; i<= colunmCount ;i++)
{
fw.append(res.getMetaData().getColumnName(i));
fw.append(",");
}
fw.append(System.getProperty("line.separator"));
while(res.next())
{
for(int i=1;i<=colunmCount;i++)
{
//you can update it here by using the column type but i am fine with the data so just converting
//everything to string first and then saving
if(res.getObject(i)!=null)
{
String data= res.getObject(i).toString();
fw.append(data) ;
fw.append(",");
}
else
{
String data= "null";
fw.append(data) ;
fw.append(",");
}
}
//new line entered after each row
fw.append(System.getProperty("line.separator"));
}
fw.flush();
fw.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
con.close();
}
catch (ClassNotFoundException e){
System.err.println("Could not load JDBC driver");
e.printStackTrace();
}
catch(SQLException ex){
System.err.println("SQLException information");
}
}
//to get numbers of rows in a result set
public static int getRowCount(ResultSet res) throws SQLException
{
res.last();
int numberOfRows = res.getRow();
res.beforeFirst();
return numberOfRows;
}
//to get no of columns in result set
public static int getColumnCount(ResultSet res) throws SQLException
{
return res.getMetaData().getColumnCount();
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.