Java tutorial
/* ================================================================== * PreparedStatementCsvReaderTests.java - 6/10/2016 1:13:32 PM * * Copyright 2007-2016 SolarNetwork.net Dev Team * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License as * published by the Free Software Foundation; either version 2 of * the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA * 02111-1307 USA * ================================================================== */ package net.solarnetwork.node.dao.jdbc.test; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNull; import java.io.IOException; import java.io.InputStreamReader; import java.io.Reader; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.LinkedHashMap; import java.util.Map; import java.util.TimeZone; import javax.annotation.Resource; import javax.sql.DataSource; import org.apache.commons.lang3.mutable.MutableInt; import org.junit.Before; import org.junit.Test; import org.springframework.dao.DataAccessException; import org.springframework.dao.DataAccessResourceFailureException; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.jdbc.core.ConnectionCallback; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowCallbackHandler; import org.supercsv.cellprocessor.ift.CellProcessor; import org.supercsv.prefs.CsvPreference; import net.solarnetwork.node.dao.jdbc.ColumnCsvMetaData; import net.solarnetwork.node.dao.jdbc.DatabaseSetup; import net.solarnetwork.node.dao.jdbc.JdbcUtils; import net.solarnetwork.node.dao.jdbc.PreparedStatementCsvReader; import net.solarnetwork.node.test.AbstractNodeTransactionalTest; /** * Test cases for the {@link PreparedStatementCsvReader} class. * * @author matt * @version 1.0 */ public class PreparedStatementCsvReaderTests extends AbstractNodeTransactionalTest { @Resource(name = "dataSource") private DataSource dataSource; private JdbcTemplate jdbcTemplate; @Before public void setup() { DatabaseSetup setup = new DatabaseSetup(); setup.setDataSource(dataSource); setup.init(); jdbcTemplate = new JdbcTemplate(dataSource); } private void importData(final String tableName) { final Map<String, ColumnCsvMetaData> columnMetaData = new LinkedHashMap<String, ColumnCsvMetaData>(8); jdbcTemplate.execute(new ConnectionCallback<Object>() { @Override public Object doInConnection(Connection con) throws SQLException, DataAccessException { columnMetaData.putAll(JdbcUtils.columnCsvMetaDataForDatabaseMetaData(con.getMetaData(), tableName)); String sql = JdbcUtils.insertSqlForColumnCsvMetaData(tableName, columnMetaData); PreparedStatement ps = con.prepareStatement(sql); Reader in; PreparedStatementCsvReader reader = null; try { in = new InputStreamReader(getClass().getResourceAsStream("csv-data-01.csv"), "UTF-8"); reader = new PreparedStatementCsvReader(in, CsvPreference.STANDARD_PREFERENCE); String[] header = reader.getHeader(true); Map<String, Integer> csvColumns = JdbcUtils.csvColumnIndexMapping(header); CellProcessor[] cellProcessors = JdbcUtils.parsingCellProcessorsForCsvColumns(header, columnMetaData); while (reader.read(ps, csvColumns, cellProcessors, columnMetaData)) { Savepoint sp = con.setSavepoint(); try { ps.executeUpdate(); } catch (SQLException e) { DataAccessException dae = jdbcTemplate.getExceptionTranslator().translate("Load CSV", sql, e); if (dae instanceof DataIntegrityViolationException) { con.rollback(sp); } else { throw e; } } } } catch (IOException e) { throw new DataAccessResourceFailureException("CSV encoding error", e); } finally { if (reader != null) { try { reader.close(); } catch (IOException e) { // ignore } } } return null; } }); } @Test public void importTable() throws Exception { final String tableName = "SOLARNODE.TEST_CSV_IO"; executeSqlScript("net/solarnetwork/node/dao/jdbc/test/csv-data-01.sql", false); importData(tableName); final MutableInt row = new MutableInt(0); final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"); sdf.setTimeZone(TimeZone.getTimeZone("UTC")); final Calendar utcCalendar = Calendar.getInstance(TimeZone.getTimeZone("UTC")); jdbcTemplate.query(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { // TODO Auto-generated method stub return con.prepareStatement("select PK,STR,INUM,DNUM,TS from solarnode.test_csv_io order by pk"); } }, new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { row.increment(); final int i = row.intValue(); assertEquals("PK " + i, i, rs.getLong(1)); if (i == 2) { assertNull("STR " + i, rs.getString(2)); } else { assertEquals("STR " + i, "s0" + i, rs.getString(2)); } if (i == 3) { assertNull("INUM " + i, rs.getObject(3)); } else { assertEquals("INUM " + i, i, rs.getInt(3)); } if (i == 4) { assertNull("DNUM " + i, rs.getObject(4)); } else { assertEquals("DNUM " + i, i, rs.getDouble(4), 0.01); } if (i == 5) { assertNull("TS " + i, rs.getObject(5)); } else { Timestamp ts = rs.getTimestamp(5, utcCalendar); try { assertEquals("TS " + i, sdf.parse("2016-10-0" + i + "T12:01:02.345Z"), ts); } catch (ParseException e) { // should not get here } } } }); assertEquals("Imported count", 5, row.intValue()); } @Test public void updateTable() throws Exception { final String tableName = "SOLARNODE.TEST_CSV_IO"; executeSqlScript("net/solarnetwork/node/dao/jdbc/test/csv-data-01.sql", false); importData(tableName); // verify the savepoint logic works to ignore inserts on data that already exists importData(tableName); } }