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
|
/**
*
*/
package uk.org.ury.library;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import uk.org.ury.database.DatabaseDriver;
import uk.org.ury.database.exceptions.QueryFailureException;
import uk.org.ury.library.LibraryItem.LibraryProperty;
import uk.org.ury.library.exceptions.EmptySearchException;
/**
* 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
{
/**
* Perform 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, memberid_add, memberid_lastedit,"
+ " digitised, clean,"
+ " a.fname AS fnameadd, a.sname AS snameadd, b.fname AS fnameedit, b.sname AS snameedit"
+ " FROM rec_record AS r"
+ " INNER JOIN rec_track AS t ON (r.recordid = t.recordid)"
+ " INNER JOIN member AS a ON (a.memberid = r.memberid_add)"
+ " LEFT JOIN member AS b ON (b.memberid = r.memberid_lastedit)"
+ " 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 ())
{
// Translate SQL columns into a list of properties.
HashMap<LibraryProperty, String> properties = new HashMap<LibraryProperty, String> ();
for (LibraryProperty p : LibraryProperty.values ())
{
try
{
properties.put (p, rs.getString (p.sql));
}
catch (SQLException e)
{
// Ignore this, as it is almost certainly just a non-existent
// property.
}
}
results.add (new LibraryItem (properties));
}
}
catch (SQLException e)
{
throw new QueryFailureException (e.getMessage ());
}
return results;
}
}
|
