IO Stream

Java performs I/O through Streams. A Stream is linked to a physical layer by java I/O system to make input and output operation in java. In general, a stream means continuous flow of data. Streams are clean way to deal with input/output without having every part of your code understand the physical.
Java encapsulates Stream under java.io package. Java defines two types of streams. They are,

1. Byte Stream : It provides a convenient means for handling input and output of byte.
2. Character Stream : It provides a convenient means for handling input and output of characters. Character stream uses Unicode and therefore can be internationalized.

---

Byte Stream Classes

Byte stream is defined by using two abstract class at the top of hierarchy, they are InputStream and OutputStream.

bytestream = inputstream/outputstream


These two abstract classes have several concrete classes that handle various devices such as disk files, network connection etc.

---

Some important Byte stream classes.

Stream class	Description
BufferedInputStream	Used for Buffered Input Stream.
BufferedOutputStream	Used for Buffered Output Stream.
DataInputStream	Contains method for reading java standard datatype
DataOutputStream	An output stream that contain method for writing java standard data type
FileInputStream	Input stream that reads from a file
FileOutputStream	Output stream that write to a file.
InputStream	Abstract class that describe stream input.
OutputStream	Abstract class that describe stream output.
PrintStream	Output Stream that contain print() and println() method

These classes define several key methods. Two most important are

1. read() : reads byte of data.
2. write() : Writes byte of data.

---

Character Stream Classes

Character stream is also defined by using two abstract class at the top of hierarchy, they are Reader and Writer.

characterstream = reader/writer



These two abstract classes have several concrete classes that handle unicode character.

---

Some important Charcter stream classes.

Stream class	Description
BufferedReader	Handles buffered input stream.
BufferedWriter	Handles buffered output stream.
FileReader	Input stream that reads from file.
FileWriter	Output stream that writes to file.
InputStreamReader	Input stream that translate byte to character
OutputStreamReader	Output stream that translate character to byte.
PrintWriter	Output Stream that contain print() and println() method.
Reader	Abstract class that define character stream input
Writer	Abstract class that define character stream output

---

Reading Console Input

We use the object of BufferedReader class to take inputs from the keyboard.

Reading Characters

read() method is used with BufferedReader object to read characters. As this function returns integer type value has we need to use typecasting to convert it into char type.

int read() throws IOException

Below is a simple example explaining character input.

class CharRead
{
 public static void main( String args[])
 {
  BufferedReader br = new Bufferedreader(new InputstreamReader(System.in));
  char c = (char)br.read();       //Reading character  
 }
}

Reading Strings

To read string we have to use readLine() function with BufferedReader class's object.

String readLine() throws IOException

---

Program to take String input from Keyboard in Java

import java.io.*;
class MyInput
{
 public static void main(String[] args)
 {
  String text;
  InputStreamReader isr = new InputStreamReader(System.in);
  BufferedReader br = new BufferedReader(isr);
  text = br.readLine();          //Reading String  
  System.out.println(text);
 }
}

---

Program to read from a file using BufferedReader class

import java. Io *;
class ReadTest
{
 public static void main(String[] args)
 {
  try 
  {
   File fl = new File("d:/myfile.txt");
   BufferedReader br = new BufferedReader(new FileReader(fl)) ;
   String str;
   while ((str=br.readLine())!=null)
   {
    System.out.println(str);
   }
   br.close();
   fl.close();
  }
  catch (IOException  e)
  { e.printStackTrace(); }
 }
}

---

Program to write to a File using FileWriter class

import java. Io *;
class WriteTest
{
 public static void main(String[] args)
 {
  try 
  {
   File fl = new File("d:/myfile.txt");
   String str="Write this string to my file";
   FileWriter fw = new FileWriter(fl) ;
   fw.write(str);
   fw.close();
   fl.close();
  }
  catch (IOException  e)
  { e.printStackTrace(); }
 }
}

Common Ports: FTP, HTTP, TELNET, SMTP, BIOS, IMAP, POP3, SSH, DHCP

