com.antiaction.raptor.sql.mssql.MSSql_SecurityTreeRule Maven / Gradle / Ivy

Go to download
/*
 * Created on 08/04/2010
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */

package com.antiaction.raptor.sql.mssql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import com.antiaction.raptor.dao.SecurityChain;
import com.antiaction.raptor.dao.SecurityEntityBase;
import com.antiaction.raptor.dao.SecurityPermission;
import com.antiaction.raptor.dao.SecurityRule;

public class MSSql_SecurityTreeRule {

	public static int insert(Connection conn, SecurityRule rule) {
		String sql;
		PreparedStatement stm;
		ResultSet rs = null;

		try {
			sql = "INSERT INTO security_tree_rule(chain_id, chain_level, permission_type_id, grant_on_tree_id, grant_on_type_id, grant_on_id) ";
			sql += "VALUES(?, ?, ?, ?, ?, ?) ";
			sql += "SELECT id FROM security_tree_rule WHERE id = @@identity ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, rule.chain_id );
			stm.setInt( 2, rule.chain_level );
			stm.setInt( 3, rule.permission_type_id );
			if ( rule.grant_permission != null ) {
				stm.setBoolean( 4, rule.grant_permission );
			}
			else {
				stm.setNull( 4, Types.BOOLEAN );
			}
			if ( rule.on_type_id != null ) {
				stm.setInt( 5, rule.on_type_id );
			}
			else {
				stm.setNull( 5, Types.INTEGER );
			}
			if ( rule.on_id != null ) {
				stm.setInt( 6, rule.on_id );
			}
			else {
				stm.setNull( 6, Types.INTEGER );
			}

			rs = stm.executeQuery();
			if ( rs.next() ) {
				rule.id = rs.getInt( 1 );
			}

			rs.close();
			rs = null;
		}
		catch (SQLException e) {
			// debug
			e.printStackTrace();
		}
		finally {
			if ( rs != null ) {
				try {
					rs.close();
				}
				catch (SQLException e) {
					// debug
					e.printStackTrace();
				}
				rs = null;
			}
		}

		return rule.id;
	}

	public static SecurityRule getById(Connection conn, int id) {
		SecurityRule rule = null;

		String sql;
		PreparedStatement stm;
		ResultSet rs = null;

		try {
			sql = "SELECT id, chain_id, chain_level, permission_type_id, grant_permission, grant_on_type_id, grant_on_id FROM security_tree_rule ";
			sql += "WHERE id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, id );

			rs = stm.executeQuery();
			if ( rs.next() ) {
				rule = new SecurityRule();
				rule.id = rs.getInt( 1 );
				rule.chain_id = rs.getInt( 2 );
				rule.chain_level = rs.getInt( 3 );
				rule.permission_type_id = rs.getInt( 4 );
				rule.grant_permission = rs.getBoolean( 5 );
				if ( rs.wasNull() ) {
					rule.grant_permission = null;
				}
				rule.on_type_id = rs.getInt( 6 );
				if ( rs.wasNull() ) {
					rule.on_type_id = null;
				}
				rule.on_id = rs.getInt( 7 );
				if ( rs.wasNull() ) {
					rule.on_id = null;
				}
			}

			rs.close();
			rs = null;
		}
		catch (SQLException e) {
			rule = null;

			// debug
			e.printStackTrace();
		}
		finally {
			if ( rs != null ) {
				try {
					rs.close();
				}
				catch (SQLException e) {
					// debug
					e.printStackTrace();
				}
				rs = null;
			}
		}

		return rule;
	}

	public static void update(Connection conn, SecurityRule rule) {
		String sql;
		PreparedStatement stm;

		try {
			sql = "UPDATE security_tree_rule SET ";
			sql += "chain_id = ?, ";
			sql += "chain_level = ?, ";
			sql += "permission_type_id = ?, ";
			sql += "grant_permission = ?, ";
			sql += "grant_on_type_id = ?, ";
			sql += "grant_on_id = ? ";
			sql += "WHERE id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, rule.chain_id );
			stm.setInt( 2, rule.chain_level );
			stm.setInt( 3, rule.permission_type_id );
			if ( rule.grant_permission != null ) {
				stm.setBoolean( 4, rule.grant_permission );
			}
			else {
				stm.setNull( 4, Types.BOOLEAN );
			}
			if ( rule.on_type_id != null ) {
				stm.setInt( 5, rule.on_type_id );
			}
			else {
				stm.setNull( 5, Types.INTEGER );
			}
			if ( rule.on_id != null ) {
				stm.setInt( 6, rule.on_id );
			}
			else {
				stm.setNull( 6, Types.INTEGER );
			}
			stm.setInt( 7, rule.id );
			stm.executeUpdate();
		}
		catch (SQLException e) {
			// debug
			e.printStackTrace();
		}
	}

