dbmanager.java

MVC设计+jsp+servlet+sqlserver开发

package com.accp.fan.db;

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

import com.accp.fan.myInterface.MyInterface;

public class DBManager {
	public DBManager() {
		super();
		// TODO Auto-generated constructor stub
	}

	private static Connection conn;

	private static final String DRIVERNAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

	private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=restrant";

	private static final String UID = "sa";

	private static final String PWD = " ";

	// 获得连接的方法
	public Connection getConn() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVERNAME);
		conn = DriverManager.getConnection(URL, UID, PWD);
		return conn;
	}

	/**
	 * 因为显示层需要的查询中，需要返回集合的情况比较多，所以在db层都定义成返回list集合，个别需要的是object类型的，在dao层再进行转换
	 * 
	 * @param sql
	 * @param myInterface
	 *            这里需要实现接口中的方法，将结果集转换为object类型
	 * @return 全查 需要返回集合
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	// 全查询 （无参）
	public List executeQuery(String sql, MyInterface myInterface)
			throws SQLException, ClassNotFoundException {
		if (null == conn || conn.isClosed()) {
			this.getConn();
		}
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List list = new ArrayList();
//		用object接收经过接口转换后的结果集，因为接口是不实现的， 所以需要在dao层写一个类去实现接口中的方法
		Object obj = myInterface.conventForObject(rs);
		list.add(obj);
		return list;
	}

	// 条件查询（有参数）
	public List executeQuery(String sql, Object[] args, MyInterface myInterface)
			throws SQLException, ClassNotFoundException {
		if (null == conn || conn.isClosed()) {
			this.getConn();
		}
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		ResultSet rs = ps.executeQuery();
		List list = new ArrayList();
		Object obj = myInterface.conventForObject(rs);
		list.add(obj);
		return list;

	}

	// 增删改 无参(好像都是有参数的？？)
	public int executeUpdate(String sql) throws SQLException,
			ClassNotFoundException {
		if (null == conn | conn.isClosed()) {
			this.getConn();
		}
		PreparedStatement ps = conn.prepareStatement(sql);
		int x = ps.executeUpdate();
		return x;
	}

	// 增删改 有参（且 返回的是int类型 不需要经过接口转换list或 object）
	public int executeUpdate(String sql, Object args[])
			throws ClassNotFoundException, SQLException {
		if (null == conn || conn.isClosed()) {
			this.getConn();
		}
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		int x = ps.executeUpdate();
		return x;
	}

	// 关闭连接
	public void closeConn() throws SQLException {
		if (conn != null) {
			if (!conn.isClosed()) {
				conn.close();
			}
		}
	}
}