/**
* 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.ec2box.manage.db;
import com.ec2box.manage.model.SortedSet;
import com.ec2box.manage.model.User;
import com.ec2box.manage.util.DBUtils;
import com.ec2box.manage.util.EncryptionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
/**
* DAO class to manage users
*/
public class UserDB {
private static Logger log = LoggerFactory.getLogger(UserDB.class);
public static final String FIRST_NM = "first_nm";
public static final String LAST_NM = "last_nm";
public static final String EMAIL = "email";
public static final String USERNAME = "username";
public static final String USER_TYPE = "user_type";
private UserDB() {
}
/**
* 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<>();
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();
}
finally {
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();
}
finally {
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.setSalt(rs.getString("salt"));
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();
String salt=EncryptionUtil.generateSalt();
PreparedStatement stmt = con.prepareStatement("insert into users (first_nm, last_nm, email, username, user_type, password, salt) 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() + salt));
stmt.setString(7, salt);
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
finally {
DBUtils.closeConn(con);
}
}
/**
* updates existing user
* @param user user object
*/
public static void updateUserNoCredentials(User user) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("update users set first_nm=?, last_nm=?, email=?, username=?, user_type=? 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.setLong(6, user.getId());
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
finally {
DBUtils.closeConn(con);
}
}
/**
* updates existing user
* @param user user object
*/
public static void updateUserCredentials(User user) {
Connection con = null;
try {
con = DBUtils.getConn();
String salt=EncryptionUtil.generateSalt();
PreparedStatement stmt = con.prepareStatement("update users set first_nm=?, last_nm=?, email=?, username=?, user_type=?, password=?, salt=? 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() + salt));
stmt.setString(7, salt);
stmt.setLong(8, user.getId());
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
finally {
DBUtils.closeConn(con);
}
}
/**
* disables users
* @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();
}
finally {
DBUtils.closeConn(con);
}
}
/**
* resets shared secret for user
* @param userId user id
*/
public static void resetSharedSecret(Long userId) {
Connection con = null;
try {
con = DBUtils.getConn();
PreparedStatement stmt = con.prepareStatement("update users set otp_secret=null where id=?");
stmt.setLong(1, userId);
stmt.execute();
DBUtils.closeStmt(stmt);
} catch (Exception e) {
e.printStackTrace();
}
finally {
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){
log.error(ex.toString(), ex);
}
finally {
DBUtils.closeConn(con);
}
return isUnique;
}
}