basedao.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.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;


public abstract class Basedao {
	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 Basedao() throws ClassNotFoundException, SQLException {
		this.getConn();
	}

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

	//条件查询（有参数）返回集合
	public List executeQuery(String sql, Object[] args, ResultSetHandler rsh)
			throws SQLException, ClassNotFoundException {
		if (null == conn || conn.isClosed()) {
			this.getConn();
		}
		// 用object接收经过接口转换后的结果集，因为接口是不实现的， 所以需要在dao层写一个类去实现接口中的方法
		QueryRunner runner = new QueryRunner();
		List list = (List) runner.query(conn, sql, args, rsh);
		DbUtils.close(conn);
		return list;
	}
	//全查询 （无参）
	public List executeQuery(String sql, ResultSetHandler rsh)
			throws SQLException, ClassNotFoundException {
		if (null == conn || conn.isClosed()) {
			this.getConn();
		}
		// 用object接收经过接口转换后的结果集，因为接口是不实现的， 所以需要在dao层写一个类去实现接口中的方法
		QueryRunner runner = new QueryRunner();
		List list = (List) runner.query(conn, sql, rsh);
		DbUtils.close(conn);
		return list;
	}
	
	
//	 增删改 有参（且返回的是int类型，或无返回类型  不需要经过接口转换成object）
	public void 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]);
		}
		 ps.executeUpdate();
	
	}
}