D-Net

import org.apache.commons.codec.digest.DigestUtils;

import java.io.ByteArrayInputStream;

import java.io.IOException;

    // TODO move to dao if needed

    @Autowired

    private DataSource dataSource;

    private static final String INSERT_INVOICE = "insert into invoice (number, alternativeid, date, source, id) values (?, ?, ?, ?, ?)";

    private static final String UPDATE_INVOICE = "update invoice set number=?, alternativeid=?, date=?, source=? where id = ?";

    private static final String GET_INVOICE = "select id, alternativeid, number, date, source from invoice where id = ?";

    private static final String GET_INVOICE_FILE = "select invoice, mimetype, file from file where invoice = ?";

    private static final String INSERT_INVOICE_FILE = "insert into file (mimetype, file, invoice) values (?, ?, ?)";

    private static final String UPDATE_INVOICE_FILE = "update file set mimetype=?, file=? where invoice=?";

    @Override

    public Invoice saveInvoice(Invoice invoice) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        if (invoice.getId() == null) {

            invoice.setSource("portal");

            invoice.setId("portal::" + UUID.randomUUID().toString());

        }

        if (jdbcTemplate.update(UPDATE_INVOICE, new Object[] {invoice.getNumber(), invoice.getAlternativeId(), invoice.getDate(), invoice.getSource(), invoice.getId()},

                new int[] {Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR}) == 0) {

            jdbcTemplate.update(INSERT_INVOICE, new Object[] {invoice.getNumber(), invoice.getAlternativeId(), invoice.getDate(), invoice.getSource(), invoice.getId()},

                    new int[] {Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});

        }

        return invoice;

    }

    @Override

    public Invoice getInvoice(String invoiceId) throws ManagerException {

        try {

            return new JdbcTemplate(dataSource).queryForObject(GET_INVOICE, new String[]{invoiceId}, new int[]{Types.VARCHAR}, new RowMapper<Invoice>() {

                @Override

                public Invoice mapRow(ResultSet rs, int rowNum) throws SQLException {

                    Invoice invoice = new Invoice();

                    invoice.setId(rs.getString("id"));

                    invoice.setAlternativeId(rs.getString("alternativeid"));

                    invoice.setNumber(rs.getString("number"));

                    invoice.setDate(rs.getTimestamp("date"));

                    invoice.setSource(rs.getString("source"));

                    return invoice;

                }

            });

        } catch (EmptyResultDataAccessException e) {

            throw new ManagerException(ManagerException.ErrorCause.NOT_EXISTS);

        }

    }

    @Override

    public void uploadInvoice(final String invoiceId, final String mimetype, InputStream invoice) throws ManagerException {

        try {

            final ByteArrayOutputStream baos = new ByteArrayOutputStream();

            final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            final PreparedStatementSetter pss = new PreparedStatementSetter() {

                @Override

                public void setValues(PreparedStatement ps) throws SQLException {

                    ps.setString(1, mimetype);

                    ps.setBytes(2, baos.toByteArray());

                    ps.setString(3, invoiceId);

                }

            };

            IOUtils.copy(invoice, baos);

            IOUtils.closeQuietly(baos);

            if (jdbcTemplate.update(UPDATE_INVOICE_FILE, pss) == 0) {

                jdbcTemplate.update(INSERT_INVOICE_FILE, pss);

            }

        } catch (Exception e) {

            throw new ManagerException(ManagerException.ErrorCause.UNKNOWN);

        }

    }

    @Override

    public InvoiceFile downloadInvoice(String invoiceId) throws ManagerException {

        try {

            return new JdbcTemplate(dataSource).queryForObject(GET_INVOICE_FILE, new String[]{invoiceId}, new int[]{Types.VARCHAR}, new RowMapper<InvoiceFile>() {

                @Override

                public InvoiceFile mapRow(ResultSet rs, int rowNum) throws SQLException {

                    return new InvoiceFile(rs.getString("invoice"), rs.getString("mimetype"), rs.getBytes("file"));

                }

            });

        } catch (EmptyResultDataAccessException e) {

            throw new ManagerException(ManagerException.ErrorCause.NOT_EXISTS);

        } catch (Exception e) {

            throw new ManagerException(ManagerException.ErrorCause.UNKNOWN);

        }

    }

    public DataSource getDataSource() {

        return dataSource;

    }

    public void setDataSource(DataSource dataSource) {

        this.dataSource = dataSource;

    }