les_13-JDBC

8/2/2019 les_13-JDBC

1/36

Copyright 2009, Oracle. All rights reserved.

Understanding JDBC and Configuring Data

Sources

8/2/2019 les_13-JDBC

2/36

Copyright 2009, Oracle. All rights reserved.1 3 - 2

Objectives

After completing this lesson, you should be able to:

Configure JDBC and JDBC data sources

Configure data source scope

Contrast two-tier and multi-tier JDBC architecture

Configure a connection pool

List the benefits of connection pools

Describe how data sources are used

Deploy JDBC resources to a target

View the server JNDI tree

Complete a connection pool checklist

Explain the components of JDBC URLs

Monitor and test a data source

8/2/2019 les_13-JDBC

3/36


Road Map

Overview of JDBC

High-level architecture of JDBC and the driver model

Design of a multi-tier architecture

Drivers provided by Oracle WebLogic Server

Data sources

Monitoring and testing data sources

8/2/2019 les_13-JDBC

4/36


JDBC Review

The Java Database Connectivity (JDBC) specification:

Is a platform- and vendor-independent mechanism for

accessing and updating a database

Provides transparency from proprietary vendor issues

Requires the use of a driver JDBC drivers are supplied by WebLogic Server or by your

database vendor.

ApplicationDB

JDBC

driver

Get connection

Perform SQL

8/2/2019 les_13-JDBC

5/36


JDBC Data Sources

Data sources:

Enable database connectivity to be managed by the

application server

Are obtained by applications from the servers JNDI tree

Use a dynamic pool of reusable database connections

Data source

PoolApp

JDBC driver

DB

Connection

Connection

Connection

Connection

App

App

Get connection

Perform SQL

JNDI lookup

8/2/2019 les_13-JDBC

6/36


Data Source Scope

Each data source configuration or module is persisted as

a separate XML document.

The system modules that are created with the console or

WLST are:

Stored in the domains config/jdbc directory Available to all applications in the domain

Application-specific modules are:

Deployed as part of Java Platform, Enterprise Edition (Java

EE) enterprise applications

Accessible only by the containing application

8/2/2019 les_13-JDBC

7/36


Multi-TierArchitecture

In the multi-tier model, commands

are sent to a middle tier of

services, which then sends the

commands to the DBMS.

The DBMS processes thecommands and sends the results

back to the middle tier, which then

sends them to the client.

DBMS

Data source

Java applet or

Java DBMS client

Client

DBMS: Proprietary protocol

Server

8/2/2019 les_13-JDBC

8/36


Type 4 Drivers

Type 4 drivers are all-Java driver implementations that do not

require client-side configuration.

JDBC driver

Client

DBMSJDBCAPI

Java app

8/2/2019 les_13-JDBC

9/36


WebLogic JDBC Drivers

Oracle and third-party drivers are included in the WLS

installation for many popular database products:

Oracle 9i, 10g, and 11g

Sybase Adaptive Server

Microsoft SQL Server IBM DB2

Informix

MySQL

PointBase

By default, these drivers are added to the servers

classpath.

8/2/2019 les_13-JDBC

10/36

Copyright 2009, Oracle. All rights reserved.13 - 10

Road Map

Overview of JDBC

Data sources

Describing a data source and how it works

Using the Administration Console to create a data source


8/2/2019 les_13-JDBC

11/36


What Is a Connection Pool?

A connection pool is a group of ready-to-use database

connections associated with a data source.

Connection pools:

Are created at Oracle WebLogic Server startup

Can be administered using the Administration Console Can be dynamically resized to accommodate increasing or

decreasing load

8/2/2019 les_13-JDBC

12/36


JDBC Connection Pooling

Managed server

DBMS

JNDI

JDBC Conn. Pool

: Connection

: Connection

: Connection

: Connection

JDBC Conn. Pool

JDBC Conn. Pool

DataSourceB

Application

DataSourceC

DataSourceA

Component

8/2/2019 les_13-JDBC

13/36


Benefits of Connection Pools

The following are some advantages of connection pooling:

Connection time and overhead are saved by using an

existing database connection.

It facilitates easier management because connection

information is managed in one location. The number of connections to a database can be controlled.

The DBMS can be changed without the application

developer having to modify the underlying code.

A connection pool allows an application to borrow a

DBMS connection.

8/2/2019 les_13-JDBC

14/36


Modular Configuration and Deployment of

JDBC Resources

The JDBC configurations in WebLogic Server are stored in

XML documents:

All JDBC configurations must conform to the newweblogic-jdbc.xsd schema.

IDEs and other tools can validate the JDBC modules basedon the schema.

You create and manage JDBC resources either as system

modules or as application modules.

The JDBC application modules are a WLS-specific

extension of Java EE modules and can be deployed eitherwithin a Java EE application or as stand-alone modules.

8/2/2019 les_13-JDBC

15/36


How Data Source Connection Pools Are Used

A client retrieves a data source through a JNDI lookup and

uses it to obtain a database connection.

Lookup

data source

DBMS

Return data source

getConnection()

Return connection

Database access

1

2

3

4

5

Client

Data source

Connection

