oracletuserdao.java

这是我在东方标准学习的时候做的一个项目

package dao.product.oracle;

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 dao.product.DbUtil;
import dao.product.TUserDAO;

import bean.TUser;

public class OracleTUserDAO implements TUserDAO {
	
	public boolean add(TUser user) {
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = null;
		sql = "insert into TUser (user_id,mobile_number,roaming_status,com_level,customer_id,account_id) values (user_sequence.nextval,?,?,?,?,?)";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setString(1, user.getMobile_number());
			prepStmt.setString(2, user.getRoaming_status());
			prepStmt.setString(3, user.getCom_level());
			prepStmt.setInt(4, user.getCustomer_id());
			prepStmt.setInt(5, user.getAccount_id());
			prepStmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			db.close();
		}
	}

	public boolean update(TUser user) {
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "update TUser set mobile_number=?,roaming_status=?,com_level=?,customer_id=?,account_id=? where user_id=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setString(1, user.getMobile_number());
			prepStmt.setString(2, user.getRoaming_status());
			prepStmt.setString(3, user.getCom_level());
			prepStmt.setInt(4, user.getCustomer_id());
			prepStmt.setInt(5, user.getAccount_id());
			prepStmt.setInt(6, user.getUser_id());
			prepStmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			db.close();
		}
	}

	public boolean delete(int user_id) {
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "delete TUser where user_id=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setInt(1, user_id);
			prepStmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			db.close();
		}
	}

	public TUser findById(int user_id) {
		TUser user = new TUser();
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from TUser where user_id=?";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			prepStmt.setInt(1, user_id);
			ResultSet rs = prepStmt.executeQuery();
			if (rs.next()) {
				user.setUser_id(rs.getInt("user_id"));
				user.setMobile_number(rs.getString("mobile_number"));
				user.setRoaming_status(rs.getString("roaming_status"));
				user.setCom_level(rs.getString("com_level"));
				user.setCustomer_id(rs.getInt("customer_id"));
				user.setAccount_id(rs.getInt("account_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return user;
	}

	public List findAll() {
		List<TUser> list = new ArrayList<TUser>();
		DbUtil db = new OracleDbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from TUser";
		try {
			PreparedStatement prepStmt = conn.prepareStatement(sql);
			ResultSet rs = prepStmt.executeQuery();
			while (rs.next()) {
				TUser user = new TUser();
				user.setUser_id(rs.getInt("user_id"));
				user.setMobile_number(rs.getString("mobile_number"));
				user.setRoaming_status(rs.getString("roaming_status"));
				user.setCom_level(rs.getString("com_level"));
				user.setCustomer_id(rs.getInt("customer_id"));
				user.setAccount_id(rs.getInt("account_id"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.close();
		}
		return list;
	}
	
}