Repository: wangguanquan/eec
Pull request #387 (2024-08-09 00:27:33)

修复ResultSetSheet导出tinyint,smallint类型转换异常


Patch:
diff --git a/pom.xml b/pom.xml
index 9a7d0ab5..bdf0790c 100644
--- a/pom.xml
+++ b/pom.xml
@@ -5,7 +5,7 @@
 
   <groupId>org.ttzero</groupId>
   <artifactId>eec</artifactId>
-  <version>0.5.18-SNAPSHOT</version>
+  <version>0.5.17</version>
   <name>Excel Write/Read Framework</name>
   <packaging>jar</packaging>
   <url>https://www.ttzero.org</url>
diff --git a/src/main/java/org/ttzero/excel/entity/ResultSetSheet.java b/src/main/java/org/ttzero/excel/entity/ResultSetSheet.java
index bc1300a5..9de1849c 100644
--- a/src/main/java/org/ttzero/excel/entity/ResultSetSheet.java
+++ b/src/main/java/org/ttzero/excel/entity/ResultSetSheet.java
@@ -258,13 +258,12 @@ public class ResultSetSheet extends Sheet {
                     if (hc.ri > 0) {
                         switch (hc.sqlType) {
                             case VARCHAR:
+                            case CHAR:
                             case LONGVARCHAR:
-                            case NULL:           e = rs.getString(hc.ri);      break;
-                            case INTEGER:
+                            case NULL:            e = rs.getString(hc.ri);     break;
+                            case INTEGER:         e = rs.getInt(hc.ri);        break;
                             case TINYINT:
-                            case SMALLINT:
-                            case BIT:
-                            case CHAR:            e = rs.getInt(hc.ri);        break;
+                            case SMALLINT:        e = rs.getShort(hc.ri);      break;
                             case DATE:            e = rs.getDate(hc.ri);       break;
                             case TIMESTAMP:       e = rs.getTimestamp(hc.ri);  break;
                             case NUMERIC:
@@ -273,9 +272,12 @@ public class ResultSetSheet extends Sheet {
                             case REAL:
                             case FLOAT:
                             case DOUBLE:          e = rs.getDouble(hc.ri);     break;
+                            case BIT:             e = rs.getBoolean(hc.ri);    break;
                             case TIME:            e = rs.getTime(hc.ri);       break;
                             default:              e = rs.getObject(hc.ri);     break;
                         }
+                        // Clear value if NULL
+                        if (rs.wasNull()) e = null;
                     } else e = null;
 
                     cellValueAndStyle.reset(row, cell, e, hc);
@@ -386,18 +388,17 @@ public class ResultSetSheet extends Sheet {
             case LONGVARCHAR:
             case NULL:      clazz = String.class;        break;
             case INTEGER:   clazz = Integer.class;       break;
+            case TINYINT:
+            case SMALLINT:  clazz = Short.class;         break;
             case DATE:      clazz = java.sql.Date.class; break;
             case TIMESTAMP: clazz = Timestamp.class;     break;
             case NUMERIC:
             case DECIMAL:   clazz = BigDecimal.class;    break;
-            case BIT:       clazz = Boolean.class;       break;
-            case TINYINT:   clazz = Byte.class;          break;
-            case SMALLINT:  clazz = Short.class;         break;
             case BIGINT:    clazz = Long.class;          break;
-            case REAL:      clazz = Float.class;         break;
+            case REAL:
             case FLOAT:
             case DOUBLE:    clazz = Double.class;        break;
-//            case CHAR:      clazz = char.class;          break;
+            case BIT:       clazz = Boolean.class;       break;
             case TIME:      clazz = java.sql.Time.class; break;
             default:        clazz = Object.class;
         }


Test patch:
diff --git a/src/test/java/org/ttzero/excel/entity/ResultSetSheetTest.java b/src/test/java/org/ttzero/excel/entity/ResultSetSheetTest.java
index a06eb929..6146a985 100644
--- a/src/test/java/org/ttzero/excel/entity/ResultSetSheetTest.java
+++ b/src/test/java/org/ttzero/excel/entity/ResultSetSheetTest.java
@@ -659,5 +659,99 @@ public class ResultSetSheetTest extends SQLWorkbookTest {
         }
     }
 
+    @Test public void testTypes() throws SQLException, IOException {
+        try (
+            Connection con = getConnection();
+            PreparedStatement ps = con.prepareStatement("select * from types_test");
+            ResultSet rs = ps.executeQuery()
+        ) {
+            new Workbook()
+                .addSheet(new ResultSetSheet(rs))
+                .writeTo(defaultTestPath.resolve("test types.xlsx"));
+
+            PreparedStatement ps1 = con.prepareStatement("select * from types_test");
+            ResultSet rs1 = ps1.executeQuery();
+            try (ExcelReader reader = ExcelReader.read(defaultTestPath.resolve("test types.xlsx"))) {
+                org.ttzero.excel.reader.Sheet sheet = reader.sheet(0);
+                Iterator<Row> iter = sheet.iterator();
+                assertTrue(iter.hasNext());
+                org.ttzero.excel.reader.Row header = iter.next();
+                assertEquals("id", header.getString(0));
+                assertEquals("t_bit", header.getString(1));
+                assertEquals("t_tinyint", header.getString(2));
+                assertEquals("t_smallint", header.getString(3));
+                assertEquals("t_int", header.getString(4));
+                assertEquals("t_bigint", header.getString(5));
+                assertEquals("t_float", header.getString(6));
+                assertEquals("t_double", header.getString(7));
+                assertEquals("t_varchar", header.getString(8));
+                assertEquals("t_char", header.getString(9));
+                assertEquals("t_date", header.getString(10));
+                assertEquals("t_datetime", header.getString(11));
+                assertEquals("t_timestamp", header.getString(12));
+
+                assertTrue(iter.hasNext());
+                assertTrue(rs1.next());
+                // Row1
+                org.ttzero.excel.reader.Row row = iter.next();
+
+                assertEquals(row.getCellType(0), CellType.INTEGER);
+                assertEquals(row.getCellType(1), "mysql".equals(protocol) ? CellType.BOOLEAN : CellType.INTEGER);
+                assertEquals(row.getCellType(2), CellType.INTEGER);
+                assertEquals(row.getCellType(3), CellType.INTEGER);
+                assertEquals(row.getCellType(4), CellType.INTEGER);
+                assertEquals(row.getCellType(5), CellType.LONG);
+                assertEquals(row.getCellType(6), CellType.DECIMAL);
+                assertEquals(row.getCellType(7), CellType.DECIMAL);
+                assertEquals(row.getCellType(8), CellType.STRING);
+                assertEquals(row.getCellType(9), CellType.STRING);
+                assertEquals(row.getCellType(10), CellType.DATE);
+                assertEquals(row.getCellType(11), CellType.DATE);
+                assertEquals(row.getCellType(12), CellType.DATE);
+
+                assertEquals(rs1.getInt(1), (int) row.getInt(0));
+                if ("mysql".equals(protocol)) {
+                    assertEquals(rs1.getBoolean(2), row.getBoolean(1));
+                } else {
+                    assertEquals(rs1.getInt(2), (int) row.getInt(1));
+                }
+                assertEquals(rs1.getInt(3), (int) row.getInt(2));
+                assertEquals(rs1.getInt(4), (int) row.getInt(3));
+                assertEquals(rs1.getInt(5), (int) row.getInt(4));
+                assertEquals(rs1.getLong(6), (long) row.getLong(5));
+                assertTrue(rs1.getDouble(7) - row.getDouble(6) <= 0.0001);
+                assertTrue(rs1.getDouble(8) - row.getDouble(7) <= 0.0001);
+                assertEquals(rs1.getString(9), row.getString(8));
+                assertEquals(rs1.getString(10), row.getString(9));
+                assertEquals(rs1.getDate(11).getTime() / 1000, row.getDate(10).getTime() / 1000);
+                assertEquals(rs1.getDate(12).getTime() / 1000, row.getDate(11).getTime() / 1000);
+                assertEquals(rs1.getTimestamp(13).getTime() / 1000, row.getTimestamp(12).getTime() / 1000);
+
+                // Row2
+                assertTrue(iter.hasNext());
+                assertTrue(rs1.next());
+                // Row1
+                row = iter.next();
+
+                assertEquals(row.getCellType(0), CellType.INTEGER);
+                assertEquals(row.getCellType(1), CellType.BLANK);
+                assertEquals(row.getCellType(2), CellType.BLANK);
+                assertEquals(row.getCellType(3), CellType.BLANK);
+                assertEquals(row.getCellType(4), CellType.BLANK);
+                assertEquals(row.getCellType(5), CellType.BLANK);
+                assertEquals(row.getCellType(6), CellType.BLANK);
+                assertEquals(row.getCellType(7), CellType.BLANK);
+                assertEquals(row.getCellType(8), CellType.BLANK);
+                assertEquals(row.getCellType(9), CellType.BLANK);
+                assertEquals(row.getCellType(10), CellType.BLANK);
+                assertEquals(row.getCellType(11), CellType.BLANK);
+                assertEquals(row.getCellType(12), CellType.BLANK);
+
+                assertEquals(rs1.getInt(1), (int) row.getInt(0));
+            }
+            rs1.close();
+            ps1.close();
+        }
+    }
 
 }
