import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class DBUtil {
/**
* 取得一个数据库连接
*
* @return
* @throws SQLException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
*/
public Connection getConnection() throws SQLException,
InstantiationException, IllegalAccessException,
ClassNotFoundException {
DBProperty pro = new DBProperty();
pro.setFilePath("/DB.properties");
Connection conn = null;
// 加载数据库驱动类
Class.forName(pro.getClassName()).newInstance();
// 数据库连接URL
String url = pro.getUrl();
// 数据库用户名
String user = pro.getName();
// 数据库密码
String password = pro.getPassword();
// 根据数据库参数取得一个数据库连接
conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 根据传入的SQL语句返回取出的结果
*
* @param sql
* @return
* @throws Exception
*/
public ArrayList select(String sql) throws Exception {
Connection conn = null;
ArrayList result = new ArrayList();
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
int i = 0;
while (rs.next() != false) {
ArrayList row = new ArrayList();
for (i = 1; i <= cols; ++i) {
if (rs.getString(i) == null) {
row.add("");
} else {
row.add(rs.getString(i));
}
}
result.add(row);
}
} catch (SQLException e) {
throw new Exception("select data exception:" + e.getMessage());
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
throw new Exception("rs close exception: " + e.getMessage());
}
try {
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
throw new Exception("stmt close exception: " + e.getMessage());
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new Exception("conn close exception: " + e.getMessage());
}
}
return result;
}
/**
* 根据传入的SQL语句向数据库增加一条记录
*
* @param sql
* @throws Exception
*/
public void insert(String sql) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException sqle) {
throw new Exception("insert data exception: " + sqle.getMessage());
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
throw new Exception("ps close exception: " + e.getMessage());
}
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new Exception("connection close exception: " + e.getMessage());
}
}
/**
* 根据传入的SQL语句更新数据库记录
*
* @param sql
* @throws Exception
*/
public void update(String sql) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException sqle) {
throw new Exception("update exception: " + sqle.getMessage());
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
throw new Exception("ps close exception: " + e.getMessage());
}
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new Exception("connection close exception: " + e.getMessage());
}
}
/**
* 根据传入的SQL语句删除一条数据库记录
*
* @param sql
* @throws Exception
*/
public void delete(String sql) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException sqle) {
throw new Exception("delete data exception: " + sqle.getMessage());
} finally {
try {
if (ps != null) {
ps.close();
}
} catch (Exception e) {
throw new Exception("ps close exception: " + e.getMessage());
}
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
throw new Exception("connection close exception: " + e.getMessage());
}
}
}
//使用
@SuppressWarnings("unchecked")
public static ArrayList getAlarmByCond() {
try {
DBUtil db = new DBUtil();
String sql = "select * from test ";
ArrayList result = new ArrayList();
try {
result = db.select(sql);
} catch (Exception e) {
e.printStackTrace();
}
return result;
} catch (RuntimeException re) {
throw re;
}
}
Java操作数据库工具类
最新推荐文章于 2022-08-30 06:25:16 发布