Together项目后台开发03

5.15~5.28，后台进行了数据库的链接。经过多重数据库链接方法的比较和尝试，最终选择使用VO、DAO等进行链接。以下以User库表为例，解释数据库的链接：

一、VO（Value Object）值对象

由一组属性和属性的set和get方法组成，使用Java语言为库表中所有的值写出方法。

以rrr.vo.User.java代码为例:

package ttt.vo;

public class User {
	private int id;

	private String uid;

	private String password;

	private String realName;

	private int gender;

	private String email;

	private String tel;

	private String validateCode;
	
	private int status=0;//激活状态
	
	private int loginNum;

	/**
	 * @return Returns the email.
	 */
	public String getEmail() {
		return email;
	}

	/**
	 * @param email
	 *            The email to set.
	 */
	public void setEmail(String email) {
		this.email = email;
	}

	/**
	 * @return Returns the gender.
	 */
	public int getGender() {
		return gender;
	}

	/**
	 * @param gender
	 *            The gender to set.
	 */
	public void setGender(int gender) {
		this.gender = gender;
	}

	/**
	 * @return Returns the id.
	 */
	public int getId() {
		return id;
	}

	/**
	 * @param id
	 *            The id to set.
	 */
	public void setId(int id) {
		this.id = id;
	}

	
	/**
	 * @return Returns the password.
	 */
	public String getPassword() {
		return password;
	}

	/**
	 * @param password
	 *            The password to set.
	 */
	public void setPassword(String password) {
		this.password = password;
	}

	/**
	 * @return Returns the realName.
	 */
	public String getRealName() {
		return realName;
	}

	/**
	 * @param realName
	 *            The realName to set.
	 */
	public void setRealName(String realName) {
		this.realName = realName;
	}

	/**
	 * @return Returns the tel.
	 */
	public String getTel() {
		return tel;
	}

	/**
	 * @param tel
	 *            The tel to set.
	 */
	public void setTel(String tel) {
		this.tel = tel;
	}

	/**
	 * @return Returns the uid.
	 */
	public String getUid() {
		return uid;
	}

	/**
	 * @param uid
	 *            The uid to set.
	 */
	public void setUid(String uid) {
		this.uid = uid;
	}

	/**
	 * @return Returns the validateCode.
	 */
	public String getValidateCode() {
		return validateCode;
	}

	/**
	 * @param validateCode
	 *            The validateCode to set.
	 */
	public void setValidateCode(String validateCode) {
		this.validateCode = validateCode;
	}

	public int getStatus() {
        return status;
    }
     
    public void setStatus(int status) {
        this.status = status;
    }
	/**
	 * @return Returns the loginNum.
	 */
	public int getLoginNum() {
		return loginNum;
	}

	/**
	 * @param loginNum The loginNum to set.
	 */
	public void setLoginNum(int loginNum) {
		this.loginNum = loginNum;
	}

}

二、DAO（Data Access Object）数据访问对象

用于访问数据库，通常和VO结合使用，DAO中包含了各种数据库的操作方法。通过它的方法,结合VO对数据库进行相关的操作。

以rrr.dao.UserDAO.java代码为例：

/**
 * 
 */
package ttt.dao;


import java.sql.Connection;
import java.util.List;
import java.text.ParseException;


import ttt.vo.Message;
import ttt.vo.User;


/**
 * @author Administrator
 */
public interface UserDAO {
	public void addUser(User user)throws Exception;


	public void updateUser(User user)throws Exception;


	public void deleteUser(String uid)throws Exception;


	public List<User> listAllUser()throws Exception;


	public User getUser(String uid)throws Exception;
	
	public void addMsg(Message msg)throws Exception;


	public void updateMsg(Message msg)throws Exception;


	public void deleteMsg(int msgid)throws Exception;


	public Message getMsg(int msgid) throws Exception;


	public List<Message> listNewMsg(String uid)throws Exception;
	
	public List<Message> listAllMsg(String uid)throws Exception;
	
	public Connection getConnection()throws Exception;
	
	public void setConnection(Connection connection)throws Exception;
	
	public User find(String email) throws ParseException;


}

三、DAOimpl

为了代码的简洁与清晰，我在DAO中写了方法接口并通过implement进行具体的功能实现，包括数据库的列举、数据的增、删、更新等操作。

以rrr.dao.impl.UserDAOImpl.java代码为例：

