API tools faq
paste
Guest User

Untitled

a guest
Oct 7th, 2018
225
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 28.40 KB | None | 0 0
raw download clone embed print report
  1. package photoalbum;
  2.  
  3. import java.sql.*;
  4. import java.util.ArrayList;
  5.  
  6. public class DBConnector {
  7.  
  8. //Basic member variables
  9. private String dbUrl;
  10. private String dbDriver;
  11. private String dbUsername;
  12. private String dbPassword;
  13. //Member variables to store the results of the latest query
  14. //ResultSets are represented as a 2D ArrayList of objects
  15. private ArrayList<ArrayList> resultSet = new ArrayList<ArrayList>();
  16. private int rowCount, colCount;
  17. ResultSetMetaData rsmd;
  18. Connection dbConnection;
  19.  
  20. //Form a connection when the bean is instantiated. Obviously in a real system this would
  21. //be more clever, and probably use a connection pool or maintain a persistent connection etc.
  22. public DBConnector() {
  23. connect("jdbc:mysql://localhost:3306/photo_album", "org.gjt.mm.mysql.Driver", "whccw", "cw-whc$2012");
  24. }
  25.  
  26. //Method for connecting to the database using supplied arguments.
  27. private void connect(String pUrl, String pDriver, String pUsername, String pPassword) {
  28.  
  29. dbUrl = pUrl;
  30. dbDriver = pDriver;
  31. dbUsername = pUsername;
  32. dbPassword = pPassword;
  33.  
  34. try {
  35. //Register the JDBC MySQL driver
  36. Class.forName(dbDriver);
  37. //Establish a connection
  38. dbConnection = DriverManager.getConnection(pUrl, pUsername, pPassword);
  39. } catch (ClassNotFoundException ex) {
  40. System.err.println(ex);
  41. throw new RuntimeException(ex);
  42. } catch (SQLException ex) {
  43. System.err.println(ex);
  44. }
  45.  
  46. }
  47.  
  48. //Executes SELECT statements
  49. private boolean executeSQL(PreparedStatement stmt) {
  50.  
  51. ResultSet rs;
  52. clearResults();
  53.  
  54. try {
  55. //Run the statement provided as the function's argument, storing the results
  56. //in a result set object.
  57. rs = stmt.executeQuery();
  58. rsmd = rs.getMetaData();
  59.  
  60. //The column count is known but the row count isn't, but we can increment it
  61. //from 0 during the loop.
  62. rowCount = 0;
  63. colCount = rsmd.getColumnCount();
  64.  
  65. //Loop through the rows of the result set using the next() operation.
  66. while (rs.next()) {
  67. //Create a new array list to store the data for this row. Data is stored
  68. //generically as an Object.
  69. ArrayList<Object> row = new ArrayList<Object>();
  70.  
  71. //Loop through the columns in this row.
  72. for (int i = 1; i <= getColCount(); i++) {
  73. //Add each column's data to the row.
  74. row.add(rs.getObject(i));
  75. }
  76.  
  77. //Finally, add the row to the resultSet ArrayList.
  78. resultSet.add(row);
  79. rowCount++;
  80. }
  81.  
  82. rs.close();
  83. stmt.close();
  84.  
  85. } catch (SQLException ex) {
  86. System.err.println(ex);
  87. return false;
  88. }
  89. return true;
  90.  
  91. }
  92.  
  93. //Run a SQL query which modifies data (i.e. INSERT, UPDATE, DELETE)
  94. private int updateSQL(PreparedStatement stmt) {
  95.  
  96. try {
  97. //Use the executeUpdate command to run this type of statement
  98. int rowsAffected = stmt.executeUpdate();
  99. stmt.close();
  100.  
  101. return rowsAffected;
  102.  
  103. } catch (SQLException ex) {
  104. System.err.println(ex);
  105. return 0;
  106. } finally {
  107. try {
  108. dbConnection.close();
  109. } catch (SQLException ex) {
  110. System.err.println(ex);
  111. }
  112. }
  113. }
  114.  
  115. //Retrieve the data Object from the ArrayList at the given row and column index.
  116. //Note this will be 0-based, not 1-based as the JDBC ResultSet is.
  117. public Object getRecord(int pRow, int pCol) {
  118. return resultSet.get(pRow).get(pCol);
  119. }
  120.  
  121. //Get the header using the result set meta data.
  122. public String getHeader(int colIndex) {
  123. try {
  124. return rsmd.getColumnName(colIndex);
  125. } catch (SQLException ex) {
  126. System.err.println(ex);
  127. return "";
  128. }
  129. }
  130.  
  131. public int getRowCount() {
  132. return rowCount;
  133. }
  134.  
  135. public int getColCount() {
  136. return colCount;
  137. }
  138.  
  139. //Clear all the result data in preparation for running a new query.
  140. private void clearResults() {
  141. colCount = 0;
  142. rowCount = 0;
  143. resultSet.clear();
  144. }
  145.  
  146. //Closes the connection with the database. This is called at the bottom of every
  147. //page which implements this bean.
  148. public boolean closeConnection() {
  149. try {
  150. if (dbConnection != null)
  151. dbConnection.close();
  152. return true;
  153. } catch (SQLException ex) {
  154. System.err.println(ex);
  155. return false;
  156. }
  157. }
  158.  
  159. void checkUsernamePassword(String username, String password) {
  160. if (dbConnection == null)
  161. return;
  162. try {
  163. PreparedStatement stmt = dbConnection.prepareStatement("SELECT id, name FROM users WHERE username = ? AND password = ?");
  164. stmt.setString(1, username);
  165. stmt.setString(2, password);
  166. executeSQL(stmt);
  167. } catch (SQLException ex) {
  168. System.err.println(ex);
  169. }
  170. }
  171.  
  172. void getAlbumPermissions(int userId) {
  173. if (dbConnection == null)
  174. return;
  175. try {
  176. PreparedStatement stmt = dbConnection.prepareStatement("SELECT album_id, type FROM permissions WHERE user_id = ?");
  177. stmt.setInt(1, userId);
  178. executeSQL(stmt);
  179. } catch (SQLException ex) {
  180. System.err.println(ex);
  181. }
  182. }
  183.  
  184. public int userPermission(int albumId, int userId) {
  185. if (dbConnection == null)
  186. return 0;
  187. try {
  188. PreparedStatement stmt = dbConnection.prepareStatement("SELECT type FROM permissions where album_id = ? AND user_id = ? "
  189. + " UNION SELECT 777 FROM albums WHERE id = ? AND owner_id = ? "
  190. + " UNION SELECT 444 FROM albums WHERE id = ? AND is_public = 1;");
  191. stmt.setInt(1, albumId);
  192. stmt.setInt(2, userId);
  193. stmt.setInt(3, albumId);
  194. stmt.setInt(4, userId);
  195. stmt.setInt(5, albumId);
  196.  
  197. executeSQL(stmt);
  198. int maxPermission=0;
  199. for(int i=0; i<this.rowCount;i++){
  200. if(Integer.valueOf(this.getRecord(i,0).toString())>maxPermission){
  201. maxPermission=Integer.valueOf(this.getRecord(i,0).toString());
  202. }
  203. }
  204. return (maxPermission);
  205. } catch (SQLException ex) {
  206. System.err.println(ex);
  207. return 0;
  208. }
  209. }
  210.  
  211.  
  212. int changeUsersPassword(String username, String pOldPassword, String pNewPassword) {
  213. if (dbConnection == null)
  214. return 0;
  215. try {
  216. PreparedStatement stmt = dbConnection.prepareStatement("UPDATE users SET password = ? WHERE username = ? AND password = ?");
  217. stmt.setString(1, pNewPassword);
  218. stmt.setString(2, username);
  219. stmt.setString(3, pOldPassword);
  220. return updateSQL(stmt);
  221. } catch (SQLException ex) {
  222. System.err.println(ex);
  223. return 0;
  224. }
  225. }
  226.  
  227. void search(String searchString) {
  228. if (dbConnection == null)
  229. return;
  230. try {
  231. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.id FROM photos p JOIN albums a ON p.album_id = a.id WHERE UPPER(p.description) LIKE UPPER('%' ? '%') OR UPPER(p.title) LIKE UPPER('%' ? '%')");
  232. stmt.setString(1, searchString);
  233. stmt.setString(2, searchString);
  234. executeSQL(stmt);
  235. } catch (SQLException ex) {
  236. System.err.println(ex);
  237. }
  238. }
  239.  
  240. public void searchWithPermissionCheck(String searchString, int userId) {
  241. if (dbConnection == null)
  242. return;
  243. try {
  244. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.id FROM photos p JOIN albums a ON p.album_id = a.id AND (a.is_public = 1 OR a.owner_id = ? OR EXISTS(SELECT 1 FROM permissions p WHERE p.album_id = a.id AND p.user_id = ?)) WHERE UPPER(p.description) LIKE UPPER('%' ? '%') OR UPPER(p.title) LIKE UPPER('%' ? '%')");
  245. stmt.setInt(1, userId);
  246. stmt.setInt(2, userId);
  247. stmt.setString(3, searchString);
  248. stmt.setString(4, searchString);
  249. executeSQL(stmt);
  250. } catch (SQLException ex) {
  251. System.err.println(ex);
  252. }
  253. }
  254.  
  255. public void getAlbumIdFromPhotoId(String photoId) {
  256. if (dbConnection == null)
  257. return;
  258. try {
  259. PreparedStatement stmt = dbConnection.prepareStatement("SELECT album_id FROM photos WHERE id = ?");
  260. stmt.setString(1, photoId);
  261. executeSQL(stmt);
  262. } catch (SQLException ex) {
  263. System.err.println(ex);
  264. }
  265. }
  266.  
  267. public int addComment(String photoId, int userId, String commentText) {
  268. if (dbConnection == null)
  269. return 0;
  270. try {
  271. PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO comments(photo_id, user_id, comment) VALUES(?, ?, ?)");
  272. stmt.setString(1, photoId);
  273. stmt.setInt(2, userId);
  274. stmt.setString(3, commentText);
  275. return updateSQL(stmt);
  276. } catch (SQLException ex) {
  277. System.err.println(ex);
  278. return 0;
  279. }
  280. }
  281.  
  282. public int addContact(String issueType, String name, String email, String text) {
  283. if (dbConnection == null)
  284. return 0;
  285. try {
  286. PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO contact(issue_type,name,email,text) VALUES(?, ?, ?, ?)");
  287. stmt.setString(1, issueType);
  288. stmt.setString(2, name);
  289. stmt.setString(3, email);
  290. stmt.setString(4, text);
  291. return updateSQL(stmt);
  292. } catch (SQLException ex) {
  293. System.err.println(ex);
  294. return 0;
  295. }
  296. }
  297.  
  298. public boolean hasAlbum(String albumId) {
  299. if (dbConnection == null)
  300. return false;
  301. try {
  302. PreparedStatement stmt = dbConnection.prepareStatement("SELECT 1 FROM albums a WHERE id = ?");
  303. stmt.setString(1, albumId);
  304. executeSQL(stmt);
  305. return (getRowCount() != 0);
  306. } catch (SQLException ex) {
  307. System.err.println(ex);
  308. return false;
  309. }
  310. }
  311.  
  312. public void getAlbumInfo(String albumId) {
  313. if (dbConnection == null)
  314. return;
  315. try {
  316. PreparedStatement stmt = dbConnection.prepareStatement("SELECT a.title, a.description, (SELECT COUNT(*) FROM permissions p WHERE p.album_id = a.id ) FROM albums a WHERE a.id = ?");
  317. stmt.setString(1, albumId);
  318. executeSQL(stmt);
  319. } catch (SQLException ex) {
  320. System.err.println(ex);
  321. }
  322. }
  323.  
  324. public void getPhotosInAlbum(String albumId) {
  325. if (dbConnection == null)
  326. return;
  327. try {
  328. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.id FROM photos p WHERE p.album_id = ?");
  329. stmt.setString(1, albumId);
  330. executeSQL(stmt);
  331. } catch (SQLException ex) {
  332. System.err.println(ex);
  333. }
  334. }
  335.  
  336. public void getIndexCoverInfo(int userId) {
  337. if (dbConnection == null)
  338. return;
  339. try {
  340. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, a.title, p.album_id FROM photos p JOIN albums a ON p.album_id = a.id AND (a.is_public = 1 OR a.owner_id = ? OR EXISTS(SELECT 1 FROM permissions pr WHERE pr.album_id = a.id AND pr.user_id = ?)) WHERE (p.id = (SELECT p2.id FROM photos p2 WHERE p2.album_id = p.album_id ORDER BY p2.id LIMIT 1))");
  341. stmt.setInt(1, userId);
  342. stmt.setInt(2, userId);
  343. executeSQL(stmt);
  344. } catch (SQLException ex) {
  345. System.err.println(ex);
  346. }
  347. }
  348.  
  349. public boolean hasPhoto(String photoId) {
  350. if (dbConnection == null)
  351. return false;
  352. try {
  353. PreparedStatement stmt = dbConnection.prepareStatement("SELECT 1 FROM photos WHERE id = ?");
  354. stmt.setString(1, photoId);
  355. executeSQL(stmt);
  356. return (getRowCount() != 0);
  357. } catch (SQLException ex) {
  358. System.err.println(ex);
  359. return false;
  360. }
  361. }
  362.  
  363. public void getPhotoInfo(String photoId) {
  364. if (dbConnection == null)
  365. return;
  366. try {
  367. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.description, a.title, a.id FROM photos p JOIN albums a ON p.album_id = a.id WHERE p.id = ?");
  368. stmt.setString(1, photoId);
  369. executeSQL(stmt);
  370. } catch (SQLException ex) {
  371. System.err.println(ex);
  372. }
  373. }
  374.  
  375. public int deletePhoto(String photoId) {
  376. if (dbConnection == null)
  377. return 0;
  378. try {
  379. PreparedStatement stmt = dbConnection.prepareStatement("DELETE FROM photos WHERE id = ?");
  380. stmt.setString(1, photoId);
  381. return updateSQL(stmt);
  382. } catch (SQLException ex) {
  383. System.err.println(ex);
  384. return 0;
  385. }
  386. }
  387.  
  388. public void getCommentsForPhoto(String photoId) {
  389. if (dbConnection == null)
  390. return;
  391. try {
  392. PreparedStatement stmt = dbConnection.prepareStatement("SELECT c.comment, u.name FROM comments c JOIN users u ON c.user_id = u.id WHERE c.photo_id = ?");
  393. stmt.setString(1, photoId);
  394. executeSQL(stmt);
  395. } catch (SQLException ex) {
  396. System.err.println(ex);
  397. }
  398. }
  399.  
  400. public int addPhoto(String title, String description, String srcName, String albumId) {
  401. if (dbConnection == null)
  402. return 0;
  403. try {
  404. PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO photos(title, description, src, album_id) " + "VALUES(?, ?, ?, ?)");
  405. stmt.setString(1, title);
  406. stmt.setString(2, description);
  407. stmt.setString(3, srcName);
  408. stmt.setString(4, albumId);
  409. return updateSQL(stmt);
  410. } catch (SQLException ex) {
  411. System.err.println(ex);
  412. return 0;
  413. }
  414. }
  415. }package photoalbum;
  416.  
  417. import java.sql.*;
  418. import java.util.ArrayList;
  419.  
  420. public class DBConnector {
  421.  
  422. //Basic member variables
  423. private String dbUrl;
  424. private String dbDriver;
  425. private String dbUsername;
  426. private String dbPassword;
  427. //Member variables to store the results of the latest query
  428. //ResultSets are represented as a 2D ArrayList of objects
  429. private ArrayList<ArrayList> resultSet = new ArrayList<ArrayList>();
  430. private int rowCount, colCount;
  431. ResultSetMetaData rsmd;
  432. Connection dbConnection;
  433.  
  434. //Form a connection when the bean is instantiated. Obviously in a real system this would
  435. //be more clever, and probably use a connection pool or maintain a persistent connection etc.
  436. public DBConnector() {
  437. connect("jdbc:mysql://localhost:3306/photo_album", "org.gjt.mm.mysql.Driver", "whccw", "cw-whc$2012");
  438. }
  439.  
  440. //Method for connecting to the database using supplied arguments.
  441. private void connect(String pUrl, String pDriver, String pUsername, String pPassword) {
  442.  
  443. dbUrl = pUrl;
  444. dbDriver = pDriver;
  445. dbUsername = pUsername;
  446. dbPassword = pPassword;
  447.  
  448. try {
  449. //Register the JDBC MySQL driver
  450. Class.forName(dbDriver);
  451. //Establish a connection
  452. dbConnection = DriverManager.getConnection(pUrl, pUsername, pPassword);
  453. } catch (ClassNotFoundException ex) {
  454. System.err.println(ex);
  455. throw new RuntimeException(ex);
  456. } catch (SQLException ex) {
  457. System.err.println(ex);
  458. }
  459.  
  460. }
  461.  
  462. //Executes SELECT statements
  463. private boolean executeSQL(PreparedStatement stmt) {
  464.  
  465. ResultSet rs;
  466. clearResults();
  467.  
  468. try {
  469. //Run the statement provided as the function's argument, storing the results
  470. //in a result set object.
  471. rs = stmt.executeQuery();
  472. rsmd = rs.getMetaData();
  473.  
  474. //The column count is known but the row count isn't, but we can increment it
  475. //from 0 during the loop.
  476. rowCount = 0;
  477. colCount = rsmd.getColumnCount();
  478.  
  479. //Loop through the rows of the result set using the next() operation.
  480. while (rs.next()) {
  481. //Create a new array list to store the data for this row. Data is stored
  482. //generically as an Object.
  483. ArrayList<Object> row = new ArrayList<Object>();
  484.  
  485. //Loop through the columns in this row.
  486. for (int i = 1; i <= getColCount(); i++) {
  487. //Add each column's data to the row.
  488. row.add(rs.getObject(i));
  489. }
  490.  
  491. //Finally, add the row to the resultSet ArrayList.
  492. resultSet.add(row);
  493. rowCount++;
  494. }
  495.  
  496. rs.close();
  497. stmt.close();
  498.  
  499. } catch (SQLException ex) {
  500. System.err.println(ex);
  501. return false;
  502. }
  503. return true;
  504.  
  505. }
  506.  
  507. //Run a SQL query which modifies data (i.e. INSERT, UPDATE, DELETE)
  508. private int updateSQL(PreparedStatement stmt) {
  509.  
  510. try {
  511. //Use the executeUpdate command to run this type of statement
  512. int rowsAffected = stmt.executeUpdate();
  513. stmt.close();
  514.  
  515. return rowsAffected;
  516.  
  517. } catch (SQLException ex) {
  518. System.err.println(ex);
  519. return 0;
  520. } finally {
  521. try {
  522. dbConnection.close();
  523. } catch (SQLException ex) {
  524. System.err.println(ex);
  525. }
  526. }
  527. }
  528.  
  529. //Retrieve the data Object from the ArrayList at the given row and column index.
  530. //Note this will be 0-based, not 1-based as the JDBC ResultSet is.
  531. public Object getRecord(int pRow, int pCol) {
  532. return resultSet.get(pRow).get(pCol);
  533. }
  534.  
  535. //Get the header using the result set meta data.
  536. public String getHeader(int colIndex) {
  537. try {
  538. return rsmd.getColumnName(colIndex);
  539. } catch (SQLException ex) {
  540. System.err.println(ex);
  541. return "";
  542. }
  543. }
  544.  
  545. public int getRowCount() {
  546. return rowCount;
  547. }
  548.  
  549. public int getColCount() {
  550. return colCount;
  551. }
  552.  
  553. //Clear all the result data in preparation for running a new query.
  554. private void clearResults() {
  555. colCount = 0;
  556. rowCount = 0;
  557. resultSet.clear();
  558. }
  559.  
  560. //Closes the connection with the database. This is called at the bottom of every
  561. //page which implements this bean.
  562. public boolean closeConnection() {
  563. try {
  564. if (dbConnection != null)
  565. dbConnection.close();
  566. return true;
  567. } catch (SQLException ex) {
  568. System.err.println(ex);
  569. return false;
  570. }
  571. }
  572.  
  573. void checkUsernamePassword(String username, String password) {
  574. if (dbConnection == null)
  575. return;
  576. try {
  577. PreparedStatement stmt = dbConnection.prepareStatement("SELECT id, name FROM users WHERE username = ? AND password = ?");
  578. stmt.setString(1, username);
  579. stmt.setString(2, password);
  580. executeSQL(stmt);
  581. } catch (SQLException ex) {
  582. System.err.println(ex);
  583. }
  584. }
  585.  
  586. void getAlbumPermissions(int userId) {
  587. if (dbConnection == null)
  588. return;
  589. try {
  590. PreparedStatement stmt = dbConnection.prepareStatement("SELECT album_id, type FROM permissions WHERE user_id = ?");
  591. stmt.setInt(1, userId);
  592. executeSQL(stmt);
  593. } catch (SQLException ex) {
  594. System.err.println(ex);
  595. }
  596. }
  597.  
  598. public int userPermission(int albumId, int userId) {
  599. if (dbConnection == null)
  600. return 0;
  601. try {
  602. PreparedStatement stmt = dbConnection.prepareStatement("SELECT type FROM permissions where album_id = ? AND user_id = ? "
  603. + " UNION SELECT 777 FROM albums WHERE id = ? AND owner_id = ? "
  604. + " UNION SELECT 444 FROM albums WHERE id = ? AND is_public = 1;");
  605. stmt.setInt(1, albumId);
  606. stmt.setInt(2, userId);
  607. stmt.setInt(3, albumId);
  608. stmt.setInt(4, userId);
  609. stmt.setInt(5, albumId);
  610.  
  611. executeSQL(stmt);
  612. int maxPermission=0;
  613. for(int i=0; i<this.rowCount;i++){
  614. if(Integer.valueOf(this.getRecord(i,0).toString())>maxPermission){
  615. maxPermission=Integer.valueOf(this.getRecord(i,0).toString());
  616. }
  617. }
  618. return (maxPermission);
  619. } catch (SQLException ex) {
  620. System.err.println(ex);
  621. return 0;
  622. }
  623. }
  624.  
  625.  
  626. int changeUsersPassword(String username, String pOldPassword, String pNewPassword) {
  627. if (dbConnection == null)
  628. return 0;
  629. try {
  630. PreparedStatement stmt = dbConnection.prepareStatement("UPDATE users SET password = ? WHERE username = ? AND password = ?");
  631. stmt.setString(1, pNewPassword);
  632. stmt.setString(2, username);
  633. stmt.setString(3, pOldPassword);
  634. return updateSQL(stmt);
  635. } catch (SQLException ex) {
  636. System.err.println(ex);
  637. return 0;
  638. }
  639. }
  640.  
  641. void search(String searchString) {
  642. if (dbConnection == null)
  643. return;
  644. try {
  645. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.id FROM photos p JOIN albums a ON p.album_id = a.id WHERE UPPER(p.description) LIKE UPPER('%' ? '%') OR UPPER(p.title) LIKE UPPER('%' ? '%')");
  646. stmt.setString(1, searchString);
  647. stmt.setString(2, searchString);
  648. executeSQL(stmt);
  649. } catch (SQLException ex) {
  650. System.err.println(ex);
  651. }
  652. }
  653.  
  654. public void searchWithPermissionCheck(String searchString, int userId) {
  655. if (dbConnection == null)
  656. return;
  657. try {
  658. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.id FROM photos p JOIN albums a ON p.album_id = a.id AND (a.is_public = 1 OR a.owner_id = ? OR EXISTS(SELECT 1 FROM permissions p WHERE p.album_id = a.id AND p.user_id = ?)) WHERE UPPER(p.description) LIKE UPPER('%' ? '%') OR UPPER(p.title) LIKE UPPER('%' ? '%')");
  659. stmt.setInt(1, userId);
  660. stmt.setInt(2, userId);
  661. stmt.setString(3, searchString);
  662. stmt.setString(4, searchString);
  663. executeSQL(stmt);
  664. } catch (SQLException ex) {
  665. System.err.println(ex);
  666. }
  667. }
  668.  
  669. public void getAlbumIdFromPhotoId(String photoId) {
  670. if (dbConnection == null)
  671. return;
  672. try {
  673. PreparedStatement stmt = dbConnection.prepareStatement("SELECT album_id FROM photos WHERE id = ?");
  674. stmt.setString(1, photoId);
  675. executeSQL(stmt);
  676. } catch (SQLException ex) {
  677. System.err.println(ex);
  678. }
  679. }
  680.  
  681. public int addComment(String photoId, int userId, String commentText) {
  682. if (dbConnection == null)
  683. return 0;
  684. try {
  685. PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO comments(photo_id, user_id, comment) VALUES(?, ?, ?)");
  686. stmt.setString(1, photoId);
  687. stmt.setInt(2, userId);
  688. stmt.setString(3, commentText);
  689. return updateSQL(stmt);
  690. } catch (SQLException ex) {
  691. System.err.println(ex);
  692. return 0;
  693. }
  694. }
  695.  
  696. public int addContact(String issueType, String name, String email, String text) {
  697. if (dbConnection == null)
  698. return 0;
  699. try {
  700. PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO contact(issue_type,name,email,text) VALUES(?, ?, ?, ?)");
  701. stmt.setString(1, issueType);
  702. stmt.setString(2, name);
  703. stmt.setString(3, email);
  704. stmt.setString(4, text);
  705. return updateSQL(stmt);
  706. } catch (SQLException ex) {
  707. System.err.println(ex);
  708. return 0;
  709. }
  710. }
  711.  
  712. public boolean hasAlbum(String albumId) {
  713. if (dbConnection == null)
  714. return false;
  715. try {
  716. PreparedStatement stmt = dbConnection.prepareStatement("SELECT 1 FROM albums a WHERE id = ?");
  717. stmt.setString(1, albumId);
  718. executeSQL(stmt);
  719. return (getRowCount() != 0);
  720. } catch (SQLException ex) {
  721. System.err.println(ex);
  722. return false;
  723. }
  724. }
  725.  
  726. public void getAlbumInfo(String albumId) {
  727. if (dbConnection == null)
  728. return;
  729. try {
  730. PreparedStatement stmt = dbConnection.prepareStatement("SELECT a.title, a.description, (SELECT COUNT(*) FROM permissions p WHERE p.album_id = a.id ) FROM albums a WHERE a.id = ?");
  731. stmt.setString(1, albumId);
  732. executeSQL(stmt);
  733. } catch (SQLException ex) {
  734. System.err.println(ex);
  735. }
  736. }
  737.  
  738. public void getPhotosInAlbum(String albumId) {
  739. if (dbConnection == null)
  740. return;
  741. try {
  742. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.id FROM photos p WHERE p.album_id = ?");
  743. stmt.setString(1, albumId);
  744. executeSQL(stmt);
  745. } catch (SQLException ex) {
  746. System.err.println(ex);
  747. }
  748. }
  749.  
  750. public void getIndexCoverInfo(int userId) {
  751. if (dbConnection == null)
  752. return;
  753. try {
  754. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, a.title, p.album_id FROM photos p JOIN albums a ON p.album_id = a.id AND (a.is_public = 1 OR a.owner_id = ? OR EXISTS(SELECT 1 FROM permissions pr WHERE pr.album_id = a.id AND pr.user_id = ?)) WHERE (p.id = (SELECT p2.id FROM photos p2 WHERE p2.album_id = p.album_id ORDER BY p2.id LIMIT 1))");
  755. stmt.setInt(1, userId);
  756. stmt.setInt(2, userId);
  757. executeSQL(stmt);
  758. } catch (SQLException ex) {
  759. System.err.println(ex);
  760. }
  761. }
  762.  
  763. public boolean hasPhoto(String photoId) {
  764. if (dbConnection == null)
  765. return false;
  766. try {
  767. PreparedStatement stmt = dbConnection.prepareStatement("SELECT 1 FROM photos WHERE id = ?");
  768. stmt.setString(1, photoId);
  769. executeSQL(stmt);
  770. return (getRowCount() != 0);
  771. } catch (SQLException ex) {
  772. System.err.println(ex);
  773. return false;
  774. }
  775. }
  776.  
  777. public void getPhotoInfo(String photoId) {
  778. if (dbConnection == null)
  779. return;
  780. try {
  781. PreparedStatement stmt = dbConnection.prepareStatement("SELECT p.src, p.title, p.description, a.title, a.id FROM photos p JOIN albums a ON p.album_id = a.id WHERE p.id = ?");
  782. stmt.setString(1, photoId);
  783. executeSQL(stmt);
  784. } catch (SQLException ex) {
  785. System.err.println(ex);
  786. }
  787. }
  788.  
  789. public int deletePhoto(String photoId) {
  790. if (dbConnection == null)
  791. return 0;
  792. try {
  793. PreparedStatement stmt = dbConnection.prepareStatement("DELETE FROM photos WHERE id = ?");
  794. stmt.setString(1, photoId);
  795. return updateSQL(stmt);
  796. } catch (SQLException ex) {
  797. System.err.println(ex);
  798. return 0;
  799. }
  800. }
  801.  
  802. public void getCommentsForPhoto(String photoId) {
  803. if (dbConnection == null)
  804. return;
  805. try {
  806. PreparedStatement stmt = dbConnection.prepareStatement("SELECT c.comment, u.name FROM comments c JOIN users u ON c.user_id = u.id WHERE c.photo_id = ?");
  807. stmt.setString(1, photoId);
  808. executeSQL(stmt);
  809. } catch (SQLException ex) {
  810. System.err.println(ex);
  811. }
  812. }
  813.  
  814. public int addPhoto(String title, String description, String srcName, String albumId) {
  815. if (dbConnection == null)
  816. return 0;
  817. try {
  818. PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO photos(title, description, src, album_id) " + "VALUES(?, ?, ?, ?)");
  819. stmt.setString(1, title);
  820. stmt.setString(2, description);
  821. stmt.setString(3, srcName);
  822. stmt.setString(4, albumId);
  823. return updateSQL(stmt);
  824. } catch (SQLException ex) {
  825. System.err.println(ex);
  826. return 0;
  827. }
  828. }
  829. }
Add Comment
Please, Sign In to add comment
Public Pastes
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!