Java Solutions: January 2015

Monday, January 19, 2015

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

This happened to me when I was trying to drop a table. It means that the table is being used and locked by another process. Following are the steps to solve such a problem.

1. If you have just executed a non select SQL, do a commit or rollback to let it release the resources that it holds. Then, try to drop the table again. If this does not solve your problem, perform step 2 and step 3 below.

2. Identify the session that holds the resources by executing the following SQL.

SET LINESIZE 100

SELECT s.INST_ID, s.SID, s.SERIAL#, p.SPID, s.USERNAME, s.PROGRAM
FROM gv$session s, gv$process p
WHERE p.addr = s.paddr AND p.inst_id = s.inst_id
AND s.type != 'BACKGROUND';

You will see something like this.
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 77 241 5448 CJOHN JDBC Thin Client
1 163 60667 5466 STAO JDBC Thin Client
1 195 827 8836 STAO JDBC Thin Client
1 233 403 5476 EJAY JDBC Thin Client
1 15 321 5159 EJAY JDBC Thin Client

Use the PROGRAM and USERNAME to identify the SPID that holds the resources.

3. Kill the process that holds the resources
At your command line type kill -9 <spid> and then return.

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

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.

SQL: WHERE clause with SELECT multiple columns IN / NOT IN

You need to put the columns separated by comma in parentheses before IN / NOT IN and put the SELECT statement after the IN / NOT IN in parentheses too. For example you want to get the information of all the managers except the managers in the human resource department, the SQL below works.

SELECT *
FROM MANAGER m1
WHERE (m1.ID, m1.SSN, m1.PHONE) NOT IN (
SELECT m2.ID, m2.SSN, m2.PHONE
FROM MANAGER m2, EMPLOYEE e
WHERE m2.ID = e.ID AND e.departmentID = 'humanResource';

OR
SELECT *
FROM MANAGER m1
WHERE (m1.ID, m1.SSN, m1.PHONE) NOT IN (
SELECT e.ID, e.SocialID, e.PhNbr
FROM EMPLOYEE e
WHERE e.departmentID = 'humanResource';

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

SQL: Create a new table by copying data from existing tables

1. You can use the following SQL to make a copy of an existing table.

CREATE TABLE <new table name>
AS
SELECT [* / <columns separated by comma>]
FROM <existing table name>
WHERE ........;

For example, you want to make a copy of your FRUIT table so that you can do some testing commands on it, you can use the following SQL.

CREATE TABLE FRUIT_COPY AS SELECT * FROM FRUIT;

2. If you just want to create a table having the same columns from an existing table but not the data, you can add a WHERE clause that never returns true.

CREATE TABLE FRUIT_COPY
AS SELECT * FROM FRUIT
WHERE 1=2;

3. Create a table by copying data from multiple existing tables.

CREATE TABLE <new table name>
AS
SELECT <columns separated by comma>
FROM <existing table names separated by comma>
WHERE ........;

Similarly, by using 'WHERE 1=2' as the where clause, you create an empty new table having the columns you selected from the existing tables. If you would like to copy the data later, you can use the SELECT into SQL to copy the data.

SELECT <columns separated by comma>
INTO <new table name>
FROM <existing table names separated by comma>
WHERE ........;

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

Friday, January 16, 2015

SQL: INSERT by copying values from two tables - Insert in combination of Select

Your Sweet Fruit company just acquired another Fruit company. You hired a new manager to be the contact person for the orders in the newly acquired company. And you want to migrate the Fruit data from the acquired company to your central database.

You can use the following SQL to do the job.

INSERT INTO FRUIT (NAME, PRICE, ORDER, CONTACT)
SELECT unique NAME, PRICE, ORDER, empName || ' ' || empPhone
FROM legacy_fruit, EMPLOYEE
WHERE empID = 100099887;

References

1. SQL: INSERT by copying values of existing columns of a table - Insert in combination of Select
2. SQL: INSERT by copying values from existing records for some columns and using other values for other columns - Insert in combination of Select

SQL: INSERT by copying values from existing records for some columns and using other values for other columns - Insert in combination of Select

For example, you have a table A, which has columns Col1, Col2, ...........Col20. You now want to copy the values of Col2, Col5, Col7 to a new table B for some reason such as allow more people to access the data. Table B has four columns: Field1, Field2, Field3, Field4. Field4 has the date when the record is created.

This is the SQL to do it.

INSERT INTO B (Field1, Field2, Field3, Field4)
SELECT Col2, Col5, Col7, sysdate
FROM A
WHERE Col1 > to_date ("11/12/2000", "mm/dd/yyyy");

References
1. SQL: INSERT by copying values of existing columns of a table - Insert in combination of Select
2. SQL: INSERT by copying values from two tables - Insert in combination of Select

SQL: INSERT by copying values of existing columns of a table - Insert in combination of Select

For example, you have a table A, which has columns Col1, Col2, ...........Col20. You now want to copy the values of Col2, Col5, Col7 to a new table B for some reason such as allow more people to access the data. Table B has three columns: Field1, Field2, Field3.

This is the SQL to do it.

INSERT INTO B (Field1, Field2, Field3)
SELECT Col2, Col5, Col7
FROM A
WHERE Col1 > to_date("11/12/2000", "mm/dd/yyyy");

References

1. SQL: INSERT by copying values from existing records for some columns and using other values for other columns - Insert in combination of Select

2. SQL: INSERT by copying values from two tables - Insert in combination of Select

Wednesday, January 14, 2015

SQL: update the value of a column conditionally

The following template updates the value of a column conditionally.

UPDATE <table name>
SET <column name> =
CASE WHEN <condition met> THEN <new value> ELSE <column name> END
WHERE ......;

What it does is that if the condition is met, set the column value to the new value, otherwise, use its old value.

For example, if you have a FRUIT table with the following data.

NAME PRICE ORDER LastOrderDate

APPLE 1.99 5 12/13/2014
ORANGE 0.99 99 1/1/2015
PEAR 1.99 150 1/10/2015

If you want to reduce the price by 0.5 if by a certain order date the order is less than 100, you can use the following sql.

UPDATE FRUIT
SET PRICE =
CASE WHEN (ORDER < 100) THEN (PRICE - 0.5) ELSE PRICE END
WHERE LastOrderDate < TO_DATE('01/05/2014', 'mm/dd/yyyy');

The Result would be:

NAME PRICE ORDER LastOrderDate

APPLE 1.49 5 12/13/2014
ORANGE 0.49 99 1/1/2015
PEAR 1.99 150 1/10/2015

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

Tuesday, January 13, 2015

DynamicReports:Conditional column styles

The ConditionalStyleBuilder can be used to set the style of a column conditionally. Following sample code shows how to set the background color to Orange and font to bold when the Price value is more than 1.

import java.awt.Color;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import net.sf.dynamicreports.jasper.builder.JasperReportBuilder;
import net.sf.dynamicreports.report.base.expression.AbstractSimpleExpression;
import net.sf.dynamicreports.report.builder.DynamicReports;
import net.sf.dynamicreports.report.builder.column.Columns;
import net.sf.dynamicreports.report.builder.column.TextColumnBuilder;
import net.sf.dynamicreports.report.builder.component.Components;
import net.sf.dynamicreports.report.builder.datatype.BigDecimalType;
import net.sf.dynamicreports.report.builder.datatype.DataTypes;
import net.sf.dynamicreports.report.builder.style.ConditionalStyleBuilder;
import net.sf.dynamicreports.report.builder.style.StyleBuilder;
import net.sf.dynamicreports.report.builder.style.Styles;
import net.sf.dynamicreports.report.constant.HorizontalAlignment;
import net.sf.dynamicreports.report.constant.PageType;
import net.sf.dynamicreports.report.datasource.DRDataSource;
import net.sf.dynamicreports.report.definition.ReportParameters;
import net.sf.dynamicreports.report.exception.DRException;
import net.sf.jasperreports.engine.JRDataSource;

public class DynamicReportsTest {

public DynamicReportsTest() {
build();
}

public void build() {
JasperReportBuilder report = DynamicReports.report();
report.setPageFormat(PageType.LETTER);
// report.setPageFormat(PageType.A4, PageOrientation.PORTRAIT);
CurrencyType ct = new CurrencyType();

TextColumnBuilder<BigDecimal> priceColumn = Columns.column("Price", "price", ct);
TextColumnBuilder<Integer> quantityOrderedColumn = Columns.column("Quantity Ordered", "quantityOrdered", DataTypes.integerType());
TextColumnBuilder<BigDecimal> totalPay = priceColumn.multiply(quantityOrderedColumn)
.setTitle("Amount Paid")
.setPattern(ct.getPattern());
report.columns(Columns.columnRowNumberColumn("Item"),
Columns.column("Name", "name", DataTypes.stringType()),
priceColumn,
quantityOrderedColumn,
totalPay
);

StyleBuilder bold = Styles.style().bold();
StyleBuilder centeredBold = Styles.style(bold)
.setHorizontalAlignment(HorizontalAlignment.CENTER);
StyleBuilder columnStyle = Styles.style(centeredBold);

//Create the ConditionalStyleBuilder
ConditionalStyleBuilder condColumnStyle = Styles.conditionalStyle(new CCExpression())
.bold()
.setHorizontalAlignment(HorizontalAlignment.CENTER)//.style(centeredBold)
.setBackgroundColor(Color.ORANGE)
.setBorder(Styles.pen1Point());

//Create a StyleBuilder using the ConditionalStyleBuilder
StyleBuilder priceStyle = Styles.style().conditionalStyles(condColumnStyle);

report.setColumnTitleStyle(columnStyle);
report.setColumnStyle(Styles.style().setHorizontalAlignment(HorizontalAlignment.CENTER));

//Apply the StyleBuilder to a report column
priceColumn.setStyle(priceStyle);
report.highlightDetailEvenRows();

report.title(Components.text("Test Report")
.setStyle(centeredBold));
report.title(Components.text("Detailed Report")
.setStyle(centeredBold));
SimpleDateFormat format = new SimpleDateFormat("MM/dd/YY");

report.pageFooter(Components.horizontalFlowList().add(Components.text("Page "))
.add(Components.pageNumber())
.setStyle(bold));

report.setDataSource(createDataSource());

try {
report.show();
}catch(DRException e){
e.printStackTrace();
}
}

private class CCExpression extends AbstractSimpleExpression<Boolean> {
public Boolean evaluate (ReportParameters param){
BigDecimal price = param.getFieldValue("price");
if (price.doubleValue() > 1){
return true;
} else {
return false;
}
}
}

private class CurrencyType extends BigDecimalType {
@Override
public String getPattern() {
return "$ #,###.##";
}
}

private JRDataSource createDataSource() {
DRDataSource dataSource = new DRDataSource("name", "price", "quantityOrdered");
dataSource.add("Apple", new BigDecimal(1.29), 120);
dataSource.add("Apple", new BigDecimal(1.69), 150);
dataSource.add("Orange", new BigDecimal(0.99), 130);
dataSource.add("Orange", new BigDecimal(0.96), 100);
dataSource.add("Mange", new BigDecimal(0), 300);
return dataSource;
}

public static void main(String[] args){
new DynamicReportsTest();
}
}

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

Java: calculate the number of days between two Dates

The following procedures help you calculate days between two Dates.

1. Java 8:
LocalDate d1 = LocalDate.of (2014, 05, 11);
LocalDate d2 = LocalDate.of (1998, 12, 23);

long dayDiff = d2.until(d1, DAYS);

2. Before Java 8
String sDate1 = "05/11/2014";
String sDate2 = "12/23/1998";

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

Date d1 = sdf.parse(sDate1);
Date d2 = sdf.parse(sDate2);

long timeDiff = d1.getTimeInMillis() - d2.getTimeInMillis();

long dayDiff = TimeUnit.DAYS.convert(timeDiff, TimeUnit.MILLISECONDS);
//or convert the time to days using code below
//long dayDiff = timeDiff / 1000 /(24 * 60 * 60);

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

The action can't be completed because the file is open in java(tm) platform se binary - not able to delete a file

This usually happens when after you stop running a program, you open the directory on your hard drive to delete the jar file. Even though the program stopped running, the jar file is still open in Java virtual machine.

IDE
If you are running the program in a Java development IDE, check the Output pane to make sure that all related executions are terminated and removed. If this does not solve the problem, close and reopen the IDE shall get it resolved.

Windows
If you are using Windows, you can open the Task Manager by press the three key Ctl+Alt+Delete at the same time. Look at the Applications tab and the Processes tab to end all the java processes.

You can also open the command line and type 'tasklist | grep java' to get all the running java programs. Terminate all the related java programs by command 'taskkill /F /PID pid'.

UNIX/Linux
If you are running on a UNIX/Linux terminal, use 'ps -ef' to get all the processes, and then use 'kill -9 pid' to kill all the programs that use the file you are trying to delete from the folder.

If none of the above works, log out and re-log in your computer will be sufficient to solve the problem. It should not go that far that requires you to reboot your computer, even though that will definitely fix the problem.

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

Monday, January 12, 2015

DynamicReports: Add a line in front of / after a sub report condittionally

The following code demonstrate how to add an horizontal line before a subreport only when the subreport is not null or only when it has data.

import java.util.ArrayList;
import java.util.List;
import net.sf.dynamicreports.jasper.builder.JasperReportBuilder;
import net.sf.dynamicreports.report.base.expression.AbstractSimpleExpression;
import static net.sf.dynamicreports.report.builder.DynamicReports.cmp;
import static net.sf.dynamicreports.report.builder.DynamicReports.col;
import static net.sf.dynamicreports.report.builder.DynamicReports.exp;
import static net.sf.dynamicreports.report.builder.DynamicReports.report;
import static net.sf.dynamicreports.report.builder.DynamicReports.stl;
import net.sf.dynamicreports.report.builder.component.SubreportBuilder;
import net.sf.dynamicreports.report.builder.datatype.DataTypes;
import net.sf.dynamicreports.report.constant.HorizontalAlignment;
import net.sf.dynamicreports.report.definition.ReportParameters;
import net.sf.dynamicreports.report.exception.DRException;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;

public class PageSubreportTest {
public static void main(String[] args){
new PageSubreportTest();
}

public PageSubreportTest() {
JasperReportBuilder report = build();
try {
report.show();
}catch (DRException e){
e.printStackTrace();
}
}

private JasperReportBuilder build() {
JasperReportBuilder report = report();
report.setDataSource(createDataSource());

SubreportBuilder storeSubreport = cmp.subreport(buildStoreSubReport())
.setDataSource(exp.subDatasourceBeanCollection("storeData"));
SubreportBuilder fruitSubreport = cmp.subreport(buildFruitSubReport())
.setDataSource(exp.subDatasourceBeanCollection("fruitData"));

report.detail(cmp.horizontalList(cmp.horizontalGap(50),storeSubreport, cmp.horizontalGap(50)),
cmp.verticalGap(15),

//To add a horizontal line only when fruitSubreport has data
cmp.line().setPen(stl.pen1Point())
.setPrintWhenExpression(new LinePrintExpression()),

cmp.verticalGap(15),

fruitSubreport,
cmp.verticalGap(15),
//To add a horizontal line only when fruitSubreport has data
cmp.line().setPen(stl.pen1Point())
.setPrintWhenExpression(new LinePrintExpression()));

report.title(cmp.text("Fuit Stores Report")
.setStyle(stl.style().bold()
.setHorizontalAlignment(HorizontalAlignment.CENTER)));
report.pageFooter(cmp.pageXofY());

return report;
}

private class LinePrintExpression extends AbstractSimpleExpression<Boolean> {
public Boolean evaluate(ReportParameters param){
List<FruitData> list = param.getValue("fruitData");
if (list == null || list.isEmpty()){
return false;
} else {
return true;
}
}
}

private JasperReportBuilder buildStoreSubReport(){
JasperReportBuilder storeReport = report();
storeReport.columns(col.column("", "storeInfo", DataTypes.stringType()));

return storeReport;
}

private JasperReportBuilder buildFruitSubReport(){
JasperReportBuilder fruitReport = report();
fruitReport.columns(col.column("Name", "fruitName", DataTypes.stringType()),
col.column("Price", "fruitPrice", DataTypes.doubleType())
.setPattern("$#,###,##0.00")
.setHorizontalAlignment(HorizontalAlignment.LEFT),
col.column("Description", "des", DataTypes.stringType()));

fruitReport.setColumnTitleStyle(stl.style().bold());
return fruitReport;
}

private JRBeanCollectionDataSource createDataSource() {
List<ReportData> dataSource = new ArrayList<ReportData>();

ReportData data = new ReportData();
List<StoreData> storeList = new ArrayList<StoreData>();
List<FruitData> fruitList = new ArrayList<FruitData>();
//first set of data
StoreData storeData = new StoreData();
storeData.setStoreInfo("Delicious Fruit");
storeList.add(storeData);
storeData = new StoreData();
storeData.setStoreInfo("554 Main Street, Burlington, MN 33596");
storeList.add(storeData);
data.setStoreData(storeList);

FruitData fruitData = new FruitData();
fruitData.setFruitName("Apple");
fruitData.setFruitPrice(1.98d);
fruitData.setDes("Medium juicy");
fruitList.add(fruitData);
fruitData = new FruitData();
fruitData.setFruitName("Orange");
fruitData.setFruitPrice(0.98d);
fruitData.setDes("Very juicy");
fruitList.add(fruitData);
data.setFruitData(fruitList);
dataSource.add(data);

//second set of data
data = new ReportData();
storeList = new ArrayList<StoreData>();
// fruitList = new ArrayList<FruitData>();

storeData = new StoreData();
storeData.setStoreInfo("Fruit Outlet");
storeList.add(storeData);
storeData = new StoreData();
storeData.setStoreInfo("99 Uphill Ave., Rochester, MN 33596");
storeList.add(storeData);
data.setStoreData(storeList);
// data.setFruitData(fruitList);
dataSource.add(data);

//Third set of data
data = new ReportData();
storeList = new ArrayList<StoreData>();
fruitList = new ArrayList<FruitData>();

storeData = new StoreData();
storeData.setStoreInfo("Fruit Depot");
storeList.add(storeData);
storeData = new StoreData();
storeData.setStoreInfo("233 Park Viewers Ave., Bay East, FL 33596");
storeList.add(storeData);
data.setStoreData(storeList);

fruitData = new FruitData();
fruitData.setFruitName("Papaya");
fruitData.setFruitPrice(2.98d);
fruitData.setDes("Medium juicy");
fruitList.add(fruitData);
fruitData = new FruitData();
fruitData.setFruitName("Mango");
fruitData.setFruitPrice(3.98d);
fruitData.setDes("Medium juicy");
fruitList.add(fruitData);
data.setFruitData(fruitList);
dataSource.add(data);

return new JRBeanCollectionDataSource(dataSource);
}

public class ReportData {
private List<StoreData> storeData;
private List<FruitData> fruitData;

public List<StoreData> getStoreData() { return storeData; }
public void setStoreData(List<StoreData> s) { storeData = s; }
public List<FruitData> getFruitData() { return fruitData; }
public void setFruitData (List<FruitData> s) { fruitData = s; }
}

public class StoreData {
private String storeInfo;

public String getStoreInfo() { return storeInfo; }
public void setStoreInfo(String s) { storeInfo = s; }
}

public class FruitData {
private String fruitName;
private Double fruitPrice;
private String des;

public String getFruitName() { return fruitName; }
public void setFruitName(String s) { fruitName = s; }
public Double getFruitPrice() { return fruitPrice; }
public void setFruitPrice(Double s) { fruitPrice = s; }
public String getDes() { return des; }
public void setDes(String s) { des = s; }
}
}

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

Friday, January 9, 2015

SQLException: ORA-00904 TableName.ColumnName: invalid

The exception occurs when the column name in a sql statement does not match any column names of the database table. This can happen in several situations.

1. Mis-spell the column name.

2. The column name is missing in the table.

3. Use an invalid column name that does not follow the valid column name criteria.

The valid column name criteria:

The column name must begin with a letter.
The column name must be less than or equal to thirty characters
The column name must only consists of alphanumeric characters and special characters ($, _, #). It must be enclosed in double quotation marks if it contains any other characters.
The column name cannot be a reserved word.

For example, the following aql will throw such an exception.

CREATE TABLE FRUIT (
01NAME VARCHAR2(30),
PRICE NUMBER,
Description VARCHAR2(50)
);

4. If you created the table with double quotation marks around the column names, the column names are case sensitive, referring the column names without double quotation marks will cause the exception. For example, if you created the FRUIT table using the following sql,

CREATE TABLE FRUIT (
"Name" VARCHAR2(30),
"Price" NUMBER,
"Description" VARCHAR2(50)
);

when you query the table with the following sql, the exception occurs.

SELECT Name, Price FROM FRUIT;

To avoid the exception, you need to put double quotation marks around the column names.

SELECT "Name", "Price" FROM FRUIT.

5. Extra comma at the end of the last column. For example the following will give such an exception.

CREATE TABLE FRUIT (
NAME VARCHAR2(30),
PRICE NUMBER,
Description VARCHAR2(50),
);

> Next

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

Monday, January 5, 2015

Error: Unable to access jarfile - Solved

The error happens in several situations.

1. The ".jar" part is missing from the jar file name. For example, your try to run your MyCode.jar with command java -jar MyCode instead of java -jar MyCode.jar

2. The path to the the jar file is incorrect. In this case the program is not able to find the jar file.

3. The jar file is missing. If for some reason the jar file is misplaced or deleted, then the error will occur.

4. You don't have permission to access the jar file. Check the permission of the folders on the path to make sure that you can access the jar file.

5. For linux operating system, if you are running the jar file from the same folder where the jar file exists, add "./" in front of the file name (e.g java -jar ./MyCode.jar) can help solve the problem.

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

Friday, January 2, 2015

Java: Set char at a particular position in a String

There a several ways to set a char at a particular position in a String.

String str = "IloveJavaProgramming"

1. Use the method setCharAt in StringBuilder.

StringBuilder sb = new StringBuilder(str);
sb.setCharAt(2, 'i');
sb.setCharAt(3, 'k');

System.out.println(sb.toString());

The output will be: IlikeJavaProgramming

2. Connect sub Strings.

str.substring(0,2) + "ik" + str.substirng(4);

3. Use char array.

char[] sa = str.toCharArray();
sa[2] = 'i';
sa[3] = 'k';

str = new String(sa);

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