JDBC What is JDBC JDBC is an acronym
JDBC
What is JDBC • JDBC is an acronym for – Java Data Base Connectivity. • It allows java/jsp program to connect to any database
How to use JDBC Step 1: First make a database Step 2: Create tables Step 3: Create data source
JDBC library in Java • To use JDBC in java, you need to know about – Connection – Statement – Result. Set – Driver. Manager All these classes are provided in pacakkage java. sql. *.
Steps to use JDBC in Java • • • Initialize variables Register or Load Driver Open a Connection Execute Sql Statement Close the Connection
Initialize Variables You need following variables Connection con; Statement stmt; Initialize variable to value null Connection con = null; Statement stmt = null;
Load Driver To load driver you need to use Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); sun. jdbc. odbc. Jdbc. Odbc. Driver is the name of driver. Class. for. Name() function loads the driver in memory.
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
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
Create Connection Contd • After loading driver and creating DSN, next step is to create a connection. • To create a connection conn = Driver. Manager. get. Connection("jdbc: odbc: cdb"); Driver. Manager class get. Connection functions takes DSN as input argument And returns an object of type Connection
Create Statement • After creating connection, you need to create statement Statement stmt = conn. create. Statement(); conn is a connection object created in previous slide
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
Insert or update or delete record To insert record in database, use method execute. Update of statement class. int i = stmt. execute. Update("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.
Get record from database • To retrieve or get record from database execute. Query() method is used. Result. Set rs = stmt. execute. Query(“Select lname, fname from personal"); The method execute. Query() takes “select” statement as input. The method execute. Query() returns an object of type Result. Set.
Complete Program for Inserting a record in Database <%@ page import="java. sql. *" %> <% Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection conn = Driver. Manager. get. Connection("jdbc: odbc: cdb"); Statement stmt = conn. create. Statement(); %>
Contd…. • String lname = request. get. Parameter("fname"); • String fname = request. get. Parameter("lname"); • String sex = request. get. Parameter("sex"); • String salary = request. get. Parameter("salary"); • String exp = request. get. Parameter("experience");
Contd…. • i = stmt. execute. Update("INSERT INTO personal VALUES('"+lname+"', '"+fname+"', '"+sex+"', '"+salary+"', '"+e xp+"')"); • if (i ==1) • • • { System. out. println("success"); //response. send. Redirect("conjoint. html"); } else { System. out. println("There has been some problem in saving your personal information. Kindly fill the personal information again"); • }
Complete Program for Retrieving records from query <%@ page import="java. sql. *" %> Connection conn = null; Statement stmt = null; Result. Set rs = null;
Contd…. Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc Driver"); conn = Driver. Manager. get. Connection("jdbc: odbc: cdb"); stmt = conn. create. Statement();
Contd…. • rs = stmt. execute. Query("Select fname, lname from personal"); • while(rs. next()) • { System. out. println(rs. get. String(1) + " " +rs. get. String(2)); • // rs. next(); • } • %>
- Slides: 20