JDBC is the Java (standard) Database Connectivity feature. It provides a vendor-independent API between Java applications and most database servers, including MS SQL Server, Oracle, IBM, MySQL/MariaDB, PostgreSQL, SQLite and many more. It is very definitely p
not obsolete and it unlikely to become so in the foreseeable future.
The Oracle JDBC drivers (there are several of them) all provide the same basic JDBC interface. They communicate as network clients to the server indicated in their connection URLs. I know of no non-JDBC Oracle connection services in native Java nor have ever seen any need for one.
The JDBC Connection object is obtained either by the JDBC DriverManager or from a JDBC Connection Pool.
You should obtain the Connection just before doing database operations and close the connection when you are done in order to ensure that system resources will be used efficiently. Once you have a Connection, you can run Statements against it, obtain ResultSets and all of the other perks of java database access.
A Connection Pool is simply a collection of pre-allocated Connection objects. Connections tend to be expensive to create, so it's better to keep a set of them, pass them to users when requested, and return them to the pool when done. A Connection Pool doesn't return direct Connection objects - it returns Connection façades. Most of the methods implemented by a Pool Connection are simply passed through to the underlying Driver Connection, but the close() method returns the Connection to its Connection Pool instead of actually closing the Driver Connection.
You do not need a Connection Pool for simple Java database apps, but webapps should almost always be using one or more Connection Pools. You may have many more webapp users active at one time than database operations in progress, so a pool allows Connections to be available to tasks that need them without having tasks hold them when they don't need them. Also Connection is an interface, not an object class, so it's not valid to hold a Connection as a Session variable between HTTP requests even if that wasn't tying up valuable resources while the user is idle.
Connection Pools are defined to the Server not to the application. That means that you can tune them without making changes to the application itself. You can also target an alternate server (such as a
test server) or even an alternative brand of database (as long as the SQL you're using is compatible).