Wednesday 1 October 2014

Get column data using index number of the column

Columns are numbered from 1 on wards. Retrieving the column data using index number is efficient.

Below table shows various methods that you can get the column value using index number.

Method
Description
Array getArray(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as an Array object in Java.
InputStream getAsciiStream(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a stream of ASCII characters.
BigDecimal getBigDecimal(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.math.BigDecimal with full precision.
InputStream getBinaryStream(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a stream of uninterpreted bytes.
Blob getBlob(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a Blob object in Java.
boolean getBoolean(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a boolean in Java.
byte getByte(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a byte in Java.
byte[] getBytes(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a byte array in Java.
Reader getCharacterStream(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.io.Reader object.
Clob getClob(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a Clob object in Java.
Date getDate(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.sql.Date object in Java.
Date getDate(int columnIndex, Calendar cal)
Returns the value of the column in the current row of this ResultSet object as a java.sql.Date object in the Java (Using Appropriate Calendar).
double getDouble(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a double in Java.
float getFloat(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a float value in Java.
int getInt(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a int value in Java.
long getLong(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a long value in Java.
Reader getNCharacterStream(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.io.Reader object.
NClob getNClob(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a NClob object in Java.
String getNString(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a String in Java(Intended to use when accessing NCHAR,NVARCHAR and LONGNVARCHAR columns).
Object getObject(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as an Object in java.
T getObject(int columnIndex, Class<T> type)
Returns the value of the column in the current row of this ResultSet object as requested Java data type, if conversion supports.
Object getObject(int columnIndex, Map<String,Class<?>> map)
Returns the value of the column in the current row of this ResultSet object as Object in Java.This method uses the given Map object for the custom mapping of the SQL structured or distinct type that is being retrieved.
Ref getRef(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a Ref object in Java.
RowId getRowId(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.sql.RowId object in Java.
short getShort(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a short in java.
SQLXML getSQLXML(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.sql.SQLXML object in Java.
String getString(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a String in Java.
Time getTime(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.sql.Time object java.
Time getTime(int columnIndex, Calendar cal)
Returns the value of the column in the current row of this ResultSet object as a java.sql.Time object java(uses the given calendar to construct an appropriate millisecond value for the time).
TimeStamp getTimestamp(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.sql.Timestamp object in Java.
TimeStamp getTimestamp(int columnIndex, Calendar cal)
Returns the value of the column in the current row of this ResultSet object as a java.sql.Timestamp object in Java(uses the given calendar to construct an appropriate millisecond value for the time)..
URL getURL(int columnIndex)
Returns the value of the column in the current row of this ResultSet object as a java.net.URL object in Java.

/* Step 1: Import sql package */
import java.sql.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException{
        
        Connection conn = getConnection();

          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement();
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);        
        query = "INSERT INTO employee values(3, \"Ptr\")";
        stmt.execute(query);

        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        while(rs.next()){
            int id  = rs.getInt(1);
            String name = rs.getString(2);
            System.out.println(id +" " + name);
        }
        
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Output
Loading/Registering driver
Connecting to database
1 Krishna
2 Arjun
3 Ptr
Dropping table employee



Prevoius                                                 Next                                                 Home

No comments:

Post a Comment