package com.cardence.lawshelf.model.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import com.cardence.lawshelf.model.CodeCollection;
import com.cardence.lawshelf.model.CodeCollectionDao;
public class JdbcCodeCollectionDao extends AttributeEnabledJdbcTemplateDao
implements CodeCollectionDao {
public static final String TABLE_NAME = "code_collection";
private static final String SQL_FIND = "select id, year, heading, name, isfederal, statecode from code_collection";
private static final String SQL_UPDATE = "update code_collection set year = ?, heading = ?, name = ?, isfederal = ?, statecode = ? where id = ?";
private static final String SQL_CREATE = "insert into code_collection (year, heading, name, isfederal, statecode) values (?, ?, ?, ?, ?) ";
private static final class CodeCollectionMapper implements
RowMapper<CodeCollection> {
public CodeCollection mapRow(ResultSet rs, int rowNum)
throws SQLException {
CodeCollection series = new CodeCollection();
series.setName(rs.getString("name"));
series.setYear(rs.getInt("year"));
series.setFederal(rs.getBoolean("isFederal"));
series.setStateCode(rs.getString("statecode"));
series.setHeading(rs.getString("heading"));
series.setId(rs.getInt("id"));
return series;
}
}
public CodeCollection findCodeCollection(int id) {
return (CodeCollection) this.getJdbcTemplate().queryForObject(
SQL_FIND + " where id = ?", new Object[] { id },
new CodeCollectionMapper());
}
public Collection<CodeCollection> findCodeCollectionsByYear(Integer year) {
return this.getJdbcTemplate().query(SQL_FIND + " where year = ?",
new Object[] { year }, new int[] { java.sql.Types.INTEGER },
new CodeCollectionMapper());
}
public Collection<CodeCollection> findAllCodeCollections() {
return this.getJdbcTemplate().query(SQL_FIND, new Object[] {},
new int[] {}, new CodeCollectionMapper());
}
public Collection<CodeCollection> findCodeCollectionsByNameAndYear(
String name, Integer year) {
return this.getJdbcTemplate().query(
SQL_FIND + " where year = ? and name = ?",
new Object[] { year, name },
new int[] { java.sql.Types.INTEGER, java.sql.Types.VARCHAR },
new CodeCollectionMapper());
}
public Collection<CodeCollection> findCodeCollectionsByState(
String statecode) {
return this.getJdbcTemplate().query(SQL_FIND + " where statecode = ?",
new Object[] { statecode },
new int[] { java.sql.Types.VARCHAR },
new CodeCollectionMapper());
}
public Collection<CodeCollection> findCodeCollectionsForFederal() {
return this.getJdbcTemplate().query(SQL_FIND + " where isFederal = 1",
new Object[] {}, new int[] {}, new CodeCollectionMapper());
}
public void updateCodeCollection(CodeCollection codeCollection) {
this.getJdbcTemplate().update(SQL_UPDATE, new Object[] { //
codeCollection.getYear(), //
codeCollection.getHeading(),//
codeCollection.getName(),//
codeCollection.isFederal(),//
codeCollection.getStateCode(),//
codeCollection.getId() //
}, new int[] { //
java.sql.Types.INTEGER,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.BOOLEAN,//
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER //
});
}
public int createCodeCollection(CodeCollection codeCollection) {
final String heading = codeCollection.getHeading();
final String name = codeCollection.getName();
final String scode = codeCollection.getStateCode();
final boolean isFed = codeCollection.isFederal();
final int year = codeCollection.getYear();
KeyHolder keyHolder = new GeneratedKeyHolder();
this.getJdbcTemplate().update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(
Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(SQL_CREATE,
new String[] { "id" });
ps.setInt(1, year);
ps.setString(2, heading);
ps.setString(3, name);
ps.setBoolean(4, isFed);
ps.setString(5, scode);
return ps;
}
}, keyHolder);
int newID = keyHolder.getKey().intValue();
codeCollection.setId(newID);
return newID;
}
@Override
protected String getDatabaseTableName() {
return TABLE_NAME;
}
}