dsd.dao.DAOProvider.java Source code

Java tutorial

Introduction

Here is the source code for dsd.dao.DAOProvider.java

Source

/*******************************************************************************
 * Copyright 2013 Andrea Bottoli, Lorenzo Pagliari, Marko Brcic, Dzana Kujan, Nikola Radisavljevic, Jorn Tillmanns, Miraldi Fifo
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package dsd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;

public class DAOProvider {

    private static DataSource dataSource;

    private DAOProvider() {

    }

    /**
     * 
     * @return
     */
    public static DataSource getDataSource() {
        try {
            if (dataSource == null) {
                Context ctx = new InitialContext();
                dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/RTBMconnection");
            }
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    /**
     * This is the secure Version of SelectTable. To use this function in the
     * right way, you use ? instead of parameters and give the parameters
     * instead as string into the parameter-array.
     * 
     * For Example: Instead of : "SELECT * FROM tb1 WHERE user= 'foo' and name
     * ='bar' use "SELECT * FROM tb1 WHERE user= ? and name = ?" and as
     * parameters[0]='foo' and parameters[1]='bar'
     * 
     * The order in the parameters are important!!
     * 
     * @param table
     * @param select
     * @param where
     * @param order
     * @param con
     * @param parameters
     * @return
     * @throws SQLException
     */
    public static ResultSet SelectTableSecure(String table, String select, String where, String order,
            Connection con, Object[] parameters) throws SQLException {
        ResultSet resultSet = null;
        try {
            PreparedStatement command = con.prepareStatement(String.format("select %s from %s %s %s", select, table,
                    (where.trim().equals("") ? "" : "where " + where),
                    (order.trim().equals("") ? "" : "order by " + order)));
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    SetParameter(command, parameters[i], i + 1);
                }
            }
            resultSet = command.executeQuery();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return resultSet;
    }

    /**
     * the delete row function done in a secure way
     * 
     * @param table
     * @param where
     * @param con
     * @return
     * @throws SQLException
     */
    public static int DeleteRowSecure(String table, String where, Connection con, Object[] parameters)
            throws SQLException {
        try {
            PreparedStatement command = con.prepareStatement(
                    String.format("delete from %s %s", table, (where.trim().equals("") ? "" : "where " + where)));

            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    SetParameter(command, parameters[i], i + 1);
                }
            }

            return command.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    /**
     * the insert row function done in a secure way
     * 
     * @param table
     * @param fields
     * @param con
     * @param valueArray
     * @return
     * @throws SQLException
     */
    public static int InsertRowSecure(String table, String fields, Connection con, Object[] valueArray)
            throws SQLException {
        try {
            String values = "";
            if (valueArray.length > 0) {
                values = "?";
            }
            for (int i = 1; i < valueArray.length; i++) {
                values += ",?";
            }

            PreparedStatement command = con
                    .prepareStatement(String.format("insert into %s (%s) values (%s)", table, fields, values));

            for (int i = 0; i < valueArray.length; i++) {
                SetParameter(command, valueArray[i], i + 1);
            }

            command.executeUpdate();

            command = con.prepareStatement(String.format("select Max(ID) from %s", table));
            ResultSet rs = command.executeQuery();
            rs.next();

            return rs.getInt(1);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public static int InsertRowsSecure(String table, String fields, Connection con, Object[][] valueArray)
            throws SQLException {
        try {
            String values = "(";
            if (valueArray[0].length > 0) {
                values += "?";
            }
            for (int i = 1; i < valueArray[0].length; i++) {
                values += ",?";
            }
            values += ")";
            String rows = "";
            for (int j = 0; j < valueArray.length; j++) {
                rows += " " + values;
                if (j != valueArray.length - 1)
                    rows += " , ";
            }

            PreparedStatement command = con
                    .prepareStatement(String.format("insert into %s (%s) values %s", table, fields, rows));

            for (int i = 0; i < valueArray.length; i++) {
                for (int j = 0; j < valueArray[i].length; j++) {
                    SetParameter(command, valueArray[i][j], i * valueArray[i].length + j + 1);
                }
            }
            command.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    /**
     * the update row method done in a secure way
     * 
     * @param table
     * @param updateColumns
     * @param where
     * @param con
     * @param valueArray
     * @param wherePartParameters
     * @return
     * @throws SQLException
     */
    public static int UpdateRowSecure(String table, String[] updateColumns, String where, Connection con,
            Object[] valueArray, Object[] wherePartParameters) throws SQLException {
        try {
            if (updateColumns.length != valueArray.length || updateColumns.length == 0)
                throw new IllegalArgumentException(
                        "The size of updateColumns and valueArray parameters should be the same!");

            String set = "";
            for (int i = 0; i < valueArray.length; i++) {
                set += updateColumns[i] + " = ?";
                if (i != valueArray.length - 1)
                    set += ", ";
            }

            PreparedStatement command = con.prepareStatement(String.format("update %s set %s %s", table, set,
                    (where.trim().equals("") ? "" : " where " + where)));

            for (int i = 0; i < valueArray.length; i++) {
                SetParameter(command, valueArray[i], i + 1);
            }
            for (int i = 0; i < wherePartParameters.length; i++) {
                SetParameter(command, wherePartParameters[i], valueArray.length + i + 1);
            }

            return command.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public static int UpdateRowsSecure(String table, String[] fields, Connection con, Object[][] valueArray)
            throws SQLException {
        try {
            String values = "(";
            if (valueArray[0].length > 0) {
                values += "?";
            }
            for (int i = 1; i < valueArray[0].length; i++) {
                values += ",?";
            }
            values += ")";
            String rows = "";
            for (int j = 0; j < valueArray.length; j++) {
                rows += " " + values;
                if (j != valueArray.length - 1)
                    rows += " , ";
            }
            String set = "";
            // index starts from 1 cause index 0 should be ID column and this
            // doesn't goes into "on duplicate key update"
            for (int i = 1; i < fields.length; i++) {
                set += fields[i] + " = values (" + fields[i] + ")";
                if (i != fields.length - 1)
                    set += " , ";
            }
            String onDuplicateKey = "on duplicate key update " + set;

            PreparedStatement command = con.prepareStatement(String.format("insert into %s (%s) values %s %s",
                    table, StringUtils.join(fields, ','), rows, onDuplicateKey));

            for (int i = 0; i < valueArray.length; i++) {
                for (int j = 0; j < valueArray[i].length; j++) {
                    SetParameter(command, valueArray[i][j], i * valueArray[i].length + j + 1);
                }
            }
            command.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    /**
     * calls the correct method for setting the command parameter depending on
     * parameter type
     * 
     * @param command
     * @param object
     * @param parameterIndex
     * @throws SQLException
     */
    private static void SetParameter(PreparedStatement command, Object object, int parameterIndex)
            throws SQLException {
        if (object instanceof Timestamp) {
            command.setTimestamp(parameterIndex, (Timestamp) object);
        } else if (object instanceof String) {
            command.setString(parameterIndex, (String) object);
        } else if (object instanceof Long) {
            command.setLong(parameterIndex, (Long) object);
        } else if (object instanceof Integer) {
            command.setInt(parameterIndex, (Integer) object);
        } else if (object instanceof Boolean) {
            command.setBoolean(parameterIndex, (Boolean) object);
        } else if (object instanceof Float) {
            command.setFloat(parameterIndex, (Float) object);
        } else {
            throw new IllegalArgumentException(
                    "type needs to be inserted in Set parameter method of DAOProvider class");
        }

    }

}