Connecting Hibernate to Oracle Database

[k1ll1n]

I'm trying to connect Hibernate to Oracle database. My config

db.url = "jdbc:oracle:thin:@domain.com:1521:PHX"
db.user = USER
db.password = "PASSWORD"

hikari.autoCommit=true
hikari.maximumPoolSize=20

My App

public class App extends Jooby {

  {
    install(new NettyServer());
    install(new GuiceModule());

    mvc(new TestController());

    install(new HikariModule());
    install(new HibernateModule());
  }

  public static void main(final String[] args) {
    runApp(args, App::new);
  }
}

My Controller

@Path("/")
public class TestController {
    @GET
    public String hello(Context ctx) {

        EntityManager em = ctx.require(EntityManager.class);
        EntityTransaction trx = em.getTransaction();

        List<Test> test = em.createQuery("from Test", Test.class).setMaxResults(1).getResultList();
        System.out.println(test);

        return "Hello World!";
    }
}

My log

22:47:25: Executing 'joobyRun'...

[2024-06-03 22:47:25,762]-[jooby-shutdown] INFO  com.zaxxer.hikari.HikariDataSource - oracle.PHX - Shutdown completed.
[2024-06-03 22:47:25,762]-[jooby-shutdown] INFO  ru.phoenixit.hathi.admin.App - Stopped App
> Task :compileJava
> Task :processResources UP-TO-DATE
> Task :classes

> Task :joobyRun
[2024-06-03 22:47:26,598]-[Execution worker Thread 2] INFO  org.reflections.Reflections - Reflections took 16 ms to scan 1 urls, producing 10 keys and 10 values
[2024-06-03 22:47:26,632]-[Execution worker Thread 2] INFO  com.zaxxer.hikari.HikariDataSource - oracle.PHX - Starting...
[2024-06-03 22:47:26,914]-[Execution worker Thread 2] INFO  com.zaxxer.hikari.pool.PoolBase - oracle.PHX - Driver does not support get/set network timeout for connections. (Receiver class oracle.jdbc.driver.T4CConnection does not define or inherit an implementation of the resolved method 'abstract int getNetworkTimeout()' of interface java.sql.Connection.)
[2024-06-03 22:47:26,928]-[Execution worker Thread 2] INFO  com.zaxxer.hikari.pool.HikariPool - oracle.PHX - Added connection oracle.jdbc.driver.T4CConnection@449b7af
[2024-06-03 22:47:26,928]-[Execution worker Thread 2] INFO  com.zaxxer.hikari.HikariDataSource - oracle.PHX - Start completed.
[2024-06-03 22:47:26,951]-[Execution worker Thread 2] INFO  org.hibernate.Version - HHH000412: Hibernate ORM core version 6.5.2.Final
[2024-06-03 22:47:26,974]-[Execution worker Thread 2] INFO  org.hibernate.cache.internal.RegionFactoryInitiator - HHH000026: Second-level cache disabled
[2024-06-03 22:47:27,148]-[Execution worker Thread 2] WARN  org.hibernate.dialect.Dialect - HHH000511: The 11.2.0 version for [org.hibernate.dialect.OracleDialect] is no longer supported, hence certain features may not work properly. The minimum supported version is 19.0.0. Check the community dialects project for available legacy versions.

And then a mistake

Caused by: java.sql.SQLException: ORA-01441: cannot decrease column length because some value is too big

[jknack]

Is a the driver the issue? The HikariModule set up pool class name to oracle.jdbc.pool.OracleDataSource

Don't seem to be a Jooby issue.

[k1ll1n]

I tried to make a manual connection, and it has no problems.

public class HibernateUtil {

    private static SessionFactory sessionFactory;

    public static SessionFactory getSessionFactory() {
        if (sessionFactory == null) {
            StandardServiceRegistry standardRegistry = new StandardServiceRegistryBuilder()
                    .applySettings(getConfigurationProperties())
                    .build();
            try {
                Metadata metadata = new MetadataSources(standardRegistry)
                        .addAnnotatedClass(Test.class)
                        .buildMetadata();

                sessionFactory = metadata.getSessionFactoryBuilder().build();
            } catch (Exception e) {
                System.err.println("Error create SessionFactory: " + e);
                if (standardRegistry != null) {
                    StandardServiceRegistryBuilder.destroy(standardRegistry);
                }
            }
        }
        return sessionFactory;
    }

    private static Properties getConfigurationProperties() {
        Properties properties = new Properties();
        properties.put(Environment.DRIVER, "oracle.jdbc.driver.OracleDriver");
        properties.put(Environment.URL, "jdbc:oracle:thin:@domain.com:1521:PHX");
        properties.put(Environment.USER, "USER");
        properties.put(Environment.PASS, "PASSWORD");
        properties.put(Environment.DIALECT, "org.hibernate.dialect.OracleDialect");
        properties.put(Environment.SHOW_SQL, "true");
        properties.put(Environment.FORMAT_SQL, "true");
        properties.put(Environment.HBM2DDL_AUTO, "validate");

        return properties;
    }

    public static void shutdown() {
        if (sessionFactory != null) {
            sessionFactory.close();
        }
    }
}

But when I use "HibernateModule" and "HikariModule" for connection, I get the problem described above. And it appears at the server startup, I do not even open any page.

Unfortunately, I can’t find a solution to the problem on my own, so I came to ask you for help.

and my gradle

buildscript {
  ext {
    joobyVersion = System.getProperty("joobyVersion")
  }
}

plugins {
  id "application"
  id "io.jooby.run" version "${joobyVersion}"
  id "io.spring.dependency-management" version "1.1.0"
  id "com.google.osdetector" version "1.7.3"
  id "com.github.johnrengelman.shadow" version "8.1.1"
}

group "com.test"
version "1.0.0"
mainClassName = "com.test.App"
sourceCompatibility = 21

repositories {
  mavenLocal()
  mavenCentral()
}

dependencyManagement {
  imports {
    mavenBom "io.jooby:jooby-bom:$joobyVersion"
  }
}

dependencies {
  implementation "io.jooby:jooby-netty"
  implementation "io.jooby:jooby-logback"
  implementation "io.jooby:jooby-guice"
  annotationProcessor "io.jooby:jooby-apt"
  implementation 'org.reflections:reflections:0.10.2'

  implementation 'io.jooby:jooby-hikari:3.1.2'
  implementation 'io.jooby:jooby-hibernate:3.1.2'
  implementation 'com.oracle.database.jdbc:ojdbc10:19.23.0.0'

  testImplementation "org.junit.jupiter:junit-jupiter-api:5.10.2"
  testImplementation "org.junit.jupiter:junit-jupiter-engine:5.10.2"
  testImplementation "io.jooby:jooby-test"
  testImplementation "com.squareup.okhttp3:okhttp:4.12.0"
}

test {
  useJUnitPlatform()
}

/** Java debug information: */
tasks.withType(JavaCompile) {
  options.compilerArgs += [
    '-parameters',
  ]
  options.debug = true
}

shadowJar {
  mergeServiceFiles()
}

joobyRun {
  mainClass = mainClassName
  restartExtensions = ["conf", "properties", "class"]
  compileExtensions = ["java"]
  port = 8080
}

[jknack]

Odd. Is there way to bootstrap an oracle db with Docker? Never used Oracle :S

[k1ll1n]

Odd. Is there way to bootstrap an oracle db with Docker? Never used Oracle :S

Hello, unfortunately there is no such possibility. And you also have no ideas on how to solve my problem?

[jknack]

ORA-01441: cannot decrease column length because some value is too big

Seems to be a schema/datatype issue, but I can't reproduce it to be 100% sure

[Mani9395]

ORA-01441: cannot decrease column length because some value is too big

Above error will produce when you are trying to modify the column to one value, and that column contains data, whose length is more than setting value, then it will throw error.

So, the only solution is to find the row-column values whose length is more that setting value and modify the data to a small value.