datasource.java

leaveBaord mvc jsp+javabean+servlet

package com.db;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.*;
import java.util.Hashtable;
import java.util.ArrayList;

public class DataSource {
    private static Connection con;
    private static Statement sta;
    private static ResultSet rst;
    /**
     * 获得连接 工厂
     * @return Connection
     */
    private static Connection getConnection(){
        try {
            if(con==null){
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
                con = DriverManager.getConnection("jdbc:odbc:LeaveBoard", "sa","");
                System.out.println("=============连接数据库成功！");
            }
        } catch (Exception ex) {
            //System.out.println("=============连接数据库出错！");
            ex.printStackTrace();
        }
        return con;
    }
    private DataSource(){
    }
    /**
     * 执行查询 结果放入集合类中
     */
    public static ArrayList queryData(String sql){
        System.out.println(">>>>>>>>>>>>>=============="+sql);
        con = getConnection();
        int cols = 0;
        ArrayList list = new ArrayList();
        ResultSetMetaData md ;
        try {
            if(sta==null)sta = con.createStatement();
            rst = sta.executeQuery(sql);//执行查询 获得结果集
            md = rst.getMetaData();
            cols = md.getColumnCount();//获得列数

            while(rst.next()){
                Hashtable hs = new Hashtable();
                for(int i=1;i<=cols;i++){//索引号从1开始
                    hs.put(md.getColumnName(i),""+rst.getString(i));
                }
                list.add(hs);//放入记录
            }
            return list;//返回列表记录
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }finally{
            close(null,sta,rst);
        }
    }
    /**
     * 执行修改操作方法
     */
    public static int update(String sql){
        System.out.println(">>>>>>>>>>>>>=============="+sql);
        con = getConnection();
        int rows = 0;
        try {
            if(sta==null)sta = con.createStatement();
            rows = sta.executeUpdate(sql);//执行
        } catch (SQLException ex) {
           // ex.printStackTrace();
            System.out.println("==============数据更新失败！");
        }finally{
             close(null,sta,rst);
        }
        return rows;
    }
    /**
     * 关闭连接 释放资源
     */
    public static void close(Connection con,Statement sta,ResultSet rst){
        try {
            if (rst != null){
                DataSource.rst.close();
                DataSource.rst=null;
            }
            if (sta != null){
                DataSource.sta.close();
                DataSource.sta=null;
            }
            if (con != null){
                DataSource.con.close();
                DataSource.con=null;
            }
            }catch (Exception ex) {
                System.out.println("=======关闭连接失败！");
            }
    }
}