1
|
package eu.dnetlib.goldoa.service.dao;
|
2
|
|
3
|
import eu.dnetlib.goldoa.domain.*;
|
4
|
import eu.dnetlib.goldoa.service.PersonManagerImpl;
|
5
|
import org.springframework.beans.factory.annotation.Autowired;
|
6
|
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
|
7
|
import org.springframework.jdbc.core.JdbcTemplate;
|
8
|
import org.springframework.jdbc.core.RowMapper;
|
9
|
|
10
|
import javax.sql.DataSource;
|
11
|
import java.sql.*;
|
12
|
import java.util.ArrayList;
|
13
|
import java.util.List;
|
14
|
|
15
|
public class PublicationDAO {
|
16
|
|
17
|
@Autowired
|
18
|
private DataSource dataSource;
|
19
|
|
20
|
private static final String UPDATE_PUBLICATION = "update publication set title=?, languages=?, subjects=?, doi=?, source=?, date=?, type=?, journal=?, publisher=?, repository=?, acceptancedate=? where id=?";
|
21
|
|
22
|
private static final String INSERT_PUBLICATION = "insert into publication (title, languages, subjects, doi, source, date, type, journal, publisher, repository, acceptancedate, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
|
23
|
|
24
|
private static final String GET_PUBLICATION =
|
25
|
"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
|
"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
|
"where p.id=?\n" +
|
30
|
"group by p.id, title, languages, subjects, doi, source, date, p.type, journal, publisher, repository, acceptancedate;";
|
31
|
|
32
|
private static final String INSERT_PUBLICATION_AFFILIATIONS =
|
33
|
"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
|
|
35
|
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
|
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
|
"from affiliation a\n" +
|
40
|
"left join person p on a.person=p.id\n" +
|
41
|
"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
|
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
|
publication.setAcceptanceDate(rs.getDate("acceptancedate"));
|
59
|
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
|
publication.setRepository(rs.getString("repository"));
|
63
|
|
64
|
publication.setAuthors(new ArrayList<Affiliation>());
|
65
|
for (int aff: (Integer[]) rs.getArray("authors").getArray())
|
66
|
publication.getAuthors().add(new Affiliation(aff));
|
67
|
|
68
|
publication.setIdentifiers(new ArrayList<PublicationIdentifier>());
|
69
|
for (String s: (String[]) rs.getArray("identifiers").getArray()) {
|
70
|
if (s != null) {
|
71
|
String[] parts = s.split("\\|\\|");
|
72
|
|
73
|
publication.getIdentifiers().add(new PublicationIdentifier(parts[0], parts[1]));
|
74
|
}
|
75
|
}
|
76
|
|
77
|
return publication;
|
78
|
}
|
79
|
});
|
80
|
}
|
81
|
|
82
|
public Publication savePublication(Publication publication) {
|
83
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
84
|
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
|
|
87
|
if (jdbcTemplate.update(UPDATE_PUBLICATION, args, types) == 0) {
|
88
|
jdbcTemplate.update(INSERT_PUBLICATION, args, types);
|
89
|
}
|
90
|
|
91
|
return publication;
|
92
|
}
|
93
|
|
94
|
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
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
123
|
|
124
|
if (publication.getAuthors() != null) {
|
125
|
for (Affiliation affiliation:publication.getAuthors()) {
|
126
|
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
|
jdbcTemplate.update(INSERT_PUBLICATION_AFFILIATIONS,
|
128
|
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
|
}
|
131
|
}
|
132
|
}
|
133
|
}
|
134
|
|
135
|
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
|
if (organisation != null) {
|
154
|
Organization org = new Organization();
|
155
|
String[] parts = organisation.split("\\|\\|");
|
156
|
|
157
|
org.setId(parts[0]);
|
158
|
org.setName(parts[1]);
|
159
|
|
160
|
return org;
|
161
|
} else
|
162
|
return null;
|
163
|
}
|
164
|
|
165
|
private Person parsePerson(String pStr) {
|
166
|
String[] parts = pStr.split("\\|\\|");
|
167
|
Person person = new Person();
|
168
|
|
169
|
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
|
|
176
|
return person;
|
177
|
}
|
178
|
});
|
179
|
|
180
|
publication.setAuthors(affiliations);
|
181
|
}
|
182
|
}
|