1 从数据库查询多条记录,通用方法
// 查询多个记录
protected Vector selectSomeNote(String sql) {
Vector<Vector<Object>> vector = new Vector<Vector<Object>>();// 创建二位的动态数组结果集向量,
Connection conn = JDBC.getConnection();// 获得数据库连接
try {
Statement stmt = conn.createStatement();// 创建连接状态对象
ResultSet rs = stmt.executeQuery(sql);// 执行SQL语句获得查询结果
int columnCount = rs.getMetaData().getColumnCount();// 获得查询数据表的列数
int row = 1;// 定义行序号
while (rs.next()) {// 遍历结果集
Vector<Object> rowV = new Vector<Object>();// 创建行向量
rowV.add(new Integer(row++));// 添加行序号
for (int column = 1; column <= columnCount; column++) {
rowV.add(rs.getObject(column));// 添加列值
}
vector.add(rowV);// 将行向量添加到结果集向量中
}
rs.close();// 关闭结果集对象
stmt.close();// 关闭连接状态对象
} catch (SQLException e) {
e.printStackTrace();
}
return vector;// 返回结果集向量
}
2 从数据库查询一条记录,通用方法
// 查询单个记录
protected Vector selectOnlyNote(String sql) {
Vector<Object> vector = null;
Connection conn = JDBC.getConnection();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
int columnCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
vector = new Vector<Object>();
for (int column = 1; column <= columnCount; column++) {
vector.add(rs.getObject(column));
}
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return vector;
}
3 从数据库查询多个值,通用方法
// 查询多个值
protected Vector selectSomeValue(String sql) {
Vector<Object> vector = new Vector<Object>();
Connection conn = JDBC.getConnection();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
vector.add(rs.getObject(1));
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return vector;
}
4 从数据库查询单个值,通用方法
// 查询单个值
protected Object selectOnlyValue(String sql) {
Object value = null;
Connection conn = JDBC.getConnection();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
value = rs.getObject(1);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
return value;
}
5 插入、修改、删除记录,通用方法
public boolean longHaul(String sql) {
boolean isLongHaul = true;// 默认持久化成功
Connection conn = JDBC.getConnection();// 获得数据库连接
try {
conn.setAutoCommit(false);// 设置为手动提交
Statement stmt = conn.createStatement();// 创建连接状态对象
stmt.executeUpdate(sql);// 执行SQL语句
stmt.close();// 关闭连接状态对象
conn.commit();// 提交持久化
} catch (SQLException e) {
isLongHaul = false;// 持久化失败
try {
conn.rollback();// 回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
return isLongHaul;// 返回持久化结果
}
6 数据库建立连接,关闭连接方法
public class JDBC {
private static final String DRIVERCLASS = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
private static final String URL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db_mm";
private static final String USERNAME = "sa";
private static final String PASSWORD = "123456";
private static final ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
static {// 通过静态方法加载数据库驱动,类加载器在加载这个类的时候执行该方法
try {
Class.forName(DRIVERCLASS).newInstance();// 加载数据库驱动
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {// 创建数据库连接的方法,从线程中获取多余连接,减少资源消耗
Connection conn = threadLocal.get();// 从线程中获得数据库连接
if (conn == null) {// 没有可用的数据库连接
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);// 创建新的数据库连接
threadLocal.set(conn);// 将数据库连接保存到线程中
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
public static boolean closeConnection() {// 关闭数据库连接
boolean isClosed = true;
Connection conn = threadLocal.get();// 从线程中获得数据库连接
threadLocal.set(null);// 清空线程中的数据库连接
if (conn != null) {// 数据库连接可用
try {
conn.close();// 关闭数据库连接
} catch (SQLException e) {
isClosed = false;
e.printStackTrace();
}
}
return isClosed;
}