DB2数据库使用经验漫谈 （4）

    来源：白菜乐园    作者：okcai

六 嵌入式SQL(SQLJ)

 

　　将SQL语句嵌入应用程序时，必须按以下步骤预编译应用程序并将其与数据库联编：

　　1.创建源文件，以包含带嵌入式 SQL 语句的程序

      格式: # SQL{ SQL语句 } 。

　　2.连接数据库，然后预编译每个源文件。

      语法: SQLJ 源文件名

 

　　例：

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator App_Cursor1 (String empno, String firstnme) ;
#sql iterator App_Cursor2 (String) ;
class App 
{
   static
   {
      try
      {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
   }
   public static void main(String argv[])
   {
      try
      {
         App_Cursor1 cursor1;
         App_Cursor1 cursor2;
         String str1 = null;
         String str2 = null;
         int   count1;
         Connection con = null;
         String url = "jdbc:odbc:tese2";
         DefaultContext ctx = DefaultContext.getDefaultContext();
         if (ctx == null) {
            try {
              if (argv.length == 0) {
                String userid ="tdl";
                String passwd ="user";
                con = DriverManager.getConnection(url, userid, passwd);
                }
              else if (argv.length == 2) {
                 //  connect with default id/password 
                con = DriverManager.getConnection(url);
              }
              else {
                System.out.println("\nUsage: java App [username password]\n");
                System.exit(0);
              }
              con.setAutoCommit(false);
              ctx = new DefaultContext(con);
            }
          catch (SQLException e) {
            System.out.println("Error: could not get a default context");
            System.err.println(e) ;
            System.exit(1);
          }
          DefaultContext.setDefaultContext(ctx);
         }
          #sql cursor1 = { SELECT empno, firstnme from db2admin.employee };
        
         System.out.println("Received results:");
         while (cursor1.next()) {
            str1 = cursor1.empno();
            str2 = cursor1.firstnme();
            System.out.print (" empno= " + str1);
            System.out.print (" firstname= " + str2);
            System.out.print ("\n");
         }
         cursor1.close();
         #sql cursor2 = { SELECT firstnme from db2admin.employee where empno = :str1 };
         System.out.println("Received results:");
         while (true) {
            #sql { FETCH :cursor2 INTO :str2 };
            if (cursor2.endFetch()) break;
            System.out.print (" empno= " + str1);
            System.out.print (" firstname= " + str2);
            System.out.print ("\n");
         }
         cursor2.close();
         //  rollback the update 
         System.out.println("\n\nRollback the update...");
         #sql { ROLLBACK work };
         System.out.println("Rollback done.");
      }
      catch( Exception e )
      {
         e.printStackTrace();
      }
   }
}

 

　　注：本程序采用JDBCODBC桥的方式访问数据库，必须配置ODBC数据源。