Android电子书项目实训【服务器JDBC部分】【2】

1、项目结构

服务器端的项目结构，action包存放Struts2的 action；dao包存放 各实体类的数据库访问类；entity存放实体类；util包存放工具类。

2、实体类：

实体类根据 数据库表创建。【自动生成也可以】

账户类：

package edu.freshen.entity;

import java.util.Date;

public class AccountInfo {
	private int id;
	private String loginName;
	private String loginPwd;
	private int level;
	private Date lastLoginDate;
	private String lastLloginIP;
	private int score;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getLoginName() {
		return loginName;
	}
	public void setLoginName(String loginName) {
		this.loginName = loginName;
	}
	public String getLoginPwd() {
		return loginPwd;
	}
	public void setLoginPwd(String loginPwd) {
		this.loginPwd = loginPwd;
	}
	public int getLevel() {
		return level;
	}
	public void setLevel(int level) {
		this.level = level;
	}
	public Date getLastLoginDate() {
		return lastLoginDate;
	}
	public void setLastLoginDate(Date lastLoginDate) {
		this.lastLoginDate = lastLoginDate;
	}
	public String getLastLloginIP() {
		return lastLloginIP;
	}
	public void setLastLloginIP(String lastLloginIP) {
		this.lastLloginIP = lastLloginIP;
	}
	public int getScore() {
		return score;
	}
	public void setScore(int score) {
		this.score = score;
	}
	@Override
	public String toString() {
		return "AccountInfo [id=" + id + ", loginName=" + loginName
				+ ", loginPwd=" + loginPwd + ", level=" + level
				+ ", lastLoginDate=" + lastLoginDate + ", lastLloginIP="
				+ lastLloginIP + ", score=" + score + "]";
	}
	
}

其他实体类，可以参考 数据库表。

3、DAO类

访问数据库的类。

帮助类的写法：

public class DaoHelper {
	private static Connection conn;
	/**
	 * 获取与数据库 的连接
	 * @return
	 */
	public static Connection  getConn(){
		try {
			if(conn!=null&& !conn.isClosed())return conn;
			//1、加载数据库驱动
			Class.forName("com.mysql.jdbc.Driver");
			//2、获取连接
			conn=DriverManager.getConnection("jdbc:mysql://localhost/db_ebooks", "用户名", "密码");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void close(ResultSet rs, PreparedStatement pstm,Statement stm,Connection conn){
		try {
			if(rs!=null) rs.close();
			if(pstm!=null)pstm.close();
			if(stm!=null)stm.close();
			if(conn!=null){
				conn.close();
				conn=null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
}

账户对象DAO类：

public class AccountInfoDao {
	/**
	 * 登录系统时 ，进行用户名和密码的验证
	 * @param ln
	 * @param lp
	 * @return
	 */
	public AccountInfo queryAccountByLogin(String ln,String lp){
		String sql ="select * from  tb_accountInfo where loginName=? and loginPwd=?";
		Connection conn=DaoHelper.getConn();
		PreparedStatement pstm=null;
		ResultSet rs=null;
		AccountInfo ai=null;
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, ln);
			pstm.setString(2, lp);
			rs=pstm.executeQuery();
			if(rs.next()){
				ai=new AccountInfo();
				ai.setId(rs.getInt("id"));
				ai.setLastLloginIP(rs.getString("lastLoginIP"));
				ai.setLastLoginDate(rs.getDate("lastLoginDate"));
				ai.setLevel(rs.getInt("level"));
				ai.setLoginName(rs.getString("loginName"));
				ai.setLoginPwd(rs.getString("loginPwd"));
				ai.setScore(rs.getInt("score"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DaoHelper.close(rs, pstm, null, conn);
		}
		return ai;
	}
	/**
	 * 新注册账号，插入数据库
	 * @param ai
	 * @return
	 */
	public int insertAccount(AccountInfo ai){
		String  sql="insert into tb_accountinfo(loginName,loginPwd,level,score,lastLoginIP,lastLoginDate)values(?,?,?,?,?,?)";
		Connection conn =DaoHelper.getConn();
		PreparedStatement pstm=null;
		int r=0;
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, ai.getLoginName());
			pstm.setString(2, ai.getLoginPwd());
			pstm.setInt(3, ai.getLevel());
			pstm.setInt(4, ai.getScore());
			pstm.setString(5, ai.getLastLloginIP());
			pstm.setString(6, DateUtil.getDateStr(ai.getLastLoginDate()));
			r=pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DaoHelper.close(null, pstm, null, conn);
		}
		return r;
	}
	/**
	 * 更新账号信息
	 * @param ai
	 * @return
	 */
	public int updateAccount(AccountInfo ai){
		String  sql="update tb_accountinfo set loginName=?,loginPwd=?,level=?," +
				" score=?,lastLoginIP=?,lastLoginDate=? where id=?";
		Connection conn =DaoHelper.getConn();
		PreparedStatement pstm=null;
		int r=0;
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, ai.getLoginName());
			pstm.setString(2, ai.getLoginPwd());
			pstm.setInt(3, ai.getLevel());
			pstm.setInt(4, ai.getScore());
			pstm.setString(5, ai.getLastLloginIP());
			pstm.setString(6, DateUtil.getDateStr(ai.getLastLoginDate()));
			pstm.setInt(7, ai.getId());
			r=pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DaoHelper.close(null, pstm, null, conn);
		}
		return r;
	}
	/**
	 * 检测用户名是否存在 
	 * @param ln  需要检测的用户名
	 * @return  1：表示用户已被注册，  0：表示用户名未被注册
	 */
	public int checkLoginName(String ln){
		int r=0;
		String sql="select * from tb_accountInfo where loginName=?";
		Connection conn=DaoHelper.getConn();
		PreparedStatement pstm=null;
		ResultSet rs =null;
		try {
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, ln);
			rs = pstm.executeQuery();
			if(rs.next())
				r=1;
			else 
				r=0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DaoHelper.close(rs, pstm, null, conn);
		}
		return r;
	}
	
}

其他类，大同小异。