package s2jsp.bysj.dao;import java.sql.*;
import java.util.*;/**
* 数据访问基类
*
* @author Kong
*/
public class BaseDao {
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=company";
Connection conn =null;
ResultSet rs = null;
PreparedStatement prepared = null; /**
* 获取连接
*/
void GetConn() {
try {
Class.forName(DRIVER);// 注册驱动
this.conn = DriverManager.getConnection(URL, "sa", "");// 获得连接对象
} catch (Exception e) {
System.out.println("连接出错");
}
} /**
* 关闭连接,用于关闭连接通道
*/
void closeAll() {
try {
if (this.rs != null)
this.rs.close();
if (this.prepared != null)
this.prepared.close();
if (this.conn != null)
this.conn.close();
} catch (SQLException e) {
System.out.println("关闭连接时出错");
}
}
/**
* 查询数据库,
*
* @param sql
* sql语句
*/
void selectDataBase(String sql) {
this.GetConn();
try {
this.prepared = this.conn.prepareStatement(sql);
this.rs = this.prepared.executeQuery();
} catch (SQLException e) {
System.out.println("查询数据库时出错!");
e.printStackTrace();
}
}
}
我使用的类属性ResultSet存放查询结果当我在子类获取查询数据库时,必须保持连接,获取基类的ResultSet,有没有更优化的方法,使用一个与连接无关的载体,
使我在基类既可关闭连接,在子类只用调用selectDataBase既可返回结果集,比如arrayList
import java.util.*;/**
* 数据访问基类
*
* @author Kong
*/
public class BaseDao {
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=company";
Connection conn =null;
ResultSet rs = null;
PreparedStatement prepared = null; /**
* 获取连接
*/
void GetConn() {
try {
Class.forName(DRIVER);// 注册驱动
this.conn = DriverManager.getConnection(URL, "sa", "");// 获得连接对象
} catch (Exception e) {
System.out.println("连接出错");
}
} /**
* 关闭连接,用于关闭连接通道
*/
void closeAll() {
try {
if (this.rs != null)
this.rs.close();
if (this.prepared != null)
this.prepared.close();
if (this.conn != null)
this.conn.close();
} catch (SQLException e) {
System.out.println("关闭连接时出错");
}
}
/**
* 查询数据库,
*
* @param sql
* sql语句
*/
void selectDataBase(String sql) {
this.GetConn();
try {
this.prepared = this.conn.prepareStatement(sql);
this.rs = this.prepared.executeQuery();
} catch (SQLException e) {
System.out.println("查询数据库时出错!");
e.printStackTrace();
}
}
}
我使用的类属性ResultSet存放查询结果当我在子类获取查询数据库时,必须保持连接,获取基类的ResultSet,有没有更优化的方法,使用一个与连接无关的载体,
使我在基类既可关闭连接,在子类只用调用selectDataBase既可返回结果集,比如arrayList
package test;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;public class JdbcUtil {
private static ThreadLocal threadConnection = new ThreadLocal();
private static ThreadLocal threadStatement = new ThreadLocal();
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=company"; public static Connection getMsSqlConnection() {
Connection conn = null;
String url = null;
try {
// 装入数据库驱动类.
Class.forName(DRIVER).newInstance();
String user = "sa";
String password = "";
conn = DriverManager.getConnection(url, user, password);
if (conn == null) {
throw new SQLException("can't connect MsSql");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
} /**
* 得到当前数据库连接
*
* @return
* @throws SQLException
*/
public static Connection getCurrentConnection() throws SQLException {
Connection conn = (Connection) threadConnection.get();
if (conn == null) {
System.out
.println("opening new connection for this threadConnectin");
// conn=getMySqlConnection();
conn = getMsSqlConnection();
threadConnection.set(conn);
}
return conn;
} /**
* 关闭当前Connection
*/
public static void closerCurrentConnection() {
try {
Connection conn = (Connection) threadConnection.get();
threadConnection.set(null);
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 执行sql文
* @param sql
* @return
*/
public ArrayList selectDataBase(String sql) {
ArrayList arryrs = new ArrayList();
try {
PreparedStatement stmt = (PreparedStatement) threadStatement.get();
Connection conn = getCurrentConnection();
stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
.....
//组装arrayList就可以了。
} catch (Exception ex) {
ex.printStackTrace();
} return arryrs;
}
}
现写的,
建议把selectDataBase方法不要放到这个类中,可以再写一个类作为基类。
这个类就是连接数据库,事务管理(自己再扩写)什么的。
public ArrayList selectDataBase(String sql) {
ArrayList arryrs = new ArrayList();
try {
PreparedStatement stmt = (PreparedStatement) threadStatement.get();
Connection conn = getCurrentConnection();
stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
.....
//组装arrayList就可以了。
} catch (Exception ex) {
ex.printStackTrace();
}finnally{
closerCurrentConnection();
} return arryrs;
}