Jdbc

JDBC

What is JDBC • JDBC is an acronym for – Java Data Base Connectivity.

• It allows java program to connect to any database

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

JDBC library in Java • To use JDBC in java, you need – Connection – Statement – ResultSet – DriverManager 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.forName("sun.jdbc.odbc.JdbcOdbcDriver");

sun.jdbc.odbc.JdbcOdbcDriver is the name of driver. Class.forName() 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 = DriverManager.getConnection("jdbc:odbc:cdb"); DriverManager class getConnection functions takes DSN as input argument And returns an object of type Connection

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 executeUpdate of statement class. int i = stmt.executeUpdate("INSERT INTO personal VALUES('"+lname+"','"+fname+"','"+sex+"','"+salary+"','"+exp+"')"); This method takes as input “Insert” query statemetn. 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 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.

Complete Program for Inserting a record in Database • •

import javax.swing.*; import java.sql.*;

• • • • • • • • • • • • • • • • •

class JdbcExample { public static void main(String[] args) { Connection conn = null; Statement stmt = null; int i = 0; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection("jdbc:odbc:cdb"); stmt = conn.createStatement(); } catch (Exception e) { System.out.println("Error in conection"); }

Contd…. •

String lname = JOptionPane.showInputDialog("Ebter your last name");

•

String fname = JOptionPane.showInputDialog("Ebter your first name");

•

String sex = JOptionPane.showInputDialog("Ebter your sex male/female");

•

String salary = JOptionPane.showInputDialog("Ebter your salary");

•

String exp = JOptionPane.showInputDialog("Ebter number of years of experience");

•

System.out.println("There has been some problem in saving your personal information. Kindly fill the personal information again"); } } }

• • •

Contd…. • • • • • • • • • • • • • • • • • •

try { i = stmt.executeUpdate("INSERT INTO personal VALUES('"+lname+"','"+fname+"','"+sex+"','"+salary+"','"+exp+"')"); } catch (Exception e) { System.out.println("Exception"); } 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"); } } }

Complete Program for Retrieving records from query • import javax.swing.*; • import java.sql.*; • class JdbcExample • { • public static void main(String[] args) • { • Connection conn = null; • Statement stmt = null; • ResultSet rs = null; • int i = 0;

Contd…. • • • • • • • • • •

try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection("jdbc:odbc:cdb"); stmt = conn.createStatement(); } catch (Exception e) { System.out.println("Error in conection"); }

Contd…. • • • • • • • • • • • • • • •

try { rs = stmt.executeQuery("Select fname, lname from personal"); while(rs.next()) { System.out.println(rs.getString(1) + " " +rs.getString(2)); // rs.next(); } } catch (Exception e) { System.out.println("Exception"); } } }

JDBC: Prepared Statements (1) • But query compilation takes a (relatively) long time! • This example is therefore inefficient. int[] students = {1, 2, 4, 7, 9}; for (int i = 0; i < students.length; ++i) { ResultSet rs = stmt.executeQuery("SELECT * " + "FROM STUDENT WHERE sid = " + students[i]); while (rs.next()) { … }

20

JDBC: Prepared Statements (2) • To speed things up, prepare statements and bind arguments to them • This also means you don’t have to worry about escaping strings, formatting dates, etc. – Problems with this lead to a lot of security holes (SQL injection) – Suppose a user inputs the name “O’Reilly”

PreparedStatement stmt = conn.prepareStatement("SELECT * " + "FROM STUDENT WHERE sid = ? "); int[] students = {1, 2, 4, 7, 9}; for (int i = 0; i < students.length; ++i) { stmt.setInt(1, students[i]); ResultSet rs = stmt.executeQuery(); while (rs.next()) { … }

21

PreparedStatement •

The contained SQL is sent to the database and compiled or prepared beforehand

•

From this point on, the prepared SQL is sent and this step is bypassed. The more dynamic Statement requires this step on every execution.

•

Depending on the DB engine, the SQL may be cached and reused even for a different PreparedStatement and most of the work is done by the DB engine rather than the driver

PreparedStatement cont. •

A PreparedStatement can take IN parameters, which act much like arguments to a method, for column values.

•

PreparedStatements deal with data conversions that can be error prone in straight ahead, built on the fly SQL handling quotes and dates in a manner transparent to the developer

PreparedStatement Steps 1. You register the drive and create the db connection in the usual manner. 2. Once you have a db connection, create the prepared statement object • • • • •

PreparedStatement updateSales = con.prepareStatement(“UPDATE OFFER_TBL SET QUANTITY = ? WHERE ORDER_NUM = ? "); // “?” are referred to as Parameter Markers // Parameter Markers are referred to by number, starting from 1, in left to right order. // PreparedStatement's setXXX() methods are used to set the IN parameters, which remain set until changed.

PreparedStatement Steps cont. 3. Bind in your variables. The binding in of variables is positional based updateSales.setInt(1, 75); updateSales.setInt(2, 10398001); 4. Once all the vairables have been bound, then you execute the prepared statement int iUpdatedRecords = updateSales.executeUpdate();

PreparedStatement cont. •

If the prepared statement object is a select statement, then you execute it, and loop through the result set object the same as in the Basic JDBC example:

PreparedStatement itemsSold = con.prepareStatement("select o.order_num, o.customer_num, c.name, o.quantity from order_tbl o, customer_tbl c where o.customer_num =c.customer_num and o.customer_num = ?;"); itemsSold.setInt(1,10398001); ResultSet rsItemsSold = itemsSold.executeQuery(); while (rsItemsSold.next()){ System.out.println( rsItemsSold.getString(“NAME") + "sold "+ rsItemsSold.getString(“QUANTITY") + " unit(s)"); }

Read ResultSet MetaData and DatabaseMetaData •

Once you have the ResultSet or Connection objects, you can obtain the Meta Data about the database or the query

•

This gives valuable information about the data that you are retrieving or the database that you are using

– ResultSetMetaData rsMeta = rs.getMetaData(); – DatabaseMetaData dbmetadata = connection.getMetaData(); There are approximately 150 methods in the DatabaseMetaData class.

ResultSetMetaData Example ResultSetMetaData meta = rs.getMetaData(); //Return the column count int iColumnCount = meta.getColumnCount(); for (int i =1 ; i <= iColumnCount ; i++){ System.out.println(“Column Name: " + meta.getColumnName(i)); System.out.println(“Column Type" + meta.getColumnType(i)); System.out.println("Display Size: " + meta.getColumnDisplaySize(i) ); System.out.println("Precision: " + meta.getPrecision(i)); System.out.println(“Scale: " + meta.getScale(i) );

}