Hibernate or DBUtils

[raviada]

Hello, I am building several REST APIs with MySql. I am coming from using spring with hibernate. I am playing with your framework, however I see all of your examples using database are done using plain JDBC code. If we have larger models and nested, it would be difficult to map the resultSet to object model. What is your recommendation to use as an ORM for easy mapping between object to sql data and vice versa.

[stevehu]

@raviada That is a very good question. For us, we build microservices most of the cases and we are dealing with 2 or 3 tables most of the time. It doesn't make any sense to use Hibernate as it increases the latency. However, if you are dealing with a large database, it makes perfect sense to use an ORM like Hibernate. In fact, these days, we are not using the SQL database very often but NO-SQL most of the time. Take a look at the light-portal user-command and user-query, which is ES and CQRS based on Apache Kafka Streams.

[raviada]

Thank you Steven. We can’t avoid using relational database in our case. we don’t have large databases but several tables with relationships between them. I agree with latency that hibernate brings. Have you used or heard of any light weight ORM like jOOQ? what’s your thoughts on that?

On Sun, Apr 19, 2020 at 6:21 PM Steve Hu notifications@github.com wrote:

@raviada https://github.com/raviada That is a very good question. For us, we build microservices most of the cases and we are dealing with 2 or 3 tables most of the time. It doesn't make any sense to use Hibernate as it increases the latency. However, if you are dealing with a large database, it makes perfect sense to use an ORM like Hibernate. In fact, these days, we are not using the SQL database very often but NO-SQL most of the time. Take a look at the light-portal user-command and user-query, which is ES and CQRS based on Apache Kafka Streams.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/networknt/light-4j/issues/698#issuecomment-616242304, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAGIAXBOHPJFRDTLOGGPSF3RNOBOFANCNFSM4ML7NIPQ .

--

Ravi Ada | Founder + CTO

110 Corcoran Street, 5th Floor, Durham, NC 27701 e: ravi@medl.io p: 972.974.8500

http://medl.io http://medl.io/

[stevehu]

I am not familiar with jOOQ so I cannot say anything about it. Light-4j is POJO based so you can select any ORM to try it out. Make sure you compare the performance between them and also against plain JDBC. With the high throughput and low latency of light-4j, the end result would surprise you. I know a bank has switched from Hibernate to JDBC for an application with hundreds of tables accessed by dozens of microservices. They reduced the number of total database connections significantly and lowered production costs with a minimum number of instances.

Take a look at the following form, the dropdowns are populated conditionally by an API based on MySQL deployed on my 11-year-old server in Toronto.

https://dev.lightapi.net/#/app/form/covidMapForm

[raviada]

Yes, thank you for explaining that. I hated Hibernate for its overhead and lot of magic it does under the hoods but loved the simplicity that it brings. One simple question, what’s the best way to convert the sql result sets to java objects or JSON string to send the response and also construct the sql from objects without mapping field by field.

On Mon, Apr 20, 2020 at 4:53 AM Steve Hu notifications@github.com wrote:

I am not familiar with jOOQ so I cannot say anything about it. Light-4j is POJO based so you can select any ORM to try it out. Make sure you compare the performance between them and also against plain JDBC. With the high throughput and low latency of light-4j, the end result would surprise you. I know a bank has switched from Hibernate to JDBC for an application with hundreds of tables accessed by dozens of microservices. They reduced the number of total database connections significantly and lowered production costs with a minimum number of instances.

Take a look at the following form, the dropdowns are populated conditionally by an API based on MySQL deployed on my 11-year-old server in Toronto.

https://dev.lightapi.net/#/app/form/covidMapForm

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/networknt/light-4j/issues/698#issuecomment-616439628, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAGIAXDLU7JQPM3UF45ENY3RNQLRRANCNFSM4ML7NIPQ .

--

Ravi Ada | Founder + CTO

110 Corcoran Street, 5th Floor, Durham, NC 27701 e: ravi@medl.io p: 972.974.8500

http://medl.io http://medl.io/

[stevehu]

There is no efficient way to do so once you have the data in RDBMS tables. Eventually, you have to use Jackson ObjectMapper to convert the ORM result into JSON. This is one of the reasons I don't like RDBMS for REST APIs. You received the data in JSON, transform into POJO, use ORM to save it into DB tables. When you need your data, you need to use ORM to retrieve it, ORM to POJO, POJO to JSON and send back to the user. Do you see how much work your server needs to do compensate for the wrong design? For most applications I am building now, the server receives JSON, converts it into an event, publishes to Kafka. A number of query side services pick up events they are interested in and create materialized view and save it into a local K/V store (Rocksdb if you are using Kafka Streams). Since you have a single event topic as your raw data, you can add more query side service as you like later on. When a request hits one of the query side services with a key, a string (in JSON) will be retrieved and send back immediately.

[raviada]

Hello Steve, I am evaluating a lightweight ORM ActiveJDBC from Javalite. It seems to satisfy my requirements. I see in your code

if (exchange.isInIoThread()) {
  exchange.dispatch(this);
  return;
}

if I don't have this, I am not able to connect to database using HCP. What exactly happening here?

Thanks, Ravi Ada

[stevehu]

The code is to dispatch the request to another worker thread to perform long-running activities like DB access and allow the IO thread to accept other new requests. This is the major difference between light-4j and Java EE which allocates one thread per request and most of the time that thread is waiting for the DB access or accessing other services.

BTW, you shouldn't need to manually add this piece of code if you are using our exception handler in the request/response chain.

https://github.com/networknt/light-4j/blob/master/exception/src/main/java/com/networknt/exception/ExceptionHandler.java#L70

[raviada]

Great, do you have an example of using Light exception handler in req/resp chain?

[raviada]

