universe@21: /*
universe@21:  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
universe@21:  * 
universe@24:  * Copyright 2018 Mike Becker. All rights reserved.
universe@21:  * 
universe@21:  * Redistribution and use in source and binary forms, with or without
universe@21:  * modification, are permitted provided that the following conditions are met:
universe@21:  *
universe@21:  *   1. Redistributions of source code must retain the above copyright
universe@21:  *      notice, this list of conditions and the following disclaimer.
universe@21:  *
universe@21:  *   2. Redistributions in binary form must reproduce the above copyright
universe@21:  *      notice, this list of conditions and the following disclaimer in the
universe@21:  *      documentation and/or other materials provided with the distribution.
universe@21:  *
universe@21:  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
universe@21:  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
universe@21:  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
universe@21:  * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
universe@21:  * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
universe@21:  * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
universe@21:  * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
universe@21:  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
universe@21:  * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
universe@21:  * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
universe@21:  * POSSIBILITY OF SUCH DAMAGE.
universe@30:  *
universe@21:  */
universe@30: package de.uapcore.lightpit.dao;
universe@30: 
universe@30: import de.uapcore.lightpit.entities.User;
universe@21: 
universe@21: import java.sql.Connection;
universe@21: import java.sql.ResultSet;
universe@21: import java.sql.SQLException;
universe@21: import java.sql.Statement;
universe@21: import java.util.ArrayList;
universe@21: import java.util.List;
universe@26: import java.util.Optional;
universe@21: 
universe@30: public class UserDao {
universe@30: 
universe@21:     /**
universe@26:      * Maps SQL columns to POJO fields.
universe@30:      *
universe@26:      * @param result the database result set
universe@30:      * @param user   the POJO
universe@30:      * @throws SQLException
universe@21:      */
universe@26:     protected void mapColumns(ResultSet result, User user) throws SQLException {
universe@26:         user.setUserID(result.getInt("userid"));
universe@26:         user.setUsername(result.getString("username"));
universe@26:         user.setGivenname(Optional.ofNullable(result.getString("givenname")));
universe@26:         user.setLastname(Optional.ofNullable(result.getString("lastname"))); 
universe@21:     }
universe@21: 
universe@21:     /**
universe@26:      * Returns a list of all users ordered by their username.
universe@21:      * 
universe@26:      * Does not return reserved system users with negative user IDs.
universe@21:      * 
universe@21:      * @param conn the connection to use
universe@26:      * @return a list of all users
universe@21:      * @throws SQLException 
universe@21:      */
universe@26:     public List<User> listAll(Connection conn) throws SQLException {
universe@26:         List<User> list = new ArrayList<>();
universe@21:         try (
universe@21:                 Statement stmt = conn.createStatement();
universe@26:                 ResultSet result = stmt.executeQuery(
universe@26:                         "SELECT * FROM lpitcore_user WHERE userid >= 0 ORDER BY username")) {
universe@21:             while (result.next()) {
universe@26:                 final User user = new User();
universe@26:                 mapColumns(result, user);
universe@26:                 list.add(user);
universe@21:             }
universe@21:         }
universe@21:         return list;
universe@21:     }
universe@21: }