Examples of SQLJ applications in DB2

Example | Apply///This example shows how to write a SQLJ application that uses the JDBC application driver to access the DB2 database.
The bank is the database in the DB2, the customer is the table in the bank
Import java.sql.*;
Import sqlj.runtime.*;
Import sqlj.runtime.ref.*;

#sql iterator App_cursor1 (string customer_id, string customer_name);
#sql iterator App_cursor2 (String);

Class APP
{

Static
{
Try
{
Registering Drivers with DriverManager
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver"). newinstance ();
}
catch (Exception e)
{
E.printstacktrace ();
}
}

public static void Main (String argv[])
{
Try
{
App_cursor1 Cursor1;
App_cursor2 Cursor2;

String str1 = null;
String str2 = null;
int count1;

Connection con = null;

The format of the URL is: jdbc:db2: Database name
String url = "Jdbc:db2:bank";

Defaultcontext CTX = Defaultcontext.getdefaultcontext ();
if (CTX = = null) {
try {
if (Argv.length = = 0) {
Connect with the default username and password
con = drivermanager.getconnection (URL);
}
else if (Argv.length = 2) {
String userid = argv[0];
String passwd = argv[1];

Connect using user-supplied username and password
con = drivermanager.getconnection (URL, userid, passwd);
}
else {
System.out.println ("Usage: Java Sqljapp [username password]");
System.exit (0);
}
Con.setautocommit (FALSE);
CTX = new Defaultcontext (con);
}
catch (SQLException e) {
System.out.println ("Error: Cannot get default content");
System.err.println (e);
System.exit (1);
}

Defaultcontext.setdefaultcontext (CTX);
}

Extracting data from a database
System.out.println ("Extract some data from the database ...");
#sql Cursor1 = {SELECT customer_id, customer_name from customer};

Show result set
Cursor1.next () returns False when no more rows
SYSTEM.OUT.PRINTLN ("Results obtained:");
while (Cursor1.next ()) {
STR1 = cursor1.customer_id ();
STR2 = Cursor1.customer_name ();

System.out.print ("Customer number =" + str1);
System.out.print ("Customer name =" + str2);
System.out.print ("");
}
Cursor1.close ();

Get the number of customers from the database
System.out.println ("Get number of rows from Customer table ...");
#sql {SELECT count (*) Into:count1 from customer};
System.out.println ("+ count1 +" line in the Customer table);

Update data
STR1 = "1";
SYSTEM.OUT.PRINTLN ("Update data ...") ");
#sql {UPDATE customer set customer_name =´ Huang Liang solo fai ´where customer_id =: str1};

Extracting updated data from the database
SYSTEM.OUT.PRINTLN ("Extract update data from Database ...");
#sql Cursor2 = {SELECT customer_name from customer where customer_id =: str1};

Show result set
Cursor2.next () returns False when no more rows
SYSTEM.OUT.PRINTLN ("Results obtained:");
while (true) {
#sql {Fetch:cursor2 INTO:STR2};
if (Cursor2.endfetch ()) break;

System.out.print ("Customer number =" + str1);
System.out.print ("Customer name =" + str2);
System.out.print ("");
}
Cursor2.close ();

Rolling back updates
SYSTEM.OUT.PRINTLN ("Rollback update ...");
#sql {ROLLBACK work};
SYSTEM.OUT.PRINTLN ("rollback complete.");
}
catch (Exception e)
{
E.printstacktrace ();
}
}
}