Java Solutions: February 2016

Monday, February 29, 2016

SQL: How to update a column with values from another table?

Lets say that you have a FRUIT table containing columns: FRUIT_ID, NAME, PRICE, and POPULARITY. The FRUIT_ID is the primary key

And you have another ORDER table having these columns: ORDER_ID, FRUIT_ID, AMOUNT, and TOTAL_PRICE, The ORDER_ID is the primary key. The FRUIT_ID is the foreign key referencing the FRUIT table.

Now, for some reason, you want the ORDER table to have a FRUIT_NAME column. You can use the following SQL to copy the NAME from the FRUIT table to the ORDER table after you add the FRUIT_NAME column to the ORDER table.

UPDATE ORDER ord
SET FRUIT_NAME = (SELECT frt.NAME
FROM FRUIT frt
WHERE frt.FRUIT_ID = ord.FRUIT_ID)
WHERE FRUIT_NAME is null;

To add the FRUIT_ID column to the ORDER table:

ALTER TABLE ORDER
ADD FRUIT_ID VARCHAR2(9);

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.

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));
}
}

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