java - 需要插入数据库表

Question

我在输入时遇到错误，因此避免错误并存储 postgre 数据库 table_sp 表

        package mypkg;

        import java.sql.CallableStatement;
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;
        import java.util.Scanner;

        public class PostgreSp {

            public static void main(String args[]) throws SQLException, ClassNotFoundException {
                Connection cn;
                Statement st;
                ResultSet rs;

                try {
                    Scanner in = new Scanner(System.in);

                    System.out.println("Enter a PK");
                    String pk = in.nextLine();
                    System.out.println("Enter a user");
                    String u = in.nextLine();
                    System.out.println("Enter a pass");
                    String ps = in.nextLine();
                    Class.forName("org.postgresql.Driver");
                    cn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/samples", "postgres", "analuo");

                    CallableStatement calstat = cn.prepareCall("{call new(?,?,?)}");
                    calstat.setString(1, pk);
                    calstat.setString(2, u);
                    calstat.setString(3, ps);
                    int i = calstat.executeUpdate();
                    cn.close();
                    calstat.close();
                    System.out.println("Your data has been inserted into table.");

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

存储程序:

   CREATE OR REPLACE FUNCTION new(pk character varying, u character varying,ps character varying)
      RETURNS SETOF record AS
    $BODY$
    BEGIN

    RAISE NOTICE 'PK is %','--'pk;
     EXECUTE 'INSERT INTO table_sp("pk_id","username","password")VALUES ('''||pk||''' ,'''||u||''','''||ps||''')' ;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100
      ROWS 1000;
    ALTER FUNCTION new(character varying, character varying,character varying)
      OWNER TO postgres;

示例输出:

run:
Enter a PK
1
Enter a user
admin
Enter a pass
admin
org.postgresql.util.PSQLException: ERROR: a column definition list is required for functions returning "record"
  Position: 15
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:330)
    at mypkg.PostgreSp.main(PostgreSp.java:37)
BUILD SUCCESSFUL (total time: 36 seconds)

我还附上了存储过程。如何避免错误并存储表格。

Answer 1

您已使用 RETURNS SETOF record 定义了该函数，但函数体不返回任何内容。查看您的 java 代码，您也不希望它返回任何内容。 (使用executeUpdate)

将函数更改为RETURNS VOID，以便 Postgres 知道不会返回任何内容。