/**
* Copyright 2013 Sean Kavanagh - sean.p.kavanagh6@gmail.com
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.keybox.manage.db;
import com.keybox.manage.model.SortedSet;
import com.keybox.manage.model.User;
import com.keybox.manage.util.DBUtils;
import com.keybox.manage.util.EncryptionUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
/**
* DAO class to manage users
*/
public class UserDB {
public static final String SORT_BY_FIRST_NM="first_nm";
public static final String SORT_BY_LAST_NM="last_nm";
public static final String SORT_BY_EMAIL="email";
public static final String SORT_BY_USERNAME="username";
public static final String SORT_BY_USER_TYPE="user_type";
/**
* returns users based on sort order defined
* @param sortedSet object that defines sort order
* @return sorted user list
*/
public static SortedSet getUserSet(SortedSet sortedSet) {
ArrayList<User> userList = new ArrayList<User>();
String orderBy = "";
if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
}
String sql = "select * from users where enabled=true " + orderBy;
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getLong("id"));
user.setFirstNm(rs.getString("first_nm"));
user.setLastNm(rs.getString("last_nm"));
user.setEmail(rs.getString("email"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setUserType(rs.getString("user_type"));
userList.add(user);
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
DBUtils.closeConn(con);
sortedSet.setItemList(userList);
return sortedSet;
}
/**
* returns user base on id
* @param userId user id
* @return user object
*/
public static User getUser(Long userId) {
User user = null;
Connection con = null;
try {
con = DBUtils.getConn();
user = getUser(con, userId);
} catch (Exception e) {
e.printStackTrace();
}
DBUtils.closeConn(con);
return user;
}
/**
* returns user base on id
* @param con DB connection
* @param userId user id
* @return user object
*/
public static User getUser(Connection con, Long userId) {
User user = null;
try {
PreparedStatement stmt = con.prepareStatement("select * from users where id=?");
stmt.setLong(1, userId);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
user = new User();
user.setId(rs.getLong("id"));
user.setFirstNm(rs.getString("first_nm"));
user.setLastNm(rs.getString("last_nm"));
user.setEmail(rs.getString("email"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setUserType(rs.getString("user_type"));
user.setProfileList(UserProfileDB.getProfilesByUser(con, userId));
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
return user;
}
/**
* inserts new user
* @param user user object
*/
public static void insertUser(User user) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("insert into users (first_nm, last_nm, email, username, user_type, password) values (?,?,?,?,?,?)");
stmt.setString(1, user.getFirstNm());
stmt.setString(2, user.getLastNm());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getUsername());
stmt.setString(5, user.getUserType());
stmt.setString(6, EncryptionUtil.hash(user.getPassword()));
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
DBUtils.closeConn(con);
}
/**
* updates existing user
* @param user user object
*/
public static void updateUser(User user) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("update users set first_nm=?, last_nm=?, email=?, username=?, user_type=?, password=? where id=?");
stmt.setString(1, user.getFirstNm());
stmt.setString(2, user.getLastNm());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getUsername());
stmt.setString(5, user.getUserType());
stmt.setString(6, EncryptionUtil.hash(user.getPassword()));
stmt.setLong(7, user.getId());
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
DBUtils.closeConn(con);
}
/**
* deletes user
* @param userId user id
*/
public static void disableUser(Long userId) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("update users set enabled=false where id=?");
stmt.setLong(1, userId);
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
DBUtils.closeConn(con);
}
/**
* checks to see if username is unique while ignoring current user
*
* @param userId user id
* @param username username
* @return true false indicator
*/
public static boolean isUnique(Long userId, String username){
boolean isUnique=true;
if(userId==null){
userId=-99L;
}
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("select * from users where enabled=true and lower(username) like lower(?) and id != ?");
stmt.setString(1,username);
stmt.setLong(2, userId);
ResultSet rs = stmt.executeQuery();
if(rs.next()) {
isUnique=false;
}
DBUtils.closeRs(rs);
DBUtils.closeStmt(stmt);
} catch(Exception ex){
ex.printStackTrace();
}
DBUtils.closeConn(con);
return isUnique;
}
}