CODEWITHSUNDEEP
postgresqljdbcjava-11
VasilyWRS
VasilyWRS

Reputation: 21

Calling a function on insert with JDBC

I have the following function and table in my PostgreSQL database:

CREATE OR REPLACE FUNCTION generate_uid(size INT) RETURNS TEXT AS $$
DECLARE
  characters TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  bytes BYTEA := gen_random_bytes(size);
  l INT := length(characters);
  i INT := 0;
  output TEXT := '';
BEGIN
  WHILE i < size LOOP
    output := output || substr(characters, get_byte(bytes, i) % l + 1, 1);
    i := i + 1;
  END LOOP;
  RETURN output;
END;
$$ LANGUAGE plpgsql VOLATILE;

create table users
(
    userid text primary key default generate_uid(50)
    , username varchar (50) not null
    , pass varchar (50) not null
    , firstname varchar (100) not null
    , lastname varchar (100) not null
    , email varchar (150) not null
    , roleid int not null
    , constraint fkrole foreign key(roleid) references userrole(roleid)
);

Then I call on the function in my DAO in JDBC with this block of code:

Account A = new Account();
        String sha256hex = Hashing.sha256()
                  .hashString(password, StandardCharsets.UTF_8)
                  .toString();
        try (Connection conn = CustomClassFactory.getConnection()) {

            String sql = "INSERT INTO users (username, pass, firstname, lastname, email, roleid) VALUES (?,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, userName);
            ps.setString(2, sha256hex);
            ps.setString(3, firstName);
            ps.setString(4, lastName);
            ps.setString(5, email);
            ps.setInt(6, roleId);

            System.out.println(ps.toString());
            int i = ps.executeUpdate(); // <---update not query. this line is what sends the information to the DB
            if (i == 0) {
                System.out.println("Sorry, database was not updated. Returning to menu");
                return null;
            }

        } catch (SQLException e) {
            System.out.println("Sorry, database was not contacted. Bring your developer coffee. In the Insert Statement");
            e.printStackTrace();
            return null;
        }

I am receiving the following error from the Stack Trace:

org.postgresql.util.PSQLException: ERROR: function gen_random_bytes(integer) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function generate_uid(integer) line 8 during statement block local variable initialization
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
    at dao.AccountDaoImp.CreateAccount(AccountDaoImp.java:35)
    at testing.Tester.main(Tester.java:11)
Exception in thread "main" java.lang.NullPointerException: Cannot invoke "models.Account.toString()" because the return value of "dao.AccountDaoImp.CreateAccount(String, String, String, String, String, int)" is null
    at testing.Tester.main(Tester.java:11)

How do I make sure it sees the function when I create a new user? The function is designed to generate a random string of text to use as a unique ID.

Upvotes: 0

Views: 491

Answers (1)

Aarati
Aarati

Reputation: 321

gen_random_bytes is part of the pgcrypto extension.

So run this in your database:

CREATE EXTENSION pgcrypto SCHEMA public;

To make sure you don't have to rely on search_path, you can prefix public to the function call, like in public.gen_random_uuid().

Upvotes: 2

Related Questions