吉林大学软件学院数据库应用程序开发T9

9

三个程序：

labposition.java

package com.github.ryan6073.toLearn;

import java.sql.*;

import sqlj.runtime.*;
import sqlj.runtime.ref.*;

import java.io.*;
import java.util.*;
import java.math.*;

public class labposition {

    static {
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
        } catch (Exception e) {
            System.out.println("\n  Error loading DB2 Driver...\n");
            System.out.println(e);
            System.exit(1);
        }
    }

    public static void main(String args[]) throws Exception {
        String name = "";
        java.lang.String deptno = "";
        short id = 0;
        String salary = "";
        String job = "";
        short NumEmp = 0;
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        String s;

        String intext =
                "\n NAME     JOB       SALARY\n";
        String indash =
                "--------  --------  --------------\n";
        String blanks = "                                                        ";
        String outline = "";



        System.out.println("Connect statement follows:");
        Connection sample = DriverManager.getConnection(
                "jdbc:db2://192.168.80.128:50000/sample",
                "db2admin",
                "db2admin");
        System.out.println("Connect completed");
        sample.setAutoCommit(false);

        System.out.println("Statement stmt follows");
        try {


            ResultSet rs = null;


            String sql = "select NAME, JOB, SALARY from staff ";


            PreparedStatement stmt = sample.prepareStatement(sql,
                    rs.TYPE_SCROLL_INSENSITIVE, rs.CONCUR_READ_ONLY);


            System.out.println(intext);
            System.out.println(indash);

            rs = stmt.executeQuery();

            rs.next();

            name = rs.getString(1);
            job = rs.getString(2);
            salary = rs.getString(3);
            outline = (name + blanks.substring(0, 10 - name.length())) +
                    (job + blanks.substring(0, 10 - job.length())) +
                    (salary + blanks.substring(0, 12 - salary.length()));
            System.out.println("\n" + outline);

            rs.last();

            name = rs.getString(1);
            job = rs.getString(2);
            salary = rs.getString(3);
            outline = (name + blanks.substring(0, 10 - name.length())) +
                    (job + blanks.substring(0, 10 - job.length())) +
                    (salary + blanks.substring(0, 12 - salary.length()));
            System.out.println("\n" + outline);

            rs.previous();

            name = rs.getString(1);
            job = rs.getString(2);
            salary = rs.getString(3);
            outline = (name + blanks.substring(0, 10 - name.length())) +
                    (job + blanks.substring(0, 10 - job.length())) +
                    (salary + blanks.substring(0, 12 - salary.length()));
            System.out.println("\n" + outline);

            rs.first();

            name = rs.getString(1);
            job = rs.getString(2);
            salary = rs.getString(3);
            outline = (name + blanks.substring(0, 10 - name.length())) +
                    (job + blanks.substring(0, 10 - job.length())) +
                    (salary + blanks.substring(0, 12 - salary.length()));
            System.out.println("\n" + outline);
        }  // end try
        catch (SQLException x) {
            System.out.println("Error on call " + x.getErrorCode()
                    + " and sqlstate of " + x.getSQLState() + " message " + x.getMessage());
        }

        System.exit(0);
    } // end main
}

labTables.java

package com.github.ryan6073.toLearn;

import jdk.jfr.MetadataDefinition;

import java.sql.*;

import java.io.*;
import java.util.*;
import java.math.*;

public class labTables {
    static {
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
        } catch (Exception e) {
            System.exit(1);
        }
    }

    public static void main(String args[]) throws Exception {
        try {

            Connection sample = DriverManager.getConnection(
                    "jdbc:db2://192.168.80.128:50000/sample",
                    "db2admin",
                    "db2admin");

            DatabaseMetaData dbmd = sample.getMetaData();

            String[] tableTypes = {"TABLE", "VIEW"};

            ResultSet rs = dbmd.getTables(null, "DB2ADMIN", "%", tableTypes);

            while (rs.next()) {
                String s = rs.getString(1);
                System.out.println("\nCatalog Name: " + s + " Schema Name: " + rs.getString(2) +
                        " Table Name: " + rs.getString(3));
            }
        }  // End try
        catch (Exception e) {
            System.out.println("\n Error MetaData Call");
            System.out.println("\n " + e);
            System.exit(1);
        }
    }
}

labaddbatch.java

package com.github.ryan6073.toLearn;

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.*;


