Fri, 23 Oct 2020 18:40:50 +0200
migrate WebColor
/* * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER. * * Copyright 2018 Mike Becker. All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions are met: * * 1. Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * * 2. Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE * POSSIBILITY OF SUCH DAMAGE. * */ package de.uapcore.lightpit.dao.postgres; import de.uapcore.lightpit.dao.UserDao; import de.uapcore.lightpit.entities.User; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Objects; import java.util.Optional; import static de.uapcore.lightpit.dao.Functions.setStringOrNull; public final class PGUserDao implements UserDao { private final PreparedStatement insert, update, list, find, findByUsername; public PGUserDao(Connection connection) throws SQLException { list = connection.prepareStatement( "select userid, username, lastname, givenname, mail " + "from lpit_user where userid >= 0 " + "order by username"); find = connection.prepareStatement( "select userid, username, lastname, givenname, mail " + "from lpit_user where userid = ? "); findByUsername = connection.prepareStatement( "select userid, username, lastname, givenname, mail " + "from lpit_user where lower(username) = lower(?) "); insert = connection.prepareStatement("insert into lpit_user (username, lastname, givenname, mail) values (?, ?, ?, ?)"); update = connection.prepareStatement("update lpit_user set lastname = ?, givenname = ?, mail = ? where userid = ?"); } static User mapColumns(ResultSet result) throws SQLException { final int id = result.getInt("userid"); if (id == 0) return null; final var user = new User(id); user.setUsername(result.getString("username")); user.setGivenname(result.getString("givenname")); user.setLastname(result.getString("lastname")); user.setMail(result.getString("mail")); return user; } @Override public void save(User instance) throws SQLException { Objects.requireNonNull(instance.getUsername()); insert.setString(1, instance.getUsername()); setStringOrNull(insert, 2, instance.getLastname()); setStringOrNull(insert, 3, instance.getGivenname()); setStringOrNull(insert, 4, instance.getMail()); insert.executeUpdate(); } @Override public boolean update(User instance) throws SQLException { if (instance.getId() < 0) return false; setStringOrNull(update, 1, instance.getLastname()); setStringOrNull(update, 2, instance.getGivenname()); setStringOrNull(update, 3, instance.getMail()); update.setInt(4, instance.getId()); return update.executeUpdate() > 0; } @Override public List<User> list() throws SQLException { List<User> users = new ArrayList<>(); try (var result = list.executeQuery()) { while (result.next()) { users.add(mapColumns(result)); } } return users; } @Override public User find(int id) throws SQLException { find.setInt(1, id); try (var result = find.executeQuery()) { if (result.next()) { return mapColumns(result); } else { return null; } } } @Override public Optional<User> findByUsername(String username) throws SQLException { findByUsername.setString(1, username); try (var result = findByUsername.executeQuery()) { if (result.next()) { return Optional.of(mapColumns(result)); } else { return Optional.empty(); } } } }