1 / 18

MySQL Application Using JDBC

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.

Download Presentation

MySQL Application Using JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related