Što je JDBC? Uvod u povezanost Java baze podataka

JDBC (Java Database Connectivity) je Java API koji upravlja povezivanjem s bazom podataka, izdavanjem upita i naredbi i rukovanjem skupovima rezultata dobivenim iz baze podataka. Izbačen kao dio JDK 1.1 1997. godine, JDBC je bila jedna od prvih komponenti razvijenih za sloj postojanosti Java.

JDBC je u početku zamišljen kao API na strani klijenta, omogućavajući Java klijentu interakciju s izvorom podataka. To se promijenilo s JDCB 2.0, koji je sadržavao neobavezni paket koji podržava JDBC veze na strani poslužitelja. Svako novo izdanje JDBC od tada sadrži ažuriranja i na klijentskoj strani ( java.sql) i na poslužiteljskoj ( javax.sql). JDBC 4.3, najaktualnija verzija ovog teksta, objavljen je u sklopu Java SE 9 u rujnu 2017.

Ovaj članak predstavlja pregled JDBC-a, nakon čega slijedi praktični uvod u upotrebu JDBC API-ja za povezivanje Java klijenta sa SQLiteom, laganom relacijskom bazom podataka.

Kako JDBC radi

Razvijen kao alternativa API-ju ODBC (Open Database Connectivity) na temelju C, JDBC nudi sučelje na programskoj razini koje rukuje mehanikom Java aplikacija koje komuniciraju s bazom podataka ili RDBMS-om. JDBC sučelje sastoji se od dva sloja:

  1. JDBC API podržava komunikaciju između Java aplikacije i JDBC upravitelja.
  2. JDBC pokretački program podržava komunikaciju između upravitelja JDBC-a i upravljačkog programa baze podataka.

JDBC je uobičajeni API s kojim kôd vašeg programa komunicira. Ispod toga je JDBC-kompatibilan upravljački program za bazu podataka koju koristite.

Slika 1 je arhitektonski pregled JDBC-a u sloju postojanosti Java.

JavaWorld /

Korištenje JDBC za povezivanje s bazom podataka

Jedna od sretnih činjenica programiranja u Java ekosustavu je da ćete vjerojatno pronaći stabilni konektor baze podataka JDBC za bilo koju bazu podataka koju odaberete. U ovom uputstvu koristit ćemo SQLite za upoznavanje JDBC-a, uglavnom zato što je tako jednostavan za upotrebu.

Koraci za povezivanje s bazom podataka pomoću JDBC su sljedeći:

  1. Instalirajte ili pronađite bazu podataka kojoj želite pristupiti.
  2. Uključite JDBC knjižnicu.
  3. Provjerite je li potreban JDBC upravljački program na vašoj stazi.
  4. Upotrijebite JDBC knjižnicu za dobivanje veze s bazom podataka.
  5. Koristite vezu za izdavanje SQL naredbi.
  6. Zatvorite vezu kad završite.

Zajedno ćemo proći ove korake.

Pronalaženje JDBC upravljačkog programa

Da biste pronašli upravljački program za bazu podataka koju želite koristiti, jednostavno pretražite svoju bazu podataka i JDBC na webu. Na primjer, upisivanjem " mysql jdbc driver" pojavit će se upravljački program za MySQL. Izazivam vas da pronađete bazu podataka kompatibilnu s Java bez JDBC pokretačkog programa!

Korak 1. Preuzmite i instalirajte SQLite

SQLite je vrlo kompaktna baza podataka. Nije namijenjen proizvodnoj upotrebi, ali je izvrstan izbor za brzo isprobavanje stvari. SQLite koristi datoteku kao svoju funkcionalnu bazu podataka, bez potrebe za instalacijom usluge ili demona.

Da biste započeli s ovom demonstracijom, preuzmite SQLite uzorak baze podataka. Otpakirajte .dbdatoteku i spremite je negdje gdje nećete zaboraviti.

Ova datoteka sadrži i funkcionalnu bazu podataka temeljenu na datotekama i uzorak sheme i podataka koje možemo koristiti.

SQL i JDBC

NoSQL je postao popularan tijekom posljednjeg desetljeća, ali relacijske baze podataka i dalje su najčešći tip pohrane podataka koji se koristi. Relacijska baza podataka je strukturirana repozitorij koji se sastoji od stolova s stupaca i redaka. SQL (jezik strukturiranih upita) jezik je koji arhitekti podataka koriste za stvaranje stvari, čitanje, ažuriranje i brisanje novih zapisa u relacijskoj bazi podataka. JDBC je prilagodbeni sloj s Jave na SQL: pruža programerima Jave zajedničko sučelje za povezivanje s bazom podataka, izdavanje upita i naredbi i upravljanje odgovorima.

