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

  * ChainsDaoJdbc.java - Implement the Dao interface for the Chains

  *

  * PicMan - The BuckoSoft Picture Manager in Java

  * Copyright(c) 2006 - Dick Balaska

  * 

  */

 package com.buckosoft.PicMan.db;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Types;

 import java.util.Iterator;

 import java.util.List;

 import javax.sql.DataSource;

 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.MappingSqlQuery;

 import org.springframework.jdbc.object.SqlFunction;

 import org.springframework.jdbc.object.SqlUpdate;

 import com.buckosoft.PicMan.domain.Chain;

 import com.buckosoft.PicMan.domain.SetSize;

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

  * @author Dick Balaska

  * @since 2006/01/24

  * @see com.buckosoft.PicMan.domain.Chain

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

  */

 public class ChainsDaoJdbc implements ChainsDao {

         private static final boolean DEBUG = false;

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

         private DataSource ds;

         private        List<Chain>        chainsCache;

     /** Set the database reference

      * @param ds The DataSource as generated by the Spring config/setup.

      */

         public void setDataSource(DataSource ds) {

                 if (DEBUG)

                         logger.info("Set Datasource.");

         this.ds = ds;

     }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.ChainsDao#getChains()

          */

         public List<Chain> getChains() throws DataAccessException {

                 if (chainsCache != null)

                         return(chainsCache);

                 if (DEBUG)

                         logger.info("getChains()");

         ChainsQuery sq = new ChainsQuery(ds);

         @SuppressWarnings("unchecked")

                 List<Chain> l = sq.execute();

         chainsCache = l;

         return(l);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.ChainsDao#getChain(java.lang.String)

          */

         public        Chain        getChain(String name) throws DataAccessException {

                 if (DEBUG)

                         logger.info("getChain: " + name);

                 if (sql_getChainQUERY == null)

                         sql_getChainQUERY = new ChainsQuery(ds, name);

         return (Chain)sql_getChainQUERY.findObject(name);

         }

         private        ChainsQuery        sql_getChainQUERY = null;

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.ChainsDao#getChain(int)

          */

         public        Chain        getChain(int cid) throws DataAccessException {

                 if (DEBUG)

                         logger.info("getChain: " + cid);

                 if (chainsCache != null) {

                         for (Chain c : chainsCache) {

                                 if (c.getCid() == cid)

                                         return(c);

                         }

                 }                        

                 if (sql_getChainCidQUERY == null)

                         sql_getChainCidQUERY = new ChainsQuery(ds, cid);

         return (Chain)sql_getChainCidQUERY.findObject(cid);

         }

         private        ChainsQuery        sql_getChainCidQUERY = null;

         /**

          * Add a Chain to the database

          * @param c The Chain to add

          */

         private void addChain(Chain c) throws DataAccessException {

                 c.setCid(getChainCount());

                 if (sql_addChainINSERT == null)

                         sql_addChainINSERT = new ChainInsert(ds);

                 chainsCache = null;

                 sql_addChainINSERT.insert(c);

                 storeChainSetSizes(c);

         }

         private        ChainInsert        sql_addChainINSERT = null;

         private        void updateChain(Chain c) throws DataAccessException {

                 ChainUpdate cu = new ChainUpdate(ds);

                 chainsCache = null;

                 cu.update(c);

                 storeChainSetSizes(c);

         }

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.ChainsDao#storeChain(com.buckosoft.PicMan.domain.Chain)

          */

         public void storeChain(Chain chain) throws DataAccessException {

                 if (chain.getCid() == -1)

                         addChain(chain);

                 else

                         updateChain(chain);

         }

         private        void storeChainSetSizes(Chain c) throws DataAccessException {

                 ChainSetSizesDelete cssd = new ChainSetSizesDelete(ds);

                 cssd.delete(c);

                 Iterator<SetSize> iter = c.getSetSizes().iterator();

                 while (iter.hasNext()) {

                         SetSize ss = (SetSize)iter.next();

                         if (sql_storeChainSetSizesINSERT == null)

                                 sql_storeChainSetSizesINSERT = new ChainSetSizesInsert(ds);

                         sql_storeChainSetSizesINSERT.insert(c.getCid(), ss.getGuiSetSize());

                 }

         }

         private        ChainSetSizesInsert        sql_storeChainSetSizesINSERT = null;

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.ChainsDao#deleteChain(com.buckosoft.PicMan.domain.Chain)

          */

         public        void        deleteChain(Chain chain) throws DataAccessException {

                 ChainDelete sd = new ChainDelete(ds);

                 sd.delete(chain);

                 ChainSetSizesDelete cssd = new ChainSetSizesDelete(ds);

                 cssd.delete(chain);

                 chainsCache = null;

         }

         private        void        getChainSetSizes(Chain c) throws DataAccessException {

                 if (sql_getChainSetSizesQUERY == null)

                         sql_getChainSetSizesQUERY = new ChainSetSizesQuery(ds);

                 List<?> l = sql_getChainSetSizesQUERY.execute(c.getCid());

                 if (l.isEmpty())

                         return;

                 Iterator<?> iter = l.iterator();

                 while (iter.hasNext()) {

                         SetSize ss = (SetSize)iter.next();

                         c.addSetSize(ss);

                 }

         }

         private        ChainSetSizesQuery                        sql_getChainSetSizesQUERY;

         /* (non-Javadoc)

          * @see com.buckosoft.PicMan.db.ChainsDao#getChainCount()

          */

         public int        getChainCount() {

                 if (sql_getChainCountCOUNTr == null)

                         sql_getChainCountCOUNTr = new ChainCount(ds);

                 if (DEBUG)

                         logger.info("getChainCount:");

                 return(sql_getChainCountCOUNTr.run());

         }

         private ChainCount        sql_getChainCountCOUNTr;

         ///////////////////////////////////////////////////////////////////////////

         /**

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

          */

         class ChainsQuery extends MappingSqlQuery {

                 ChainsQuery(DataSource ds) {

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

             compile();

         }

                 ChainsQuery(DataSource ds, String name) {

             super(ds, "SELECT * from chains where name = ?");

                         declareParameter(new SqlParameter(Types.VARCHAR));

             compile();

         }

                 ChainsQuery(DataSource ds, int cid) {

             super(ds, "SELECT * from chains where cid = ?");

                         declareParameter(new SqlParameter(Types.INTEGER));

             compile();

         }

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

                 Chain c = new Chain();

                 c.setCid(rs.getInt("cid"));

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

                 c.setShortName(rs.getString("shortName"));

                 c.setDescription(rs.getString("description"));

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

                 c.setEngine(rs.getString("engine"));

                 c.setContactPrefix(rs.getString("contactPrefix"));

                 c.setContactDirectory(rs.getString("contactDirectory"));

                 c.setContactCount(rs.getInt("contactCount"));

                 c.setContactWidth(rs.getInt("contactWidth"));

                 c.setContactHeight(rs.getInt("contactHeight"));

                 c.setPicSelector(rs.getInt("picSelector"));

                 c.setRatingMin(rs.getInt("ratingMin"));

                 c.setRatingMax(rs.getInt("ratingMax"));

                 c.setShowUnusedSets(rs.getBoolean("showUnused"));

                 getChainSetSizes(c);

                 return(c);

         }

         }

         /**

          * <code>Chain</code> Count Object.

          */

         class ChainCount extends SqlFunction {

                 ChainCount(DataSource ds) {

                         super(ds, "SELECT COUNT(*) from chains");

                         compile();

                 }                

         }

         /**

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

          */

         protected class ChainUpdate extends SqlUpdate {

                 /**

                  * Create a new instance of ChainUpdate.

                  * @param ds the DataSource to use for the update

                  */

                 protected ChainUpdate(DataSource ds) {

                         super(ds, "UPDATE chains SET name=?, shortName=?, description=?, active=?, engine=?, "

                                         + "contactPrefix=?, contactDirectory=?, contactCount=?, contactWidth=?, contactHeight=?, "

                                         + "picSelector=?, ratingMin=?, ratingMax=?, showUnused=? WHERE cid = ? 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.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 /**

                  * Method to update a <code>Chain</code>'s data.

                  * @param c The Chain to update

                  * @return the number of rows affected by the update (better be 1)

                  */

                 protected int update(Chain c) {

                         return this.update(new Object[] {

                                         c.getName(), c.getShortName(),

                                         c.getDescription(), new Integer(c.isActive() ? 1 : 0),

                                         c.getEngine(), c.getContactPrefix(),

                                         c.getContactDirectory(), new Integer(c.getContactCount()),

                                         new Integer(c.getContactWidth()), new Integer(c.getContactHeight()),

                                         new Integer(c.getPicSelector()), new Integer(c.getRatingMin()), new Integer(c.getRatingMax()),

                                         new Integer(c.isShowUnusedSets() ? 1 : 0),

                                         new Integer(c.getCid()),

                         });

                 }

         }

         /**

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

          */

         protected class ChainInsert extends SqlUpdate {

                 /**

                  * Create a new instance of ChainInsert.

                  * @param ds the DataSource to use for the insert

                  */

                 protected ChainInsert(DataSource ds) {

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

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         declareParameter(new SqlParameter(Types.TINYINT));

                         compile();

                 }

                 protected void insert(Chain c) {

                         Object[] objs = new Object[] {

                                 new Integer(c.getCid()),

                                 c.getName(), c.getShortName(),

                                 c.getDescription(), new Integer(c.isActive() ? 1 : 0),

                                 c.getEngine(), c.getContactPrefix(),

                                 c.getContactDirectory(), new Integer(c.getContactCount()),

                                 new Integer(c.getContactWidth()), new Integer(c.getContactHeight()),

                                 new Integer(c.getPicSelector()), new Integer(c.getRatingMin()), new Integer(c.getRatingMax()),

                                 new Integer(c.isShowUnusedSets() ? 1 : 0),

                                 };

                         super.update(objs);

                 }

         }

         /**

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

          */

         protected class ChainDelete extends SqlUpdate {

                 /**

                  * Create a new instance of ChainDelete.

                  * @param ds the DataSource to use for the delete

                  */

                 protected ChainDelete(DataSource ds) {

                         super(ds, "DELETE FROM chains WHERE cid = (?) LIMIT 1");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 protected void delete(Chain c) {

                         Object[] objs = new Object[] {

                                         new Integer(c.getCid())};

                         super.update(objs);

                 }

         }

         /**

          * <code>Chain SetSize</code>s Query object.  Read the SetsSizes for a Chain.

          */

         class ChainSetSizesQuery extends MappingSqlQuery {

                 ChainSetSizesQuery(DataSource ds) {

                         super(ds, "SELECT * FROM chainsSetSizes WHERE cid = ?");

             declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

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

                         SetSize ss = new SetSize();

                         ss.setSetSize(rs.getString("setsSize"));

                         return(ss);

                 }

         }

         /**

          * <code>Chain.SetSizes</code> Insert Object.

          */

         protected class ChainSetSizesInsert extends SqlUpdate {

                 /**

                  * Create a new instance of ChainSetSizesInsert.

                  * @param ds the DataSource to use for the insert

                  */

                 protected ChainSetSizesInsert(DataSource ds) {

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

                         declareParameter(new SqlParameter(Types.INTEGER));

                         declareParameter(new SqlParameter(Types.VARCHAR));

                         compile();

                 }

                 protected void insert(int cid, String setSizeName) {

                         Object[] objs = new Object[] {

                                 new Integer(cid), setSizeName,

                                 };

                         super.update(objs);

                 }

         }

         /**

          * <code>Chain.SetSizes</code> Delete Object.

          */

         protected class ChainSetSizesDelete extends SqlUpdate {

                 /**

                  * Create a new instance of ChainDelete.

                  * @param ds the DataSource to use for the delete

                  */

                 protected ChainSetSizesDelete(DataSource ds) {

                         super(ds, "DELETE FROM chainsSetSizes WHERE cid = (?)");

                         declareParameter(new SqlParameter(Types.INTEGER));

                         compile();

                 }

                 protected void delete(Chain c) {

                         Object[] objs = new Object[] {

                                         new Integer(c.getCid())};

                         super.update(objs);

                 }

         }

 }