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 stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 blocke.printStackTrace();}} finally {closeResultSet(rs);closeStatement(ps);}return user;}public void addMsg(Message msg) throws Exception {// TODO Auto-generated method stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 stubPreparedStatement 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 blocke.printStackTrace();}connection = null;}public static void main(String[] args) {try {Database.getConnection();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}