/******************************************************************************

  * RootsDaoJdbc.java - Implement the Dao interface for the Roots

  *

  * PicMan - The BuckoSoft Picture Manager in Java

  * Copyright(c) 2006 - Dick Balaska

  * 

  */

 package com.buckosoft.PicMan.db;

 import java.util.List;

 import java.util.Iterator;

 import javax.sql.DataSource;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Types;

 import org.apache.commons.logging.Log;

 import org.apache.commons.logging.LogFactory;

 import org.springframework.dao.DataAccessException;

 import org.springframework.jdbc.core.SqlParameter;

 //import org.springframework.jdbc.object.SqlFunction;

 import org.springframework.jdbc.object.SqlUpdate;

 import org.springframework.jdbc.object.MappingSqlQuery;

 import com.buckosoft.PicMan.domain.Root;

 import com.buckosoft.PicMan.db.RootsDao;

 /** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Root}s.

  * @author Dick Balaska

  * @since 2006/09/30

  * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/RootsDaoJdbc.java">RootsDaoJdbc.java</a>

  */

 public class RootsDaoJdbc implements RootsDao {

         /** Logger for this class and subclasses */

         private static final boolean DEBUG = false;

         protected final Log logger = LogFactory.getLog(getClass());

         private DataSource ds;

         private        List<Root>        rootsCache = null;

         /** Set the reference to the JDBC datasource.

          * @param ds The datasource as configured by Spring.

          * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/WebContent/WEB-INF/picManDatabase/picManDatabase.xml">picManDatabase.xml</a>

          */ 

         public void setDataSource(DataSource ds) {

                 if (DEBUG)

                         logger.info("Set Datasource.");

         this.ds = ds;

     }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#getRoots()

          */

         @SuppressWarnings("unchecked")

         public List<Root> getRoots() throws DataAccessException {

                 if (rootsCache != null)

                         return(rootsCache);

                 if (DEBUG)

                         logger.info("getSets()");

         RootsQuery rq = new RootsQuery(ds);

         List<Root> l = rq.execute();

         rootsCache = l;

         return(l);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#getInactiveRoots()

          */

         public List<Root> getInactiveRoots() throws DataAccessException {

                 RootsQuery rq = new RootsQuery(ds);

                 @SuppressWarnings("unchecked")

         List<Root> inactiveRoots = rq.execute();

                 Iterator<Root> siter = inactiveRoots.iterator();

                 while (siter.hasNext()) {

                         Root root = (Root)siter.next();

                         if (root.isActive())

                                 siter.remove();

                 }

                 return(inactiveRoots);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#getActiveRoots()

          */

         @SuppressWarnings("unchecked")

         public List<Root> getActiveRoots() throws DataAccessException {

                 RootsQuery rq = new RootsQuery(ds);

         List<Root> activeRoots = rq.execute();

                 Iterator<Root> siter = activeRoots.iterator();

                 while (siter.hasNext()) {

                         Root root = siter.next();

                         if (!root.isActive())

                                 siter.remove();

                 }

                 return(activeRoots);

         }

                 /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#getRootCount()

          */

         public int        getRootCount() throws DataAccessException {

                 if (rootsCache == null)

                         getRoots();

                 return(rootsCache.size());

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#getRoot(int)

          */

         public        Root        getRoot(int rid) throws DataAccessException {

                 if (rootsCache == null)

                         getRoots();

                 Iterator<Root> iter = rootsCache.iterator();

                 while (iter.hasNext()) {

                         Root root = (Root)iter.next();

                         if (root.getRid() == rid)

                                 return(root);

                 }

                 return(null);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#getRoot(java.lang.String)

          */

         public        Root        getRoot(String name) throws DataAccessException {

                 if (rootsCache == null)

                         getRoots();

                 Iterator<Root> iter = rootsCache.iterator();

                 while (iter.hasNext()) {

                         Root root = (Root)iter.next();

                         if (root.getName().equals(name))

                                 return(root);

                 }

                 return(null);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#storeRoot(com.buckosoft.PicMan.domain.Root)

          */

         public void storeRoot(Root root) throws DataAccessException {

                 if (root.getRid() <= 0)

                         throw(new RuntimeException("Can't store Root with rid=" + root.getRid()));

                 rootsCache = null;

                 RootUpdate ru = new RootUpdate(ds, root);

                 ru.update(root);                        

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#setRoots(java.util.List)

          */

         public void setRoots(List<Root> roots) throws DataAccessException {

                 rootsCache = null;

                 RootUpdate ru = new RootUpdate(ds);

                 ru.update(roots);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#addRoot(com.buckosoft.PicMan.domain.Root)

          */

         public void addRoot(Root r) throws DataAccessException {

                 rootsCache = null;

                 RootInsert ri = new RootInsert(ds);

                 ri.insert(r);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.RootsDao#deleteRoot(com.buckosoft.PicMan.domain.Root)

          */

         public        void        deleteRoot(Root r) throws DataAccessException {

                 rootsCache = null;

                 RootDelete rd = new RootDelete(ds);

                 rd.delete(r);

         }

         /**

          * <code>Root</code> Query object.

          */

         class RootsQuery extends MappingSqlQuery {

                 RootsQuery(DataSource ds) {

             super(ds, "SELECT * from roots");

             compile();

         }

         protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                 Root r = new Root();

                 r.setRid(rs.getInt("rid"));

                 r.setName(rs.getString("name"));

                 r.setPath(rs.getString("path"));

                 r.setFilePrefix(rs.getString("filePrefix"));

                 r.setActive(rs.getBoolean("active"));

                 return(r);

         }

         }

         /**

          * <code>Root</code> Update Object.

          */

         protected class RootUpdate extends SqlUpdate {

                 private        DataSource        ds;

                 /**

                  * Create a new instance of RootUpdate.

                  * @param ds the DataSource to use for the update

                  */

                 protected RootUpdate(DataSource ds) {

                         this.ds = ds;

                 }

                 protected RootUpdate(DataSource ds, Root root) {

                         super(ds, "UPDATE roots SET name=?, path=?, filePrefix=?, active=? WHERE rid = ? LIMIT 1");

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 /**

                  * Method to update the <code>Root</code>'s data.

                  * @param roots The Roots that should be stored

                  * @return 0

                  */

                 protected int update(List<Root> roots) {

                         // Empty the existing table

                         SqlUpdate sf = new SqlUpdate(ds, "TRUNCATE TABLE roots");

                         sf.compile();

                         int ret = sf.update();

                         logger.info("sf.empty() returned" + ret);

                         // Save each of our attributes

                         RootInsert ri = new RootInsert(ds);

                         Iterator<Root> it = roots.iterator();

                         while (it.hasNext())

                                 ri.insert(it.next());

                         return(0);

                 }

                 protected int update(Root root) {

                         return this.update(new Object[] {

                                         root.getName(),

                                         root.getPath(),

                                         root.getFilePrefix(),

                                         new Integer(root.isActive() ? 1 : 0),

                                         new Integer(root.getRid()),

                         });

                 }

         }

         /**

          * <code>Root</code> Insert Object.

          */

         protected class RootInsert extends SqlUpdate {

                 /**

                  * Create a new instance of SetInsert.

                  * @param ds the DataSource to use for the insert

                  */

                 protected RootInsert(DataSource ds) {

                         super(ds, "INSERT INTO roots VALUES(?,?,?,?,?)");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         compile();

                 }

                 protected void insert(Root r) {

                         Object[] objs = new Object[] {

                                 new Integer(r.getRid()),

                                 new Integer(r.isActive() ? 1 : 0),

                                 r.getName(), r.getPath(), r.getFilePrefix(),

                                 };

                         super.update(objs);

                 }

         }

         /**

          * <code>Root</code> Delete Object.

          */

         protected class RootDelete extends SqlUpdate {

                 /**

                  * Create a new instance of SetDelete.

                  * @param ds the DataSource to use for the delete

                  */

                 protected RootDelete(DataSource ds) {

                         super(ds, "DELETE FROM rots WHERE name = (?)");

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         compile();

                 }

                 protected void delete(Root root) {

                         Object[] objs = new Object[] {

                                         root.getName()};

                         super.update(objs);

                 }

         }

 }