If this is an integration test, you can configure your persistence layer to use an in-memory database.
If this is a unit test, then you shouldn't be using a database at all! You need to properly mock the services that your User class is using, inject them in the User under test, and then verify that the correct methods on the mocked service were called in the correct way.
By the way, a test method named testUser() doesn't inspire a lot of confidence that it has a clear single responsibility. You are probably making too many assertions in your test. I also find it curious that your User class would need to access a database at all. I think your classes have rheir responsibilities mixed up.
The client has no business telling you how to write unit tests. Mocking your services to perform unit tests is the only way to do it properly.
I gather you're using Spring. I don't have a lot of experience with Spring transactions or Spring testing, but the following discussion might help you on your way.
It's fine to use an actual database for an integration test (NOT a unit test), but note that if test methods depend on data committed in other test methods, it may not be possible to use the @Rollback annotation.
I think you configure your test class with @SpringJUnitWebConfig and @TestExecutionListeners like this:
If you're using Maven Failsafe plugin, it's important that the class name starts with IT or ends with IT or ITCase, so that Maven knows it's an integration test and not a unit test.
There is a product called dbUnit that I used to use with Spring JPA. It provides a good framework for database unit testing, including the pre-test data setup and the rolling back of changes.
In my case, I was using DB2 as the production database and Apache Derby as the testing database because they have many similarities, but Derby can be run as an in-memory DBMS.
Regardless, you should ABSOLUTELY, POSITIVELY NEVER run database testing against production databases, rollback or no. Ideally you'll have a process where you can construct, populate, test, and destroy a database of your own. That not only assures you have the most replicable and predictable test environment, it also can be quicker to clean up, since a DROP DATABASE cures all ills.
I'm going to be a "small government" candidate. I'll be the government. Just me. No one else.
Fully agree with Tim. I hope that when you say the client requires you to use the actual database, they mean use a local copy of the same database engine that they are using. While this might be a sensible choice for integration tests on your company's test server, it is a choice that your company must make and not something that a client can prescribe.
For acceptance tests that make use of the client's own test database (like Tim said, ABSOLUTELY, POSITIVELY NEVER the production database), rollbacks are not appropriate because integration tests are performed before deployment. If a client wants an automated acceptance test, they can use other test frameworks and restore the database using a script.