Introduction To JDBC
Feb 07
This tutorial aims to give you a quick intro to Java Database Connectivity (JDBC) API. JDBC technology was designed to keep simple things simple. This means that the JDBC API makes everyday database tasks, such as simple SELECT statements, very easy. After this you should be able to use the API to do basic and some advanced Database related tasks.
I will use MySQL for this tutorial but you can use whatever DBMS you want. Before starting you should download the appropriate JDBC driver based on your database selection. The driver should be available on the database site with the instructions for installing it. However the installation process will be (in most cases) adding the driver JAR to the project path and importing it.
You can download MySQL JDBC driver from here
- Loading Drivers
- Making a Connection
- Creating a Table
- For a SELECT statement, the method to use is executeQuery.
- For statements that create or modify tables, the method to use is executeUpdate.
- Entering Data into a Table
- Getting Data from a Table
- Prepared Statements & Variable based queries
- Using Joins
- Transactions
- Scrollable Result Set
1 |
Class.forName("com.mysql.jdbc.Driver").newInstance(); |
This step aims to make a connection to the database you will work on. The JDBC URL format for MySQL Connector/J is as follows, with items in square brackets ([, ]) being optional:
1 |
jdbc:mysql://[host][:port]/[database] |
If the host name is not specified, it defaults to 127.0.0.1. If the port is not specified, it defaults to 3306, the default port number for MySQL servers. If the database is not specified, the connection will be made with no default database. In this case, you will need to either call the set- Catalog() method on the Connection instance or fully specify table names using the database name (that is, SELECT db- name.tablename.colname FROM dbname.tablename…) in your SQL
Now, in your project this is how you make a connection to the database named TestJDBC (you should create it before trying to use it).
1 |
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:8889/TestJDBC", "root", "root"); |
A Statement object is used for executing a static SQL statement and returning the results it produces. Simply you create a Statement and then execute it. You have couple of options when executing a Statement:
1 2 3 |
Statement statement = connection.createStatement(); statement.executeUpdate("CREATE TABLE BOOKS " + "(NAME VARCHAR(32), AUTHOR_ID INTEGER, PRICE FLOAT) "); statement.executeUpdate("create table AUTHORS (AUTHOR_ID INTEGER, " + "NAME VARCHAR(40))"); |
NOTE: Unlike DBMS, the Java compiler care about where lines are divided, and it will not compile a String object that extends beyond one line. So, when you are giving strings in the Java programming language, you need to enclose each line in quotation marks and use a plus sign (+) to concatenate them.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Statement statement = connection.createStatement(); // Books Data statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('The Shopkeeper', 101, 12.95)"); statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('First, Do No Harm', 102, 7.99)"); statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Dogwood', 103, 10.39)"); statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Tunnel Vision', 104, 17.15)"); statement.executeUpdate("INSERT INTO BOOKS " + "VALUES ('Not in the Heart', 103, 11.19)"); // Authors Data statement.executeUpdate("insert into AUTHORS values (101, 'James D. Best')"); statement.executeUpdate("insert into AUTHORS values (102, 'Lisa Belkin')"); statement.executeUpdate("insert into AUTHORS values (103, 'Chris Fabry')"); statement.executeUpdate("insert into AUTHORS values (104, 'Gary Braver')"); |
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
1 2 3 4 5 6 7 8 |
String query = "SELECT NAME, PRICE FROM BOOKS"; ResultSet rs = statement.executeQuery(query); while (rs.next()) { String s = rs.getString("NAME"); float n = rs.getFloat("PRICE"); System.out.println(s + " " + n); } |
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
1 2 3 4 5 6 7 8 9 |
PreparedStatement updateStatement = connection.prepareStatement("UPDATE BOOKS SET PRICE = ? WHERE AUTHOR_ID = ?"); updateStatement.setInt(1, 20); updateStatement.setInt(2, 103); updateStatement.executeUpdate(); updateStatement.setInt(1, 25); updateStatement.setInt(2, 102); updateStatement.executeUpdate(); |
1 2 3 4 5 6 7 |
query = "SELECT BOOKS.NAME, AUTHORS.NAME " + "FROM BOOKS, AUTHORS " + "WHERE BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID"; rs = statement.executeQuery(query); while (rs.next()) { String bookName = rs.getString("BOOKS.NAME"); String authorName = rs.getString("AUTHORS.NAME"); System.out.println(bookName + " by " + authorName); } |
If you don’t know what is meant by transaction you may want read this from Wikipedia first Database transaction.
JDBC Connection created with auto-commit mode on, so you need to switch it off, do your work, then commit it.
1 2 3 4 5 6 7 8 9 10 11 12 |
connection.setAutoCommit(false); PreparedStatement updateStatement = connection.prepareStatement("UPDATE BOOKS SET PRICE = ? WHERE AUTHOR_ID = ?"); updateStatement.setInt(1, 20); updateStatement.setInt(2, 103); updateStatement.executeUpdate(); updateStatement.setInt(1, 25); updateStatement.setInt(2, 102); updateStatement.executeUpdate(); connection.commit(); connection.setAutoCommit(true); |
Unlike the regular ResultSet, in a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = statement.executeQuery("SELECT NAME, PRICE FROM BOOKS"); rs.absolute(1); // Move Cursor to first element System.out.println(rs.getString("NAME")); rs.absolute(5); // Move Cursor to fifth element System.out.println(rs.getString("NAME")); // Move Cursor to first element based on the previous cursor // location which is 5, so 5-4=1 rs.relative(-4); System.out.println(rs.getString("NAME")); rs.next(); System.out.println(rs.getString("NAME")); rs.previous(); System.out.println(rs.getString("NAME")); |
NOTE: I encourage you to go to the official API and read more about this. ResultSet Official API
If you want the eclipse project that contains all this code you can download it from Here
I can see you are an expert at your field! I am launching a web site soon, and your facts will be very useful for me.. Thanks for all your assist and wishing you all the success.
You are welcome
Cool post. Hopin’ for more. 🙂
hey 🙂 what design are you utilizing for this one in particular? (this actual blog) I kinda love this and I was questioning if this can be accessible.
I’m using Suffusion theme and you can get it from
http://wordpress.org/extend/themes/suffusion (It’s a free one 😉 )