Admin: 1 версия

2010-06-01T05:06:30Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== Convert a ResultSet to XML ==



<source lang="java">
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class Main {
public static void main(String args[]) throws Exception {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();
Element results = doc.createElement("Results");
doc.appendChild(results);
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager
.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/access.mdb");

ResultSet rs = con.createStatement().executeQuery("select * from product");
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
while (rs.next()) {
Element row = doc.createElement("Row");
results.appendChild(row);
for (int i = 1; i <= colCount; i++) {
String columnName = rsmd.getColumnName(i);
Object value = rs.getObject(i);
Element node = doc.createElement(columnName);
node.appendChild(doc.createTextNode(value.toString()));
row.appendChild(node);
}
}
DOMSource domSource = new DOMSource(doc);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer transformer = tf.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
transformer.setOutputProperty(OutputKeys.METHOD, "xml");
transformer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");
StringWriter sw = new StringWriter();
StreamResult sr = new StreamResult(sw);
transformer.transform(domSource, sr);
System.out.println(sw.toString());
con.close();
rs.close();
}
}</source>



== Converting types: DATE to String ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn
.createStatement();
st.executeUpdate("create table survey (id int,myDate DATE);");
String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";

PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
pstmt.setString(1, "1");
java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
pstmt.setDate(2, sqlDate);

pstmt.executeUpdate();

