Java/Database SQL JDBC/WebRowSet — различия между версиями

Материал из Java эксперт
Перейти к: навигация, поиск
 
м (1 версия)
 
(нет различий)

Текущая версия на 06:35, 1 июня 2010

Convert WebRowSet To String

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st = conn.createStatement();
    String query = "select * from survey where 1 = 0";
    WebRowSet webRS = new WebRowSetImpl();
    webRS.setCommand(query);
    webRS.execute(conn);
    // convert xml to a String object
     StringWriter sw = new StringWriter();
     webRS.writeXml(sw);
     System.out.println(sw.toString());
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}





Create WebRowSet

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.sql.RowSetMetaData;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");
    String sqlQuery = "SELECT * FROM survey";
    WebRowSet webRS = new WebRowSetImpl();
    webRS.setCommand(sqlQuery);
    webRS.execute(conn);
    // create RowSetMetaData object
    RowSetMetaData rsMD = (RowSetMetaData) webRS.getMetaData();
    System.out.println("rsMD=" + rsMD);
    if (rsMD == null) {
      System.out.println("vendor does not support RowSetMetaData");
    } else {
      int columnCount = rsMD.getColumnCount();
      System.out.println("columnCount=" + columnCount);
    }
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}





Get RowSet MetaData From WebRowSet

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.sql.RowSetMetaData;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");
    String sqlQuery = "SELECT * FROM survey";
    WebRowSet webRS = new WebRowSetImpl();
    webRS.setCommand(sqlQuery);
    webRS.execute(conn);
    // create RowSetMetaData object
    RowSetMetaData rsMD = (RowSetMetaData) webRS.getMetaData();
    System.out.println("rsMD=" + rsMD);
    if (rsMD == null) {
      System.out.println("vendor does not support RowSetMetaData");
    } else {
      int columnCount = rsMD.getColumnCount();
      System.out.println("columnCount=" + columnCount);
    }
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}





Output WebRowSet in XML

/*
Output:
<?xml version="1.0"?>
<webRowSet xmlns="http://java.sun.ru/xml/ns/jdbc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.ru/xml/ns/jdbc http://java.sun.ru/xml/ns/jdbc/webrowset.xsd">
  <properties>
    <command>SELECT * FROM survey WHERE id=&apos;1&apos;</command>
    <concurrency>1008</concurrency>
    <datasource><null/></datasource>
    <escape-processing>true</escape-processing>
    <fetch-direction>1000</fetch-direction>
    <fetch-size>0</fetch-size>
    <isolation-level>2</isolation-level>
    <key-columns>
    </key-columns>
    <map>
    </map>
    <max-field-size>0</max-field-size>
    <max-rows>0</max-rows>
    <query-timeout>0</query-timeout>
    <read-only>true</read-only>
    <rowset-type>ResultSet.TYPE_SCROLL_INSENSITIVE</rowset-type>
    <show-deleted>false</show-deleted>
    <table-name>survey</table-name>
    <url><null/></url>
    <sync-provider>
      <sync-provider-name>com.sun.rowset.providers.RIOptimisticProvider</sync-provider-name>
      <sync-provider-vendor>Sun Microsystems Inc.</sync-provider-vendor>
      <sync-provider-version>1.0</sync-provider-version>
      <sync-provider-grade>2</sync-provider-grade>
      <data-source-lock>1</data-source-lock>
    </sync-provider>
  </properties>
  <metadata>
    <column-count>2</column-count>
    <column-definition>
      <column-index>1</column-index>
      <auto-increment>false</auto-increment>
      <case-sensitive>false</case-sensitive>
      <currency>false</currency>
      <nullable>1</nullable>
      <signed>true</signed>
      <searchable>true</searchable>
      <column-display-size>11</column-display-size>
      <column-label>ID</column-label>
      <column-name>ID</column-name>
      <schema-name>PUBLIC</schema-name>
      <column-precision>10</column-precision>
      <column-scale>0</column-scale>
      <table-name>SURVEY</table-name>
      <catalog-name></catalog-name>
      <column-type>4</column-type>
      <column-type-name>INTEGER</column-type-name>
    </column-definition>
    <column-definition>
      <column-index>2</column-index>
      <auto-increment>false</auto-increment>
      <case-sensitive>false</case-sensitive>
      <currency>false</currency>
      <nullable>1</nullable>
      <signed>false</signed>
      <searchable>true</searchable>
      <column-display-size>32766</column-display-size>
      <column-label>NAME</column-label>
      <column-name>NAME</column-name>
      <schema-name>PUBLIC</schema-name>
      <column-precision>2147483647</column-precision>
      <column-scale>0</column-scale>
      <table-name>SURVEY</table-name>
      <catalog-name></catalog-name>
      <column-type>12</column-type>
      <column-type-name>VARCHAR</column-type-name>
    </column-definition>
  </metadata>
  <data>
    <currentRow>
      <columnValue>1</columnValue>
      <columnValue>nameValue</columnValue>
    </currentRow>
  </data>
</webRowSet>
*/


import java.io.File;
import java.io.FileWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,"anotherValue")");    
    
    WebRowSet webRS;
    ResultSet rs = null;
    Statement stmt = null;
    stmt = conn.createStatement();
    webRS = null;
    String sqlQuery = "SELECT * FROM survey WHERE id="1"";
    webRS = new WebRowSetImpl();
    webRS.setCommand(sqlQuery);
    webRS.execute(conn);
    FileWriter fw = null;
    File file = new File("1.xml");
    fw = new FileWriter(file);
    System.out.println("Writing db data to file " + file.getAbsolutePath());
    webRS.writeXml(fw);
    // convert xml to a String object
    StringWriter sw = new StringWriter();
    webRS.writeXml(sw);
    System.out.println("==============");
    System.out.println(sw.toString());
    System.out.println("==============");
    fw.flush();
    fw.close();
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}





WebRowSet Demo

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st = conn.createStatement();
    String query = "select * from survey where 1 = 0";
    WebRowSet webRS = new WebRowSetImpl();
    webRS.setCommand(query);
    webRS.execute(conn);
    // convert xml to a String object
     StringWriter sw = new StringWriter();
     webRS.writeXml(sw);
     System.out.println(sw.toString());
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}


Источник — «http://jexp.ru/index.php?title=Java/Database_SQL_JDBC/WebRowSet&oldid=6987»