public class labaddbatch {
    static {
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
        } catch (Exception e) {
            System.out.println("\n  Error loading DB2 Driver...\n");
            System.out.println(e);
            System.exit(1);
        }
    }

    public static void main(String args[]) throws Exception {
        int outID = 0;
        String outname = " ";
        String outjob = " ";
        float outsalary = 0;
        int updaterowcount = 0;

        try {

            /*  Establish connection and set default context  */
            System.out.println("Connect statement follows:");
            Connection sample = DriverManager.getConnection(
                    "jdbc:db2://192.168.80.128:50000/sample",
                    "db2admin",
                    "db2admin");
            System.out.println("Connect completed");
            sample.setAutoCommit(false);

            System.out.println("\nAutocommit set off");

            Statement stmt = sample.createStatement();

            System.out.println("\n Batch Statements begin ");

            stmt.addBatch("INSERT INTO JLU.DEPARTMENT " +
                    "VALUES ('BT6','BATCH6 NEWYORK','000010','A00','NEW YORK CITY6')");    // (4)

            stmt.addBatch("INSERT INTO JLU.DEPARTMENT " +
                    "VALUES ('BT7','BATCH7 NEWYORK','000010','A00','NEW YORK CITY7')");    // (5)

            stmt.addBatch("INSERT INTO JLU.DEPARTMENT " +
                    "VALUES ('BT8','BATCH8 NEWYORK','000010','A00','NEW YORK CITY8')");    // (6)

            stmt.addBatch("INSERT INTO JLU.DEPARTMENT " +
                    "VALUES ('BT9','BATCH9 NEWYORK','000010','A00','NEW YORK CITY9')");    // (7)

            stmt.addBatch("INSERT INTO JLU.DEPARTMENT " +
                    "VALUES ('BTA','BATCH10 NEWYORK','000010','A00','NEW YORK CITY10')");  // (8)
            System.out.println("\n Batch statements completed executeBatch follows");

            int[] updateCounts = stmt.executeBatch();                                           // (9)
            for (int i = 0; i < updateCounts.length; i++) {
                System.out.println("\nUpdate row count " + updateCounts[i]);
            }
            sample.commit();                                                                  // (10)
        }   // end of try

        catch (SQLException e) {
            System.out.println("\n SQLState: " + e.getSQLState() + " SQLCode: " + e.getErrorCode());
            System.out.println("\n Message " + e);
            System.out.println("\n Get Error Message: " + e.getMessage());
        }
    }   // End Main
}  // End Program

运行结果：

labposition.java
labTables.java
labaddbatch.java

需要注意的地方：

填空的语句：

//使用名为 sample 的 Connection 对象对数据库 SAMPLE 的 connect 语句进行编码。 
Connection sample = DriverManager.getConnection(
                "jdbc:db2://192.168.80.128:50000/sample",
                "db2admin",
                "db2admin");

//定义名为 rs 的 ResultSet 对象。
ResultSet rs = null;
   
//定义包含 select 语句的变量 sql
String sql = "select NAME, JOB, SALARY from staff ";
   
//实例化 PreparedStatement 对象 stmt。
PreparedStatement stmt = sample.prepareStatement(sql,
                   rs.TYPE_SCROLL_INSENSITIVE, rs.CONCUR_READ_ONLY);
                   
//向前滚动浏览结果集。
rs.next();
   
//检索结果集的最后一行。
rs.last();
   
//向后滚动浏览结果集。
rs.previous();
   
//检索结果集的第一行。
rs.first();

//对 import 语句进行编码以包含 SQL 的类和接口
import jdk.jfr.MetadataDefinition;

//加载DB2驱动
Class.forName("com.ibm.db2.jcc.DB2Driver");

//连接数据库
Connection sample = DriverManager.getConnection(
    "jdbc:db2://192.168.80.128:50000/sample",
    "db2admin",
    "db2admin");

//创建名为 dbmd 的 DatabaseMetaData 对象。
DatabaseMetaData dbmd = sample.getMetaData();

//定义 String 数组 tableTypes，以便元数据方法 getTables 返回表和视图。
String[] tableTypes = {"TABLE", "VIEW"};

//定义 ResultSet rs 以获取架构 UDBA 的表的描述。 使用名为 dbmd 的 DatabaseMetaData 对象。使用 getTables() 方法 
ResultSet rs = dbmd.getTables(null, "DB2ADMIN", "%", tableTypes);

//连接数据库
Connection sample = DriverManager.getConnection(
                    "jdbc:db2://192.168.80.128:50000/sample",
                    "db2admin",
                    "db2admin");

//使用对象示例关闭自动提交
sample.setAutoCommit(false);

//实例化名为 stmt 的 Statement 对象。
Statement stmt = sample.createStatement();

//将 INSERT 语句添加到与对象 stmt 关联的命令列表中。在 JLU.DEPARTMENT 中插入一行值为“BT6”、“BATCH6 NEWYORK”、“BBBBB1”、“BTT”、“NEW YORK CITY6”的数据
stmt.addBatch("INSERT INTO JLU.DEPARTMENT " +
               "VALUES ('BT6','BATCH6 NEWYORK','000010','A00','NEW YORK CITY6')");

//编写一个语句，该语句将执行批处理中的命令。 保存表中为批处理中每个 SQL 语句添加到表中的行数。
int[] updateCounts = stmt.executeBatch(); 
for (int i = 0; i < updateCounts.length; i++) {
           System.out.println("\nUpdate row count " + updateCounts[i]);
       }

//使用名为 sample 的连接对象提交逻辑工作单元。 
sample.commit();

吉林大学软件学院数据库应用程序开发T9

9

参与讨论