java web项目操作mysql数据库的数据封装

在eclipse中，新建一个动态web项目

zhuxiongxian

6043人浏览 · 2014-05-02 16:30:45

zhuxiongxian · 2014-05-02 16:30:45 发布

在开发中， 我们常常会用到mysql数据库, 对操作mysql数据库的数据进入封装， 减少不必要代码的编写， 提高程序的逻辑性， 在开发项目时可以达到事关功倍的效果.

1. 在eclipse中， 新建一个动态web项目， File——>New——>Dynamic Web Project

2. 在src右键新建一个工具包util， 形如com.xxx.yyy.util， com表示商业性的， 也可是org(组织)， 或cn(中国)， xxx表示公司或组织或学校的简写，yyy表示项目应用的简写， 然后新建一个类， 名为ConnectionUtil.java

拷贝以下代码到ConnectionUtil.java里：

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;

public class ConnectionUtil {
	
	private static DataSource ds = null;
	
	static{
		try{ 
			Context initCtx = new InitialContext();
			Context envCtx = (Context)initCtx.lookup("java:comp/env");
			ds = (DataSource)envCtx.lookup("jdbc/WroxTC6");
		}
		catch(Exception ex) {
			throw new RuntimeException(ex);  
		}
	}
	
	public static Connection getConnection() throws SQLException {
		Connection conn = ds.getConnection();
		return conn;
	}