package ttt.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;


import ttt.dao.UserDAO;
import ttt.util.db.Database;
import ttt.vo.Message;
import ttt.vo.User;


/**
 * @author Administrator
 */
public class UserDAOImpl implements UserDAO {
	private Connection connection;


	public UserDAOImpl() {
		try {
			connection = Database.getConnection();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		}
	}


	/**
	 * @return Returns the connection.
	 */
	public Connection getConnection() {
		return connection;
	}


	/**
	 * @param connection
	 *            The connection to set.
	 */
	public void setConnection(Connection connection) {
		this.connection = connection;
	}


	/*
	 * (non-Javadoc)
	 * 
	 * @see ch2.dao.UserDAO#addUser(ch2.vo.User)
	 */
	public void addUser(User user) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;


		try {
			ps = connection
					.prepareStatement("Insert into tbl_user(u_uid,u_password,u_realName,u_gender,u_email,u_tel) values(?,?,?,?,?,?)");
			ps.setString(1, user.getUid());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getRealName());
			ps.setInt(4, user.getGender());
			ps.setString(5, user.getEmail());
			ps.setString(6, user.getTel());
			ps.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeStatement(ps);
		}
	}


	/*
	 * (non-Javadoc)
	 * 
	 * @see ch2.dao.UserDAO#updateUser(ch2.vo.User)
	 */
	public void updateUser(User user) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;


		try {
			ps = connection
					.prepareStatement("update tbl_user set u_password=?,u_realName=?,u_gender=?,u_email=?,u_tel=?,u_validateCode=?,u_loginNum=? where u_uid=? ");
			ps.setString(1, user.getPassword());
			ps.setString(2, user.getRealName());
			ps.setInt(3, user.getGender());
			ps.setString(4, user.getEmail());
			ps.setString(5, user.getTel());
			ps.setString(6, user.getValidateCode());
			ps.setInt(7, user.getLoginNum());
			ps.setString(8, user.getUid());
			ps.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeStatement(ps);
		}
	}


	/*
	 * (non-Javadoc)
	 * 
	 * @see ch2.dao.UserDAO#deleteUser(java.lang.String)
	 */
	public void deleteUser(String uid) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;


		try {
			ps = connection.prepareStatement("delete from tbl_user where u_uid=?");
			ps.setString(1, uid);
			ps.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeStatement(ps);
		}
	}


	/*
	 */
	public List<User> listAllUser() throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> result = null;


		try {
			ps = connection
					.prepareStatement("select * from tbl_user order by u_uid asc");
			rs = ps.executeQuery();
			User user = null;
			result = new ArrayList<User>();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt("u_id"));
				user.setUid(rs.getString("u_uid"));
				user.setPassword(rs.getString("u_password"));
				user.setRealName(rs.getString("u_realName"));
				user.setGender(rs.getInt("u_gender"));
				user.setEmail(rs.getString("u_email"));
				user.setTel(rs.getString("u_tel"));
				user.setValidateCode(rs.getString("u_validateCode"));
				user.setLoginNum(rs.getInt("u_loginNum"));
				result.add(user);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		return result;
	}


	/*
	 * (non-Javadoc)
	 * 
	 * @see ch2.dao.UserDAO#getUser(java.lang.String)
	 */
	public User getUser(String uid) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement ps = null;
		ResultSet rs = null;
		User user = null;


		try {
			ps = connection.prepareStatement("select * from tbl_user where u_uid=?");
			ps.setString(1, uid);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt("u_id"));
				user.setUid(rs.getString("u_uid"));
				user.setPassword(rs.getString("u_password"));
				user.setRealName(rs.getString("u_realName"));
				user.setGender(rs.getInt("u_gender"));
				user.setEmail(rs.getString("u_email"));
				user.setTel(rs.getString("u_tel"));
				user.setValidateCode(rs.getString("u_validateCode"));
				user.setLoginNum(rs.getInt("u_loginNum"));
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			throw sqle;
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		return user;
	}


	 public  User find(String email) throws ParseException{
		 PreparedStatement ps = null;
		 ResultSet rs = null;
		 User user = null;


		try{
			ps = connection.prepareStatement("find * from tbl_user where u_email=?");
			ps.setString(1, email);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt("u_id"));
				user.setUid(rs.getString("u_uid"));
				user.setPassword(rs.getString("u_password"));
				user.setRealName(rs.getString("u_realName"));
				user.setGender(rs.getInt("u_gender"));
				user.setEmail(rs.getString("u_email"));
				user.setTel(rs.getString("u_tel"));
				user.setValidateCode(rs.getString("u_validateCode"));
				user.setLoginNum(rs.getInt("u_loginNum"));
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			try {
				throw sqle;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} finally {
			closeResultSet(rs);
			closeStatement(ps);
		}
		return user;
	}
	
		public void addMsg(Message msg) throws Exception {
			// TODO Auto-generated method stub
			PreparedStatement ps = null;


			try {
				ps = connection
						.prepareStatement("Insert into tbl_msg(msg_uid,msg_tid,msg_msg,msg_state,msg_time,msg_suid) values(?,?,?,?,?,?)");
				ps.setString(1, msg.getUid());
				ps.setInt(2, msg.getTid());
				ps.setString(3, msg.getMesg());
				ps.setInt(4, msg.getState());
				ps.setTimestamp(5, new Timestamp(msg.getTime().getTime())); 
				ps.setString(6, msg.getSuid());
				ps.executeUpdate();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				throw sqle;
			} finally {
				closeStatement(ps);
			}
		}


		 
		public void updateMsg(Message msg) throws Exception {
			// TODO Auto-generated method stub
			PreparedStatement ps = null;


			try {
				ps = connection
						.prepareStatement("update tbl_msg set msg_uid=?,msg_tid=?,msg_msg=?,msg_state=?,msg_time=?,msg_suid=? where msg_id=? ");
				ps.setString(1, msg.getUid());
				ps.setInt(2, msg.getTid());
				ps.setString(3, msg.getMesg());
				ps.setInt(4, msg.getState());
				ps.setTimestamp(5, new Timestamp(msg.getTime().getTime())); 
				ps.setString(6, msg.getSuid());
				ps.setInt(7, msg.getId());
				ps.executeUpdate();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				throw sqle;
			} finally {
				closeStatement(ps);
			}
		}


		 
		public void deleteMsg(int msgid) throws Exception {
			// TODO Auto-generated method stub
			PreparedStatement ps = null;


			try {
				ps = connection.prepareStatement("delete from tbl_msg where msg_id=?");
				ps.setInt(1, msgid);
				ps.executeUpdate();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				throw sqle;
			} finally {
				closeStatement(ps);
			}
		}


		public Message getMsg(int msgid) throws Exception {
			// TODO Auto-generated method stub
			PreparedStatement ps = null;
			ResultSet rs = null;
			Message msg = null;


			try {
				ps = connection
						.prepareStatement("select * from tbl_msg where msg_id=? ");
				ps.setInt(1, msgid);
				rs = ps.executeQuery();
				 
				if (rs.next()) {
					msg = new Message();
					msg.setId(rs.getInt("msg_id"));
					msg.setUid(rs.getString("msg_uid"));
					msg.setTid(rs.getInt("msg_tid"));
					msg.setMesg(rs.getString("msg_msg"));
					msg.setState(rs.getInt("msg_state"));
					msg.setTime(rs.getTimestamp("msg_time"));
					msg.setSuid(rs.getString("msg_suid"));
				}
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				throw sqle;
			} finally {
				closeResultSet(rs);
				closeStatement(ps);
			}
			return msg;
		}
		
		public List<Message> listNewMsg(String uid) throws Exception {
			// TODO Auto-generated method stub
			PreparedStatement ps = null;
			ResultSet rs = null;
			List<Message> result = null;


			try {
				ps = connection
						.prepareStatement("select * from tbl_msg where msg_uid=? and msg_state>0 order by msg_time desc");
				ps.setString(1, uid);
				rs = ps.executeQuery();
				Message msg = null;
				result = new ArrayList<Message>();
				while (rs.next()) {
					msg = new Message();
					msg.setId(rs.getInt("msg_id"));
					msg.setUid(rs.getString("msg_uid"));
					msg.setTid(rs.getInt("msg_tid"));
					msg.setMesg(rs.getString("msg_msg"));
					msg.setState(rs.getInt("msg_state"));
					msg.setTime(rs.getTimestamp("msg_time"));
					msg.setSuid(rs.getString("msg_suid"));
					result.add(msg);
				}
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				throw sqle;
			} finally {
				closeResultSet(rs);
				closeStatement(ps);
			}
			return result;
		}
 
		public List<Message> listAllMsg(String uid) throws Exception {
			// TODO Auto-generated method stub
			PreparedStatement ps = null;
			ResultSet rs = null;
			List<Message> result = null;


			try {
				ps = connection
						.prepareStatement("select * from tbl_msg where msg_uid=? order by msg_time desc");
				
				ps.setString(1, uid);
				rs = ps.executeQuery();
				Message msg = null;
				result = new ArrayList<Message>();
				while (rs.next()) {
					msg = new Message();
					msg.setId(rs.getInt("msg_id"));
					msg.setUid(rs.getString("msg_uid"));
					msg.setTid(rs.getInt("msg_tid"));
					msg.setMesg(rs.getString("msg_msg"));
					msg.setState(rs.getInt("msg_state"));
					msg.setTime(rs.getTimestamp("msg_time"));
					msg.setSuid(rs.getString("msg_suid"));
					result.add(msg);
				}
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				throw sqle;
			} finally {
				closeResultSet(rs);
				closeStatement(ps);
			}
			return result;
		}


	 
	public static void closeStatement(Statement st) {
		if (st != null) {
			try {
				st.close();
				st = null;
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
	}


	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
	}
}

