databaseop.java

jsp实现的论坛 使用jsp+servlet+mysql 开发环境是myeclipes

package database;

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.sql.Timestamp;
//import java.util.List;
import java.util.Vector;

import entity.Back;
import entity.Message;
import entity.Notice;
import entity.User;

public class DataBaseOP {

	private Connection con;

	public DataBaseOP() {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public void addBack(Back back){
		try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("insert into back_info values(null,?,?,?,?,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP())");
			pstmt.setString(1, back.getFromId());
			pstmt.setInt(2, back.getTomessageNo());
			pstmt.setString(3, back.getSub());
			pstmt.setString(4, back.getContent());
			//pstmt.setTimestamp(5, back.getSendTime());
			//pstmt.setTimestamp(6, back.getModifyTime());
			
			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 addMessage(Message message){
		
		
		try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("insert into message_info values(null,?,?,?,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP(),?)");
			pstmt.setString(1, message.getSendid());
			pstmt.setString(2, message.getSub());
			pstmt.setString(3, message.getContent());
			//pstmt.setTime(4, message.getSendTime());
			//pstmt.setTime(5, message.getModifyTime());
			pstmt.setInt(4, message.getGroupNo());
			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 addNotice(Notice notice){
    	try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("insert into notice_info values(null,?,?,CURRENT_TIMESTAMP())");
			pstmt.setString(1, notice.getTitile());
			pstmt.setString(2, notice.getContent());
			
			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 addUser(User user){
    	try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("insert into user_info values(null,?,?,0,?,?,?,?)");
			pstmt.setString(1, user.getId());
			pstmt.setString(2, user.getPassword());
			pstmt.setInt(3, user.getAge());
			pstmt.setString(4, user.getSex());
			pstmt.setString(5, user.getQq());
			pstmt.setString(6, user.getName());
			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 deleteBack(int BackNo){
    	try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("delete from back_info where back_no=?");
			pstmt.setInt(1,BackNo);
			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 deleteMessage(int messageNo){
    	try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("delete from message_info where message_no=?");
			pstmt.setInt(1,messageNo);
			pstmt.executeUpdate();
			PreparedStatement pstmt2=con.prepareStatement("delete from back_info where back_tomessageno=?");
			pstmt2.setInt(1,messageNo);
			pstmt2.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 deleteUser(String ID){
       	try {
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
			PreparedStatement pstmt=con.prepareStatement("delete from user_info where user_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 Vector getBack(int backno){
       	Vector v=new Vector();
    	Statement stmt1=null;
    	ResultSet rs1=null;
    	
    	try {
			
    		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
    		stmt1=con.createStatement();
    		rs1=stmt1.executeQuery("select back_sub,back_content from back_info where back_no="+backno);
    		if(rs1.next()){
    		Vector v1=new Vector();
    		v1.add(0,rs1.getString(1));
    		v1.add(1,rs1.getString(2));

    		v.add(v1);
    		}        	
    		return v;
        	
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	return null;
    }
    public Vector getBackToMessage(int messageno){
    	Vector v=new Vector();
    	Statement stmt1=null;
    	
    	ResultSet rs1=null;
try {
			
    		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
    		stmt1=con.createStatement();
    		rs1=stmt1.executeQuery("select back_fromid,back_sub,back_sendtime,back_content,back_no from back_info where back_tomessageno="+messageno);
    		while(rs1.next()){
    			Vector v2=new Vector();
        		
        		v2.add(0,rs1.getString(1));
        		v2.add(1,rs1.getString(2));
        		v2.add(2,rs1.getTimestamp(3));
        		v2.add(3,rs1.getString(4));
        		v2.add(4,rs1.getInt(5));
        		v.add(v2);
        	}
        	
        	return v;
        	
        	} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    
    	return null;
    	
    }
    public Vector getGroupList(){
    	Vector v=new Vector();
    	Statement stmt=null;
    	ResultSet rs=null;
    	
    	try {
			
    		con=DriverManager.getConnection("jdbc:mysql://localhost:3306/forum?useUnicode=true&characterEncoding=gbk","root","123");
    		stmt=con.createStatement();
    		rs=stmt.executeQuery("select group_no,group_name from group_info order by group_no");
    	while(rs.next()){
    		Vector v1=new Vector();
    		v1.add(0, Integer.toString(rs.getInt(1)));
    		v1.add(1,rs.getString(2));
    		v.add(v1);
    	}