プロジェクトでデータベースに接続するツールクラス

プロジェクトで異なるデータベースが使用される場合があります.プロジェクトにはmysql、SQL server、oracle、accessなど、異なるデータベースに接続するツールクラスが書かれています.

import java.io.File;
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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ConnectionDbUtils {

    /**
     *          (sql server)
     * 
     * @param server
     *               
     * @param database
     *                
     * @param user_id
     *               
     * @param password
     *              
     * @return Connection
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getSqlServerConnection(String server, String database, String user_id, String password)
            throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection con = DriverManager.getConnection("jdbc:sqlserver://" + server + ":1433;DatabaseName=" + database,
                user_id, password);
        return con;
    }
    
    
    /**
     *          (MySql)
     * 
     * @param server
     *               
     * @param database
     *                
     * @param user_id
     *               
     * @param password
     *              
     * @return Connection
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getMySqlConnection(String server, String database, String user_id, String password)
            throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://"+server+":3306/" + database,
        		user_id, password);
    }

    /**
     * JDBC  oracle
     * @param server IP
     * @param database    
     * @param user_id    
     * @param password   
     * @param sql 
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getOracleConnection(String server, String database, String user_id, String password)
            throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" + server + ":1521:" + database, user_id,
                password);
        return conn;
    }

    /**
     * JDBC  Access
     * @param database      
     * @param user_id    
     * @param password   
     * @param sql 
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     */
    public static Connection getAccessConnection(String database, String user_id, String password)
            throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        System.out.println("==============" + database);
        Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
        Connection conn = DriverManager.getConnection("jdbc:Access:///" + database, user_id, password);
        System.out.println("    ");
        return conn;
    }

    /**
     * Access     
     * @param database
     * @param user_id
     * @param password
     * @param strSql
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> queryAccessData(String database, String user_id, String password,
            String strSql) throws Exception {
		File file = new File(database);
		if (file.exists()){
			if (file.canWrite()) {
				System.out.println("   ");
			} else {
				System.out.println("  ");
				file.setWritable(true);
			}
		} else {
			System.out.println("   ");
		}
        Connection con = ConnectionDbUtils.getAccessConnection(database, user_id, password);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(strSql);
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i);
                Object objValue = rs.getObject(columnName);
                map.put(columnName, objValue);
            }
            listMap.add(map);
        }
        return listMap;
    }

    /**
     * Access   
     * @param database
     * @param user_id
     * @param password
     * @param sql
     */
    public static void createAccessSQLExecute(String database, String user_id, String password, String sql) {

        Statement stmt = null;
        try {
            Connection con = ConnectionDbUtils.getAccessConnection(database, user_id, password);
            stmt = con.createStatement();
            int i = stmt.executeUpdate(sql);
            System.out.println("  sql  ：" + sql);
            System.out.println("    ！     " + i);
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("    ，              ");
        }
        finally {
            try {
                if (null != stmt) {
                    stmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * oracle     
     * @param server
     * @param database
     * @param user_id
     * @param password
     * @param strSql
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> queryOracleData(String server, String database, String user_id,
            String password, String strSql) throws Exception {
        Connection con = ConnectionDbUtils.getOracleConnection(server, database, user_id, password);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(strSql);
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i);
                Object objValue = rs.getObject(columnName);
                map.put(columnName, objValue);
            }
            listMap.add(map);
        }
        return listMap;
    }

    /**
     * oracle   
     * @param server
     * @param database
     * @param user_id
     * @param password
     * @param sql
     */
    public static void createOracleSQLExecute(String server, String database, String user_id, String password,
            String sql) {

        Statement stmt = null;
        try {
            Connection con = ConnectionDbUtils.getOracleConnection(server, database, user_id, password);
            stmt = con.createStatement();
            System.out.println("  sql  ：" + sql);
            int i = stmt.executeUpdate(sql);
            System.out.println("    ！     " + i);
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("    ，              ");
        }
        finally {
            try {
                if (null != stmt) {
                    stmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     *     (sql server)
     * 
     * @param server    
     * @param database     
     * @param user_id    
     * @param password   
     * @param strSql sql  
     * @return List<Map<String,Object>>
     * @throws Exception
     */
    public static List<Map<String, Object>> querySqlServerData(String server, String database, String user_id,
            String password, String strSql) throws Exception {
        Connection con = ConnectionDbUtils.getSqlServerConnection(server, database, user_id, password);
        Statement stmt = con.createStatement();
        System.out.println("querySqlServerData sql  ===========" + strSql);
        ResultSet rs = stmt.executeQuery(strSql);
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i);
                Object objValue = rs.getObject(columnName);
                map.put(columnName, objValue);
            }
            listMap.add(map);
        }
        return listMap;
    }

    /**
     *   sql   、  、    
     * 
     * @param conn
     * @param sql
     */
    public static void createSQLExecute(String server, String database, String user_id, String password,
            List<String> sql) {
        Statement stmt = null;
        try {
            Connection con = ConnectionDbUtils.getSqlServerConnection(server, database, user_id, password);
            stmt = con.createStatement();
            for (String s : sql) {
                System.out.println("  sql  ：" + sql);
                int i = stmt.executeUpdate(s);
                System.out.println("    ！     " + i);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("    ，              ");
        }
        finally {
            try {
                if (null != stmt) {
                    stmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     *   sql   、  、    
     * 
     * @param conn
     * @param sql
     */
    public static void createSQLExecute(String server, String database, String user_id, String password, String sql) {

        Statement stmt = null;
        try {
            System.out.println("  sql  ：" + sql);
            Connection con = ConnectionDbUtils.getSqlServerConnection(server, database, user_id, password);

            stmt = con.createStatement();
            int i = stmt.executeUpdate(sql);
            System.out.println("    ！     " + i);
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("    ，              ");
        }
        finally {
            try {
                if (null != stmt) {
                    stmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    
    
    /**
     * MySql     
     * @param server
     * @param database
     * @param user_id
     * @param password
     * @param strSql
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> queryMySqlData(String server, String database, String user_id,
            String password, String sql) throws Exception {
        Connection con = ConnectionDbUtils.getMySqlConnection(server, database, user_id, password);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i);
                Object objValue = rs.getObject(columnName);
                map.put(columnName, objValue);
            }
            listMap.add(map);
        }
        return listMap;
    }
    
    /**
     *   MySql    
     * @param server
     * @param database
     * @param user_id
     * @param password
     * @param sql
     */
    public static void createMySqlExecute(String server,String database,String user_id,String password,String sql){
    	Connection con=null;
        Statement stmt = null;
        try {
            System.out.println("  sql  ：" + sql);
            con= ConnectionDbUtils.getMySqlConnection(server, database, user_id, password);
            stmt = con.createStatement();
            int successCount = stmt.executeUpdate(sql);
            System.out.println("    ！     " + successCount);
        }
        catch (Exception e) {
            e.printStackTrace();
            System.out.println("    ，              ");
        }
        finally {
            try {
                if (null != stmt) {
                    stmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}