diff --git a/apache-poi-3/pom.xml b/apache-poi-3/pom.xml index f306e9eec89e..7fb345a8febe 100644 --- a/apache-poi-3/pom.xml +++ b/apache-poi-3/pom.xml @@ -39,6 +39,11 @@ poi-ooxml-schemas ${poi-ooxml-schemas.version} + + com.h2database + h2 + ${h2.version} + org.apache.xmlbeans xmlbeans @@ -82,7 +87,8 @@ - 5.2.5 + 2.3.232 + 5.3.0 4.1.2 4.2.0 5.2.0 diff --git a/apache-poi-3/src/test/java/com/baeldung/resultset/DataPreparer.java b/apache-poi-3/src/test/java/com/baeldung/resultset/DataPreparer.java new file mode 100644 index 000000000000..2a7e60abdaa2 --- /dev/null +++ b/apache-poi-3/src/test/java/com/baeldung/resultset/DataPreparer.java @@ -0,0 +1,56 @@ +package com.baeldung.resultset; + +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Statement; + +public class DataPreparer { + + private static final String SELECT_SQL = "SELECT id, name, category, price FROM products "; + + private Connection connection = null; + + public DataPreparer(Connection connection) { + this.connection = connection; + } + + private void prepareTable() throws SQLException { + String createSql = "CREATE TABLE products (" + + "id INT AUTO_INCREMENT PRIMARY KEY, " + + "name VARCHAR(255) NOT NULL, " + + "category VARCHAR(255), " + + "price DECIMAL(10, 2) )"; + + try (Statement statement = connection.createStatement()) { + statement.execute(createSql); + } + } + + private void prepareData() throws SQLException { + String insertSql = "INSERT INTO products(name, category, price) " + + "VALUES ('%s', '%s', %s) "; + + try (Statement statement = connection.createStatement()) { + statement.executeUpdate(String.format(insertSql, "Chocolate", "Confectionery", "2.99")); + statement.executeUpdate(String.format(insertSql, "Fruit Jellies", "Confectionery", "1.5")); + statement.executeUpdate(String.format(insertSql, "Crisps", "Snacks", "1.69")); + statement.executeUpdate(String.format(insertSql, "Walnuts", "Snacks", "5.95")); + statement.executeUpdate(String.format(insertSql, "Orange Juice", "Juices", "2.19")); + } + } + + public void prepare() { + try { + prepareTable(); + prepareData(); + } + catch (SQLException se) { + se.printStackTrace(); + } + } + + public String getSelectSql() { + return SELECT_SQL; + } + +} diff --git a/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSet2Workbook.java b/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSet2Workbook.java new file mode 100644 index 000000000000..26d7fed6b52a --- /dev/null +++ b/apache-poi-3/src/test/java/com/baeldung/resultset/ResultSet2Workbook.java @@ -0,0 +1,52 @@ +package com.baeldung.resultset; + +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; + +import java.io.IOException; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; + +public class ResultSet2Workbook { + + private final ResultSet resultSet; + + private final ResultSetMetaData resultSetMetaData; + + private int numOfColumns = 0; + + public ResultSet2Workbook(ResultSet rs) throws SQLException { + this.resultSet = rs; + this.resultSetMetaData = rs.getMetaData(); + this.numOfColumns = resultSetMetaData.getColumnCount(); + } + + private void createHeaderRow(Sheet sheet) throws SQLException { + Row row = sheet.createRow(sheet.getLastRowNum()+1); + for (int n=0; n