5 JDBC

JDBC Overview What is a JDBC JDBC stands for Java Database Connectivity, which is a standard Java API for database

independent connectivity between the Java programming language and a wide range of databases. The JDBC API supports both two

tier and three

tier processing models for database access but in general, JDBC Architecture consists of two layers − § JDBC API : This provides the application

to

JDBC Manager connection. § JDBC Driver API : This supports the JDBC Manager

to

Driver Connection.

SQL Commands we will use § Create § Drop § Insert § Select

Database Connections ̈ Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code. ̈ Database URL Formulation: This is to create a properly formatted address that points to the database to which you wish to connect. ̈ Create Connection Object: Finally, code a call to the DriverManager object's getConnection ( ) method to establish actual database connection.

Statement Object ̈ boolean execute (String SQL): Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL. ̈ int executeUpdate (String SQL): Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected

for example, an INSERT, UPDATE, or DELETE statement. ̈ ResultSet executeQuery (String SQL): Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement

Result Sets The SELECT statement is the standard way to select rows from a database and view them in a result set. The java.sql.ResultSet interface represents the result set of a database query. ̈ Navigational methods: Used to move the cursor around. ̈ Get methods: Used to view the data in the columns of the current row being pointed by the cursor. ̈ Update methods: Used to update the data in the columns of the current row. The updates can then be updated in the underlying database as well.

Open a Connection static final String DB_URL = " jdbc:derby:CoffeeDB ;" // Create a connection to the database. Connection conn = DriverManager.getConnection (DB_URL); // Database credentials static final String USER = "username"; static final String PASS = "password";

Open a Connection try{
//Open a connection conn =
DriverManager.getConnection (DB_URL, USER,PASS);

Execute a query stmt

conn.createStatement (); String sql ; sql = "SELECT ProdNum , Description,
Price FROM Coffee"; ResultSet rs

stmt.executeQuery ( sql );

Extract data from result set while( rs.next ()){ //Retrieve by column name String id = rs.getString (" ProdNum "); double price = rs.getDouble ("Price"); String description = rs.getString ("Description"); //Display values System.out.print ("ID: " + id.trim ()); System.out.print (", Price: " + price); System.out.println (", Desc : " + description);

Clean

up environment rs.close (); stmt.close (); conn.close ();