Secure DataAccessLayer for JSP MVC application.
Post date: Jul 19, 2011 3:17:32 AM
I plan to create one Data Access Layer that need to handle all the database related activities like select, insert, update and calling the existing procedure without provide the place for SQL Injection.
TechDBManager used to establish the connection and read the property files if you required.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import javax.servlet.ServletException;
import oracle.jdbc.OracleDriver;
public class TechDBManager extends OracleDriver {
private static Properties prop;
/**
* @param prop
*/
public TechDBManager(Properties prop) {
super();
this.prop = prop;
}
// Get Properties files
public static Properties getProp() {
return prop;
}
public static void setProp(Properties prop) {
TechDBManager.prop = prop;
}
public static Connection getTechnoConnection() throws SQLException, ServletException{
try{
//i am trying to make the connection like this DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl",
"technokratz", "implee");
String driver =prop.getProperty("driverClassName");
String url =prop.getProperty("url");
String username =prop.getProperty("username");
String password = prop.getProperty("password");
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
throw new ServletException("Database Connection Failed, Try Again!");
} //Loading of JDBC driver, driver has been loaded but not instantiated
}
}
DAO Interface:
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.servlet.ServletException;
public interface TechDataAccessObject {
//execute insert operations
public int execute(String sql, Object... params) throws SQLException, ServletException;
//This will used to select the information from database and return list of object as output. Here your object attributes should match with database column name.
public <E> List<E> fetch(Class<E> clazz, String sql, Object... parms) throws SQLException, InstantiationException, IllegalAccessException, SecurityException, NoSuchFieldException, ServletException ;// Return E object as output.
//Calling the procedure
public void call (String procedure ) throws SQLException, ServletException;
//Get the result set as out if you required.
public ResultSet getRow(String sql, Object... params) throws SQLException, ServletException;
}
DAO Implementation:
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import com.great.dao.TechDBManager;
import com.great.dao.FieldMaping;
import com.great.pojo.IConstant;
public class TechJdbcDataAccessObject implements TechDataAccessObject {
// TO PERFORM DML OPERATIONS
@Override
public int execute(String sql, Object... params) throws SQLException, ServletException {
Connection conn = null;
PreparedStatement pst = null;
try{
conn = TechDBManager.getTechnoConnection();
pst= conn.prepareStatement(sql);
if(params!= null){
for(int i=0; i<params.length; i++)
pst.setObject(i+1, params[i]);
}
return pst.executeUpdate();
}finally{
try{
pst.close();
conn.commit();
conn.close();
}catch (Exception e) {
throw new ServletException("Datbase: Unable to Process ");
}
}
}
// TO FETCH ALL THE INFORMACTION AND RETURN IN THE FORM OF RESULT SET
@Override
public ResultSet getRow(String sql, Object... params) throws SQLException, ServletException{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try{
conn = TechDBManager.getTechnoConnection();
pst = conn.prepareStatement(sql);
if(params!= null){
for(int i=0; i< params.length; i++)
pst.setObject(i+1, params[i]);
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return rs = pst.executeQuery();
}
// TO FETCH ALL THE ROW FROM DATABSE AND FILL IT INTO MY CLASS
@Override
public <E> List<E> fetch(Class<E> clazz, String sql, Object... parms)
throws InstantiationException, IllegalAccessException,
SecurityException, NoSuchFieldException, SQLException, ServletException {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
SimpleDateFormat datefmt = new SimpleDateFormat(IConstant.DATEFORMATE);
try{
//conn = TechDBManager.getConnection();
conn = TechDBManager.getTechnoConnection();
pst = conn.prepareStatement(sql);
System.out.println(sql);
if(parms!=null){
for(int i=0; i<parms.length; i++)
pst.setObject(i+1, parms[i]);
}
rs= pst.executeQuery();
// Meta Data about the selected query
ResultSetMetaData rsmd = rs.getMetaData();
// Create new Instance of the Class given to me
List<E> list = new ArrayList<E>();
while(rs.next()){
E e =clazz.newInstance();
for(int i=1; i<= rsmd.getColumnCount(); i++){
// It has matching equvalent Database varibale in the Local Object variable
Field field =clazz.getDeclaredField(
rsmd.getColumnName(i).toLowerCase()); // Access declared Fields
field.setAccessible(true);// Accessing private fields
int colType = rsmd.getColumnType(i);
if(colType == Types.NUMERIC || colType== Types.INTEGER){
field.set(e, rs.getInt(i));
}
else if (colType== Types.DECIMAL|| colType== Types.DOUBLE){
field.set(e, rs.getDouble(i));
}
else if (colType == Types.VARCHAR)
field.set(e, rs.getString(i));
else if(colType== Types.DATE || colType==Types.TIMESTAMP){
field.set(e, datefmt.format(rs.getDate(i)));
}
}
list.add(e);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new ServletException("Datbase: Something went wrong, We will fix it soon!");
}
finally{
try{
rs.close();
pst.close();
conn.close();
}catch (Exception e) {
throw new ServletException("Datbase: Something went wrong, We will fix it soon!");
}
}
}
// TO CALL THE PROCEDURE IN THE DATABASE
@Override
public void call(String procedure) throws SQLException, ServletException {
Connection conn = null;
CallableStatement cst = null;
try{
conn= TechDBManager.getTechnoConnection();
cst = conn.prepareCall("{ call "+ procedure+ "()" +"}");
cst.executeUpdate();
}
finally{
try{
cst.close();
conn.close();
}catch (Exception e) {
throw new SQLException("Datbase: Something went wrong, We will fix it soon!");
}
}
}
// To call the statement
public CallableStatement getCallableStatement(String sSql) throws ServletException
{
Connection conn = null;
try {
conn = TechDBManager.getTechnoConnection();
if (conn != null) {
return conn.prepareCall(sSql);
}
}
catch (SQLException e) {
throw new ServletException("Database Failed To Perform Your Request!");
}
return null;
}
// List contain only collection of OBject
public List<Object[]> getObjectList(String sSql){
java.sql.Statement st = null;
List<Object[]> resultTable = null;
Connection conn = null;
try {
conn = TechDBManager.getTechnoConnection();
if (conn != null) {
st = conn.createStatement();
ResultSet rs = st.executeQuery(sSql);
resultTable = new ArrayList<Object[]>();
while (rs.next()) {
int iCol = rs.getMetaData().getColumnCount();
Object[] objData = new Object[iCol];
for (int iIndex = 0; iIndex < iCol; iIndex++) {
Object obj = rs.getObject(iIndex + 1);
if (obj == null) {
objData[iIndex] = "";
}
else {
objData[iIndex] = obj;
}
}
resultTable.add(objData);
}
rs.close();
}
}
catch (Exception e) {
}
finally {
try {
st.close();
}
catch (SQLException e) {
}
}
return resultTable;
}
}
- Boobalan: