Java/Database SQL JDBC/WebRowSet
Содержание
Convert WebRowSet To String
<source lang="java">
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; }
}
</source>
Create WebRowSet
<source lang="java">
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; }
}
</source>
Get RowSet MetaData From WebRowSet
<source lang="java">
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; }
}
</source>
Output WebRowSet in XML
<source lang="java">
/* 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='1'</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> <currentRow> <columnValue>1</columnValue> <columnValue>nameValue</columnValue> </currentRow>
</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; }
}
</source>
WebRowSet Demo
<source lang="java">
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; }
}
</source>