Efficient Database Interaction with JDBC in Java Applications

1. MySQL Application Using JDBC • JDBC is an API that allows a Java program to communicate with a database server using SQL commands. • JDBC API can be found at java.sql package • java.sql package provides a straightforward way to send SQL queries and receive results from the database server MySQL In Action

2. Accessing Database • There are four most important techniques in JDBC programming. • Registering a database driver • Obtaining a database connection • Sending SQL statements to the database server • Retrieving results from the database server MySQL In Action

3. Registering A Database Driver • Each database has its own way to keep track all the data in its database structure. • Therefore, in order to make an application work for a certain database program, the application programmer has to register a driver program. • In JDBC, registering a driver can be accomplish by using forName function from Class class. • Class.forName( driver name ) MySQL In Action

4. Registering A Database Driver (Cont.) • Example: • Referring to the program listing, mySQL database that I used has the following driver: • twz1.jdbc.mysql.jdbcMysqlDriver from twz1.jdbc.mysql package // This is the driver to MySql database. String driver = "twz1.jdbc.mysql.jdbcMysqlDriver"; // If the driver is specified, register it so that we // can connect and execute the program. if (driver != null) Class.forName(driver); MySQL In Action

5. Obtaining A Database Connection • We can obtain a database connection by using DriverManager class to get a Connection object that represent a database connection. • getConnection function will do it for us. • getConnection( url , user, password ) • url is the url to the database server • jdbc:subprotocol://host:port/databasename • user is user login name • password is the user password MySQL In Action

6. Obtaining A Database Connection (Example) • The database that I created is called video_rental and it is running on cochise on port 3306 • Therefore, I used the following code to get a Connection object in my program. String url = “jdbc:z1MySQL://cochise.cs.washington.edu:3306/video_rental"; // Login name and password of the user. String user = ”"; String password = ”"; Connection conn = DriverManager.getConnection(url, user, password); MySQL In Action

7. Sending SQL Statement • After getting a Connection object, we can use createStatement() method to create an object that implements the Statement interface. • executeQuery() is used to send the SQL queries • executeUpdate() is used to update the database • execute() is used to send a statement that can be a query or an update MySQL In Action

8. Sending SQL Statement (Example) • In my program, I used execute() method to make the program more flexible to the user inputs. • Syntax: execute( statement ) • execute() method will return TRUE if the statement is a query and FALSE if the statement is an update. boolean status = s.execute(sql); if (status) // execute the query else // execute the update MySQL In Action

9. Retrieving Results From The Server • We can use getResultSet() method of Statement to retrieve an object that implement the ResultSet interface. • This object will return the values which are organized in rows and columns like a table. • A ResultSet offers its data one row at a time. MySQL In Action

10. Retrieving Results From The Server (Cont.) • next() is used to move from the current row to the next row. • getX() is used to retrieve the data from each column of the current row as a number of different types. MySQL In Action

11. Closing A Database Connection • Before the program is terminated, it should close the database connection. • close() method will do the closing for you. • Usually, close() method is called right before the program termination and also it is called if an exception is thrown by the program. MySQL In Action

12. Understanding The Tutorial Program • Now, you have understood the basic technique used in JDBC programming, let’s go to my program. • Tutorial program uses all of the basic techniques discussed in the previous slides: connecting to the database, execute SQL statements, and display the results. • The program will parse the argument determine the database driver, url, user login name and password. MySQL In Action

13. Understanding The Tutorial Program (Cont.) • For example, you want to invoke the Tutorial program with a statement like the following: • java Tutorial -p ffaizal -u abcd • Notice also that the program uses execute() method to determine if the statement is a query or an update. MySQL In Action

14. Understanding The Tutorial Program (Cont.) • printResultsTable() method is called to display the result of the query. • This method gets ResultSetMetaData object to find out the information returned by the query. • With those information, printResultsTable() method will be able to format the data. MySQL In Action

15. Understanding The Tutorial Program (Cont.) • There are other techniques that you as JDBC programmer to familiarize. • SQLException object supports the standard exception message with getMessage(). • It may also return additional messages that can be obtained by calling getSQLState() method of the Exception object. MySQL In Action

16. Understanding The Tutorial Program (Cont.) • SQLWarning class is a subclass of SQLException. • When a SQL command is executed, any warning returned by server will be stored in a linked-list of SQLWarning object. • getWarnings() of Connection object will get these warning for you. MySQL In Action

17. Understanding The Tutorial Program (Cont.) • getNextWarning() of SQLWarning object can be used to get the additional warning from the linked-list. MySQL In Action

18. Further Reading • Java Examples In A Nutshell by David Flanagan • Database Programming with JDBC and JAVA by George Reese MySQL In Action