simple query jdbc java

Querys.java

package com.blogspot.na5cent.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 *
 * @author redcrow
 */
public class Querys {

    private String sql;
    private Map<Integer, Object> parameters;

    private Querys(String sql) {
        this.sql = sql;
    }

    public static Querys fromSQL(String sql) {
        return new Querys(sql);
    }

    public Querys setParameter(Integer key, Object value) {
        if (parameters == null) {
            parameters = new HashMap<Integer, Object>();
        }

        parameters.put(key, value);
        return this;
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
        Properties properties = PropertiesFileUtils.load("/database/jdbc.properties");
        //
        String databaseUrl = properties.getProperty("database_url");
        String databaseUsername = properties.getProperty("database_username");
        String databasePassword = properties.getProperty("database_password");
        String jdbcDriver = properties.getProperty("jdbc_driver");

        Class.forName(jdbcDriver);
        return DriverManager.getConnection(databaseUrl, databaseUsername, databasePassword);
    }

    private void close(ResultSet resultSet, Statement statement, Connection connection) throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }

        if (statement != null) {
            statement.close();
        }

        if (connection != null) {
            connection.close();
        }
    }

    private ResultSet setParametersAndExecuteQuery(PreparedStatement statement) throws SQLException {
        if(parameters != null) {
            for (Map.Entry<Integer, Object> entry : parameters.entrySet()) {
                statement.setObject(entry.getKey(), entry.getValue());
            }
        }

        //query
        return statement.executeQuery();
    }

    private List<Map<String, Object>> transformResultSet(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()) {

            Map<String, Object> rowMap = new HashMap<String, Object>();
            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                String columnName = metaData.getColumnName(columnIndex);
                Object columnValue = resultSet.getObject(columnName);
                rowMap.put(columnName, columnValue);
            }

            results.add(rowMap);
        }

        return results;
    }

    public List<Map<String, Object>> getResults() throws SQLException, ClassNotFoundException, IOException {
        List<Map<String, Object>> results = null;
        //
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            //START Transaction ------------------------------------------------
            connection.setAutoCommit(false);

            //set parameters
            statement = connection.prepareStatement(sql);
            resultSet = setParametersAndExecuteQuery(statement);

            results = transformResultSet(resultSet);

            connection.commit();
            //END Transaction --------------------------------------------------
        } catch (SQLException ex) {
            throw ex;
        } catch (ClassNotFoundException ex) {
            throw ex;
        } finally {
            close(resultSet, statement, connection);
            return results;
        }
    }
}

Note :
PropertiesUtils.java  reference : http://na5cent.blogspot.com/2012/12/java-read-properties-file.html

Example1
QuerysTest.java

package com.blogspot.na5cent.util;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author redcrow
 */
public class QuerysTest {
    
    private static final Logger LOG = LoggerFactory.getLogger(QuerysTest.class);
    
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        List<Map<String, Object>> results;
        results = Querys.fromSQL("SELECT * FROM Restaurant").getResults();
        
        for(Map<String, Object> result : results){
            for(Map.Entry<String, Object> entry : result.entrySet()){
                LOG.debug("key, value => [{},{}]", entry.getKey(), entry.getValue());
            }
        }
    }
}

Example2

//
List<Map<String, Object>> results;
results = Querys.fromSQL("SELECT * FROM Restaurant rest WHERE rest.id = ?")
                .setParameter(1, 1)
                .getResults();
//

classpath:database/jdbc.properties

jdbc_driver=com.mysql.jdbc.Driver
database_url=jdbc:mysql://localhost:3306/testdb?zeroDateTimeBehavior=convertToNull
database_username=root
database_password=root