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 com.att.pirates.controller; import com.att.pirates.model.Company; import com.att.pirates.model.InsightProject; import com.att.pirates.model.InsightRelease; import com.att.pirates.util.DBUtility; import com.att.pirates.util.PiratesConstants; import org.apache.commons.lang.StringUtils; import com.att.pirates.util.PropertyReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.LinkedList; import java.util.List; import javax.servlet.http.HttpServletRequest; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import org.apache.log4j.Logger; /** * * @author mercychan */ @Controller public class GlobalDataController { private static Logger logger = Logger.getLogger(GlobalDataController.class); private static String msgHeader = "[Merce]: "; @RequestMapping(value = "/getProjectHierarchies", method = RequestMethod.GET) public @ResponseBody List<String> GetProjectHierarchies() { /* Download the driver JAR from the link provided by Olaf and add it to your local Maven repository with; mvn install:install-file -Dfile=sqljdbc4.jar -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.0 -Dpackaging=jar Then add it to your project with; <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> */ // logger.error(msgHeader + "GetProjectHierarchies called"); List<String> projects = new ArrayList<String>(); Statement stmt = null; ResultSet rs = null; Connection conn = null; // logger.error(msgHeader + "after declaring variables"); try { /* String userName = "merce"; String password = "1234"; String url = "jdbc:sqlserver://173.203.67.29;databaseName=CCMProjectTracker"; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection conn = DriverManager.getConnection(url, userName, password); */ PropertyReader reader = new PropertyReader(); String forName = reader.readPropertyByName(PiratesConstants.CLASSFORNAME); if (StringUtils.isBlank(forName) || StringUtils.isEmpty(forName)) { throw new Exception("forName is empty from property file"); } String serverName = reader.readPropertyByName(PiratesConstants.SERVERNAME); String databaseName = reader.readPropertyByName(PiratesConstants.DATABASENAME); String user = reader.readPropertyByName(PiratesConstants.USER); String password = reader.readPropertyByName(PiratesConstants.PASSWORD); if (StringUtils.isBlank(serverName) || StringUtils.isEmpty(databaseName) || StringUtils.isBlank(user) || StringUtils.isEmpty(password)) { throw new Exception("connection string is empty from property file"); } String url = serverName + ";" + "databaseName=" + databaseName; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn = DriverManager.getConnection(url, user, password); // SQL query command String SQL = "SELECT UPPER(FullName) as FullName FROM ATT_Employees"; stmt = conn.createStatement(); rs = stmt.executeQuery(SQL); while (rs.next()) { String val = rs.getString("FullName"); // logger.error(msgHeader + val); projects.add(val); } } catch (SQLException e) { logger.error(e.getMessage()); } catch (Exception e) { logger.error(e.getMessage()); } finally { try { if (rs != null) rs.close(); } catch (Exception e) { } ; try { if (stmt != null) stmt.close(); } catch (Exception e) { } ; try { if (conn != null) conn.close(); } catch (Exception e) { } ; } return projects; } @RequestMapping(value = "/GlobalData/DoSearch", method = RequestMethod.POST) public String GlobalNavBarPrismIdSearch(HttpServletRequest request) { String searchVar = request.getParameter("query"); // logger.error("GlobalNavBarPrismIdSearch.. " + searchVar); return "redirect:/projects/index/" + searchVar; } @RequestMapping(value = "/GetAllProjectPrismIds", method = RequestMethod.GET) public @ResponseBody List<String> GetAllProjectPrismIds(HttpServletRequest request) throws Exception { String sessionUser = (String) request.getSession().getAttribute("loginUser"); if (sessionUser == null || sessionUser.isEmpty() || "Not Logged In".equalsIgnoreCase(sessionUser)) { request.getSession().setAttribute("loginUser", "Not Logged In"); // throw new Exception("[Merce]: login user not defined !"); } // List<InsightRelease> prismIds = GetProjectHierarchiesPOJO(sessionUser); List<String> rcs = GetAllPRISMIDs(); // for(InsightRelease r: prismIds){ // for(InsightProject p: r.getProjects()){ // rcs.add(p.getPrismId()); // } // } return rcs; } public static boolean isATTEmployeeITUPRoleEmpty(String UUID) { ResultSet rs = null; Connection conn = null; PreparedStatement preparedStatement = null; boolean rc = false; try { conn = DBUtility.getDBConnection(); // SQL query command String SQL = " select * from ATTEmployeeArtifacts where uuid = ? "; preparedStatement = conn.prepareStatement(SQL); preparedStatement.setString(1, UUID); rs = preparedStatement.executeQuery(); if (rs.next()) { rc = true; } } catch (SQLException e) { logger.error(e.getMessage()); } catch (Exception e) { logger.error(e.getMessage()); } finally { try { if (rs != null) rs.close(); } catch (Exception e) { } ; try { if (preparedStatement != null) preparedStatement.close(); } catch (Exception e) { } ; try { if (conn != null) conn.close(); } catch (Exception e) { } ; } return rc; } public static List<String> GetAllPRISMIDs() { List<String> apps = new ArrayList<String>(); ResultSet rs = null; Connection conn = null; PreparedStatement preparedStatement = null; try { conn = DBUtility.getDBConnection(); // SQL query command String SQL = " SELECT [PRISMId] " + " FROM [Projects] "; preparedStatement = conn.prepareStatement(SQL); rs = preparedStatement.executeQuery(); while (rs.next()) { String appName = rs.getString("PRISMId"); apps.add(appName); } } catch (SQLException e) { logger.error(e.getMessage()); } catch (Exception e) { logger.error(e.getMessage()); } finally { try { if (rs != null) rs.close(); } catch (Exception e) { } ; try { if (preparedStatement != null) preparedStatement.close(); } catch (Exception e) { } ; try { if (conn != null) conn.close(); } catch (Exception e) { } ; } return apps; } public static List<InsightRelease> GetProjectHierarchiesPOJO(String UUID) { // logger.error(msgHeader + "GetProjectHierarchies called"); boolean hasITUProles = isATTEmployeeITUPRoleEmpty(UUID); List<InsightRelease> rc = new ArrayList<InsightRelease>(); List<InsightProject> pc = new ArrayList<InsightProject>(); ResultSet rs = null; Connection conn = null; PreparedStatement selectRow = null; try { PropertyReader reader = new PropertyReader(); String forName = reader.readPropertyByName(PiratesConstants.CLASSFORNAME); if (StringUtils.isBlank(forName) || StringUtils.isEmpty(forName)) { throw new Exception("forName is empty from property file"); } String serverName = reader.readPropertyByName(PiratesConstants.SERVERNAME); String databaseName = reader.readPropertyByName(PiratesConstants.DATABASENAME); String user = reader.readPropertyByName(PiratesConstants.USER); String password = reader.readPropertyByName(PiratesConstants.PASSWORD); if (StringUtils.isBlank(serverName) || StringUtils.isEmpty(databaseName) || StringUtils.isBlank(user) || StringUtils.isEmpty(password)) { throw new Exception("connection string is empty from property file"); } String url = serverName + ";" + "databaseName=" + databaseName; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn = DriverManager.getConnection(url, user, password); // SQL query command String SQL = ""; if (!hasITUProles) { logger.error(msgHeader + "hasITUProles is false !"); SQL = " select r.ReleaseName, p.PRISMId, p.ProjectName " + " from Releases r join Projects p " + " on p.ReleaseId = r.ReleaseId " + " join DomainProjects dp on dp.PRISMId = p.PRISMId " + " join ATTEmployeeDomains ad on dp.domainId = ad.domainId " + " join ATTEmployeeProjectStatus eps on p.statusid = eps.statusid " + " where ad.UUID = ? and eps.UUID = ? " + " order by r.ReleaseId asc, p.PRISMId "; } else { logger.error(msgHeader + "hasITUProles is true !"); SQL = " select r.ReleaseName, p.PRISMId, p.ProjectName " + " from Releases r join Projects p " + " on p.ReleaseId = r.ReleaseId " + " join DomainProjects dp on dp.PRISMId = p.PRISMId " + " join ATTEmployeeDomains ad on dp.domainId = ad.domainId " + " join ATTEmployeeProjectStatus eps on p.statusid = eps.statusid " + " join " + " (" + " select distinct ao.PrismId " + " from AppProjectArtifactOwners ao join " + " ATTEmployeeArtifacts aa on aa.UUID=ao.UUID " + " and aa.ArtifactName = ao.ArtifactName " + " where aa.uuid = ? \n " + " ) as tbl on tbl.prismId = dp.PRISMId " + " where ad.UUID = ? and eps.UUID = ? order by r.ReleaseId asc, p.PRISMId "; } selectRow = conn.prepareStatement(SQL); selectRow.setString(1, UUID); selectRow.setString(2, UUID); if (hasITUProles) { selectRow.setString(3, UUID); } rs = selectRow.executeQuery(); while (rs.next()) { String releaseName = rs.getString("ReleaseName"); String prismId = rs.getString("PRISMId"); String projectName = rs.getString("ProjectName"); // create a project and add it to list InsightProject p = new InsightProject(); p.setProjectName(projectName); p.setPrismId(prismId); p.setReleaseName(releaseName); pc.add(p); boolean rlExists = false; // create a release and add it to list if it's not already added if (rc.size() > 0) { for (InsightRelease r : rc) { if (r.getReleaseName().equalsIgnoreCase(releaseName)) { rlExists = true; break; } } } if (!rlExists) { InsightRelease rl = new InsightRelease(); rl.setReleaseName(releaseName); rc.add(rl); } // logger.error(msgHeader + releaseName + "," + prismId + "," + projectName); } if (rc.size() > 0) { for (InsightRelease r : rc) { if (pc.size() > 0) { for (InsightProject p : pc) { if (p.getReleaseName().equalsIgnoreCase(r.getReleaseName())) { r.getProjects().add(p); } } } } } } catch (SQLException e) { logger.error(e.getMessage()); } catch (Exception e) { logger.error(e.getMessage()); } finally { try { if (rs != null) rs.close(); } catch (Exception e) { } ; try { if (selectRow != null) selectRow.close(); } catch (Exception e) { } ; try { if (conn != null) conn.close(); } catch (Exception e) { } ; } return rc; } /// <summary> /// Method that returns all companies used in this example /// </summary> /// <returns>List of companies</returns> /// <summary> /// Singleton collection of companies /// </summary> private static List<Company> CompanyData = null; public static List<Company> GetCompanies(String prismId) { // application (name), notes (address), createdby (town), dateCreated (dateCreated) if (CompanyData != null) { CompanyData = null; } if (CompanyData == null) { CompanyData = getCummulativeNotesForPrismId(prismId); } return CompanyData; } private static LinkedList<Company> getCummulativeNotesForPrismId(String prismId) { LinkedList<Company> notes = new LinkedList<Company>(); ResultSet rs = null; Connection conn = null; PreparedStatement preparedStatement = null; try { conn = DBUtility.getDBConnection(); // SQL query command String SQL = " SELECT NoteId, ApplicationName " + " ,n.Notes " + " ,n.DateCreated " + " ,e.FullName " + " FROM CummulativeNotes n Join ATT_Employees e " + " On e.UUID = n.CreatedBy " + " where n.PRISMId = ? " + " Order By n.DateCreated Desc "; preparedStatement = conn.prepareStatement(SQL); preparedStatement.setString(1, prismId); rs = preparedStatement.executeQuery(); while (rs.next()) { String AppName = rs.getString("ApplicationName"); String note = rs.getString("Notes"); String dateCreated = rs.getString("DateCreated"); String createdBy = rs.getString("FullName"); int noteId = rs.getInt("NoteId"); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); SimpleDateFormat yFormat = new SimpleDateFormat("MM/dd/yyyy"); dateCreated = yFormat.format(format.parse(dateCreated)); String rowId = "row_" + Integer.toString(noteId); String edit = "<a href='#' class='glyphicon glyphicon-pencil' data-toggle='modal' data-target='#updateNoteModal' onclick='return NoteRowButtonClickedPerNote(this);' id='btnNoteEdit_" + Integer.toString(noteId) + "' ></a> "; // Company p = new Company(Integer.toString(noteId),AppName,note,createdBy,dateCreated, rowId); Company p = new Company(rowId, AppName, note, createdBy, dateCreated, rowId, edit); notes.add(p); } } catch (SQLException e) { logger.error(e.getMessage()); } catch (Exception e) { logger.error(e.getMessage()); } finally { try { if (rs != null) rs.close(); } catch (Exception e) { } ; try { if (preparedStatement != null) preparedStatement.close(); } catch (Exception e) { } ; try { if (conn != null) conn.close(); } catch (Exception e) { } ; } return notes; } }