JDBC

/**
 * vo类是程序中临时用的数据载体
 * @author PC
 *
 */
public class Page<T> {
	
	private int currentPage;//当前页
	private int pageSize;//每一页多少条数据
	private int preViousPage;//上一页
	private int nextPage;//下一页
	private int totalCount;//总记录数
	private int totalPage;//总页数
	
	private List<T> data;//当前页的数据
	
	private String[] keywords;//模糊的关键字

	public int getCurrentPage() {
		return currentPage;
	}

	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getPreViousPage() {
		return preViousPage;
	}

	public void setPreViousPage(int preViousPage) {
		this.preViousPage = preViousPage;
	}

	public int getNextPage() {
		return nextPage;
	}

	public void setNextPage(int nextPage) {
		this.nextPage = nextPage;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}

	public int getTotalPage() {
		return totalPage;
	}

	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}

	public List<T> getData() {
		return data;
	}

	public void setData(List<T> data) {
		this.data = data;
	}

	public String[] getKeywords() {
		return keywords;
	}

	public void setKeywords(String[] keywords) {
		this.keywords = keywords;
	}

	@Override
	public String toString() {
		return "Page [currentPage=" + currentPage + ", pageSize=" + pageSize + ", preViousPage=" + preViousPage
				+ ", nextPage=" + nextPage + ", totalCount=" + totalCount + ", totalPage=" + totalPage + ", data="
				+ data + ", keywords=" + Arrays.toString(keywords) + "]";
	}
}

