New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Choosing a JDBC driver

From: Java Database Integration with JDBC

Video: Choosing a JDBC driver

Applications that use the JDBC API require drivers. A JDBC driver is a software library that encapsulates the logic you need to communicate between your application and a database management system or a database file stored on the local hard disk. All JDBC drivers follow the same set of rules that is the API, that's defined in Java Standard Edition. A driver library package will contain specific implementations of these Java interfaces.

Choosing a JDBC driver

Applications that use the JDBC API require drivers. A JDBC driver is a software library that encapsulates the logic you need to communicate between your application and a database management system or a database file stored on the local hard disk. All JDBC drivers follow the same set of rules that is the API, that's defined in Java Standard Edition. A driver library package will contain specific implementations of these Java interfaces.

Connection, which lets you connect to the database, ResultSet, which encapsulates data returned from the database, Statement, PreparedStatement, and CallableStatement, which represent requests to the database, and many more. Not all JDBC drivers will support all features of the JDBC spec. Check the documentation for your particular database and your particular driver to see what it's able to do. But nearly all JDBC drivers will support these five interfaces. Typically, you'll get your driver packages from the database vendors themselves, a MySQL driver from MySQL an Oracle driver for Oracle, and so on, but there are also third-party drivers available both free and commercial.

There are four distinct types of drivers, distinguished by their architecture. The oldest type of driver is called the Type 1 driver or the JDBC-ODBC Bridge. In the mid to late '90s when JDBC got started, ODBC or the Open Database Connectivity protocol was the dominant model for communicating with the database. And so Java first communicated with databases through ODBC. You would use the JDBC-ODBC Bridge driver, and then you would also use a separate ODBC driver that was designed for your specific database.

At runtime, requests would go from the application through the JDBC API to the Bridge driver from there to the ODBC driver and then to the database. This wasn't particularly fast, but it was dependable, and one of the pros was that you could talk to any database for which an ODBC driver existed, and that meant pretty much every relational database. The downsides of the ODBC Bridge driver are that it's not 100% Java and therefore not portable between operating systems. Also you're working with two drivers not just one, and they both have to be on the same computer as the application, so you have increased maintenance.

And finally, the ODBC driver has to match the database version, and so if you updated your database on the server, you'd have to go around to all the client applications and update those as well. The Type 2 driver combines a native API driver and a Java driver. They're both installed on the client system just like with the Bridge driver and the ODBC driver, but because you're working primarily with native APIs, the Type 2 driver tends to be very fast and can give you the best possible performance. Once again, just like the Bridge driver, you're not working in 100% Java, and therefore, your applications aren't portable between operating systems.

The native API driver has to be installed on the application client and maintained, and once again, if the database is updated, the client software has to be updated as well, but if you want the best possible performance, a Type 2 driver might be the way to go. The Type 3 driver actually is installed in multiple locations. You'll have 100% Java driver that's installed in the client along with the application, but then with the Type 3 driver architecture, you'll have a middleware server which hosts its own application, requests go at runtime from the application to the Type 3 driver that's installed on the client and from there over the network to the middleware server and from there to the database.

With a Type 3 driver, the middleware driver can be native, and so the communication between the middleware and the database can be very fast, but once again, you have maintenance challenges because you have more than one driver to maintain. The fourth type of driver, and one of the most common, is called the Type 4, or the 100% Java thin driver. With a 100% Java driver, there is only one driver package, not two, and the driver is packaged with the Java application itself, whether on a client computer, or in a web environment on a Java Enterprise Edition application server.

Requests go from your application to the driver that's on the client and then your communications go from the application through JDBC through the thin driver to the database server if it's out on the web, or to the database file if it's on the local hard disk. With the Java thin driver, you're communicating directly from the application to the database. There are no additional layers to install or maintain, and so maintenance is greatly simplified. The only downside is that you need a different driver package for each database you're working with.

Most applications will only use a single database type, but if you're working with more than one database management system, you'll need to provide multiple drivers. In this course I'll be doing all of my demonstrations with the two Type 4 drivers. One will be for the MySQL database server, and with this driver I'll show you how to communicate with a database server that's accessed over the web. I'll be actually working with a local copy of MySQL and addressing it as local host, but the code I'll be using would work just as well if the MySQL server were hosted out in the Cloud.

The other database I'll be working with in this course is called HyperSQL, or HSQLDB. HyperSQL is one of many pure Java databases that are used by Java developers, others include the Apache Derby database and H2. These databases run as in-memory databases. But the database is initialized with a local file, but then it runs in the same Java process as the application. So with these two drivers and these two database management systems, I'll be able to show you how to handle differences between databases while making your code as portable as possible, and I'll also be emphasizing the use of these Type 4 drivers, which are pure Java and can be encapsulated into your applications.

There are other types of drivers out there that aren't directly recognized by the JDBC API but can also be useful. For example, in 2010 there was talk of a Type 5 driver. A Type 5 driver has the same architecture as Type 4 that is it's 100% Java and Thin, but it gives you better performance than a pure Type 4 driver. And then there are other drivers that don't have specific type numbers that can run in the same Java process as the application just like the HyperSQL driver I'll show you. But if you stick with learning the four main types of drivers that are defined by JDBC, Types 1, 2, 3, and 4, those will cover the vast majority of JDBC drivers that are available to Java developers.

Show transcript

This video is part of

Image for Java Database Integration with JDBC
Java Database Integration with JDBC

32 video lessons · 6005 viewers

David Gassner
Author

 
Expand all | Collapse all
  1. 5m 23s
    1. Welcome
      53s
    2. What you should know before starting this course
      1m 20s
    3. Using the exercise files
      3m 10s
  2. 12m 2s
    1. Testing your Java development environment
      5m 14s
    2. Importing a MySQL database
      5m 1s
    3. Creating a database user in MySQL
      1m 47s
  3. 32m 47s
    1. What is JDBC?
      4m 26s
    2. Choosing a JDBC driver
      6m 46s
    3. Connecting to a MySQL database server
      8m 7s
    4. Connecting to a HyperSQL database file
      6m 23s
    5. Executing a static SQL statement
      7m 5s
  4. 17m 42s
    1. Connecting to multiple databases
      6m 24s
    2. Handling JDBC exceptions
      7m 7s
    3. Closing database resources in Java 7
      4m 11s
  5. 47m 25s
    1. Looping through result sets
      8m 23s
    2. Moving the cursor in scrollable result sets
      5m 51s
    3. Limiting the number of fetched rows
      6m 57s
    4. Filtering data with prepared statements
      6m 58s
    5. Calling stored procedures
      5m 48s
    6. Handling multiple values from stored procedures
      5m 54s
    7. Using generic getter methods in Java SE 7
      7m 34s
  6. 45m 23s
    1. Managing data entities with JavaBean classes
      5m 0s
    2. Retrieving a single row as a JavaBean object
      6m 5s
    3. Inserting rows with prepared statements
      8m 2s
    4. Updating rows with prepared statements
      5m 4s
    5. Deleting rows with prepared statements
      4m 9s
    6. Managing data with updatable result sets
      6m 6s
    7. Using a persistent database connection
      6m 43s
    8. Committing and rolling back transactions
      4m 14s
  7. 9m 35s
    1. Getting the DatabaseMetaData object
      3m 40s
    2. Getting column and data type information
      5m 55s
  8. 50s
    1. Next steps
      50s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.