	public static void deleteByObj(Connection conn, SecurityRule rule) {
		if ( rule != null && rule.id > 0 ) {
			deleteById( conn, rule.id );
		}
	}

	public static void deleteById(Connection conn, int id) {
		String sql;
		PreparedStatement stm;

		try {
			sql = "DELETE security_tree_rule ";
			sql += "WHERE id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, id );
			stm.executeUpdate();
		}
		catch (SQLException e) {
			// debug
			e.printStackTrace();
		}
	}

	public static void deleteByChainObj(Connection conn, SecurityChain chain) {
		if ( chain != null && chain.id > 0 ) {
			deleteByPermissionId( conn, chain.id );
		}
	}

	public static void deleteByChainId(Connection conn, int chain_id) {
		String sql;
		PreparedStatement stm;

		try {
			sql = "DELETE security_tree_rule ";
			sql += "WHERE chain_id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, chain_id );
			stm.executeUpdate();
		}
		catch (SQLException e) {
			// debug
			e.printStackTrace();
		}
	}

	public static void deleteByPermissionObj(Connection conn, SecurityPermission permission) {
		if ( permission != null && permission.id > 0 ) {
			deleteByPermissionId( conn, permission.id );
		}
	}

	public static void deleteByPermissionId(Connection conn, int permission_id) {
		String sql;
		PreparedStatement stm;

		try {
			sql = "DELETE security_tree_rule ";
			sql += "FROM security_tree_rule str1 ";
			sql += "INNER JOIN security_tree_chain stc1 ON stc1.id = str1.chain_id ";
			sql += "WHERE stc1.permission_id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, permission_id );
			stm.executeUpdate();
		}
		catch (SQLException e) {
			// debug
			e.printStackTrace();
		}
	}

	public static Map getPermissionsByUserObj(Connection conn, SecurityEntityBase securityEntity) {
		Map permissions = new HashMap();
		if ( securityEntity != null && securityEntity.id > 0 ) {
			permissions = getPermissionsByUserId( conn, securityEntity.tree_id, securityEntity.id );
		}
		return permissions;
	}

	public static Map getPermissionsByUserId(Connection conn, int tree_id, int user_id) {
		Map permissions = new HashMap();

		SecurityPermission permission;
		SecurityChain chain;
		SecurityRule rule;

		String sql;
		PreparedStatement stm;
		ResultSet rs = null;

		int p_id;
		int c_id;
		//int r_id;

		try {
			sql = "SELECT scp1.id p_id, scp1.user_tree_id, scp1.user_id, scp1.on_tree_id, scp1.on_id, stc1.id c_id, stc1.permission_id, ";
			sql += "str1.id r_id, str1.chain_id, str1.chain_level, str1.permission_type_id, str1.grant_permission, str1.on_type_id, str1.on_id ";
			sql += "FROM security_tree_permission scp1 ";
			sql += "LEFT JOIN security_tree_chain stc1 ON stc1.permission_id = scp1.id ";
			sql += "LEFT JOIN security_tree_rule str1 ON str1.chain_id = stc1.id ";
			sql += "WHERE scp1.user_tree_id = ? AND scp1.user_id = ? ";
			sql += "ORDER BY scp1.id, stc1.id, str1.chain_level ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, user_id );

			rs = stm.executeQuery();
			if ( rs.next() ) {
				p_id = rs.getInt( "p_id" );
				permission = permissions.get( p_id );
				if ( permission == null ) {
					permission = new SecurityPermission();
					permission.id = p_id;
					permission.user_tree_id = rs.getInt( "user_tree_id" );
					permission.user_id = rs.getInt( "user_id" );
					permission.on_tree_id = rs.getInt( "on_tree_id" );
					permission.on_id = rs.getInt( "on_id" );
					permissions.put( p_id, permission );
				}

				c_id = rs.getInt( "c_id" );
				chain = permission.chains.get( c_id );
				if ( chain != null ) {
					chain = new SecurityChain();
					chain.id = c_id;
					chain.permission_id = rs.getInt( "permission_id" );
					permission.chains.put( chain.id, chain );
				}

				rule = new SecurityRule();
				rule.id = rs.getInt( "r_id" );
				rule.chain_id = rs.getInt( "chain_id" );
				rule.chain_level = rs.getInt( "chain_level" );
				rule.permission_type_id = rs.getInt( "permission_type_id" );
				rule.grant_permission = rs.getBoolean( "grant_permission" );
				rule.on_type_id = rs.getInt( "on_type_id" );
				rule.on_id = rs.getInt( "on_id" );
				chain.rules.add( rule );
			}

			rs.close();
			rs = null;
		}
		catch (SQLException e) {
			rule = null;

			// debug
			e.printStackTrace();
		}
		finally {
			if ( rs != null ) {
				try {
					rs.close();
				}
				catch (SQLException e) {
					// debug
					e.printStackTrace();
				}
				rs = null;
			}
		}

		return permissions;
	}

}