dbmanager.java

java开发购物车+网上书城

package com.xaccp.aj3q8073.db;

	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
import java.sql.Statement;
	import java.util.ArrayList;
	import java.util.List;

import javax.sql.DataSource;


	public class DBManager {
		//java.sql.DataSource包下的
		static DataSource ds;

		public static DataSource getDs() {
			return ds;
		}

		public static void setDs(DataSource ds) {
			DBManager.ds = ds;
		}

		/**
		 * 用一个单独的方法来写连接而不把Connection 设成成员属性？？？
		 * 
		 * @return
		 * @throws SQLException
		 */
		public Connection getConnection() throws SQLException {
//			Connection conn = DriverManager.getConnection(URL, UID, PWD);
			return ds.getConnection();
		}

		/**
		 * 执行查询总条数
		 * @param sql
		 * @return
		 */
		public int  executeQuery(String sql){
			int i=0;
			ResultSet rt=null;
			Connection con=null;
			PreparedStatement pst = null;
			try {
				 con= this.getConnection();
				 pst= con.prepareStatement(sql);
				 rt= pst.executeQuery();
				 if(rt.next()){
					i= rt.getInt(1);
				 }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				this.closePst(pst);
				this.closeCon(con);
				this.closeRes(rt);
			}
			return i;
		}
	
		
		/**
		 * 增删改的方法 返回影响的行数
		 * 
		 * @param sql
		 * @param obj
		 * @return
		 */
		public int executeUpdate(String sql, Object[] obj) {
			Connection con = null;
			PreparedStatement pst = null;
			try {
				con = this.getConnection();
				pst = con.prepareStatement(sql);
				if (obj != null) {
					for (int i = 0; i < obj.length; i++) {
						// 参数设置从第1个开始
						pst.setObject(i + 1, obj[i]);
					}
				}
				int rows = pst.executeUpdate();
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.closeCon(con);
				this.closePst(pst);
			}
			return -1;
		}
		
		/**
		 * 增删改的方法 并且返回当前的自动增长值
		 * 
		 * @param sql
		 * @param obj
		 * @return
		 */
		public int getIdentityByInsert(String sql, Object[] obj) {
			Connection con = null;
			PreparedStatement pst = null;
			try {
				con = this.getConnection();
				pst = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				if (obj != null) {
					for (int i = 0; i < obj.length; i++) {
						// 参数设置从第1个开始
						pst.setObject(i + 1, obj[i]);
					}
				}
				pst.executeUpdate();
				ResultSet rs=pst.getGeneratedKeys();
				rs.next();
				return rs.getInt(1);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.closeCon(con);
				this.closePst(pst);
			}
			return -1;
		}

		/**
		 * 查询结果返回List集合
		 * 
		 * @param sql
		 * @param obj
		 * @param mapper
		 * @return
		 */
		public List queryForList(String sql, Object[] obj, RowMapper mapper) {
			Connection con = null;
			PreparedStatement pst = null;
			ResultSet rt = null;
			try {
				con = this.getConnection();
				pst = con.prepareStatement(sql);
				if (obj != null) {
					for (int i = 0; i < obj.length; i++) {
						pst.setObject(i + 1, obj[i]);
					}
				}
				rt = pst.executeQuery();
				List list = new ArrayList();
				while (rt.next()) {
					list.add(mapper.rowMapper(rt));
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.closeCon(con);
				this.closePst(pst);
				this.closeRes(rt);
			}
			return null;
		}

		/**
		 * 查询结果返回Object对象
		 * 
		 * @param sql
		 * @param obj
		 * @param mapper
		 * @return
		 */
		public Object queryForObject(String sql, Object[] obj, RowMapper mapper) {
			Connection con = null;
			PreparedStatement pst = null;
			ResultSet rt = null;
			Object ob = null;
			try {
				con = this.getConnection();
				pst=con.prepareStatement(sql);
				if (obj != null) {
					for (int i = 0; i < obj.length; i++) {
						pst.setObject(i + 1, obj[i]);
					}
				}
				rt = pst.executeQuery();
				if (rt.next()) {
					return ob = mapper.rowMapper(rt);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.closeCon(con);
				this.closePst(pst);
				this.closeRes(rt);
			}
			return null;
		}

		/**
		 * 关闭Connection连接
		 * 
		 * @param con
		 */
		public void closeCon(Connection con) {
			try {
				if (con != null && !con.isClosed()) {
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		/**
		 * 关闭pst连接
		 * 
		 * @param pst
		 */
		public void closePst(PreparedStatement pst) {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		/**
		 * 关闭
		 * 
		 * @param rt
		 */
		public void closeRes(ResultSet rt) {
			if (rt != null) {
				try {
					rt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}


}