Data Testing / REST Assured / Java Unit Testing

So in the past I have worked with rest assured and other java frameworks.

This is more an open ended question in what is the best / modern way to test data either sql / oracle ...etc

So the basics would be is you need to confirm data in a table is equal to a specific string / int / boolean ...etc. The test needs to be able to run via maven or something that can be integrated into a Jenkins type of environment so that it will be run on every build.

What would you suggest is the best way to achieve this any idea or tools that you have used would be welcome.

I have searched around and most people are just doing normal unit tests. But if i wanted to test production data that should remain static what would you suggest or are the best practices.

Thanks
Flenters

Let me present my opinion, hopefully it will clarify some things:

First of all, the data testing is really a broad topic, there is no one "silver bullet solution" and one adopted approach.

Usually, you can test your DAO code (SQL queries) with the help of a spring testing framework that, among other things provides a way to load some data before the test, run the code that interacts with the database, transaction management, etc.

This kind of test is not a unit testing, it falls into the category of an integration testing, which can be easily supported by maven/Gradle, be run from jenkins as a part of the pipeline.

There is also a tool called DbUnit that also works in this domain.

However, this is not the same as "testing the data".

Let me ask you a question, many questions actually, which all are boil down to: what exactly do you want to test? The fact that the data is consistent? Do you have constraints in the database, do you rely on them? Or avoid them and stuff like indices for performance reasons or any other reason that can be justified by your use case?

Do you want to populate the data during the test? How will it influence the data that already exists in production?

Do you plan to use transactions in the tests? For example spring testing framework that I've mentioned above is able to automatically open a "test transaction" before the test, and after the test execute an artificial rollback regardless the actual result of the test. This way it guarantees that all the changes done during the test will rollback and the data won't be changed after the test, but the question is open?

Do you plan to test the schema changes over-time. Usually, it's possible to change schema with new versions of the product. In this field the tools like liquidbase or flyway shine. For example, Flyway provides a notion of "atomic" migration and you basically can apply a series of migrations "transforming" the schema to the desired state. It doesn't fall into the category of testing tools though, although you can run this migration thing from maven if you wish.

As you see, these questions do not have "generally correct" answers, different projects have different needs. So bottom line if you want to discuss a specific approach that you think is applicable to your product - you'll welcome to provide more technical details, but other than that - all the frameworks/libraries that I mentioned can qualify as tools that you ask about, and still there is a lot of missing information here.