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.dao.DataIntegrityViolationException;
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.Code;
import com.cardence.lawshelf.model.Section;
import com.cardence.lawshelf.model.SectionDao;
public class JdbcSectionDao extends AttributeEnabledJdbcTemplateDao implements
SectionDao {
public static final String TABLE_NAME = "section";
private static final String SQL_FIND_SECTION = "select id, parent_section_id, code_id, heading, short_heading, source_reference, section_sequence, level_type, level_position, heading_text from section";
private static final String SQL_UPDATE_SECTION = "update section set parent_section_id = ?, code_id = ?, heading = ?, short_heading = ?, source_reference = ?, section_sequence = ?, level_type = ?, level_position = ?, heading_text = ? where id = ?";
private static final String SQL_CREATE_SECTION = "insert into section (parent_section_id, code_id, heading, short_heading, source_reference, section_sequence, level_type, level_position, heading_text) values (?, ?, ?, ?, ?, ?, ?, ?, ?) ";
private static final class SectionMapper implements RowMapper<Section> {
public Section mapRow(ResultSet rs, int rowNum) throws SQLException {
Section section = new Section();
Section parent = new Section();
parent.setId(rs.getInt("parent_section_id"));
section.setParentSectionId(parent.getId());
section.setParent(parent);
Code code = new Code();
code.setId(rs.getInt("code_id"));
section.setCode(code);
section.setCodeId(code.getId());
section.setId(rs.getInt("id"));
section.setSourceReference(rs.getString("source_reference"));
section.setSectionSequence(rs.getInt("section_sequence"));
section.setShortHeading(rs.getString("short_heading"));
section.setHeading(rs.getString("heading"));
section.setLevelType(rs.getString("level_type"));
section.setLevelPosition(rs.getString("level_position"));
section.setTextHeading(rs.getString("heading_text"));
return section;
}
}
public Section findSection(int id) {
return (Section) this.getJdbcTemplate().queryForObject(
SQL_FIND_SECTION + " where id = ?", new Object[] { id },
new SectionMapper());
}
public Section findSectionByCodeAndReference(int codeId, String ref) {
try {
return (Section) this.getJdbcTemplate().queryForObject(
SQL_FIND_SECTION
+ " where code_id = ? and source_reference = ?",
new Object[] { codeId, ref }, new SectionMapper());
} catch (Throwable t) {
System.out
.println("No Results for Query (findSectionByCodeAndReference) with codeId ["
+ codeId + "] and reference [" + ref + "]");
return null;
}
}
public Section findSectionByLevelTypeAndPosition(String levelType,
String levelPosition) {
return (Section) this.getJdbcTemplate().queryForObject(
SQL_FIND_SECTION
+ " where level_type = ? and level_position = ?",
new Object[] { levelType, levelPosition }, new SectionMapper());
}
public Collection<Section> findSectionsByParent(int parentId) {
return this.getJdbcTemplate().query(
SQL_FIND_SECTION + " where parent_section_id = ?",
new Object[] { parentId },
new int[] { java.sql.Types.INTEGER }, new SectionMapper());
}
public Collection<Section> findSectionByReference(String ref) {
return this.getJdbcTemplate().query(
SQL_FIND_SECTION + " where source_reference = ?",
new Object[] { ref }, new int[] { java.sql.Types.VARCHAR },
new SectionMapper());
}
public Collection<Section> findTopLevelSectionByCode(int codeId) {
return this.getJdbcTemplate().query(
SQL_FIND_SECTION
+ " where parent_section_id IS NULL and code_id = ?",
new Object[] { codeId }, new int[] { java.sql.Types.INTEGER },
new SectionMapper());
}
public Collection<Section> findSectionByCode(int codeId) {
return this.getJdbcTemplate().query(
SQL_FIND_SECTION + " where code_id = ?",
new Object[] { codeId }, new int[] { java.sql.Types.INTEGER },
new SectionMapper());
}
public void updateSection(Section section) {
// parent_section_id, code_id, heading, full_reference,
// section_sequence, level_type, level_position
this.getJdbcTemplate().update(SQL_UPDATE_SECTION, new Object[] { //
section.getParentSectionId(), //
section.getCodeId(), //
section.getHeading(), //
section.getShortHeading(), //
section.getSourceReference(), //
section.getSectionSequence(), //
section.getLevelType(), //
section.getLevelPosition(), //
section.getTextHeading(), //
section.getId() //
}, new int[] { //
java.sql.Types.INTEGER,//
java.sql.Types.INTEGER,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER //
});
}
public int createSection(Section section) {
try {
// parent_section_id, code_id, heading, full_reference,
// section_sequence, level_type, level_position
final String leveltype = section.getLevelType();
final String levelpos = section.getLevelPosition();
final String ref = section.getSourceReference();
final Integer parentId = section.getParentSectionId();
final Integer codeId = section.getCodeId();
final String shead = section.getShortHeading();
final String heading = section.getHeading();
final int sequence = section.getSectionSequence();
final String headingText = section.getTextHeading();
KeyHolder keyHolder = new GeneratedKeyHolder();
this.getJdbcTemplate().update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(
Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(
SQL_CREATE_SECTION, new String[] { "id" });
if (parentId == null) {
ps.setNull(1, java.sql.Types.INTEGER);
} else {
ps.setInt(1, parentId);
}
if (codeId == null) {
ps.setNull(2, java.sql.Types.INTEGER);
} else {
ps.setInt(2, codeId);
}
ps.setString(3, heading);
ps.setString(4, shead);
ps.setString(5, ref);
ps.setInt(6, sequence);
ps.setString(7, leveltype);
ps.setString(8, levelpos);
ps.setString(9, headingText);
return ps;
}
}, keyHolder);
int newID = keyHolder.getKey().intValue();
section.setId(newID);
return newID;
} catch (DataIntegrityViolationException e) {
System.out.println("Could not create section: "
+ e.getLocalizedMessage());
System.out.println(section);
throw e;
}
}
@Override
protected String getDatabaseTableName() {
return TABLE_NAME;
}
public void deleteAllForCode(Integer codeId) {
deleteRecord("code_id", codeId);
}
}