Skip to content

Specify Oracle user during DML operations #1099

Closed

Description

@mipo256

We have an Oracle database, and a table, which is owned by TERRABYTE user. The thing is that the connection to Oracle database is obtained not by the TERRABYTE user, but by another user. Therefore we have to explicitly specify not only the Table we want to insert to, but also the owner, like:

INSERT TERRABYTE.PARKING_PLACE(......

instead of simply doing

INSERT PARKING_PLACE(....

Is there a way to do it in spring-data-jdbc? Seems @Table does not provide such functionality.

Activity

  1. schauder commented on Nov 24, 2021

    @schauder
    Contributor

    You can register a custom NamingStrategy which implements getSchema to return the schema name.

  2. mipo256 commented on Nov 24, 2021

    @mipo256
    ContributorAuthor

    Ok that's great, but I there is a problem. I mean, it does not work along with @Table annotation, consider the example:

    @Data
    @Table("PARKING_PLACE")
    public class ParkingPlace {
    
        @Id
        @Column("Id")
        private long id;
    
        @Column("location_code")
        private int locationCode;
    
        @Column("occupier_id")
        private int occupierId;
    
        @Column("purchase")
        private LocalDateTime purchaseDate;
    }
    

    And also, if we register the bean like this:

        @Bean
        NamingStrategy namingStrategy() {
            return new NamingStrategy() {
                @Override
                public String getSchema() {
                    return "TERRABYTE";
                }
            };
        }
    

    The generated query will look like the following:

    Executing prepared SQL statement [INSERT INTO "PARKING_PLACE" ("location_code", "occupier_id", "purchase") VALUES (?, ?, ?)]

    Whereas if I remove @Table with qualified name, the generated query includes the schema (Oracle user in our case):

    Executing prepared SQL statement [INSERT INTO "TERRABYTE"."PARKING_PLACE" ("location_code", "occupier_id", "purchase") VALUES (?, ?, ?)]

    But I cannot remove the @Table in my case, nor I can rename the entity. I assume that this is a bug

  3. schauder commented on Nov 24, 2021

    @schauder
    Contributor

    You are correct, this is a bug.

    We should
    a) allow to specify a schema in the @Table annotation
    b) if none is given, use the schema returned by the NamingStrategy with the name given via @Table.

  4. mipo256 commented on Nov 24, 2021

    @mipo256
    ContributorAuthor

    I think yes, this would be a great improvement. I can take a loot at it. Can you please take a loot at #1047. It will be great to merge it in near future also. Thank you in advance.

  5. added a commit that references this issue on Dec 7, 2021
  6. added a commit that references this issue on Apr 1, 2024
    d12146d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions