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