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 projekt.servise.impl; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; import javax.transaction.Transactional; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Configurable; import org.springframework.stereotype.Controller; import org.springframework.stereotype.Service; import projekt.entity.Course; import projekt.service.CourseService; import projekt.service.ReadDataFromExcelService; /** * * @author Lenovo */ @Service @Transactional public class ReadDataFromExcelServiceImpl implements ReadDataFromExcelService { @Autowired CourseService courseService; @Override public void getData() { try { POIFSFileSystem fs = null; try { fs = new POIFSFileSystem(new FileInputStream( "C:/Users/Lenovo/Documents/NetBeansProjects/SoftwareArchitectureProject-master/src/main/java/projekt/Koormused_test.xls")); } catch (FileNotFoundException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } HSSFWorkbook wb = null; try { wb = new HSSFWorkbook(fs); } catch (IOException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; int rows = sheet.getPhysicalNumberOfRows(); int cols = 0; int tmp = 0; for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } Connection conn = DriverManager.getConnection( "jdbc:postgresql://dev.vk.edu.ee:5432/GroupWork?currentSchema=project", "t131566", "t131566"); for (int r = 11; r < rows + 11; r++) { row = sheet.getRow(r); if (row != null) { PreparedStatement preparedStatementRoles; ResultSet resultRoles = null; try { preparedStatementRoles = conn.prepareStatement("SELECT * FROM project.role"); resultRoles = preparedStatementRoles.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultRoles.next()) { conn.setAutoCommit(false); PreparedStatement preparedStatementRole = conn .prepareStatement("INSERT INTO project.role (rolename) VALUES (?)"); preparedStatementRole.setString(1, "teacher"); preparedStatementRole.addBatch(); preparedStatementRole.setString(1, "student"); preparedStatementRole.addBatch(); preparedStatementRole.setString(1, "admin"); preparedStatementRole.addBatch(); preparedStatementRole.executeBatch(); conn.commit(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } PreparedStatement preparedStatementLanguages = null; try { preparedStatementLanguages = conn .prepareStatement("SELECT * FROM project.language where name=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementLanguages.setString(1, StringUtils.trim(row.getCell(13).getStringCellValue())); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } ResultSet resultLanguages = null; try { resultLanguages = preparedStatementLanguages.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultLanguages.next()) { PreparedStatement preparedStatementLanguage = conn .prepareStatement("INSERT INTO project.language (name) VALUES (?)"); preparedStatementLanguage.setString(1, StringUtils.trim(row.getCell(13).getStringCellValue())); preparedStatementLanguage.executeUpdate(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } PreparedStatement preparedStatementTeacher = null; try { preparedStatementTeacher = conn .prepareStatement("SELECT * FROM project.person where lastname=? and firstname=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } String firstname = row.getCell(12).getStringCellValue().split("\\.")[0]; String lastname = row.getCell(12).getStringCellValue().split("\\.")[1]; try { preparedStatementTeacher.setString(1, lastname); preparedStatementTeacher.setString(2, firstname); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } ResultSet resultTeacher = null; try { resultTeacher = preparedStatementTeacher.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultTeacher.next()) { PreparedStatement preparedStatementNewTeacher = conn.prepareStatement( "INSERT INTO project.person (firstname,lastname,roleid) VALUES (?,?,?)"); PreparedStatement preparedStatementTeacherRole = conn .prepareStatement("SELECT * FROM project.role where rolename='teacher'"); int roleId = 0; ResultSet resultTeacherRole = preparedStatementTeacherRole.executeQuery(); if (resultTeacherRole.next()) { roleId = resultTeacherRole.getInt(1); } preparedStatementNewTeacher.setString(1, firstname); preparedStatementNewTeacher.setString(2, lastname); preparedStatementNewTeacher.setInt(3, roleId); preparedStatementNewTeacher.executeUpdate(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } String courseCode = row.getCell(2).getStringCellValue(); Course course = null; try { course = courseService.getByCode(courseCode); } catch (Exception e) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, e); } int courseId = 0; if (course == null) { PreparedStatement preparedStatementCourse = null; try { preparedStatementCourse = conn.prepareStatement( "INSERT INTO project.course (code,name,lectureship) VALUES (?,?,?)"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementCourse.setString(1, row.getCell(2).getStringCellValue()); preparedStatementCourse.setString(2, row.getCell(3).getStringCellValue()); preparedStatementCourse.setString(3, row.getCell(1).getStringCellValue()); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementCourse.executeUpdate(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } else { courseId = course.getId(); } if (courseId != 0) { PreparedStatement preparedStatementLanguageId = null; try { preparedStatementLanguageId = conn .prepareStatement("SELECT * FROM project.language where name=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementLanguageId.setString(1, row.getCell(13).getStringCellValue()); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } int languageId = 0; ResultSet resultLanguageId = null; try { resultLanguageId = preparedStatementLanguageId.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (resultLanguageId.next()) { languageId = resultLanguageId.getInt(1); PreparedStatement preparedStatementTeacherId = conn.prepareStatement( "SELECT * FROM project.person where firstname=? and lastname=?"); preparedStatementTeacherId.setString(1, firstname); preparedStatementTeacherId.setString(2, lastname); int teacherId = 0; ResultSet resultTeacherId = preparedStatementTeacherId.executeQuery(); if (resultTeacherId.next()) { teacherId = resultTeacherId.getInt(1); PreparedStatement preparedStatementExistingCourseData = conn .prepareStatement("SELECT * FROM project.coursedata where courseid=? " + "and practice=? and excercise=? and lecture=? and languageid=? and teacherid=?"); preparedStatementExistingCourseData.setInt(1, courseId); int practice = (int) (row.getCell(6) != null ? row.getCell(6).getNumericCellValue() : 0); int lecture = (int) (row.getCell(5) != null ? row.getCell(5).getNumericCellValue() : 0); int exercise = (int) (row.getCell(7) != null ? row.getCell(7).getNumericCellValue() : 0); preparedStatementExistingCourseData.setInt(2, practice); preparedStatementExistingCourseData.setInt(3, exercise); preparedStatementExistingCourseData.setInt(4, lecture); preparedStatementExistingCourseData.setInt(5, languageId); preparedStatementExistingCourseData.setInt(6, teacherId); ResultSet resultExistingCourseData = preparedStatementExistingCourseData .executeQuery(); if (!resultExistingCourseData.next()) { PreparedStatement preparedStatementCourseData = conn .prepareStatement("INSERT INTO project.coursedata " + "(courseid,practice,lecture,excercise,lecturesperweek,languageid,semester,teacherid) " + "VALUES (?,?,?,?,?,?,?,?)"); preparedStatementCourseData.setInt(1, courseId); preparedStatementCourseData.setInt(2, practice); preparedStatementCourseData.setInt(3, lecture); preparedStatementCourseData.setInt(4, exercise); preparedStatementCourseData.setFloat(5, (float) row.getCell(9).getNumericCellValue()); preparedStatementCourseData.setInt(6, (int) languageId); preparedStatementCourseData.setString(7, row.getCell(14).getStringCellValue()); preparedStatementCourseData.setInt(8, (int) teacherId); preparedStatementCourseData.executeUpdate(); int courseDataId = 0; PreparedStatement preparedStatementLastCourseData = conn .prepareStatement("SELECT id FROM project.coursedata where " + "courseid=? and practice=? and lecture=? and excercise=? and lecturesperweek=? and languageid=? and semester=? and teacherid=?"); preparedStatementLastCourseData.setInt(1, courseId); preparedStatementLastCourseData.setInt(2, practice); preparedStatementLastCourseData.setInt(3, lecture); preparedStatementLastCourseData.setInt(4, exercise); preparedStatementLastCourseData.setFloat(5, (float) row.getCell(9).getNumericCellValue()); preparedStatementLastCourseData.setInt(6, (int) languageId); preparedStatementLastCourseData.setString(7, row.getCell(14).getStringCellValue()); preparedStatementLastCourseData.setInt(8, (int) teacherId); ResultSet resultLastCourseData = preparedStatementLastCourseData .executeQuery(); if (resultLastCourseData.next()) { courseDataId = resultLastCourseData.getInt(1); String[] groupCodes = row.getCell(4).getStringCellValue().split(" "); for (int i = 0; i < groupCodes.length; i++) { PreparedStatement preparedStatementGroupCode = conn .prepareStatement( "SELECT * FROM project.group where groupcode=?"); preparedStatementGroupCode.setString(1, groupCodes[i]); ResultSet resultGroupCode = preparedStatementGroupCode .executeQuery(); if (!resultGroupCode.next()) { PreparedStatement preparedStatementGroup = conn .prepareStatement( "INSERT INTO project.group (groupcode) VALUES (?)"); preparedStatementGroup.setString(1, groupCodes[i]); preparedStatementGroup.executeUpdate(); PreparedStatement preparedStatementLastGroup = conn .prepareStatement( "SELECT id FROM project.group where groupcode=?"); preparedStatementLastGroup.setString(1, groupCodes[i]); int groupId = 0; ResultSet resultLastGroup = preparedStatementLastGroup .executeQuery(); if (resultLastGroup.next()) { groupId = resultLastGroup.getInt(1); PreparedStatement preparedStatementGroupCourseData = conn .prepareStatement( "INSERT INTO project.groupcoursedata (groupid,goursedataid) VALUES (?,?)"); preparedStatementGroupCourseData.setInt(1, groupId); preparedStatementGroupCourseData.setInt(2, courseDataId); preparedStatementGroupCourseData.executeUpdate(); } } } } } } } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } } } } catch (SQLException ioe) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ioe); } } }