1 /* |
|
2 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. |
|
3 * |
|
4 * Copyright 2018 Mike Becker. All rights reserved. |
|
5 * |
|
6 * Redistribution and use in source and binary forms, with or without |
|
7 * modification, are permitted provided that the following conditions are met: |
|
8 * |
|
9 * 1. Redistributions of source code must retain the above copyright |
|
10 * notice, this list of conditions and the following disclaimer. |
|
11 * |
|
12 * 2. Redistributions in binary form must reproduce the above copyright |
|
13 * notice, this list of conditions and the following disclaimer in the |
|
14 * documentation and/or other materials provided with the distribution. |
|
15 * |
|
16 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
|
17 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
|
18 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE |
|
19 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE |
|
20 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR |
|
21 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF |
|
22 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS |
|
23 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN |
|
24 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) |
|
25 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
|
26 * POSSIBILITY OF SUCH DAMAGE. |
|
27 * |
|
28 */ |
|
29 package de.uapcore.lightpit.dao.postgres; |
|
30 |
|
31 import de.uapcore.lightpit.dao.UserDao; |
|
32 import de.uapcore.lightpit.entities.User; |
|
33 |
|
34 import java.sql.Connection; |
|
35 import java.sql.PreparedStatement; |
|
36 import java.sql.ResultSet; |
|
37 import java.sql.SQLException; |
|
38 import java.util.ArrayList; |
|
39 import java.util.List; |
|
40 import java.util.Objects; |
|
41 import java.util.Optional; |
|
42 |
|
43 import static de.uapcore.lightpit.dao.Functions.getSafeString; |
|
44 import static de.uapcore.lightpit.dao.Functions.setStringOrNull; |
|
45 |
|
46 public final class PGUserDao implements UserDao { |
|
47 |
|
48 private final PreparedStatement insert, update, list, find, findByUsername; |
|
49 |
|
50 public PGUserDao(Connection connection) throws SQLException { |
|
51 list = connection.prepareStatement( |
|
52 "select userid, username, lastname, givenname, mail " + |
|
53 "from lpit_user where userid >= 0 " + |
|
54 "order by username"); |
|
55 find = connection.prepareStatement( |
|
56 "select userid, username, lastname, givenname, mail " + |
|
57 "from lpit_user where userid = ? "); |
|
58 |
|
59 findByUsername = connection.prepareStatement( |
|
60 "select userid, username, lastname, givenname, mail " + |
|
61 "from lpit_user where lower(username) = lower(?) "); |
|
62 |
|
63 insert = connection.prepareStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)"); |
|
64 update = connection.prepareStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?"); |
|
65 } |
|
66 |
|
67 static User mapColumns(ResultSet result) throws SQLException { |
|
68 final int id = result.getInt("userid"); |
|
69 if (id == 0) return null; |
|
70 final var user = new User(id); |
|
71 user.setUsername(result.getString("username")); |
|
72 user.setGivenname(getSafeString(result, "givenname")); |
|
73 user.setLastname(getSafeString(result, "lastname")); |
|
74 user.setMail(getSafeString(result, "mail")); |
|
75 return user; |
|
76 } |
|
77 |
|
78 @Override |
|
79 public void save(User instance) throws SQLException { |
|
80 Objects.requireNonNull(instance.getUsername()); |
|
81 insert.setString(1, instance.getUsername()); |
|
82 setStringOrNull(insert, 2, instance.getLastname()); |
|
83 setStringOrNull(insert, 3, instance.getGivenname()); |
|
84 setStringOrNull(insert, 4, instance.getMail()); |
|
85 insert.executeUpdate(); |
|
86 } |
|
87 |
|
88 @Override |
|
89 public boolean update(User instance) throws SQLException { |
|
90 if (instance.getId() < 0) return false; |
|
91 setStringOrNull(update, 1, instance.getLastname()); |
|
92 setStringOrNull(update, 2, instance.getGivenname()); |
|
93 setStringOrNull(update, 3, instance.getMail()); |
|
94 update.setInt(4, instance.getId()); |
|
95 return update.executeUpdate() > 0; |
|
96 } |
|
97 |
|
98 @Override |
|
99 public List<User> list() throws SQLException { |
|
100 List<User> users = new ArrayList<>(); |
|
101 try (var result = list.executeQuery()) { |
|
102 while (result.next()) { |
|
103 users.add(mapColumns(result)); |
|
104 } |
|
105 } |
|
106 return users; |
|
107 } |
|
108 |
|
109 @Override |
|
110 public User find(int id) throws SQLException { |
|
111 find.setInt(1, id); |
|
112 try (var result = find.executeQuery()) { |
|
113 if (result.next()) { |
|
114 return mapColumns(result); |
|
115 } else { |
|
116 return null; |
|
117 } |
|
118 } |
|
119 } |
|
120 |
|
121 @Override |
|
122 public Optional<User> findByUsername(String username) throws SQLException { |
|
123 findByUsername.setString(1, username); |
|
124 try (var result = findByUsername.executeQuery()) { |
|
125 if (result.next()) { |
|
126 return Optional.of(mapColumns(result)); |
|
127 } else { |
|
128 return Optional.empty(); |
|
129 } |
|
130 } |
|
131 } |
|
132 } |
|