1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
/*
* LibraryUtils.java
* -----------------
*
* Part of the URY Common Packages
*
* V0.00 2011/03/23
*
* (C) 2011 URY Computing
*/
package uk.org.ury.common.library;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import uk.org.ury.backend.database.DatabaseDriver;
import uk.org.ury.backend.database.exceptions.QueryFailureException;
import uk.org.ury.common.library.exceptions.EmptySearchException;
import uk.org.ury.common.library.item.LibraryItem;
import uk.org.ury.common.library.item.LibraryItemProperty;
/**
* A set of common utility routines to facilitate the extraction of library
* items from the library areas of the URY database.
*
* @author Matt Windsor
*/
public class LibraryUtils {
/**
* Performs a library search.
*
* @param db
* The database to query.
*
* @param search
* The search fragment to include in the search. Can be empty or
* null.
*
* @throws IllegalArgumentException
* if the search term is are null.
*
* @throws QueryFailureException
* if the database backend yielded an error while executing the
* search query.
*
* @throws EmptySearchException
* if the search term is empty (to be handled as a user error).
*
* @return a list of LibraryItems matching the search terms.
*/
public static List<LibraryItem> search(DatabaseDriver db, String search)
throws QueryFailureException, EmptySearchException {
if (db == null)
throw new IllegalArgumentException("Database handle is null.");
if (search == null)
throw new IllegalArgumentException("Search string is null.");
List<LibraryItem> results = new ArrayList<LibraryItem>();
// Return empty set if the search term is null.
if (search.equals(""))
throw new EmptySearchException();
ResultSet rs = null;
Object[] params = { "%" + search + "%", "%" + search + "%",
"%" + search + "%" };
try {
rs = db.executeQuery(
"SELECT r.title AS album, t.title,"
+ " t.artist, recordlabel AS label, status, media AS medium, format,"
+ " datereleased, EXTRACT(EPOCH FROM dateadded) as dateadded,"
+ " EXTRACT(EPOCH FROM datetime_lastedit) AS dateedited,"
+ " shelfletter, shelfnumber, cdid, digitised, clean"
+ " FROM rec_record AS r"
+ " INNER JOIN rec_track AS t ON (r.recordid = t.recordid)"
+ " WHERE t.title ILIKE ?"
+ " OR t.artist ILIKE ?"
+ " OR r.title ILIKE ?"
+ " ORDER BY digitised DESC, medium ASC, r.title ASC,"
+ " t.artist ASC, t.title ASC;", params, 50);
} catch (SQLException e) {
throw new QueryFailureException(e.getMessage());
}
try {
while (rs.next()) {
results.add(translateRow(rs));
}
} catch (SQLException e) {
throw new QueryFailureException(e.getMessage());
}
return results;
}
/**
* Translates a row retrieved from the database into a LibraryItem.
*
* @param rs
* The result-set, or database cursor, pointing to the row to
* translate.
*
* @return A new LibraryItem containing the properties extracted from the
* translated row.
*/
private static LibraryItem translateRow(ResultSet rs) {
// Translate SQL columns into a list of properties.
Map<LibraryItemProperty, String> properties = new HashMap<LibraryItemProperty, String>();
for (LibraryItemProperty p : LibraryItemProperty.values()) {
try {
properties.put(p, rs.getString(p.sql));
} catch (SQLException e) {
// Ignore this, as it is almost certainly just a non-existent
// property.
}
}
return new LibraryItem(properties);
}
}
|
