API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 25th, 2017
125
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.73 KB | None | 0 0
raw download clone embed print report
  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.sql.Statement;
  6. import java.util.ArrayList;
  7. import java.util.List;
  8. import java.sql.PreparedStatement;
  9.  
  10. public class SearchDAO {
  11.  
  12. List<BookInformation> exactdata = new ArrayList<BookInformation> ();
  13. List<BookInformation> partialdata = new ArrayList<BookInformation> ();
  14.  
  15. boolean resultfound = false;
  16.  
  17. public void Search(BookInformation data) {
  18.  
  19. final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  20. final String DB_URL = "jdbc:mysql://localhost/book";
  21.  
  22. // Database credentials
  23. final String USER = "root";
  24. final String PASS = "1234";
  25.  
  26. Connection conn = null;
  27. Statement stmt = null;
  28. try{
  29. //STEP 2: Register JDBC driver
  30. // Class.forName("com.mysql.jdbc.Driver");
  31. Class.forName("com.mysql.jdbc.Driver");
  32. //STEP 3: Open a connection
  33. System.out.println("Connecting to database...");
  34. conn = DriverManager.getConnection(DB_URL,USER,PASS);
  35.  
  36. //STEP 4: Execute a query
  37. System.out.println("Creating statement...");
  38. //System.out.println("check"+data.Author);
  39.  
  40. stmt = conn.createStatement();
  41. String sql;
  42. String join = "";
  43. //
  44. // sql = "SELECT bookinfo.book_id,bookinfo.Book_Title,bookinfo.Date_Issue, "
  45. // + "group_concat(distinct(Author_Name)) as Author_Name,group_concat(distinct(Trans_Name)) as Trans_Name,genreinfo.Genre_Name "
  46. // + "from bookinfo left join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
  47. // + "left join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
  48. // + "left join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
  49. // + "where Book_Status = 1 "
  50. // + "group by bookinfo.Book_Id";
  51.  
  52. //sql = "SELECT bookinfo.book_id from bookinfo where book_id = " + data.getId();
  53.  
  54. sql = "SELECT bookinfo.book_id "
  55. + "from bookinfo "
  56. + " join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
  57. + " join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
  58. + " join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
  59. + "where Book_Status = 1 " ;
  60.  
  61.  
  62. if(data.getTitle().trim().equals("") == false){
  63. sql += " AND bookinfo.book_title = '" + data.getTitle().trim() + "' " ;
  64. System.out.println("use this");
  65. }
  66. if(data.getDmy().trim().equals("") == false){
  67.  
  68. sql += " AND bookinfo.date_issue = '" + data.getDmy().trim() + "' " ;
  69. }
  70. if(data.getDonor().trim().equals("") == false){
  71.  
  72. sql += " AND bookinfo.donor_name = '" + data.getDonor().trim() + "' " ;
  73. }
  74. if(data.getGenre().trim().equals("none") == false ){
  75.  
  76. sql += " AND genreinfo.genre_name = '" + data.getGenre().trim() + "' " ;
  77. }
  78.  
  79. if(data.getauthorlist().size() >= 1){
  80. sql += " AND authorinfo.author_name IN (" ;
  81. String tempitem = "";
  82. for(String item : data.getauthorlist()){
  83. tempitem += ",'" + item.trim() + "'";
  84. }
  85.  
  86. tempitem = tempitem.substring(1);
  87.  
  88. sql += tempitem + ") " ;
  89.  
  90. }
  91.  
  92. if(data.gettranslatorlist().size() >= 1){
  93. sql += " AND translatorinfo.trans_name IN (" ;
  94. String tempitem = "";
  95. for(String item : data.gettranslatorlist()){
  96. tempitem += ",'" + item.trim() + "'";
  97. }
  98.  
  99. tempitem = tempitem.substring(1);
  100.  
  101. sql += tempitem + ") " ;
  102.  
  103. }
  104.  
  105.  
  106.  
  107.  
  108.  
  109.  
  110. ResultSet rs = stmt.executeQuery(sql);
  111. System.out.println(sql);
  112.  
  113. List<String>BookID = new ArrayList<String>();
  114. while(rs.next()){
  115. BookID.add(rs.getString("book_id"));
  116. }
  117.  
  118. String partialID = "";
  119. for(String item : BookID){
  120. partialID += " or bookinfo.Book_Id = " + item;
  121.  
  122. }
  123.  
  124. if(partialID.length() > 4){
  125. partialID = partialID.substring(3);
  126.  
  127.  
  128. sql = "SELECT bookinfo.book_id,bookinfo.Book_Title,bookinfo.Date_Issue, "
  129. + "group_concat(distinct(Author_Name)) as Author_Name,group_concat(distinct(Trans_Name)) as Trans_Name,genreinfo.Genre_Name "
  130. + "from bookinfo join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
  131. + " join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
  132. + " join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
  133. + "where " + partialID
  134. + " group by bookinfo.Book_Id";
  135.  
  136. rs = stmt.executeQuery(sql);
  137.  
  138.  
  139. while (rs.next()) {
  140. BookInformation t = new BookInformation();
  141. t.Id = rs.getString("Book_Id");
  142. t.Title = rs.getString("Book_Title");
  143. t.Author = rs.getString("Author_Name");
  144. t.Translator = rs.getString("Trans_Name");
  145. t.Genre = rs.getString("Genre_Name");
  146. t.Dmy = rs.getString("Date_Issue");
  147.  
  148. String checkauthor = rs.getString("Author_Name");
  149. List<String> authorlist = new ArrayList<String>();
  150. if(checkauthor != null)
  151. {
  152. String [] authortemp = rs.getString("Author_Name").split(",");
  153. for(String item: authortemp)
  154. {
  155. if(item.trim().equals("") == false)
  156. {
  157. authorlist.add(item);
  158. }
  159. }
  160. }
  161. String checktranslator = rs.getString("Trans_Name");
  162. List<String> translatorlist = new ArrayList<String>();
  163. if(checktranslator != null)
  164. {
  165. String [] translatortemp = rs.getString("Trans_Name").split(",");
  166. for(String item: translatortemp)
  167. {
  168. if(item.trim().equals("") == false)
  169. {
  170. translatorlist.add(item);
  171. }
  172. }
  173. }
  174. t.authorlist = authorlist;
  175. t.translatorlist = translatorlist;
  176. // System.out.println(rs.getString("Book_Id"));
  177. // System.out.println(rs.getString("Book_Title"));
  178. // System.out.println(rs.getString("Author_Name"));
  179. // System.out.println(rs.getString("Trans_Name"));
  180. // System.out.println(rs.getString("Genre_Name"));
  181. // System.out.println(rs.getString("Date_Issue"));
  182. partialdata.add(t);
  183. }
  184. }
  185.  
  186.  
  187. sql = "SELECT bookinfo.book_id "
  188. + "from bookinfo ";
  189. // + "left join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
  190. // + "left join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
  191. // + "left join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
  192. // + "where Book_Status = 1 " ;
  193. sql += " join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id ";
  194.  
  195. if(data.gettranslatorlist().size() >= 1){
  196. int tempt = 1;
  197. for(String item : data.gettranslatorlist()){
  198. sql+= " join translatorinfo t" +tempt + "on bookinfo.Book_Id = t"+ tempt + ".Book_id AND t"+ tempt + ".trans_name = " + item + " " ;
  199. tempt++;
  200. }
  201. }
  202.  
  203. sql += " join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id";
  204.  
  205. if(data.getauthorlist().size() >= 1){
  206. int tempt = 1;
  207. for(String item : data.getauthorlist()){
  208. sql+= " join authorinfo a" +tempt + "on bookinfo.Book_Id = a"+ tempt + ".Book_id AND "+ tempt + ".author_name = " + item + " " ;
  209. tempt++;
  210. }
  211. }
  212.  
  213.  
  214. sql += " where bookinfo.Book_Status = 1 ";
  215.  
  216. if(data.getTitle().trim().equals("") == false){
  217. sql += " AND bookinfo.book_title = '" + data.getTitle().trim() + "' " ;
  218. System.out.println("use this");
  219. }
  220. if(data.getDmy().trim().equals("") == false){
  221.  
  222. sql += " AND bookinfo.date_issue = '" + data.getDmy().trim() + "' " ;
  223. }
  224. if(data.getDonor().trim().equals("") == false){
  225.  
  226. sql += " AND bookinfo.donor_name = '" + data.getDonor().trim() + "' " ;
  227. }
  228. if(data.getGenre().trim().equals("none") == false ){
  229.  
  230. sql += " AND genreinfo.genre_name = '" + data.getGenre().trim() + "' " ;
  231. }
  232.  
  233. sql += "group by bookinfo.Book_Id ";
  234.  
  235.  
  236. System.out.println("test query" + sql);
  237. rs = stmt.executeQuery(sql);
  238. // System.out.println(sql);
  239.  
  240. List<String>BookID2 = new ArrayList<String>();
  241. while(rs.next()){
  242. BookID2.add(rs.getString("book_id"));
  243. }
  244.  
  245. String exactID = "";
  246. for(String item : BookID2){
  247. exactID += " or bookinfo.Book_Id = " + item;
  248.  
  249. }
  250.  
  251. if(exactID.length() > 4){
  252. exactID = exactID.substring(3);
  253.  
  254.  
  255. sql = "SELECT bookinfo.book_id,bookinfo.Book_Title,bookinfo.Date_Issue, "
  256. + "group_concat(distinct(Author_Name)) as Author_Name,group_concat(distinct(Trans_Name)) as Trans_Name,genreinfo.Genre_Name "
  257. + "from bookinfo join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
  258. + " join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
  259. + " join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
  260. + " where " + exactID
  261. + " group by bookinfo.Book_Id";
  262.  
  263. rs = stmt.executeQuery(sql);
  264.  
  265.  
  266. while (rs.next()) {
  267. BookInformation t = new BookInformation();
  268. t.Id = rs.getString("Book_Id");
  269. t.Title = rs.getString("Book_Title");
  270. t.Author = rs.getString("Author_Name");
  271. t.Translator = rs.getString("Trans_Name");
  272. t.Genre = rs.getString("Genre_Name");
  273. t.Dmy = rs.getString("Date_Issue");
  274.  
  275. String checkauthor = rs.getString("Author_Name");
  276. List<String> authorlist = new ArrayList<String>();
  277. if(checkauthor != null)
  278. {
  279. String [] authortemp = rs.getString("Author_Name").split(",");
  280. for(String item: authortemp)
  281. {
  282. if(item.trim().equals("") == false)
  283. {
  284. authorlist.add(item);
  285. }
  286. }
  287. }
  288. String checktranslator = rs.getString("Trans_Name");
  289. List<String> translatorlist = new ArrayList<String>();
  290. if(checktranslator != null)
  291. {
  292. String [] translatortemp = rs.getString("Trans_Name").split(",");
  293. for(String item: translatortemp)
  294. {
  295. if(item.trim().equals("") == false)
  296. {
  297. translatorlist.add(item);
  298. }
  299. }
  300. }
  301. t.authorlist = authorlist;
  302. t.translatorlist = translatorlist;
  303. // System.out.println(rs.getString("Book_Id"));
  304. // System.out.println(rs.getString("Book_Title"));
  305. // System.out.println(rs.getString("Author_Name"));
  306. // System.out.println(rs.getString("Trans_Name"));
  307. // System.out.println(rs.getString("Genre_Name"));
  308. // System.out.println(rs.getString("Date_Issue"));
  309. exactdata.add(t);
  310. }
  311. }
  312.  
  313.  
  314.  
  315.  
  316.  
  317.  
  318. if(partialdata.size() >= 1 || exactdata.size() >= 1){
  319. resultfound = true;
  320. }
  321.  
  322. rs.close();
  323.  
  324.  
  325.  
  326. stmt.close();
  327. conn.close();
  328. }catch(SQLException se){
  329. //Handle errors for JDBC
  330. se.printStackTrace();
  331. }catch(Exception e){
  332. //Handle errors for Class.forName
  333. e.printStackTrace();
  334. }finally{
  335. //finally block used to close resources
  336. try{
  337. if(stmt!=null)
  338. stmt.close();
  339. }catch(SQLException se2){
  340. }// nothing we can do
  341. try{
  342. if(conn!=null)
  343. conn.close();
  344. }catch(SQLException se){
  345. se.printStackTrace();
  346. }//end finally try
  347. }//end try
  348.  
  349.  
  350. }
  351.  
  352. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!