Korak 2. Uvezite JDBC u svoj Java program

Kodiranje bismo mogli obaviti u IDE-u, ali kodiranje izravno u uređivaču teksta bolje će pokazati JDBC-ovu jednostavnost. Za početak ćete morati imati kompatibilnu JDK instalaciju za svoj operativni sustav.

Pod pretpostavkom da imate instalirane alate za programere Java platforme, možemo započeti stvaranjem jednostavnog Java programa. U svoj uređivač teksta zalijepite kod prikazan na popisu 1. Pozovite ovu datoteku WhatIsJdbc.java.

Popis 1. Jednostavan Java program

 class WhatIsJdbc{ public static void main(String args[]){ System.out.println("Hello JavaWorld"); } } 

Sada sastaviti kod unosom naredbe: javac WhatIsJdbc.java. Kompiliranje će dati WhatIsJdbc.classdatoteku. Izvršavanje ove datoteke iz naredbenog retka s pozivom: java WhatIsJdbc.

[Pogledajte "Što je JDK? Uvod u Java Developer Kit" za više informacija o interakciji s JDK u naredbenom retku.]

Nakon što imate osnovni Java program, možete uključiti JDBC knjižnice. Zalijepite kod s popisa 2 na čelu vašeg jednostavnog Java programa.

Popis 2. Uvoz JDBC

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.Statement; 

Svaki od ovih uvoza pruža pristup klasi koja olakšava standardno povezivanje Java baze podataka:

  • Connection represents the connection to the database.
  • DriverManager obtains the connection to the database. (Another option is DataSource, used for connection pooling. )
  • SQLException handles SQL errors between the Java application and the database.
  • ResultSet and Statement model the data result sets and SQL statements.

We'll see each of these in action shortly.

Step 3. Add the JDBC driver to your classpath

Next, you'll add the SQLite driver to your classpath. A JDBC driver is a class that implements the JDBC API for a specific database.

Download the SQLite driver from GitHub. Be sure to get the most recent .jar file and store it somewhere you'll remember.

The next time you execute your Java program, you will pull that .jar file in via the classpath. There are several ways to set the classpath. Listing 3 shows how to do it using a command-line switch.

Listing 3. Executing SQLite driver on the Java classpath

 java.exe -classpath /path-to-driver/sqlite-jdbc-3.23.1.jar:. WhatIsJdbc 

Notice that we've set the classpath to point at the driver and the local directory; this way Java will still find our class file.

Step 4. Obtain a database connection

The classpath now has access to the driver. Now, change your simple Java application file to look like the program in Listing 4.

Listing 4. Using the JDBC Connection class to connect to SQLite

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.Statement; class WhatIsJdbc{ public static void main(String[] args) { Connection conn = null; try { String url = "jdbc:sqlite:path-to-db/chinook/chinook.db"; conn = DriverManager.getConnection(url); System.out.println("Got it!"); } catch (SQLException e) { throw new Error("Problem", e); } finally { try { if (conn != null) { conn.close(); } } catch (SQLException ex) { System.out.println(ex.getMessage()); } } } } 

Compile and execute this code. Assuming all goes well, you will get an affirming message.

No suitable driver found?

If you've received an error that looks like "No suitable driver found for jdbc:sqlite," then you need to revisit the classpath and make sure it points to the driver you downloaded. Failed driver connection is the most common stumbling block for beginners using JDBC. Don't sweat it; just fix it.

Now we're ready for some SQL commands.

Step 5. Query the database

With the live connection object in hand, we can do something useful, like querying the database. Listing 5 shows how to query SQLite using the JDBC Connection and Statement objects.

Listing 5. Querying the database with JDBC

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.Statement; class WhatIsJdbc{ public static void main(String[] args) { Connection conn = null; try { String url = "jdbc:sqlite:path-to-db-file/chinook/chinook.db"; conn = DriverManager.getConnection(url); Statement stmt = null; String query = "select * from albums"; try { stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String name = rs.getString("title"); System.out.println(name); } } catch (SQLException e ) { throw new Error("Problem", e); } finally { if (stmt != null) { stmt.close(); } } } catch (SQLException e) { throw new Error("Problem", e); } finally { try { if (conn != null) { conn.close(); } } catch (SQLException ex) { System.out.println(ex.getMessage()); } } } } 

