studao.java

银行开发系统需要的TestJdbc,辅助帮助作用

package org.xjtu.testjdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.xjtu.testjdbc.dbaccess.DBaccess;
import org.xjtu.testjdbc.vo.StuVo;



/**
 * 
 * 
 * 一个对studnet进行CURD 的数据库访问类
 * @author leon
 *
 */

public class StuDao {
	 
	DBaccess dbaccess = null;
	
	Connection conn = null;
	
	PreparedStatement stmt = null;
	
	ResultSet rs = null;
	
	
	
	String sql = "insert into student (stu_name,stu_birthday,stu_sex,stu_class)  values(?,?,?,?)";
	String sql1 = "delete from student where stu_id=?";
	String sql2="update student set stu_name=? , stu_birthday= ? , " +
			"stu_sex=? , stu_class = ? where stu_id=?";
	
	String sql3 = "select * from student where stu_id=?";
	
	String sql4 ="select * from student where stu_name like ?";
	
	
	
	/**
	 * 保存一个学员信息
	 * @param s StuVo
	 */
	public void stuAdd(StuVo s)
	{
		
	dbaccess = new 	DBaccess();
	dbaccess.connDB();
	conn = dbaccess.getConn();
	try {
		 stmt = conn.prepareStatement(sql);
		 stmt.setString(1,s.getStu_name());
		 stmt.setDate(2, new java.sql.Date(s.getStu_birthday().getTime()));
		 stmt.setString(3,s.getStu_sex());
		 stmt.setInt(4, s.getStu_class());
		
		stmt.executeUpdate();
	} catch (SQLException e) {
		
		e.printStackTrace();
	}
	finally
	{
		dbaccess.closeConn(conn);
		dbaccess = null;
	}	
		
	}
	
	
	/**
	 * 删除一个学员信息
	 * @param s StuVo
	 */
	public void stuDel(StuVo s)
	{
		dbaccess = new 	DBaccess();
		dbaccess.connDB();
		conn = dbaccess.getConn();
		try {
			 stmt = conn.prepareStatement(sql1);
			 stmt.setInt(1,s.getStu_id());			 
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally
		{
			dbaccess.closeConn(conn);
			dbaccess = null;
		}	
		
		
		
	}
	/**
	 * 修改一个学员信息
	 * @param s StuVo
	 */
	public void stuUpdate(StuVo s)
	{
		
		dbaccess = new 	DBaccess();
		dbaccess.connDB();
		conn = dbaccess.getConn();
		try {
			 stmt = conn.prepareStatement(sql2);
			 stmt.setInt(5,s.getStu_id());
			 stmt.setString(1,s.getStu_name());
			 stmt.setDate(2, new java.sql.Date(s.getStu_birthday().getTime()));
			 stmt.setString(3,s.getStu_sex());
			 stmt.setInt(4,s.getStu_class());
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally
		{
			dbaccess.closeConn(conn);
			dbaccess = null;
		}	
		
	}
	/**
	 * 查询一个学员信息
	 * @param s StuVo
	 */
	public StuVo getStuByKey(StuVo s)
	{
		
		dbaccess = new 	DBaccess();
		dbaccess.connDB();
		conn = dbaccess.getConn();
		StuVo vo = null;
		try {
			 stmt = conn.prepareStatement(sql3);
			 stmt.setInt(1,s.getStu_id());			
			 rs = stmt.executeQuery();
			
			 if (rs.next())
			 {
				 
				 vo = new  StuVo(rs.getInt("stu_id"),
						   rs.getString("stu_name"),
						   rs.getDate("stu_birthday"),
						   rs.getString("stu_sex"),
						   rs.getInt("stu_class"));
				 
			 }
			
			
			
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally
		{
			dbaccess.closeConn(conn);
			dbaccess = null;
		}	
		
		
		
		
		
		return vo;
		
		
	}
	
	/**
	 * 模糊查询学员信息
	 * @param s StuVo
	 */
	public List getStuByName(StuVo s)
	{
		dbaccess = new 	DBaccess();
		dbaccess.connDB();
		conn = dbaccess.getConn();
		StuVo vo = null;
		
		
		List<StuVo> list = new ArrayList<StuVo>();
		
		
		try {
			 stmt = conn.prepareStatement(sql4);
			 stmt.setString(1,"%"+s.getStu_name()+"%");			
			 rs = stmt.executeQuery();
			
			 while (rs.next())
			 {
				 
				 vo = new  StuVo(rs.getInt("stu_id"),
						   rs.getString("stu_name"),
						   rs.getDate("stu_birthday"),
						   rs.getString("stu_sex"),
						   rs.getInt("stu_class"));
				 list.add(vo);
				 
				 
				 
			 }
			
			
			
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		finally
		{
			dbaccess.closeConn(conn);
			dbaccess = null;
		}		
		return list;
		
	}
	
	
	
	/**
	 * 查询所有学员信息
	 * @param s StuVo
	 */
	public void getAllStu()
	{
		
		
		
		
	}
	
	
	public static void main(String g[])
	{
		//添加
//		StuVo vo = new StuVo("李望6",new Date(),"男6",4);
//		StuDao dao = new StuDao();
//		dao.stuAdd(vo);
		
        //删除
//		StuVo vo = new StuVo();
//		vo.setStu_id(47);
//		StuDao dao = new StuDao();
//		dao.stuDel(vo);
       
		
		
		//修改
       		StuVo vo = new StuVo(10,"小王",new Date(),"女",5);
       		
       		StuDao dao = new StuDao();
       		dao.stuUpdate(vo);
		
		//根据主键来查询 
//		StuVo vo = new StuVo(6);
//		
//		StuDao dao = new StuDao();
//		vo     = dao.getStuByKey(vo);
//        System.out.print(vo.getStu_name());
		
		
		//根据姓名来查询 
//		StuVo vo = new StuVo();	
//		vo.setStu_name("王");
//		StuDao dao = new StuDao();
//		
//		List list = dao.getStuByName(vo);
//        
//		Iterator i = list.iterator();
//		
//		
//		while (i.hasNext())
//		{
//		System.out.print(((StuVo)i.next()).getStu_name());
//		}
		
		
		
	}
	
	
	

}