Source code: comiccollection/SQLComicAccesser.java
1 package comiccollection;
2
3 import java.util.*;
4
5 import java.sql.*;
6
7 class SQLComicAccesser implements ComicAccesser{
8 Connection conn;
9 String limitString;
10 String prefix;
11
12 public void prepare(Hashtable setupData) throws Exception{
13
14 String driverName=(String)setupData.get("driverName");
15 String dbURL=(String)setupData.get("url");
16 String dbUsername=(String)setupData.get("username");
17 String dbPassword=(String)setupData.get("password");
18 String tmpLimitString=(String)setupData.get("limits");
19 if((tmpLimitString==null)||(tmpLimitString.equals("yes"))){
20 limitString=" LIMIT 1";
21 }else{
22 limitString="";
23 }
24 prefix=(String)setupData.get("prefix");
25
26 Class.forName(driverName);
27
28 conn=DriverManager.getConnection(dbURL, dbUsername, dbPassword);
29 }
30
31 public ArrayList getComicList() throws Exception{
32 String query="SELECT * FROM "+prefix+"comiclist";
33 ComicObject tmpComic;
34 ArrayList list=new ArrayList();
35
36 Statement stmt=conn.createStatement();
37 ResultSet rs=stmt.executeQuery(query);
38
39 while(rs.next()){
40 tmpComic=new ComicObject();
41
42 tmpComic.id=rs.getInt("id");
43 tmpComic.name=rs.getString("name");
44 tmpComic.access_string=rs.getString("access_string");
45 System.out.println("Access String:"+rs.getString("access_string"));
46 tmpComic.multi_access_string=rs.getString("multi_access_string");
47 tmpComic.extract_rules=rs.getString("extract_rules");
48 tmpComic.ext_list=rs.getString("ext_list");
49 tmpComic.auto_get=rs.getString("auto_get");
50 tmpComic.system_type=rs.getString("system_type");
51 tmpComic.order=rs.getString("attempt_order");
52 tmpComic.inc=rs.getString("inc");
53
54 list.add(tmpComic);
55 }
56 stmt.close();
57
58 return list;
59 }
60
61 public ComicStrip getStrip(ComicObject comic, GregorianCalendar date, int offset, boolean online) throws Exception{
62 String query=null;
63 java.sql.Date sqlDate=new java.sql.Date(date.getTime().getTime());
64
65 if(offset==0){
66 query="SELECT * FROM "+prefix+"strips WHERE comic_id='"+String.valueOf(comic.id)+"' AND for_day='"+sqlDate.toString()+"'";
67 }else if(offset==1){
68 if(online){
69 date.add(Calendar.DATE, 1);
70 sqlDate=new java.sql.Date(date.getTime().getTime());
71 query="SELECT * FROM "+prefix+"strips WHERE comic_id='"+String.valueOf(comic.id)+"' AND for_day='"+sqlDate.toString()+"'";
72 }else{
73 query="SELECT * FROM "+prefix+"strips WHERE comic_id='"+String.valueOf(comic.id)+"' AND for_day>'"+sqlDate.toString()+"' ORDER BY for_day"+limitString;
74 System.out.println(query);
75 }
76 }else if(offset==-1){
77 if(online){
78 date.add(Calendar.DATE, -1);
79 sqlDate=new java.sql.Date(date.getTime().getTime());
80 query="SELECT * FROM "+prefix+"strips WHERE comic_id='"+String.valueOf(comic.id)+"' AND for_day='"+sqlDate.toString()+"'";
81 }else{
82 query="SELECT * FROM "+prefix+"strips WHERE comic_id='"+String.valueOf(comic.id)+"' AND for_day<'"+sqlDate.toString()+"' ORDER BY for_day DESC"+limitString;
83 }
84 }
85 System.out.println(query);
86
87 return getStrip(query);
88 }
89
90 ComicStrip getStrip(String query) throws Exception{
91 ComicStrip strip=new ComicStrip();
92 Statement stmt=conn.createStatement();
93 ResultSet rs=stmt.executeQuery(query);
94
95 if(!rs.next()){
96 return null;
97 }else{
98 strip.id=rs.getInt("id");
99 strip.title=rs.getString("title");
100 strip.desc=rs.getString("description");
101 strip.date=new GregorianCalendar();
102 strip.date.setTime(rs.getDate("for_day"));
103 }
104
105 // stmt.close();
106
107 query="SELECT * FROM "+prefix+"images WHERE strip_id='"+String.valueOf(strip.id)+"' ORDER BY local_order, id";
108 System.out.println(query);
109 rs=stmt.executeQuery(query);
110 System.out.println("Test!");
111 strip.images=new ArrayList();
112
113 ComicImage ci;
114 while(rs.next()){
115 ci=new ComicImage();
116 System.out.println("Test");
117 ci.id=rs.getInt("id");
118 ci.type=rs.getString("type");
119 ci.width=rs.getInt("width");
120 ci.height=rs.getInt("height");
121 strip.images.add(ci);
122 }
123 stmt.close();
124 return strip;
125 }
126
127 public ComicStrip getStrip(int id) throws Exception{
128 return getStrip("SELECT * FROM "+prefix+"strips WHERE id='"+String.valueOf(id)+"'");
129 }
130
131
132 public int createStrip(ComicStrip strip, ComicObject comic) throws Exception{
133 java.sql.Date date=new java.sql.Date(strip.date.getTime().getTime());
134
135 String query="INSERT INTO "+prefix+"strips(comic_id, for_day) VALUES ('"+comic.id+"', '"+date.toString()+"')";
136 System.out.println(query);
137
138 Statement stmt=conn.createStatement();
139 stmt.execute(query);
140
141
142 query="SELECT id FROM "+prefix+"strips WHERE comic_id='"+comic.id+"' AND for_day='"+date+"'";
143 System.out.println(query);
144 ResultSet rs=stmt.executeQuery(query);
145 rs.next();
146
147 return rs.getInt("id");
148 }
149
150 public int createImage(ComicImage image, int strip) throws Exception{
151 String query="INSERT INTO "+prefix+"images(strip_id, type) VALUES('"+strip+"', '"+image.type+"')";
152 System.out.println(query);
153
154 Statement stmt=conn.createStatement();
155 stmt.execute(query);
156
157 query="SELECT MAX(id) AS 'id' FROM "+prefix+"images WHERE strip_id='"+strip+"'";
158 ResultSet rs=stmt.executeQuery(query);
159 rs.next();
160
161 return rs.getInt("id");
162 }
163
164 public void writeStrip(int id, String title, String desc) throws Exception{
165 String wherePart, query;
166
167
168 // if(strip==null){
169 // return;
170 // }
171
172 // if(strip.id!=0){
173 wherePart=" WHERE id='"+String.valueOf(id)+"'";
174 // }else{
175 // java.sql.Date sqlDate=new java.sql.Date(date.getTime().getTime());
176 // wherePart=" WHERE for_day='"+sqlDate.toString()+"'";
177 // }
178
179 /* query="UPDATE "+prefix+"strips";
180 if(title!=null){
181 query+=" SET title='"+title+"'";
182 }
183 if(desc!=null){
184 if(title!=null){
185 query+=",";
186 }else{
187 query+=" SET";
188 }
189 query+=" description='"+desc+"'";
190 }
191 query+=wherePart;
192 */
193
194 query="UPDATE "+prefix+"strips";
195 if(title!=null){
196 query+=" SET title=?";
197 }
198 if(desc!=null){
199 if(title!=null){
200 query+=",";
201 }else{
202 query+=" SET";
203 }
204 query+=" description=?";
205 }
206 query+=wherePart;
207
208 System.out.println(query);
209
210 PreparedStatement stmt=conn.prepareStatement(query);
211 stmt.setEscapeProcessing(true);
212 if(title!=null){
213 stmt.setString(1, title);
214 if(desc!=null){
215 stmt.setString(2, desc);
216 }
217 }else{
218 stmt.setString(1, desc);
219 }
220 stmt.executeUpdate();
221 }
222
223 public ArrayList doSearch(String search) throws Exception{
224 String query=null;
225 ArrayList retVal, tmp;
226 StringBuffer searchBuf=new StringBuffer(search);
227 int quotePos;
228
229 retVal=new ArrayList();
230
231 while(true){
232 quotePos=searchBuf.toString().indexOf("\'");
233 if(quotePos==-1){
234 break;
235 }
236 searchBuf.replace(quotePos, quotePos+1, "\\\'");
237 }
238
239 query="SELECT id, title FROM "+prefix+"strips WHERE title LIKE '%"+searchBuf.toString()+
240 "%' OR description LIKE '%"+searchBuf.toString()+"%'";
241 System.out.println(query);
242
243 Statement stmt=conn.createStatement();
244 ResultSet rs=stmt.executeQuery(query);
245 while(rs.next()){
246 System.out.println(String.valueOf(rs.getInt("id"))+": "+rs.getString("title"));
247 tmp=new ArrayList();
248 tmp.add(new Integer(rs.getInt("id")));
249 tmp.add(rs.getString("title"));
250 retVal.add(tmp);
251 }
252 return retVal;
253 }
254
255 public void execQuery(String query) throws Exception{
256 Statement stmt=conn.createStatement();
257 stmt.execute(query);
258 }
259
260 SQLComicAccesser(){
261 }
262 }