How to fetch data from the database

There are several methods to fetch data from the Database. I have checked out my best things to program in various methods. Please check out the various ways, described below. Out of this i personally prefer DNS less connectivity, as its very easy and handy for any user.

Here goes the code:
============

JDBC-ODBC Bridge
============
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MainClass {
public static Connection getConnection() throws Exception {
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc:odbc:northwind";
String username = "";
String password = "";
Class.forName(driver); // load JDBC-ODBC driver
return DriverManager.getConnection(url, username, password);
}

public static void main(String args[]) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
String query = "select EmployeeID, LastName, FirstName from Employees";
rs = stmt.executeQuery(query);
while (rs.next()) {
System.out.println(rs.getString("EmployeeID") + " " + rs.getString("LastName") + " "
+ rs.getString("FirstName"));
}
} catch (Exception e) {
// handle the exception
e.printStackTrace();
System.err.println(e.getMessage());
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (Exception ee) {
ee.printStackTrace();
}
}
}
}



JdbcOdbc Connect
============
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class JdbcConnect {

public static void main(String[] args) throws Exception {
Connection conn1 = null;
Connection conn2 = null;
Connection conn3 = null;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String jdbcUrl = "jdbc:odbc:authors";

String user = "yourName";
String pwd = "mypwd";

conn1 = DriverManager.getConnection(jdbcUrl);
if (conn1 != null) {
System.out.println("Connection 1 successful!");
}
Properties prop = new Properties();
prop.put("user", user);
prop.put("password", pwd);

conn2 = DriverManager.getConnection(jdbcUrl, prop);
if (conn2 != null) {
System.out.println("Connection 2 successful!");
}
conn3 = DriverManager.getConnection(jdbcUrl, user, pwd);
if (conn3 != null) {
System.out.println("Connection 3 successful!");
}
conn1.close();
conn2.close();
conn3.close();
if (conn1.isClosed()) {
System.out.println("Connection 1 is closed");
}

if (conn2.isClosed()) {
System.out.println("Connection 2 is closed");
}
if (conn3.isClosed()) {
System.out.println("Connection 3 is closed");
}

conn1.close();
conn2.close();
conn3.close();

}
}

jdbc odbc bridge connection string
======================
import java.sql.Connection;
import java.sql.DriverManager;

public class Main {
public static void main(String[] argv) throws Exception {
String url = "jdbc:odbc:datasource";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection connection = DriverManager.getConnection(url);
System.out.println("Connect to " + connection.getCatalog() + " a success!");
}
}


Simple example of JDBC-ODBC functionality
============================
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcDemo {
public static void main(String args[]) throws Exception {
String query = "SELECT Name,Description,Qty,Cost FROM Stock";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String name = rs.getString("Name");
String desc = rs.getString("Description");
int qty = rs.getInt("Qty");
float cost = rs.getFloat("Cost");
System.out.println(name + ", " + desc + "\t: " + qty + "\t@ $" + cost);
}
con.close();
}
}


Using a PreparedStatement through sun.jdbc.odbc.JdbcOdbcDriver
==========================================

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PreparedStmt {
public static void main(String args[]) throws Exception {

String query = "SELECT * FROM Stock WHERE Item_Number = ?";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, 2);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String name = rs.getString("Name");
String desc = rs.getString("Description");
int qty = rs.getInt("Qty");
float cost = rs.getFloat("Cost");
System.out.println(name + ", " + desc + "\t: " + qty + "\t@ $" + cost);
}
}
}


Creating and using a stored procedure with sun.jdbc.odbc.JdbcOdbcDriver
==============================================


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

public class CallableStmt {
public static void main(String args[]) throws Exception {
String storedProc = "create procedure SHOW_ORDERS_BY_STATE @State CHAR (2) as "
+ "select c.Last_Name+', '+c.First_Name AS Name,o.Order_Number "
+ "from CUSTOMERS c, ORDERS o where c.Customer_Number = o.Customer_Number "
+ "AND c.State = @State order by c.Last_Name;";

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Customers");
Statement stmt = con.createStatement();
stmt.executeUpdate(storedProc);
CallableStatement cs = con.prepareCall("{call SHOW_ORDERS_BY_STATE(?)}");
cs.setString(1, "NJ");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
String name = rs.getString("Name");
int orderNo = rs.getInt("Order_Number");
System.out.println(name + ": " + orderNo);
}
}
}


Retrieving a ResultSet from JdbcOdbcDriver
===========================
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PrintResultSet {
public static void main(String args[]) throws Exception {
String query = "SELECT Name,Description,Qty,Cost FROM Stock";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {
System.out.print(rs.getString("Name") + "\t");
System.out.print(rs.getString("Description") + "\t");
System.out.print(rs.getInt("Qty") + "\t");
System.out.println(rs.getFloat("Cost"));
}
}
}


