com.serotonin.mango.db.dao.UserDao.java Source code

Java tutorial

Introduction

Here is the source code for com.serotonin.mango.db.dao.UserDao.java

Source

/*
Mango - Open Source M2M - http://mango.serotoninsoftware.com
Copyright (C) 2006-2011 Serotonin Software Technologies Inc.
@author Matthew Lohbihler
    
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
    
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
    
You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.serotonin.mango.db.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;

import com.serotonin.db.spring.GenericRowMapper;
import com.serotonin.mango.Common;
import com.serotonin.mango.rt.dataImage.SetPointSource;
import com.serotonin.mango.rt.dataSource.modbus.ModbusDataSource;
import com.serotonin.mango.rt.event.EventInstance;
import com.serotonin.mango.vo.User;
import com.serotonin.mango.vo.UserComment;
import com.serotonin.mango.vo.permission.DataPointAccess;
import com.serotonin.web.taglib.Functions;

public class UserDao extends BaseDao {
    private final Log LOG = LogFactory.getLog(ModbusDataSource.class);

    private static final String USER_SELECT = "select id, username, password, email, phone, admin, disabled, selectedWatchList, homeUrl, lastLogin, "
            + "  receiveAlarmEmails, receiveOwnAuditEvents " + "from users ";

    public User getUser(int id) {
        User user = queryForObject(USER_SELECT + "where id=?", new Object[] { id }, new UserRowMapper(), null);
        populateUserPermissions(user);
        return user;
    }

    public User getUser(String username) {
        User user = queryForObject(USER_SELECT + "where lower(username)=?", new Object[] { username.toLowerCase() },
                new UserRowMapper(), null);
        populateUserPermissions(user);
        return user;
    }

    class UserRowMapper implements GenericRowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            int i = 0;
            user.setId(rs.getInt(++i));
            user.setUsername(rs.getString(++i));
            user.setPassword(rs.getString(++i));
            user.setEmail(rs.getString(++i));
            user.setPhone(rs.getString(++i));
            user.setAdmin(charToBool(rs.getString(++i)));
            user.setDisabled(charToBool(rs.getString(++i)));
            user.setSelectedWatchList(rs.getInt(++i));
            user.setHomeUrl(rs.getString(++i));
            user.setLastLogin(rs.getLong(++i));
            user.setReceiveAlarmEmails(rs.getInt(++i));
            user.setReceiveOwnAuditEvents(charToBool(rs.getString(++i)));
            return user;
        }
    }

    public List<User> getUsers() {
        List<User> users = query(USER_SELECT + "order by username", new Object[0], new UserRowMapper());
        populateUserPermissions(users);
        return users;
    }

    public List<User> getActiveUsers() {
        List<User> users = query(USER_SELECT + "where disabled=?", new Object[] { boolToChar(false) },
                new UserRowMapper());
        populateUserPermissions(users);
        return users;
    }

    private void populateUserPermissions(List<User> users) {
        for (User user : users)
            populateUserPermissions(user);
    }

    private static final String SELECT_DATA_SOURCE_PERMISSIONS = "select dataSourceId from dataSourceUsers where userId=?";
    private static final String SELECT_DATA_POINT_PERMISSIONS = "select dataPointId, permission from dataPointUsers where userId=?";

    public void populateUserPermissions(User user) {
        if (user == null)
            return;

        user.setDataSourcePermissions(
                queryForList(SELECT_DATA_SOURCE_PERMISSIONS, new Object[] { user.getId() }, Integer.class));
        user.setDataPointPermissions(query(SELECT_DATA_POINT_PERMISSIONS, new Object[] { user.getId() },
                new GenericRowMapper<DataPointAccess>() {
                    public DataPointAccess mapRow(ResultSet rs, int rowNum) throws SQLException {
                        DataPointAccess a = new DataPointAccess();
                        a.setDataPointId(rs.getInt(1));
                        a.setPermission(rs.getInt(2));
                        return a;
                    }
                }));
    }

    public void saveUser(final User user) {
        getTransactionTemplate().execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                if (user.getId() == Common.NEW_ID)
                    insertUser(user);
                else
                    updateUser(user);
            }
        });
    }

    private static final String USER_INSERT = "insert into users ("
            + "  username, password, email, phone, admin, disabled, homeUrl, receiveAlarmEmails, receiveOwnAuditEvents) "
            + "values (?,?,?,?,?,?,?,?,?)";

    void insertUser(User user) {
        int id = doInsert(USER_INSERT,
                new Object[] { user.getUsername(), user.getPassword(), user.getEmail(), user.getPhone(),
                        boolToChar(user.isAdmin()), boolToChar(user.isDisabled()), user.getHomeUrl(),
                        user.getReceiveAlarmEmails(), boolToChar(user.isReceiveOwnAuditEvents()) },
                new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                        Types.VARCHAR, Types.VARCHAR, Types.INTEGER, Types.VARCHAR });
        user.setId(id);
        saveRelationalData(user);
    }

    private static final String USER_UPDATE = "update users set "
            + "  username=?, password=?, email=?, phone=?, admin=?, disabled=?, homeUrl=?, receiveAlarmEmails=?, "
            + "  receiveOwnAuditEvents=? " + "where id=?";

    void updateUser(User user) {
        // potential fix or "An attempt was made to get a data value of type 'VARCHAR' from a data value of type 'null'"  
        if (user.getPhone() == null)
            user.setPhone("");
        if (user.getHomeUrl() == null)
            user.setHomeUrl("");

        try {
            ejt.update(USER_UPDATE, new Object[] { user.getUsername(), user.getPassword(), user.getEmail(),
                    user.getPhone(), boolToChar(user.isAdmin()), boolToChar(user.isDisabled()), user.getHomeUrl(),
                    user.getReceiveAlarmEmails(), boolToChar(user.isReceiveOwnAuditEvents()), user.getId() },
                    new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
                            Types.VARCHAR, Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.INTEGER });

            saveRelationalData(user);
        } catch (DataAccessException e) {
            LOG.error("Error updating user: " + user, e);
            throw e;
        }
    }

    private void saveRelationalData(final User user) {
        // Delete existing permissions.
        ejt.update("delete from dataSourceUsers where userId=?", new Object[] { user.getId() });
        ejt.update("delete from dataPointUsers where userId=?", new Object[] { user.getId() });

        // Save the new ones.
        ejt.batchUpdate("insert into dataSourceUsers (dataSourceId, userId) values (?,?)",
                new BatchPreparedStatementSetter() {
                    public int getBatchSize() {
                        return user.getDataSourcePermissions().size();
                    }

                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setInt(1, user.getDataSourcePermissions().get(i));
                        ps.setInt(2, user.getId());
                    }
                });
        ejt.batchUpdate("insert into dataPointUsers (dataPointId, userId, permission) values (?,?,?)",
                new BatchPreparedStatementSetter() {
                    public int getBatchSize() {
                        return user.getDataPointPermissions().size();
                    }

                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setInt(1, user.getDataPointPermissions().get(i).getDataPointId());
                        ps.setInt(2, user.getId());
                        ps.setInt(3, user.getDataPointPermissions().get(i).getPermission());
                    }
                });
    }

    public void deleteUser(final int userId) {
        getTransactionTemplate().execute(new TransactionCallbackWithoutResult() {
            @SuppressWarnings("synthetic-access")
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                Object[] args = new Object[] { userId };
                ejt.update("update userComments set userId=null where userId=?", args);
                ejt.update("delete from mailingListMembers where userId=?", args);
                ejt.update("update pointValueAnnotations set sourceId=null where sourceId=? and sourceType="
                        + SetPointSource.Types.USER, args);
                ejt.update("delete from userEvents where userId=?", args);
                ejt.update(
                        "update events set ackUserId=null, alternateAckSource="
                                + EventInstance.AlternateAcknowledgementSources.DELETED_USER + " where ackUserId=?",
                        args);
                ejt.update("delete from users where id=?", args);
            }
        });
    }

    public void recordLogin(int userId) {
        ejt.update("update users set lastLogin=? where id=?", new Object[] { System.currentTimeMillis(), userId });
    }

    public void saveHomeUrl(int userId, String homeUrl) {
        ejt.update("update users set homeUrl=? where id=?", new Object[] { homeUrl, userId });
    }

    //
    //
    // User comments
    //
    private static final String USER_COMMENT_INSERT = "insert into userComments (userId, commentType, typeKey, ts, commentText) "
            + "values (?,?,?,?,?)";

    public void insertUserComment(int typeId, int referenceId, UserComment comment) {
        comment.setComment(Functions.truncate(comment.getComment(), 1024));
        ejt.update(USER_COMMENT_INSERT,
                new Object[] { comment.getUserId(), typeId, referenceId, comment.getTs(), comment.getComment() });
    }
}