| 1 |
35518
|
antonis.le
|
package eu.dnetlib.goldoa.service.dao;
|
| 2 |
|
|
|
| 3 |
|
|
import eu.dnetlib.goldoa.domain.*;
|
| 4 |
36089
|
antonis.le
|
import eu.dnetlib.goldoa.service.PersonManagerImpl;
|
| 5 |
35518
|
antonis.le
|
import org.springframework.beans.factory.annotation.Autowired;
|
| 6 |
35521
|
antonis.le
|
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
|
| 7 |
35518
|
antonis.le
|
import org.springframework.jdbc.core.JdbcTemplate;
|
| 8 |
|
|
import org.springframework.jdbc.core.RowMapper;
|
| 9 |
|
|
|
| 10 |
|
|
import javax.sql.DataSource;
|
| 11 |
35521
|
antonis.le
|
import java.sql.*;
|
| 12 |
35518
|
antonis.le
|
import java.util.ArrayList;
|
| 13 |
35521
|
antonis.le
|
import java.util.List;
|
| 14 |
35518
|
antonis.le
|
|
| 15 |
|
|
public class PublicationDAO {
|
| 16 |
|
|
|
| 17 |
|
|
@Autowired
|
| 18 |
|
|
private DataSource dataSource;
|
| 19 |
|
|
|
| 20 |
36045
|
antonis.le
|
private static final String UPDATE_PUBLICATION = "update publication set title=?, languages=?, subjects=?, doi=?, source=?, date=?, type=?, journal=?, publisher=?, repository=?, acceptancedate=? where id=?";
|
| 21 |
35518
|
antonis.le
|
|
| 22 |
36045
|
antonis.le
|
private static final String INSERT_PUBLICATION = "insert into publication (title, languages, subjects, doi, source, date, type, journal, publisher, repository, acceptancedate, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
|
| 23 |
35518
|
antonis.le
|
|
| 24 |
|
|
private static final String GET_PUBLICATION =
|
| 25 |
36062
|
antonis.le
|
"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" +
|
| 26 |
35518
|
antonis.le
|
"from publication p\n" +
|
| 27 |
|
|
"left join publication_affiliation pa on pa.publication=p.id\n" +
|
| 28 |
|
|
"left join publication_identifier i on i.publication = p.id\n" +
|
| 29 |
35679
|
stefania.m
|
"where p.id=?\n" +
|
| 30 |
36063
|
antonis.le
|
"group by p.id, title, languages, subjects, doi, source, date, p.type, journal, publisher, repository, acceptancedate;";
|
| 31 |
35518
|
antonis.le
|
|
| 32 |
35521
|
antonis.le
|
private static final String INSERT_PUBLICATION_AFFILIATIONS =
|
| 33 |
36089
|
antonis.le
|
"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";
|
| 34 |
35521
|
antonis.le
|
|
| 35 |
35658
|
antonis.le
|
private static final String UPDATE_PUBLICATION_AFFILIATIONS =
|
| 36 |
|
|
"with aff as (select id from affiliation where person=? and organization=?) insert into publication_affiliation (publication, affiliation) select ?, aff.id from aff";
|
| 37 |
|
|
|
| 38 |
35749
|
antonis.le
|
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" +
|
| 39 |
35535
|
antonis.le
|
"from affiliation a\n" +
|
| 40 |
35749
|
antonis.le
|
"left join person p on a.person=p.id\n" +
|
| 41 |
35535
|
antonis.le
|
"left join organisation o on o.id=a.organization\n" +
|
| 42 |
|
|
"join publication_affiliation pa on pa.affiliation=a.id and pa.publication=?\n";
|
| 43 |
|
|
|
| 44 |
35518
|
antonis.le
|
public Publication getPublication(final String publicationId) {
|
| 45 |
|
|
return new JdbcTemplate(dataSource).queryForObject(GET_PUBLICATION, new String[]{publicationId}, new int[]{Types.VARCHAR}, new RowMapper<Publication>() {
|
| 46 |
|
|
@Override
|
| 47 |
|
|
public Publication mapRow(ResultSet rs, int i) throws SQLException {
|
| 48 |
|
|
|
| 49 |
|
|
Publication publication = new Publication();
|
| 50 |
|
|
|
| 51 |
|
|
publication.setId(rs.getString("id"));
|
| 52 |
|
|
publication.setTitle(rs.getString("title"));
|
| 53 |
|
|
publication.setLanguages(rs.getString("languages"));
|
| 54 |
|
|
publication.setSubjects(rs.getString("subjects"));
|
| 55 |
|
|
publication.setDoi(rs.getString("doi"));
|
| 56 |
|
|
publication.setSource(rs.getString("source"));
|
| 57 |
|
|
publication.setPublicationDate(rs.getDate("date"));
|
| 58 |
36045
|
antonis.le
|
publication.setAcceptanceDate(rs.getDate("acceptancedate"));
|
| 59 |
35518
|
antonis.le
|
publication.setType(Publication.Type.valueOf(rs.getString("type")));
|
| 60 |
|
|
publication.setJournal(new Journal(rs.getString("journal")));
|
| 61 |
|
|
publication.setPublisher(new Publisher(rs.getString("publisher")));
|
| 62 |
35639
|
antonis.le
|
publication.setRepository(rs.getString("repository"));
|
| 63 |
35518
|
antonis.le
|
|
| 64 |
|
|
publication.setAuthors(new ArrayList<Affiliation>());
|
| 65 |
35679
|
stefania.m
|
for (int aff: (Integer[]) rs.getArray("authors").getArray())
|
| 66 |
35518
|
antonis.le
|
publication.getAuthors().add(new Affiliation(aff));
|
| 67 |
|
|
|
| 68 |
|
|
publication.setIdentifiers(new ArrayList<PublicationIdentifier>());
|
| 69 |
|
|
for (String s: (String[]) rs.getArray("identifiers").getArray()) {
|
| 70 |
35679
|
stefania.m
|
if (s != null) {
|
| 71 |
|
|
String[] parts = s.split("\\|\\|");
|
| 72 |
35518
|
antonis.le
|
|
| 73 |
35679
|
stefania.m
|
publication.getIdentifiers().add(new PublicationIdentifier(parts[0], parts[1]));
|
| 74 |
|
|
}
|
| 75 |
35518
|
antonis.le
|
}
|
| 76 |
|
|
|
| 77 |
|
|
return publication;
|
| 78 |
|
|
}
|
| 79 |
|
|
});
|
| 80 |
|
|
}
|
| 81 |
|
|
|
| 82 |
|
|
public Publication savePublication(Publication publication) {
|
| 83 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 84 |
36045
|
antonis.le
|
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()};
|
| 85 |
|
|
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};
|
| 86 |
35518
|
antonis.le
|
|
| 87 |
|
|
if (jdbcTemplate.update(UPDATE_PUBLICATION, args, types) == 0) {
|
| 88 |
|
|
jdbcTemplate.update(INSERT_PUBLICATION, args, types);
|
| 89 |
|
|
}
|
| 90 |
|
|
|
| 91 |
|
|
return publication;
|
| 92 |
|
|
}
|
| 93 |
|
|
|
| 94 |
35521
|
antonis.le
|
public void insertIdentifiers(final Publication publication) {
|
| 95 |
|
|
|
| 96 |
|
|
new JdbcTemplate(dataSource).batchUpdate("insert into publication_identifier (publication, type, identifier) values (?, ?, ?)", new BatchPreparedStatementSetter() {
|
| 97 |
|
|
@Override
|
| 98 |
|
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
| 99 |
|
|
PublicationIdentifier identifier = publication.getIdentifiers().get(i);
|
| 100 |
|
|
|
| 101 |
|
|
ps.setString(1, publication.getId());
|
| 102 |
|
|
ps.setString(2, identifier.getType());
|
| 103 |
|
|
ps.setString(3, identifier.getValue());
|
| 104 |
|
|
}
|
| 105 |
|
|
|
| 106 |
|
|
@Override
|
| 107 |
|
|
public int getBatchSize() {
|
| 108 |
|
|
return publication.getIdentifiers()!=null?publication.getIdentifiers().size():0;
|
| 109 |
|
|
}
|
| 110 |
|
|
});
|
| 111 |
|
|
}
|
| 112 |
|
|
|
| 113 |
|
|
public void deleteAffiliations(Publication publication) {
|
| 114 |
|
|
new JdbcTemplate(dataSource).update("delete from publication_affiliation where publication=?", new String[] {publication.getId()}, new int[] {Types.VARCHAR});
|
| 115 |
|
|
}
|
| 116 |
|
|
|
| 117 |
|
|
public void deleteIdentifiers(Publication publication) {
|
| 118 |
|
|
new JdbcTemplate(dataSource).update("delete from publication_identifier where publication=?", new String[] {publication.getId()}, new int[] {Types.VARCHAR});
|
| 119 |
|
|
}
|
| 120 |
|
|
|
| 121 |
|
|
public void saveAffiliations(final Publication publication) {
|
| 122 |
35658
|
antonis.le
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 123 |
35521
|
antonis.le
|
|
| 124 |
35658
|
antonis.le
|
if (publication.getAuthors() != null) {
|
| 125 |
|
|
for (Affiliation affiliation:publication.getAuthors()) {
|
| 126 |
35749
|
antonis.le
|
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) {
|
| 127 |
35658
|
antonis.le
|
jdbcTemplate.update(INSERT_PUBLICATION_AFFILIATIONS,
|
| 128 |
36089
|
antonis.le
|
new Object[] {PersonManagerImpl.generateId(affiliation), affiliation.getPerson().getId(), affiliation.getOrganization() != null?affiliation.getOrganization().getId():null, affiliation.getStart(), affiliation.getEnd(), affiliation.getDepartment(), publication.getId()},
|
| 129 |
|
|
new int[] {Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});
|
| 130 |
35658
|
antonis.le
|
}
|
| 131 |
35521
|
antonis.le
|
}
|
| 132 |
35658
|
antonis.le
|
}
|
| 133 |
35521
|
antonis.le
|
}
|
| 134 |
|
|
|
| 135 |
35535
|
antonis.le
|
public void loadAffiliations(Publication publication) {
|
| 136 |
|
|
List<Affiliation> affiliations = new JdbcTemplate(dataSource).query(LOAD_AFFILIATIONS, new String[]{publication.getId()}, new int[]{Types.VARCHAR}, new RowMapper<Affiliation>() {
|
| 137 |
|
|
@Override
|
| 138 |
|
|
public Affiliation mapRow(ResultSet rs, int rowNum) throws SQLException {
|
| 139 |
|
|
Affiliation aff = new Affiliation();
|
| 140 |
|
|
|
| 141 |
|
|
aff.setId(rs.getInt("id"));
|
| 142 |
|
|
aff.setDepartment(rs.getString("department"));
|
| 143 |
|
|
aff.setStart(rs.getDate("startdate"));
|
| 144 |
|
|
aff.setEnd(rs.getDate("enddate"));
|
| 145 |
|
|
|
| 146 |
|
|
aff.setPerson(this.parsePerson(rs.getString("person")));
|
| 147 |
|
|
aff.setOrganization(this.parseOrganisation(rs.getString("organisation")));
|
| 148 |
|
|
|
| 149 |
|
|
return aff;
|
| 150 |
|
|
}
|
| 151 |
|
|
|
| 152 |
|
|
private Organization parseOrganisation(String organisation) {
|
| 153 |
35749
|
antonis.le
|
if (organisation != null) {
|
| 154 |
|
|
Organization org = new Organization();
|
| 155 |
|
|
String[] parts = organisation.split("\\|\\|");
|
| 156 |
35535
|
antonis.le
|
|
| 157 |
35749
|
antonis.le
|
org.setId(parts[0]);
|
| 158 |
|
|
org.setName(parts[1]);
|
| 159 |
35535
|
antonis.le
|
|
| 160 |
35749
|
antonis.le
|
return org;
|
| 161 |
|
|
} else
|
| 162 |
|
|
return null;
|
| 163 |
35535
|
antonis.le
|
}
|
| 164 |
|
|
|
| 165 |
|
|
private Person parsePerson(String pStr) {
|
| 166 |
|
|
String[] parts = pStr.split("\\|\\|");
|
| 167 |
|
|
Person person = new Person();
|
| 168 |
|
|
|
| 169 |
35749
|
antonis.le
|
person.setId(parts[0]);
|
| 170 |
|
|
person.setEmail(parts[1]);
|
| 171 |
|
|
person.setName(parts[2]);
|
| 172 |
|
|
person.setLastname(parts[3]);
|
| 173 |
|
|
if (parts.length > 4)
|
| 174 |
|
|
person.setInitials(parts[4]);
|
| 175 |
35535
|
antonis.le
|
|
| 176 |
|
|
return person;
|
| 177 |
|
|
}
|
| 178 |
|
|
});
|
| 179 |
|
|
|
| 180 |
|
|
publication.setAuthors(affiliations);
|
| 181 |
|
|
}
|
| 182 |
|
|
}
|