JDBC的分层

学生表（student）结构：

sid sname sage sdid sbir sphone

实体类

package entity;

import java.util.Date;

public class Student {
	private int sid;
	private String sname;
	private int sage;
	private int did;
	private Date sbir;	
	private String sphone;
	
	
	public Student() {
		
	}	
	
	public Student(int sid, String sname, int sage, int did, Date sbir,String sphone) {
		this.sid = sid;
		this.sname = sname;
		this.sage = sage;
		this.did = did;
		this.sbir = sbir;
		this.sphone=sphone;
	}

	public String getSphone() {
		return sphone;
	}

	public void setSphone(String sphone) {
		this.sphone = sphone;
	}

	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public int getSage() {
		return sage;
	}
	public void setSage(int sage) {
		this.sage = sage;
	}
	public int getDid() {
		return did;
	}
	public void setDid(int did) {
		this.did = did;
	}
	public Date getSbir() {
		return sbir;
	}
	public void setSbir(Date sbir) {
		this.sbir = sbir;
	}	
}

连接数据库的工具类

package util;

import java.sql.*;

public class ConnDB {
	private static String URL="jdbc:mysql://localhost/test_jdbc";
	private static String USERNAME="root";
	private static String PASSWORD="123456";
	private static Connection conn=null;
	
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConn(){
		try {
			conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}	
}

DAO

package dao;

import java.util.ArrayList;

import entity.Student;

public interface StudentDao {	
	
	//student通过id和phone来登录(0 成功 1用户名错误 2密码错误)
	public int login(int id,String phone);
	

	//保存一个student对象	
	public boolean saveStudent(Student s);
	
	
	//通过id查询一个student对象
	public Student getStudentByID(int id);
	
	//通过name查询student对象
	public Student getStudentByName(String name);	
	
	
	//查询所有的student对象
	public ArrayList<Student> getAllStudent();
	
	
	//通过id更新一个student对象
	public boolean updateStudent(int id,Student s);
	
	
	//通过id删除一个student对象
	public boolean deleteStudent(int id);
	
	//关闭资源
	public void close();
	
}

DAO的实现

package dao;

import java.sql.*;
import java.util.ArrayList;

import util.ConnDB;

import entity.Student;

public class StudentDaoImpl implements StudentDao{
	private Connection conn;
	private PreparedStatement psta;
	private ResultSet rs;

