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;
}
}
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();
}
}
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();
}
}
Comments
Post a Comment