regdao.java

数学网

package math.reg.dao;

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

import javax.sql.DataSource;

import math.dao.DAO;
import math.login.model.User;
import math.news.model.News;
import math.news.model.NewsList;
import math.reg.model.Reg;
import math.reg.model.UpPass;

public class RegDao extends DAO {

	public RegDao(DataSource ds) {
		super(ds);
	}

	

	public void insert(Reg reg) throws SQLException {
		// ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();
			String sql = "insert into users(username,real_name,password,users_sex,users_ask,users_key,users_email,users_phone,users_school,users_pro,users_grade,users_cou,users_ip_addr) values (?,?,?,?,?,?,?,?,?,?,1,0,? )";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,reg.getUsername());
			pstmt.setString(2,reg.getReal_name());
			pstmt.setString(3,reg.getPassword());
			pstmt.setInt(4,reg.getUsers_sex());
			pstmt.setString(5,reg.getUsers_ask());
			pstmt.setString(6,reg.getUsers_key());
			pstmt.setString(7,reg.getUsers_email());
			pstmt.setString(8,reg.getUsers_phone());
			pstmt.setString(9,reg.getUsers_school());
			pstmt.setString(10,reg.getUsers_pro());			
			pstmt.setString(11,reg.getReg_ip_addr());

			pstmt.executeUpdate();
			conn.commit();
			close(pstmt);

		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	public News news(int newsid) throws SQLException {
		// ArrayList list = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		News news = new News();
		try {
			conn = ds.getConnection();
			String sql = "select n.*,u.username from news n,users u where n.userId=u.id and n.newsId =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,newsid);
			rs = pstmt.executeQuery();
			//
			if (rs.next()) {
				news.setNewsId(rs.getInt("newsId"));
				news.setNewsTitle(rs.getString("newsTitle"));	
				news.setNewsImage(rs.getString("newsImage"));
				news.setNewsContent(rs.getString("newsContent"));
				news.setUserName(rs.getString("username"));
			}else{
				news.setNewsId(0);
				news.setNewsTitle("无");
				news.setNewsImage("");
			}
			close(rs);
			close(pstmt);

		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return news;
	}
	public Reg select(int id) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Reg reg = new Reg();
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from users where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();

			if (rs.next()) {

				reg.setId(id);
				reg.setUsername(rs.getString("username"));
				reg.setReal_name(rs.getString("real_name"));
				reg.setUsers_sex(rs.getInt("users_sex"));
				reg.setUsers_email(rs.getString("users_email"));
				reg.setUsers_phone(rs.getString("users_phone"));
				reg.setUsers_school(rs.getString("users_school"));
				reg.setUsers_pro(rs.getString("users_pro"));
				

				// article.setPageroffset(offset);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return reg;
	}
	public void update(Reg reg) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			conn = ds.getConnection();
			
				String sql = "update  users set real_name=?,users_sex=?,users_email=?,users_phone=?,users_school=?,users_pro=? where id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, reg.getReal_name());
				pstmt.setInt(2, reg.getUsers_sex());
				pstmt.setString(3, reg.getUsers_email());
				pstmt.setString(4, reg.getUsers_phone());
				pstmt.setString(5, reg.getUsers_school());
				pstmt.setString(6, reg.getUsers_pro());
				pstmt.setInt(7, reg.getId());
				pstmt.executeUpdate();
			
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	public boolean updatepassword(UpPass uppass) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean bool=false;
		try {
			
			conn = ds.getConnection();
			
				String sql = "update  users set password=? where id = ? and password=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, uppass.getNewpassword());
				pstmt.setInt(2, uppass.getId());
				pstmt.setString(3, uppass.getOldpassword());
				int i=pstmt.executeUpdate();
				if(i>0)
				bool=true;
			close(rs);
			close(pstmt);
			
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		 return bool;
	}
}