今天学习的JDBC三层架构基本模型

package org.jxau.dao.impl;

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.jxau.bean.Admin;
import org.jxau.dao.IAdminDao;
import org.jxau.tools.Tools;

public class AdminDao implements IAdminDao {
	
	Connection connection = Tools.getConnection();
	PreparedStatement pStatement = null;
	ResultSet resultSet = null;
	
	@Override
	public Admin getAdminByNameAndPwd(String name, String pwd) {
		Admin info = null;
		String sql = "select * from admin where name=? and pwd=?";
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setString(1,name);
			pStatement.setString(2,pwd);
			resultSet = pStatement.executeQuery();
			if(resultSet.next()){
				info = new Admin();
				info.setId(resultSet.getInt("id"));
				info.setName(resultSet.getString("name"));
				info.setPwd(resultSet.getString("pwd"));
				info.setRoleid(resultSet.getInt("roleid"));		
			}
			return info;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			Tools.closeAll(connection, pStatement, resultSet);
		}
		return info;
	}

	@Override
	public List
 
   getAdminAll() {
		List
  
    list = new ArrayList
   
    (); String sql = "select * from admin"; try { pStatement = connection.prepareStatement(sql); resultSet = pStatement.executeQuery(); while(resultSet.next()){ Admin info = new Admin(); info.setId(resultSet.getInt("id")); info.setName(resultSet.getString("name")); info.setPwd(resultSet.getString("pwd")); info.setRoleid(resultSet.getInt("roleid")); list.add(info); } return list; } catch (SQLException e) { e.printStackTrace(); }finally{ Tools.closeAll(connection, pStatement, resultSet); } return list; } public boolean deleteAdmin(String name,String pwd) { String sql = "delete admin where name=? and pwd=?"; try { pStatement = connection.prepareStatement(sql); pStatement.setString(1,name); pStatement.setString(2,pwd); if(pStatement.executeUpdate()>0){ return true; } } catch (SQLException e) { e.printStackTrace(); }finally{ Tools.closeAll(connection, pStatement, resultSet); } return false; } @Override public boolean updateAdmin(String name, String pwd, int i,String value) { String sql = Tools.getSql(i); try { pStatement = connection.prepareStatement(sql); pStatement.setString(1,value); pStatement.setString(2,name); pStatement.setString(3,pwd); if(pStatement.executeUpdate()>0){ return true; } } catch (SQLException e) { e.printStackTrace(); }finally{ Tools.closeAll(connection, pStatement, resultSet); } return false; } }
   
  
 

package org.jxau.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

	
	public class Tools {
	@Test
		public static Connection getConnection(){
			Connection connection = null;
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			String user = "C##DHH";
			String password = "123456";
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				connection = DriverManager.getConnection(url, user, password);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return connection;
		}
	
		public static void closeAll(Connection c,Statement s,ResultSet r){
			try {
				if(r!=null) r.close();
				if(s!=null) s.close();
				if(c!=null) c.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		public static String getSql(int i){
			String sql = null;
			switch (i) {
			case 1:
				return sql = "update admin set id=? where name=? and pwd=?";
			case 2:
				return sql = "update admin set name=? where name=? and pwd=?";
			case 3:
				return sql = "update admin set pwd=? where name=? and pwd=?";
			case 4:
				return sql = "update admin set roleid=? where name=? and pwd=?";
			default:
				return null;
			}
		}
	}