Embedded SQL

] > Embedded SQL

[next] [prev] [prev-tail] [tail] [up] [toc]

Embedded SQL

14.1 Background

Java API’s for accessing databases (JDBC: Java database connectivity)
Database and third-party developers offer driver manager APIs for registering drivers that connect with databases (JDBC drivers)
Information about our driver is available at file://localhost/usr/local/jConnect-5_2/

----------| --------| ------| -----------| | java -----JDBC ----- ---|| database | application ||--manager -----driver----management | -program--| --------| ------| ---system----

14.2 Loading Drivers

Registering a driver object with the driver manager.
<..sybdriver = get a sybase driver ..>
SybDriver sybdriver =
       (SybDriver) Class
          . forName("com.sybase.jdbc2.jdbc.SybDriver")
          . newInstance();
sybdriver.setVersion(
      com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);
-_-_-
Compilation:

javac -classpath /usr/local/jConnect-5_2/classes/jconn2.jar:/usr/local/jConnect-5_2/classes:. filename.java
Execution:

java -classpath /usr/local/jConnect-5_2/classes/jconn2.jar:/usr/local/jConnect-5_2/classes:. filename "username" "password"

<..DBdriver.java ..>
import java.sql.*;
import com.sybase.jdbcx.SybDriver;

public class DBdriver {
   public static void main(String args[]) {
     try {
       <.sybdriver = get a sybase driver .>
     } catch(Exception e) {
         System.err.println("error " + e);
} } }
-_-_-

[java.lang.Class, java.sql, com.sybase.jdbcx.SybDriver]

14.3 Getting Connections

The driver manager DriverManager offers the following methods for connecting to databases through java.sql.Connection objects.

static Connection getConnection(String url, String username, String password)
The database is identified by a URL of the following form.

jdbc:subprotocol:database-name

The subprotocol is provided by the database developer. For SyBase we have sybase:Tds, where Tds refers to a driver type.

<..connection = connect to database ..>
Connection connection = null;
String server = "database";
String port = "4101";
String database = "c670ab";
String username = args[0];
String password = args[1];
String url =   "jdbc:sybase:Tds:"
              + server + ":"
              + port + "/"
              + database;
connection =
   DriverManager.getConnection(url, username, password);
-_-_-

<..Connect.java ..>
import java.sql.*;
import com.sybase.jdbcx.SybDriver;

public class Connect {
   public static void main(String args[]) {
     try {
       <.sybdriver = get a sybase driver .>
       <.connection = connect to database .>
       if(!connection.isClosed()){
         System.out.println("Hurray!");
         connection.close();
       }
     } catch(Exception e) { System.err.println("error " + e); }
} }
-_-_-

14.4 Executing SQL’s DDL and Update Instructions

The Connection interface introduces the following method to create java.sql.Statement objects that can communicate with databases.

Statement createStatement()
A Statement object may use the following method for sending SQL Data Definition Language (DDL) commands such as CREATE TABLE and DROP TABLE, and SQL update commands such as INSERT, UPDATE, and DELETE.

int executeUpdate(String sql)
Multiple statements may be used concurrently.

<..ddl and update ..>
Statement stat = connection.createStatement();
stat.executeUpdate(
   "CREATE TABLE DBupdate (Name CHAR(10))" );
stat.executeUpdate(
   "INSERT INTO DBupdate VALUES ( ’my name’)" );
stat.executeUpdate(
   "DROP TABLE DBupdate" );
stat.close();
-_-_-

14.5 Query Instructions

The following method of the Statement interface may be used for executing SQL statements that return values through java.sql.ResultSet objects.

ResultSet executeQuery(String sql)
The table of a ResultSet object can be inspected one row at a time.
A cursor is used for pointing into the current row. Initially, the cursor is located before the first row.
The following ResultSet method can be used for traversing the rows of the table returned from a query.

boolean next()

The following ResultSet methods can be used for retrieving the value of column col in the current row, where the column is identify by an int index or a String name.

int	getInt(col)
long	getLong(col)
short	getShort(col)
byte	getByte(col)
double	getDouble(col)
float	getFloat(col)
boolean	getBoolean(col)
String	getString(col)
byte []	getBytes(col)
Date	getDate(col)
Time	getTime(col)
Timestamp	getTimestamp(col)
Ref	getRef(col)
InputStream	getAsciiStream(col)
InputStream	getBinaryStream(col)
Reader	getCharacterStream(col)
URL	getURL(col)

Row and column indexes start at 1.
The following method offers the means to check whether the last read value had a NULL value

boolean wasNull()

<..java query instructions ..>
Statement stat = connection.createStatement();
stat.executeUpdate(
   "CREATE TABLE DBquery (Name CHAR(10))" );