diff --git a/src/test/java/org/ttzero/excel/entity/SQLWorkbookTest.java b/src/test/java/org/ttzero/excel/entity/SQLWorkbookTest.java
index 3cdf2ef2..dbf9685b 100644
--- a/src/test/java/org/ttzero/excel/entity/SQLWorkbookTest.java
+++ b/src/test/java/org/ttzero/excel/entity/SQLWorkbookTest.java
@@ -22,6 +22,7 @@ import org.ttzero.excel.util.StringUtil;
 
 import java.io.IOException;
 import java.sql.Connection;
+import java.sql.Date;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -35,7 +36,7 @@ import java.util.Properties;
  */
 public class SQLWorkbookTest extends WorkbookTest {
     private static final Properties pro;
-    private static String protocol;
+    protected static String protocol;
     static {
         pro = new Properties();
         try {
@@ -86,6 +87,7 @@ public class SQLWorkbookTest extends WorkbookTest {
             rs = ps.executeQuery();
             // No data in database
             if (!rs.next()) {
+                rs.close();
                 ps.close();
                 con.setAutoCommit(false);
                 ps = con.prepareStatement("insert into student(name, age, create_date, update_date) values (?,?,?,?)");
@@ -107,6 +109,45 @@ public class SQLWorkbookTest extends WorkbookTest {
                 }
                 ps.executeBatch();
                 con.commit();
+            } else {
+                rs.close();
+                ps.close();
+            }
+
+            String typesTest = "CREATE TABLE if not exists `types_test` (`id` int(11) NOT NULL,`t_bit` bit(1) DEFAULT NULL,`t_tinyint` tinyint(3) DEFAULT NULL,`t_smallint` smallint(6) DEFAULT NULL,`t_int` int(11) DEFAULT NULL,`t_bigint` bigint(19) DEFAULT NULL,`t_float` float DEFAULT NULL,`t_double` double DEFAULT NULL,`t_varchar` varchar(45) DEFAULT NULL,`t_char` char(10) DEFAULT NULL,`t_date` date DEFAULT NULL,`t_datetime` datetime DEFAULT NULL,`t_timestamp` timestamp NULL DEFAULT NULL)";
+            ps = con.prepareStatement(typesTest);
+            ps.executeUpdate();
+            ps.close();
+
+            ps = con.prepareStatement("select id from types_test limit 1");
+            rs = ps.executeQuery();
+            // No data in database
+            if (!rs.next()) {
+                rs.close();
+                ps.close();
+                con.setAutoCommit(true);
+                ps = con.prepareStatement("insert into types_test(id,t_bit,t_tinyint,t_smallint,t_int,t_bigint,t_float,t_double,t_varchar,t_char,t_date,t_datetime,t_timestamp) values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
+                ps.setInt(1, 1);
+                ps.setByte(2, (byte) 1);
+                ps.setByte(3, (byte) 123);
+                ps.setShort(4, (short) 1234);
+                ps.setInt(5, 12345678);
+                ps.setLong(6, 125436456543L);
+                ps.setFloat(7, 129.304F);
+                ps.setDouble(8, 47484.395D);
+                ps.setString(9, "abc");
+                ps.setString(10, "are");
+                ps.setDate(11, new Date(System.currentTimeMillis()));
+                ps.setDate(12, new Date(System.currentTimeMillis()));
+                ps.setTimestamp(13, new Timestamp(System.currentTimeMillis()));
+                ps.executeUpdate();
+                ps = con.prepareStatement("insert into types_test(id) values(?)");
+                ps.setInt(1, 2);
+                ps.executeUpdate();
+                ps.close();
+            } else {
+                rs.close();
+                ps.close();
             }
         } catch (SQLException e) {
             e.printStackTrace();