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