Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package Main; import Logic.IndeedScraper; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.poi.common.usermodel.Hyperlink; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFHyperlink; import static java.lang.Class.forName; import static java.lang.String.format; import static java.sql.DriverManager.getConnection; import static java.util.logging.Logger.getLogger; import org.apache.poi.ss.usermodel.WorkbookFactory; import static org.apache.poi.ss.usermodel.WorkbookFactory.create; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author jason * * this class contains method which allow the ability to connect to the * database,create new tables,disconnect from the database and append results * from the database to the Excel Sheet. */ public class Database { static Connection connect = null; PreparedStatement addStmt; PreparedStatement dropStmt; PreparedStatement createStmt; PreparedStatement selectStmt; ResultSet resultSet; String classPathDB = "jdbc:mysql://localhost:3306/"; String database = "jobs"; String user = "root"; String password = "root"; String createDBTable; FileInputStream excelInputStream = null; FileOutputStream excelOutputStream = null; File excelFile = null; String title, date, link; //helps iterate down the rows in the Excel sheet. int rowNumber; /** * * @return - returns a connection to the database in the form of an object. * * Create and connect to the database. */ public Connection connectDatabase() { try { forName("com.mysql.jdbc.Driver"); connect = getConnection(classPathDB + database, user, password); //Statement statement = connect.createStatement(); //statement.executeUpdate("CREATE DATABASE IF NOT EXISTS JOBS"); } catch (ClassNotFoundException ex) { getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } return connect; } /** * * @param tableName - name of the table being created. * * This method drops table if it exists, then creates a brand new table. */ public void createNewTable(String tableName) { createDBTable = "create table " + tableName + " (\n" + " ID Integer PRIMARY KEY AUTO_INCREMENT,\n" + " title varchar(255) NOT NULL,\n" + " link LONGTEXT NOT NULL,\n" + " date varchar(255) NOT NULL\n" + ");"; try { connect = connectDatabase(); dropStmt = connect.prepareStatement(format("DROP TABLE IF EXISTS %s", tableName)); //dropStmt = connect.prepareStatement(sqlNewTableStmt); dropStmt.execute(); createStmt = connect.prepareStatement(createDBTable); createStmt.execute(); } catch (SQLException ex) { getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } finally { closeDBSession(); } } /** * * @param title -the job title passed into database. * @param link -the job link passed into database. * @param date -the job date passed into database. * @param dbTable -the job title passed into database. * * adds each job title,link,and date to the database, and close the database * after inserting. */ public void addToDataBase(String title, String link, String date, String dbTable) { try { connect = connectDatabase(); // the mysql insert statement String sqlAddStmt = " insert into " + dbTable + " (title,link,date)" + " values (?, ?, ?)"; addStmt = connect.prepareStatement(sqlAddStmt); addStmt.setString(1, title); addStmt.setString(2, link); addStmt.setString(3, date); addStmt.executeUpdate(); } catch (SQLException ex) { getLogger(IndeedScraper.class.getName()).log(Level.SEVERE, null, ex); } finally { closeDBSession(); } } /** * helper method to close the db after a session. */ public void closeDBSession() { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } } if (addStmt != null) { try { addStmt.close(); } catch (SQLException e) { } } if (connect != null) { try { connect.close(); } catch (SQLException e) { } } } /** * @param dbTable - the database table which contains job results from a * particular job board. * @param excelSheetIndex - the sheet index on the Excel File which will * house the results from the database. * * This method appends results from the specific database table onto the * index of the excel sheet. */ /* public void appendDBTableToExcel(String dbTable, int excelSheetIndex) { try { connect = connectDatabase(); String sqlQuery = "Select *from " + dbTable; // create the java statement selectStmt = connect.prepareStatement(sqlQuery); resultSet = selectStmt.executeQuery(); //call upon the excel file. excelFile = new File("C:\\Users\\jason\\Desktop\\Job Scraper\\JobScraper\\src\\main\\java\\Main\\links.xlsx"); excelInputStream = new FileInputStream(excelFile); Workbook workbook = create(excelInputStream); CreationHelper createHelper = workbook.getCreationHelper(); Sheet sheet = workbook.getSheetAt(excelSheetIndex); excelOutputStream = new FileOutputStream(excelFile); //set link style to blue CellStyle hlinkstyle = workbook.createCellStyle(); Font hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); // iterate through the java database,grabbing the details of the job. System.out.println("I can see contents of database"); while (resultSet.next()) { this.title = resultSet.getString("title"); this.link = resultSet.getString("link"); this.date = resultSet.getString("date"); System.out.println(this.title + " / " + this.link + " / " + this.date ); //append database Information onto Excel Row row = sheet.createRow(rowNumber); Cell titleCell = row.createCell(0); titleCell.setCellValue(title); Cell linkCell = row.createCell(1); linkCell.setCellValue(link); Cell dateCell = row.createCell(2); dateCell.setCellValue(date); //Make the link a clickable blue url. XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); hyperLink.setAddress(link); linkCell.setHyperlink(hyperLink); linkCell.setCellStyle(hlinkstyle); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i+=2) { sheet.autoSizeColumn(i); } selectStmt.close(); excelOutputStream = new FileOutputStream(excelFile); workbook.write(excelOutputStream); excelOutputStream.close(); System.out.println("Printed out " + dbTable); } catch (SQLException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(IndeedScraper.class.getName()).log(Level.SEVERE, null, ex); } } */ public void appendDBTableToExcel(String dbTable) { rowNumber = 0; try { connect = connectDatabase(); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("Select * from " + dbTable); File file = new File("exceldatabase.xlsx"); if (file.exists()) { closeDBSession(); callExistingExcel(dbTable); } else { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(dbTable); while (resultSet.next()) { XSSFRow row = spreadsheet.createRow(rowNumber); XSSFCell titleCell = row.createCell(0); titleCell.setCellValue(resultSet.getString("title")); XSSFCell linkCell = row.createCell(1); linkCell.setCellValue(resultSet.getString("link")); XSSFCell dateCell = row.createCell(2); dateCell.setCellValue(resultSet.getString("date")); System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link") + resultSet.getString("date")); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i += 2) { spreadsheet.autoSizeColumn(i); } FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx")); workbook.write(out); out.close(); System.out.println("exceldatabase.xlsx written successfully"); } } catch (SQLException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } } //create the excel sheets private void callExistingExcel(String dbTable) throws FileNotFoundException, IOException, SQLException { rowNumber = 0; connect = connectDatabase(); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("Select * from " + dbTable); File file = new File("exceldatabase.xlsx"); FileInputStream fIP = new FileInputStream(file); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); try { if (file.isFile() && file.exists()) { System.out.println("openworkbook.xlsx file open successfully."); XSSFSheet spreadsheet = workbook.createSheet(dbTable); while (resultSet.next()) { XSSFRow row = spreadsheet.createRow(rowNumber); XSSFCell titleCell = row.createCell(0); titleCell.setCellValue(resultSet.getString("title")); XSSFCell linkCell = row.createCell(1); linkCell.setCellValue(resultSet.getString("link")); XSSFCell dateCell = row.createCell(2); dateCell.setCellValue(resultSet.getString("date")); System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link") + resultSet.getString("date")); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i += 2) { spreadsheet.autoSizeColumn(i); } FileOutputStream out = new FileOutputStream("exceldatabase.xlsx"); workbook.write(out); out.close(); System.out.println("Successfully written"); } else { System.out.println("Error to open openworkbook.xlsx file."); } } catch (IllegalArgumentException ex) { } } }