Jdbc tutorial - jdbc reference PDF

Preview

Summary

jdbc reference...

Description

About the Tutorial JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.

Audience This tutorial is designed for Java programmers who would like to understand the JDBC framework in detail along with its architecture and actual usage.

Prerequisites Before proceeding with this tutorial, you should have a good understanding of Java programming language. As you are going to deal with RDBMS, you should have prior exposure to SQL and Database concepts.

Copyright & Disclaimer  Copyright 2015 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at [email protected]

i

Table of Contents About the Tutorial ..................................................................................................................................... i Audience .................................................................................................................................................... i Prerequisites .............................................................................................................................................. i Copyright & Disclaimer ..............................................................................................................................i Table of Contents ......................................................................................................................................ii

1. INT INTROD ROD RODUCTIO UCTIO UCTION N..................................................................................................................... 1 What is JDBC? ........................................................................................................................................... 1 Pre-Requisite ............................................................................................................................................ 1 JDBC Architecture ..................................................................................................................................... 1 Common JDBC Components ...................................................................................................................... 2 The JDBC 4.0 Packages ..............................................................................................................................3

2. SQL SYN SYNTA TA TAX X .......................................................................................................................... 4 Create Database ....................................................................................................................................... 4 Drop Database ..........................................................................................................................................4 Create Table..............................................................................................................................................4 Drop Table ................................................................................................................................................ 5 INSERT Data ..............................................................................................................................................5 SELECT Data ..............................................................................................................................................5 UPDATE Data ............................................................................................................................................6 DELETE Data .............................................................................................................................................. 6

3. ENVI ENVIRONM RONM RONMEN EN ENTT ..................................................................................................................... 8 Install Java ................................................................................................................................................8 Install Database ........................................................................................................................................ 8 Install Database Drivers ............................................................................................................................9

ii

Set Database Credential............................................................................................................................9 Create Database ....................................................................................................................................... 9 Create Table............................................................................................................................................ 10 Create Data Records ...............................................................................................................................11

4. SAM SAMPLE PLE C CODE ODE ..................................................................................................................... 12 Creating JDBC Application ....................................................................................................................... 12 Sample Code ........................................................................................................................................... 12

5. DRI DRIVER VER TYPE TYPESS...................................................................................................................... 16 What is JDBC Driver? ...............................................................................................................................16 JDBC Drivers Types .................................................................................................................................. 16 Which Driver should be Used? ................................................................................................................ 19

6. CON CONNE NE NECTI CTI CTIONS ONS .................................................................................................................... 20 Import JDBC Packages ............................................................................................................................. 20 Register JDBC Driver ...............................................................................................................................20 Database URL Formulation .....................................................................................................................22 Create Connection Object ....................................................................................................................... 22 Closing JDBC Connections .......................................................................................................................24

7. STATE STATEMEN MEN MENTS TS ...................................................................................................................... ......................................................................................................................25 25 The Statement Objects ...........................................................................................................................25 The PreparedStatement Objects ............................................................................................................. 29 Prepare - Example Code .......................................................................................................................... 31 The CallableStatement Objects ............................................................................................................... 34

8. RES RESULT ULT SETS ....................................................................................................................... .......................................................................................................................40 40 Type of ResultSet .................................................................................................................................... 40 Concurrency of ResultSet ........................................................................................................................ 41 iii

Navigating a Result Set ...........................................................................................................................41 Navigate - Example Code ........................................................................................................................43 Viewing a Result Set ...............................................................................................................................46 Viewing - Example Code.......................................................................................................................... 47 Updating a Result Set .............................................................................................................................. 51 Updating - Example Code ........................................................................................................................ 52

9. DAT DATA A TY TYPES PES ........................................................................................................................ ........................................................................................................................58 58 Date & Time Data Types..........................................................................................................................60 Handling NULL Values ............................................................................................................................. 62

10. TRAN TRANSACTI SACTI SACTIONS ONS ................................................................................................................... 63 Commit & Rollback ................................................................................................................................. 63 Commit - Example Code .......................................................................................................................... 64 Using Savepoints ..................................................................................................................................... 68 Savepoints - Example Code ..................................................................................................................... 69

11. EXCE EXCEPTI PTI PTIONS ONS ........................................................................................................................ 74 SQLException Methods ........................................................................................................................... 74

12. BATC BATCH H PPROCE ROCE ROCESSIN SSIN SSING G ............................................................................................................ 78 Batching with Statement Object ............................................................................................................. 78 Batching - Example Code ......................................................................................................................... 79 Batching with PrepareStatement Object ................................................................................................. 83 Batching - Example Code ......................................................................................................................... 85

13. STO STORED RED PPROCE ROCE ROCEDURE DURE ................................................................ .......................................................................................................... .......................................... ..........................................90 90 Creating CallableStatement Object ......................................................................................................... 90 Closing CallableStatement Object: .......................................................................................................... 92 JDBC SQL Escape Syntax .......................................................................................................................... 92 iv

d, t, ts Keywords ..................................................................................................................................... 92 escape Keyword ...................................................................................................................................... 93 fn Keyword .............................................................................................................................................93 call Keyword ...........................................................................................................................................94 oj Keyword.............................................................................................................................................. 94

14. STRE STREAMIN AMIN AMING G DAT DATA A ................................ ............................................................................................................... ............................................................................... 95 15. CRE CREATE ATE DA DATAB TAB TABASE ASE................................................................ ............................................................................................................ ............................................ 100 Required Steps...................................................................................................................................... 100 Sample Code ......................................................................................................................................... 100

16. SELE SELECT CT DA DATAB TAB TABASE ASE................................ ............................................................................................................. ............................................................................. 103 Required Steps...................................................................................................................................... 103 Sample Code ......................................................................................................................................... 103

17. DRO DROP PD DATA ATA ATABASE BASE ............................................................................................................... 106 Required Steps...................................................................................................................................... 106 Sample Code ......................................................................................................................................... 106

18. CRE CREATE ATE TAB TABLE LE LESS ................................................................ ................................................................................................................. ................................................. 109 Required Steps...................................................................................................................................... 109 Sample Code ......................................................................................................................................... 109

19. DRO DROP P TTABL ABL ABLES ES .................................................................................................................... 11 112 2 Required Steps...................................................................................................................................... 112 Sample Code ......................................................................................................................................... 112

20. INSE INSERT RT REC RECORDS ORDS............................................................................................................... 115 Required Steps...................................................................................................................................... 115 Sample Code ......................................................................................................................................... 115

v

21. SELE SELECT CT RE RECOR COR CORDS DS .............................................................................................................. ..............................................................................................................118 118 Required Steps...................................................................................................................................... 118 Sample Code ......................................................................................................................................... 118

22. UPD UPDATE ATE RE RECORD CORD CORDSS ............................................................................................................. 121 Required Steps...................................................................................................................................... 121 Sample Code ......................................................................................................................................... 121

23. DEL DELETE ETE RECO RECORDS RDS .............................................................................................................. 125 Required Steps...................................................................................................................................... 125 Sample Code ......................................................................................................................................... 125

24. WHE WHERE RE CL CLAUSE AUSE................................................................................................................. 129 Required Steps...................................................................................................................................... 129 Sample Code ......................................................................................................................................... 129

25. LIKE CLA CLAUSE USE ................................ ...................................................................................................................... ...................................................................................... 133 Required Steps...................................................................................................................................... 133 Sample Code ......................................................................................................................................... 133

26. SOR SORTING TING D DAT AT ATA A .................................................................................................................. 137 Required S...