Java Solutions: December 2017

Thursday, December 28, 2017

SQL: Return the first / top n rows of each group

For example, you have a product table containing the following columns.

Name Type
---------- ------------
Apple Fruit
Banana Fruit
Walnut Nut
Papaya Fruit
Cashew Nut
Pine Nut Nut

You want your SQL to return the top 2 rows from each Type, a result like this.

Type Name
--------- ------------------------------
Fruit Apple
Fruit Banana
Nut Cashew
Nut Pine Nut

The SQLs below shall help.

SQL> select Type, Name
from (
select Type, Name,
ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type, Name) as type_rank
from product

) AS product

where type_rank <= 2;

OR

SQL> WITH prequery AS (
select Type,
Name,
ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type, Name) as type_rank
from product
)
SELECT Type, Name,
FROM prequery
WHERE type_rank <= 2;

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

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: Concatenate values in each result group as a list; LISTAGG and its alternative

For example, you have a product table containing the following columns.

Name Type
---------- ------------
Apple Fruit
Banana Fruit
Walnut Nut
Papaya Fruit
Cashew Nut

You want your SQL to return a result like this.

Type Names
--------- ------------------------------
Fruit Apple, Banana, Papaya
Nut Cashew, Walnut

1. Find the version of your Oracle database

select * from v$version;

2. If your Oracle version is 11.2 or later, the Oracle LISTAGG function can help you to achieve your goal.

SQL> select Type, LISTAGG(coalesce( Name, ' '), ',') WITHIN GROUP (ORDER BY Name) as Names
from product
group by Type;

The names can be separated by any character by replacing the comma in LISTAGG(Name, ',') with the character.

3. If you use an Oracle database earlier than release 11.2 and LISTAGG is not available, the following shall work for you.

SQL> select Type, rtrim(xmlagg(xmlelement(e, coalesce( Name, ' '), ', ') order by Name).extract('//text()').getclobval(), ', ') AS Names
from product
group by Type;

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

Wednesday, December 27, 2017

SQL: Add single quotes, double quotes or string around a query result value

Lets say you have a Fruit table.

name price desc
---------- --------- ----------------
Apple 1.94 Very popular
Mango 2.05 Popular in Summer

Now, you want to list the fruits and their descriptions and the descriptions be printed with single or double quotes around them. You can either use the string quoting literals introduced in 10g or by concatenating the quotes.

The string quoting literal is to put your string in q'[your string]'. The square brackets can be replace by { }, < >, ( ), or ! !.

1. Single quote

SQL> select name, q'[']' || desc || q'[']' from fruit;
or
SQL> select name, q'!'!' || desc || q'<'>' from fruit;
or
SQL> select name, '''' || desc || '''' from fruit;

The '''' is four single quotes

2. Double quote

SQL> select name, q'["]' || desc || q'["]' from fruit;
or
SQL> select name, q'(")' || desc || q'{"}';
or
SQL> select '"' || desc || '"' from fruit;

The '"' is a double quote surrounded by single quotes.

3. String

Now, you want to add a string "Popularity: " in front of the descriptions.

SQL> select name, q'{Popularity: }' || desc from fruit;
or
SQL> select name, 'Popularity: ' || desc from fruit;

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

Friday, December 22, 2017

Use MethodHandle to dynamically invoke method

MethodHandle is introduced in Java 7. For dynamic method invoking, it is faster than reflection.

In the code below, class MethodHandleTest2 has three JTextField fields and an objModified method. When any of the JTextField fields is modified, the objModified method will be called to handle the modification accordingly.

import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.lang.invoke.MethodHandle;
import java.lang.invoke.MethodHandles;
import java.lang.invoke.MethodType;
import java.lang.reflect.InvocationTargetException;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JTextField;

public class MethodHandleTest2 extends JFrame{
public MethodHandleTest2() {
JTextField[] data = {new JTextField("Peach", 20),
new JTextField("Papaya", 20),
new JTextField("Mango", 20)};
JPanel panel = new JPanel(new GridLayout(3, 1));

for (int i=0; i<data.length; i++){
try {
data[i].addActionListener(new ObjectActionListener("objModified",
new Object[]{this, data[i], "Field "+(i+1)+" Modified"}
));
panel.add(data[i]);
} catch (NoSuchMethodException e){
System.out.println(e.getMessage());
}
}
this.add(panel);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setSize(700, 300);
this.pack();
}

public static void main(String[] args){
javax.swing.SwingUtilities.invokeLater(new Runnable() {
@Override
public void run() {
(new MethodHandleTest2()).setVisible(true);
}
});
}

public class ObjectActionListener implements ActionListener {
private Object[] parameters = null;
private String methodName = null;

public ObjectActionListener(String methodName, Object[] parameters)
throws NoSuchMethodException, SecurityException {
this.methodName = methodName;
this.parameters = parameters;
}

private MethodHandle getMethod() throws NoSuchMethodException,
IllegalAccessException {

//For the objModified method, its return type is void, and it takes
// a JTextField argument and a String argument.
MethodType mt = MethodType.methodType(void.class, JTextField.class, String.class);

//Get the MethodHandle: argument1 is the class where the method is located;
// argument2 is the method name;
//argument3 is the method type of the method signature
MethodHandle mh = MethodHandles.lookup()
.findVirtual(MethodHandleTest2.class,
methodName,
mt);
return mh;
}

@Override
public void actionPerformed(ActionEvent event) {
try {
MethodHandle method = getMethod();
//The invoke method is supposed to be able to take an array argument.
//Somehow, it does not work for me in my test code.
//So, I have to put each component of the array in the arguments.
method.invoke(parameters[0], parameters[1], parameters[2]);
} catch (IllegalAccessException e) {
System.out.println(e.getMessage());
} catch (InvocationTargetException e) {
System.out.println(e.getMessage());
} catch (Throwable e) {
System.out.println(e.getMessage());
}
}
}

public void objModified(JTextField obj, String message){
String text = obj.getText();
System.out.println(message+": "+text);
}
}

The code using reflection to do the same job can be found here

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

Thursday, December 21, 2017

Java 8: Calculate average, sum, maximum, minimum and count of elements of a list

The easiest way probably is to get the summary statistics of the list through stream first. Get all the values, then, from the summary statistics. Below is an example for a integer list. The same logic applies to all other type of numbers.

List<Integer> intList = Arrays.asList(123, 886, 43, 25, 33, 89);

IntSummaryStatistics intStat = intList.stream()
.mapToInt ( i -> i)
.summaryStatistics();

double average = intStat.getAverage();
long sum = intStat.getSum();
long count = intStat.getCount();
int max = intStat.getMax();
int min = intStat.getMin();

Of course, if you just want to get the sum or find the maximum, you can do it like below.

int sum = intList.stream()
.mapToInt(i -> i)
.sum();

int max = intList.stream()
.mapToInt(i -> i)
.max()
.orElse(0);

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

Friday, December 15, 2017

java 8: Convert a long array to String array

//Convert a primitive long[] to String[]
long[] pla = {1155L, 22334L, 559876L};

String[] spla = Arrays.stream(pla)
.mapToObj(String :: valueOf)
.toArray(String[] :: new);

//Convert a boxed Long[] to String[]
Long[] bla = LongStream.of(pla)
.boxed().toArray(Long[] :: new);

String[] sbla = Arrays.stream(bla)
.mapToLong(Long::longValue)
.mapToObj(String::valueOf)
.toArray(String[] :: new);

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

java 8: Convert a Long number list to a boxed and unboxed primitive array / convert a String list to String array by Stream

To convert a Long list to array, while Long[] ar = list.stream().toArray(size -> new Long[size]) is perfectly fine, long[] par = list.stream().toArray(size -> new long[size]) does not work. The argument of the toArray method, the IntFunction, takes an parameter type that is bound to Object, and long is not an Object while Long is.

The workaround is to map each element in the Stream to a Long so that when the toArray method without argument is called, it returns an long[] instead of an Object[].

import java.util.List;
import java.util.Arrays;

public class StreamListToArray {
public static void main(String[] args){
List<Long> list = Arrays.asList(new Long(11238), new Long(55444), new Long(1099886));

Long[] ar = list.stream().toArray(size -> new Long[size]);
for(Long lg : ar){
System.out.println(lg);
}
System.out.println();

//Method 1
long[] ars = list.stream().mapToLong(Long :: new).toArray();
for(Long lg : ars){
System.out.println(lg);
}
System.out.println();

//Method 2
long[] par = list.stream().mapToLong(Long :: longValue).toArray();
for(long lg : par){
System.out.println(lg);
}

System.out.println();
List<String> slist = Arrays.asList("String line 1", "String line 2", "String line 3");

String[] sar = slist.stream().toArray(size -> new String[size]);
for(String s : sar){
System.out.println(s);
}
}
}

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

Thursday, December 14, 2017

Set up a Timer to repeatedly execute a piece of code or a task or to repeatedly update the state of your objects

Lets say, you have an integer field and you want to increment it by 5 every minute.

public class TimerTest {
private int theValue = 0;
private javax.swing.Timer timer1 = null;

public TimerTest() {
//Set up the timer to perform the action repeatedly
//after a delay of 1 minute which is 60000 milliseconds.
timer1 = new javax.swing.Timer(60000, new TimerAction());
timer1.start();

}

private class TimerAction implements ActionListener {
@Override
public void actionPerformed(ActionEvent e) {
theValue += 5;
System.out.println("The value currently is "+theValue);
}
}
}

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

Monday, December 4, 2017

Java 8: Three ways to calculate days between two dates

import java.time.Duration;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;

public class DateTest101 {

public static void main(String[] args){
String dt2 = "2017335";
String dt1 = "2016335";

DateTimeFormatter pattern = DateTimeFormatter.ofPattern("yyyyDDD");
LocalDate dat2 = LocalDate.parse(dt2, pattern);
LocalDate dat1 = LocalDate.parse(dt1, pattern);
System.out.println("dat2: "+dat2.toString()+", dat1: "+dat1);

//Method 1
long daysBetween1 = dat1.until(dat2, ChronoUnit.DAYS);
System.out.println("daysBetween1: "+ daysBetween1);

//Method 2
long daysBetween2 = ChronoUnit.DAYS.between(dat1, dat2);
System.out.println("daysBetween2: "+ daysBetween2);

//Method 3
long daysBetween3 = Duration.between(dat1.atStartOfDay(), dat2.atStartOfDay()).toDays();
System.out.println("daysBetween3: "+ daysBetween3);
}
}

Reference:

1. Calculate days between two dates in Java 8

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