API tools faq
paste
Guest User

Untitled

a guest
Jan 21st, 2019
131
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 31.88 KB | None | 0 0
raw download clone embed print report
  1. package dbsettings;
  2.  
  3.  
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.PreparedStatement;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  10. import java.util.ArrayList;
  11.  
  12.  
  13. public class db {
  14. Connection con = null;
  15.  
  16. //Setup
  17. public db(){
  18.  
  19. try {
  20. Class.forName("org.sqlite.JDBC");
  21. } catch (ClassNotFoundException e) {
  22. e.printStackTrace();
  23. }
  24. try {
  25. con = DriverManager.getConnection("jdbc:sqlite:database/forums.db");
  26. } catch (SQLException e) {
  27. e.printStackTrace();
  28. }
  29. }
  30. //Create content
  31. public void createTables() throws SQLException{ //lai izveidotu jaunu tabulu db
  32. Statement stat = con.createStatement();
  33. stat.execute("CREATE TABLE reply(ID_reply INTEGER PRIMARY KEY AUTOINCREMENT, ID_topic INTEGER, ID_user INTEGER, date TEXT, replycontent TEXT)");
  34. }
  35.  
  36. public void createtrigger() throws SQLException{
  37. PreparedStatement st = con.prepareStatement("CREATE TRIGGER delete_reply AFTER DELETE ON topic BEGIN DELETE FROM reply WHERE ID_topic=OLD.ID_topic; END;");
  38. int rowsEffected = st.executeUpdate();
  39. if(rowsEffected != 0)
  40. System.out.println("Trigger added");
  41. }
  42. //Testing
  43. public void getallusers() throws SQLException {
  44. Statement stat = con.createStatement();
  45. ResultSet rezultati = stat.executeQuery("SELECT * FROM userlist");
  46. while(rezultati.next()){
  47. int idNolasitais = rezultati.getInt("ID_user");
  48. String readusername = rezultati.getString("username");
  49. String readpassword = rezultati.getString("password");
  50. System.out.println("---");
  51. System.out.println("ID:" + idNolasitais + " username:" + readusername + " pw:" + readpassword);
  52. }
  53.  
  54. }
  55. public void getalluserlevels() throws SQLException {
  56. Statement stat = con.createStatement();
  57. ResultSet rezultati = stat.executeQuery("SELECT * FROM userlevel;");
  58. while(rezultati.next()){
  59. String readusername = rezultati.getString("username");
  60. String readuselevel = rezultati.getString("userlevel");
  61. System.out.println("---");
  62. System.out.println("username:" + readusername + " level:" + readuselevel);
  63. }
  64. }
  65. public ArrayList<String> getallcategory() throws SQLException{
  66. ArrayList<String> result = new ArrayList<>();
  67. PreparedStatement st = con.prepareStatement("SELECT * FROM category;");
  68. ResultSet rezultati = st.executeQuery();
  69. while(rezultati.next()) {
  70. int tempid = rezultati.getInt("ID_category");
  71. String tempname = rezultati.getString("name");
  72. String tempuserlevel = rezultati.getString("userlevel");
  73. result.add("ID:" + tempid + ",Name:" + tempname + ",Userlevel:" + tempuserlevel);
  74. }
  75. return result;
  76. }
  77. public void printallreply() throws SQLException{ //Trigera testam
  78. Statement stat = con.createStatement();
  79. ResultSet rezultati = stat.executeQuery("SELECT * FROM reply");
  80. while(rezultati.next()){
  81. int idTopic = rezultati.getInt("ID_topic");
  82. int idReply = rezultati.getInt("ID_reply");
  83. System.out.println("Topic id: " + idTopic + " Reply id: "+ idReply);
  84. }
  85. }
  86. //User management
  87. public boolean adduser(String username, String password) throws SQLException {
  88. boolean status = false;
  89. PreparedStatement stcheck = con.prepareStatement("SELECT username FROM userlist WHERE username=?;");
  90. stcheck.setString(1, username);
  91. ResultSet result = stcheck.executeQuery();
  92. if(!result.isBeforeFirst()) {
  93. PreparedStatement st = con.prepareStatement("INSERT INTO userlist(username, password)VALUES(?, ?);");
  94. st.setString(1, username);
  95. st.setString(2, password);
  96. int rowsEffected = st.executeUpdate();
  97. if(rowsEffected != 0)
  98. status = true;
  99. else
  100. status = false;
  101. return status;
  102. }
  103. else
  104. return status;
  105. }
  106. public boolean deluser(int id, String username) throws SQLException {
  107. boolean status = false;
  108. PreparedStatement st2 = con.prepareStatement("DELETE FROM userlevel WHERE username=?;");
  109. st2.setString(1, username);
  110. st2.executeUpdate();
  111. PreparedStatement st = con.prepareStatement("DELETE FROM userlist WHERE ID_user=?;");
  112. st.setInt(1, id);
  113. int rowsEffected = st.executeUpdate();
  114. if(rowsEffected != 0)
  115. status = true;
  116. else
  117. status = false;
  118. return status;
  119. }
  120. public boolean adduserlevel(String username, String userlevel) throws SQLException {
  121. boolean status = false;
  122. PreparedStatement lvlcheck = con.prepareStatement("SELECT username FROM userlevel WHERE username=?;");
  123. lvlcheck.setString(1, username);
  124. ResultSet lvlresult = lvlcheck.executeQuery();
  125. if(!lvlresult.isBeforeFirst()) {
  126. PreparedStatement st = con.prepareStatement("INSERT INTO userlevel(username, userlevel)VALUES(?, ?);");
  127. st.setString(1, username);
  128. st.setString(2, userlevel);
  129. int rowsEffected = st.executeUpdate();
  130. if(rowsEffected != 0)
  131. status = true;
  132. else
  133. status = false;
  134. return status;
  135. }
  136. else {
  137. PreparedStatement st = con.prepareStatement("UPDATE userlevel SET userlevel=? WHERE username=?;");
  138. st.setString(1, userlevel);
  139. st.setString(2, username);
  140. int rowsEffected = st.executeUpdate();
  141. if(rowsEffected != 0)
  142. status = true;
  143. else
  144. status = false;
  145. return status;
  146. }
  147. }
  148. public boolean changename(String username, String newusername) throws SQLException {
  149. boolean status = checkIfUserExists(username);
  150. if(status) {
  151. PreparedStatement st = con.prepareStatement("UPDATE userlist SET username=? WHERE username=?;");
  152. st.setString(1, newusername);
  153. st.setString(2, username);
  154. int rowsEffected = st.executeUpdate();
  155. if(rowsEffected != 0) {
  156. String temp = getuserlevel(username);
  157. if(!temp.equals("")) {
  158. PreparedStatement st2 = con.prepareStatement("UPDATE userlevel SET username=? WHERE username=?;");
  159. st2.setString(1, newusername);
  160. st2.setString(2, username);
  161. st2.executeUpdate();
  162. }
  163. return true;
  164. }
  165. else
  166. return false;
  167. }
  168. else {
  169. return false;
  170. }
  171. }
  172. public boolean changepassword(int ID_user, String newpassword) throws SQLException{
  173. PreparedStatement st = con.prepareStatement("UPDATE userlist SET password=? WHERE ID_user=?");
  174. st.setString(1, newpassword);
  175. st.setInt(2, ID_user);
  176. int rowsEffected = st.executeUpdate();
  177. if(rowsEffected != 0)
  178. return true;
  179. else
  180. return false;
  181. }
  182. public boolean login(String username, String password) throws SQLException{
  183. boolean status = false;
  184. PreparedStatement check = con.prepareStatement("SELECT username, password FROM userlist WHERE username=? and password=?;");
  185. check.setString(1, username);
  186. check.setString(2, password);
  187. ResultSet result = check.executeQuery();
  188. if(result.next())
  189. status = true;
  190. else
  191. status = false;
  192. return status;
  193. }
  194. //User functions
  195. public boolean checkIfUserExists(String username) throws SQLException{
  196. boolean status = true;
  197. PreparedStatement check = con.prepareStatement("SELECT username FROM userlist WHERE username=?;");
  198. check.setString(1, username);
  199. ResultSet result = check.executeQuery();
  200. if(result.next())
  201. status = true;
  202. else
  203. status = false;
  204. return status;
  205. }
  206. public String getuserlevel(String username) throws SQLException{
  207. PreparedStatement get = con.prepareStatement("SELECT userlevel FROM userlevel WHERE username=?;");
  208. get.setString(1, username);
  209. ResultSet result = get.executeQuery();
  210. String userlevelresult = "";
  211. while(result.next()) {
  212. userlevelresult = result.getString("userlevel");
  213. }
  214. return userlevelresult;
  215. }
  216. public String getusernamefromid(int ID_user) throws SQLException{
  217. PreparedStatement st = con.prepareStatement("SELECT username FROM userlist WHERE ID_user=?");
  218. st.setInt(1, ID_user);
  219. ResultSet rezultati = st.executeQuery();
  220. String username = "";
  221. while(rezultati.next()) {
  222. username = rezultati.getString("username");
  223. }
  224. return username;
  225. }
  226. public int getuserid(String username) throws SQLException{
  227. PreparedStatement st = con.prepareStatement("SELECT ID_user FROM userlist WHERE username=?;");
  228. st.setString(1, username);
  229. ResultSet rezultati = st.executeQuery();
  230. int idNolasitais = 0;
  231. while(rezultati.next()){
  232. idNolasitais = rezultati.getInt("ID_user");
  233. }
  234. return idNolasitais;
  235. }
  236. //Category functions
  237. public boolean addcategory(String name, String userlevel) throws SQLException{
  238. PreparedStatement st = con.prepareStatement("INSERT INTO category(name, userlevel) VALUES(?, ?)");
  239. st.setString(1, name);
  240. st.setString(2, userlevel);
  241. int rowsEffected = st.executeUpdate();
  242. if(rowsEffected != 0)
  243. return true;
  244. else
  245. return false;
  246. }
  247. public boolean deletecategory(String name) throws SQLException{
  248. PreparedStatement st = con.prepareStatement("DELETE FROM category WHERE name=?");
  249. st.setString(1, name);
  250. int rowsEffected = st.executeUpdate();
  251. if(rowsEffected != 0)
  252. return true;
  253. else
  254. return false;
  255. }
  256. public boolean updatecategory(String name, String newname, String userlevel) throws SQLException{
  257. PreparedStatement st = con.prepareStatement("UPDATE category SET name=?, userlevel=? WHERE name=?");
  258. st.setString(1, newname);
  259. st.setString(2, userlevel);
  260. st.setString(3, name);
  261. int rowsEffected = st.executeUpdate();
  262. if(rowsEffected != 0)
  263. return true;
  264. else
  265. return false;
  266. }
  267. public ArrayList<Integer> getallcategoryid() throws SQLException{
  268. ArrayList<Integer> result = new ArrayList<>();
  269. PreparedStatement st = con.prepareStatement("SELECT ID_category FROM category ORDER BY ID_category;");
  270. ResultSet rezultati = st.executeQuery();
  271. while(rezultati.next()) {
  272. int tempid = rezultati.getInt("ID_category");
  273. result.add(tempid);
  274. }
  275. return result;
  276. }
  277. public ArrayList<String> getallcategoryname() throws SQLException{
  278. ArrayList<String> result = new ArrayList<>();
  279. PreparedStatement st = con.prepareStatement("SELECT name FROM category ORDER BY ID_category;");
  280. ResultSet rezultati = st.executeQuery();
  281. while(rezultati.next()) {
  282. String tempname = rezultati.getString("name");
  283. result.add(tempname);
  284. }
  285. return result;
  286. }
  287. public ArrayList<String> getallcategoryuserlevel() throws SQLException{
  288. ArrayList<String> result = new ArrayList<>();
  289. PreparedStatement st = con.prepareStatement("SELECT userlevel FROM category ORDER BY ID_category;");
  290. ResultSet rezultati = st.executeQuery();
  291. while(rezultati.next()) {
  292. String tempname = rezultati.getString("userlevel");
  293. result.add(tempname);
  294. }
  295. return result;
  296. }
  297. public String getcategoryname(int ID_category) throws SQLException{
  298. PreparedStatement st = con.prepareStatement("SELECT name FROM category WHERE ID_category=?");
  299. st.setInt(1, ID_category);
  300. ResultSet rezultati = st.executeQuery();
  301. String tempname = "";
  302. while(rezultati.next()) {
  303. tempname = rezultati.getString("name");
  304. }
  305. return tempname;
  306. }
  307. public int getcategoryid(String categoryname) throws SQLException{
  308. PreparedStatement st = con.prepareStatement("SELECT ID_category FROM category WHERE name=?");
  309. st.setString(1, categoryname);
  310. ResultSet rezultati = st.executeQuery();
  311. int id = 0;
  312. while(rezultati.next()) {
  313. id = rezultati.getInt("ID_category");
  314. }
  315. return id;
  316. }
  317. public int getcategoryid(int ID_topic) throws SQLException{
  318. PreparedStatement st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=?");
  319. st.setInt(1, ID_topic);
  320. ResultSet rezultati = st.executeQuery();
  321. int id = 0;
  322. while(rezultati.next()){
  323. id = rezultati.getInt("ID_category");
  324. }
  325. return id;
  326. }
  327. public ArrayList<String> getallcategorynameforuserlevel(String userlevel) throws SQLException{
  328. ArrayList<String> result = new ArrayList<>();
  329. ResultSet rezultati = null;
  330. if(userlevel.equals("")){
  331. PreparedStatement st = con.prepareStatement("SELECT name FROM category WHERE userlevel='' ORDER BY ID_category;");
  332. rezultati = st.executeQuery();
  333. }
  334. else if(userlevel.equals("mod")) {
  335. PreparedStatement st = con.prepareStatement("SELECT name FROM category WHERE userlevel='mod' or userlevel='' ORDER BY ID_category;");
  336. rezultati = st.executeQuery();
  337. }
  338. else if(userlevel.equals("admin")) {
  339. PreparedStatement st = con.prepareStatement("SELECT name FROM category ORDER BY ID_category;");
  340. rezultati = st.executeQuery();
  341. }
  342. while(rezultati.next()) {
  343. String tempname = rezultati.getString("name");
  344. result.add(tempname);
  345. }
  346. return result;
  347. }
  348. //Topic functions
  349. public boolean addtopic(int ID_category, int ID_user, String date, int priority, String topicname, String topiccontent) throws SQLException{
  350. PreparedStatement st = con.prepareStatement("INSERT INTO topic(ID_category, ID_user, date, priority, topicname, topiccontent) VALUES(?, ?, ?, ?, ?, ?)");
  351. st.setInt(1, ID_category);
  352. st.setInt(2, ID_user);
  353. st.setString(3, date);
  354. st.setInt(4, priority);
  355. st.setString(5, topicname);
  356. st.setString(6, topiccontent);
  357. int rowsEffected = st.executeUpdate();
  358. if(rowsEffected != 0)
  359. return true;
  360. else
  361. return false;
  362. }
  363. public boolean deltopic(int ID_topic) throws SQLException{
  364. PreparedStatement st = con.prepareStatement("DELETE FROM topic WHERE ID_topic=?");
  365. st.setInt(1, ID_topic);
  366. int rowsEffected = st.executeUpdate();
  367. if(rowsEffected != 0)
  368. return true;
  369. else
  370. return false;
  371. }
  372. public ArrayList<String> gettopicnames(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{
  373. ArrayList<String> result = new ArrayList<>();
  374. PreparedStatement st = null;
  375. ResultSet rezultati = null;
  376. switch(type) {
  377. case "profiletopics":
  378. st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_user=? ORDER BY ID_topic DESC");
  379. st.setInt(1, ID_user);
  380. rezultati = st.executeQuery();
  381. while(rezultati.next()) {
  382. String tempname = rezultati.getString("topicname");
  383. result.add(tempname);
  384. }
  385. break;
  386. case "profilereply":
  387. ArrayList<Integer> topics = new ArrayList<>();
  388. PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC");
  389. st2.setInt(1, ID_user);
  390. ResultSet rezultati2 = st2.executeQuery();
  391. PreparedStatement st3 = con.prepareStatement("SELECT topicname FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  392. while(rezultati2.next()) {
  393. int id = rezultati2.getInt("ID_topic");
  394. st3.setInt(1, id);
  395. boolean verify = false;
  396. for(int i = 0; i < topics.size(); i++) {
  397. if(topics.get(i) == id) {
  398. verify = true;
  399. break;
  400. }
  401. }
  402. if(!verify) {
  403. topics.add(id);
  404. rezultati = st3.executeQuery();
  405. while(rezultati.next()) {
  406. String tempname = rezultati.getString("topicname");
  407. result.add(tempname);
  408. }
  409. }
  410. }
  411. break;
  412. case "normal":
  413. st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  414. st.setInt(1, ID_category);
  415. rezultati = st.executeQuery();
  416. while(rezultati.next()) {
  417. String tempname = rezultati.getString("topicname");
  418. result.add(tempname);
  419. }
  420. break;
  421. case "search":
  422. for(int i = 0; i < list.size(); i++) {
  423. st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  424. st.setInt(1, list.get(i));
  425. rezultati = st.executeQuery();
  426. while(rezultati.next()) {
  427. String tempname = rezultati.getString("topicname");
  428. result.add(tempname);
  429. }
  430. }
  431. break;
  432. default:
  433. st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  434. st.setInt(1, ID_category);
  435. rezultati = st.executeQuery();
  436. while(rezultati.next()) {
  437. String tempname = rezultati.getString("topicname");
  438. result.add(tempname);
  439. }
  440. break;
  441. }
  442. return result;
  443. }
  444. public ArrayList<String> gettopicauthors(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{
  445. ArrayList<String> result = new ArrayList<>();
  446. PreparedStatement st = null;
  447. ResultSet rezultati = null;
  448. switch(type) {
  449. case "profiletopics":
  450. st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_user=? ORDER BY ID_topic DESC");
  451. st.setInt(1, ID_user);
  452. rezultati = st.executeQuery();
  453. while(rezultati.next()) {
  454. int tempnameid = rezultati.getInt("ID_user");
  455. String tempname = getusernamefromid(tempnameid);
  456. result.add(tempname);
  457. }
  458. break;
  459. case "profilereply":
  460. ArrayList<Integer> topics = new ArrayList<>();
  461. PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC");
  462. st2.setInt(1, ID_user);
  463. ResultSet rezultati2 = st2.executeQuery();
  464. PreparedStatement st3 = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  465. while(rezultati2.next()) {
  466. int id = rezultati2.getInt("ID_topic");
  467. st3.setInt(1, id);
  468. boolean verify = false;
  469. for(int i = 0; i < topics.size(); i++) {
  470. if(topics.get(i) == id) {
  471. verify = true;
  472. break;
  473. }
  474. }
  475. if(!verify) {
  476. topics.add(id);
  477. rezultati = st3.executeQuery();
  478. while(rezultati.next()) {
  479. int tempnameid = rezultati.getInt("ID_user");
  480. String tempname = getusernamefromid(tempnameid);
  481. result.add(tempname);
  482. }
  483. }
  484. }
  485. break;
  486. case "normal":
  487. st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  488. st.setInt(1, ID_category);
  489. rezultati = st.executeQuery();
  490. while(rezultati.next()) {
  491. int tempnameid = rezultati.getInt("ID_user");
  492. String tempname = getusernamefromid(tempnameid);
  493. result.add(tempname);
  494. }
  495. break;
  496. case "search":
  497. for(int i = 0; i < list.size(); i++) {
  498. st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  499. st.setInt(1, list.get(i));
  500. rezultati = st.executeQuery();
  501. while(rezultati.next()) {
  502. int tempnameid = rezultati.getInt("ID_user");
  503. String tempname = getusernamefromid(tempnameid);
  504. result.add(tempname);
  505. }
  506. }
  507. break;
  508. default:
  509. st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  510. st.setInt(1, ID_category);
  511. rezultati = st.executeQuery();
  512. while(rezultati.next()) {
  513. int tempnameid = rezultati.getInt("ID_user");
  514. String tempname = getusernamefromid(tempnameid);
  515. result.add(tempname);
  516. }
  517. break;
  518. }
  519. return result;
  520. }
  521. public ArrayList<String> gettopicdates(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{
  522. ArrayList<String> result = new ArrayList<>();
  523. ResultSet rezultati = null;
  524. PreparedStatement st = null;
  525. switch(type) {
  526. case "profiletopics":
  527. st = con.prepareStatement("SELECT date FROM topic WHERE ID_user=? ORDER BY ID_topic DESC");
  528. st.setInt(1, ID_user);
  529. rezultati = st.executeQuery();
  530. while(rezultati.next()) {
  531. String tempname = rezultati.getString("date");
  532. result.add(tempname);
  533. }
  534. break;
  535. case "profilereply":
  536. ArrayList<Integer> topics = new ArrayList<>();
  537. PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC");
  538. st2.setInt(1, ID_user);
  539. ResultSet rezultati2 = st2.executeQuery();
  540. PreparedStatement st3 = con.prepareStatement("SELECT date FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  541. while(rezultati2.next()) {
  542. int id = rezultati2.getInt("ID_topic");
  543. st3.setInt(1, id);
  544. boolean verify = false;
  545. for(int i = 0; i < topics.size(); i++) {
  546. if(topics.get(i) == id) {
  547. verify = true;
  548. break;
  549. }
  550. }
  551. if(!verify) {
  552. topics.add(id);
  553. rezultati = st3.executeQuery();
  554. while(rezultati.next()) {
  555. String tempname = rezultati.getString("date");
  556. result.add(tempname);
  557. }
  558. }
  559. }
  560. break;
  561. case "normal":
  562. st = con.prepareStatement("SELECT date FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  563. st.setInt(1, ID_category);
  564. rezultati = st.executeQuery();
  565. while(rezultati.next()) {
  566. String tempname = rezultati.getString("date");
  567. result.add(tempname);
  568. }
  569. break;
  570. case "search":
  571. for(int i = 0; i < list.size(); i++) {
  572. st = con.prepareStatement("SELECT date FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  573. st.setInt(1, list.get(i));
  574. rezultati = st.executeQuery();
  575. while(rezultati.next()) {
  576. String tempname = rezultati.getString("date");
  577. result.add(tempname);
  578. }
  579. }
  580. break;
  581. default:
  582. st = con.prepareStatement("SELECT date FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  583. st.setInt(1, ID_category);
  584. rezultati = st.executeQuery();
  585. while(rezultati.next()) {
  586. String tempname = rezultati.getString("date");
  587. result.add(tempname);
  588. }
  589. break;
  590. }
  591.  
  592. return result;
  593. }
  594. public ArrayList<Integer> gettopicid(int ID_category, String type, int ID_user, ArrayList<Integer> list) throws SQLException{
  595. ArrayList<Integer> result = new ArrayList<>();
  596. PreparedStatement st = null;
  597. ResultSet rezultati = null;
  598. switch(type) {
  599. case "profiletopics":
  600. st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_user=? ORDER BY ID_topic DESC");
  601. st.setInt(1, ID_user);
  602. rezultati = st.executeQuery();
  603. while(rezultati.next()) {
  604. int tempid = rezultati.getInt("ID_topic");
  605. result.add(tempid);
  606. }
  607. break;
  608. case "profilereply":
  609. ArrayList<Integer> topics = new ArrayList<>();
  610. PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC");
  611. st2.setInt(1, ID_user);
  612. ResultSet rezultati2 = st2.executeQuery();
  613. PreparedStatement st3 = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  614. while(rezultati2.next()) {
  615. int id = rezultati2.getInt("ID_topic");
  616. st3.setInt(1, id);
  617. boolean verify = false;
  618. for(int i = 0; i < topics.size(); i++) {
  619. if(topics.get(i) == id) {
  620. verify = true;
  621. break;
  622. }
  623. }
  624. if(!verify) {
  625. topics.add(id);
  626. rezultati = st3.executeQuery();
  627. while(rezultati.next()) {
  628. int tempid = rezultati.getInt("ID_topic");
  629. result.add(tempid);
  630. }
  631. }
  632. }
  633. break;
  634. case "normal":
  635. st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  636. st.setInt(1, ID_category);
  637. rezultati = st.executeQuery();
  638. while(rezultati.next()) {
  639. int tempid = rezultati.getInt("ID_topic");
  640. result.add(tempid);
  641. }
  642. break;
  643. case "search":
  644. for(int i = 0; i < list.size(); i++) {
  645. st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  646. st.setInt(1, list.get(i));
  647. rezultati = st.executeQuery();
  648. while(rezultati.next()) {
  649. int tempid = rezultati.getInt("ID_topic");
  650. result.add(tempid);
  651. }
  652. }
  653. break;
  654. default:
  655. st = con.prepareStatement("SELECT ID_topic FROM topic WHERE ID_category=? ORDER BY ID_topic DESC");
  656. st.setInt(1, ID_category);
  657. rezultati = st.executeQuery();
  658. while(rezultati.next()) {
  659. int tempid = rezultati.getInt("ID_topic");
  660. result.add(tempid);
  661. }
  662. break;
  663. }
  664.  
  665. return result;
  666. }
  667. public ArrayList<String> getalltopiccategory(String type, int ID_user, ArrayList<Integer> list) throws SQLException{
  668. ArrayList<String> result = new ArrayList<>();
  669. ResultSet rezultati = null;
  670. PreparedStatement st = null;
  671. switch(type) {
  672. case "profiletopics":
  673. st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_user=? ORDER BY ID_topic DESC");
  674. st.setInt(1, ID_user);
  675. rezultati = st.executeQuery();
  676. while(rezultati.next()) {
  677. int catId = rezultati.getInt("ID_category");
  678. String tempname = getcategoryname(catId);
  679. result.add(tempname);
  680. }
  681. break;
  682. case "profilereply":
  683. ArrayList<Integer> topics = new ArrayList<>();
  684. PreparedStatement st2 = con.prepareStatement("SELECT ID_topic FROM reply WHERE ID_user=? ORDER BY ID_topic DESC");
  685. st2.setInt(1, ID_user);
  686. ResultSet rezultati2 = st2.executeQuery();
  687. PreparedStatement st3 = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  688. while(rezultati2.next()) {
  689. int id = rezultati2.getInt("ID_topic");
  690. st3.setInt(1, id);
  691. boolean verify = false;
  692. for(int i = 0; i < topics.size(); i++) {
  693. if(topics.get(i) == id) {
  694. verify = true;
  695. break;
  696. }
  697. }
  698. if(!verify) {
  699. topics.add(id);
  700. rezultati = st3.executeQuery();
  701. while(rezultati.next()) {
  702. int catId = rezultati.getInt("ID_category");
  703. String tempname = getcategoryname(catId);
  704. result.add(tempname);
  705. }
  706. }
  707. }
  708. break;
  709. case "search":
  710. for(int i = 0; i < list.size(); i++) {
  711. st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  712. st.setInt(1, list.get(i));
  713. rezultati = st.executeQuery();
  714. while(rezultati.next()) {
  715. int catId = rezultati.getInt("ID_category");
  716. String tempname = getcategoryname(catId);
  717. result.add(tempname);
  718. }
  719. }
  720. break;
  721. default:
  722. for(int i = 0; i < list.size(); i++) {
  723. st = con.prepareStatement("SELECT ID_category FROM topic WHERE ID_topic=? ORDER BY ID_topic DESC");
  724. st.setInt(1, list.get(i));
  725. rezultati = st.executeQuery();
  726. while(rezultati.next()) {
  727. int catId = rezultati.getInt("ID_category");
  728. String tempname = getcategoryname(catId);
  729. result.add(tempname);
  730. }
  731. }
  732. break;
  733. }
  734. return result;
  735. }
  736. public String gettopicname(int ID_topic) throws SQLException{
  737. PreparedStatement st = con.prepareStatement("SELECT topicname FROM topic WHERE ID_topic=?");
  738. st.setInt(1, ID_topic);
  739. ResultSet rezultati = st.executeQuery();
  740. String name = "";
  741. while(rezultati.next()) {
  742. name = rezultati.getString("topicname");
  743. }
  744. return name;
  745. }
  746. public String gettopicmessage(int ID_topic) throws SQLException{
  747. PreparedStatement st = con.prepareStatement("SELECT topiccontent FROM topic WHERE ID_topic=?");
  748. st.setInt(1, ID_topic);
  749. ResultSet rezultati = st.executeQuery();
  750. String msgcontent = "";
  751. while(rezultati.next()) {
  752. msgcontent = rezultati.getString("topiccontent");
  753. }
  754. return msgcontent;
  755. }
  756. public String gettopicauthor(int ID_topic) throws SQLException{
  757. PreparedStatement st = con.prepareStatement("SELECT ID_user FROM topic WHERE ID_topic=?");
  758. st.setInt(1, ID_topic);
  759. ResultSet rezultati = st.executeQuery();
  760. int id = 0;
  761. while(rezultati.next()) {
  762. id = rezultati.getInt("ID_user");
  763. }
  764. String author = getusernamefromid(id);
  765. return author;
  766. }
  767. public String gettopicdate(int ID_topic) throws SQLException{
  768. PreparedStatement st = con.prepareStatement("SELECT date FROM topic WHERE ID_topic=?");
  769. st.setInt(1, ID_topic);
  770. ResultSet rezultati = st.executeQuery();
  771. String date = "";
  772. while(rezultati.next()) {
  773. date = rezultati.getString("date");
  774. }
  775. return date;
  776. }
  777. public int gettopicpriority(int ID_topic) throws SQLException{
  778. PreparedStatement st = con.prepareStatement("SELECT priority FROM topic WHERE ID_topic=?");
  779. st.setInt(1, ID_topic);
  780. ResultSet rezultati = st.executeQuery();
  781. int priority = 0;
  782. while(rezultati.next()) {
  783. priority = rezultati.getInt("priority");
  784. }
  785. return priority;
  786. }
  787. public int gettopicidbynameandcontent(String topicname, String date) throws SQLException{
  788. PreparedStatement st = con.prepareStatement("SELECT ID_topic FROM topic WHERE topicname=? AND date=?");
  789. st.setString(1, topicname);
  790. st.setString(2, date);
  791. int id = 0;
  792. ResultSet rezultati = st.executeQuery();
  793. while(rezultati.next()) {
  794. id = rezultati.getInt("ID_topic");
  795. }
  796. return id;
  797. }
  798. public boolean changetopicpriority(int ID_topic, int priority) throws SQLException {
  799. PreparedStatement st = con.prepareStatement("UPDATE topic SET priority=? WHERE ID_topic=?");
  800. st.setInt(1, priority);
  801. st.setInt(2, ID_topic);
  802. int rowsEffected = st.executeUpdate();
  803. if(rowsEffected != 0)
  804. return true;
  805. else
  806. return false;
  807. }
  808. public boolean edittopiccontent(int ID_topic, String newcontent) throws SQLException{
  809. PreparedStatement st = con.prepareStatement("UPDATE topic SET topiccontent=? WHERE ID_topic=?");
  810. st.setString(1, newcontent);
  811. st.setInt(2, ID_topic);
  812. int rowsEffected = st.executeUpdate();
  813. if(rowsEffected != 0)
  814. return true;
  815. else
  816. return false;
  817. }
  818. //Reply functions
  819. public boolean addreply(int ID_topic, int ID_user, String date, String replycontent) throws SQLException {
  820. PreparedStatement st = con.prepareStatement("INSERT INTO reply(ID_topic, ID_user, date, replycontent) VALUES(?, ?, ?, ?)");
  821. st.setInt(1, ID_topic);
  822. st.setInt(2, ID_user);
  823. st.setString(3, date);
  824. st.setString(4, replycontent);
  825. int rowsEffected = st.executeUpdate();
  826. if(rowsEffected != 0)
  827. return true;
  828. else
  829. return false;
  830. }e
  831. public boolean deletereply(int ID_reply) throws SQLException{
  832. PreparedStatement st = con.prepareStatement("DELETE FROM reply WHERE ID_reply=?");
  833. st.setInt(1, ID_reply);
  834. int rowsEffected = st.executeUpdate();
  835. if(rowsEffected != 0)
  836. return true;
  837. else
  838. return false;
  839. }
  840. public boolean editreplycontent(int ID_reply, String newcontent) throws SQLException{
  841. PreparedStatement st = con.prepareStatement("UPDATE reply SET replycontent=? WHERE ID_reply=?");
  842. st.setString(1, newcontent);
  843. st.setInt(2, ID_reply);
  844. int rowsEffected = st.executeUpdate();
  845. if(rowsEffected != 0)
  846. return true;
  847. else
  848. return false;
  849. }
  850. public ArrayList<Integer> getallreplyid(int ID_topic) throws SQLException{
  851. ArrayList<Integer> ids = new ArrayList<>();
  852. PreparedStatement st = con.prepareStatement("SELECT ID_reply FROM reply WHERE ID_topic=?");
  853. st.setInt(1, ID_topic);
  854. ResultSet rezultati = st.executeQuery();
  855. while(rezultati.next()) {
  856. ids.add(rezultati.getInt("ID_reply"));
  857. }
  858. return ids;
  859. }
  860. public String getreplyauthor(int ID_reply) throws SQLException{
  861. PreparedStatement st = con.prepareStatement("SELECT ID_user FROM reply WHERE ID_reply=?");
  862. st.setInt(1, ID_reply);
  863. ResultSet rezultati = st.executeQuery();
  864. int id = 0;
  865. while(rezultati.next()) {
  866. id = rezultati.getInt("ID_user");
  867. }
  868. String username = "";
  869. username = getusernamefromid(id);
  870. return username;
  871. }
  872. public String getreplycontent(int ID_reply) throws SQLException{
  873. PreparedStatement st = con.prepareStatement("SELECT replycontent FROM reply WHERE ID_reply=?");
  874. st.setInt(1, ID_reply);
  875. ResultSet rezultati = st.executeQuery();
  876. String content = "";
  877. while(rezultati.next()) {
  878. content = rezultati.getString("replycontent");
  879. }
  880. return content;
  881. }
  882. public String getreplydate(int ID_reply) throws SQLException{
  883. PreparedStatement st = con.prepareStatement("SELECT date FROM reply WHERE ID_reply=?");
  884. st.setInt(1, ID_reply);
  885. ResultSet rezultati = st.executeQuery();
  886. String date = "";
  887. while(rezultati.next()) {
  888. date = rezultati.getString("date");
  889. }
  890. return date;
  891. }
  892. //Search function
  893. public ArrayList<Integer> searchtopics(String searchstring, String userlevel) throws SQLException{
  894. ArrayList<Integer> validtopicids = new ArrayList<>();
  895. ArrayList<Integer> categoryids = new ArrayList<>();
  896. ArrayList<String> categorynames = new ArrayList<>();
  897. int possiblenameid = getuserid(searchstring);
  898. categorynames = getallcategorynameforuserlevel(userlevel);
  899. for(int i = 0; i < categorynames.size(); i++) {
  900. categoryids.add(getcategoryid(categorynames.get(i)));
  901. }
  902. for(int i = 0; i < categoryids.size(); i++) {
  903. PreparedStatement st = con.prepareStatement("SELECT DISTINCT ID_topic FROM topic WHERE ID_category=? AND (topicname LIKE ? OR topiccontent LIKE ? OR ID_user=?) ORDER BY ID_topic DESC;");
  904. st.setInt(1, categoryids.get(i));
  905. st.setString(2, "%" + searchstring + "%");
  906. st.setString(3, "%" + searchstring + "%");
  907. st.setInt(4, possiblenameid);
  908. ResultSet rezultati = st.executeQuery();
  909. while(rezultati.next()) {
  910. boolean verify = false;
  911. int id = rezultati.getInt("ID_topic");
  912. for(int j = 0; j < validtopicids.size(); j++) {
  913. if(id == validtopicids.get(j)) {
  914. verify = true;
  915. break;
  916. }
  917. }
  918. if(!verify) {
  919. validtopicids.add(id);
  920. }
  921. }
  922. }
  923. return validtopicids;
  924. }
  925.  
  926. }
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!