How to pass array from java to stored procedure which accepts argument of type varray ?

Oracle
---------
I think Mysql wont support varray as per my knowledge.

1)Create varray type
CREATE OR REPLACE TYPE STUDENTARRAY as varray(20) of varchar2(50) 

2)Create table with new type studentarray

create table studentsvarraytable(
     starray studentarray

);

3)Create procedure which accepts argument of type studentarray

CREATE OR REPLACE PROCEDURE VARRAYPROC ( svarray in studentarray )

is

   begin

        insert into studentsvarraytable values ( svarray );

   end;

4)Pass array from java code in to pl/sql procedure which inserts the passed
    students in to table

Code:

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbcsample;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;


/**
 *
 * @author Rishitha
 */
public class ArrayPassingClass {


 

    /**
     * @param args the command line arguments
     */
    public static Connection getConnection() {

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e) {

            System.out.println("Please add ojdbc6.jar in your classpath if you are using oracle,"
                    + "otherwise set the respective database driver jar file.In netbeans right click on"
                    + "libraries,click on add jar/folder and provide your jar file");

        }
        Connection connection = null;

        try {
            //XE -- Here Give your database name
            //hr -- Give Your database username
            //hr -- Give your database password
            //You have to add ojdbc.jar in your classpath

            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:XE", "hr",
                    "hr");
            System.out.println("Hurrah got connection " + connection);

        } catch (SQLException e) {

            System.out.println("Please check if database name username password are correct ?");

        }
        return connection;

    }

   

    public static void main(String args[]) throws Exception {
       Connection con = getConnection();
       ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STUDENTARRAY",con);
       String[] students = new String[] { "Anilkumar", "Sunilkumar" };
        ARRAY array = new ARRAY ( desc, con, students );
       
      String stmtString =
        "begin VARRAYPROC(?); end;";
      CallableStatement cstmt = con.prepareCall( stmtString );
      cstmt.setArray( 1, array );
      cstmt.execute();
    }

}

5)Select * from studentsvarraytable;

How to display all the tables in Database using JDBC DatabaseMetaData api ?

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbcsample;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 *
 * @author Rishitha
 */
public class MetaDataExample {


 

    /**
     * @param args the command line arguments
     */
    public static Connection getConnection() {

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

        } catch (ClassNotFoundException e) {

            System.out.println("Please add ojdbc6.jar in your classpath if you are using oracle,"
                    + "otherwise set the respective database driver jar file.In netbeans right click on"
                    + "libraries,click on add jar/folder and provide your jar file");

        }
        Connection connection = null;

        try {
            //XE -- Here Give your database name
            //hr -- Give Your database username
            //hr -- Give your database password
            //You have to add ojdbc.jar in your classpath

            connection = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:XE", "hr",
                    "hr");
            System.out.println("Hurrah got connection " + connection);

        } catch (SQLException e) {

            System.out.println("Please check if database name username password are correct ?");

        }
        return connection;

    }

    public void getAllTables() throws Exception {

        Connection con = getConnection();
        DatabaseMetaData dbmd = con.getMetaData();
        if (dbmd == null) {
            throw new Exception("metadata not supported by vendor");
        }

        ResultSet tables = dbmd.getTables(null, null, null, null);
        while (tables.next()) {
            System.out.println("TableName: " + tables.getString(3) + " Schema: " + tables.getString(2));

        }
    }

    public static void main(String args[]) throws Exception {
        MetaDataExample db = new MetaDataExample();
        db.getAllTables();
    }

}

Small example to make you easily understand about JDBC driver concept

Small example to make you easily understand about jdbc driver concept....

Assume there are 3 persons

A who knows Telugu
B who knows Hindi
C who knows English

Suppose if you want to speak to these 3 guys you should know
3 languages..... right ?

But what sun said is dont learn 3 languages learn jdbc
language

You dont talk in telugu or hindi or english

You talk JDBC language...

and if u want to communicate with A(Telugu)
add telugudriver who translates jdbc language to telugu
when you want to communicate with telugu speaking guy

and if u want to communicate with B(Hindi)
add hindidriver who translates jdbc language to hindi
when you want to communicate with telugu speaking guy

and if u want to communicate with C(English)
add englishdriver who translates jdbc language to english
when you want to communicate with telugu speaking guy

Now u can imagine what will happen if you add
telugudriver to communicate with person C.
It fails right because

telugudriver cant' convert jdbc language to English language
it can only conver jdbc to telugu calls.....

Now u can easily understand that if you want to communicate
with mysql db from jdbc u want mysql related jar file not ojdbc jar,

If you want to communicate with oracle database you need ojdbc jar
not mysql jar which has the capability to convert jdbc calls to mysql database

This concept applies to JNDI also...., in jdbc you will communicate
with databases in JNDI you communicate with directory services.. kind of things...

Hope u understood...........

What is sequence in Oracle ?

What is purpose of sequence ?

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

In oracle you can use sequence to generate primary key 

Syntax to create sequence

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

CREATE SEQUENCE STUDENTS_ID_SEQ
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 10000
    NOCACHE
    NOCYCLE;

Example:

create table students(
sid number primary key,
fname varchar2(100),
lname varchar2(100));

Insert statements 

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

Insert into students values(STUDENTS_ID_SEQ.nextval,'Anil','kumar');
Insert into students values(STUDENTS_ID_SEQ.nextval,'Sunil','kumar');