stat.executeUpdate(
   "INSERT INTO DBquery VALUES ( ’my name’)" );
stat.executeUpdate(
   "INSERT INTO DBquery VALUES ( ’your name’)" );

ResultSet query =
   stat.executeQuery("SELECT * FROM DBquery");
while( query.next() ){
   System.out.println( query.getString( "Name" ) );
}
query.close();

stat.executeUpdate( "DROP TABLE DBquery" );
stat.close();
-_-_-

my name
your name

14.6 Navigating within Returned Tables

ResultSet objects can have different properties.

type
TYPE_FORWARD_ONLY	asks for forward moving cursor
TYPE_SCROLL_INSENSITIVE TYPE_SCROLL_SENSITIVE	ask for scrollable tables and indicate whether changes by others are allowed
CLOSE_CURSORS_AT_COMMIT HOLD_CURSORS_OVER_COMMIT	close object when commit is called?
concurrency
CONCUR_READ_ONLY CONCUR_UPDATABLE	available concurrency modes

The objects can be requested with the following Connection instructions.

Statement createStatement(int resultSetType, int resultSetConcurrency)

The following ResultSet methods can be used for navigating between rows.

boolean	first()
boolean	last()
void	beforeFirst()
void	afterLast()
boolean	next()
boolean	previous()
boolean	absolute(int row)
boolean	relative(int rows)

move the cursor to the specified row

boolean isBeforeFirst()
boolean isAfterLast()
boolean isFirst()
boolean isLast()

is the cursor in the specified location?

int getRow()

provides the current row number

The following method gives the index of the named column.

int findColumn(String columnName)

<..java navigation instructions ..>
Statement stat = connection.createStatement(
      ResultSet.TYPE_SCROLL_SENSITIVE,
      ResultSet.CONCUR_READ_ONLY);
stat.executeUpdate(
      "CREATE TABLE DBnav (Name CHAR(10))" );
stat.executeUpdate(
      "INSERT INTO DBnav VALUES ( ’my name’)" );
stat.executeUpdate(
      "INSERT INTO DBnav VALUES ( ’your name’)" );
ResultSet query =
      stat.executeQuery("SELECT * FROM DBnav");
while( query.relative( 1 ) ){
   System.out.println( query.getString( "Name" ) );
}
// query.close();
-_-_-

14.7 Java’s Field Updating Instructions

Specific fields of a ResultSet table can be updated with commands of the following form, where col stands for an int index or a String name.
void updateBoolean(col, boolean x)
void updateByte(col, byte x)
void updateDate(col, Date x)
void updateDouble(col, double x)
void updateFloat(col, float x)
void updateInt(col, int x)
void updateLong(col, long x)
void updateNull(col)
void updateRef(col, Ref x)
void updateShort(col, short x)
void updateString(col, String x)
void updateTime(col, Time x)
void updateTimestamp(col, Timestamp x)
The changes in the current row of the ResultSet table can be revoked with the following command, as long as the cursor did not move to another row.

void cancelRowUpdates()
The following method can be used to update the database table behind the ResultSet table, at the corresponding row.

void updateRow()

<..update result table ..>
Statement stat = connection.createStatement(
      ResultSet.TYPE_SCROLL_SENSITIVE,
      ResultSet.CONCUR_UPDATABLE);
stat.executeUpdate(
      "CREATE TABLE DBupdatetbl (Name CHAR(10))" );
stat.executeUpdate(
      "INSERT INTO DBupdatetbl VALUES ( ’my name’)" );

ResultSet query =
      stat.executeQuery("SELECT * FROM DBupdatetbl");
query.absolute(1);
// not supported?
query.updateString("Name", "your name");
query.updateRow();

-_-_-

14.8 Transactions

A transaction is a set statements that are committed together, after they all execute flawlessly.
The following java.sql.Connection method can be used to determine whether statements are to be automatically committed when their executions are completed.

void setAutoCommit(boolean autoCommit)
When a non-auto commit mode is on, the following instruction may be used to commit the available transactions.

void commit()
In the default setting new connections assume auto-commit modes.
The following instruction asks to undo all the pending changes made by the connection.

void rollback()

<..commit transactions ..>
connection.setAutoCommit(false);
stat.executeUpdate(
"INSERT INTO DBcommit VALUES ( ’my name’)" );
stat.executeUpdate(
"INSERT INTO DBcommit VALUES ( ’your name’)" );
connection.commit();
connection.setAutoCommit(true);
-_-_-

14.9 Prepared Statements

