D-Net

import org.springframework.transaction.annotation.Propagation;

import org.springframework.transaction.annotation.Transactional;

    @Autowired

    private long expirationOk;

    private final String SEARCH_PROJECTS = "select id, acronym, title, \"grant\" from project where lower(acronym) like lower(?) or lower(title) like lower(?) or lower(\"grant\") like lower(?)";

                    "left join project_coordinator pc on pc.project=p.id\n" +

    private final String GET_PROJECTS_FOR_ORGANIZATION =

            "select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +

                    "from project  p\n" +

                    "left join project_organization po on po.project=p.id\n" +

                    "left join project_coordinator pc on pc.project=p.id\n" +

                    "where po.organization=? and p.enddate > now() - (? || ' months')::interval and p.enddate < now() \n" +

                    "group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";

    private final String GET_FUTURE_PROJECTS_FOR_ORGANIZATION=

            "select id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source, array_agg(distinct pc.coordinator) as coordinators, array_agg(distinct po.organization) as organizations\n" +

                    "from project  p\n" +

                    "left join project_organization po on po.project=p.id\n" +

                    "left join project_coordinator pc on pc.project=p.id\n" +

                    "where po.organization=? and p.enddate > now() \n" +

                    "group by id, acronym, title, funder, fundingstream, scientificarea, call, \"grant\", startdate, enddate, sc39, url, source";

    private RowMapper<Project> rowMapper = new RowMapper<Project>() {

        @Override

        public Project mapRow(ResultSet rs, int rowNum) throws SQLException {

            Project project = new Project();

            project.setId(rs.getString("id"));

            project.setTitle(rs.getString("title"));

            project.setAcronym(rs.getString("acronym"));

            project.setFunder(rs.getString("funder"));

            project.setFundingString(rs.getString("fundingstream"));

            project.setScientificArea(rs.getString("scientificarea"));

            project.setCall(rs.getString("call"));

            project.setGrant(rs.getString("grant"));

            project.setStartDate(rs.getTimestamp("startdate"));

            project.setEndDate(rs.getTimestamp("enddate"));

            project.setSc39(rs.getBoolean("sc39"));

            project.setUrl(rs.getString("url"));

            project.setCoordinators(new ArrayList<Person>());

            for (String coordinatorId : (String[]) rs.getArray("coordinators").getArray())

                project.getCoordinators().add(new Person(coordinatorId));

            project.setOrganizations(new ArrayList<Organization>());

            for (String organizationId : (String[]) rs.getArray("organizations").getArray())

                project.getOrganizations().add(new Organization(organizationId));

            return project;

        }

    };

    public DataSource getDataSource() {

        return dataSource;

    }

    public void setDataSource(DataSource dataSource) {

        this.dataSource = dataSource;

    }

    public List<Project> getEligibleProjectsForOrganization(String organizationId) {

        return new JdbcTemplate(dataSource).query(GET_PROJECTS_FOR_ORGANIZATION, new Object[] {organizationId, this.expirationOk}, new int[] {Types.VARCHAR, Types.INTEGER}, rowMapper);

    }

    public List<Project> getFutureEligibleProjectsForOrganization(String organizationId) {

        return new JdbcTemplate(dataSource).query(GET_FUTURE_PROJECTS_FOR_ORGANIZATION, new String[] {organizationId}, new int[] {Types.VARCHAR}, rowMapper);

    }

    public long getExpirationOk() {

        return expirationOk;

    }

    public void setExpirationOk(long expirationOk) {

        this.expirationOk = expirationOk;

    }