ResultSet rs = st.executeQuery("SELECT * FROM survey");
outputResultSet(rs);
rs.close();
st.close();
conn.close();
}
private static void outputResultSet(ResultSet rs) throws Exception {
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
System.out.print(columnName + " ");
}
System.out.println();
System.out.println("----------------------");
while (rs.next()) {
for (int i = 1; i < numberOfColumns + 1; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.println();
}
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



== Determine If a Fetched Value Is NULL ==

<p>To determine whether the actual value is a NULL, wasNull() must be called.</p>



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
while (rs.next()) {
String name = rs.getString(2);
if (rs.wasNull()) {
System.out.println("was NULL");
} else {
System.out.println("not NULL");
}
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



<pre class=codeResult>not NULL
was NULL
not NULL</pre>

== Get Data from a ResultSet ==

<OL><LI>The result set maintains a reference to the current row called the cursor.</LI><LI>The cursor is positioned before the first row when a result set is created.</LI><LI>When a result set"s next() method is called, the cursor moves to the first row of the resultset, and that row becomes the current row.</LI></OL>

== Get int value from ResultSet ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
// extract data from the ResultSet
while (rs.next()) {
int id = rs.getInt(1);
System.out.println("id=" + id);
String name = rs.getString(2);
System.out.println("name=" + name);
if (rs.wasNull()) {
System.out.println("name is null");
} else {
System.out.println("name is not null");
}
System.out.println("---------------");
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



== Get the Number of Rows in a Database Table ==



<source lang="java">
select count(*) form <table-name></source>



== If you Do Not Know the Name, Position, and Type of Each Column, how to get value from ResultSet ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
while (rs.next()) {
int id = rs.getInt(1); // index 1 is the "id" column
String name = rs.getString(2); // index 2 is the "name" column
System.out.println(id);
System.out.println(name);
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



<pre class=codeResult>1
nameValue
2
null
3
Tom</pre>

== Limit the Number of Rows Returned from a SQL Query ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLWarning;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
conn.setAutoCommit(false);
Statement st = conn.createStatement();

st.setFetchSize(1);

st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,"nameValue")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");

rs.setFetchSize(1);

outputResultSet(rs);
checkForWarning(rs.getWarnings());
rs.close();
st.close();
conn.close();
}
static boolean checkForWarning(SQLWarning w) {
if (w == null) {
return false;
}
do {
System.err.println("Warning:\nMessage: " + w.getMessage());
System.err.println("SQL state: " + w.getSQLState());
System.err.println("Vendor code: " + w.getErrorCode() + "\n----------------");
} while ((w = w.getNextWarning()) != null);
return true;
}
private static void outputResultSet(ResultSet rs) throws Exception {
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
System.out.print(columnName + " ");
}
System.out.println();
System.out.println("----------------------");
while (rs.next()) {
for (int i = 1; i < numberOfColumns + 1; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.println();
}
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



== ResultSet: A SQL query returns a ResultSet containing the requested data ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement();

st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");

// extract data from the ResultSet
while (rs.next()) {
int id = rs.getInt(1);
System.out.println("id="+id);
String name = rs.getString(2);
System.out.println("name="+name);
if (rs.wasNull()) {
System.out.println("name is null");
} else {
System.out.println("name is not null");
}
System.out.println("---------------");
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



<pre class=codeResult>id=1
name=nameValue
name is not null
---------------
id=2
name=null
name is null
---------------</pre>

== ResultSet"s Methods to Access Columns by Index ==

<OL><LI>String getString(int columnIndex);</LI><LI>boolean getBoolean(int columnIndex);</LI><LI>byte getByte(int columnIndex);</LI><LI>short getShort(int columnIndex);</LI><LI>int getInt(int columnIndex);</LI><LI>long getLong(int columnIndex);</LI><LI>float getFloat(int columnIndex);</LI><LI>double getDouble(int columnIndex);</LI><LI>byte[] getBytes(int columnIndex);</LI><LI>java.sql.Date getDate(int columnIndex);</LI><LI>java.sql.Date getDate(int columnIndex, java.util.Calendar cal);</LI><LI>java.sql.Time getTime(int columnIndex);</LI><LI>java.sql.Timestamp getTimestamp(int columnIndex);</LI><LI>java.sql.Timestamp getTimestamp(int columnIndex, java.util.Calendar cal);</LI><LI>java.io.InputStream getAsciiStream(int columnIndex);</LI><LI>java.io.InputStream getBinaryStream(int columnIndex);</LI><LI>Object getObject(int columnIndex);</LI><LI>Object getObject(int columnIndex, java.util.Map map);</LI><LI>java.sql.Array getArray(int columnIndex);</LI><LI>java.math.BigDecimal getBigDecimal(int columnIndex);</LI><LI>java.sql.Blob getBlob(int columnIndex);</LI><LI>java.sql.Clob getClob(int columnIndex);</LI><LI>java.io.Reader getCharacterStream(int columnIndex);</LI><LI>java.sql.Ref getRef(int columnIndex);</LI><LI>java.net.URL getURL(int columnIndex);</LI></OL>

== ResultSet"s Methods to Access Columns by Name ==

<OL><LI>String getString(String columnName);</LI><LI>boolean getBoolean(String columnName);</LI><LI>byte getByte(String columnName);</LI><LI>short getShort(String columnName);</LI><LI>int getInt(String columnName);</LI><LI>long getLong(String columnName);</LI><LI>float getFloat(String columnName);</LI><LI>double getDouble(String columnName);</LI><LI>byte[] getBytes(String columnName);</LI><LI>java.sql.Date getDate(String columnName);</LI><LI>java.sql.Date getDate(String columnName, java.util.Calendar cal);</LI><LI>java.sql.Time getTime(String columnName);</LI><LI>java.sql.Timestamp getTimestamp(String columnName);</LI><LI>java.sql.Timestamp getTimestamp(String columnName, java.util.Calendar cal);</LI><LI>java.io.InputStream getAsciiStream(String columnName);</LI><LI>java.io.InputStream getBinaryStream(String columnName);</LI><LI>Object getObject(String columnName);</LI><LI>Object getObject(String columnName, java.util.Map map);</LI><LI>java.sql.Array getArray(String columnName);</LI><LI>java.math.BigDecimal getBigDecimal(String columnName);</LI><LI>java.sql.Blob getBlob(String columnName);</LI><LI>java.sql.Clob getClob(String columnName);</LI><LI>java.io.Reader getCharacterStream(String columnName);</LI><LI>java.sql.Ref getRef(String columnName);</LI><LI>java.net.URL getURL(String columnName);</LI></OL>

== Retrieving the Value of a Column Using the Column Name ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
while (rs.next()) {
String name = rs.getString("name");
System.out.println(name);
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



<pre class=codeResult>nameValue
null</pre>

== Retrieving the Value of a Column Using the Index Number ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
while (rs.next()) {
String name = rs.getString(2);
System.out.println(name);
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



<pre class=codeResult>nameValue
null</pre>

== Selecting all rows from a table and creates a result set: ==



<source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
while (rs.next()) {
String name = rs.getString("name");
System.out.println(name);
}
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>



<pre class=codeResult>nameValue
null
Tom</pre>

← Предыдущая	Версия 05:06, 1 июня 2010
(нет различий)

Java Tutorial/Database/Query ResultSet - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010