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.ContentPart;
import com.cardence.lawshelf.model.ContentPartDao;
import com.cardence.lawshelf.model.Section;
public class JdbcContentPartDao extends AttributeEnabledJdbcTemplateDao
implements ContentPartDao {
public static final String TABLE_NAME = "content_part";
private static final String SQL_FIND = "select id, content, section_id, content_id, content_sequence, is_heading, is_notes, content_type, notes_type, format_type from content_part";
private static final String SQL_UPDATE = "update content_part set content = ?, section_id = ?, content_id = ?, content_sequence = ?, is_heading = ?, is_notes = ?, content_type = ?, notes_type = ?, format_type = ? where id = ?";
private static final String SQL_CREATE = "insert into content_part "
+ "(content, section_id, content_id, content_sequence, is_heading, is_notes, content_type, notes_type, format_type) "
+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
private static final class ContentMapper implements RowMapper<ContentPart> {
public ContentPart mapRow(ResultSet rs, int rowNum) throws SQLException {
ContentPart c = new ContentPart();
Section section = new Section();
section.setId(rs.getInt("section_id"));
c.setSection(section);
c.setSectionId(section.getId());
c.setContent(rs.getString("content"));
c.setContentSequence(rs.getInt("content_sequence"));
c.setContentType(rs.getString("content_type"));
c.setHeader(rs.getBoolean("is_heading"));
c.setNotes(rs.getBoolean("is_notes"));
c.setNotesType(rs.getString("notes_type"));
c.setContentId(rs.getInt("content_id"));
c.setFormatType(rs.getString("format_type"));
c.setId(rs.getInt("id"));
return c;
}
}
public ContentPart findContentPart(int id) {
return (ContentPart) this.getJdbcTemplate().queryForObject(
SQL_FIND + " where id = ?", new Object[] { id },
new ContentMapper());
}
public Collection<ContentPart> findContentPartForSection(int sectionId) {
return this.getJdbcTemplate().query(SQL_FIND + " where section_id = ?",
new Object[] { sectionId },
new int[] { java.sql.Types.INTEGER }, new ContentMapper());
}
public void updateContentPart(ContentPart content) {
this.getJdbcTemplate().update(SQL_UPDATE, new Object[] { //
content.getContent(), //
content.getSectionId(), //
content.getContentId(), //
content.getContentSequence(), //
content.isHeader(), //
content.isNotes(), //
content.getContentType(), //
content.getNotesType(),//
content.getFormatType(),//
content.getId() //
}, new int[] { //
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER,//
java.sql.Types.INTEGER,//
java.sql.Types.INTEGER,//
java.sql.Types.BOOLEAN,//
java.sql.Types.BOOLEAN,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER //
});
}
public int createContentPart(ContentPart content) {
try {
final String text = content.getContent();
final int sectionId = content.getSectionId();
final Integer contentId = content.getContentId();
final int sortKey = content.getContentSequence();
final boolean isHeader = content.isHeader();
final boolean isNotes = content.isNotes();
final String ctype = content.getContentType();
final String ntype = content.getNotesType();
final String ftype = content.getFormatType();
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.setString(1, text);
ps.setInt(2, sectionId);
if (contentId == null) {
ps.setNull(3, java.sql.Types.INTEGER);
} else {
ps.setInt(3, contentId);
}
ps.setInt(4, sortKey);
ps.setBoolean(5, isHeader);
ps.setBoolean(6, isNotes);
ps.setString(7, ctype);
ps.setString(8, ntype);
ps.setString(9, ftype);
return ps;
}
}, keyHolder);
int newID = keyHolder.getKey().intValue();
content.setId(newID);
return newID;
} catch (DataIntegrityViolationException e) {
System.out.println("Could not create content part: "
+ e.getLocalizedMessage());
System.out.println(content);
throw e;
}
}
@Override
protected String getDatabaseTableName() {
return TABLE_NAME;
}
public void deleteAllForSection(int sectionId) {
deleteRecord("section_id", sectionId);
}
public void deleteAllForCode(Integer codeId) {
try {
this.getJdbcTemplate().update(
"delete from " + TABLE_NAME + " c join "
+ JdbcSectionDao.TABLE_NAME
+ " s on s.id = c.section_id where s.code_id = ?",
new Object[] { //
codeId }, new int[] { java.sql.Types.INTEGER });
} catch (Throwable t) {
System.out
.println("Did not delete content part... possibly not there: "
+ t.getLocalizedMessage());
}
}
public void deleteAllAttributesForSection(int sectionId) {
try {
// delete from attribute a
// join content_part cp on cp.id = a.tablename
// where cp.section_id = ?
this.getJdbcTemplate().update(
"delete from attribute where tablename = '"
+ TABLE_NAME.toUpperCase()
+ "' and tablerow_id in (select id from "
+ TABLE_NAME + " where section_id = ?)",
new Object[] { //
sectionId }, new int[] { java.sql.Types.INTEGER })
;
} catch (Throwable t) {
System.out
.println("Did not delete attributes for content part... possibly not there: "
+ t.getLocalizedMessage());
}
}
}