//先得到一共有多少页
public Integer getCount2(Page<User> page) {
		Integer count=0;
		try {
			Connection con = CommonDao.getConnection();
			String sql = "select count(id) geshu from t_user " + "where username like ? and address like ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + page.getKeywords()[0] + "%");
			pstmt.setString(2, "%" + page.getKeywords()[1] + "%");
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("geshu");
			}
			CommonDao.closeAll(con, pstmt, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
    //查找得到数据
	@Override
	public List<User> getUsersByPage2(Page<User> page) {
		List<User> users=new ArrayList<User>();
		try {
			Connection con = CommonDao.getConnection();
			String sql = "select id,username uname,userpassword upwd,age,address from t_user "
					+ "where username like ? and address like ? " + "limit ?,?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + page.getKeywords()[0] + "%");
			pstmt.setString(2, "%" + page.getKeywords()[1] + "%");
			pstmt.setInt(3, (page.getCurrentPage() - 1) * page.getPageSize());
			pstmt.setInt(4, page.getPageSize());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("uname"));
				user.setPassword(rs.getString("upwd"));
				user.setAge(rs.getInt("age"));
				user.setAddress(rs.getString("address"));
				users.add(user);
			}
			CommonDao.closeAll(con, pstmt, rs);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return users;
	}

public void testFindUsersByPage2(){
		
		UserDao userDao=new UserDaoMySql();
		Page page=new Page();
		page.setCurrentPage(1);//设置当前页为第一页
		page.setPageSize(3);//每页三个数据
		
		page.setKeywords(new String[]{"a","ch"});
		//查询数据库,获取总记录数
		int totalCount=userDao.getCount2(page);
		page.setTotalCount(totalCount);
		//计算总页数
		int totalPage=(totalCount%page.getPageSize()==0)? (totalCount/page.getPageSize()):(totalCount/page.getPageSize()+1);
		page.setTotalPage(totalPage);
		
		//计算前一页
		if(page.getCurrentPage()==1){
			page.setPreViousPage(1);
		}else{
			page.setPreViousPage(page.getCurrentPage()-1);
		}
		//计算后一页
		if(page.getCurrentPage()==totalPage){
			page.setNextPage(totalPage);
		}else{
			page.setNextPage(page.getCurrentPage()+1);
		}
		
		List<User> users=userDao.getUsersByPage2(page);
		page.setData(users);
		//到此为止page中的所有数据都存储完了
		System.out.println(page);
	}

public class CommonDao {
	/**
	 * 获取连接的公共方法
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection()throws Exception{
		Connection con=null;
		Class.forName("com.mysql.jdbc.Driver");
		con=DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/testdb",
				"root",
				"root");
		return con;
	}
	/**
	 * 关闭资源的公共方法
	 * @param con
	 * @throws Exception
	 */
	public static void closeAll(Connection con,PreparedStatement pstmt,ResultSet rs)throws Exception{
		if(con!=null){
			con.close();
		}
		if(pstmt!=null){
			pstmt.close();
		}
		if(rs!=null){
			rs.close();
		}
	}

}

public Integer updateUser(User user) {
		int rowAffect=0;
		   try {
			//1.开连接/获取连接
			Connection con = CommonDao.getConnection();
			//2.构建dml的sql语句
			String sql = "update t_user set username=?,userpassword=?,age=?,address=? where id=?";
			//3.基于连接把dml的SQL语句运送到数据库,构建执行计划  sql语句中有?占位符,给?占位符赋值
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPassword());
			pstmt.setInt(3, user.getAge());
			pstmt.setString(4, user.getAddress());
			pstmt.setInt(5, user.getId());
			
			//4.基于连接和完整的sql语句,调用executeUpdate()方法执行sql语句
			rowAffect = pstmt.executeUpdate();
			//5.关闭连接和相关资源
			CommonDao.closeAll(con, pstmt,null);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return rowAffect;
	}

public User findUserById(Integer id) {
		User user=null;
		try {
			
			//1.开连接/获取连接
			Connection con = CommonDao.getConnection();
			//2.构建dql的sql语句
			String sql = "select id,username,userpassword,age,address from t_user where id=?";
			//3.基于连接把dql的SQL语句运送到数据库,构建执行计划,sql语句中有?占位符,给?占位符赋值
			PreparedStatement pstmt = con.prepareStatement(sql);
			
			pstmt.setInt(1, id);
			//4.基于连接和完整的sql语句,调用executeQuery()方法执行sql语句
			ResultSet rs = pstmt.executeQuery();
			//5.循环遍历ResultSet中的数据,并把数据转存给缓存List集合
			if (rs.next()) {
				user=new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("username"));
				user.setPassword(rs.getString("userpassword"));
				user.setAge(rs.getInt("age"));
				user.setAddress(rs.getString("address"));
			}
			//6.关闭连接和相关资源
			CommonDao.closeAll(con, pstmt, rs);
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		return user;
	}

public interface RowMapper<T> {
	//此方法专门用来做结果集和实体对象的映射关系
	public T mapRow(ResultSet rs)throws SQLException;
}

/**
	 * 策略版本的通用查询
	 * @param rm 处理结果集的策略
	 * @param sql  dql的sql语句
	 * @param params  dql的参数
	 * @return List集合
	 * @throws Exception
	 */
	public static <T> List<T> executeQuery(RowMapper<T> rm,String sql,Object...params)throws Exception{
		List<T> list = new ArrayList<T>();
		Connection con=getConnection();
		PreparedStatement pstmt=con.prepareStatement(sql);
		if(params!=null){
			for(int i=0;i<params.length;i++){
				pstmt.setObject(i+1, params[i]);
			}
		}
		ResultSet rs=pstmt.executeQuery();
		while(rs.next()){
			T t=rm.mapRow(rs);//接口的对象.子类重写后的方法,返回值可以是任何定义的类
			list.add(t);
		}
		closeAll(con,pstmt,rs);
		return list;
	}

public User findUserById(Integer id) {
		List<User> list = new ArrayList<User>();

		try {
			String sql = "select id,username,userpassword,age,address from t_user where id=?";
			RowMapper<User> rm = new RowMapper<User>() {

				@Override
				public User mapRow(ResultSet rs) throws SQLException {
					User user = new User();
					user.setName(rs.getString("username"));
					user.setId(rs.getInt("id"));
					user.setPassword(rs.getString("userpassword"));
					user.setAge(rs.getInt("age"));
					user.setAddress(rs.getString("address"));
					return user;
				}

			};
			list = CommonDao.executeQuery(rm, sql, id);//params占位符就是方法的参数id
		} catch (Exception e) {
			// TODO: handle exception
		}
		return list.get(0);//查找的数据只有一条,所以取第一个
	}

 * 反射版本的通用查询
 * 必须要求结果集的列名字跟实体中的setter方法匹配
 * @param clazz 
 * @param sql  dql的sql语句
 * @param params  dql的参数
 * @return List集合
 * @throws Exception
 */
public static <T> List<T> executeQuery(Class<T> clazz,String sql,Object...params)throws Exception{
	List<T> list = new ArrayList<T>();
	Connection con=getConnection();
	PreparedStatement pstmt=con.prepareStatement(sql);
	if(params!=null){
		for(int i=0;i<params.length;i++){
			pstmt.setObject(i+1, params[i]);
		}
	}
	//获取结果集对象
	ResultSet rs=pstmt.executeQuery();
	//获取结果集的元数据(meta data)
	
	ResultSetMetaData rsmd=rs.getMetaData();
	//从结果集的元数据中获取相应的元数据,比如结果集的列名字
	List<String> columnNames=new ArrayList<String>();
	for(int i=0;i<rsmd.getColumnCount();i++){
		columnNames.add(rsmd.getColumnLabel(i+1));
	}
	//select id,username name,userpassword password,age,address from t_user
	//columnNames:[id,name,password,age,address]
	while(rs.next()){
		T t=clazz.newInstance();//实例化实体对象,类比为user
		//利用反射机制给t对象set数据,数据从rs中getObject()
		for(String columnName:columnNames){
			//此for循环,一次循环构建一个setter方法
			//setId setName setPassword setAge setAddress
			String setterName="set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
			Field field = clazz.getDeclaredField(columnName);//反射获取该属性(数据库列名和实体属性名一样)
			Method method=clazz.getDeclaredMethod(setterName, field.getType());//反射得到该方法
			Object value=rs.getObject(columnName);
			
			/*//遍历所有方法,找到对应的方法,循环的方法效率较低
			Method[] methods=clazz.getDeclaredMethods();
			for(Method method:methods){
				if(setterName.equals(method.getName())){
					//执行到这说明能找到实体类中对应的setter方法
					//从rs中当前行指定columnName列的数据
					Object value=rs.getObject(columnName);*/
			
					//处理value是聚合函数的值,因为value在结果集中是Long类型
					if(value instanceof Long){
						Long l=(Long)value;
						value=l.intValue();
					}
					
					//处理Oracle的id字段为number类型,进入到结果集中就变成了BigDecimal
					if(value instanceof BigDecimal){
						BigDecimal bd=(BigDecimal)value;
						value=bd.intValue();
					}
					
					
					//调用setter方法把结果集中的数据存到t对象
					method.invoke(t, value);
				
			
			
		}
		list.add(t);
	}
	closeAll(con,pstmt,rs);
	return list;
}