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

Go to download
/*
 * Created on 09/06/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.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.antiaction.raptor.dao.EntityBase;
import com.antiaction.raptor.dao.TreeItem;
import com.antiaction.raptor.sql.SqlResult;

public class MSSql_Entity {

	private static Logger logger = Logger.getLogger( MSSql_Entity.class.getName() );

	public static SqlResult insert(Connection conn, int tree_id, int type_id) {
		String sql;
		PreparedStatement stm;
		SqlResult result = null;

		try {
			// tree_id, id, type_id, name
			sql = "INSERT INTO eav_entity(tree_id, type_id) ";
			sql += "VALUES(?, ?) ";
			sql += "SELECT e.*, 0 AS children FROM eav_entity e WHERE id = @@identity ";

			result = new SqlResult( conn, sql );
			stm = result.stm;
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, type_id );

			result.rs = stm.executeQuery();
		}
		catch (SQLException e) {
			logger.log( Level.SEVERE, e.toString(), e );
		}

		return result;
	}

	public static SqlResult getById(Connection conn, int tree_id, int id) {
		String sql;
		PreparedStatement stm;
		SqlResult result = null;

		try {
			// tree_id, id, type_id, name
			//sql = "SELECT * FROM eav_entity ";
			//sql += "WHERE tree_id = ? AND id = ? ";

			sql = "SELECT e1.*, COUNT(t1.delta_level) AS children FROM eav_entity e1 ";
			sql += "LEFT JOIN eav_tree_entity t1 ON e1.tree_id = t1.tree_id AND t1.from_id = e1.id AND t1.delta_level = 1 ";
			sql += "WHERE e1.tree_id = ? AND e1.id = ? ";
			sql += "GROUP BY e1.tree_id, e1.type_id, e1.id ";

			result = new SqlResult( conn, sql );
			stm = result.stm;
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, id );

			result.rs = stm.executeQuery();
		}
		catch (SQLException e) {
			logger.log( Level.SEVERE, e.toString(), e );
		}

		return result;
	}

	public static void update(Connection conn, EntityBase entity) {
		String sql;
		PreparedStatement stm = null;

		try {
			sql = "UPDATE eav_entity SET ";
			sql += "tree_id = ?, ";
			sql += "type_id = ? ";
			//sql += "name = ?, ";
			sql += "WHERE id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, entity.tree_id );
			stm.setInt( 2, entity.type_id );
			/*
			if ( entity.name != null ) {
				stm.setString( 3, entity.name );
			}
			else {
				stm.setNull( 3, Types.VARCHAR );
			}
			*/
			stm.setInt( 3, entity.id );
			stm.executeUpdate();
			stm.close();
			stm = null;
		}
		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, EntityBase entity) {
		if ( entity != null && entity.tree_id > 0 && entity.id > 0 ) {
			deleteById( conn, entity.tree_id, entity.id );
		}
	}

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

		try {
			sql = "DELETE eav_entity ";
			sql += "WHERE tree_id = ? AND id = ? ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, id );
			stm.executeUpdate();
			stm.close();
			stm = null;
		}
		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, EntityBase entity) {
		if ( entity != null && entity.tree_id > 0 && entity.id > 0 ) {
			deleteSubtreeById( conn, entity.tree_id, entity.id );
		}
	}

	public static void deleteSubtreeById(Connection conn, int tree_id, int id) {
		String sql;
		PreparedStatement stm = null;

		try {
			sql = "DELETE eav_entity ";
			sql += "FROM eav_entity e1 ";
			sql += "INNER JOIN eav_tree_entity t1 ON t1.tree_id = ? AND t1.from_id = ? ";
			sql += "WHERE e1.tree_id = t1.tree_id AND e1.id = t1.to_id ";

			stm = conn.prepareStatement( sql );
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, id );
			stm.executeUpdate();
			stm.close();
			stm = null;
		}
		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, a.val_varchar as name, 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 eav_entity e ON e.tree_id = c.tree_id AND e.id = c.to_id ";
			sql += "LEFT JOIN eav_attribute a ON a.tree_id = e.tree_id AND a.entity_id = e.id AND a.type_id = 1 ";
			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, a.val_varchar, 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;
			stm.close();
			stm = 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;
	}

}