	//根据id删除学生
	public boolean deleteStudent(int id) {
		boolean flag=false;
		conn=ConnDB.getConn();
		String sql="delete from student where sid=?";
		try {
			psta=conn.prepareStatement(sql);
			psta.setInt(1, id);
			if(psta.executeUpdate()>0){
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.close();
		return false;
	}

	
	//查询所有的学生
	public ArrayList<Student> getAllStudent() {
		ArrayList<Student> list=new ArrayList<Student>();
		conn=ConnDB.getConn();
		String sql="select * from student";
		try {
			psta=conn.prepareStatement(sql);
			rs=psta.executeQuery();
			while(rs.next()){
				Student s=new Student();
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSage(rs.getInt("sage"));
				s.setDid(rs.getInt("did"));
				s.setSbir(rs.getDate("sbir"));
				s.setSphone(rs.getString("sphone"));
				list.add(s);				
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		this.close();
		return list;
	}

	
	//根据id查询学生
	@Override
	public Student getStudentByID(int id) {
		Student s=new Student();
		conn=ConnDB.getConn();
		String sql="select * from student where sid=?";
		try {
			psta=conn.prepareStatement(sql);
			psta.setInt(1, id);
			rs=psta.executeQuery();
			if(rs.next()){
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSage(rs.getInt("sage"));
				s.setDid(rs.getInt("did"));
				s.setSbir(rs.getDate("sbir"));
				s.setSphone(rs.getString("sphone"));								
			}else{
				s=null;
			}		
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return s;
	}

	
	//根据名字查询学生
	@Override
	public Student getStudentByName(String name) {
		Student s=new Student();
		conn=ConnDB.getConn();
		String sql="select * from student where sname=?";
		try {
			psta=conn.prepareStatement(sql);
			psta.setString(1, name);
			rs=psta.executeQuery();
			if(rs.next()){
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSage(rs.getInt("sage"));
				s.setDid(rs.getInt("did"));
				s.setSbir(rs.getDate("sbir"));
				s.setSphone(rs.getString("sphone"));								
			}else{
				s=null;
			}		
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return s;
	}

	
	//保存学生
	@Override
	public boolean saveStudent(Student s) {
		boolean flag=false;
		conn=ConnDB.getConn();
		String sql="insert into student(sid,sname,sage,sbir,sdid,sphone) values(?,?,?,?,?,?)";
		try {
			psta=conn.prepareStatement(sql);
			psta.setInt(1, s.getSid());
			psta.setString(2, s.getSname());
			psta.setInt(3, s.getSage());
			//java的date转sql的date
			psta.setDate(4, new java.sql.Date(s.getSbir().getTime()));
			psta.setInt(5, s.getDid());
			psta.setString(6, s.getSphone());
			if(psta.executeUpdate()>0){
				flag=true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		this.close();
		return flag;
	}

	//更新学生
	@Override
	public boolean updateStudent(int id, Student s) {
		boolean flag=false;
		conn=ConnDB.getConn();
		String sql="update student set sname=?,sage=?,sdid=?,sbir=?,sphone=? where sid=?";
		try {
			psta=conn.prepareStatement(sql);			
			psta.setString(1, s.getSname());
			psta.setInt(2, s.getSage());
			psta.setInt(3, s.getDid());
			//java的date转sql的date
			psta.setDate(4, new java.sql.Date(s.getSbir().getTime()));			
			psta.setString(5, s.getSphone());
			psta.setInt(6, s.getSid());
			if(psta.executeUpdate()>0){
				flag=true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		this.close();
		return flag;
	}

	//用户登录
	@Override
	public int login(int id, String phone) {
		int n=3;//系统内部错误
		conn=ConnDB.getConn();
		String sql="select sphone from student where sid=?";
		try {
			psta=conn.prepareStatement(sql);
			psta.setInt(1, id);
			rs=psta.executeQuery();
		
		if(rs.next()){
			if(phone.equals(rs.getString(1))){
				n=0;
			}else{
				n=2;
			}
		}else{
			n=1;
		}
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.close();
		return n;
	}

	
	@Override
	public void close() {
		try {
			if(conn!=null){
				conn.close();				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if(psta!=null){
			try {
				psta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
	}
}

测试类

package test;

import java.util.Date;

import dao.StudentDao;
import dao.StudentDaoImpl;
import entity.Student;

public class Test {
	public static void main(String[] args) {
		Student s=new Student(1001,"zhangsan",18,1,new Date(),"12345678912");
		StudentDao	sd=new StudentDaoImpl();
		Boolean flag=sd.saveStudent(s);
		
		if(flag){
			System.out.println("保存成功");
		}else{
			System.out.println("保存失败");
		}		
	}
}

package test;

import java.util.Scanner;

import dao.StudentDao;
import dao.StudentDaoImpl;

public class Test_login {
	public static void main(String[] args) {
		System.out.println("请输入id后回车输入用户电话");
		Scanner sc=new Scanner(System.in);
		int id=sc.nextInt();
		String phone=sc.next();
		System.out.println("id:"+id+" phone:"+phone);
		StudentDao sd=new StudentDaoImpl();
		int n=sd.login(id, phone);
		if(n==0){
			System.out.println("登录成功");
		}else if(n==1){
			System.out.println("用户名错误");
		}else if(n==2){
			System.out.println("密码错误");
		}else if(n==3){
			System.out.println("系统内部错误");
		}
	}
}