JDBC stands for Java database connectivity. This includes connecting to DB in Java, running queries and processing results.
A relational DB that has tables consisting of rows and columns. You can interact with a relational DB with -
- JDBC APIs. You get a connection, create a statement and get result set of the query.
- Use JPA(Java persistence API). This uses a concept called ORM (Object relational mapping) where you map Java objects to tables and operate on these objects. For eg. hibernate is one such framework.
- SQL (Structured query language) is used to interact with the relational DB.
In this post we are going to understand JDBC.
Interfaces in JDBC
All JDBC classes in Java are part of java.sql.* package. There are 4 important interfaces that you need to understand -
- Driver : Know how to get connection from DB
- Connection : Knows how to interact with DB
- Statement : Knows how to run SQL on the DB
- ResultSet : Knows the result returned by the SQL query from the DB.
To see a sample Java code on how to connect to a DB from Java you can see one of my previous posts -
Above code snippet uses mysql DB but you can use any DB really. There are some common things that we sill see in a moment.
NOTE : You no longer have to explicitly load the driver class using Class.ForName(driver). From JDBC 4 driver class is automatically loaded from the class path.
Building DB application with JDBC
Let's start by looking at how JDBC url looks like and is constructed -
As you can see JDBC URL is split into 3 parts -
- 1st part is the jdbc protocol
- 2nd part is the name of the DB. For eg. mysql, postgres, derby or oracle
- 3rd part is respective DB specific format
Some other examples are -
But since JDBC 4 you don't. Java loads automatically for you if it's present in the classpath. Once driver is loaded next you need to get the Connection from it. You can do so with -
- Connection conn = DriverManager.getConnection("jdbc:derby:testDB");
NOTE : If you get exception like "java.sql.SQLException: No suitable driver found for..." then the driver is not present on the classpath. Add it.
Once you have the connection you can get the Statement from it as follows -
- Statement stmt = conn.createStatement();
Once you have the statement you are all set to execute queries on DB -
- ResultSet rs = stmt.executeQuery("select * from countries");
- int res = stmt.executeUpdate("insert into countries values(1, 'India')");
NOTE : Column indexes start with 1. So something like rs.getInt(0)will throw SQL exception.
Once you have processed the result set never forget to close the resources and that include your -
Why do we use a DataSource instead of a DriverManager?
Always use a datasource over DriverManager as-
Why do we use a PreparedStatement instead of a Statement
You should always use PreparedStatement instead of a Statement. PreparedStatement is subclass of Statement. This has multiple reasons -
- Performance: A PreparedStatement figures out a plan to run the SQL and remembers it. Helps when same query is run multiple times.
- Security: To prevent SQL injection. It's a famous hacking technique. Go ahead read it up on google.
- Readability: No String concatenations in building queries.