com.antiaction.raptor.sql.mssql.MSSql_SecurityEntity Maven / Gradle / Ivy
/*
* Created on 19/03/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.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.antiaction.raptor.dao.SecurityEntityBase;
import com.antiaction.raptor.dao.TreeItem;
import com.antiaction.raptor.sql.SqlResult;
public class MSSql_SecurityEntity {
private static Logger logger = Logger.getLogger( MSSql_Attribute.class.getName() );
public static SqlResult insert(Connection conn, int tree_id, int type_id) {
//SecurityEntity securityEntity = null;
String sql;
PreparedStatement stm;
SqlResult result = null;
try {
sql = "INSERT INTO security_entity(tree_id, type_id, is_admin, language_id) ";
sql += "VALUES(?, ?, ?, ?) ";
sql += "SELECT tree_id, id, type_id, name, is_admin, language_id, login, password, 0 AS children FROM security_entity WHERE id = @@identity ";
result = new SqlResult( conn, sql );
stm = result.stm;
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, type_id );
stm.setBoolean( 3, false );
stm.setInt( 4, 0 );
result.rs = stm.executeQuery();
/*
if ( rs.next() ) {
securityEntity = new SecurityEntity();
securityEntity.tree_id = rs.getInt( 1 );
securityEntity.id = rs.getInt( 2 );
securityEntity.type_id = rs.getInt( 3 );
securityEntity.name = null;
securityEntity.is_admin = false;
securityEntity.language_id = 0;
securityEntity.login = null;
securityEntity.password = null;
}
rs.close();
rs = null;
*/
}
catch (SQLException e) {
//securityEntity = null;
logger.log( Level.SEVERE, e.toString(), e );
}
/*
finally {
if ( rs != null ) {
try {
rs.close();
}
catch (SQLException e) {
// debug
e.printStackTrace();
}
rs = null;
}
}
*/
//return securityEntity;
return result;
}
public static SqlResult getById(Connection conn, int tree_id, int id) {
//SecurityEntity securityEntity = null;
String sql;
PreparedStatement stm;
SqlResult result = null;
try {
//sql = "SELECT tree_id, id, type_id, name, is_admin, language_id, login, password FROM security_entity ";
//sql += "WHERE tree_id = ? AND id = ? ";
sql = "SELECT se1.*, COUNT(t1.delta_level) AS children FROM security_entity se1 ";
sql += "LEFT JOIN eav_tree_entity t1 ON se1.tree_id = t1.tree_id AND t1.from_id = se1.id AND t1.delta_level = 1 ";
sql += "WHERE se1.tree_id = ? AND se1.id = ? ";
sql += "GROUP BY se1.tree_id, se1.type_id, se1.id, se1.name, se1.is_admin, se1.language_id, se1.login, se1.password ";
result = new SqlResult( conn, sql );
stm = result.stm;
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
result.rs = stm.executeQuery();
/*
if ( rs.next() ) {
securityEntity = new SecurityEntity();
securityEntity.tree_id = rs.getInt( 1 );
securityEntity.id = rs.getInt( 2 );
securityEntity.type_id = rs.getInt( 3 );
securityEntity.name = rs.getString( 4 );
securityEntity.is_admin = rs.getBoolean( 5 );
securityEntity.language_id = rs.getInt( 6 );
securityEntity.login = rs.getString( 7 );
securityEntity.password = rs.getString( 8 );
}
rs.close();
rs = null;
*/
}
catch (SQLException e) {
//securityEntity = null;
logger.log( Level.SEVERE, e.toString(), e );
}
/*
finally {
if ( rs != null ) {
try {
rs.close();
}
catch (SQLException e) {
// debug
e.printStackTrace();
}
rs = null;
}
}
*/
//return securityEntity;
return result;
}
public static SqlResult getByLoginCredentials(Connection conn, int tree_id, String login) {
// , String password
//SecurityEntity securityEntity = null;
String sql;
PreparedStatement stm;
SqlResult result = null;
try {
// AND password = ?
//sql = "SELECT tree_id, id, type_id, name, is_admin, language_id, login, password FROM security_entity ";
//sql += "WHERE tree_id = ? AND login = ? ";
sql = "SELECT se1.tree_id, se1.id, se1.type_id, se1.name, se1.is_admin, se1.language_id, se1.login, se1.password, COUNT(t1.delta_level) AS children FROM security_entity se1 ";
sql += "LEFT JOIN eav_tree_entity t1 ON se1.tree_id = t1.tree_id AND t1.from_id = se1.id AND t1.delta_level = 1 ";
sql += "WHERE se1.tree_id = ? AND se1.login = ? ";
sql += "GROUP BY se1.tree_id, se1.type_id, se1.id, se1.name, se1.is_admin, se1.language_id, se1.login, se1.password ";
result = new SqlResult( conn, sql );
stm = result.stm;
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setString( 2, login );
//stm.setString( 3, password );
result.rs = stm.executeQuery();
/*
if ( rs.next() ) {
securityEntity = new SecurityEntity();
securityEntity.tree_id = rs.getInt( 1 );
securityEntity.id = rs.getInt( 2 );
securityEntity.type_id = rs.getInt( 3 );
securityEntity.name = rs.getString( 4 );
securityEntity.is_admin = rs.getBoolean( 5 );
securityEntity.language_id = rs.getInt( 6 );
securityEntity.login = rs.getString( 7 );
securityEntity.password = rs.getString( 8 );
}
rs.close();
rs = null;
*/
}
catch (SQLException e) {
//securityEntity = null;
logger.log( Level.SEVERE, e.toString(), e );
}
/*
finally {
if ( rs != null ) {
try {
rs.close();
}
catch (SQLException e) {
// debug
e.printStackTrace();
}
rs = null;
}
}
*/
//return securityEntity;
return result;
}
public static void update(Connection conn, SecurityEntityBase securityEntity) {
String sql;
PreparedStatement stm = null;
try {
sql = "UPDATE security_entity SET ";
sql += "tree_id = ?, ";
sql += "type_id = ?, ";
sql += "name = ?, ";
sql += "is_admin = ?, ";
sql += "language_id = ?, ";
sql += "login = ?, ";
sql += "password = ? ";
sql += "WHERE id = ? ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, securityEntity.tree_id );
stm.setInt( 2, securityEntity.type_id );
if ( securityEntity.name != null ) {
stm.setString( 3, securityEntity.name );
}
else {
stm.setNull( 3, Types.VARCHAR );
}
stm.setBoolean( 4, securityEntity.is_admin );
stm.setInt( 5, securityEntity.language_id );
if ( securityEntity.login != null ) {
stm.setString( 6, securityEntity.login );
}
else {
stm.setNull( 6, Types.VARCHAR );
}
if ( securityEntity.password != null ) {
stm.setString( 7, securityEntity.password );
}
else {
stm.setNull( 7, Types.VARCHAR );
}
stm.setInt( 8, securityEntity.id );
stm.executeUpdate();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
finally {
if ( stm != null ) {
try {
stm.close();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
stm = null;
}
}
}
public static void deleteByObj(Connection conn, SecurityEntityBase securityEntity) {
if ( securityEntity != null && securityEntity.tree_id > 0 && securityEntity.id > 0 ) {
deleteById( conn, securityEntity.tree_id, securityEntity.id );
}
}
public static void deleteById(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
try {
sql = "DELETE security_entity ";
sql += "WHERE tree_id = ? AND id = ? ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
stm.executeUpdate();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
finally {
if ( stm != null ) {
try {
stm.close();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
stm = null;
}
}
}
public static void deleteSubtreeByObj(Connection conn, SecurityEntityBase securityEntity) {
if ( securityEntity != null && securityEntity.tree_id > 0 && securityEntity.id > 0 ) {
deleteSubtreeById( conn, securityEntity.tree_id, securityEntity.id );
}
}
public static void deleteSubtreeById(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
try {
sql = "DELETE security_entity ";
sql += "FROM security_entity se1 ";
sql += "INNER JOIN eav_tree_entity t1 ON t1.tree_id = ? AND t1.from_id = ? ";
sql += "WHERE se1.tree_id = t1.tree_id AND se1.id = t1.to_id ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
stm.executeUpdate();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
finally {
if ( stm != null ) {
try {
stm.close();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
stm = null;
}
}
}
public static List children(Connection conn, int tree_id, int parent_id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
List treeItems = new ArrayList();
TreeItem treeItem;
try {
sql = "SELECT c.tree_id, c.from_id, c.from_level, c.to_id, c.to_level, e.type_id, e.id, e.name, e.login, COUNT(g.tree_id) as children FROM eav_tree_entity p ";
sql += "JOIN eav_tree_entity c ON p.tree_id = c.tree_id AND p.to_id = c.from_id AND p.from_level + 1 = c.to_level ";
sql += "JOIN security_entity e ON e.tree_id = c.tree_id AND e.id = c.to_id ";
sql += "LEFT JOIN eav_tree_entity g ON c.tree_id = g.tree_id AND c.to_id = g.from_id AND c.to_level + 1 = g.to_level ";
sql += "WHERE p.tree_id = ? AND p.from_id = ? AND p.from_id = p.to_id AND p.from_level = p.to_level ";
sql += "GROUP BY e.tree_id, e.type_id, e.id, e.name, e.login, c.tree_id, c.from_id, c.from_level, c.to_id, c.to_level ";
// debug
//System.out.println( sql );
//System.out.println( " tree_id: " + tree_id );
//System.out.println( " parent_id: " + parent_id );
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, parent_id );
rs = stm.executeQuery();
while ( rs.next() ) {
treeItem = new TreeItem();
treeItem.id = rs.getInt( "id" );
treeItem.type_id = rs.getInt( "type_id" );
treeItem.name = rs.getString( "name" );
treeItem.icon = null;
treeItem.children = rs.getInt( "children" );
treeItems.add( treeItem );
}
rs.close();
rs = null;
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
finally {
if ( rs != null ) {
try {
rs.close();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
rs = null;
}
if ( stm != null ) {
try {
stm.close();
}
catch (SQLException e) {
logger.log( Level.SEVERE, e.toString(), e );
}
stm = null;
}
}
return treeItems;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy