Embedded postgreSQL in JAVA using JDBC @TCU

1
Embedded postgreSQLin JAVAusing JDBC _at_TCU
2
Entities

• 1. Reviews   (Movie, Role, Critic,
  Review)
• 2. Movies ( Movie, OverallReview, Budget)
• 3. Players   (Movie, Role, Category, Actor,
  Award)
• 4. Profits (Movie, Week, Income)

3
JDBC Overall Architecture
your machine
Your program Main or applet
PostgreSQL database
riogrande.cs.tcu.edu
4
What is JDBC

• Its a javasoft specification that defines
• An api that drivers have to complie to
• Includes X/Open types
• The DBMS must support SQL92
• A uniform api that programmer can use
• Write one code, access many DB
• Oracle, postgreSQL, DB2, mySQL

5
Motivations

• Need for accessing DB from Java application,
  applet, servlet, etc.
• Complete java cross-platform with a cross
  platform DB access
• DB are core system of many client/server systems
• Internet openess implies many different sources
  (Sybase, DB2, Oracle, etc.)
• Benefits of ODBC and Java technologies

6
Details

• Drivers do not need to be pre-installed
• Some facilities for applets (special security are
  in the sandbox)
• URL syntax for locating a resource
• Driver Manager instantiate appropriate driver
• SQL types are mapped into java types

7
How-to start

• Register and instantiate the driver
• jdbc.driver property (from command line)
• Java Djdbc.driversorg.postgresql.Driver example
• Hardcoded
• Class.forName( org.postgresql.Driver )
• This allow runtime loading of a specific driver !

8
Connecting

• Connecting to a DB
• DbDriverManager.getConnection(url, usr, pwd)
• No DB specification (the driver has been loaded
  and will be selected by the DriverManager
  regarding url).
• URL syntax
• Jdbcpostgresqldatabase
• Jdbcpostgresql//host/dabase
• Jdbcpostgresql//hostport/dabase
• Jdbcpostgresqldatabase?userme
• Jdbcpostgresqldatabase?usermepasswordmypass

9
Connection methods

• setAutoCommit()
• Transaction mode
• Rollback()
• Commit()
• PreparedStatement
• CreateStatement

10
Doing a statement

• St db.createStatement()
• St.executeUpdate( sql code )
• St.executeQuery( sql code )
• St.execute( sql code )
• St.addBatch( sql statement )
• St.executeBatch()

11
JAVA with JDBC/postgreSQL _at_TCU

• Interfaces, classes and exceptions of the
• java.sql package

12
JAVA with JDBC/postgreSQL _at_TCU

• Methods for the Statement class/interface

13
JAVA with JDBC/postgreSQL _at_TCU

• Methods for the Connection class/interface

14
Resultset

• ResultexecuteUpdate( select from toto)
• Result.getMetaData()
• Retrieves the number, types and properties of
  this ResultSet object's columns
• Result.next()
• Moves the cursor down one row from its current
  position
• Result.getObject(i)
• Gets the value of the designated column in the
  current row of this ResultSet object as an Object
  in the Java programming language

15
JAVA with JDBC/postgreSQL _at_TCUthings to remember
Include the jar file ? postgresql-8.0-314.jdbc3.ja
r into your classpath when you
compile your program Include the
the following Import in your program
import java.sql. // postgreSQL classes Follow
the format of the program provided Provide the
adequate parameters in the connection c
DriverManager.getConnection
("jdbcpostgresql//riogrande.cs.tcu.edu/name",
name",
pwd")
16
JAVA with JDBC/postgreSQL _at_TCUthings to remember
2
When using read ? You must turn on the readOnly
flag of the connection using
setReadOnly(true) When using
rollback/commit ? You must turn off the auto
commit of the connection using
setAutoCommit(false) Always review the
API of the SQL classes http//java.sun.com/j2se/1
.3/docs/api/java/sql/Connection.html http//java.s
un.com/j2se/1.3/docs/api/java/sql/Statement.html
17
Using large object

• create table images (imgname name,imgoid oid)
• To insert an image, you would use
• File file new File("myimage.gif")
• FileInputStream fis new FileInputStream(file)
• PreparedStatement ps conn.prepareStatement("inse
  rt into images values (?,?)")
• ps.setString(1,file.getName())
  ps.setBinaryStream(2,fis,file.length())
• ps.executeUpdate()
• ps.close()
• fis.close()

18
Retrieving a large object

• PreparedStatement ps con.prepareStatement("selec
  t oid from images where name?")
• ps.setString(1,"myimage.gif")
• ResultSet rs ps.executeQuery()
• if(rs!null)
• while(rs.next())
• InputStream is rs.getBinaryInputStream(1)
• // use the stream in some way here is.close()
• rs.close()
• ps.close()