Oracle_JDBC

package excelAnalysis;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
 * Simple jdbc to oracle (add ojdbc6.jar to build path)
 */
public class Materials_HMA_Design{static Connection conn; static Statement stmt;
    static String sql, dbUser="myuser",dbUrl="jdbc:oracle:thin:@blap07vw.us:1565:mysid";
   
    public static void main(String[] args){
        try{logIt("Getting db connection");
            conn=DriverManager.getConnection(dbUrl,dbUser,dbUser+"psswrd");
        }catch(SQLException se){
            logIt("Error getting connected. "+se.getMessage());
            return;
        }
        sql="select * from dual";
        try{logIt("Reading table");
            stmt=conn.createStatement();
             ResultSet rs=stmt.executeQuery(sql); 
             while(rs.next()){
                 logIt(rs.getString(1));
            }
        }catch(SQLException e){
            logIt(sql); logIt(e.getMessage());
            logIt(e.getSQLState()); e.printStackTrace();
        }finally{try{conn.close();}catch(SQLException ee){}}   
        logIt("Job complete");
    }
    static void logIt(String msg){        System.out.println(msg);    }
    public static Connection getConnection(String dbUrl, String userName, String password) throws SQLException {
        Connection conn = DriverManager.getConnection(dbUrl,userName,password);
        logIt("Connected to database server as user "+userName); return conn;
    }
}

Useful db helpers

package utils.db;


import java.sql.Connection;
import java.sql.SQLException;
import utils.MainUtilsClass;

public class DbHelpers{
public static void main(String[] args){
String sql="select col from table where 1=1";
MainUtilsClass.logIt(getHtmlFriendlySqlString(sql));
}
/**
* Show HTML friendly sql output. Inserts line breaks before keywords such as FROM and WHERE 
* @param sql text
* @return text designed for friendly html output
*/
public static String getHtmlFriendlySqlString(String sql){
sql=sql.toUpperCase();
sql=sql.replace("FROM ","<br/>FROM ");
sql=sql.replace(" JOIN "," JOIN <br/> ");
sql=sql.replace("WHERE ","<br/>WHERE ");
sql=sql.replace("ORDER BY ","<br/>ORDER BY ");
return sql;
}
/**
* Show connection info such as URL and username/schema
* The Connection.getSchema() version was added in Java 7 / JDBC 4.1. 
* @param conn SQL Conn obj
* @param returnOneLine or two
* @return String of conn info
*/
public static String getConnInfoHtmlString(Connection conn, boolean returnOneLine){
StringBuffer buf=new StringBuffer(); String schemaOrUser=null;
try{buf.append("URL:"+conn.getMetaData().getURL());
}catch(AbstractMethodError err){
}catch(SQLException err){}

try{schemaOrUser=conn.getSchema();
}catch(AbstractMethodError err){
}catch(SQLException err){}
if(schemaOrUser==null){
try{schemaOrUser=conn.getMetaData().getUserName();
}catch(AbstractMethodError err){
}catch(SQLException err){}
}

if(schemaOrUser!=null){
if(returnOneLine){
buf.append(" (User:"+schemaOrUser+")");
}else{
buf.append("<br/>Schema/User:"+schemaOrUser);
}
}
return buf.toString();
}
/**
* Show HTML friendly sql output. Inserts line breaks before keywords such as FROM and WHERE.
* Also shows connection info such as URL, username/schema
* @param sql String
* @param conn SQL Conn obj
* @return String of friendly sql and conn info
*/
public static String getHtmlFriendlySqlAndConnInfoString(String sql, Connection conn){
StringBuffer buf=new StringBuffer(); 
buf.append(getConnInfoHtmlString(conn, true));
buf.append("<br/>"+getHtmlFriendlySqlString(sql));
return buf.toString();
}

}