java - org.postgresql.util.PSQLException : ERROR: syntax error at or near "("

Question

我创建了一个 Web 应用程序，可以通过 maven eclipse 部署在 Heroku 上。

组 ID:org.glassfish.jersey.archetypes

工件 ID:jersey-heroku-webapp

版本:2.17

我在本地主机和 POSTMAN 上测试了该应用程序，它工作正常。我将其推送到 heroku 以在 servlet 容器上测试它，但我收到 520 OK 520 它只是我在 SQLEXCEPTION 中返回的一个数字。在 Heroku 日志中我发现了这个错误:

2015-05-13T13:10:37.364388+00:00 app[web.1]:    at java.lang.Thread.run(Thread.j
ava:745)
2015-05-13T13:10:37.389547+00:00 app[web.1]: org.postgresql.util.PSQLException:
ERROR: syntax error at or near "("
2015-05-13T13:10:37.389560+00:00 app[web.1]:   Position: 45
2015-05-13T13:10:37.389740+00:00 app[web.1]:    at org.postgresql.core.v3.QueryE
xecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)

数据库类:

public class Database {


    public Database() {

    }

    public void drivercConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("jar works :) ");

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static Connection getConnection() throws URISyntaxException, SQLException {

        URI dbUri = new URI(System.getenv("DATABASE_URL"));
        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':'
                + dbUri.getPort() + dbUri.getPath();

        Connection con = DriverManager.getConnection(dbUrl, username, password);
        return con;
    }

    public int insertData(String mac, int route, double latD, double longD) {
        int status = 201;

        drivercConnection();

        try {
            Connection con = null;
            try {
                con = getConnection();
            } catch (URISyntaxException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


            // Create a statement
            Statement stt = con.createStatement();

            DatabaseMetaData dbm = con.getMetaData();

            ResultSet tables = dbm.getTables(null, null, "bus", null);

            if (tables.next()) {
                // stt.execute("ALTER TABLE bus AUTO_INCREMENT = 1");

                return insertRecord(mac, route, latD, longD, status, con);

            } else {
                // Create bus table
                stt.execute("CREATE TABLE IF NOT EXISTS bus"
                        + "(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                        + "mac VARCHAR(30) NOT NULL UNIQUE,"
                        + "route int(11) NOT NULL,"
                        + "latitude FLOAT(10,6) NOT NULL,"
                        + "longitude FLOAT(10,6) NOT NULL,"
                        + "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

                stt.execute("CREATE EVENT IF NOT EXISTS  AutoDelete "
                        + "ON SCHEDULE EVERY 3 MINUTE "
                        + "DO "
                        + "DELETE FROM bus WHERE created_at < (NOW() - INTERVAL 3 MINUTE)");

                stt.execute("SET GLOBAL event_scheduler = ON");

                first_data_insert(mac, route, latD, longD, con);

            }
            return status;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return status = 520;

        }
    }

Answer 1

表明sql查询不正确。你可能想把它改成这样。

DROP TABLE IF EXISTS bus;

CREATE TABLE bus( 
id SERIAL PRIMARY KEY,
mac VARCHAR(30) NOT NULL UNIQUE,
route int NOT NULL,
latitude numeric(10,6) NOT NULL,
longitude numeric(10,6) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

请注意，create 语句 (afaik) 不是 sql 标准命令。因此，因为您使用的是 postgresql，所以需要将其更改为 postgresql create 语句。