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
|
/**
*
*/
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;
/**
* 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.
*
* Presently, the title and artist comparisons are logically ANDed.
*
* @param db The database to query.
*
* @param title The search fragment to include in the search.
* Can be empty or null.
*
* @throws IllegalArgumentException if db, title or artist
* are null.
*
* @throws QueryFailureException if the database backend
* yielded an error while executing the search
* query.
*
* @return a list of LibraryItems matching the search terms.
*/
public static List<LibraryItem>
search (DatabaseDriver db, String search)
throws QueryFailureException
{
if (db == null)
throw new IllegalArgumentException ("Database handle is null.");
if (search == null)
throw new IllegalArgumentException ("Search string is null.");
if (search.equals(""))
//TODO: Be nicer about this
System.exit(1);
ResultSet rs = null;
List<LibraryItem> results = new ArrayList<LibraryItem> ();
Object[] params = {"%" + search + "%", "%" + search + "%"};
try
{
rs = db.executeQuery (
"SELECT title, 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,"
+ " a.fname AS fnameadd, a.sname AS snameadd, b.fname AS fnameedit, b.sname AS snameedit"
+ " FROM rec_record AS r"
+ " INNER JOIN member AS a ON (a.memberid = r.memberid_add)"
+ " LEFT JOIN member AS b ON (b.memberid = r.memberid_lastedit)"
+ " WHERE title ILIKE ?"
+ " OR artist ILIKE ?;", 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;
}
}
|
