项目中连接数据库的工具类

在项目有时会用到不同数据库，项目写了一个连接不同数据库（包括mysql，SQL server， oracle ，access）的工具类： import java.io.File;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMet

在项目有时会用到不同数据库，项目写了一个连接不同数据库（包括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();
            }
        }
    }
}