dbbean.java

通讯录

package cn.com.txl.jdbc;

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

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

import cn.com.txl.tools.MD5;
import cn.com.txl.vo.PageBean;
import cn.com.txl.vo.TxlInfo;
import cn.com.txl.vo.UserInfo;
import cn.com.txl.tools.GetDateInfo;

public class DBbean {
	private DataSource ds;

	private Connection con;

	public DBbean() {
		try {
			GetDateInfo datainfo = GetDateInfo.getInstance();
			String datasourcename = datainfo.getInfo();
			Context initCtx = new InitialContext();
			ds = (DataSource) initCtx.lookup(datasourcename);
			if (ds != null) {
				con = ds.getConnection();

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean setUserInfo(UserInfo userinfo) {
		boolean rec = true;
		String username = userinfo.getUsername();
		String password = userinfo.getPassword();
		MD5 md = new MD5();
		password = md.getMD5ofStr(password);
		String sex = userinfo.getSex();
		String tel = userinfo.getTel();
		String address = userinfo.getAddress();
		String sql = "insert into UserInfo(username,password,sex,tel,address,zcdate) values(?,?,?,?,?,getdate())";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			pstmt.setString(3, sex);
			pstmt.setString(4, tel);
			pstmt.setString(5, address);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			rec = false;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rec;
	}

	public boolean setTxlInfo(TxlInfo txlinfo) {
		boolean rec = true;
		String username = txlinfo.getUsername();
		String address = txlinfo.getAddress();
		String sex = txlinfo.getSex();
		String tel = txlinfo.getTel();
		String qq = txlinfo.getQq();
		String filename = txlinfo.getFilename();
		int uid = txlinfo.getUid();
		String sql = "insert into TxlInfo(username,address,sex,tel,qq,uid,filename,crdate) values(?,?,?,?,?,?,?,getdate())";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, address);
			pstmt.setString(3, sex);
			pstmt.setString(4, tel);
			pstmt.setString(5, qq);
			pstmt.setInt(6, uid);
			pstmt.setString(7, filename);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			rec = false;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rec;
	}

	public boolean delTxlInfo(String id) {
		boolean rec = true;
		String sql = "delete from TxlInfo where id =?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			rec = false;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rec;
	}

	public boolean updateTxlInfo(TxlInfo txlinfo) {
		boolean rec = true;
		String sql = "update TxlInfo set username=?,address=?,sex=?,tel=?,qq=? where id=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, txlinfo.getUsername());
			pstmt.setString(2, txlinfo.getAddress());
			pstmt.setString(3, txlinfo.getSex());
			pstmt.setString(4, txlinfo.getTel());
			pstmt.setString(5, txlinfo.getQq());
			pstmt.setInt(6, txlinfo.getId());
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			rec = false;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rec;
	}

	public UserInfo findUserInfo(UserInfo userinfo) {
		String sql = "select id from UserInfo where username=? and password =?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userinfo.getUsername());
			pstmt.setString(2, new MD5().getMD5ofStr(userinfo.getPassword()));
			rs = pstmt.executeQuery();
			while (rs.next()) {
				userinfo.setId(rs.getInt("id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					rs.close();
					pstmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return userinfo;
	}

	public boolean findUserName(String username) {
		boolean rec = true;
		String sql = "select id from UserInfo  where username=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				rec = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					rs.close();
					pstmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return rec;
	}

	public int txlinfoCount() {
		int txlcount = 0;
		String sql = "select count(*) from TxlInfo";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				txlcount = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					rs.close();
					pstmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return txlcount;
	}

	public void listTxlInfo(PageBean pagebean) {
		int maxlist = pagebean.getCurPage() * pagebean.getRowsPerPage();
		String sql = "select top " + maxlist + " * from TxlInfo";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int i = 1;
			while (rs.next()) {
				if (i > ((pagebean.getCurPage() - 1) * pagebean.getRowsPerPage())) {
					TxlInfo txlinfo = new TxlInfo();
					txlinfo.setId(rs.getInt("id"));
					txlinfo.setUsername(rs.getString("username"));
					txlinfo.setAddress(rs.getString("address"));
					txlinfo.setSex(rs.getString("sex"));
					txlinfo.setTel(rs.getString("tel"));
					txlinfo.setQq(rs.getString("qq"));
					txlinfo.setFilename(rs.getString("filename"));
					list.add(txlinfo);
				}
				i++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					rs.close();
					pstmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		pagebean.setList(list);
	}

	public TxlInfo getTxlInfo(String id) {
		String sql = "select * from TxlInfo where id=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		TxlInfo txlinfo = new TxlInfo();
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				txlinfo.setId(rs.getInt("id"));
				txlinfo.setUsername(rs.getString("username"));
				txlinfo.setAddress(rs.getString("address"));
				txlinfo.setSex(rs.getString("sex"));
				txlinfo.setTel(rs.getString("tel"));
				txlinfo.setQq(rs.getString("qq"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					rs.close();
					pstmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return txlinfo;
	}

	public List myListTxlInfo(String uid) {
		String sql = "select * from TxlInfo where uid=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, uid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TxlInfo txlinfo = new TxlInfo();
				txlinfo.setId(rs.getInt("id"));
				txlinfo.setUsername(rs.getString("username"));
				txlinfo.setAddress(rs.getString("address"));
				txlinfo.setSex(rs.getString("sex"));
				txlinfo.setTel(rs.getString("tel"));
				txlinfo.setQq(rs.getString("qq"));
				list.add(txlinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					rs.close();
					pstmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public void dbclose() {
		try {
			if (con != null)
				con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}