db.java

用于BBS交流的网站

package com.andrew.db;

import com.andrew.elements.Article;
import com.andrew.elements.DigitalSrc;
import com.andrew.elements.FileUpload;
import com.andrew.elements.FindingUser;
import com.andrew.elements.SimpleUser;
import com.andrew.elements.Software;
import com.andrew.elements.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DB {
	private Connection conn;
	
	/**
	 * 注册驱动，并且创建一个数据库链接
	 */
	public DB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/bbs", "root", "860930");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 注册新用户
	 * @param context
	 * @param user 用户信息的封装
	 * @return true 如果注册成功；否则false
	 */
	public boolean doRegister(User user) {
		if (doCheckUserInfo(user)) {
			doAddUser(user);
			close();
			return true;
		} else {
			close();
			return false;
		}
		
	}
	
	public void doAddUser(User user) {
		String sql = "insert into usertlb values(?,?,?,?,?,?)";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, user.getUserName());
			preStatement.setString(2, user.getPassword());
			preStatement.setString(3, user.getEmail());
			preStatement.setString(4, user.getRealname());	
			preStatement.setInt(5, user.getGender());	
			preStatement.setDate(6, user.getBirthday());	
			preStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
	}
	
	/**
	 * 根据sql创建一个预编译句柄
	 * @param sql
	 * @return
	 */
	public PreparedStatement createPreStatment(String sql) {
		PreparedStatement preStatement = null;
		try {
			preStatement = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return preStatement;
	}
	
	/**
	 * 检测用户名是否已经存在
	 * @return false如果已经存在；否则true
	 */
	public boolean doCheckUserInfo(User user) {
		String sql = "select username from usertlb";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			while (rs.next()) {
				if (rs.getString("username").equals(user.getUserName())) {
					return false;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
		}
			return true;		
	}
	
	/**
	 * 创建一个句柄，用于执行sql语句
	 * @return 创建的句柄
	 */
	public Statement getStatement() {
		Statement statement = null;
		try {
			statement = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return statement;
	}
	
	/**
	 * 用指定句柄执行sql语句
	 * @param statement
	 * @param sql
	 * @return 返回执行后的结果集
	 */
	public ResultSet getResultSet(Statement statement, String sql) {
		ResultSet rs = null;
		try {
			rs = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 关闭创建的数据库链接
	 */
	public void close() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
			conn = null;
		}
		
	}
	
	/**
	 * 关闭句柄
	 * @param statement
	 */
	public void close(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			statement = null;
		}
		
	}
	
	/**
	 * 关闭结果集
	 * @param rs
	 */
	public void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		
	}
	
	public void close(PreparedStatement preStatement) {
		if (preStatement != null) {
			try {
				preStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			preStatement = null;
		}
		
	}

	/**
	 * 用于登入检测
	 * @param userName
	 * @param password
	 * @return
	 */
	public boolean doEnterChecking(String username, String password) {
		String sql = "select username,passwd from usertlb";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			while (rs.next()) {
				if (rs.getString("username").equals(username) && rs.getString("passwd").equals(password)) {
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close();
		}
			return false;
		
	}

	/**
	 * 用于找回密码
	 * @param userName
	 * @param sport
	 * @param email 将密码发送至此邮箱
	 * @return
	 */
	public boolean doFindChecking(FindingUser user) {
		String sql = "select username,passwd,realname,birthday from usertlb";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			while (rs.next()) {
				if (rs.getString("username").equals(user.getUserName()) && rs.getString("realname").equals(user.getRealname()) && rs.getDate("birthday").equals(user.getBirthday())) {
					user.setPassword(rs.getString("passwd"));//user的设置密码
					//发送邮件到你的邮箱；；；；；；待做
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
			close();
		}
			return false;
	}

	/**
	 * 更新用户信息
	 * @param user
	 * @return
	 */
	public boolean doChangeUserInfo(User user) {
		if (!doCheckUserInfo(user)) {	//用户存在
			doUpdateUserInfo(user);
			close();
			return true;
		} else {
			close();
			return false;
		}
	}

	private void doUpdateUserInfo(User user) {
		String sql = "update usertlb set passwd=?,realname=?,mail=?,gender=?,birthday=? where username=" + "'" + user.getUserName() + "'";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, user.getPassword());
			preStatement.setString(2, user.getRealname());
			preStatement.setString(3, user.getEmail());
			preStatement.setInt(4, user.getGender());
			preStatement.setDate(5, user.getBirthday());
			preStatement.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
	}
	
	/**
	 * 判断是否将文件信息加入数据库中
	 * @param file
	 * @return 成功加入返回true，否则false
	 */
	public boolean addUploadingFileItem(FileUpload file) { 
		if (isFileExisted(file)) {
			close();
			return false;
		} else {
			doAddFile(file);
			close();
			return true;
		}
	}
	
	/**
	 * 检测用户名是否已经存在
	 * @return true如果已经存在；否则false
	 */
	public boolean isFileExisted(FileUpload file) {
		String sql = "select filename from uploadtlb where username=" + "'" + file.getUsername() + "'";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			while (rs.next()) {
				if (rs.getString("filename").equals(file.getFilename())) {
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
		}
			return false;	
	}
	
	/**
	 * 增加上传文件的信息到数据库uploadtlb
	 * @param file
	 */
	public void doAddFile(FileUpload file) {
		String sql = "insert into uploadtlb values(?,?,?,curdate())";
		PreparedStatement preStatement = createPreStatment(sql);
		try {
			conn.setAutoCommit(false);
			preStatement.setString(1, null);
			preStatement.setString(2, file.getUsername());
			preStatement.setString(3, file.getFilename());	
			preStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			close(preStatement);
		}
	}
	
	public String uploadedFileList(SimpleUser user) {
		String result = "";
		String sql = "select * from uploadtlb where username=" + "'" + user.getUserName() + "'";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			int count = 0;//用于计入节点数目
			while (rs.next()) {
				count ++;
				if (count%2 == 0) {
					result = result + "<tr bgcolor='#999999'>";
				} else {
					result = result + "<tr bgcolor='#666666'>";
				}
				result = result + "<td ><div align='center'>" + rs.getString("username")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getInt("fileID")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("filename")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getDate("uploaddate")+"</div></td>";
				result = result + "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
		}
			return result;	
	}
	//////////////////////////////////////////////
	//////////////////////////////////////////////
	
	/**
	 * 根据类型获得相关资源
	 * @param type
	 * @param digitalType
	 * @return 找到的相关的资源
	 */
	public String getDigitals(String type, String digitalType) {
		String result = "";
		String sql = "select * from digitaltlb where srcType=" + "'" + type + "'" + " and " + "digitalType=" + "'" + digitalType + "'";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			int count = 0;//用于计入节点数目
			while (rs.next()) {
				count ++;
				if (count%2 == 0) {
					result = result + "<tr bgcolor='#999999'>";
				} else {
					result = result + "<tr bgcolor='#666666'>";
				}
				result = result + "<td ><div align='center'>" + rs.getString("digitalID")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("srcType")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("digitalType")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("digitalName")+"</div></td>";
				result = result + "<td ><div align='center'><a href=digital/download.jsp?digitalPath=" + rs.getString("digitalPath")+">下载</a></div></td>";
				result = result + "<td ><div align='center'>" + rs.getInt("digitalSize")+"</div></td>";
				result = result + "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs);
		}
			return result;	
	}
	/**
	 * 获得可以下载的软件列表
	 * @param softwareType
	 * @return
	 */
	public String getSoftwares(String softwareType) {
		String result = "";
		String sql = "select * from downloadtlb where softwareType=" + "'" + softwareType + "'";
		ResultSet rs = getResultSet(getStatement(),sql);
		try {
			int count = 0;//用于计入节点数目
			while (rs.next()) {
				count ++;
				if (count%2 == 0) {
					result = result + "<tr bgcolor='#999999'>";
				} else {
					result = result + "<tr bgcolor='#666666'>";
				}
				result = result + "<td ><div align='center'>" + rs.getString("softwareID")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("softwareType")+"</div></td>";
				result = result + "<td ><div align='center'>" + rs.getString("softwareName")+"</div></td>";
				result = result + "<td ><div align='center'><a href=download/download.jsp?softwarePath=" + rs.getString("softwarePath")+">下载</a></div></td>";
				result = result + "<td ><div align='center'>" + rs.getInt("softwareSize")+"</div></td>";
				result = result + "</tr>";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {