notesqlbean.java

用mvc实现的留言板

package com.jq.bean;

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

public class NoteSqlBean {

	Connection con;
	PreparedStatement pre;
	ResultSet rs;
	public NoteSqlBean() {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=noteMVC","sa","");
	} catch (Exception e) {
		// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}	
	
	//用户验证
	public String getperson(NoteJavaBean jBean) {
		String name = "";
		String sql = "SELECT name FROM person WHERE sId=? and password=?" ;
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, jBean.getSId());
			pre.setString(2, jBean.getPassword());
			rs = pre.executeQuery();
			if(rs.next())
			{
				name = rs.getString(1);
			}
			rs.close() ;
			pre.close() ;
			con.close() ;
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return name;
	}

	//信息查询
	public List getSearch(String keyword) {
		List list = new ArrayList();
		String sql = "";
		
		//返红执行的SQL语句
		if (!"".equals(keyword) && keyword != null) {
			sql = "SELECT id,title,author,content FROM note WHERE title like ? or author like ? or content like ?";
		} else {
			sql = "SELECT id,title,author,content FROM note";
		}
		try {
			pre = con.prepareStatement(sql);
			
			//返红参数;
			if (!"".equals(keyword) && keyword != null) {
				pre.setString(1, "%"+keyword+"%");
				pre.setString(2,"%"+keyword+"%") ;
				pre.setString(3,"%"+keyword+"%") ;
			}
			rs = pre.executeQuery();
			while (rs.next()) {
				NoteJavaBean jBean = new NoteJavaBean();
				jBean.setId(rs.getInt("id"));
				jBean.setTitle(rs.getString("title"));
				jBean.setAuthor(rs.getString("author"));
				jBean.setContent(rs.getString("content"));
				list.add(jBean);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return list;
	}
	
	public int getInsert(NoteJavaBean jBean) {
		int count = 0;
		String sql = "INSERT INTO note VALUES(?,?,?)";
		try {
			pre = con .prepareStatement(sql);
			pre.setString(1, jBean.getTitle());
			pre.setString(2, jBean.getAuthor());
			pre.setString(3, jBean.getContent());
			count = pre.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return count;
	}
	
	public int getDelete(int id) {
		int count = 0;
		String sql = "DELETE FROM note WHERE id=?";
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			count = pre.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		
		return count;
	}
	
	public NoteJavaBean getUpdateSearch(int id) {
		NoteJavaBean jBean = new NoteJavaBean();
		String sql = "SELECT id,title,author,content FROM note WHERE id=?";
		try {
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			rs =pre.executeQuery();
			if (rs.next()) {
				jBean.setId(rs.getInt("id"));
				jBean.setTitle(rs.getString("title"));
				jBean.setAuthor(rs.getString("author"));
				jBean.setContent(rs.getString("content"));
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		
		return jBean;
	}
	
	public int getUpdate(NoteJavaBean jBean) {
		int count = 0;
		String sql = "UPDATE note set title=?,author=?,content=?, WHERE id=?";
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, jBean.getTitle());
			pre.setString(2, jBean.getAuthor());
			pre.setString(3, jBean.getContent());
			pre.setInt(4, jBean.getId());
			count = pre.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return count;
	}
}