Joining RowSet Objects When Not Connected to the Data Source - Java JDBC

Java examples for JDBC:CachedRowSet

  1. HOME
  2. Java
  3. JDBC
  4. CachedRowSet

Introduction

Use a JoinRowSet to take data from two relational database tables and join them.

Demo Code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JoinRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

import com.sun.rowset.JoinRowSetImpl;

public class Main {
  static Connection conn = null;
  static CachedRowSet employee = null;
  static CachedRowSet work = null;
  static JoinRowSet jrs = null;

  public static void main(String[] args) throws Exception {
    conn = getConnection();/*from   www .  jav  a2  s. c  om*/
    queryBookAuthor();
    queryAuthorWork();
    joinRowQuery();
  }

  public static void queryBookAuthor() {
    RowSetFactory factory;
    try {
      factory = RowSetProvider.newFactory();
      employee = factory.createCachedRowSet();
      employee.setCommand("SELECT ID, LASTNAME, FIRSTNAME FROM Employee");
      employee.execute(conn);
      while (employee.next()) {
        System.out.println(employee.getString(1) + ": "
            + employee.getString(2) + ", " + employee.getString(3));
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public static void queryAuthorWork() {
    RowSetFactory factory;
    try {
      factory = RowSetProvider.newFactory();
      work = factory.createCachedRowSet();
      work
          .setCommand("SELECT AW.ID, AUTHOR_ID, B.TITLE FROM Product AW, "
              + "BOOK B " + "WHERE B.ID = AW.BOOK_ID");
      work.execute(conn);
      while (work.next()) {
        System.out.println(work.getString(1) + ": "
            + work.getString(2) + " - " + work.getString(3));
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public static void joinRowQuery() {
    try {
      jrs = new JoinRowSetImpl();
      jrs.addRowSet(employee, 1);
      jrs.addRowSet(work, 2);
      while (jrs.next()) {
        System.out.println(jrs.getInt("ID") + ": " + jrs.getString("TITLE")
            + " - " + jrs.getString("FIRSTNAME") + " "
            + jrs.getString("LASTNAME"));
      }

    } catch (SQLException ex) {
      ex.printStackTrace();
    }

  }

  public static Connection getConnection() throws SQLException {
    Connection conn = null;
    String hostname = null;
    String port = null;
    String database = null;
    String username = null;
    String password = null;
    String driver = null;
    String jdbcUrl;
    if (driver.equals("derby")) {
      jdbcUrl = "jdbc:derby://" + hostname + ":" + port + "/" + database;
    } else {
      jdbcUrl = "jdbc:oracle:thin:@" + hostname + ":" + port + ":" + database;
    }
    conn = DriverManager.getConnection(jdbcUrl, username, password);
    System.out.println("Successfully connected");
    return conn;
  }
}

Related Tutorials