java - 在查询错误中将不同的数据类型插入postgreSQL

Question

我想向 PostgreSQL 数据库插入一条记录。它的大部分字段都是String，但也有java.sql.date和BIGINT。我怎样才能做到这一点？我的代码似乎不起作用。

    public Record(RecordFields data)
    {
        CreateNewRecord(data);
    }

        private void CreateNewRecord(RecordFields recordFields)
        {
            try
            {    

                    String addRecord = "INSERT INTO " + GlobalFields.TABLE + " VALUES (? , ? , ? , ? , ? , ? , ? , ? , ?);";

                    Connection conn = DataBase.Connect();

                    PreparedStatement query = conn.prepareStatement(addRecord);
                    query.setLong(1, recordFieldsID);
                    query.setString(2, recordFields.surname);
                    query.setString(3, recordFields.name);
                    query.setString(4, recordFields.sex);

                    DateTime date = recordFields.bornDate.toDateTime();
                    query.setTimestamp(5, new Timestamp(date.getMillis()));

                    query.setString(6, recordFields.adress);
                    query.setString(7, recordFields.contact);
                    query.setString(8, recordFields.insurance);
                    query.setString(9, recordFields.commentary);

                    query.executeUpdate(addRecord);
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }


    }    

表格生成器:

public static void CreateTable()
    {
        try
        {
            Connection conn = DataBase.Connect();

            try
            {
                Statement stat = conn.createStatement();

                String createTable = "CREATE TABLE " + GlobalFields.TABLE 
                    + "(id BIGINT PRIMARY KEY,"
                    + "surname TEXT,"
                    + "name TEXT,"
                    + "sex CHAR(1),"                 
                    + "bornDate DATE,"
                    + "adress TEXT,"
                    + "contact TEXT,"
                    + "insurance TEXT,"
                    + "commentary TEXT);";

                stat.executeUpdate(createTable);
            }
            finally
            {
                conn.close();
            }
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        } 
    } 

公共(public)类记录字段{ 公共(public)记录字段() { }

public long id;
public String surname;
public String name;
public String sex;
public MutableDateTime bornDate;
public String adress;
public String contact;
public String insurance;
public String commentary;

}

Answer 1

stat.executeUpdate(createTable);

是罪魁祸首。关于PreparedStatements executeUpdate takes no parameters .

因此，将该行修改为 - 只是 as expained by Tom Anderson

stat.executeUpdate();

你就完成了

另外，

INSERT table VALUES (values)

使您的代码变得不必要的脆弱。使用

INSERT INTO table (fieldlist) VALUES (valuelist)

相反。

还有2:

DateTime date = recordFields.bornDate.toDateTime();
query.setTimestamp(5, new Timestamp(data.getMillis()));

是一个相当复杂的说法

query.setTimestamp(5,new Timestamp(recordFields.bornDate.getMillis()));

编辑:关于Date in postgressql

Tom 所说的是，要填充 PostgreSQL DATE 类型列，您可以使用 java.sql.date 类型变量(而不是“常见”java.util.Date)，或javax.sql.TimeStamp。但这与当前问题完全无关(这是由调用executeUpdate 中的参数引起的)，您在这里所做的(使用 Timestamp 类型变量设置它)是可以的。