userservice.java

利用servlet写的用户注册登陆

package com.tarena.persitence;

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

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

public class UserService {

	public List<User> findAll() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		List<User> users = null;

		try {
			con = getConnection();
			System.out.println(con);
			String sql = "select id,uname,pwd,gender,degree,birthday,hobbies,introduction from user_0807";
			// System.out.println(sql);
			st = con.createStatement();
			rs = st.executeQuery(sql);
			users = getUser(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			close(rs, st, con);
		}

		return users;
	}

	private List<User> getUser(ResultSet rs) throws SQLException {
		List<User> users = new ArrayList<User>();
		while (rs.next()) {
			User user = new User();
			user.setId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setGender(rs.getString(4));
			user.setDegree(rs.getString(5));
			user.setBirthday(rs.getDate(6));
			user.setHobbies(rs.getString(7));
			user.setIntroduction(rs.getString(8));
			users.add(user);
		}

		return users;
	}

	// private Connection getConnection() throws Exception {
	// Class.forName("oracle.jdbc.driver.OracleDriver");
	// return DriverManager.getConnection(
	// "jdbc:oracle:thin:@192.168.0.20:1521:tarena", "openlab",
	// "open123");
	// }

	private Connection getConnection() throws Exception {
		Context ctx = new InitialContext();
		DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/oracle");
		return ds.getConnection();
	}

	private void close(ResultSet rs, Statement st, Connection con) {
		try {
			rs.close();
		} catch (Exception e) {
		}

		try {
			st.close();
		} catch (Exception e) {
		}

		try {
			con.close();
		} catch (Exception e) {
		}

	}

	public void add(User user) {
		Connection con = null;
		PreparedStatement ps = null;

		try {
			con = getConnection();
			user.setId(generateId(con, "user_sequence"));
			String sql = "insert into user_0807(id,uname,pwd,gender,degree,birthday,hobbies,introduction) values(?,?,?,?,?,?,?,?)";
			System.out.println(sql);
			ps = con.prepareStatement(sql);
			int index = 1;
			ps.setInt(index++, user.getId());
			ps.setString(index++, user.getUserName());
			ps.setString(index++, user.getPassword());
			ps.setString(index++, user.getGender());
			ps.setString(index++, user.getDegree());
			ps.setDate(index++, user.getBirthday());
			ps.setString(index++, user.getHobbies());
			ps.setString(index++, user.getIntroduction());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			close(null, ps, con);
		}
	}

	private int generateId(Connection con, String sequence) {
		Statement st = null;
		ResultSet rs = null;
		int id = -1;

		try {
			String sql = "select " + sequence + ".nextval from dual";
			System.out.println(sql);
			st = con.createStatement();
			rs = st.executeQuery(sql);
			rs.next();
			id = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			close(rs, st, null);
		}

		return id;
	}

	public void modify(User user) {

	}

	public void remove(int id) {

	}

	public User find(String userName, String password) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> users = null;

		try {
			con = getConnection();
			String sql = "select id,uname,pwd,gender,degree,birthday,hobbies,introduction from user_0807 ";
			sql += "where uname=? and pwd=?";
			System.out.println(sql);
			ps = con.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2, password);
			rs = ps.executeQuery();
			users = getUser(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			close(rs, ps, con);
		}

		return users.isEmpty() ? null : users.iterator().next();
	}

	public User findById(int id) {
		return null;
	}

}