20 FTP data (File Transfer Protocol)
21 FTP (File Transfer Protocol)
22 SSH (Secure Shell)
23 Telnet
25 SMTP (Send Mail Transfer Protocol)
43 whois
53 DNS (Domain Name Service)
68 DHCP (Dynamic Host Control Protocol)
79 Finger
80 HTTP (HyperText Transfer Protocol)
110 POP3 (Post Office Protocol, version 3)
115 SFTP (Secure File Transfer Protocol)
119 NNTP (Network New Transfer Protocol)
123 NTP (Network Time Protocol)
137 NetBIOS-ns
138 NetBIOS-dgm
139 NetBIOS
143 IMAP (Internet Message Access Protocol)
161 SNMP (Simple Network Management Protocol)
194 IRC (Internet Relay Chat)
220 IMAP3 (Internet Message Access Protocol 3)
389 LDAP (Lightweight Directory Access Protocol)
443 SSL (Secure Socket Layer)
445 SMB (NetBIOS over TCP)
666 Doom
993 SIMAP (Secure Internet Message Access Protocol)
995 SPOP (Secure Post Office Protocol) 

Oracle useful commands & DDL, DML, DCL, TCL Operations


// To find out total number of rows from table
select count(*) from tablename

// To find out all db users table count
select count(*) from dba_tables

// To find out table names of all db users
SELECT TABLE_NAME FROM DBA_TABLES;

// To find out current db user table count
SELECT COUNT(*) FROM USER_TABLES;

CREATE TABLE - BY COPYING ALL COLUMNS FROM ANOTHER TABLE

// The syntax for the SQL CREATE TABLE AS statement copying all of the columns is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

  CREATE TABLE - BY COPYING SELECTED COLUMNS FROM ANOTHER TABLE

// The syntax for the CREATE TABLE AS statement copying the selected columns is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

      CREATE TABLE - BY COPYING SELECTED COLUMNS FROM MULTIPLE TABLES

// The syntax for the CREATE TABLE AS statement copying columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);


// How can I create a SQL table from another table without copying any values from the old table?

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);


SELECT dbms_metadata.get_ddl( 'TABLE', 'tablename' ) FROM DUAL;

// To get index details of table
select DBMS_METADATA.GET_DDL('INDEX','') from DUAL;

// Create index
create index INDEX_NAME on tablename ( columnname ); 

=========================================================================

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Let me know for queries.

Apache POI - Password Protected Excel


Apache POI, a project run by the Apache Software Foundation, and previously a sub-project of the Jakarta Project, provides pure Java libraries for reading and writing files in Microsoft Office formats, such as Word, PowerPoint and Excel.

The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.

In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet.

Tools & dependencies:

1. Java JDK 1.5 or above
2. Apache POI library v3.8 or above (download)
3. Eclipse 3.2 above (optional)

Create a password protected excel file or use an existing template and make it password protected. This will give the users a "read only" access though. Here's an example where I have an excel file that has a password "sachin"


import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

public class ProtectedExcelFile { 

    public static void main(final String... args) throws Exception {     

        String fname = "C:/Users/username/excel.xls"; // Add your excel sheet path

        FileInputStream fileInput = null;       
        BufferedInputStream bufferInput = null;      
        POIFSFileSystem poiFileSystem = null;    
        FileOutputStream fileOut = null;

        try {           

            fileInput = new FileInputStream(fname);         
            bufferInput = new BufferedInputStream(fileInput);            
            poiFileSystem = new POIFSFileSystem(bufferInput);            

            Biff8EncryptionKey.setCurrentUserPassword("sachin");      // Use 'sachin' as  a password
            HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem, true);            
            HSSFSheet sheet = workbook.getSheetAt(0);           

            HSSFRow row = sheet.createRow(0);
            Cell cell = row.createCell(0);

            cell.setCellValue("THIS WORKS!"); 

            fileOut = new FileOutputStream(fname);
            workbook.writeProtectWorkbook(Biff8EncryptionKey.getCurrentUserPassword(), "");
            workbook.write(fileOut);

        } catch (Exception ex) {

            System.out.println(ex.getMessage());      

        } finally {         

              try {            

                  bufferInput.close();     

              } catch (IOException ex) {

                  System.out.println(ex.getMessage());     

              }    

              try {            

                  fileOut.close();     

              } catch (IOException ex) {

                  System.out.println(ex.getMessage());     

              } 
        }       

    }

}

Let me know your queries.