自我总结的数据库连接方法
(以查询全部为例)
一、传统连接
1.类DBConnection ,用于得到Connection 对象
public class DBConnection {
private final String DBDRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver" ;
private final String DBURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=JSP" ;
private final String DBUSER = "sa" ;
private final String DBPASSWORD = "123456" ;
private Connection conn = null ;
public DBConnection()
{
try
{
Class.forName(DBDRIVER) ;
this.conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
}
catch (Exception e)
{
}
}
// 取得数据库连接
public Connection getConnection()
{
return this.conn ;
}
// 关闭数据库连接
public void close()
{
try
{
this.conn.close() ;
}
catch (Exception e)
{
}
}
}
2.查询全部的代码
public List queryAll() throws Exception {
// TODO Auto-generated method stub
List all = new ArrayList() ;
String sql = "SELECT id,title,author,content FROM note" ;
PreparedStatement pstmt = null ;
DBConnection dbc = null ;
dbc = new DBConnection() ;
try
{
pstmt = dbc.getConnection().prepareStatement(sql) ;
ResultSet rs = pstmt.executeQuery() ;
while(rs.next())
{
Note note = new Note() ;
note.setId(rs.getInt(1)) ;
note.setTitle(rs.getString(2)) ;
note.setAuthor(rs.getString(3)) ;
note.setContent(rs.getString(4)) ;
all.add(note) ;
}
rs.close() ;
pstmt.close() ;
}
catch (Exception e)
{
System.out.println(e) ;
throw new Exception("操作中出现错误!!!") ;
}
finally
{
dbc.close() ;
}
return all ;
}
二、采用数据源连接
1.类DBConnection ,用于得到Connection 对象
public class DBConnection {
private Connection conn = null ;
public DBConnection()
{
try
{
Context initContext=new InitialContext();
Context context=(Context)initContext.lookup("java:/comp/env");
DataSource ds=(Context)context.lookup("jdbc/sqlds");
this.conn = ds.getConnection();
}
catch (Exception e)
{
}
}
// 取得数据库连接
public Connection getConnection()
{
return this.conn ;
}
// 关闭数据库连接
public void close()
{
try
{
this.conn.close() ;
}
catch (Exception e)
{
}
}
}
2.查询全部的代码(同“一”中)
三、采用Commons-DbUtils组件(不应用数据源)
public class DBConnection {
private final String DBDRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver" ;
private final String DBURL = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=JSP" ;
private final String DBUSER = "sa" ;
private final String DBPASSWORD = "123456" ;
private Connection conn = null ;
public DBConnection()
{
try
{
DbUtils.loadDriver(DBDRIVER ) ;
this.conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
}
catch (Exception e)
{
}
}
// 取得数据库连接
public Connection getConnection()
{
return this.conn ;
}
}
}
2.查询全部的代码
public List queryAll() throws Exception {
// TODO Auto-generated method stub
List all = new ArrayList() ;
String sql = "SELECT id,title,author,content FROM note" ;
DBConnection dbc = new DBConnection() ;
Connection conn=dbc.getConnection();
QueryRunner qr=new QueryRunner();
all=(List)qr.query(conn,sql,new BeanListHandler(Note.class));
return all ;
}
四、采用Commons-DbUtils组件(应用数据源)
1.类DBDataSource ,用于得到DataSource 对象
public class DBDataSource {
private DataSource ds = null ;
public DBDataSource()
{
Context initContext=new InitialContext();
Context context=(Context)initContext.lookup("java:/comp/env");
ds=(Context)context.lookup("jdbc/sqlds");
}
// 取得数据源
public DataSource getDataSource()
{
return this.ds ;
}
}
2.查询全部的代码
public List queryAll() throws Exception {
// TODO Auto-generated method stub
List all = new ArrayList() ;
String sql = "SELECT id,title,author,content FROM note" ;
DBDataSource dbc = new DBDataSource() ;
DataSource ds=dbc.getDataSource();
QueryRunner qr=new QueryRunner(ds);
all=(List)qr.query(sql,new BeanListHandler(Note.class));
return all ;
}