四、database.java

使用JDBC进行数据库驱动

代码如下：

package ttt.util.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.PropertyResourceBundle;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * @author Administrator
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class Database {
	/**
	 * 数据库访问URL
	 */
	private static String url;

	/**
	 * 数据库驱动
	 */
	private static String driver;

	/**
	 * 数据库访问用户名
	 */
	private static String username;

	/**
	 * 数据库访问口令
	 */
	private static String password;

	/**
	 * 访问类型
	 */
	private static String type;

	/**
	 * 数据源名称
	 */
	private static String datasource;

	/**
	 * 配置文件名称
	 */
	private final static String fileName = "database";

	private static ThreadLocal connection = new ThreadLocal();

	static {
		config();
	}

	private static void config() {
		// 读取系统配置
		PropertyResourceBundle resourceBundle = (PropertyResourceBundle) PropertyResourceBundle
				.getBundle(fileName);
		// 将系统设置赋值给类变量
		Enumeration enu = resourceBundle.getKeys();
		while (enu.hasMoreElements()) {
			String propertyName = enu.nextElement().toString();
			if (propertyName.equals("database.url"))
				url = resourceBundle.getString("database.url");
			if (propertyName.equals("database.driver"))
				driver = resourceBundle.getString("database.driver");
			if (propertyName.equals("database.username"))
				username = resourceBundle.getString("database.username");
			if (propertyName.equals("database.password"))
				password = resourceBundle.getString("database.password");
			if (propertyName.equals("database.type"))
				type = resourceBundle.getString("database.type");
			if (propertyName.equals("database.datasource"))
				datasource = resourceBundle.getString("database.datasource");
		}

	}

	/**
	 * 取得数据库连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public synchronized static java.sql.Connection getConnection()
			throws SQLException {
		Connection con = (Connection) connection.get();
		if (con != null && !con.isClosed()) {
			return con;
		}
		if ("pooled".equalsIgnoreCase(type)) {
			// 从JNDI中取得数据源
			try {
				// 此处对于不同的应用服务器，对env传入不同
				Hashtable env = new Hashtable();
				// 此处对于不同的应用服务器，对env传入不同
				Context ctx = new InitialContext(env); // 从命名系统中获取 DataSource
														// 工厂对象
				DataSource dataSource = (DataSource) ctx.lookup(datasource);
				con = dataSource.getConnection();
				connection.set(con);
				return con;
			} catch (NamingException e) {
				e.printStackTrace();
			}
		} else {
			// 直接使用JDBC驱动连接
			try {
				Class providerClass = Class.forName(driver);
				con = DriverManager.getConnection(url, username, password);
				con.setAutoCommit(false);
				connection.set(con);
				return con;

			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public static void commit() {
		Connection con = (Connection) connection.get();
		try {
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void rollback() {
		Connection con = (Connection) connection.get();
		try {
			con.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public synchronized static void releaseConnection(Connection connection) {

		try {
			if (connection != null && !connection.isClosed())
				connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		connection = null;
	}

	public static void main(String[] args) {

		try {
			Database.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}