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.File; import java.io.FileInputStream; 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.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; import javax.transaction.Transactional; import static org.apache.commons.lang3.ArrayUtils.removeElement; import static org.apache.commons.lang3.StringUtils.isAllUpperCase; import org.apache.poi.hwpf.HWPFDocument; import org.apache.poi.hwpf.extractor.WordExtractor; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import projekt.entity.Group1; import projekt.service.ReadDataFromWordService; import projekt.service.GroupService; @Service @Transactional public class ReadDataFromWordServiceImpl implements ReadDataFromWordService { @Autowired private GroupService groupService; @Override public void getData() { String FilePath = "C:/Users/Lenovo/Documents/NetBeansProjects/SoftwareArchitectureProject-master/src/main/java/projekt/nimekiri_test.doc"; FileInputStream fis; try { fis = new FileInputStream(new File(FilePath)); HWPFDocument doc = new HWPFDocument(fis); WordExtractor extractor = new WordExtractor(doc); Connection conn = DriverManager.getConnection( "jdbc:postgresql://dev.vk.edu.ee:5432/GroupWork?currentSchema=project", "t131566", "t131566"); String text = extractor.getText(); String strippedText = extractor.stripFields(text).replace("\r\n\r\n", "\n").replace("\t", " ") .replace("\r\n", "\n"); String[] paragraphs = strippedText.split("\n"); String code = ""; List<String> groupNames = groupService.getGroupNames(); for (int i = 8; i < paragraphs.length; i++) { String line = paragraphs[i].replace("*", "").replace("OK", "").replace("TREV", "").replace("REV", ""); int index = 0; String jrk = ""; if (!paragraphs[i].trim().isEmpty() && paragraphs[i].substring(0, 3).contains("Jrk")) { String groupCode = paragraphs[i].substring(17, 21); jrk = paragraphs[i]; index = strippedText.indexOf(jrk); String groupName = ""; int j = 1; do { if (!paragraphs[i - j].trim().isEmpty()) { if (Character.isUpperCase(paragraphs[i - j].charAt(3))) { groupName = paragraphs[i - j]; if (groupName.contains("(KAUGPE)")) { groupName = groupName.replace("(KAUGPE)", "").trim(); } } } j++; } while (!paragraphs[i - j].trim().isEmpty()); PreparedStatement preparedStatementGetGroup = conn.prepareStatement( "SELECT id FROM project.group where name is null and groupcode like ?"); preparedStatementGetGroup.setString(1, groupCode + "%"); ResultSet resultGroup = preparedStatementGetGroup.executeQuery(); while (resultGroup.next()) { int groupId = resultGroup.getInt(1); PreparedStatement preparedStatementSetGroupName = conn .prepareStatement("UPDATE project.group SET name=? where id=?"); preparedStatementSetGroupName.setString(1, groupName.replace(" ", " ")); preparedStatementSetGroupName.setInt(2, groupId); preparedStatementSetGroupName.executeUpdate(); } } } for (int i = 8; i < paragraphs.length; i++) { String line = paragraphs[i].replace("*", "").replace("OK", "").replace("TREV", "").replace("REV", ""); /* int index = 0; String jrk = "";*/ if (!paragraphs[i].trim().isEmpty() && paragraphs[i].substring(0, 3).contains("Jrk")) { /* String groupCode = paragraphs[i].substring(17, 21);*/ code = line.substring(line.indexOf(":") + 1, line.indexOf(":") + 11); code = code.replace("", "").replace(" - ", "").replace(" ", ""); } if (!line.trim().isEmpty() && !line.contains("KOOD") && !line.contains("KAUGPE") && !line.contains("lipilane") && !groupNames.contains(line) && !line.contains("Jrk") && !isAllUpperCase(line)) { String[] splittedLine = line.split(" "); String studentLastname = ""; List<String> newSplittedLine = new ArrayList<String>(); for (String item : splittedLine) { if (!item.isEmpty()) { newSplittedLine.add(item); } } if (newSplittedLine.size() >= 4) { PreparedStatement preparedStatementGetStudent = conn .prepareStatement("SELECT * FROM project.student where code=?"); if (newSplittedLine.size() == 4) { preparedStatementGetStudent.setString(1, newSplittedLine.get(2)); studentLastname = newSplittedLine.get(1); } else if (newSplittedLine.size() == 5) { preparedStatementGetStudent.setString(1, newSplittedLine.get(3)); studentLastname = newSplittedLine.get(1) + " " + newSplittedLine.get(2); } ResultSet resultStudent = preparedStatementGetStudent.executeQuery(); if (!resultStudent.next()) { PreparedStatement preparedStatementGetPerson = conn.prepareStatement( "SELECT * FROM project.person where firstname=? and lastname=?"); preparedStatementGetPerson.setString(1, newSplittedLine.get(0)); preparedStatementGetPerson.setString(2, studentLastname); ResultSet resultPersonExists = preparedStatementGetPerson.executeQuery(); if (!resultPersonExists.next()) { PreparedStatement preparedStatementNewPerson = conn.prepareStatement( "INSERT INTO project.person (firstname,lastname,roleid) VALUES (?,?,?)"); if (newSplittedLine.size() == 4) { preparedStatementNewPerson.setString(1, newSplittedLine.get(0)); preparedStatementNewPerson.setString(2, studentLastname); preparedStatementNewPerson.setInt(3, 2); } else if (newSplittedLine.size() == 5) { preparedStatementNewPerson.setString(1, newSplittedLine.get(0)); preparedStatementNewPerson.setString(2, studentLastname); preparedStatementNewPerson.setInt(3, 2); } preparedStatementNewPerson.executeUpdate(); PreparedStatement preparedStatementLastPerson = conn.prepareStatement( "SELECT id FROM project.person where firstname=? and lastname=? and roleid=?"); preparedStatementLastPerson.setString(1, newSplittedLine.get(0)); preparedStatementLastPerson.setString(2, studentLastname); preparedStatementLastPerson.setInt(3, 2); Integer personId = 0; ResultSet resultPerson = preparedStatementLastPerson.executeQuery(); if (resultPerson.next()) { personId = resultPerson.getInt(1); System.out.println("GROUP CODE " + code); Group1 group = groupService.getByGroupcode(code); if (group != null) { System.out.println("GROUP ID " + group.getId()); PreparedStatement preparedStatementSetStudent = conn.prepareStatement( "INSERT INTO project.student (personid,groupid,code) values(?,?,?)"); preparedStatementSetStudent.setInt(1, personId); preparedStatementSetStudent.setInt(2, group.getId()); preparedStatementSetStudent.setString(3, code); preparedStatementSetStudent.executeUpdate(); System.out.println("NEW STUDENT " + personId); } else { System.out.println("GROUP WAS NULL "); PreparedStatement preparedStatementGroup = conn.prepareStatement( "INSERT INTO project.group (groupcode) VALUES (?)"); preparedStatementGroup.setString(1, code); preparedStatementGroup.executeUpdate(); System.out.println("NEW GROUP " + code); code = code.replace(" ", ""); PreparedStatement preparedStatementLastGroup = conn .prepareStatement("SELECT id FROM project.group where groupcode=?"); preparedStatementLastGroup.setString(1, code); System.out.println("SELECT id FROM project.group where groupcode=" + code); int groupId = 0; ResultSet resultLastGroup = preparedStatementLastGroup.executeQuery(); if (resultLastGroup.next()) { PreparedStatement preparedStatementStudentExist = conn.prepareStatement( "SELECT * FROM project.student where personid=?"); preparedStatementStudentExist.setInt(1, personId); ResultSet studentExists = preparedStatementStudentExist.executeQuery(); if (!studentExists.next()) { groupId = resultLastGroup.getInt(1); PreparedStatement preparedStatementSetStudent = conn .prepareStatement( "INSERT INTO project.student (personid,groupid,code) values(?,?,?)"); preparedStatementSetStudent.setInt(1, personId); preparedStatementSetStudent.setInt(2, groupId); preparedStatementSetStudent.setString(3, code); preparedStatementSetStudent.executeUpdate(); System.out.println("NEW STUDENT " + personId); } } } } } } } // System.out.println(); } } conn.commit(); conn.close(); } catch (IOException e) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, e); } catch (SQLException ex) { Logger.getLogger(ReadDataFromWordServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } }