javabean servlet jsp(一)

package cn.jbit.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
	// 连接字符串
	private final static String CONNECTIONSTRING = "jdbc:sqlserver://127.0.0.1:1433;databaseName=lib";
	private Connection connection;
	private ResultSet rs;
	private PreparedStatement pstmt;

	// 获取连接
	private  void getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			connection = DriverManager.getConnection(CONNECTIONSTRING, "sa",
					"sa");
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * 获取查询结果集
	 * @param sql
	 *            要查询的sql
	 * @param objs参数列表
	 * @return ResultSet结果集
	 */
	public ResultSet getResultSet(String sql, Object[] objs) {
		try {
			getConnection();
			pstmt = connection.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				pstmt.setObject(i + 1, objs[i]);
			}

			return pstmt.executeQuery();
		} catch (SQLException e) {
			System.out.println(e);
		}
		return null;
	}

	/**
	 * 执行增删改操作
	 * @param sql
	 *            要执行的sql语句
	 * @param objs
	 *            参数列表
	 * @return
	 */
	public int excuteUpdate(String sql, Object[] objs) {
		getConnection();
		try {
			pstmt = connection.prepareStatement(sql);
			for (int i = 0; i < objs.length; i++) {
				pstmt.setObject(i + 1, objs[i]);// 参数设置从1开始
			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			return -1;
		}
	}

	// 释放连接
	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				pstmt.close();
			}
		} catch (SQLException e) {
			System.out.println(e);
		}
	}

}

package cn.jbit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import cn.jbit.entity.BookInfo;
import cn.jbit.util.Page;

import com.sun.org.apache.regexp.internal.recompile;


public class BookInfoDao extends BaseDao   {
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 分页查询 列表信息
	 * 
	 * @param page
	 *            分页信息
	 * @param bookName
	 *            条件
	 * @return 结果集
	 */
	public List
  
    getListByList(Page page, String bookName) {
		try {
			StringBuffer sb = new StringBuffer();
			List parm = new ArrayList();// 参数集合
			// 构建查询语句
			sb.append("	select * from (	select *,ROW_NUMBER() over (order by bid) as r from Book where 1=1 ");
			if (bookName != null && !bookName.isEmpty()) {
				sb.append(" and bName like   ");
				parm.add("%" + bookName + "%");
			}

			sb.append(")	 as t ");
			// 查询总条数
			ResultSet rs = getResultSet(
					"select count(1) from  (" + sb.toString() + ") as tt",
					parm.toArray());
			if (rs.next()) {
				int count = rs.getInt(1);
				if (count < 1) {
					return null;
				}
				page.setCount(count);
			} else {
				return null;
			}
			close();//释放资源
			sb.append("  where t.r>





  and t.r
    list = new ArrayList
   
    (); BookInfo book; while (rs.next()) { // 使用构造函数进行赋值 book = new BookInfo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5),rs.getString(6)); list.add(book);// 添加到集合 } close();// 释放资源 return list;// 返回数据 } } catch (SQLException e) { System.out.println(e); } return null; } /** * 通过id查询 * * @param id * id * @return 查询到的结果 */ public BookInfo getByid(String id) { String sql = "select * from book where bid=  "; ResultSet rs = getResultSet(sql, new Object[] { id }); BookInfo book = null; try { if (rs.next()) { book = new BookInfo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5),rs.getString(6)); } close(); return book; } catch (SQLException e) { } return null; } /** * 保存 * * @param book */ public void save(BookInfo book) { String sql = "INSERt INTO BOOK VALUES( , , , , )"; int count = excuteUpdate( sql, new Object[] {book.getName(),book.getPrice(),book.getCategory(),book.getDate(),book.getLoan() });// 设置参数 } /** * 更新 * * @param book */ public void update(BookInfo book) { String sql = "update BOOK set bName=  ,bprice= ,bcategory=  ,bdate=  ,bloan=  where bid= "; int count = excuteUpdate( sql, new Object[] {book.getName(),book.getPrice(),book.getCategory(),book.getDate(),book.getLoan(),book.getId() });// 设置参数 } // 保存或者更新 public void saveorUpdate(BookInfo book) { if(boo