In Listing 5 we use our Connection object to obtain a Statement object: conn.createStatement(). We then use this object to execute an SQL query: stmt.executeQuery(query).

The executeQuery command returns a ResultSet object, which we then use to iterate over the data with while (rs.next()). In this example, you should see the album titles we've queried on as output.

Notice that we also closed the connection, via a call to conn.close().

Network connections with JDBC

The database connection string in Listing 5 is for a local connection: jdbc:sqlite:path-to-db-file/chinook/chinook.db. To access the database via a network, the connection string would need to include the network URL and (usually) credentials for accessing it.

Doing more with JDBC

So far we've covered the basics of using JDBC to connect to a database and issue SQL commands. While Statementss and ResultSets work well for common scenarios, you'll likely need additional options for larger or more complex applications. Fortunately, the JDBC library continues evolving to meet most database access needs.

PreparedStatements

One easy way to increase the flexibility of your code is to replace the Statement class with PreparedStatement, as shown in Listing 6.

Listing 6. Using JDBC PreparedStatements

 String prepState = "insert into albums values (?, ?);"; PreparedStatement prepState = connection.prepareStatement(sql); prepState.setString(1, "Uprising"); prepState.setString(2, "Bob Marley and the Wailers "); int rowsAffected = preparedStatement.executeUpdate(); 

PreparedStatement replaces Statement's hard-coded values with question marks (?). Using PreparedStatements optimizes your code for reuse: a PreparedStatement is compiled only once, and can then be reused with a variety of parameters. As your code base grows, you simply insert new values into the statement, instead of hacking the string object itself.

Batch updates

Whenever an application has several updates to issue, doing them in batches can greatly benefit performance. The essence of batching is to take the multiple updates and collect them together, then issue them all at once. Listing 7 uses JDBC's batch methods to perform a batch update of several PreparedStatements.

Listing 7. Batching with PreparedStatement

 prepState.setString(1, "Uprising"); prepState.setString(2, "Bob Marley and the Wailers"); preparedStatement.addBatch(); prepState.setString(1, "Wildflowers"); prepState.setString(2, "Tom Petty and the Heartbreakers"); preparedStatement.addBatch(); int[] rowsAffected = preparedStatement.executeBatch(); 

JDBC transactions

Transactions in relational databases allow for a set of updates to be wrapped in an interaction that either succeeds or fails altogether. The basics of using a transaction via JDBC are to tell the system to turn off auto-commit, and then manually tell the system to commit when you are done. By default, auto-commit is on, which means whenever an executeUpdate or executeInsert is run, the command is committed.

Listing 8 shows a small slice of a JDBC transaction.

Listing 8. JDBC transactions

 connection.setAutoCommit(false); // Use executeUpdate multiple times connection.commit(); 

When connection.commit() is encountered, all the updates wrapped inside will be attempted, and if any fail, they all will be rolled back.

There are many more features in JDBC 4.3 worth exploring, including using CallableStatement for stored procedures, using DataSource objects for improved application performance (especially via connection pooling), and converting a JDBC ResultSet to a Java Stream.

Database-specific features

Although every JDBC-compliant database offers the same core features for connecting and interacting with a database via SQL, some databases do more than others. As an example, Oracle DB offers result caching, which is not required by the JDBC specification. Here's an example:

 conn.prepareStatement ("select /*+ result_cache */ * from employees where employee_id < : 1"); 

This example is taken from the documentation for Oracle's JDBC OCI Driver.

Conclusion

JDBC is one of Java's oldest APIs, providing an easy-to-use solution for one of the perennial needs of Java application development. Knowing just the few JDBC calls demonstrated in this article will get you started using JDBC to connect to virtually any database. Once you've got those commands down, you can begin to explore some of the more sophisticated options that have been built into JDBC.

Iako je JDBC dovoljan za jednostavnije aplikacije, većina programera na kraju će potražiti Java Persistent API (JPA) kako bi razvili formalniji sloj pristupa podacima. JPA zahtijeva više prethodnog rada i sofisticiranije razumijevanje arhitekture aplikacije, ali uspostavlja vam dosljedniji, izoliraniji i dobro definirani sloj pristupa podacima. Pogledajte popratni članak ovog članka "Što je JPA? Uvod u Java Persistent API" za više informacija o razvoju sloja postojanosti podataka za vaše Java programe.

Ovu priču, "Što je JDBC? Uvod u povezivanje Java baza podataka" izvorno je objavio JavaWorld.