dbutil.java

java swing 和sql server 数据库做的 图书管理系统

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bookmanager.bean;
import java.sql.Connection;
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 org.apache.commons.dbcp.BasicDataSource;
/**
 *��ݿ����
 * @author hjl
 * @since 2008-05-13
 */
public class DbUtil{ 
    private static BasicDataSource dataSource =null;
    private static Statement stmt=null;
    private static PreparedStatement preparedStat=null;
    private static Connection conn=null;
    public static BasicDataSource init() {   
        dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        dataSource.setUsername("sa");
        dataSource.setPassword("3671241");
        dataSource.setUrl("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=BookManager");
        return dataSource;
    }   
    public static synchronized Connection getConnection() throws  SQLException {   
        if(dataSource==null)
          dataSource =init();
        conn=null;
        if (dataSource != null) {   
            conn= dataSource.getConnection();   
        }   
        return conn;   
    }   
    /**
     * ִ�з��ؽ��.
     * @param sql
     * @return
     */
    public static synchronized ResultSet executeQuery(String sql){
		ResultSet tmp = null;
                try{
          conn=DbUtil.getConnection();
		  stmt=conn.createStatement();
			if (stmt != null) {
				tmp = stmt.executeQuery(sql);
				return tmp;
			} else {
				return null;
			}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}   
   /**
     * ִ�з��ؽ��.
     * @param sql
     * @return
     */
    public static synchronized ResultSet executeQuery(String sql,Object[] obj){
		ResultSet tmp = null;
       try{
          conn=DbUtil.getConnection();                	
		  preparedStat=conn.prepareStatement(sql);
		  if (preparedStat != null) {
                    for(int i=1;i<=obj.length;i++)
                      preparedStat.setObject(i+1,obj[i]);
		    tmp = stmt.executeQuery(sql);
		    return tmp;
		 } else {
		    return null;
		}
		}catch(SQLException e){
			return null;
		}
	} 
     /**
      * ִ�е�����ֵ(�磺ִ��count(*))
      * @param sql
      * @return
      */    
    public static synchronized int executeCount(String sql){
		int nCount = 0;
		try {
			conn=DbUtil.getConnection();  
                    stmt=conn.createStatement();
			if( stmt != null ) {
				ResultSet tmp = null;
				tmp = stmt.executeQuery(sql);
				if( tmp!=null && tmp.next() ) {
					nCount = tmp.getInt(1);
				}else {
					nCount = 0;
				}
			}else {
				nCount = 0;
			}
		}catch(SQLException e) {
			nCount = 0;
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return nCount;
	}       
   /**
     * ִ�з��ؽ��.
     * @param sql
     * @return
     */
     public static synchronized int executeUpdate( String sql ){
		int result = 0;
                try {
                	conn=DbUtil.getConnection(); 
		stmt=conn.createStatement();		
		
			if( stmt != null ) {
				result = stmt.executeUpdate(sql);
			}else {
                           return 0;
			}
		}catch(SQLException e) {
                   return 0;
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
                return result;
	}
   /**
     * ִ�з��ؽ��.
     * @param sql
     * @return
     */
     public static synchronized int executeUpdate( String sql,Object[] obj){
		int result = 0;
        try {
        	conn=DbUtil.getConnection(); 
            preparedStat = conn.prepareStatement(sql);
            
		  if (preparedStat != null) {
                    for(int i=0;i<obj.length;i++)
                      preparedStat.setObject(i+1,obj[i]);
                      result = preparedStat.executeUpdate();
                return result;

                 }else
                     return 0;            
        } catch (SQLException ex) {
            Logger.getLogger(DbUtil.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
        	try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        return 0;
     }
     public static synchronized void closeConnection(){
    	 if(conn!=null)
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
     }
                  
}