JDBC

1. JDBC

2. What is JDBC • JDBC is an acronym for • Java Data Base Connectivity. • It allows java/jsp program to connect to any database

3. How to use JDBC Step1: First make a database Step2: Create tables Step3: Create data source

4. JDBC library in Java • To use JDBC in java, you need to know about • Connection • Statement • ResultSet • DriverManager All these classes are provided in pacakkage java.sql.*.

5. Steps to use JDBC in Java • Initialize variables • Register or Load Driver • Open a Connection • Execute Sql Statement • Close the Connection

6. Initialize Variables You need following variables Connection con; Statement stmt; Initialize variable to value null Connection con = null; Statement stmt = null;

7. Load Driver To load driver you need to use Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); sun.jdbc.odbc.JdbcOdbcDriver is the name of driver. Class.forName() function loads the driver in memory.

8. Create Connection • After loading driver, you need to create a connection • Create connection means, you need to connect your java program with the database. • To connect to a database you must configure the DSN first • DSN is an acronym for Data Source Name

9. What is DSN • DSN is an acronym for Data Source Name • DSN act as an interface between your java application and Database • To create DSN • Open Control Panel • Administrative Tools • ODBC

10. Create Connection Contd • After loading driver and creating DSN, next step is to create a connection. • To create a connection conn = DriverManager.getConnection("jdbc:odbc:cdb"); DriverManager class getConnection functions takes DSN as input argument And returns an object of type Connection

11. Create Statement • After creating connection, you need to create statement Statement stmt = conn.createStatement(); conn is a connection object created in previous slide

12. Execute Query • After connection is created successfully, you need to execute query • There are 2 types of query • That gets record from database • That insert or update or delete record

13. Insert or update or delete record To insert record in database, use method executeUpdate of statement class. int i = stmt.executeUpdate("INSERT INTO personal VALUES('"+lname+"','"+fname+"','"+sex+"','"+salary+"','"+exp+"')"); This method takes as input “Insert” query statement. This method returns 1 (success) or 0 (failure). You can replace “Insert” query statemetn with update or delete query statement.

14. Get record from database • To retrieve or get record from database executeQuery() method is used. ResultSet rs = stmt.executeQuery(“Select lname, fname from personal"); The method executeQuery() takes “select” statement as input. The method executeQuery() returns an object of type ResultSet.

15. Complete Program for Inserting a record in Database <%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection("jdbc:odbc:cdb"); Statement stmt = conn.createStatement(); %>

16. Contd…. • String lname = request.getParameter("fname"); • String fname = request.getParameter("lname"); • String sex = request.getParameter("sex"); • String salary = request.getParameter("salary"); • String exp = request.getParameter("experience");

17. Contd…. • i = stmt.executeUpdate("INSERT INTO personal VALUES('"+lname+"','"+fname+"','"+sex+"','"+salary+"','"+exp+"')"); • if (i ==1) • { • System.out.println("success"); • //response.sendRedirect("conjoint.html"); • } • else • { System.out.println("There has been some problem in saving your personal information. Kindly fill the personal information again"); • }

18. Complete Program for Retrieving records from query <%@ page import="java.sql.*" %> Connection conn = null; Statement stmt = null; ResultSet rs = null;

19. Contd…. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection("jdbc:odbc:cdb"); stmt = conn.createStatement();

20. Contd…. • rs = stmt.executeQuery("Select fname, lname from personal"); • while(rs.next()) • { System.out.println(rs.getString(1) + " " +rs.getString(2)); • // rs.next(); • } • %>