DAO模式

一 Base Dao工具

package cn.dao;

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

public class BaseDao {
	private String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/userpetshop";
	private String num = "root";
	private String pwd = "1234";
	Connection conn = null;
	/**
	 * 建立连接
	 * @return Connection对象
	 */
	public Connection getconn(){
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,num,pwd);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 关闭连接
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public void closeAll(ResultSet rs, Statement st, Connection conn){
			try {
				if(rs != null)
					rs.close();
				if(st != null)
					st.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}

}

　1，增删改

/**
* 增删改
* @throws Exception 
*/

public int executeUpdate(String sql,Object...obj) throws Exception{
//获取连接
getConnection();
//获取prepareStatement对象
statement= connection.prepareStatement(sql);
//循环加载参数
for (int i = 1; i <=obj.length; i++) {
statement.setObject(i,obj[i-1]);
}
//执行SQL
int count=statement.executeUpdate();
return count;
}

2，查询

/**
* 查询的方法
* @throws Exception 
*/
public ResultSet executeQuery(String sql,Object...obj) throws Exception{
//获取连接
getConnection();
//获取
statement= connection.prepareStatement(sql);
//循环加载参数
for (int i = 1; i <=obj.length; i++) {
statement.setObject(i, obj[i-1]);
}
//执行SQL
rs= statement.executeQuery();
return rs;
}

3，回收

/**
* 回收资源
* @throws Exception 
*/
public void clossResource() throws Exception{
if (rs!=null) {
rs.close();
}if (statement!=null) {
statement.close();
}if (connection!=null) {
connection.close();
connection=null;
}
}
}

二，Student实体类

package com.students;

public class students {
private int StudentNo;
private String LoginPwd;
private String studentName;
private int sex;
private String Phone;
private String Address;
private String bornDate;
private String Email;
private String IdentityCard;
private int Gradeid;

public int getGradeid() {
return Gradeid;
}
public void setGradeid(int gradeid) {
Gradeid = gradeid;
}
private Grade grade;

public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}

public int getStudentNo() {
return StudentNo;
}
public void setStudentNo(int studentNo) {
StudentNo = studentNo;
}
public String getLoginPwd() {
return LoginPwd;
}
public void setLoginPwd(String loginPwd) {
LoginPwd = loginPwd;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getPhone() {
return Phone;
}
public void setPhone(String phone) {
Phone = phone;
}
public String getAddress() {
return Address;
}
public void setAddress(String address) {
Address = address;
}
public String getBornDate() {
return bornDate;
}
public void setBornDate(String bornDate) {
this.bornDate = bornDate;
}
public String getEmail() {
return Email;
}
public void setEmail(String email) {
Email = email;
}
public String getIdentityCard() {
return IdentityCard;
}
public void setIdentityCard(String identityCard) {
IdentityCard = identityCard;
}
}

三，Student Dao接口

package com.student.dao;

import java.util.List;

import com.students.students;

public interface StudentDao {
/*
* 查询得方法
*/
List<students> stu() throws Exception;
}

四、StudentDaoImpl接口实现类

package com.student.dao.impl;

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

import BaseDao.BaseDao;

import com.student.dao.StudentDao;
import com.students.Grade;
import com.students.students;

public class StudentDaoImpl extends BaseDao implements StudentDao{

@Override
public List<students> stu() throws Exception {
List<students> students=new ArrayList<students>();

//准备SQL
String sql="SELECT student.*,grade.* FROM student,grade WHERE student.GradeId=grade.GradeID";
ResultSet rs = executeQuery(sql);

if (rs!=null) {
while (rs.next()) {
students stu=new students();
stu.setStudentNo(rs.getInt("StudentNo"));
stu.setLoginPwd(rs.getString("LoginPwd"));
stu.setStudentName(rs.getString("StudentName"));
stu.setSex(rs.getInt("Sex"));
stu.setGradeid(rs.getInt("Gradeid"));
stu.setPhone(rs.getString("Phone"));
stu.setAddress(rs.getString("Address"));
stu.setBornDate(rs.getString("BornDate"));
stu.setEmail(rs.getString("Email"));
stu.setIdentityCard(rs.getString("IdentityCard"));
Grade grade = new Grade();
grade.setGradeName(rs.getString("GradeName"));
stu.setGrade(grade);
students.add(stu);
}
}
clossResource();
return students;
}
}

五、Test实现类

package com.student.dao.impl;

import java.util.List;

import com.student.dao.StudentDao;
import com.students.students;

public class Test {

public static void main(String[] args) throws Exception {
StudentDao studentDao=new StudentDaoImpl();
System.out.println("学号	密码	姓名	性别	年级编号		地址	手机号	邮箱	日期	身份证号	 年纪名称");
List<students> stu=studentDao.stu();
for (students students : stu) {
System.out.println(students.getStudentNo()+"	"+students.getLoginPwd()+"	"+students.getStudentName()+"	"+students.getSex()+"	"+students.getGradeid()+"	"+students.getAddress()+"	"+students.getPhone()+"	"+students.getEmail()+"	"+students.getBornDate()+"	"+students.getIdentityCard()+"	"+students.getGrade().getGradeName());
}
}
}

查询s1年级下的学生记录

一、grade类：

package com.myschool.entity;
import java.util.List;
public class grade {
　　 private int gradeid;　　//年级编号
　　 private String gradename;　　//年级名称
 
  　　  public List<student> list;　　//学生表集合
 
 　　public List<student> getList() {
  　　　　return list;
 　　}
　　 public void setList(List<student> list) {
　　　　  this.list = list;
 　　}
　　 public int getGradeid() {
 　　　　 return gradeid;
　　 }
　　 public void setGradeid(int gradeid) {
 　　　　 this.gradeid = gradeid;
　　 }
　　 public String getGradename() {
 　　　　 return gradename;
　　 }
 　　public void setGradename(String gradename) {
 　　　　 this.gradename = gradename;
　　 }
 　　//带参构造
　　 public grade(int gradeid, String gradename) {
 　　　　 super();
 　　　　 this.gradeid = gradeid;
  　　　　this.gradename = gradename;
　　 }
　　//无参构造
　　 public grade(){
  
　　 }
}

 

 

二、Grade实现类

@Override
　　 public grade getgrade(String gradename) throws Exception {
　　　　  List<student> list1=new ArrayList<student>();
 　　　　 String sql="select s.*,g.GradeName from student as s,grade as g where s.GradeId=g.GradeId and g.gradename=?";
 　　　　 Object[] obj={gradename};
  　　　　rs=executeQuery(sql, obj);
 　　　　 grade grade=null;
 　　　　 if (rs!=null) {
   　　　　　　while(rs.next()){
  　　　　　　　　  grade=new grade();
   　　　　　　　　 student student=new student();
  　　　　　　　　  student.setStudentno(rs.getInt("studentno"));
    　　　　　　　　student.setStudentname(rs.getString("studentname"));
  　　　　　　　　  list1.add(student);
  　　　　　　　　  grade.setGradename(rs.getString("gradename"));
  　　　　　　 }
 　　　　　　  grade.setList(list1);
　　　　  }
　　　　  return grade;
 　　}
 

 

三、Test实现类

 

public static void main(String[] args) throws Exception {
　　　　  getstudent();
 　　　　 System.out.println("=================================");
  　　　　grade grade=dao.getgrade("S1");
  　　　　for (student stu : grade.getList()) {
   　　　　　　System.out.println(stu.getStudentno()+"	"+stu.getStudentname()+"	"+grade.getGradename());
  　　　　}
　　 }