userbuesiness.java

实现用户登录查询删除等功能

package org.inspure.testservletpro.model;

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 org.inspure.testservletpro.utils.UUIDPK;

public class UserBuesiness {
	private Connection conn = null;

	public UserBuesiness(Connection conn) {
		this.conn = conn;
	}

	public int create(Users users) {
		int state = 0;
		String sql = "INSERT INTO T_CSDNUSER (id,username,realusername,sex,province,hobbies) VALUES (?,?,?,?,?,?)";
		try {
			PreparedStatement pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, UUIDPK.getPk());
			pstmt.setString(2, users.getUsername());
			pstmt.setString(3, users.getRealusername());
			pstmt.setString(4, users.getSex());
			pstmt.setString(5, users.getProvince());
			pstmt.setString(6, users.getHobbies());
			state = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return state;

	}

	public List<Users> findAll() {
		List<Users> user = new ArrayList<Users>();
		String sql = "SELECT id,username,realusername,sex,province,hobbies FROM T_CSDNUSER";
		Users users = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = this.conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs != null) {
				while (rs.next()) {
					users = new Users();
					users.setId(rs.getString("id"));
					users.setUsername(rs.getString("username"));
					users.setRealusername(rs.getString("realusername"));
					users.setSex(rs.getString("sex"));
					users.setProvince(rs.getString("province"));
					users.setHobbies(rs.getString("hobbies"));
					user.add(users);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			pstmt = null;
			rs = null;
		}
		return user;
	}

	public Users findByPrimaryKey(String id) {
		String sql = "SELECT * FROM T_CSDNUSER WHERE id='" + id + "'";
		Users users = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = this.conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs != null) {
				if (rs.next()) {
					users = new Users();
					users.setId(rs.getString("id"));
					users.setUsername(rs.getString("username"));
					users.setRealusername(rs.getString("realusername"));
					users.setSex(rs.getString("sex"));
					users.setProvince(rs.getString("province"));
					users.setHobbies(rs.getString("hobbies"));
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			pstmt = null;
			rs = null;
		}
		return users;
	}

	public void delete(String id) {
		String sql = "DELETE FROM T_CSDNUSER WHERE id='" + id + "'";
		PreparedStatement pstmt=null;
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			pstmt = null;
		}

	}

	public void update(String id,String username, String realusername, String sex,
			String province, String _hobbies) {
		String sql = "UPDATE T_CSDNUSER SET username='"+username+"',realusername='"+realusername+"',sex='"+sex+"',province='"+province+"',hobbies='"+_hobbies+ "' WHERE id='"+id+"'";
//		System.out.println(sql);
		PreparedStatement pstmt=null;
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			pstmt=null;
		}
	}
	public List<Users> find(String username,String selProvice) {
		List<Users> user = new ArrayList<Users>();
		String sql = "SELECT * FROM t_csdnuser WHERE username LIKE '%"+username+"%' AND province LIKE '%"+selProvice+"%'";
//		System.out.println(sql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt=this.conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs != null){
				while(rs.next()){
			Users users = new Users();
					users.setId(rs.getString("id"));
					users.setUsername(rs.getString("username"));
					users.setRealusername(rs.getString("realusername"));
					users.setSex(rs.getString("sex"));
					users.setProvince(rs.getString("province"));
					users.setHobbies(rs.getString("hobbies"));
					user.add(users);
				}
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			pstmt = null;
		}
		return user;
	}
}