D-Net

import eu.dnetlib.goldoa.domain.*;

import java.sql.*;

    @Autowired

    private DataSource dataSource;

    private static final String UPDATE_PUBLICATION = "update publication set title=?, languages=?, subjects=?, doi=?, source=?, date=?, type=?, journal=?, publisher=?, repository=?, acceptancedate=? where id=?";

    private static final String INSERT_PUBLICATION = "insert into publication (title, languages, subjects, doi, source, date, type, journal, publisher, repository, acceptancedate, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String GET_PUBLICATION =

            "select p.id, title, languages, subjects, doi, source, date, p.type, journal, publisher, repository, acceptancedate, array_agg(distinct pa.affiliation) as authors, array_agg(distinct i.type || '||' || i.identifier) as identifiers\n" +

            "from publication p\n" +

            "left join publication_affiliation pa on pa.publication=p.id\n" +

            "left join publication_identifier i on i.publication = p.id\n" +

            "where p.id=?\n" +

            "group by p.id, title, languages, subjects, doi, source, date, p.type, journal, publisher, repository, acceptancedate;";

    private static final String INSERT_PUBLICATION_AFFILIATIONS =

            "with aff as (insert into affiliation (id, person, organization, startdate, enddate, department) values (?, ?, ?, ?, ?, ?) returning id as affid) insert into publication_affiliation (publication, affiliation) select ?, affid from aff";

    private static final String UPDATE_PUBLICATION_AFFILIATIONS =

            "with aff as (select id from affiliation where person=? and organization=?) insert into publication_affiliation (publication, affiliation) select ?, aff.id from aff";

    private static final String LOAD_AFFILIATIONS = "select a.id, startdate, enddate, department, p.id || '||' || p.email || '||' || p.firstname || '||' || p.lastname || '||' || case when p.initials is null then '' else p.initials end as person, o.id || '||' || o.name as organisation\n" +

            "from affiliation a\n" +

            "left join person p on a.person=p.id\n" +

            "left join organisation o on o.id=a.organization\n" +

            "join publication_affiliation pa on pa.affiliation=a.id and pa.publication=?\n";

    public Publication getPublication(final String publicationId) {

        return new JdbcTemplate(dataSource).queryForObject(GET_PUBLICATION, new String[]{publicationId}, new int[]{Types.VARCHAR}, new RowMapper<Publication>() {

            @Override

            public Publication mapRow(ResultSet rs, int i) throws SQLException {

                Publication publication = new Publication();

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

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

                publication.setLanguages(rs.getString("languages"));

                publication.setSubjects(rs.getString("subjects"));

                publication.setDoi(rs.getString("doi"));

                publication.setSource(rs.getString("source"));

                publication.setPublicationDate(rs.getDate("date"));

                publication.setAcceptanceDate(rs.getDate("acceptancedate"));

                publication.setType(Publication.Type.valueOf(rs.getString("type")));

                publication.setJournal(new Journal(rs.getString("journal")));

                publication.setPublisher(new Publisher(rs.getString("publisher")));

                publication.setRepository(rs.getString("repository"));

                publication.setAuthors(new ArrayList<Affiliation>());

                for (int aff: (Integer[]) rs.getArray("authors").getArray())

                    publication.getAuthors().add(new Affiliation(aff));

                publication.setIdentifiers(new ArrayList<PublicationIdentifier>());

                for (String s: (String[]) rs.getArray("identifiers").getArray()) {

                    if (s != null) {

                        String[] parts = s.split("\\|\\|");

                        publication.getIdentifiers().add(new PublicationIdentifier(parts[0], parts[1]));

                    }

                }

                return publication;

            }

        });

    }

    public Publication savePublication(Publication publication) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] args = {publication.getTitle(), publication.getLanguages(), publication.getSubjects(), publication.getDoi(), publication.getSource(), publication.getPublicationDate(), publication.getType().toString(), publication.getJournal()!=null?publication.getJournal().getId():null, publication.getPublisher()!=null?publication.getPublisher().getId():null, publication.getRepository(), publication.getAcceptanceDate(), publication.getId()};

        int[] types = {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE, Types.VARCHAR};

        if (jdbcTemplate.update(UPDATE_PUBLICATION, args, types) == 0) {

            jdbcTemplate.update(INSERT_PUBLICATION, args, types);

        }

        return publication;

    }

    public void insertIdentifiers(final Publication publication) {

        new JdbcTemplate(dataSource).batchUpdate("insert into publication_identifier (publication, type, identifier) values (?, ?, ?)", new BatchPreparedStatementSetter() {

            @Override

            public void setValues(PreparedStatement ps, int i) throws SQLException {

                PublicationIdentifier identifier = publication.getIdentifiers().get(i);

                ps.setString(1, publication.getId());

                ps.setString(2, identifier.getType());

                ps.setString(3, identifier.getValue());

            }

            @Override

            public int getBatchSize() {

                return publication.getIdentifiers()!=null?publication.getIdentifiers().size():0;

            }

        });

    }

    public void deleteAffiliations(Publication publication) {

        new JdbcTemplate(dataSource).update("delete from publication_affiliation where publication=?", new String[] {publication.getId()}, new int[] {Types.VARCHAR});

    }

    public void deleteIdentifiers(Publication publication) {

        new JdbcTemplate(dataSource).update("delete from publication_identifier where publication=?", new String[] {publication.getId()}, new int[] {Types.VARCHAR});

    }

    public void saveAffiliations(final Publication publication) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        if (publication.getAuthors() != null) {

            for (Affiliation affiliation:publication.getAuthors()) {

                if (jdbcTemplate.update(UPDATE_PUBLICATION_AFFILIATIONS, new String[] {affiliation.getPerson().getId(), affiliation.getOrganization() != null? affiliation.getOrganization().getId(): null, publication.getId()}, new int[] {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}) == 0) {

                    jdbcTemplate.update(INSERT_PUBLICATION_AFFILIATIONS,

                            new Object[] {PersonManagerImpl.generateId(affiliation), affiliation.getPerson().getId(), affiliation.getOrganization() != null?affiliation.getOrganization().getId():null, affiliation.getStart(), affiliation.getEnd(), affiliation.getDepartment(), publication.getId()},

                            new int[] {Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});

                }

            }

        }

    }

    public void loadAffiliations(Publication publication) {

        List<Affiliation> affiliations = new JdbcTemplate(dataSource).query(LOAD_AFFILIATIONS, new String[]{publication.getId()}, new int[]{Types.VARCHAR}, new RowMapper<Affiliation>() {

            @Override

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

                Affiliation aff = new Affiliation();

                aff.setId(rs.getInt("id"));

                aff.setDepartment(rs.getString("department"));

                aff.setStart(rs.getDate("startdate"));

                aff.setEnd(rs.getDate("enddate"));

                aff.setPerson(this.parsePerson(rs.getString("person")));

                aff.setOrganization(this.parseOrganisation(rs.getString("organisation")));

                return aff;

            }

            private Organization parseOrganisation(String organisation) {

                if (organisation != null) {

                    Organization org = new Organization();

                    String[] parts = organisation.split("\\|\\|");

                    org.setId(parts[0]);

                    org.setName(parts[1]);

                    return org;

                } else

                    return null;

            }

            private Person parsePerson(String pStr) {

                String[] parts = pStr.split("\\|\\|");

                Person person = new Person();

                person.setId(parts[0]);

                person.setEmail(parts[1]);

                person.setName(parts[2]);

                person.setLastname(parts[3]);

                if (parts.length > 4)

                    person.setInitials(parts[4]);

                return person;

            }

        });

        publication.setAuthors(affiliations);

    }