Java Tutorial/Database/Query ResultSet
Содержание
- 1 Convert a ResultSet to XML
- 2 Converting types: DATE to String
- 3 Determine If a Fetched Value Is NULL
- 4 Get Data from a ResultSet
- 5 Get int value from ResultSet
- 6 Get the Number of Rows in a Database Table
- 7 If you Do Not Know the Name, Position, and Type of Each Column, how to get value from ResultSet
- 8 Limit the Number of Rows Returned from a SQL Query
- 9 ResultSet: A SQL query returns a ResultSet containing the requested data
- 10 ResultSet"s Methods to Access Columns by Index
- 11 ResultSet"s Methods to Access Columns by Name
- 12 Retrieving the Value of a Column Using the Column Name
- 13 Retrieving the Value of a Column Using the Index Number
- 14 Selecting all rows from a table and creates a result set:
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
To determine whether the actual value is a NULL, wasNull() must be called.
<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>
not NULL was NULL not NULL
Get Data from a ResultSet
- The result set maintains a reference to the current row called the cursor.
- The cursor is positioned before the first row when a result set is created.
- 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.
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>
1 nameValue 2 null 3 Tom
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>
id=1 name=nameValue name is not null --------------- id=2 name=null name is null ---------------
ResultSet"s Methods to Access Columns by Index
- String getString(int columnIndex);
- boolean getBoolean(int columnIndex);
- byte getByte(int columnIndex);
- short getShort(int columnIndex);
- int getInt(int columnIndex);
- long getLong(int columnIndex);
- float getFloat(int columnIndex);
- double getDouble(int columnIndex);
- byte[] getBytes(int columnIndex);
- java.sql.Date getDate(int columnIndex);
- java.sql.Date getDate(int columnIndex, java.util.Calendar cal);
- java.sql.Time getTime(int columnIndex);
- java.sql.Timestamp getTimestamp(int columnIndex);
- java.sql.Timestamp getTimestamp(int columnIndex, java.util.Calendar cal);
- java.io.InputStream getAsciiStream(int columnIndex);
- java.io.InputStream getBinaryStream(int columnIndex);
- Object getObject(int columnIndex);
- Object getObject(int columnIndex, java.util.Map map);
- java.sql.Array getArray(int columnIndex);
- java.math.BigDecimal getBigDecimal(int columnIndex);
- java.sql.Blob getBlob(int columnIndex);
- java.sql.Clob getClob(int columnIndex);
- java.io.Reader getCharacterStream(int columnIndex);
- java.sql.Ref getRef(int columnIndex);
- java.net.URL getURL(int columnIndex);
ResultSet"s Methods to Access Columns by Name
- String getString(String columnName);
- boolean getBoolean(String columnName);
- byte getByte(String columnName);
- short getShort(String columnName);
- int getInt(String columnName);
- long getLong(String columnName);
- float getFloat(String columnName);
- double getDouble(String columnName);
- byte[] getBytes(String columnName);
- java.sql.Date getDate(String columnName);
- java.sql.Date getDate(String columnName, java.util.Calendar cal);
- java.sql.Time getTime(String columnName);
- java.sql.Timestamp getTimestamp(String columnName);
- java.sql.Timestamp getTimestamp(String columnName, java.util.Calendar cal);
- java.io.InputStream getAsciiStream(String columnName);
- java.io.InputStream getBinaryStream(String columnName);
- Object getObject(String columnName);
- Object getObject(String columnName, java.util.Map map);
- java.sql.Array getArray(String columnName);
- java.math.BigDecimal getBigDecimal(String columnName);
- java.sql.Blob getBlob(String columnName);
- java.sql.Clob getClob(String columnName);
- java.io.Reader getCharacterStream(String columnName);
- java.sql.Ref getRef(String columnName);
- java.net.URL getURL(String columnName);
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>
nameValue null
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>
nameValue null
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>
nameValue null Tom
