CODEWITHSUNDEEP
javasql-serverjdbcormspring-jdbc
Ravi Maurya
Ravi Maurya

Reputation: 329

Is it possible to set the schema in the JDBC connection string or any other generic way for SQL Server

I am trying to connect to sql server using by Java application. While connecting to H2 database, I appended schema=schema_name to the connection string in my java application and that worked.

However the same does not works for sqlserver.Connection is established but if my query does not contains the schema name prior to table name then query execution fails.

Please let me know of JDBC supports schema name in connection string or their is any other way.

Upvotes: 2

Views: 9075

Answers (2)

Ashish Ratan
Ashish Ratan

Reputation: 2870

Sure, It is possible if we are using hibernate or spring datasource ORM,

let’s break down the CustomPhysicalNamingStrategy class you’ve provided. This class is part of Hibernate’s API, specifically used for customizing how Hibernate maps entity names and database table names between Java and SQL.

Overview of CustomPhysicalNamingStrategy

The CustomPhysicalNamingStrategy class implements the PhysicalNamingStrategy interface from Hibernate. This interface allows you to define custom naming strategies for various database elements like tables, columns, sequences, and catalogs.

Here’s a detailed explanation of each part of the class:

Class Declaration

public class CustomPhysicalNamingStrategy implements PhysicalNamingStrategy {
  • CustomPhysicalNamingStrategy: This is the name of your custom naming strategy class.
  • implements PhysicalNamingStrategy: This means that your class must provide implementations for all the methods defined in the PhysicalNamingStrategy interface.

Constant Definition

private static final String SCHEMA = "job";
  • SCHEMA: A constant used to define the schema prefix that will be added to table and sequence names.

Method Implementations

  1. toPhysicalCatalogName

    @Override
public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment context) {
    return name;
}
    • This method converts a catalog name from the logical naming (used in your code) to the physical catalog name (used in the database).
    • In this implementation, it simply returns the name without modification, implying that catalog names are not customized.

  2. toPhysicalSchemaName

    @Override
public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment context) {
    return name;
}
    • Similar to toPhysicalCatalogName, this method converts the schema name from logical to physical form.
    • This implementation also returns the name as-is, indicating no custom schema name modifications.

  3. toPhysicalTableName

    @Override
public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
    return new Identifier(SCHEMA + "." + name.getText(), name.isQuoted());
}
    • This method converts a logical table name to its physical representation.
    • In this implementation, it prepends the schema prefix (SCHEMA) to the table name. For example, if the logical table name is Employee, it will be converted to job.Employee.
    • name.getText() retrieves the text of the logical table name, and name.isQuoted() maintains whether the original name was quoted.

  4. toPhysicalSequenceName

    @Override
public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment context) {
    return new Identifier(SCHEMA + "." + name.getText(), name.isQuoted());
}
    • This method is similar to toPhysicalTableName, but it deals with sequence names.
    • It also prepends the schema prefix to the sequence name, following the same logic.

  5. toPhysicalColumnName

    @Override
public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
    return name;
}
    • This method converts a logical column name to its physical representation.
    • In this implementation, column names are returned unchanged, meaning no custom naming strategy is applied to column names.

Summary

The CustomPhysicalNamingStrategy class customizes the physical naming of tables and sequences in the database by adding a schema prefix (job.) to their names. It leaves catalog names, schema names, and column names unchanged. This can be useful for ensuring that tables and sequences are organized under a specific schema in the database.

Upvotes: 0

Dragas
Dragas

Reputation: 1323

Sadly, that is not possible. What you might want to do is set default schema for that user instead.

create login foo with password = "superstronkpassword";
create user foober for login foo with default_schema = "my super schema";

Upvotes: 2

Related Questions