databaseop.java

这是用jsp编写的通信录

package data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class DatabaseOp {
public Connection con;
public DatabaseOp(){
	try {
		Class.forName("com.mysql.jdbc.Driver");
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contacts?useUnicode=true&characterEncoding=gbk","root","");
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}








public Staff showStaff(String ID){
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	Staff staff=new Staff();
	try {
		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contacts?useUnicode=true&characterEncoding=gbk","root","");
		pstmt=con.prepareStatement("select * from staff,dep where id=? AND staff.depid=dep.depid");
		pstmt.setInt(1,Integer.parseInt(ID));
		
		rs=pstmt.executeQuery();
		if(rs.next()){
			
		staff.setId(rs.getInt(1));
		staff.setName(rs.getString(2));
		staff.setAge(rs.getInt(3));
		staff.setJob(rs.getString(4));
		staff.setTelephone(rs.getString(5));
		staff.setMobilephone(rs.getString(6));
		staff.setEmail(rs.getString(7));
		staff.setDepname(rs.getString("dep.name"));
		staff.setDepid(rs.getInt("dep.depid"));
		
		}
		return staff;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	finally{
		try {
			con.close();
			rs.close();
			pstmt.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return null;
}
public void deletStuff(String ID){
		try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contacts?useUnicode=true&characterEncoding=gbk","root","");
			PreparedStatement pstmt=con.prepareStatement("delete from staff where id=?");
			pstmt.setInt(1, Integer.parseInt(ID));
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}finally{
			try {
				con.close();
				
				
				
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
	
	
		}
	
	
	
	
	
}
public void addStaff(Staff staff){
	PreparedStatement pstmt=null;
	try {
		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contacts?useUnicode=true&characterEncoding=gbk","root","");
		 pstmt=con.prepareStatement("insert into staff values(null,?,?,?,?,?,?,?)");
		pstmt.setString(1, staff.getName());
		pstmt.setInt(2, staff.getAge());
		pstmt.setString(3, staff.getJob());
		pstmt.setString(4, staff.getTelephone());
		pstmt.setString(5, staff.getMobilephone());
		pstmt.setString(6, staff.getEmail());
		pstmt.setInt(7, staff.getDepid());
		System.out.println("test0"+staff.getAge()+staff.getName());
		System.out.println("test1"+pstmt.executeUpdate());
		;
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	finally{
		try {
			con.close();
			
			
			pstmt.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
public void updateStaff(String id,Staff staff){
	PreparedStatement pstmt = null;
	System.out.println(staff.getName());
	try {
		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/contacts?useUnicode=true&characterEncoding=gbk","root","");
		pstmt = con.prepareStatement("update staff set name=?,age=?,job=?,telephone=?,mobilephone=?,email=?,depid=? where id=?");
		
		
		pstmt.setString(1, staff.getName());
		pstmt.setInt(2, staff.getAge());
		pstmt.setString(3, staff.getJob());
		pstmt.setString(4, staff.getTelephone());
		pstmt.setString(5, staff.getMobilephone());
		pstmt.setString(6, staff.getEmail());
		pstmt.setInt(7, staff.getDepid());
		pstmt.setInt(8, staff.getId());
		
		
		
		
		
		
		
		
		
		pstmt.executeUpdate();
	
	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	finally{
		try {
			con.close();
			
			
			pstmt.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
}
}