	public static void returnConnection(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

在项目的.../WebContent/META-INF/下新建一个context.xml文件， 拷贝以下代码进context.xml里：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<Context>
	<Resource name="jdbc/WroxTC6" auth="Container"
		type="javax.sql.DataSource"
		maxActive="50"
		maxIdle="100"
		maxWait="10000"
		username="root"
		password="admin"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/b2cmall?useUnicode=true&characterEncoding=UTF-8"
	/>
</Context>

其中上面有三处要进入修改， username 填写你的mysql用户名， 默认是root， password 填写你的mysql用户对应的密码， 最后替换b2cmall为你要连接的数据库名称。

除了上面ConnectionUtil.java和context.xml文件外， 还要有连接mysql数据库的驱动包JDBC：mysql-connector-java-5.1.30-bin.jar，其中5.1.30是mysql-connector-java驱动包的版本号，  mysql-connector-java驱动包官方下载

将mysql-connector-java-5.1.30-bin.jar拷贝进.../WebContent/WEB-INF/lib/下即可。(ps:lib文件夹用来存在web项目需要使用的架包， 即类库)

3. ConnectionUtil.java、 context.xml、mysql-connector-java-5.1.30都准备后好， 开始编写DAO层

核心代码：

private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;

try{
	conn = ConnectionUtil.getConnection();
	//对mysql数据库进入操作
	......

	
}catch(Exception e){
	e.printStackTrace();
}finally{
	try{
		if(rs != null)
			rs.close();
		if(pstmt != null)
			pstmt.close();
		if(conn != null)
			conn.close();
	}catch(Exception ex){
		ex.printStackTrace();
	}
}

 下面是一个简单的例子：

MemberDao.java代码(ps:此处暂时不考虑sql注入等问题， 所以暂时不过滤sql不安全字符)：

package com.b2c.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.b2c.common.Member;
import com.b2c.util.ConnectionUtil;

//数据库访问层--会员
public class MemberDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	/**
	 * 注册，注意积分和级别的确定；密码需要通过加密算法处理后保存。
	 * @param member
	 * @return
	 */
	public int add(Member member){
		int val = 0;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "INSERT INTO member(email,nick,password,credit,layerid,rDatetime,lastlogintime,lastloginip) VALUES(?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getEmail());
			pstmt.setString(2, member.getNick());
			pstmt.setString(3, member.getPassword());
			pstmt.setInt(4, member.getCredit());
			pstmt.setInt(5, member.getLayerid());
			pstmt.setString(6, member.getrDatetime());
			pstmt.setString(7, member.getLastLoginTime());
			pstmt.setString(8, member.getLastLoginIp());
			
			val = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return val;
	}
	
	/**
	 * 会员信息修改
	 * @param member
	 * @return
	 */
	public int update(Member member){
		int val = 0;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "UPDATE member SET email=?, nick=?, password=?, credit=?, layerid=?, rDatetime=?, lastlogintime=?, lastloginip=? WHERE id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getEmail());
			pstmt.setString(2, member.getNick());
			pstmt.setString(3, member.getPassword());
			pstmt.setInt(4, member.getCredit());
			pstmt.setInt(5, member.getLayerid());
			pstmt.setString(6, member.getrDatetime());
			pstmt.setString(7, member.getLastLoginTime());
			pstmt.setString(8, member.getLastLoginIp());
			pstmt.setInt(9, member.getId());
		
			val = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return val;
	}
	/**
	 * 根据id删除会员
	 * @param id
	 * @return
	 */
	public int delete(Integer id){
		int val = 0;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "DELETE FROM member WHERE id=" + id;
			pstmt = conn.prepareStatement(sql);
			
			val = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return val;
	}
	/**
	 * 登录，验证用户的合法性。同时注意密码需要通过加密算法处理然后匹配。
	 * @param username
	 * @param pasword
	 * @return
	 */
	public boolean validate(String username, String pasword){
		boolean flag = false;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "SELECT * FROM member WHERE email=? AND password=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, pasword);
			rs = pstmt.executeQuery();
			if(rs.next())
				flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return flag;
	}
	/**
	 * 重置密码，将某个用户的密码重置为新密码
	 * @param username
	 * @param password
	 * @return
	 */
	public int resetPassword(String email, String password){
		int val = 0;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "UPDATE member set password=? WHERE email=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, password);
			pstmt.setString(2, email);
			val = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return val;
	}
	/**
	 * 根据id查询会员
	 * @param id
	 * @return
	 */
	public Member findById(Integer id){
		Member memb = null;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "SELECT * FROM member WHERE id=" + id;
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return memb;
	}
	/**
	 * 根据邮箱email查询会员
	 * @param email
	 * @return
	 */
	public Member findByEmail(String email){
		Member memb = null;
		String sql = "";
		try{
			conn = ConnectionUtil.getConnection();
			sql = "SELECT * FROM member WHERE email=?";	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			
			rs = pstmt.executeQuery();
			if(rs.next()){
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return memb;
	}
	/**
	 * 根据查询条件分布查询会员
	 * @param sqlCause
	 * @param startindex
	 * @param size
	 * @return
	 */
	public List<Member> findList(String sqlCause, int startindex, int size){
		List<Member> list = null;
		Member memb = null;
		String sql = "";
		try{
			list = new ArrayList<Member>();
			conn = ConnectionUtil.getConnection();
			sql = "SELECT * FROM member ";
			if(!sqlCause.equals(""))
				sql += "WHERE " + sqlCause;
			sql += " LIMIT ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, startindex);
			pstmt.setInt(2, size);
			
			rs = pstmt.executeQuery();
			while(rs.next()){
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));
				
				list.add(memb);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return list;
	}
	/**
	 * 查找所有会员
	 * @return
	 */
	public List<Member> findList(){
		List<Member> list = null;
		Member memb = null;
		String sql = "";
		try{
			list = new ArrayList<Member>();
			conn = ConnectionUtil.getConnection();
			sql = "SELECT * FROM member";
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			while(rs.next()){
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));
				
				list.add(memb);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return list;
	}
	/**
	 * 根据查询条件获取记录数
	 * @param sqlCause
	 * @return
	 */
	public int findCount(String sqlCause){
		int count = 0;
		String sqlQuery = "";
		try{
			conn = ConnectionUtil.getConnection();
			sqlQuery = "SELECT count(*) FROM member ";
			if(!sqlCause.equals(""))
				sqlQuery += "WHERE " + sqlCause;
			pstmt = conn.prepareStatement(sqlQuery);
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getInt(1);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null)
					rs.close();
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
					conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
		return count;
	}
}

4.  本人打包了struts2所需架包及上面的提及到的ConnectionUtil.java、 context.xml、mysql-connector-java-5.1.30， 点击下载

5. 以上的代码还不能更好地体验数据的封装， 比如上面不断重复以下代码：

try{
	if(rs != null)
		rs.close();
	if(pstmt != null)
		pstmt.close();
	if(conn != null)
		conn.close();
}catch(Exception ex){
	ex.printStackTrace();
}

对于重复的代码， 我们应该合并为一个方法即可。下面在此进行进一步优化...结构如下：

上面DBConnectionUtil.java和db.properties两个文件是关键， db.properties封装mysql的用户名、密码、jdbc驱动包、数据库源，文件后缀名一定要为.properties.

db.properties代码内容如下：

user=root
url=jdbc\:mysql\://localhost\:3306/b2cmall?useUnicode=true&characterEncoding=UTF-8
password=admin
driver=com.mysql.jdbc.Driver

DBConnectionUtil.java封装了连接mysql数据库的驱动管理实例， 代码如下：

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class DBConnectionUtil {
	private static String user;
	private static String password;
	private static String url;
	private static String driver;

	static {
		try {
			ClassLoader classLoader = DBConnectionUtil.class.getClassLoader();
			InputStream is = classLoader.getResourceAsStream("config/props/db.properties");
			Properties props = new Properties();
			props.load(is);
			url = props.getProperty("url");
			user = props.getProperty("user");
			password = props.getProperty("password");
			driver = props.getProperty("driver");
			// 注册驱动
			Class.forName(driver);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("找不到驱动");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("加载properties文件错误");
		}
	}

	/**
	 * 获取连接
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 关闭连接
	 * @param conn
	 * @param pstmt
	 * @param rs
	 * @throws Exception
	 */
	public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs) throws Exception {
		if (rs != null)
			rs.close();
		if (pstmt != null)
			pstmt.close();
		if (conn != null)
			conn.close();
	}
}

将上面的DBConnectionUtil.java、db.properties、和mysql-connector-java-5.1.30-bin.jar驱动包放置到各自相应的位置即可， 使用示例如下：

MemberDao.java代码内容(增强版)：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gditc.b2cmall.common.Member;
import com.gditc.b2cmall.util.DBConnectionUtil;

//数据库访问层--会员
public class MemberDao {

	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

	/**
	 * 注册，注意积分和级别的确定；密码需要通过加密算法处理后保存。
	 * @param member
	 * @return
	 * @throws Exception 
	 */
	public int add(Member member) throws Exception{
		int val = 0;
		String sql = "";
		try{
			conn = DBConnectionUtil.getConnection();
			sql = "INSERT INTO member(email,nick,password,credit,layerid,rDatetime,lastlogintime,lastloginip) VALUES(?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getEmail());
			pstmt.setString(2, member.getNick());
			pstmt.setString(3, member.getPassword());
			pstmt.setInt(4, member.getCredit());
			pstmt.setInt(5, member.getLayerid());
			pstmt.setString(6, member.getrDatetime());
			pstmt.setString(7, member.getLastLoginTime());
			pstmt.setString(8, member.getLastLoginIp());

			val = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return val;
	}

	/**
	 * 会员信息修改
	 * @param member
	 * @return
	 * @throws Exception 
	 */
	public int update(Member member) throws Exception {
		int val = 0;
		String sql = "";
		try {
			conn = DBConnectionUtil.getConnection();
			sql = "UPDATE member SET email=?, nick=?, password=?, credit=?, layerid=?, rDatetime=?, lastlogintime=?, lastloginip=? WHERE id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getEmail());
			pstmt.setString(2, member.getNick());
			pstmt.setString(3, member.getPassword());
			pstmt.setInt(4, member.getCredit());
			pstmt.setInt(5, member.getLayerid());
			pstmt.setString(6, member.getrDatetime());
			pstmt.setString(7, member.getLastLoginTime());
			pstmt.setString(8, member.getLastLoginIp());
			pstmt.setInt(9, member.getId());

			val = pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return val;
	}
	/**
	 * 根据id删除会员
	 * @param id
	 * @return
	 * @throws Exception 
	 */
	public int delete(Integer id) throws Exception {
		int val = 0;
		String sql = "";
		try {
			conn = DBConnectionUtil.getConnection();
			sql = "DELETE FROM member WHERE id=" + id;
			pstmt = conn.prepareStatement(sql);

			val = pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return val;
	}
	/**
	 * 登录，验证用户的合法性。同时注意密码需要通过加密算法处理然后匹配。
	 * @param username
	 * @param pasword
	 * @return
	 * @throws Exception 
	 */
	public boolean validate(String username, String pasword) throws Exception {
		boolean flag = false;
		String sql = "";
		try {
			conn = DBConnectionUtil.getConnection();
			sql = "SELECT * FROM member WHERE email=? AND password=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, pasword);
			rs = pstmt.executeQuery();
			if(rs.next())
				flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return flag;
	}
	/**
	 * 重置密码，将某个用户的密码重置为新密码
	 * @param username
	 * @param password
	 * @return
	 * @throws Exception 
	 */
	public int resetPassword(String email, String password) throws Exception {
		int val = 0;
		String sql = "";
		try {
			conn = DBConnectionUtil.getConnection();
			sql = "UPDATE member set password=? WHERE email=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, password);
			pstmt.setString(2, email);
			val = pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return val;
	}
	/**
	 * 根据id查询会员
	 * @param id
	 * @return
	 * @throws Exception  
	 */
	public Member findById(Integer id) throws Exception {
		Member memb = null;
		String sql = "";
		try{
			conn = DBConnectionUtil.getConnection();
			sql = "SELECT * FROM member WHERE id=" + id;
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
			if(rs.next()){
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return memb;
	}
	/**
	 * 根据邮箱email查询会员
	 * @param email
	 * @return
	 * @throws Exception  
	 */
	public Member findByEmail(String email) throws Exception {
		Member memb = null;
		String sql = "";
		try {
			conn = DBConnectionUtil.getConnection();
			sql = "SELECT * FROM member WHERE email=?";	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);

			rs = pstmt.executeQuery();
			if(rs.next()){
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);	
		}
		return memb;
	}
	/**
	 * 根据查询条件分布查询会员
	 * @param sqlCause
	 * @param startindex
	 * @param size
	 * @return
	 * @throws Exception 
	 */
	public List<Member> findList(String sqlCause, int startindex, int size) throws Exception {
		List<Member> list = null;
		Member memb = null;
		String sql = "";
		try {
			list = new ArrayList<Member>();
			conn = DBConnectionUtil.getConnection();
			sql = "SELECT * FROM member ";
			if(!sqlCause.equals(""))
				sql += "WHERE " + sqlCause;
			sql += " LIMIT ?,?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, startindex);
			pstmt.setInt(2, size);

			rs = pstmt.executeQuery();
			while(rs.next()) {
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));

				list.add(memb);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 查找所有会员
	 * @return
	 * @throws Exception 
	 */
	public List<Member> findList() throws Exception {
		List<Member> list = null;
		Member memb = null;
		String sql = "";
		try{
			list = new ArrayList<Member>();
			conn = DBConnectionUtil.getConnection();
			sql = "SELECT * FROM member";
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
			while(rs.next()) {
				memb = new Member();
				memb.setId(rs.getInt("id"));
				memb.setEmail(rs.getString("email"));
				memb.setNick(rs.getString("nick"));
				memb.setPassword(rs.getString("password"));
				memb.setCredit(rs.getInt("credit"));
				memb.setLayerid(rs.getInt("layerid"));
				memb.setrDatetime(rs.getString("rDatetime"));
				memb.setLastLoginTime(rs.getString("lastlogintime"));
				memb.setLastLoginIp(rs.getString("lastloginip"));

				list.add(memb);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 根据查询条件获取记录数
	 * @param sqlCause
	 * @return
	 * @throws Exception 
	 */
	public int findCount(String sqlCause) throws Exception {
		int count = 0;
		String sqlQuery = "";
		try {
			conn = DBConnectionUtil.getConnection();
			sqlQuery = "SELECT count(*) FROM member ";
			if(!sqlCause.equals(""))
				sqlQuery += "WHERE " + sqlCause;
			pstmt = conn.prepareStatement(sqlQuery);
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionUtil.close(conn, pstmt, rs);
		}
		return count;
	}
}

至此， 我们减少了更多重复代码的编写，逻辑性也更加良好， 更好地体验到数据封装的理念...

6. OK. Enjoy it!!!

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

全家桶集齐！Qwen3.5四款小模型上线魔乐社区，附昇腾全套实践教程

魔乐社区

Pont - 搭建前后端之桥：高效、灵活的接口管理工具

Pont 是一款强大的数据服务层解决方案，它能够帮助开发者快速搭建前后端之间的桥梁，实现接口的高效管理和代码自动生成。无论是新手还是有经验的开发者，都能通过 Pont 轻松处理接口文档、生成类型安全的 API 代码，从而显著提升开发效率。[![Pont 工具标志](https://raw.gitcode.com/gh_mirrors/po/pont/raw/3f1b7d4bbba3fd2dda

魔乐社区

如何快速上手 hvac：HashiCorp Vault Python 客户端零基础入门指南

**hvac** 是 HashiCorp Vault 的 Python 3.X 客户端库，专为开发者提供简单高效的 Vault 交互方式。无论你是需要管理密钥、配置身份验证，还是实现安全的秘密数据存储，hvac 都能帮助你轻松搞定 Vault 的各项操作。本文将带你零基础快速入门，从安装到基础操作，让你在几分钟内即可上手使用这个强大的工具。[![hvac 客户端 Logo](https://r