/*
* @(#)CommonSql.java 2011-9-5
*
* Copyright 2011 Bianjing,All rights reserved.
*/
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
/**
* 用于JDBC操作数据库的共通类
*
* @author Bianjing
* @version 1.0.0 2011-9-5
*/
public class CommonSql {
/** 数据源 */
private DataSource dataSource;
/** 数据库连接对象 */
private Connection connection;
/** 数据库操作对象 */
private PreparedStatement ps;
/** 数据库操作对象 */
private Statement statement;
/** 返回的数据结果集对象 */
private ResultSet rs;
/** 是否自动提交事务，默认为true，如果该值为false则需要手动提交事务 */
private boolean autoCommit = true;
/** 数据库连接是否已经打开 */
private boolean openConnection;
/** JNDI名称 */
private String jndiName;
/** 数据库驱动 */
private String driver;
/** 数据库访问地址 */
private String url;
/** 用户名 */
private String user;
/** 密码 */
private String pwd;
public CommonSql() {
}
public CommonSql(DataSource dataSource) {
this.dataSource = dataSource;
}
public CommonSql(String jndiName) {
this.jndiName = jndiName;
}
public CommonSql(String driver, String url, String user, String pwd) {
this.driver = driver;
this.url = url;
this.user = user;
this.pwd = pwd;
}
/**
* 打开数据库连接并创建数据库连接对象
* 支持通过ICO注入数据源、数据库驱动、数据库驱动、JNDI名称、数据库访问地址和用户名、密码
*
* @return boolean true:连接成功，false:连接失败
*/
public boolean openConnection() {
/**
* 通过数据源来获取数据库连接对象
*/
if (dataSource != null) {
try {
connection = dataSource.getConnection();
// 数据库连接已经打开
openConnection = true;
} catch (SQLException e) {
closeAll();
// 所有的"System.out.println"都可以替换为"logger.error"
System.out.println("从数据源获取数据库连接失败！");
throw new RuntimeException(e);
}
return openConnection;
}
/**
* 通过JNDI来获取数据库连接对象
*/
if (jndiName != null) {
try {
Context initContext = new InitialContext();
dataSource = (DataSource) initContext.lookup(jndiName);
connection = dataSource.getConnection();
// 数据库连接已经打开
openConnection = true;
} catch (Exception e) {
closeAll();
System.out.println("从JNDI获取数据库连接失败！");
throw new RuntimeException(e);
}
return openConnection;
}
/**
* 通过数据库驱动、数据库访问地址、用户名、密码来获取数据库连接对象
*/
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, user, pwd);
// 数据库连接已经打开
openConnection = true;
} catch (Exception e) {
closeAll();
System.out.println("数据库连接失败！");
throw new RuntimeException(e);
}
return openConnection;
}
/**
* 执行数据库的更新操作
*
* @param sql
* 要执行的SQL语句
* @return boolean true:执行成功，false:执行失败
*/
public boolean execUpdate(String sql, Object... args) {
boolean isPassed = false;
// 判断连接数据库是否成功
if (openConnection) {
try {
ps = connection.prepareStatement(sql);
// 设置参数
if (args != null && args.length > 0) {
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
}
ps.executeUpdate();
isPassed = true;
} catch (SQLException e) {
try {
if (autoCommit) {
connection.rollback();
}
} catch (SQLException e1) {
throw new RuntimeException(e1);
}
System.out.println("SQL:" + sql);
throw new RuntimeException(e);
} finally {
if (autoCommit) {
closeAll();
}
}
} else {
System.out.println("数据库连接对象没有打开！");
}
return isPassed;
}
/**
* 执行数据库的更新操作
*
* @param sql
* 要执行的SQL语句
* @return boolean true:执行成功，false:执行失败
*/
public boolean execUpdate(String sql, List> args) {
return execUpdate(sql, args.toArray());
}
/**
* 执行批量更新数据库操作
*
* @param sql
* 要执行的SQL语句的字符串数组
* @return boolean true:执行成功，false:执行失败
*/
public boolean execUpdate(Object[] sql) {
boolean flag = false;
// 判断连接数据库是否成功
if (openConnection) {
try {
statement = connection.createStatement();
for (int i = 0; i < sql.length; i++) {
statement.addBatch((String) sql[i]);
}
statement.executeBatch();
flag = true;
} catch (SQLException e) {
try {
if (autoCommit) {
connection.rollback();
}
} catch (SQLException e1) {
throw new RuntimeException(e1);
}
for (int i = 0; i < sql.length; i++) {
System.out.println("SQL " + (i + 1) + ":" + sql[i]);
}
throw new RuntimeException(e);
} finally {
if (autoCommit) {
closeAll();
}
}
} else {
System.out.println("数据库连接对象没有打开！");
}
return flag;
}
/**
* 执行批量更新数据库操作
*
* @param sql
* 要执行的SQL语句的集合
* @return boolean true:执行成功，false:执行失败
*/
public boolean execUpdate(List> sql) {
return execUpdate(sql.toArray());
}
/**
* 执行数据库查询操作
*
* @param sql
* 要执行的SQL语句
* @param args
* 查询参数列表
* @return ResultSet 返回查询的结果集对象
*/
public ResultSet execQuery(String sql, Object... args) {
rs = null;
// 判断连接数据库是否成功
if (openConnection) {
try {
ps = connection.prepareStatement(sql);
// 设置参数
if (args != null && args.length > 0) {
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
}
rs = ps.executeQuery();
} catch (SQLException e) {
if (autoCommit) {
closeAll();
}
System.out.println("SQL:" + sql);
throw new RuntimeException(e);
}
} else {
System.out.println("数据库连接对象没有打开！");
}
return rs;
}
/**
* 执行数据库查询操作
*
* @param sql
* 要执行的SQL语句
* @param args
* 查询参数列表
* @return ResultSet 返回查询的结果集对象
*/
public ResultSet execQuery(String sql, List> args) {
return execQuery(sql, args.toArray());
}
/**
* 根据标准SQL查询数据库，返回一个int值
*
* @param sql
* 要执行的SQL语句
* @param args
* 查询参数列表
* @return int值，如果出错则返回-1
*/
public int findForInt(String sql, Object... args) {
ResultSet rs = execQuery(sql, args);
int count = -1;
try {
if (rs != null && rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (autoCommit) {
closeAll();
}
}
return count;
}
/**
* 根据标准SQL查询数据库，返回一个int值
*
* @param sql
* 要执行的SQL语句
* @param args
* 查询参数列表
* @return int值，如果出错则返回-1
*/
public int findForInt(String sql, List> args) {
return findForInt(sql, args.toArray());
}
/**
* 关闭所有数据库连接对象
*/
public void closeAll() {
if (rs != null || ps != null || statement != null || connection != null) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
rs = null;
if (ps != null || statement != null || connection != null) {
try {
if (ps != null && !ps.isClosed()) {
ps.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
ps = null;
if (statement != null || connection != null) {
try {
if (statement != null && !statement.isClosed()) {
statement.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
statement = null;
try {
if (connection != null
&& !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
connection = null;
}
}
}
}
}
}
}
}
/**
* 提交事务并关闭数据库连接
*/
public void commit() {
try {
if (!autoCommit) {
connection.commit();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
autoCommit = true;
closeAll();
}
}
/**
* 回滚事务并关闭数据库连接
*/
public void rollback() {
try {
if (!autoCommit) {
connection.rollback();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
autoCommit = true;
closeAll();
}
}
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public boolean getAutoCommit() {
return autoCommit;
}
public void setAutoCommit(boolean autoCommit) {
try {
connection.setAutoCommit(autoCommit);
} catch (SQLException e) {
closeAll();
throw new RuntimeException(e);
}
this.autoCommit = autoCommit;
}
public boolean getOpenConnection() {
return openConnection;
}
public String getJndiName() {
return jndiName;
}
public void setJndiName(String jndiName) {
this.jndiName = jndiName;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public Connection getConnection() {
return connection;
}
/**
* 测试数据库连接是否成功
*
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=demo";
String user = "sa";
String pwd = "sa";
CommonSql commonSql = new CommonSql(driver, url, user, pwd);
if (commonSql.openConnection()) {
System.out.println("数据库连接成功！");
DatabaseMetaData dbMetaData = commonSql.getConnection()
.getMetaData();
System.out
.print("当前连接的数据库是:" + dbMetaData.getDatabaseProductName());
System.out.println(" " + dbMetaData.getDatabaseProductVersion());
} else {
System.out.println("数据库连接失败！");
}
commonSql.closeAll();
}
}
1
顶
0
踩
分享到：
2010-03-03 20:02
浏览 5743
评论
2 楼
vipbooks
2011-10-10
liangzai718 写道
RWProperties.getProperty("driver", "com/test/config/db.properties");
-- RWProperties类貌似没有getProperty(arg0,arg1)这个方法呀？这个怎么调呀？
哈哈，不好意思啊，这个类已经改了很多地方了，我还没有把改好的上传上来，今天我就更新一下！
RWProperties已经更新过了，易用性更好了！
1 楼
liangzai718
2011-10-09
RWProperties.getProperty("driver", "com/test/config/db.properties");
-- RWProperties类貌似没有getProperty(arg0,arg1)这个方法呀？这个怎么调呀？
1372
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
