Hey guys!! Hate to bother you but could you guys help me modify this code so it
ID: 3701252 • Letter: H
Question
Hey guys!! Hate to bother you but could you guys help me modify this code so it works!!
package cwhdemo;
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.*;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author camil
*/
public class CWHDemo {
/**
* @param args the command line arguments
*/
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);
}
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);
} catch (ClassNotFoundException ex) {
Logger.getLogger(CWHDemo.class.getName()).log(Level.SEVERE, null, ex);
}
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();
}
}
}
}
Explanation / Answer
// You need to add Apache POI and Open CVS jar dependencies in your project.
// Which will clear all the compile time checks. Use below maven dependencies or download jars and add to build path.
// Then manage imports. Refactored necessary things in below snippet.
// <dependency>
// <groupId>org.apache.poi</groupId>
// <artifactId>poi</artifactId>
// <version>3.17</version>
//</dependency>
//<dependency>
// <groupId>com.opencsv</groupId>
// <artifactId>opencsv</artifactId>
// <version>3.3</version>
//</dependency>
// Below code worked for me. :)
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import com.opencsv.CSVReader;
/**
*
* @author Jayash J
*/
public class CWHDemo {
/**
* @param args
* the command line arguments
*/
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.toString());
fw.write(time.toString());
}
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.createRichTextString(line[i]));
}
}
FileOutputStream out = new FileOutputStream("new.xls");
wb.write(out);
out.close();
rs.close();
} catch (SQLException se) {
Logger.getLogger(CWHDemo.class.getName()).log(Level.SEVERE, null, se);
} catch (ClassNotFoundException ex) {
Logger.getLogger(CWHDemo.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException e) {
Logger.getLogger(CWHDemo.class.getName()).log(Level.SEVERE, null, e);
}
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);
} catch (ClassNotFoundException e) {
System.out.println(e);
} 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();
}
}
}
}