Getting an output parameter from a stored procedure
=================================
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class CheckPassword {
public static void main(String args[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Customers", "user", "pwd");

CallableStatement cs = con.prepareCall("{call CHECK_USER_NAME(?,?,?)}");
cs.setString(1, "C");
cs.setString(2, "V");
cs.registerOutParameter(3, java.sql.Types.VARCHAR);
cs.executeUpdate();
System.out.println(cs.getString(3));
con.close();
}
}


Opening an updatable ResultSet
====================

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

public class PrintResultSet {
public static void main(String args[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc: Contacts");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT Name,Description,Qty,Cost FROM Stock");
ResultSetMetaData md = rs.getMetaData();

if (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE)
System.out.println("UPDATABLE");
else
System.out.println("READ_ONLY");

int nColumns = md.getColumnCount();
for (int i = 1; i <= nColumns; i++) {
System.out.print(md.getColumnLabel(i) + ((i == nColumns) ? "\n" : "\t"));
}
while (rs.next()) {
rs.updateString("Street", "123 Main");
rs.updateRow();
for (int i = 1; i <= nColumns; i++) {
System.out.print(rs.getString(i) + ((i == nColumns) ? "\n" : "\t"));
}
}
}
}

Using ResultSetMetaData from jdbc:odbc
==========================
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class PrintResultSet {
public static void main(String args[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Inventory");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from employee");
ResultSetMetaData md = rs.getMetaData();

int nColumns = md.getColumnCount();
for (int i = 1; i <= nColumns; i++) {

System.out.print(md.getColumnLabel(i) + ((i == nColumns) ? "\n" : "\t"));
}
while (rs.next()) {
for (int i = 1; i <= nColumns; i++) {
System.out.print(rs.getString(i) + ((i == nColumns) ? "\n" : "\t"));
}
}
}
}



Creating a table using JdbcOdbcDriver
=========================

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

public class TableMaker {
static String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";

static String dbName = "Contacts";

static String url = "jdbc:odbc:";

static String SQLCreate = "CREATE TABLE CONTACT_INFO ("
+ "CONTACT_ID INTEGER NOT NULL PRIMARY KEY,"
+ "ZIP VARCHAR(10) NOT NULL" + ");";

public static void main(String[] args) throws Exception {
Class.forName(jdbcDriver);
url += dbName;
Connection con = null;
Statement stmt = null;
con = DriverManager.getConnection(url);
stmt = con.createStatement();
stmt.execute(SQLCreate);
con.close();
if (con != null) {
con.close();
}
if (stmt != null) {
stmt.close();
}

}
}

Using INSERT with JdbcOdbcDriver
=======================


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

import sun.jdbc.odbc.JdbcOdbcDriver;

public class DataInserter {
public static void main(String args[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
DriverManager.registerDriver(new JdbcOdbcDriver());
String SQLCommand = "INSERT INTO CONTACT_INFO "
+ "(First_Name,MI,Last_Name,Street,City,State,Zip) " + "VALUES "
+ "('Michael','J','Corleone','86 Horsehead Blvd','NY','NY','12345');";
String url = "jdbc:odbc:Contacts";
Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
stmt.execute(SQLCommand);
con.close();

}
}



Using UPDATE with JdbcOdbcDriver
=======================
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import sun.jdbc.odbc.JdbcOdbcDriver;

public class DataUpdater {
public static void main(String args[]) throws Exception {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
DriverManager.registerDriver(new JdbcOdbcDriver());
String url = "jdbc:odbc:Contacts";

Connection con = DriverManager.getConnection(url);
Statement stmt = con.createStatement();
String SQLCommand = "UPDATE CONTACT_INFO " + "SET STREET = '58 Broadway', ZIP = '10008' "
+ "WHERE First_Name = 'Michael' AND " + "Last_Name ='Corleone';";

stmt.execute(SQLCommand);
con.close();
}
}

Creating and dropping indexes
===================

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

public class PrintIndexedResultSet {
public static void main(String args[]) throws Exception {
String query = "SELECT STATE, COUNT(STATE) FROM MEMBER_PROFILES GROUP BY STATE";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Members");
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE INDEX STATE_INDEX ON MEMBER_PROFILES(STATE)");

java.util.Date startTime = new java.util.Date();

ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData md = rs.getMetaData();

int nColumns = md.getColumnCount();
for (int i = 1; i <= nColumns; i++) {
System.out.print(md.getColumnLabel(i) + ((i == nColumns) ? "\n" : "\t"));
}

while (rs.next()) {
for (int i = 1; i <= nColumns; i++) {
System.out.print(rs.getString(i) + ((i == nColumns) ? "\n" : "\t"));
}
}
java.util.Date endTime = new java.util.Date();
long elapsedTime = endTime.getTime() - startTime.getTime();
System.out.println("Elapsed time: " + elapsedTime);

stmt.executeUpdate("DROP INDEX MEMBER_PROFILES.STATE_INDEX");
}
}

Using DSN-less connection
=================
import java.sql.Connection;
import java.sql.DriverManager;

public class Main {
public static void main(String[] argv) throws Exception {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String myDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:/data.MDB";
Connection conn = DriverManager.getConnection(myDB, "UserName", "Password");
ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {
System.out.println(rs.getString("EmployeeID") + " " + rs.getString("LastName") + " "
+ rs.getString("FirstName"));
}
}catch (Exception e) {
// handle the exception
e.printStackTrace();
System.err.println(e.getMessage());
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (Exception ee) {
ee.printStackTrace();
}
}
}