Java Database Connectivity: A Comprehensive Guide, Slides of Java Programming

Download Java Database Connectivity: A Comprehensive Guide and more Slides Java Programming in PDF only on Docsity!

Java

ava - programming

An Introduction

Java Short Course

Day-

J

Java

Today’s Lecture

o Java Database Connectivity

 Concepts

 JDBC API

 Data Base Connectivity using Java

 Important Features of JDBC API

Java

Java

Java

Java

Connecting to Database

o Import

 import java.sql.*;

 import sun.jdbc.*;

o Declare variables

 Connection conn;

 Statement stm;

 ResultSet records;

o Load Driver

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Java

Exceptions

o The following methods throws exceptions and these must be

handled

 Methods are

 Class.forName  DriverManager.getConnection  Statement.executeQuery

o Exceptions are

 ClassNotFoundException

 SQLException

Java

Complete Code-

o package connection;

o import java.sql.; o import sun.jdbc.;

o public class DBTest {

o Connection conn; o Statement stm; o ResultSet records; o o public DBTest() { o }

o public void connect(){ o try { o Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); o conn=DriverManager.getConnection("jdbc:odbc:mydb","",""); o stm=conn.createStatement(); o } o catch (ClassNotFoundException e){System.out.println("Cannot Load Driver"+e);} o c atch (SQLException e){System.out.println("Error while connecting"+e);} o }

Java

Output

o ---------------------------------------

o Employ ID: 1

o Employ Name : nauman

o City : Islmabad

o HomePhone :

o EmailAddress: nauman@pieas.edu.pk

o ---------------------------------------

o Employ ID: 2

o Employ Name : aslam

o City : Karachi

o HomePhone :

o EmailAddress: aslam@pieas.edu.pk

o ---------------------------------------

o Employ ID: 3

o Employ Name : anwar

o City : Lahore

o HomePhone :

o EmailAddress: anwar@pieas.edu.pk

Java

DataBase Information

o System-wide data

 – connection.getMetaData().getDatabaseProductName()

 – connection.getMetaData().getDatabaseProductVersion()

o • Table-specific data

 – resultSet.getMetaData().getColumnCount()

o • When using the result, remember that

 the index starts at 1, not 0

 – resultSet.getMetaData().getColumnName()

Java

Statement Object

o Overview

 – Through the Statement object, SQL statements are sent to the

database.

o Three types of statement objects are available:

 Statement

 For executing a simple SQL statement

 PreparedStatement

 For executing a precompiled SQL statement passing in parameters

 CallableStatement

 For executing a database stored procedure

[slide taken from internet]

Java

Useful Mthods

o executeQuery

 Executes the SQL query and returns the data in a table (ResultSet)

 The resulting table may be empty but never null

 ResultSet results =statement.executeQuery("SELECT a, b FROM table");

o executeUpdate

 Used to execute for INSERT, UPDATE, or DELETE SQL statements

 The return is the number of rows that were affected in the database

 Supports Data Definition Language (DDL) statements CREATE TABLE,

DROP TABLE and ALTER TABLE

 int rows = statement.executeUpdate("DELETE FROM EMPLOYEES" + "WHERE STATUS=0");

[slide taken from internet]

Java

Prepared Statement

o Idea

 If you are going to execute similar SQL statements multiple times, using

“prepared” (parameterized) statements can be more efficient

 Create a statement in standard form that is sent to the database for

compilation before actually being used

 Each time you use it, you simply replace some of the marked

parameters using the setXxx methods

o As PreparedStatement inherits from Statement the corresponding

execute methods have no parameters

 execute()

 executeQuery()

 executeUpdate()

Java

Example Code

 Connection connection = DriverManager.getConnection(url, user, password);

 PreparedStatement statement = connection.prepareStatement("UPDATE

employees "+ "SET salary =? " +"WHERE id = ?");

 int[] newSalaries = getSalaries();

 int[] employeeIDs = getIDs();

 For (int i=0; i<employeeIDs.length; i++) {

 statement.setInt(1, newSalaries[i]);

 statement.setInt(2, employeeIDs[i]);

 statement.executeUpdate();

 }