Java Solutions: ResultSet

Showing posts with label ResultSet. Show all posts

Wednesday, February 3, 2016

Java try-with-resources statement vs. try-finally block

The try-with-resources is a feature added in Java 1.7. A resource is an object implementing the java.lang.AutoCloseable interface. The try-with-resources statement declares a block on one or more resources. The resources automatically close at the end of the block. The resources are always closed regardless of exceptions. If more than one resources are included, the resources are separated by semicolon.

1. An example of using the try-with-resources block.

public void printData(String sql) throws SQLException {
//try-with-resources block
try (PreparedStatement ps = con.prepareStatement(sql)) {
//try-with-resources block
try (ResultSet rs = ps.executeQuery()) {
//code processing the ResultSet.
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
}

The above code automatically closes the rs at the end of the inner try-with-resources block and the ps at the end of the outer try-with-resources block.

2. Comparing the above try-with-resources code with the below try-finally code.

public void printData(String sql) throws SQLException {
  PreparedStatement ps = null;
  ResultSet rs = null;

//try-finally block
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
//code processing the ResultSet.
while (rs.next()) {
System.out.println(rs.getString(1));
}
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
}
}

Both codes ensure that the rs and ps are closed. However, if the database query code or the result processing code throws a SQLException and then one of the rs.close() and ps.close() methods also throws a SQLException, the SQLException thrown by the printData(String sql) is different using try-with-resources statement from try-finally statement. With the try-with-resources statement, the printData(String sql) throws the SQLException occurred from the database query code or the result processing code. Whereas the printData(String sql) throws the SQLException generated by the close() method if the  try-finally block is used.

3. Examples of including more than one resources.

try (BufferedReader reader = new BufferedReader(new FileReader(file));
BufferedWriter writer = new BufferedWriter(new FileWriter(file2, true))) {
String line;
while (line = reader.readLine() != null) {
writer.write(line);
}
}

public void printData(String sql) throws SQLException {
try (PreparedStatement ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
//code processing the ResultSet.
while (rs.next()) {
System.out.println(rs.getString(1));
}
}

}
---------------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Tuesday, April 15, 2014

CLOBs, NCLOBs and BLOBs

CLOB, NCLOB and BLOB are database data types with data size up to 128 terabytes. The data size of regular VARCHAR and VARBINARY is 32 kilobytes.

CLOB stands for character large object, which is a Unicode character string. It is appropriate for storing text-orientated information.

NCLOB stands for national character large object, which is a Unicode character string. This data type like the CLOB data type, is appropriate for storing text-orientated information.

BLOB stands for binary large object, which is a binary string. This data type is appropriate for storing images, sound, and videos.

Following is a sample code of using these data types.

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.PreparedStatement;

public class Test {
public static void main(String[] args) {
Connection conn = null;
try {
conn = <your method of getting the database connection>;

//Save data to database

//Create the data type objects
Clob theClob = conn.createClob();
NClob theNclob = conn.createNClob();
Blob theBlob = conn.createBlob();

//Add data to the data type objects
BufferedReader charReader = new BufferedReader(
new FileReader("descritpion.txt"));
StringBuilder file = new StringBuilder();
String line = "";
while ((line = charReader.readLine()) != null){
file.append(line);
}

theClob.setString(1, file.toString());
theNclob.setString(1, file.toString());
charReader.close();

BufferedInputStream input = new BufferedInputStream(
new FileInputStream("happyBird.gif"));

OutputStream byteWriter = theBlob.setBinaryStream(1);
int theChar = -1;
while ((theChar = input.read()) != -1) {
byteWriter.write(theChar);
}
byteWriter.flush();
input.close();
byteWriter.close();

//Insert the data into database
String sql = "INSERT into picture values(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setClob(1, theClob);
pstmt.setNClob(2, theNclob);
pstmt.setBlob(3, theBlob);
pstmt.executeUpdate();
pstmt.close();

//Retrieve data from database

String sql2 = "SELECT title, description, image from picture";
pstmt = conn.prepareStatement(sql2);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Clob myClob = rs.getClob(1);
String title = myClob.getSubstring(1, myClob.length());

NClob myNclob = rs.getNClob(2);
String description = myNclob.getSubstring(1, myNclob.length());

Blob myBlob = rs.getBlob(3);
byte[] imageArray = myBlob.getBytes(1, myBlob.length());
}
rs.close();
pstmt.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
}
}

References:

1.Using Large Objects
2. 26 Oracle Data Types

---------------------------------------------------------------------------------------------------------------

Thursday, January 16, 2014

java.sql.SQLException: Invalid column index

The exception may be caused by using 0 or a number larger than the number of columns in your sql as the column index. While in Java the convention of a collection count starts from 0, it starts from 1 in database query.

A particular situation is using alias in query with union. The alias instead of the real column name should be used in the order by clause. Otherwise, it will throw the exception.

Example 1: Select

String sql = "SELECT name, id, address from employee";

ResultSet rs = statement.executeQuery(sql);

while (rs.next()) {
//exception because 0 if an invalid index.
String c0 = rs.getString(0);
String c1 = rs.getString(1);
long id = rs.getLong(2);
String address = rs.getString(3);
//Exception because the sql has only 3 columns
long deptId = rs.getLong(4);
}

Example 2: Insert

String sql = "INSERT INTO employee (name, id, department, empdate) values (?, ?, ?, ?)";

PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "John Young");
ps.setLong(2, 90000);
ps.setLong(3, 1);
ps.setInt(4, 2017200);
//Exception because the sql has only 4 columns
ps.setString(5, "Excellent in public speaking.");

Example 3: Union

Select storeName as "store name", storeAddress, storePhone from store where storeName like '%Mart%'
UNION
Select storeName as "store name", storeAddress, storePhone from store where storePhone like '505%'
//Exception because it cannot identify the storeName
order by storeName;

The working query is
Select storeName as "store name", storeAddress, storePhone from store where storeName like '%Mart%'
UNION
Select storeName as "store name", storeAddress, storePhone from store where storePhone like '505%'
order by "store name";

-------------------------------------------------------------------------------------------------------------