/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package tool;

import com.google.common.base.Objects;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 *
 * @author martin
 */
public class SqlTool {
    public static String url = "orford",
                  user = "root",
                  pass = "root";
    
    public static Connection getConnection(String url, String user, String pass) throws ClassNotFoundException, SQLException{
        //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection(url,user,pass);
        
        return connection;
    }
    
    public static List<Map<String,Object>> query(Connection connection, String sql) throws ClassNotFoundException, SQLException{

        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        
        ImmutableList.Builder list = ImmutableList.builder();
        ResultSetMetaData md =rs.getMetaData();
        while(rs.next()){
            ImmutableMap.Builder map = ImmutableMap.builder();
            for (int i=1;i<=md.getColumnCount();i++){
                map.put(md.getColumnName(i),Objects.firstNonNull(rs.getObject(i),""));
            }
            list.add(map.build());
        }
        return list.build();
    }
    
    public static boolean exec(Connection connection, String sql) throws SQLException{
        Statement statement = connection.createStatement();
        return statement.execute(sql);
    }
}