Great, thanks! I am starting to understand the framework. I think it will take sometime to get a good handle on this. However, I am on the time crunch to have a solution quickly. I have experience with spring boot, hibernate etc for my last few projects. I had some performance issues with that stack.

I am trying to replace spring boot with light4j and hibernate with ActiveJDBC. I was able to get it to work, but getting the database connection when I needed to query something from the database.

The way Light4J dispatching to worker threads, db connection is not available in worker thread. I need some quick help to understand the best possible place to create the connection and do db queries etc.

Here is my code to test multiple of these new frameworks

package com.dobeyond.database.handler;

import com.dobeyond.database.model.Test;
import com.networknt.handler.LightHttpHandler;
import com.networknt.service.SingletonServiceFactory;
import io.undertow.server.HttpServerExchange;
import io.undertow.util.HttpString;
import org.javalite.activejdbc.Base;
import org.javalite.activejdbc.LazyList;

import javax.sql.DataSource;

public class TestGetHandler implements LightHttpHandler {
  private static final DataSource ds = SingletonServiceFactory.getBean(DataSource.class);

  @Override
  public void handleRequest(HttpServerExchange exchange) throws Exception {
    if (exchange.isInIoThread()) {
      exchange.dispatch(this);
      return;
    }
    exchange.getResponseHeaders().add(new HttpString("Content-Type"), "application/json");
    Base.open(ds);
    LazyList<Test> tests;
    tests = Test.findAll();
    Base.close();
    exchange.getResponseSender().send(tests.toJson(true));
  }
}

What is happening is when I run it debug mode it works, but when I run it thru jar file, it gives an error that db connection is not available.

According to ActiveJDBC documentation, https://javalite.io/database_connection_management , it creates the connection on the same thread where query is being executed. Can you please explain how getHandler is handling the request in worker threads.

[stevehu]

I don't feel right if it creates a connection in the same worker thread. At least there should be a connection pool or something. Creating a DB connection is very resource-intensive and time-consuming. Here is an example in one of my handlers.

https://github.com/networknt/light-reference/blob/master/src/main/java/com/networknt/reference/handler/DataGetHandler.java

[raviada]

I am still using Hikari pool, I am passing the datasource variable to Base.open(ds), will this be still a problem? and if thats the case, why I am not able to see the connection in the worker thread?

[stevehu]

Where is the connection in worker thread? in the thread-local? It doesn't make sense to me. Connection and thread shouldn't have any dependency.

[raviada]

Sorry I misunderstood the workings of ActiveJDBC. I was closing the connection before accessing the resultset. There was no problem in getting the connection from a pool within worker thread. It is working now. There was a little bit of confusion when coming from Hibernate world. ActiveJDBC eliminates a lot of reflection and cacheing relationships etc, making it more leaner. If someone is interested in lightweight ORM, I would recommend this library. We can close this now.

[stevehu]

@raviada I think a lot of users might have the same issue as yours. I am wondering if you could contribute an example in the light-example-4j project to show others how to use the AcitveJDBC. Writing and publishing a blog would be even better. Thanks.

[raviada]

Sure, no problem. will do sometime this week. It make sense to have an example with ORM since a lot of people are still using RDBMS in several applications.

[raviada]

  static final Logger logger = LoggerFactory.getLogger(CptSearchGetHandlerTest.class);
  static final String url = enableHttp2 || enableHttps ? "https://localhost:" + httpsPort : "http://localhost:" + httpPort;
  static final String JSON_MEDIA_TYPE = "application/json";
  @ClassRule
  public static TestServer server = TestServer.getInstance();
  static final boolean enableHttp2 = server.getServerConfig().isEnableHttp2();
  static final boolean enableHttps = server.getServerConfig().isEnableHttps();
  static final int httpPort = server.getServerConfig().getHttpPort();
  static final int httpsPort = server.getServerConfig().getHttpsPort();

When I used codegen from openapi yaml file, it created the test classed like this. Any reason why it generated with misplaced/jumbled up code? I was not able to compile because of this reason. Have you experienced this before?

[stevehu]

That is odd. Are you using the website https://codegen.lightapi.net/form/codeGenSingleForm or you are using the command line?

[raviada]

Command line. I cloned master branch because I am using java 11 JDK from AWS (correto)

[stevehu]

I am guessing you are using the release branch which is the default branch of light-codegen. I just tested it and everything works. I can compile the entire project without any issue. Of course, the generated test cases are commented out in the beginning and you need to enable them once you have the handlers implemented. Could you please attach one of the test files somewhere so that I can see the detail?

[raviada]

why query parameter is not matching with any route? I am getting this error. Since it is not required, if I remove the query param it is working. any ideas?

no matches found: https://localhost:8443/smartcatalog/v1/metadata/cpt/search?term=abc

OpenApi 3.0 spec

paths:
  /metadata/cpt/search:
    get:
      tags:
        - valueset
      summary: Finds cpt by tags
      description: >-
       Universal search by string or cpt code.
      operationId: searchCpt
      parameters:
        - name: term
          in: query
          required: false
          schema:
            type: string
      responses:
        '200':
          description: successful operation
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Cpt'
        '400':
          description: Invalid tag value

handler.yml

paths:
  - path: '/smartcatalog/v1/metadata/cpt/search'
    method: 'GET'
    exec:
      - default
      - com.dobeyond.smartcatalog.handler.MetadataCptSearchGetHandler

[stevehu]

query parameters are not part of the path so they are not checked at all. I am wondering if you have some special characters in the query parameter. We have APIs with query parameters most of the time. One way to figure it out is to debug into it. Light-4j application is just a POJO, so you can just debug it like other Java applications in an IDE.

https://doc.networknt.com/tutorial/common/debug/