java - 将数据插入DB ERROR: 'VARCHAR'类型的列不能保存 'INTEGER'类型的值

Question

我已经建立了一个数据库，现在我正在编写一个将数据插入该数据库的函数。
我想这个问题是我看不到的，我得到的错误是:

Columns of type 'VARCHAR' cannot hold values of type 'INTEGER'.

虽然我完全理解那意味着我无法正常工作。

这是我的插入代码:

public static void insertIntoCouponsDB(long COMPANY_ID, String TITLE, String START_DATE, String END_DATE, int AMOUNT, String TYPE, String MESSAGE, double PRICE, String IMAGE) throws SQLException {

    Connection connection = DriverManager.getConnection(connectionString);

    String sql = String.format("insert into Coupons (COMPANY_ID, TITLE, START_DATE,END_DATE,AMOUNT,TYPE,MESSAGE,PRICE,IMAGE) values (%d, '%s', '%s','%s',%d,'%s','%s',%.2f,'%s')",COMPANY_ID,TITLE,START_DATE,END_DATE,AMOUNT,TYPE,MESSAGE,PRICE,IMAGE);

    PreparedStatement preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

    preparedStatement.executeUpdate();

    ResultSet resultSet = preparedStatement.getGeneratedKeys();

    resultSet.next();

    int id = resultSet.getInt(1);

    System.out.println("Insertion into Coupons DONE !!! New ID: " + id);

}

}

这是创建tabke的代码:

public static void buildCouponsDB() {

    try {
        Connection connection = DriverManager.getConnection(connectionString);
        Statement statement = connection.createStatement();



        String sql = "create table Coupons (" +
                "ID bigint not null primary key " + 
                "generated always as identity(start with 1, increment by 1), "+
                "COMPANY_ID bigint not null, "+
                "TITLE varchar(50) not null, "+
                "START_DATE date not null, "+
                "END_DATE date not null, "+
                "AMOUNT integer not null, "+
                "TYPE varchar(50) not null, "+
                "MESSAGE varchar(250) not null, "+
                "PRICE double not null, "+
                "IMAGE varchar(100) not null)";


        statement.executeUpdate(sql);
        System.out.println("Coupons Table has been Created Succesfully !!");

    } catch (Exception ex) {
        System.out.println(ex.getMessage());
    }

}

有人可以帮忙吗？
我是一个初学者，因此很容易提出问题，但仍然寻求帮助。提前致谢。

Answer 1

似乎DerbyDB不像许多(大多数？)其他数据库那样，支持从整数到varchar数据类型的隐式转换。
快速浏览the documentation并没有提供有关隐式转换的任何信息。

有CAST函数CAST ( [ expression | NULL | ? ] AS dataType )，但是从文档表中可以看出，不支持所有数字数据类型(SMALLINT，INTEGER，BIGINT，DECIMAL等)的转换。
幸运的是，thete是另一个[CHAR(https://db.apache.org/derby/docs/10.14/ref/rrefbuiltchar.html)函数，似乎可以使用该函数将数值转换为varchar datatye:

Integer to character syntax

CHAR ( integerExpression )

integerExpression

An expression that returns a value that is an integer data type (either SMALLINT, INTEGER, or BIGINT). The result is the character string representation of the argument in the form of an SQL integer constant. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. The result is left justified.

• If the first argument is a SMALLINT: The length of the result is 6. If the number of characters in the result is less than 6, then the result is padded on the right with blanks to length 6.
• If the first argument is an INTEGER: The length of the result is 11. If the number of characters in the result is less than 11, then the result is padded on the right with blanks to length 11.
• If the first argument is a BIGINT: The length of the result is 20. If the number of characters in the result is less than 20, then the result is padded on the right with blanks to length 20.

因此，您必须使用CHAR函数将数字转换为VARCHAR数据类型。

我使用 ij derby客户端进行的快速测试表明上述内容是正确的:

ij> connect 'jdbc:derby://localhost:1527/myDB;create=true'
> ;
ij> create table x( x integer, y varchar(20) );
0 wierszy wstawionych/zaktualizowanych/usuniŕtych

ij> insert into x values( 1,1);
BúąD 42821: Kolumny typu 'VARCHAR' nie mog╣ przechowywaŠ wartoťci typu 'INTEGER'.

ij> insert into x values( 1, cast(1 as varchar));
BúąD 42X01: B│╣d sk│adniowy: Encountered ")" at line 1, column 43.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.

ij> insert into x values( 1, char(1));
1 wiersz wstawiony/zaktualizowany/usuniŕty
ij> commit;
ij> select * from x;
X          |Y
--------------------------------
1          |1

1 wiersz wybrany
ij>

编辑

似乎 CAST( 1 AS CHAR )也可以使用，我做了一个测试:

ij> insert into x values( 1, cast(1 as char));
1 wiersz wstawiony/zaktualizowany/usuniŕty
ij>