[学习笔记]小型论坛功能——连接数据库 [1]

//DB.java

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.bbs.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author he
 */
public class DB {
   
   
    static{
       
        try{
      Class.forName("com.mysql.jdbc.Driver");
        }catch(Exception e){
        e.getStackTrace();
        }
    }
/**
 * public static void getConnection()
 *
 * 由于已经有了连接池DataPool
 * 则可以直接使用getDataPool()获得数据源ds
 * ds.getConnection();由于在建立连接池的时候
 * 采用的是mySql 用户名root，密码root都已经添加过了，所以没有必要再使用
 * getConnection(String url,String name,String password);来获得连接
 *
 */
   
    //获得连接
    public static Connection getConnection(){
            DataSource ds = null;
            Connection conn = null;
            String url="jdbc:mysql://127.0.0.1/bbs";
            String name="root";
            String password="root";
        try {
          //  ds = getDataPool();
           
            conn=DriverManager.getConnection(url,name,password);
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
      
        }
        return conn;
      
    }
   
    /**
 * 建立数据源名字为DataPool
 * 也就是所谓的连接池。
 */
    private static DataSource getDataPool() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/DataPool");
    }
    /**
     * 创建Statement类的对象
     */
   
    public static Statement getStmt(Connection conn){
           Statement stmt = null;
        try {
           stmt = conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return stmt;
   
    }
    /**
     * 查询语句的结果集
     */
    public static ResultSet executeQuery(Statement stmt,String sql){
        ResultSet rs = null;
        try {
             rs = stmt.executeQuery(sql);
        
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
/**
 更新语句的执行
 *
 */
    public static void executeUpdate(Statement stmt,String sql){
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    /**
     * PreparedStatement prepareStatement(String sql,int autoGeneratedKeys)
     *
     * 创建一个默认 PreparedStatement 对象，该对象能获取自动生成的键。
     *
     * 获得可动态执行SQL语句的接口(带有自动生成键的)
     *
    */
    public static PreparedStatement prepareKey(Connection conn,String sql,
            int autoGeneratedKey){
         PreparedStatement pstmt = null;
        try {
          
            pstmt = conn.prepareStatement(sql, autoGeneratedKey);
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pstmt;
    }
    /**
     * 获得PreparedStatement 对象
     */
    public static  PreparedStatement prepareStmt(Connection conn,String sql){
        PreparedStatement pstmt = null;
        try {
          
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pstmt;
       
    }
    /**
     * 设置统一关闭方法
     */
     public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {

            if (rs != null) {
                rs.close();
                rs = null;
            }

            if (stmt != null) {
                stmt.close();
                stmt = null;
            }

            if (conn != null) {
                conn.close();
                conn = null;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
     public static void main(String args[]){
            String sql = "select * from t_user";
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
        try {
         
            conn = DB.getConnection();
            System.out.println("连接成功！");
            stmt = DB.getStmt(conn);
            rs = DB.executeQuery(stmt, sql);
            while (rs.next()) {
              String userName = rs.getString("username");
              System.out.println(userName);
              String userPassword=rs.getString("userpassword");
              System.out.println(userPassword);
             
            }
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
        DB.close(conn,stmt,rs);
        }
        
        
     }

 

}