LightPIT: src/main/java/de/uapcore/lightpit/dao/postgres/PGProjectDao.java@b3f14cd4f3ab

     1 /*

     2  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

     3  *

     4  * Copyright 2018 Mike Becker. All rights reserved.

     5  *

     6  * Redistribution and use in source and binary forms, with or without

     7  * modification, are permitted provided that the following conditions are met:

     8  *

     9  *   1. Redistributions of source code must retain the above copyright

    10  *      notice, this list of conditions and the following disclaimer.

    11  *

    12  *   2. Redistributions in binary form must reproduce the above copyright

    13  *      notice, this list of conditions and the following disclaimer in the

    14  *      documentation and/or other materials provided with the distribution.

    15  *

    16  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"

    17  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE

    18  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE

    19  * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE

    20  * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR

    21  * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF

    22  * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS

    23  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN

    24  * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)

    25  * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE

    26  * POSSIBILITY OF SUCH DAMAGE.

    27  *

    28  */

    29 package de.uapcore.lightpit.dao.postgres;

    31 import de.uapcore.lightpit.dao.Functions;

    32 import de.uapcore.lightpit.dao.ProjectDao;

    33 import de.uapcore.lightpit.entities.IssueSummary;

    34 import de.uapcore.lightpit.entities.Project;

    35 import de.uapcore.lightpit.entities.User;

    37 import java.sql.Connection;

    38 import java.sql.PreparedStatement;

    39 import java.sql.ResultSet;

    40 import java.sql.SQLException;

    41 import java.util.List;

    43 import static de.uapcore.lightpit.dao.Functions.setForeignKeyOrNull;

    44 import static de.uapcore.lightpit.dao.Functions.setStringOrNull;

    46 public final class PGProjectDao implements ProjectDao {

    48     private final PreparedStatement insert, update, list, find, findByNode;

    49     private final PreparedStatement issue_summary;

    51     public PGProjectDao(Connection connection) throws SQLException {

    52         final var query = "select projectid, name, node, description, repourl, " +

    53                 "userid, username, lastname, givenname, mail " +

    54                 "from lpit_project " +

    55                 "left join lpit_user owner on lpit_project.owner = owner.userid ";

    57         list = connection.prepareStatement(query + " order by name");

    59         find = connection.prepareStatement(query + " where projectid = ?");

    60         findByNode = connection.prepareStatement(query + " where node = ?");

    62         issue_summary = connection.prepareStatement(

    63                 "select phase, count(*) as total "+

    64                         "from lpit_issue " +

    65                         "join lpit_issue_phases using(status) " +

    66                         "where project = ? "+

    67                         "group by phase "

    68         );

    70         insert = connection.prepareStatement(

    71                 "insert into lpit_project (name, node, description, repourl, owner) values (?, ?, ?, ?, ?)"

    72         );

    73         update = connection.prepareStatement(

    74                 "update lpit_project set name = ?, node = ?, description = ?, repourl = ?, owner = ? where projectid = ?"

    75         );

    76     }

    78     private static Project mapColumns(ResultSet result) throws SQLException {

    79         final var proj = new Project(result.getInt("projectid"));

    80         proj.setName(result.getString("name"));

    81         proj.setNode(result.getString("node"));

    82         proj.setDescription(result.getString("description"));

    83         proj.setRepoUrl(result.getString("repourl"));

    84         proj.setOwner(PGUserDao.mapColumns(result));

    86         return proj;

    87     }

    89     public IssueSummary getIssueSummary(Project project) throws SQLException {

    90         issue_summary.setInt(1, project.getId());

    91         final var result = issue_summary.executeQuery();

    92         final var summary = new IssueSummary();

    93         while (result.next()) {

    94             final var phase = result.getInt("phase");

    95             final var total = result.getInt("total");

    96             switch(phase) {

    97                 case 0:

    98                     summary.setOpen(total);

    99                     break;

   100                 case 1:

   101                     summary.setActive(total);

   102                     break;

   103                 case 2:

   104                     summary.setDone(total);

   105                     break;

   106             }

   107         }

   108         return summary;

   109     }

   111     private static int setColumns(PreparedStatement stmt, Project instance) throws SQLException {

   112         int column = 0;

   113         stmt.setString(++column, instance.getName());

   114         stmt.setString(++column, instance.getNode());

   115         setStringOrNull(stmt, ++column, instance.getDescription());

   116         setStringOrNull(stmt, ++column, instance.getRepoUrl());

   117         setForeignKeyOrNull(stmt, ++column, instance.getOwner(), User::getId);

   118         return column;

   119     }

   121     @Override

   122     public void save(Project instance) throws SQLException {

   123         setColumns(insert, instance);

   124         insert.executeUpdate();

   125     }

   127     @Override

   128     public boolean update(Project instance) throws SQLException {

   129         if (instance.getId() < 0) return false;

   130         int column = setColumns(update, instance);

   131         update.setInt(++column, instance.getId());

   132         return update.executeUpdate() > 0;

   133     }

   135     @Override

   136     public List<Project> list() throws SQLException {

   137         return Functions.list(list, PGProjectDao::mapColumns);

   138     }

   140     @Override

   141     public Project find(int id) throws SQLException {

   142         find.setInt(1, id);

   143         return Functions.find(find, PGProjectDao::mapColumns);

   144     }

   146     @Override

   147     public Project findByNode(String node) throws SQLException {

   148         findByNode.setString(1, node);

   149         return Functions.find(findByNode, PGProjectDao::mapColumns);

   150     }

   151 }

Mercurial > hg > LightPIT / file revision