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

在项目有时会用到不同数据库，项目写了一个连接不同数据库（包括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> 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> listMap = new ArrayList>();
 while (rs.next()) {
 Map map = new HashMap();
 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> 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> listMap = new ArrayList>();
 while (rs.next()) {
 Map map = new HashMap();
 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>
 * @throws Exception
 */
 public static List> 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> listMap = new ArrayList>();
 while (rs.next()) {
 Map map = new HashMap();
 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 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> 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> listMap = new ArrayList>();
 while (rs.next()) {
 Map map = new HashMap();
 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();
 }
 }
 }
}