Home>

I want to execute the following Select statement in SpringBoot, but I could execute it in ObjectBrowser
If i write in the street, you will get an error and will not move.

Select * From schem.table1
(schema = schema name, table1 = table name)

How can I execute a SQL statement by specifying the schema name and table name? (Select * From table1 can be executed)

schema is not attached to @Table attached to entity class.
If possible, I would like to specify the schema in the Select statement.
If i know anyone, please give me a professor.
Also, if you have any questions that are not answered, please point out.

Source
@Override
@SuppressWarnings ("unchecked")
public List<enti>getData () {
  StringBuilder sql = new StringBuilder ();
  sql.append ("Select e From schema.enti e");// ← Select statement I want to execute
  Query query = manager.createQuery (sql.toString ());
  return query.getResultList ();
}


enti = entity class

Error message

org.hibernate.hql.internal.ast.QuerySyntaxException: schema.enti is not mapped

Supplemental information (FW/tool version etc.)

STS: 3.9.4.RELEASE
SpringBoot: 1.5.10.RELEASE
java: 1.8

  • Answer # 1

    I checked in my environment, but it seems that I can select without any problem.

    Verification environment

    Windows7

    Oracle 11g/XE

    Java8

    SpringBoot 1.5.14.RELEASE

    com.oracle:ojdbc6:11.2.0.3

    DDL
    CREATE TABLE "SAMPLE". "TEST" (
     ID NUMBER (19,0) NOT NULL ENABLE,
     NAME VARCHAR2 (255) NOT NULL ENABLE,
     PRIMARY KEY ("ID")
    );
    Entity class (Omitting Getter and Setter)
    @ Entity
    @Table (name = "test")
    public class TestModel {
      @GeneratedValue (strategy = GenerationType.AUTO, generator = "native")
      @GenericGenerator (name = "native", strategy = "native")
      @Column (name = "id", unique = true, nullable = false)
      @Id
      Long id;
      @Column (name = "name")
      String name;
    }
    Service class
    /** * /
    @Service
    public class TestService {
      @Autowired private EntityManager manager;
      public List<TestModel>getDataJPQL () {
        StringBuilder sql = new StringBuilder ();
        sql.append ("SELECT e From TestModel e");// ← JPQL statement to be executed
        return manager.createQuery (sql.toString (), TestModel.class) .getResultList ();
      }
      public List<TestModel>getDataNativeQuery () {
        StringBuilder sql = new StringBuilder ();
        sql.append ("select * from sample.test");// ← Select statement you want to execute
        return manager.createNativeQuery (sql.toString (), TestModel.class) .getResultList ();
      }
    }

    As you may know, in the case of CreateQuery, it is necessary to specify the entity name for the part corresponding to the table name, and the actual table name for createNativeQuery.

    If you specify the log (logging part) as shown below, you can output the SQL statement and parameters that are actually issued, so you should check for errors.

    application.yml
    spring:
     datasource:
       platform: Oracle11g
       driverClassName: oracle.jdbc.driver.OracleDriver
       url: jdbc: oracle: thin: @ 127.0.0.1: 1521/XE
       username: [username]
       password: [password]
     jpa:
       database-platform: org.hibernate.dialect.Oracle10gDialect
    logging:
      level:
        org.springframework: INFO
        org.hibernate.SQL: TRACE
        org.hibernate.type.descriptor.sql.BasicBinder: TRACE