数据库连接方法总结

自我总结的数据库连接方法

(以查询全部为例)

一、传统连接

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 ;

 }