com.antiaction.raptor.sql.mssql.MSSql_Tree Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of raptor-dbl Show documentation
Show all versions of raptor-dbl Show documentation
Raptor database layer implementation.
The newest version!
/*
* Created on 23/02/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;
public class MSSql_Tree {
private static Logger logger = Logger.getLogger( MSSql_Tree.class.getName() );
/**
* Inserts an id as the child of another while also inserting all the intermediate levels.
* So there is an entry from the child to all its parents along the path to the root.
*/
public static void insert(Connection conn, int tree_id, int parent_id, int id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
int from_id;
int from_level;
TreePath pathEntry;
try {
List path = new ArrayList();
// Get parent path.
sql = "SELECT from_id, from_level ";
sql += "FROM eav_tree_entity ";
sql += "WHERE tree_id = ? AND to_id = ? ";
sql += "ORDER BY from_level ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, parent_id );
rs = stm.executeQuery();
while ( rs.next() ) {
from_id = rs.getInt( 1 );
from_level = rs.getInt( 2 );
path.add( new TreePath( tree_id, from_id, from_level ) );
}
rs.close();
rs = null;
stm.close();
stm = null;
// Insert new parent entries.
sql = "INSERT INTO eav_tree_entity (tree_id, from_id, from_level, to_id, to_level, delta_level) ";
sql += " VALUES (?, ?, ?, ?, ?, ?) ";
stm = conn.prepareStatement( sql );
int level = path.size();
if ( parent_id != id ) {
for ( int i=0; i dest_parents = getParentTreePathList( conn, tree_id, to_parent_id );
dest_parents.add( 0, new TreePath( tree_id, to_parent_id, dest_parents.size() ) );
List src_tree = getSubtreeComplete( conn, tree_id, id );
int src_tree_level = src_tree.get( 0 ).from_level;
TreePath parent;
TreeEntry child;
/*
for ( int i=0; i getParentTreePathList(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
List parentTreePathList = new ArrayList();
TreePath treePath;
try {
sql = "SELECT * FROM eav_tree_entity ";
sql += "WHERE tree_id = ? AND to_id = ? AND NOT from_id = to_id ";
sql += "ORDER BY from_level DESC ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
rs = stm.executeQuery();
while ( rs.next() ) {
treePath = new TreePath();
treePath.tree_id = rs.getInt( "tree_id" );
treePath.id = rs.getInt( "from_id" );
treePath.level = rs.getInt( "from_level" );
parentTreePathList.add( treePath );
}
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 parentTreePathList;
}
/**
* Returns an elements path to the root as a list of id's in reverse order.
* @param conn
* @param tree_id
* @param id
* @return
*/
public static List getParentPathIdList(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
List parentPathIdList = new ArrayList();
int from_id;
try {
sql = "SELECT * FROM eav_tree_entity ";
sql += "WHERE tree_id = ? AND to_id = ? AND NOT from_id = to_id ";
sql += "ORDER BY from_level DESC ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
rs = stm.executeQuery();
while ( rs.next() ) {
from_id = rs.getInt( "from_id" );
parentPathIdList.add( from_id );
}
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 parentPathIdList;
}
public static List getSubtreeComplete(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
List treeEntryList = new ArrayList();
TreeEntry treeEntry;
try {
sql = "SELECT t2.* FROM eav_tree_entity t1 ";
sql += "INNER JOIN eav_tree_entity t2 ON t1.tree_id = t2.tree_id AND t2.from_id = t1.to_id ";
sql += "WHERE t1.tree_id = ? AND t1.from_id = ? ";
sql += "ORDER BY t2.from_level, t2.to_level ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
rs = stm.executeQuery();
while ( rs.next() ) {
treeEntry = new TreeEntry();
treeEntry.tree_id = rs.getInt( "tree_id" );
treeEntry.from_id = rs.getInt( "from_id" );
treeEntry.from_level = rs.getInt( "from_level" );
treeEntry.to_id = rs.getInt( "to_id" );
treeEntry.to_level = rs.getInt( "to_level" );
treeEntry.delta_level = rs.getInt( "delta_level" );
treeEntryList.add( treeEntry );
}
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 treeEntryList;
}
/**
* Returns a list of an elements subtree as treepath elements including element itself. Level is relative to subtree root.
* @param conn
* @param tree_id
* @param id
* @return
*/
public static List getSubtree(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
List treePathList = new ArrayList();
TreePath treePath;
try {
sql = "SELECT * FROM eav_tree_entity ";
sql += "WHERE tree_id = ? AND from_id = ? ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
rs = stm.executeQuery();
while ( rs.next() ) {
treePath = new TreePath();
treePath.tree_id = rs.getInt( "tree_id" );
treePath.id = rs.getInt( "from_id" );
treePath.level = rs.getInt( "delta_level" );
treePathList.add( treePath );
}
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 treePathList;
}
/**
* Returns the number of element in a subtree including element itself.
* @param conn
* @param tree_id
* @param id
* @return
*/
public static int getSubtreeCount(Connection conn, int tree_id, int id) {
String sql;
PreparedStatement stm = null;
ResultSet rs = null;
int subtreeCount = 0;
try {
sql = "SELECT COUNT(*) FROM eav_tree_entity ";
sql += "WHERE tree_id = ? AND from_id = ? ";
stm = conn.prepareStatement( sql );
stm.clearParameters();
stm.setInt( 1, tree_id );
stm.setInt( 2, id );
rs = stm.executeQuery();
if ( rs.next() ) {
subtreeCount = 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;
}
}
return subtreeCount;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy