Connecting SQL database with DriverManager

Preparation

Download sqljdbc_4.1. Then unpack it. Put 'sqljdbc41.jar' in the project as external jar. To use Trusted connection, put sqljdbc_auth.dll from \sqljdbc_4.1\enu\auth\x64 to C:\Windows\System32

3 points to remember
  1. Create connection
      Connection   con = DriverManager.getConnection(connectionUrl);

  2. Excute query
      stmt = con.createStatement();
      rs = stmt.executeQuery(SQL);

  3. Get the output
       while (rs.next()) 
   {

    System.out.println(rs.getString(1) + " " + rs.getString(2));
   }

Code

import java.sql.*;


public class DataPlay {

    public static void main(String[] args)
    {

        String connectionUrl = "jdbc:sqlserver://TJZR9XNH9H-1\\MSSQLSERVER2012:1433;" +
                "databaseName=KB;integratedSecurity=true;";//user=GreenItUser;password=GreenItUser123";
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // Establish the connection.           // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");            con = DriverManager.getConnection(connectionUrl);

            // Create and execute an SQL statement that returns some data.            String SQL = "SELECT TOP 10 * FROM dbo.menuitem";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.            while (rs.next()) {
                System.out.println(rs.getString(1) + " " + rs.getString(2));
            }
        }

        // Handle any errors that may have occurred.        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (rs != null) try { rs.close(); } catch(Exception e) {}
            if (stmt != null) try { stmt.close(); } catch(Exception e) {}
            if (con != null) try { con.close(); } catch(Exception e) {}
        }
    }
}

Date calculation

package com.parvez;

import java.util.Calendar;
import java.util.Date;

public class DatingWithDates {

public static void main(String[] args)
{
;
Date d1=new Date();
System.out.println("Today"+" "+d1.toString());
Calendar c=Calendar.getInstance();
c.setTime(d1);

displayDate("Date after a month",getDateNextMonth(d1));
displayDate("Date before a month",getDateLastMonth(d1));
displayDate("First date of last month",getDate_FirstDateOfLastMonth(d1));
displayDate("First date of next month",getDate_FirstDateOfNextMonth(d1));
displayDate("Last date of last month",getDate_LastDateOfLastMonth(d1));
displayDate("Last date of next month",getDate_LastDateOfNextMonth(d1));



}
public static void displayDate(String msg,Date d)
{
System.out.println(msg+" "+d.toString());
}
public static Date getDateNextMonth(Date di)
{
Calendar c=Calendar.getInstance();
c.setTime(di);
c.add(Calendar.MONTH,1);
Date dout=c.getTime();
return dout;
}

public static Date getDateLastMonth(Date di)
{
Calendar c=Calendar.getInstance();
c.setTime(di);
c.add(Calendar.MONTH,-1);
Date dout=c.getTime();
return dout;
}

public static Date getDate_FirstDateOfLastMonth(Date di)
{
Calendar c=Calendar.getInstance();
c.setTime(di);
c.add(Calendar.MONTH,-1);
c.set(Calendar.DAY_OF_MONTH, 1);
Date dout=c.getTime();
return dout;
}
public static Date getDate_FirstDateOfNextMonth(Date di)
{
Calendar c=Calendar.getInstance();
c.setTime(di);
c.add(Calendar.MONTH,1);
c.set(Calendar.DAY_OF_MONTH, 1);
Date dout=c.getTime();
return dout;
}

public static Date getDate_LastDateOfLastMonth(Date di)
{
Calendar c=Calendar.getInstance();
c.setTime(di);
c.set(Calendar.DAY_OF_MONTH, 1);
c.add(Calendar.DATE,-1);
Date dout=c.getTime();
return dout;
}
public static Date getDate_LastDateOfNextMonth(Date di)
{
Calendar c=Calendar.getInstance();
c.setTime(di);
c.add(Calendar.MONTH,2);
c.set(Calendar.DAY_OF_MONTH, 1);
c.add(Calendar.DATE,-1);
Date dout=c.getTime();
return dout;
}
}

Passing Arraylist of sub class type to a function

Suppose we have Animal class and Dog class is subclass of Animal class. To pass the arraylist of Dog which is subclass of Animal to a function Display (which takes ArrayList of Animal), we have to use Type of ArrayList. ArrayList is checked in compile time. So, when we use type, then its tied to a specific type, You cant add any new element in the function.

 private <T extends Animal> void Display(List<T> animals)

Animal.java

public class Animal {

    public void eat()
    {
        System.out.println("Animal eating");
    }

    public static void main(String[] args)
    {

        List<Dog> dogs=new ArrayList<Dog>();
        dogs.add(new Dog());
        dogs.add(new Dog());
        new Animal().Display(dogs);


    }


    private <T extends Animal> void Display(List<T> animals) {
        for(T a:animals)
        {

            a.eat();
        }
    }
}

Dog.java

public class Dog extends Animal{

    public void eat()
    {
        System.out.println("Dog eating");
    }
}

Comparator

Comparator is better than Comparable. To implement comparator, we have to
1. Create a comparator class which will implement "Comparator" interface.
2. Override "compare" method.
3. You can also override "reversed" method to get reverse order.
4. Call Collections.sort and pass arraylist object and comparator class object.

Song.java

package com.parvez.albumCollection;

public class Song {
    public Song(String t,String a,int r)
    {
        setArtist(a);
        setRanking(r);
        setTitle(t);
    }
    private String title;
    private String artist;
    private int ranking;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getArtist() {
        return artist;
    }

    public void setArtist(String artist) {
        this.artist = artist;
    }

    public int getRanking() {
        return ranking;
    }

    public void setRanking(int ranking) {
        this.ranking = ranking;
    }


    public String toString()
    {
        return "Title: "+ this.getTitle() + " Artist: " +getArtist() +" Ranking: " + getRanking();
    }
}

RankingComparator.java

package com.parvez.albumCollection;

import java.util.Comparator;


public class RankingComparator implements Comparator<Song>
{
    @Override    
public int compare(Song song1, Song song2) {
        return song1.getRanking()-song2.getRanking();
    }

    @Override    
public Comparator<Song> reversed() {
        return new RankingComparator(){
            @Override            
public int compare(Song song1, Song song2) {
                return song2.getRanking()-song1.getRanking();
            }
        };
    }
}



AlbumSongs.java

package com.parvez.albumCollection;

import java.io.*;
import java.util.ArrayList;
import java.util.Collections;


public class AlbumSongs {
    ArrayList<Song> songs=new ArrayList<Song>();
    public static void main(String[] args)
    {
        AlbumSongs album=new AlbumSongs();
        album.getSongs();
        album.displaySongs();
        System.out.println();
        Collections.sort(album.songs,new RankingComparator());
        album.displaySongs();
        System.out.println();
        Collections.sort(album.songs,new RankingComparator().reversed());
        album.displaySongs();
        System.out.println();
    }

    private void displaySongs() {
        for (Song song: songs)
        {
            System.out.println(song.toString());
        }
    }

    private void getSongs() {
        File file;
        BufferedReader bufferedReader;

        try        {
            file=new File("input.txt");
            bufferedReader=new BufferedReader(new FileReader(file));

            String line;
            while((line=bufferedReader.readLine())!=null)
            {
                String[] lineString=line.split(",");
                Song aSong=new Song(lineString[1],lineString[0],Integer.parseInt(lineString[2]));
                songs.add(aSong);
            }
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }


}

Comparable

The compareTo method of the class is defining the "natural" ordering of that object. That method is contractually obligated (though not demanded) to be in line with other methods on that object, such as a 0 should always be returned for objects when the .equals() comparisons return true. compareTo method must be implemented in the class for which object is sorting. Disadvantage is that you have imlement Comparable interface and override compareTo method. If it is not possible, then we have to extend the class.

Song.java

package com.parvez.albumCollection;

public class Song implements Comparable<Song>{
    public Song(String t,String a,int r)
    {
        setArtist(a);
        setRanking(r);
        setTitle(t);
    }
    private String title;
    private String artist;
    private int ranking;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getArtist() {
        return artist;
    }

    public void setArtist(String artist) {
        this.artist = artist;
    }

    public int getRanking() {
        return ranking;
    }

    public void setRanking(int ranking) {
        this.ranking = ranking;
    }

    @Override    public int compareTo(Song o) {
        return this.getTitle().compareTo(o.getArtist());
    }

    public String toString()
    {
        return "Title: "+ this.getTitle() + " Artist: " +getArtist() +" Ranking: " + getRanking();
    }
}

AlbumSongs.java

package com.parvez.albumCollection;

import java.io.*;
import java.util.ArrayList;
import java.util.Collections;


public class AlbumSongs {
    ArrayList<Song> songs=new ArrayList<Song>();
    public static void main(String[] args)
    {
        AlbumSongs album=new AlbumSongs();
        album.getSongs();
        album.displaySongs();
        System.out.println();
        Collections.sort(album.songs);

    }

    private void displaySongs() {
        for (Song song: songs)
        {
            System.out.println(song.toString());
        }
    }

    private void getSongs() {
        File file;
        BufferedReader bufferedReader;

        try        {
            file=new File("input.txt");
            bufferedReader=new BufferedReader(new FileReader(file));

            String line;
            while((line=bufferedReader.readLine())!=null)
            {
                String[] lineString=line.split(",");
                Song aSong=new Song(lineString[1],lineString[0],Integer.parseInt(lineString[2]));
                songs.add(aSong);
            }
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }


}