API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 19th, 2016
149
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.44 KB | None | 0 0
raw download clone embed print report
  1. package database;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.Date;
  5. import java.sql.DriverManager;
  6. import java.sql.PreparedStatement;
  7. import java.sql.ResultSet;
  8. import java.sql.SQLException;
  9. import java.time.LocalDate;
  10. import java.util.ArrayList;
  11. import java.util.List;
  12.  
  13. import domain.Konto;
  14.  
  15. public class KontoSøgningDB {
  16.  
  17. public List<Konto> søgKonto(Konto konto) {
  18. List<Konto> list = new ArrayList<>();
  19. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  20. PreparedStatement statement = connection.prepareStatement(
  21. "SELECT kontonummer,kundeid,kontonavn, slutdato from kontooplysninger where kontonummer like ? and kundeid like ? and upper(kontonavn) like ?");) {
  22.  
  23. statement.setString(1, "%" + konto.getKontonummer() + "%");
  24. statement.setString(2, "%" + konto.getKundeid() + "%");
  25. statement.setString(3, "%" + konto.getKontoNavn().toUpperCase() + "%");
  26.  
  27. ResultSet rs = statement.executeQuery();
  28.  
  29. while (rs.next()) {
  30. Konto k = new Konto();
  31. k.setKontonummer(rs.getString("kontonummer"));
  32. k.setKundeid(rs.getString("kundeid"));
  33. k.setKontoNavn(rs.getString("kontonavn"));
  34. k.setSlutdato(rs.getDate("slutdato"));
  35. if (Date.valueOf(LocalDate.now()).before(k.getSlutdato())) {
  36. list.add(k);
  37. }
  38. }
  39. } catch (SQLException e) {
  40. e.printStackTrace();
  41. }
  42. return list;
  43. }
  44. }
  45.  
  46.  
  47.  
  48.  
  49.  
  50.  
  51.  
  52.  
  53.  
  54.  
  55. package database;
  56.  
  57. import java.sql.Connection;
  58. import java.sql.DriverManager;
  59. import java.sql.PreparedStatement;
  60. import java.sql.ResultSet;
  61. import java.sql.SQLException;
  62. import java.util.ArrayList;
  63. import java.util.List;
  64.  
  65. import domain.Kunde;
  66.  
  67. public class KundeSøgningDB {
  68.  
  69. public List<Kunde> søgKunde(Kunde kunde) {
  70. List<Kunde> list = new ArrayList<>();
  71. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  72. PreparedStatement statement = connection.prepareStatement("SELECT navn,adresse,telefonnummer, cpr_cvr, kundeid from kundeoplysninger where upper(navn) like ? and upper(adresse) like ? and upper(telefonnummer) like ? and upper(cpr_cvr) like ? and kundeid like ?");) {
  73.  
  74. statement.setString(1, "%"+kunde.getNavn().toUpperCase()+"%");
  75. statement.setString(2, "%"+kunde.getAdresse().toUpperCase()+"%");
  76. statement.setString(3, "%"+kunde.getTelefon().toUpperCase()+"%");
  77. statement.setString(4, "%"+kunde.getCpr_cvr().toUpperCase()+"%");
  78. statement.setString(5, "%"+kunde.getKundeid()+"%");
  79.  
  80. ResultSet rs = statement.executeQuery();
  81.  
  82. while (rs.next()) {
  83. Kunde k = new Kunde();
  84. k.setNavn(rs.getString("navn"));
  85. k.setAdresse(rs.getString("adresse"));
  86. k.setTelefon(rs.getString("telefonnummer"));
  87. k.setCpr_cvr(rs.getString("cpr_cvr"));
  88. k.setKundeid(rs.getString("kundeid"));
  89. list.add(k);
  90.  
  91. }
  92.  
  93. } catch (SQLException e) {
  94. e.printStackTrace();
  95. }
  96. return list;
  97. }
  98. }
  99.  
  100.  
  101.  
  102.  
  103.  
  104.  
  105.  
  106.  
  107.  
  108.  
  109.  
  110.  
  111.  
  112.  
  113. package database;
  114.  
  115. import java.sql.Connection;
  116. import java.sql.DriverManager;
  117. import java.sql.PreparedStatement;
  118. import java.sql.SQLException;
  119.  
  120. import domain.Kunde;
  121.  
  122. public class KundeVedligeholdDB {
  123. public void opretKunde(Kunde kunde) {
  124. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  125. PreparedStatement statement = connection.prepareStatement(
  126. "INSERT INTO KUNDEOPLYSNINGER (NAVN, ADRESSE, FØDSELSDAG, POSTNUMMER, TELEFONNUMMER, EMAIL, CPR_CVR ) VALUES ( ?, ?, ?, ?, ?, ?, ?)");) {
  127. try {
  128. connection.setAutoCommit(false);
  129. statement.setString(1, kunde.getNavn());
  130. statement.setString(2, kunde.getAdresse());
  131. statement.setString(3, kunde.getFødselsdag());
  132. statement.setString(4, kunde.getPostnummer());
  133. statement.setString(5, kunde.getTelefon());
  134. statement.setString(6, kunde.getEmail());
  135. statement.setString(7, kunde.getCpr_cvr());
  136. int antal = statement.executeUpdate();
  137. System.out.println("Antal rækker berørt : " + antal);
  138. connection.commit();
  139. } catch (SQLException e) {
  140. System.out.println("Fejl ved oprettelse");
  141. connection.rollback();
  142. }
  143. } catch (SQLException e) {
  144. e.printStackTrace();
  145. }
  146.  
  147. }
  148. public void redigerKunde(Kunde kunde) {
  149. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  150. PreparedStatement statement = connection.prepareStatement("update kundeoplysninger set navn = ?, adresse = ?, telefonnummer = ?, cpr_cvr = ? where kundeid = ?");) {
  151. try {
  152. connection.setAutoCommit(false);
  153. statement.setString(1, kunde.getNavn());
  154. statement.setString(2, kunde.getAdresse());
  155. statement.setString(3, kunde.getTelefon());
  156. statement.setString(4, kunde.getCpr_cvr());
  157. statement.setString(5, kunde.getKundeid());
  158. int antal = statement.executeUpdate();
  159. System.out.println("Antal rækker berørt : " + antal);
  160. connection.commit();
  161. } catch (SQLException e) {
  162. System.out.println("Fejl ved redigering");
  163. connection.rollback();
  164. }
  165. } catch (SQLException e) {
  166. e.printStackTrace();
  167. }
  168.  
  169. }
  170. }
  171.  
  172.  
  173.  
  174.  
  175.  
  176.  
  177.  
  178.  
  179.  
  180.  
  181.  
  182.  
  183.  
  184.  
  185.  
  186.  
  187.  
  188.  
  189. package database;
  190.  
  191. import java.sql.Connection;
  192. import java.sql.Date;
  193. import java.sql.DriverManager;
  194. import java.sql.PreparedStatement;
  195. import java.sql.ResultSet;
  196. import java.sql.SQLException;
  197. import java.time.LocalDate;
  198.  
  199. import domain.Overførsel;
  200.  
  201. public class OverførselerDB {
  202.  
  203. public Overførsel findKontoer(Overførsel overførsel) {
  204. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  205. PreparedStatement statement = connection
  206. .prepareStatement("SELECT saldo, kontonummer from kontooplysninger where kontonummer = ?");) {
  207.  
  208. statement.setString(1, overførsel.getFraKonto());
  209. ResultSet rs = statement.executeQuery();
  210. if (rs.next()) {
  211.  
  212. overførsel.setFraKontoSaldo(rs.getBigDecimal("saldo"));
  213. }
  214.  
  215. statement.setString(1, overførsel.getTilKonto());
  216. rs = statement.executeQuery();
  217. if (rs.next()) {
  218. overførsel.setTilKontoSaldo(rs.getBigDecimal("saldo"));
  219. }
  220.  
  221. } catch (SQLException e) {
  222. e.printStackTrace();
  223. }
  224. return overførsel;
  225. }
  226.  
  227. public void overfør(Overførsel overførsel) {
  228. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  229. PreparedStatement statement1 = connection.prepareStatement(
  230. "INSERT INTO POSTERINGER (KONTONUMMER, BELOB, POSTERINGSTEKST, POSTERINGSDATO ) VALUES ( ?, ?, ?, ?);");
  231. PreparedStatement statement2 = connection.prepareStatement(
  232. "INSERT INTO POSTERINGER (KONTONUMMER, BELOB, POSTERINGSTEKST, POSTERINGSDATO ) VALUES ( ?, ?, ?, ?);");
  233. PreparedStatement statement3 = connection
  234. .prepareStatement("update kontooplysninger SET saldo=? WHERE kontonummer = ?;");
  235. PreparedStatement statement4 = connection
  236. .prepareStatement("update kontooplysninger SET saldo=? WHERE kontonummer = ?;");
  237.  
  238. ) {
  239. try {
  240. connection.setAutoCommit(false);
  241. statement1.setString(1, overførsel.getFraKonto());
  242. statement1.setString(2, "-" + overførsel.getBeløb().toString());
  243. statement1.setString(3, overførsel.getFraPostering());
  244. statement1.setDate(4, Date.valueOf(LocalDate.now()));
  245. statement1.executeUpdate();
  246.  
  247. statement2.setString(1, overførsel.getTilKonto());
  248. statement2.setString(2, "+" + overførsel.getBeløb().toString());
  249. statement2.setString(3, overførsel.getTilPostering());
  250. statement2.setDate(4, Date.valueOf(LocalDate.now()));
  251. statement2.executeUpdate();
  252.  
  253. statement3.setBigDecimal(1, overførsel.getFraKontoSaldo());
  254. statement3.setString(2, overførsel.getFraKonto());
  255. statement3.executeUpdate();
  256.  
  257. statement4.setBigDecimal(1, overførsel.getTilKontoSaldo());
  258. statement4.setString(2, overførsel.getTilKonto());
  259. statement4.executeUpdate();
  260.  
  261. connection.commit();
  262. } catch (SQLException e) {
  263. System.out.println("Fejl ved overførsel");
  264. connection.rollback();
  265. }
  266. } catch (
  267.  
  268. SQLException e) {
  269. e.printStackTrace();
  270. System.out.println("Fejl - det er helt galt");
  271. }
  272. }
  273.  
  274. public void indsætning(Overførsel indsæt) {
  275. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  276. PreparedStatement statement1 = connection.prepareStatement(
  277. "INSERT INTO POSTERINGER (KONTONUMMER, BELOB, POSTERINGSTEKST, POSTERINGSDATO ) VALUES ( ?, ?, ?, ?);");
  278. PreparedStatement statement2 = connection
  279. .prepareStatement("update kontooplysninger SET saldo=? WHERE kontonummer = ?;");) {
  280. try {
  281. connection.setAutoCommit(false);
  282. statement1.setString(1, indsæt.getTilKonto());
  283. statement1.setString(2, indsæt.getBeløb().toString());
  284. statement1.setString(3, indsæt.getTilPostering());
  285. statement1.setDate(4, Date.valueOf(LocalDate.now()));
  286. statement1.executeUpdate();
  287.  
  288. statement2.setBigDecimal(1, indsæt.getTilKontoSaldo());
  289. statement2.setString(2, indsæt.getTilKonto());
  290. statement2.executeUpdate();
  291. connection.commit();
  292.  
  293. } catch (SQLException e) {
  294. System.out.println("Fejl ved indsætning");
  295. connection.rollback();
  296. }
  297. } catch (
  298.  
  299. SQLException e) {
  300. e.printStackTrace();
  301. System.out.println("Fejl - det er helt galt");
  302. }
  303. }
  304. }
  305.  
  306.  
  307.  
  308.  
  309.  
  310.  
  311.  
  312.  
  313.  
  314.  
  315.  
  316.  
  317.  
  318.  
  319.  
  320.  
  321.  
  322. package database;
  323.  
  324. import java.sql.Connection;
  325. import java.sql.DriverManager;
  326. import java.sql.PreparedStatement;
  327. import java.sql.ResultSet;
  328. import java.sql.SQLException;
  329. import java.util.ArrayList;
  330. import java.util.List;
  331.  
  332. import domain.Posteringer;
  333.  
  334. public class PosteringerKontoDB {
  335.  
  336. public List<Posteringer> findPosteringer(Posteringer posteringer) {
  337. List<Posteringer> list = new ArrayList<>();
  338. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  339. PreparedStatement statement = connection
  340. .prepareStatement("SELECT kontonummer, belob, posteringstekst, posteringsdato from POSTERINGER where kontonummer = ?");) {
  341.  
  342. statement.setString(1, posteringer.getKontoNummer());
  343.  
  344. ResultSet rs = statement.executeQuery();
  345.  
  346. while (rs.next()) {
  347. Posteringer p = new Posteringer();
  348. p.setKontoNummer(rs.getString("kontonummer"));
  349. p.setBelob(rs.getString("belob"));
  350. p.setPosteringstekst(rs.getString("posteringstekst"));
  351. p.setPosteringsdato(rs.getString("posteringsdato"));
  352. list.add(p);
  353. }
  354.  
  355. } catch (SQLException e) {
  356. e.printStackTrace();
  357. }
  358. return list;
  359. }
  360.  
  361. }
  362.  
  363.  
  364.  
  365.  
  366.  
  367.  
  368.  
  369.  
  370.  
  371.  
  372.  
  373.  
  374.  
  375.  
  376.  
  377.  
  378.  
  379.  
  380. package database;
  381.  
  382. import java.sql.Connection;
  383. import java.sql.Date;
  384. import java.sql.DriverManager;
  385. import java.sql.PreparedStatement;
  386. import java.sql.ResultSet;
  387. import java.sql.SQLException;
  388. import java.time.LocalDate;
  389. import java.util.ArrayList;
  390. import java.util.List;
  391.  
  392. import domain.Konto;
  393.  
  394. public class SpecifikKundeDB {
  395.  
  396. public List<Konto> kontoOversigt(Konto konto) {
  397. List<Konto> list = new ArrayList<>();
  398. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  399. PreparedStatement statement = connection
  400. .prepareStatement("select navn, kontonavn, kundeid, kontonummer, saldo, slutdato from kundeoplysninger "
  401. + "join kontooplysninger " + "on kundeoplysninger.kundeid = kontooplysninger.kundeid "
  402. + "where kundeid = ?");) {
  403.  
  404. statement.setString(1, konto.getKundeid());
  405. ResultSet rs = statement.executeQuery();
  406.  
  407. while (rs.next()) {
  408. Konto kontoSlut = new Konto();
  409. kontoSlut.setKontonummer(rs.getString("kontonummer"));
  410. kontoSlut.setSaldo(rs.getString("saldo"));
  411. kontoSlut.setKontoNavn(rs.getString("kontonavn"));
  412. kontoSlut.setSlutdato(rs.getDate("slutdato"));
  413. if (Date.valueOf(LocalDate.now()).before(kontoSlut.getSlutdato())) {
  414. list.add(kontoSlut);
  415. }
  416. }
  417.  
  418. } catch (SQLException e) {
  419. e.printStackTrace();
  420. }
  421. return list;
  422. }
  423.  
  424. public void opretKonto(Konto konto) {
  425. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  426. PreparedStatement statement = connection.prepareStatement(
  427. "INSERT INTO KONTOOPLYSNINGER ( KONTONUMMER, SALDO, KUNDEID, STARTDATO, SLUTDATO, KONTONAVN ) "
  428. + "VALUES (? ,? ,? ,?, ?, ?)");) {
  429. try {
  430. connection.setAutoCommit(false);
  431. statement.setString(1, konto.getKontonummer());
  432. statement.setString(2, konto.getSaldo());
  433. statement.setString(3, konto.getKundeid());
  434. statement.setDate(4, Date.valueOf(LocalDate.now()));
  435. statement.setDate(5, Date.valueOf(LocalDate.of(9999, 12, 31)));
  436. statement.setString(6, konto.getKontoNavn());
  437. int antal = statement.executeUpdate();
  438.  
  439. System.out.println("Antal rækker berørt : " + antal);
  440. connection.commit();
  441. } catch (SQLException e) {
  442. System.out.println("Fejl ved opret konto");
  443. connection.rollback();
  444. }
  445. } catch (SQLException e) {
  446. e.printStackTrace();
  447. }
  448. }
  449.  
  450. public void sletKonto(Konto konto) {
  451. try (Connection connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mydb", "SA", "");
  452. PreparedStatement statement = connection
  453. .prepareStatement("update kontooplysninger SET slutdato = ? WHERE kontonummer = ?");) {
  454. try {
  455. connection.setAutoCommit(false);
  456. statement.setDate(1, Date.valueOf(LocalDate.now()));
  457. statement.setString(2, konto.getKontonummer());
  458. int antal = statement.executeUpdate();
  459.  
  460. System.out.println("Antal rækker berørt : " + antal);
  461. connection.commit();
  462. } catch (SQLException e) {
  463. System.out.println("Fejl i slet konto");
  464. connection.rollback();
  465. }
  466. } catch (SQLException e) {
  467. e.printStackTrace();
  468. }
  469. }
  470. }
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!