Chapter 9a: Persistence Tier: Data Layer, Apache Derby, JDBC

1
Chapter 9aPersistence TierData Layer, Apache
Derby, JDBC
Reference Eclipse WTP
2
Objectives

• Designs for Persistence Layer
• Apache Derby
• Eclipse WTP Data Source
• JDBC Sample Code

3
Persistence Layer Designs
Client and Presentation Tier
Business Tier
Service Layer (Facades)
Domain Model (POJO)
Data Layer (Data Access Object Interfaces)
Persistence Tier
1. JDBC (JDBC API)
2. EJB, CMP (Remote Stubs)
3. O/R Mapping (ORM Framework)
4
Designs for Persistence Layer

• Reference Pages 370 - 374
• JDBC
• Simple but requires a good understanding of
  relational databases and SQL
• Cons change in DB need to change Java code
• Entity Beans (EJB CMP)
• Insert, update, select, remove data
• Container-managed (CMP) or bean-managed (BMP)
• New EJB 3/JPA
• Object-Relational Framework
• Declare O/R mappings
• Cons steep learning curve
• E.g. Hibernate, TOPLink, iBatis

5
Apache Derby

• Open-source database
• Embedded Single-user Mode
• Only one connection allowed
• Driver Derby Embedded JDBC Driver
• Network Mode
• Allows multiple connection
• Driver Derby Client JDBC Driver
• Note needed for Assignments 3 and 4

6
Starting Derby in Network Mode

• Edit StartDerbyServer.bat
• REM script to run Derby 10.3.1 in network mode
• set DERBY_INSTALLC\dev\appservers\db-derby-10.3.
  1.4-bin
• call "DERBY_INSTALL"/bin/setNetworkServerCP.bat
• call "DERBY_INSTALL"/bin/startNetworkServer.bat
• Run StartDerbyServer.bat
• Listens on Port 1527 (default)
• Creates a new database under the current
  directory (e.g., c\DerbyDatabases)

7
Creating a DB Connection in WTP

• Reference Pages 375-378
• Data Source Explorer (WTP 2.x)
• Create new database connection
• Generic JDBC Connection
• Name leaguedb
• Driver Derby Client JDBC
• DB leaguedb
• URL change sample
• to leaguedb
• To Test
• Click Test Connection

8
Creating a DB Script in WTP

• Object Model Project (e.g. LeaguePlanetModel)
• Create dbscripts folder under src
• Note The dbscripts folder will be exported
• File New Other
• SQL Development SQL File
• dbcreate.sql
• E.g., APP is the default DB name
• DROP TABLE APP.LEAGUE
• CREATE TABLE APP.LEAGUE (
• ID INT NOT NULL,
• NAME VARCHAR(40) )

9
Executing a DB Script in WTP

• Connect to the database
• E.g., leaguedb
• Open script file (e.g. dbcreate.sql)
• Connection Profile
• Type Derby_10.x
• Name leaguedb
• DB leaguedb
• Execute All
• Drop table errors
• Continue? Yes

10
Using Derby Embedded JDBC Drivers

• Reference Page 387
• IceHockeyJdbcDAOImpl.java
• private Connection openConnection() throws
  ClassNotFoundException, SQLException
• Connection connection
• Class.forName("org.apache.derby.jdbc.EmbeddedDrive
  r")
• connection DriverManager.
• getConnection("jdbcderbyC\\leaguedb")
• return connection

11
Using Derby Client JDBC Drivers

• Replace the driver name and connection string
  (URL)
• Note Driver located in derbyclient.jar
• private Connection openConnection() throws
  ClassNotFoundException, SQLException
• Connection connection
• Class.forName("org.apache.derby.jdbc.ClientDriver"
  )
• connection DriverManager
• .getConnection("jdbcderby//localhost1527/survey
  dbcreatetrue")
• return connection

12
Accessing the Database

• Reference Page 388
• Note executeQuery() returns not
  java.sql.ResultSet object
• public Schedule findSchedule(long id)
• Schedule schedule new Schedule(id, "2005/6
  Regular Season")
• Connection connection null
• Statement statement null
• ResultSet resultset null
• try
• connection openConnection()
• String QUERY "SELECT FROM APP.GAME WHERE
  SCHEDULEID" id
• statement connection.createStatement()
• resultset statement.executeQuery(QUERY)
• while (resultset.next())
• Game game createGameFromResultSet(resultset)
• schedule.getEvents().add(game)
• catch (Exception e)
• e.printStackTrace()

13
Converting Resultset to Object

• Reference Page 388
• Data Layer returns POJO (not java.sql.ResultSet)
• private Game createGameFromResultSet(ResultSet
  rs)
• throws SQLException, ParseException
• Game game new Game()
• game.setId(rs.getInt("ID"))
• game.setLocation(new Location(0,
  rs.getString("ARENA")))
• Team homeTeam new Team()
• homeTeam.setName(rs.getString("HOME"))
• game.setHome(homeTeam)
• return game

14
More JDBC Examples

• Reference Beginning JSP textbook pages 768 - 770
• SQL Insert, Update and Delete Sample Code
• JDBCRosterManager class
• addNewTeamToRoster()
• modifyTeamInRoster()
• deleteTeamFromRoster()

15
addNewTeamToRoster()

• public boolean addNewTeamToRoster(Team newTeam)
  throws DataException
• PreparedStatement statement null
• boolean result false
• try
• statement _connection.prepareStateme
  nt( "INSERT INTO roster (teamname) values (?) )
• statement.setString(1,
  newTeam.getName())
• result statement.execute()
• catch (SQLException unexpected)
• throw new DataException(unexpected.get
  Message())
• finally
• safeClose(statement)
• return result

16
modifyTeamInRoster()

• public boolean modifyTeamInRoster(Team
  modifyTeam) throws DataException
• PreparedStatement statement null
• boolean result false
• try
• statement _connection.prepareStateme
  nt( "UPDATE roster set teamname ? where id ?
  )
• statement.setString(1,
  modifyTeam.getName())
• statement.setInt(2,
  modifyTeam.getId())
• result statement.execute()
• catch (SQLException unexpected)
• throw new DataException(unexpected.get
  Message())
• finally
• safeClose(statement)
• return result

17
deleteTeamFromRoster()

• public boolean deleteTeamFromRoster(int teamId)
  throws DataException
• PreparedStatement statement null
• boolean result false
• try
• statement _connection.prepareStateme
  nt( "DELETE from roster where id ? )
• statement.setInt(1, teamId)
• result statement.execute()
• catch (SQLException unexpected)
• throw new DataException(unexpected.get
  Message())
• finally
• safeClose(statement)
• return result

18
Chapter 9 Iteration 2

• Reference Page 390
• In LeagueFacadeImpl.java constructor (Service
  Layer)
• Execute the JDBC code instead of in-memory data
  code
• public LeagueFacadeImpl()
• // option 1
• // populate in-memory data when facade is created
• // this.leagueDAO IceHockeyDOAImpl.getLeagueDAO(
  )
• // option 2
• // load data from Derby database using JDBC
• // Note must start Derby and run dbcreate.sql
  and dbinsert.sql first
• this.leagueDAO IceHockeyJdbcDAOImpl.getLeagueDAO
  ()

19
Next Steps

• Tutorials
• Chapter 3 Iteration 3
• Chapter 9 Iteration 2
• Lab Service Layer (with JDBC)
• Assignment 3