Syntax to get the sequence number is sequencename.nextval. 

You can see that in the above insert statements

I call nextval to get the primary key which is automatically

generated by the sequence we created

When I created STUDENTS_ID_SEQ I said that sequence should start

with 1 and increment by 1 

So when u call the nextval for first time it generates 1

If you call again it will generate 2 

You can say increment as 2 

Then the generated sequences will be 1,3,5... 

If you don't know this.. you have to get the max student id and increment

it by one.... right ? 

To just display the next sequence number you can execute

following query 

select STUDENTS_ID_SEQ.nextval from dual; 

Small Task....... :)

Try to generate nextval more than 10000 times

and see what happens........ ? 

How to print Time in various Countries ?

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package org.jl.tz;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;

/**
 *
 * @author Rishitha
 */
public class TimeZoneExample {
   
   
    public static void main(String args[]){
       
       
       
        Calendar indCal = Calendar.getInstance();
       
       
        Calendar usCal = Calendar.getInstance();
       
        TimeZone tz = TimeZone.getTimeZone("America/Los_Angeles");
        usCal.setTimeZone(tz);
       
        SimpleDateFormat sf = new SimpleDateFormat();
       
        System.out.println("Indian Time "+sf.format(indCal.getTime()));
       
        SimpleDateFormat usSf = new SimpleDateFormat();
        usSf.setTimeZone(tz);
        System.out.println("Los_Angeles Time "+usSf.format(usCal.getTime()));
       
       
       
       
    }
   
   
   
}

Jdbc Programme to find the columns which are having values that are length and starts with A and containing number after first letter ?

The following example was tried on Oracle database..I think mysql and other databases
may not support all_tab_columns.... Please check....

package javalearners;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TableMatcher {
  public void displayTables() {

    System.out.println("-------- Oracle JDBC Connection Testing ------");

    try {

        Class.forName("oracle.jdbc.driver.OracleDriver");

    } catch (ClassNotFoundException e) {

        System.out.println("Where is your Oracle JDBC Driver?");
        e.printStackTrace();
        return;

    }

    System.out.println("Oracle JDBC Driver Registered!");

    Connection connection = null;

    try {

        connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE", "system",
                "oracle");

    } catch (SQLException e) {

        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
        return;

    }

    if (connection != null) {
        System.out.println("You made it, take control your database now!");
    } else {
        System.out.println("Failed to make connection!");
    }
   
    String allColumns = "select * from all_tab_columns ";
    Statement colSt=null,findSt = null;
    ResultSet colRs=null,findRs = null;
   
    try{
   
       colSt = connection.createStatement();
       colRs = colSt.executeQuery(allColumns);
      
       findSt = connection.createStatement();

       while(colRs.next()){
   
           String columnName = "";
           if(colRs.getString("column_name") != null ){
               columnName = colRs.getString("column_name");
           }
           String dynamicSt = "SELECT count(*) as cnt from "+colRs.getString("owner")+"."+
           colRs.getString("table_name")+" WHERE "+
           colRs.getString("column_name")+" like 'A%' and " +
                   " upper(substr("+columnName+",2))= lower(substr("+columnName+",2)) and length(trim("+colRs.getString("column_name")+")) =7";
     
          
           // System.out.println(colRs.getString("owner"));
           // System.out.println(colRs.getString("table_name"));
           //System.out.println(colRs.getString("column_name"));
           System.out.println(dynamicSt);
           try{
              int ct = 0;
              findRs = findSt.executeQuery(dynamicSt);
              if(findRs.next()){
                  ct = findRs.getInt("cnt");
              }
              //System.out.println("ct is "+ct);
              if(ct > 0 ){
                 System.out.println("Schema: "+colRs.getString("owner")+" Table:***"+colRs.getString("table_name"));
                 System.out.print("ColName "+colRs.getString("column_name"));
              }
     
           }catch(SQLException e ){
              
              
           }
       }
    }catch(SQLException e ){
          System.out.println(e.getMessage());      
    }finally{
        try{
          if(colSt != null )
             colSt.close();
          if(findSt != null)
              findSt.close();
          if(colRs != null)
              colRs.close();
          if(findRs!=null)
              findRs.close();
          if(connection != null )
              connection.close();
        }catch(Exception e ){
           
        }
       
    }
      
      
    }// End of method
   
   
   
   
  }   
   
 
Assume you have 3 tables with data as given below

StudentTable

sid  sname         saddress
1     Anil             Kumar
2     A123456    test

SecondTable

sid  sname         saddress
1     Anil             Kumar
2     ABC            test

ThirdTable

sid  sname         saddress
1     Anil             Kumar
2     ABC            A987654





 My Output should be
 ------------------------
StudentTable -- sname (since sname column contains value with length 7 and first character is A
and rest of the characters are digits....)

ThirdTable--saddress(since saddress column contains value with length 7 and first character is A
and rest of the characters are digits....)

How to create another table with records that satisy your conditions from original table ?

Original Table:Employees







How to create another table which contains employee id greater than 105.
Following is the syntax.

create table tempemp
as select * from employees where employee_id > 105;

Now select table tempemp.It contains only employees having employee_id > 105;

Let us select records table from tempemp and check the results.
You can see new table contains employees > 106.

select * from tempemp;