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.CodeDao;
public class JdbcCodeDao extends AttributeEnabledJdbcTemplateDao implements
CodeDao {
public static final String TABLE_NAME = "code";
private static final String SQL_FIND = "select id, heading, short_heading, name, code_sequence, status, code_collection_id, heading_title from code";
private static final String SQL_UPDATE = "update code set heading = ?, short_heading = ?, name = ?, code_sequence = ?, status = ?, code_collection_id = ?, heading_title = ? where id = ?";
private static final String SQL_CREATE = "insert into code (heading, short_heading, name, code_sequence, status, code_collection_id, heading_title) values (?, ?, ?, ?, ?, ?, ?) ";
private static final class CodeMapper implements RowMapper<Code> {
public Code mapRow(ResultSet rs, int rowNum) throws SQLException {
Code code = new Code();
code.setHeading(rs.getString("heading"));
code.setShortHeading(rs.getString("short_heading"));
code.setName(rs.getString("name"));
code.setCodeSequence(rs.getInt("code_sequence"));
code.setStatus(rs.getString("status"));
code.setCodeCollectionId(rs.getInt("code_collection_id"));
code.setId(rs.getInt("id"));
code.setTitle(rs.getString("heading_title"));
return code;
}
}
public Code findCode(int id) {
return (Code) this.getJdbcTemplate().queryForObject(
SQL_FIND + " where id = ?", new Object[] { id },
new CodeMapper());
}
public Collection<Code> findCodeByCollection(Integer collectionId) {
return this.getJdbcTemplate().query(
SQL_FIND + " where code_collection_id = ?",
new Object[] { collectionId },
new int[] { java.sql.Types.INTEGER }, new CodeMapper());
}
public Collection<Code> findCodeByNameAndSequenceAndCollection(String name,
Integer seq, Integer id) {
return this
.getJdbcTemplate()
.query(SQL_FIND
+ " where name = ? and code_sequence = ? and code_collection_id = ? ",
new Object[] { name, seq, id },
new int[] { java.sql.Types.VARCHAR,
java.sql.Types.INTEGER, java.sql.Types.INTEGER },
new CodeMapper());
}
public void updateCode(Code code) {
this.getJdbcTemplate().update(SQL_UPDATE, new Object[] { //
code.getHeading(), //
code.getShortHeading(), //
code.getName(), //
code.getCodeSequence(), //
code.getStatus(), //
code.getCodeCollectionId(), //
code.getTitle(), //
code.getId() //
}, new int[] { //
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER,//
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER, //
java.sql.Types.VARCHAR,//
java.sql.Types.INTEGER //
});
}
public int createCode(Code code) {
try {
final String name = code.getName();
final String heading = code.getHeading();
final String shead = code.getShortHeading();
final String status = code.getStatus();
final int colId = code.getCodeCollectionId();
final int codeSeq = code.getCodeSequence();
final String headTitle = code.getTitle();
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, heading);
ps.setString(2, shead);
ps.setString(3, name);
ps.setInt(4, codeSeq);
ps.setString(5, status);
ps.setInt(6, colId);
ps.setString(7, headTitle);
return ps;
}
}, keyHolder);
int newID = keyHolder.getKey().intValue();
code.setId(newID);
return newID;
} catch (DataIntegrityViolationException e) {
System.out.println("Could not create code: "
+ e.getLocalizedMessage());
System.out.println(code);
throw e;
}
}
@Override
protected String getDatabaseTableName() {
return TABLE_NAME;
}
public void deleteCode(int codeId) {
deleteRecord(codeId);
}
}