JDBC By Mahesh Sarangapani

Slide 1: 

Mahesh Sarangapani(7204408752) - JDBC - Java Database Connectivity Mahesh Sarangapani 7204408752 maheshsthattil2007@gmail.com

JDBC: 

Mahesh Sarangapani(7204408752) JDBC JDBC is a standard interface for connecting to relational databases from Java. The JDBC classes and interfaces are in the java.sql package.

Overview of Querying a Database With JDBC: 

Mahesh Sarangapani(7204408752) Query Close Connect Process results Overview of Querying a Database With JDBC

Stage 1: Connect: 

Mahesh Sarangapani(7204408752) Query Close Connect Process results Register the driver Connect to the database Stage 1: Connect

A JDBC Driver: 

Mahesh Sarangapani(7204408752) Is an interpreter that translates JDBC method calls to database commands Implements interfaces in java.sql Driver JDBC calls Database commands Database A JDBC Driver

How to Make the Connection: 

Mahesh Sarangapani(7204408752) Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection (jdbc:odbc:DataSourceName); How to Make the Connection 1. Register the driver. 2. Connect to the database.

Stage 2: Query: 

Mahesh Sarangapani(7204408752) Close Connect Query Create a statement Process results Query the database Stage 2: Query

How to Query the Database: 

Mahesh Sarangapani(7204408752) Statement stmt = conn .createStatement(); ResultSet rs = stmt .executeQuery( query ); stmt .executeUpdate( query ); stmt .execute( query ); How to Query the Database 1. Create an empty statement object. 2. Execute the statement.

The Statement Object: 

Mahesh Sarangapani(7204408752) The Statement Object A Statement object sends your SQL statement to the database. You need an active connection to create a JDBC statement. Statement has three methods to execute a SQL statement: executeQuery() for QUERY statements executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements execute() for either type of statement

Querying the Database: Examples: 

Mahesh Sarangapani(7204408752) Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from login"); Statement stmt = conn.createStatement(); stmt.executeUpdate("delete from login where id=100"); Querying the Database: Examples Execute a select statement. Execute a delete statement.

Stage 3: Process the Results: 

Mahesh Sarangapani(7204408752) Close Query Step through the results Process results Assign results to Java variables Connect Stage 3: Process the Results

How to Process the Results: 

Mahesh Sarangapani(7204408752) while ( rs .next()) { … } String val = rs .getString( colname ); while (rs.next()) { String name = rs.getString(“name"); String password = rs.getString(“password"); } String val = rs .getString( colIndex ); How to Process the Results 1. Step through the result set. 2. Use get---() to get each column value.

The ResultSet Object: 

Mahesh Sarangapani(7204408752) The ResultSet Object JDBC returns the results of a query in a ResultSet object. A ResultSet maintains a cursor pointing to its current row of data. Use next() to step through the result set row by row. getString(), getInt(), and so on assign each value to a Java variable.

Slide 14: 

Mahesh Sarangapani(7204408752) ResultSet rs = stmt.executeQuery("select * from login"); int id = rs.getInt(1); String name = rs.getString(2); String password = rs.getString(3); Col Name id name password Type NUMBER VARCHAR VARCHAR Mapping Database Types to Java Types ResultSet maps database types to Java types.

Stage 4: Close: 

Mahesh Sarangapani(7204408752) Connect Query Process results Close Close the result set Close the statement Close the connection Stage 4: Close

How to Close the Connection: 

Mahesh Sarangapani(7204408752) 1. Close the ResultSet object. 2. Close the Statement object. 3. Close the connection (not necessary for server-side driver). rs .close(); stmt .close(); conn .close(); How to Close the Connection

The PreparedStatement Object: 

Mahesh Sarangapani(7204408752) The PreparedStatement Object A PreparedStatement object holds precompiled SQL statements . Use this object for statements you want to execute more than once . A prepared statement can contain variables that you supply each time you execute the statement.

How to Create a Prepared Statement: 

Mahesh Sarangapani(7204408752) How to Create a Prepared Statement 1.Register the driver and create the database connection. 2.Create the prepared statement, identifying variables with a question mark (?). PreparedStatement ps = conn.prepareStatement(“select * from login where id=?");

How to Execute a Prepared Statement: 

Mahesh Sarangapani(7204408752) How to Execute a Prepared Statement 1. Supply values for the variables. 2. Execute the statement. ps .set---( index, value ); ps .executeQuery(); ps .executeUpdate(); PreparedStatement ps =conn.prepareStatement ("update login set password = ? where id = ?"); ps.setString(1, “raindrops"); ps.setInt(2, 5); ps.executeUpdate();

Sample Programs: 

Mahesh Sarangapani(7204408752) Sample Programs

Slide 21: 

Mahesh Sarangapani(7204408752) import java.sql.*; public class jdbcDemo { public static void main(String a[]) { int id; String name,password; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=DriverManager.getConnection("jdbc:odbc:test"); Statement st=con.createStatement(); ResultSet rs=st.executeQuery("select * from login"); while(rs.next()) { id=rs.getInt("id"); name=rs.getString("name"); password=rs.getString("password"); System.out.println(id+" "+name+" "+password); } } catch(Exception e) { System.out.println(e); } } }

Slide 22: 

Mahesh Sarangapani(7204408752) import java.sql.*; public class jdbcDemo2 { public static void main(String a[]) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con=DriverManager.getConnection("jdbc:odbc:test"); PreparedStatement ps=con.prepareStatement("update login set password=? where id=?"); ps.setString(1,"rainbow"); ps.setInt(2, 2); ps.executeUpdate(); } catch(Exception e) { System.out.println(e); } } }

Thank You: 

Mahesh Sarangapani(7204408752) Thank You Mahesh Sarangapani 7204408752 maheshsthattil2007@gmail.com