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

Go to download
/*
 * Created on 10/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.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.antiaction.raptor.dao.AttributeBase;
import com.antiaction.raptor.dao.AttributePoke;
import com.antiaction.raptor.dao.EntityBase;
import com.antiaction.raptor.sql.SqlResult;

public class MSSql_Attribute {

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

	private static final String ATTRIBUTE_INSERT_SQL = ""
			+ "INSERT INTO eav_attribute(tree_id, entity_id, type_id, val_int, val_datetime, val_varchar, val_text) "
			+ "VALUES(?, ?, ?, ?, ?, ?, ?) ";
			//+ "SELECT * FROM eav_attribute WHERE id = @@identity ";

	public static void insert(Connection conn, AttributeBase attribute) {
		PreparedStatement stm = null;
		ResultSet rs = null;

		/*
		Integer a_val_int = attribute.getInteger();
		Timestamp a_val_datetime = attribute.getTimestamp();
		String a_val_varchar = attribute.getVarchar();
		String a_val_text = attribute.getText();
		*/

		Integer a_val_int = AttributePoke.getInteger( attribute );
		Timestamp a_val_datetime = AttributePoke.getTimestamp( attribute );
		String a_val_varchar = AttributePoke.getVarchar( attribute );
		String a_val_text = AttributePoke.getText( attribute );

		try {
			// tree_id, id, type_id, name
			stm = conn.prepareStatement( ATTRIBUTE_INSERT_SQL, Statement.RETURN_GENERATED_KEYS );
			//stm = conn.prepareStatement( ATTRIBUTE_INSERT_SQL );
			stm.clearParameters();
			stm.setInt( 1, attribute.tree_id );
			stm.setInt( 2, attribute.entity_id );
			stm.setInt( 3, attribute.type_id );
			if ( a_val_int != null ) {
				stm.setInt( 4, a_val_int );
			}
			else {
				stm.setNull( 4, Types.INTEGER );
			}
			if ( a_val_datetime != null ) {
				stm.setTimestamp( 5, a_val_datetime );
			}
			else {
				stm.setNull( 5, Types.TIMESTAMP );
			}
			if ( a_val_varchar != null ) {
				stm.setString( 6, a_val_varchar );
			}
			else {
				stm.setNull( 6, Types.VARCHAR );
			}
			if ( a_val_text != null ) {
				stm.setString( 7, a_val_text );
			}
			else {
				stm.setNull( 7, Types.VARCHAR );
			}
			//rs = stm.executeQuery();
			int res = stm.executeUpdate();
			rs = stm.getGeneratedKeys();

			if ( rs.next() ) {
				//attribute.id = rs.getInt( "id" );
				attribute.id = rs.getInt( 1 );
			}
			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;
			}
		}
	}

	private static final String ATTRIBUTES_GET_BY_ENTITY_ID_SQL = ""
			+ "SELECT * FROM eav_attribute a "
			+ "WHERE a.tree_id = ? AND a.entity_id = ? ";

	public static SqlResult getAttributes(Connection conn, int tree_id, int entity_id) {
		PreparedStatement stm;
		SqlResult result = null;
		try {
			result = new SqlResult( conn, ATTRIBUTES_GET_BY_ENTITY_ID_SQL );
			stm = result.stm;
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, entity_id );
			result.rs = stm.executeQuery();
		}
		catch (SQLException e) {
			logger.log( Level.SEVERE, e.toString(), e );
		}
		return result;
	}

	private static final String ATTRIBUTES_TYPED_GET_BY_ENTITY_ID_SQL = ""
			+ "SELECT eta.tree_id, entity_id, eta.id as type_id, ea.id, name, class_namespace, class_name, datatype, viewtype, val_int, val_datetime, val_varchar, val_text, def_int, def_datetime, def_varchar, def_text FROM eav_type_attribute eta "
			+ "LEFT JOIN eav_attribute ea ON entity_id = ? AND ea.tree_id = eta.tree_id AND ea.type_id = eta.id "
			+ "WHERE eta.tree_id = ? ";

	public static SqlResult getTypedAttributes(Connection conn, int tree_id, int entity_id) {
		PreparedStatement stm;
		SqlResult result = null;
		try {
			result = new SqlResult( conn, ATTRIBUTES_TYPED_GET_BY_ENTITY_ID_SQL );
			stm = result.stm;
			stm.clearParameters();
			stm.setInt( 1, entity_id );
			stm.setInt( 2, tree_id );
			result.rs = stm.executeQuery();
		}
		catch (SQLException e) {
			logger.log( Level.SEVERE, e.toString(), e );
		}
		return result;
	}

	/*
	public static ResultSet getAttribute(Connection conn, int tree_id, int entity_id, int attribute_id) {
		// TODO getAttribute
		return null;
	}
	*/

	private static final String ATTRIBUTE_UPDATE_SQL = ""
			+ "UPDATE eav_attribute SET "
			+ "val_int = ?, "
			+ "val_datetime = ?, "
			+ "val_varchar = ?, "
			+ "val_text = ? "
			+ "WHERE tree_id = ? AND id = ? AND entity_id = ? AND type_id = ? ";

	public static void update(Connection conn, AttributeBase attribute) {
		PreparedStatement stm = null;

		/*
		Integer a_val_int = attribute.getInteger();
		Timestamp a_val_datetime = attribute.getTimestamp();
		String a_val_varchar = attribute.getVarchar();
		String a_val_text = attribute.getText();
		*/

		Integer a_val_int = AttributePoke.getInteger( attribute );
		Timestamp a_val_datetime = AttributePoke.getTimestamp( attribute );
		String a_val_varchar = AttributePoke.getVarchar( attribute );
		String a_val_text = AttributePoke.getText( attribute );

		try {
			stm = conn.prepareStatement( ATTRIBUTE_UPDATE_SQL );
			stm.clearParameters();
			if ( a_val_int != null ) {
				stm.setInt( 1, a_val_int );
			}
			else {
				stm.setNull( 1, Types.INTEGER );
			}
			if ( a_val_datetime != null ) {
				stm.setTimestamp( 2, a_val_datetime );
			}
			else {
				stm.setNull( 2, Types.TIMESTAMP );
			}
			if ( a_val_varchar != null ) {
				stm.setString( 3, a_val_varchar );
			}
			else {
				stm.setNull( 3, Types.VARCHAR );
			}
			if ( a_val_text != null ) {
				stm.setString( 4, a_val_text );
			}
			else {
				stm.setNull( 4, Types.VARCHAR );
			}
			stm.setInt( 5, attribute.tree_id );
			stm.setInt( 6, attribute.id );
			stm.setInt( 7, attribute.entity_id );
			stm.setInt( 8, attribute.type_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, AttributeBase attribute) {
		if ( attribute != null && attribute.tree_id > 0 && attribute.id > 0 && attribute.entity_id > 0 ) {
			deleteById( conn, attribute.tree_id, attribute.id, attribute.entity_id );
		}
	}

	private static final String ATTRIBUTE_DELETE_BY_ID_SQL = ""
			+ "DELETE eav_attribute "
			+ "WHERE tree_id = ? AND id = ? AND entity_id = ? ";

	public static void deleteById(Connection conn, int tree_id, int attribute_id, int entity_id) {
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement( ATTRIBUTE_DELETE_BY_ID_SQL );
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, attribute_id );
			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 deleteByEntityObj(Connection conn, EntityBase entity) {
		if ( entity != null && entity.tree_id > 0 && entity.id > 0 ) {
			deleteByEntityId( conn, entity.tree_id, entity.id );
		}
	}

	private static final String ATTRIBUTE_DELETE_BY_ENTITY_ID_SQL = ""
			+ "DELETE eav_attribute "
			+ "WHERE tree_id = ? AND entity_id = ? ";

	public static void deleteByEntityId(Connection conn, int tree_id, int entity_id) {
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement( ATTRIBUTE_DELETE_BY_ENTITY_ID_SQL );
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, 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 deleteSubtreeByObj(Connection conn, EntityBase entity) {
		if ( entity != null && entity.tree_id > 0 && entity.id > 0 ) {
			deleteSubtreeByEntityId( conn, entity.tree_id, entity.id );
		}
	}

	private static final String ATTRIBUTE_DELETE_SUBTREE_BY_ENTITY_ID_SQL = ""
			+ "DELETE eav_attribute "
			+ "FROM eav_attribute a1 "
			+ "INNER JOIN eav_tree_entity t1 ON t1.tree_id = ? AND t1.from_id = ? "
			+ "WHERE a1.tree_id = t1.tree_id AND a1.entity_id = t1.to_id ";

	public static void deleteSubtreeByEntityId(Connection conn, int tree_id, int entity_id) {
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement( ATTRIBUTE_DELETE_SUBTREE_BY_ENTITY_ID_SQL );
			stm.clearParameters();
			stm.setInt( 1, tree_id );
			stm.setInt( 2, 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;
			}
		}
	}

}