Slide 1

UNIT- IVCHAPTER IN BOOK- 4jdbcpart- iV-K. IndhuSYLLABUS COVERED HEREBasic JDBC Programming conceptsPopulating a databaseExecuting QueriesScrollable and Updateable Result SetsGoalsJDBC Classes & InterfacesBasic JDBC ConceptsJDBC Components InteractionBasic Steps to use Database in JavaSeven Steps to use Database in Java thru JDBCDSN-less Connectionget() Method of Result Setexecute() Method of Statement ObjectSQL Java Mapping Data TypesPopulating a Database Executing QueriesScrollable Result SetsConstants in Scrollable Result SetsUpdateable Result SetsJDBC PreparedStatement

Jdbc class & interfacesIN java.SQL PACKAGE =>DriverManager CLASS->> Manages a list of Database Drivers.> Matches connection requests from the java application with the proper database driver using communication sub-protocol.

Driver INTERFACE->> Handles communication with Database Server.> Interacting directly with Driver objects very rarely, instead Interacting with DriverManager objects.

Connection INTERFACE->> Contains all methods for contacting a database.Jdbc class & interfacesIN java.SQL PACKAGE =>4. Statement INTERFACE->> Used to submit & Execute SQL statements in to database.

5. ResultSet INTERFACE->> Used to hold data(output) retrieved from a database> after executing SQL query using Statement objects.> It acts as Iterator to allow moving through its data.

SQLException CLASS->> Handles errors if they occur in a database application.Basic Jdbc conceptsDriver Manager-> Loads database drivers and manages connection between the application and the driver.> Driver Manager manages (2) & (3).

Driver-> Translates API calls into operations for a specific data source.

Connection-> A session between an application and a database.Basic Jdbc conceptsStatement-> A SQL Statement to perform a query or update operation.

Metadata-> Information about returned data, database and driver.

ResultSet-> Logical set of columns and rows returned by executing an SQL statement (resulting tuples).Jdbc components interaction

Basic steps to use Database in JavaImport the necessary Package-> "java.sql"Load JDBC driverIdentify Database Source Name (DSN)Allocate a Connection object (create)Allocate a Statement object (create)Execute a query using the Statement objectRetrieve data from the returned ResultSet objectCommit the "Connection" objectClose the Connection objectBasic steps to use Database in Java

1. Loading driverRegistering the driver directly automatically:-> Class.forName(org.oracle.Driver");

Calling Class.forName, which-> automatically creates an instance of the driver,> registers the driver with the DriverManager.2. Identify data sourceGives required information for making Connection to WHICHEVER Database.Specified using the URL format.: :EXAMPLE:-jdbc:oracle://foo.itu.edu/mydatabasejdbc:oracle://localhost/testdb3. Creating a connectionConnection connection = DriverManager.getConnection("jdbc:oracle://localhost/moviedb","testuser","mypassword");

SYNTAX->Connection getConnection(String url, String user, String password) => is a Static Method that Connects to given JDBC URL with username & password Throws java.sql.SQLExceptionreturns a Connection object4. Creating a statementStatement stmt = con.createStatement();Creates a Statement object for sending SQL statements to the database.5. Executing the statementString createEmployee = "Create table Employee " + "(ID Integer not null, Name VARCHAR(32))";int returnCode = stmt.executeUpdate(createEmployee);

String insertEmployee = "Insert into Employee values (342327,John)";int returnCode = stmt.executeUpdate(insertEmployee);

String strSelect = "select RegNo, StudentName, BloodGroup from Students";ResultSet rset = stmt.executeQuery(strSelect);6. Get result-setString strSelect = "select RegNo, StudentName, BloodGroup from Students";

ResultSet rset = stmt.executeQuery(strSelect);

while(rset.next()){int RegNo = rset.getInt("RegNo");String StudentName = rset.getString("StudentName");String BloodGroup = rset.getString("BloodGroup");}7. commit & close connectionconn.commit();conn.close();DSN-LESS connection

Get() method of resultsetEach getxxx() method> will make reasonable type conversions> when data-type of the method doesn't match data-type of the column.

For example, the call-> rs.getString("Price")> converts float value of Price column to string.execute() methodsResultSet executeQuery(String sql)> Executes SQL statement in "sql & returns ResultSet.> "sql" will be "select" statement.

int executeUpdate(String sql)> Executes INSERT, UPDATE or DELETE statement specified in "sql".> Executes Data Definition Language(DDL) statements such as CREATE TABLE.> Returns number of records affected, or -1 if not affected.

execute() methodsboolean execute(String sql)> Executes SQL statement specified in "sql".> Returns true if statement returns a result set, Else false.

int getUpdateCount()> Returns no. of records affected by the preceding update statement, or -1 if not updated.

ResultSet getResultSet()> Returns Result Set of preceding query statement.Sql, java data types

Populating a databaseCREATE TABLE Publisher ( Publisher_Id char(5), Name char(30), URL char(80) )- executeUpdate()

INSERT INTO Publisher VALUES ('01262', 'AcademicPress', 'www.apnet.com')- executeUpdate()INSERT INTO Publisher VALUES ('18835', 'Coriolis','www.coriolis.com/')- executeUpdate()Executing queriesSELECT * FROM Publisher- executeQuery()

UPDATE Publisher SET Name = Indian WHERE Publisher_Id = 01262 executeUpdate()

DELETE FROM Publisher WHERE Publisher_Id = 18835 executeUpdate()Scrollable result setStatement stmt =con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

String query=select students from class where type=not sleeping ;ResultSet rs = stmt.executeQuery( query );

rs.previous();//go back in the RS (not possible in JDBC 1)rs.relative(-5);//go 5 records backrs.relative(7);//go 7 records forwardrs.absolute(100);//go to 100th recordScrollable result set

Creating scrollable result set

Creating scrollable result set

Constants in result sets

Constants in result sets

updateable result setStatement stmt =con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);String query = " select students, grade from class where type=really listening this presentation ;ResultSet rs = stmt.executeQuery( query );while ( rs.next() ){int grade = rs.getInt(grade);rs.updateInt(grade, grade+10);rs.updateRow();}updateable result set

updateable result set

updateable result setStatement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);ResultSet rs = stmt.executeQuery(select a, b from Table2");//rs will be scrollable, not show changes made by others & be //updatable

rs.absolute(5); // moves the cursor to the fifth row of rs.rs.updateString(StudentName", "AINSWORTH");// updates NAME column of row 5 to be AINSWORTHrs.updateRow(); // updates the row in the data source

updateable result setrs.moveToInsertRow(); //moves cursor to the insert rowrs.updateString(1, "WORTH");//update 1st column of ins. row to WORTHrs.updateInt(2,35); //updates second column to be 35rs.updateBoolean(3, true); //updates third column to truers.insertRow();rs.moveToCurrentRow();Jdbc prepareDstatementJDBC PreparedStatement can be used when we plan to use Same SQL Statement many times.

It is used to handle precompiled query.

If we want to execute same query with different values for more than one time then precompiled queries will reduce the no of compilations.

Connection.prepareStatement() method provides PreparedStatment object.

PreparedStatement Object provides setXXX() methods to provide query values.Jdbc prepareDstatement

Jdbc prepareDstatement

Jdbc prepareDstatement

So far we studiedBasic JDBC Programming conceptsPopulating a databaseExecuting QueriesScrollable and Updateable Result SetsK. INDHUHAPPY LEARNING!!!