Prepared statements represent, normally parameterized, SQL instructions.
The instructions are precompiled and stored in the DBMS as an object, so that there is no need to compile them each time they are invoked.
Prepared statements are held in java.sql.PreparedStatement objects that can be requested by the following java.sql.Connection method.

PreparedStatement prepareStatement(String sql)
Parameters are represented by the question mark character ‘?’
Arguments are offered with instructions of the following form.
void setBoolean(int parameterIndex, boolean x)
void setByte(int parameterIndex, byte x)
void setDate(int parameterIndex, Date x)
void setDouble(int parameterIndex, double x)
void setFloat(int parameterIndex, float x)
void setInt(int parameterIndex, int x)
void setLong(int parameterIndex, long x)
void setNull(int parameterIndex, int sqlType)
void setNull(int paramIndex, int sqlType, String typeName)
void setRef(int i, Ref x)
void setShort(int parameterIndex, short x)
void setString(int parameterIndex, String x)
void setTime(int parameterIndex, Time x)
void setTimestamp(int parameterIndex, Timestamp x)
void setURL(int parameterIndex, URL x)
The content of a prepared statement can be committed with the following instruction. The returned values tells the number of rows updated.

int executeUpdate()

<..prepared statements ..>
Statement stat = connection.createStatement();
stat.executeUpdate(
"CREATE TABLE DBprepare (Name CHAR(10), Age int)" );
PreparedStatement prep = connection.prepareStatement(
"INSERT INTO DBprepare VALUES ( ?, ?)" );
prep.setString( 1, "my name" );
prep.setInt( 2, 10 );
prep.executeUpdate();
stat.executeUpdate( "DROP TABLE DBprepare" );
stat.close();
-_-_-

14.10 Information about Tables

The following method of ResultSet returns a java.sql.ResultSetMetaData object recording the properties returned tables.

ResultSetMetaData getMetaData()

int	getColumnCount()	number of columns
String	getColumnLabel(int column)	column’s title for displays
String	getColumnName(int column)	column’s name
int	getColumnType(int column)	column’s SQL type
String	getTableName(int column)	column’s table name
int	isNullable(int column)	is NULL permitted?

<..info about tables ..>
ResultSet query =
    stat.executeQuery( "SELECT * FROM DBinfotbl" );
ResultSetMetaData infoTbl = query.getMetaData();

System.out.println(
   "number of columns: " + infoTbl.getColumnCount() +
   "\ncolumn label: " + infoTbl.getColumnLabel( 1 ) +
   "\ncolumn name: " + infoTbl.getColumnName( 1 ) +
   "\ncolumn type: " + infoTbl.getColumnType( 1 ) +
   "\ntable name: " + infoTbl.getTableName( 1 )
);

query.last();
System.out.println(
   "number of rows: " + query.getRow()
);

query.close();
-_-_-

number of columns: 1
column label: Name
column name: Name
column type: 1

14.11 Information about Databases

The following java.sql.Connection method provides an object containing information about the connected database.

DatabaseMetaData getMetaData()
The information can be retrieved by java.sql.DatabaseMetaData methods similar to the following ones.
String getDatabaseProductName()
String getDatabaseProductVersion()
String getDriverName()
String getDriverVersion()
int getMaxRowSize()
boolean storesUpperCaseIdentifiers()
boolean supportsANSI92FullSQL()
boolean supportsOuterJoins()
boolean supportsUnion()

<..db meta info ..>
DatabaseMetaData meta = connection.getMetaData();
System.out.println(
   "product name: " + meta.getDatabaseProductName() +
   "\ndriver name: " + meta.getDriverName() +
   "\nsupports full ANSI 92 SQL: " +
   meta.supportsANSI92FullSQL() +
   "\nsupports outer joins: " +
   meta.supportsOuterJoins()
);
-_-_-

product name: Sybase SQL Server
driver name: jConnect (TM) for JDBC (TM)
supports full ANSI 92 SQL: false
supports outer joins: true

14.12 References

file://localhost/usr/local/jConnect-5_2/
Maydene Fisher, JDBC Database Access, Sun Microsystems

14.13 Assignment #8

Due: Mo, Mar 9, midnight

Provide a Java program with embedded SQL code which performs the following operations.

Creates the tables of Figure 1.2 in the textbook (page 7 4th ed; 8 5th ed).
Reads a student’s name
Prints the grade point average of the student

Notes

Submit your file electronically using the departmental submit utility: submit XXX lab8 lab8.java. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
- Files are restricted to 1,048,576 bytes
- The submit program issues error messages to the student’s CSE email account
Your file should be well formatted.

Q&A

Reference: Ch. 9.4 in textbook; sample Jhyton program (contributed by Rick Holbert)

[next] [prev] [prev-tail] [front] [up] [toc]