Managed server

JNDI

Connection

pool

8/2/2019 les_13-JDBC

16/36


2

Creating a Data Source Using the

Administration Console

1

3

Notice non-XA

8/2/2019 les_13-JDBC

17/36


Non-XA Configuration

This appears only if a non-XA driver was selected previously.

8/2/2019 les_13-JDBC

18/36


Data Source Connection Properties

Not just name. Bemindful of whether

you are entering a

service name vs.

instance name vs.

database name.

Sample schemas

8/2/2019 les_13-JDBC

19/36


Test Configuration

8/2/2019 les_13-JDBC

20/36


Connection Pool Configuration

1

2

8/2/2019 les_13-JDBC

21/36


Connection Pool Advanced

Connection pool size

Grows pool when more

connections are

needed

Periodically tests for

bad connections and

closes

Periodically closes idle

connections

More options not

shown

8/2/2019 les_13-JDBC

22/36


Targeting a Data Source

Deploy data sources to one or more servers in your domain.

1

2

8/2/2019 les_13-JDBC

23/36


Viewing the Server JNDI Tree via the

Administration Console

1

2Confirm data source

deployment using the

servers JNDI tree.

3

8/2/2019 les_13-JDBC

24/36


Listing the JNDI Contents viaWLST

WLST provides a command-line utility for viewing the

JNDI bindings.

jndi() changes to the JNDI tree and ls() lists the

bindings.

wls:/offline> connect("weblogic","welcome1","t3://localhost:7020")

wls:/base_domain/serverConfig> jndi()

wls:/base_domain/jndi> cd('AdminServer')

wls:/base_domain/jndi/AdminServer> ls()

dr-- ejbdr-- javax

dr-- weblogic

-r-- cgDataSource weblogic.rmi.cluster.ClusterableRemoteObject

-r-- cgDataSource-nonXA weblogic.rmi.cluster.ClusterableRemoteObject

-r-- mejbmejb_jarMejb_EO weblogic.rmi.cluster.ClusterableRemoteObject

-r-- samplesDataSource weblogic.rmi.cluster.ClusterableRemoteObject

JDBC data

source

8/2/2019 les_13-JDBC

25/36


Demonstration

Configure data sources for Oracle Database.

Go to OTN > Tutorials > Fusion Middleware > Oracle

WebLogic Server 10.3 > Deploy J2EE Applications >

Configure Data Sources.

8/2/2019 les_13-JDBC

26/36


JDBC URLs

Database locations are specified using a JDBC Uniform

Resource Locator (URL).

Example 1:

This URL specifies that the oracle:thin subprotocol

should be used to connect to an Oracle Database:

Example 2:

This URL can be used to access a PointBase database:

jdbc:pointbase:server://dbhost:9092/HRDATABASE

jdbc:oracle:thin:@dbhost:1521:SALESINFO

8/2/2019 les_13-JDBC

27/36


Connection Properties

Are key/value pairs

Are used to configure JDBC connections

Are passed to the driver during connection setup

8/2/2019 les_13-JDBC

28/36


Specifying Connection Properties

A partial list of connection properties for the supplied drivers:

Driver Some Connection Properties

Oracle User, Password, ServerName, ServiceName, PortNumber

Sybase User, Password, ServerName, DatabaseName, PortNumber

MSSQL User, Password, ServerName, DatabaseName, PortNumber

Informix User, Password, ServerName, DatabaseName, PortNumber

PointBasecache.size, crypto.communication, database.home,database.pagesize

8/2/2019 les_13-JDBC

29/36


Road Map

Overview of JDBC

Data sources


Monitoring

Testing Suspend/resume

8/2/2019 les_13-JDBC

30/36


Monitoring and Testing a Data Source

Monitor data

source statistics.

Data source

retested

successfully.

8/2/2019 les_13-JDBC

31/36


Connection Pool Life Cycle

For this datasource

on a

given

server

take this

action.

8/2/2019 les_13-JDBC

32/36


Quiz

Which of the following is NOT an available configuration

attribute for a JDBC data source?

1. Host name

2. Queue size

3. Test frequency4. Initial capacity

5. Capacity increment

8/2/2019 les_13-JDBC

33/36


Quiz

Which are the two levels of data sources available in Oracle

WebLogic Server?

1. Connection

2. Web

3. Application4. Process

5. System

8/2/2019 les_13-JDBC

34/36


Quiz

Client applications look up data sources from the local servers

___________ tree:

1. Application

2. Web

3. LDAP directory4. JNDI

5. System

8/2/2019 les_13-JDBC

35/36


Summary

In this lesson, you should have learned how to:

Define JDBC high-level architecture

Configure Oracle WebLogic Serverprovided JDBC driver

types

Create data source definitions Create connection pool definitions

Manage JDBC resources using the Administration Console

8/2/2019 les_13-JDBC

36/36

Copyright 2009 Oracle All rights reserved13 - 36

Practice 13 Overview:

Configuring JDBC Data Sources

This practice covers the following topics:

Creating JDBC modules (via GUI and WLST)

Deploying JDBC modules

Testing JDBC modules

Documents

les_13-JDBC