package com.model.javabean;

import java.sql.*;

public class DB {
	private String classname="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private String URL="jdbc:sqlserver://localhost:1433;DatabaseName=10.20";
	private String username="sa";
	private String pwd="";
	private Connection con;
	private Statement stm;
	private ResultSet rs;
	
	public DB(){
		try{
			Class.forName(classname);
		}catch(ClassNotFoundException e){
			e.printStackTrace();
			System.out.println("加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)失?。?quot;);
		}
	}
	private void getCon(){
		if(con==null){
			try{
				con=DriverManager.getConnection(URL,username,pwd);
			}catch(SQLException e){
				con=null;
				e.printStackTrace();
				System.out.println("創(chuàng)建數(shù)據(jù)庫(kù)連接失敗！");
			}			
		}
	}
	private void getStm(){
		if(stm==null){
			try {
				getCon();
				stm=con.createStatement();
			} catch (SQLException e) {
				System.out.println("創(chuàng)建Statement對(duì)象失??！");
				e.printStackTrace();
			}			
		}
	}
	private void getStmed(){
		if(stm==null){
			try {
				getCon();
				stm=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE ,ResultSet.CONCUR_READ_ONLY);
			} catch (SQLException e) {
				e.printStackTrace();
				System.out.println("創(chuàng)建用來(lái)生成可滾動(dòng)結(jié)果集的Statement對(duì)象失?。?quot;);
			}		
		}
	}
	public ResultSet Read(String sql){
		if(sql==null)sql="";
		System.out.println("sql:"+sql);
		getStmed();
		ResultSet rs=null;
		try {
			rs = stm.executeQuery(sql);
		} catch (SQLException e) {
			rs=null;
			e.printStackTrace();
            System.out.println("查詢數(shù)據(jù)庫(kù)失敗！");
		}
		return rs;
	}
	
	public int Delete(String sql){
		if(sql==null)sql="";
		System.out.println("sql:"+sql);
		getStmed();
		int i=0;
		try {
		 	i = stm.executeUpdate(sql);
		} catch (SQLException e) {
			i=0;
			e.printStackTrace();
            System.out.println("刪除數(shù)據(jù)失??！");
		}
		return i;
	}
	
	public int CreateOrUpdate(String sql){
		if(sql==null)sql="";
		System.out.println("sql:"+sql);
		getStmed();
		int i=0;
		try {
			i = stm.executeUpdate(sql);
		} catch (SQLException e) {
			i=0;
			e.printStackTrace();
            System.out.println("插入或更新數(shù)據(jù)失?。?quot;);
		}
		return i;
	}
	public void closed(){
		try{
			if(rs!=null){
			    rs.close();
		    }
		    if(stm!=null){
			    stm.close();
		    }
		    if(con!=null){
			    con.close();
		    }
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("關(guān)閉數(shù)據(jù)庫(kù)失??！");
		}
	}
}