作业

 

package com.openlab.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

public abstract class JDBCUtils {
	private static String driver=null;
	private static String url=null;
	private static String user=null;
	private static String password=null;
	static{
		try {
			Properties properties=new Properties();
			InputStream in=JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
			properties.load(in);
			driver=properties.getProperty(driver);
			url=properties.getProperty(url);
			user=properties.getProperty(user);
			password=properties.getProperty(password);
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		Connection conn=null;
		try {
			 conn=DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}return conn;
	}
	
	public static void close(Connection conn,PreparedStatement ps,ResultSet rs){
		try {
			if(rs!=null){
				rs.close();
			}
			rs=null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(ps!=null){
					ps.close();
				}
				ps=null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					if(conn!=null){
						conn.close();
					}
					conn=null;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	
	
	public static void update(String sql,Object...args){
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(conn,ps,null);
		}
	}
	
	public Object queryByOne(String sql){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Object res=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			res=resultSetToObject(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}return res;
	}
	
	
	public List queryByAll(String sql){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List list=null;
		try {
			conn=getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			list=resultSetToList(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}return list;
	}

	protected abstract Object resultSetToObject(ResultSet rs); 
	protected abstract List resultSetToList(ResultSet rs);
}

 

 

package com.openlab.pojo;

public class Employee {
	private String id;
	private String username;
	private double salary;
	private int age;
	private String depart;
	
	

	public Employee() {
		
	}

	public Employee(String id, String username, double salary, int age,String depart) {
		super();
		this.id = id;
		this.username = username;
		this.salary = salary;
		this.age = age;
		this.depart = depart;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getDepart() {
		return depart;
	}

	public void setDepart(String dapart) {
		this.depart = depart;
	}

	@Override
	public String toString() {
		return "Employee [id="+id+",username="+username+",salary="+salary+",age="+age+",depart="+depart+"]";
	}
	
	

}

 

package com.openlab.dao;

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

import com.openlab.pojo.Employee;
import com.openlab.utils.JDBCUtils;
//数据链路层
public class EmployeeServiceDao extends JDBCUtils {

	@Override
	protected Object resultSetToObject(ResultSet rs) {
		Employee emp = new Employee();
		try {
			if (rs.next()) {
				emp.setId(rs.getString("id"));
				emp.setUsername(rs.getString("username"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				emp.setDepart(rs.getString("depart"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emp;
	}

	@Override
	protected List resultSetToList(ResultSet rs) {
		List list=new ArrayList();
		try {
			while (rs.next()) {
				Employee emp = new Employee();
				emp.setId(rs.getString("id"));
				emp.setUsername(rs.getString("username"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				emp.setDepart(rs.getString("depart"));
				list.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	

}

package com.openlab.service;

import java.util.List;

import com.openlab.pojo.Employee;
//业务逻辑层
public interface EmployeeService {
	public Employee queryById(String id);
	public List<Employee>queryByName(String name);

}

package com.openlab.service.impl;

import java.util.List;

import com.openlab.dao.EmployeeServiceDao;
import com.openlab.pojo.Employee;
import com.openlab.service.EmployeeService;

public class EmployeeServiceImpl implements EmployeeService{
	EmployeeServiceDao dao=new EmployeeServiceDao();
	@Override
	public Employee queryById(String id){
		String sql="select * fom emp where id='"+id+"'";
		Object obj=dao.queryByOne(sql);//把执行后的值给到obj
		Employee emp=(Employee)obj;
		return emp;
	}
	@Override
	public List<Employee> queryByName(String name) {
		String sql="SELECT * FROM emp where username='"+name+"'";
		List list=dao.queryByAll(sql);
		return list;
	}

}