toconn.java

用J2EE开发的网站,可以应用到图书馆,图书超市

package ksnb;

import java.sql.*;
import javax.sql.*;
import javax.servlet.http.*;
import java.text.*;
import java.util.*;

public class ToConn {
    private conn c = null;
    private Connection con = null;
    private Statement stm = null;
    private ResultSet rs = null;
    private String strSql=null;
    private String textfield=null;
    private String Date_time=null;
    private Collection ret=null;
    java.util.Date st2=null;
    int answer=0;
   String select;
    public ToConn() {
        //连接数据库

        try {
            c = new conn();
            con = c.getConncetion();
        } catch (connException ex) {
        }

    }
    //解决乱码
      private static String convert( String str )
      {
        try
        {
          byte[] bytesStr=str.getBytes( "ISO-8859-1" ) ;
          return new String( bytesStr, "gb2312" ) ;
        }
        catch( Exception ex)
        {
            return str ;
        }
      }

    public void SelectOpinion(HttpServletRequest request)
    { //从页面中读取数据
        //System.out.println("查询");
            ret = new ArrayList();
            textfield = convert((String) request.getParameter("textfiled").trim());
            select = convert((String) request.getParameter("select").trim());
            int answer = Integer.parseInt(request.getParameter("Answer"));
            Date_time = (String) request.getParameter("Date_time").trim();
            System.out.println(textfield);
            System.out.println(select);
            System.out.println(answer);
            System.out.println(Date_time);
            if (Date_time != "") { //规范日期格式，与数据库匹配
                Date_time = Date_time + "01:00:00";
            }
            //根据页面上传递的参数判断执行的SQL语句
            if (textfield.equals("") && Date_time == "") {
                strSql = "select * from LeaveWord where Level =" + answer;
            }
            else if (textfield == "" && Date_time != "") {
                try {
                    //将字符转化为日期类型
                    SimpleDateFormat bartDateFormat = new SimpleDateFormat(
                            "yyyy-mm-dd hh:mm:ss");
                     st2= bartDateFormat.parse(Date_time);
                    strSql = "select *from LeaveWord where Level =" + answer +
                             " and Date_time>" + st2;
                } catch (Exception e) {
                    System.out.println(e.toString());
                }
            } else if (textfield != "" && Date_time == "") {
                strSql = "select * from LeaveWord where " + select + " like '%" +
                         textfield + "%' and Level =" + answer;
            }
            System.out.println(strSql);
            strSql = strSql + "order by Date_time desc";
        //执行SQL语句
        try{
                stm = con.createStatement();
                rs=stm.executeQuery(strSql);
                while(rs.next())
                {
                    Bean b=new Bean();
                    b.setId((int)rs.getInt("Id"));
                    b.setAuthor((String)rs.getString("Author"));
                    b.setContent((String)rs.getString("Content"));
                    b.setAnswer((String)rs.getString("Answer"));
                    b.setDatetime(rs.getDate("Date_time"));
                    b.setLevel((int)rs.getInt("Level"));
                    ret.add(b);
                }
                rs.close();
                stm.close();
        }
        catch(Exception e){System.out.println(e.toString());}
        request.setAttribute("select",ret);
    }
    public void Update(HttpServletRequest request)//更新记录
    {

        //从页面中取值
        int id=Integer.parseInt(request.getParameter("cmd1"));
        int level=Integer.parseInt(request.getParameter("level"));
        String answer=convert((String)request.getParameter("answer"));
        //System.out.println(level);
        //判断值是否合法
        if(level!=1 || level!=0)
        {
            level=0;
        }
        strSql="update LeaveWord set Answer='"+ answer + "' ,Level='"+level+"' where id='"+id+"'";
        //执行SQL语句
        //System.out.println(strSql);
        try
        {
            stm=con.createStatement();
            stm.executeUpdate(strSql);
            request.setAttribute("record","update");
            stm.close();
        }catch(Exception e)
        {
            System.out.println(e.toString());
        }

    }
    public void Delete(HttpServletRequest request)
    {
        int id=Integer.parseInt(request.getParameter("cmd1"));
        strSql="delete from LeaveWord where id='"+id+"'";
        System.out.println("delete ok");
        try
        {
            stm=con.createStatement();
            stm.executeUpdate(strSql);
            request.setAttribute("record","delete");
            stm.close();
        }catch(Exception e)
        {
            System.out.println(e.toString());
        }
    }
    public void insert(HttpServletRequest request)
    {
        String sql="insert into LeaveWord(Author,Content,Answer,Date_time,Level) values(?,?,?,?,?)";
        String username=convert((String)request.getParameter("username"));
        String content=convert((String)request.getParameter("content"));
        String answer=convert((String)request.getParameter("answer"));
        int level=Integer.parseInt(request.getParameter("level"));
        try
         {
             //写一个DATETIME对象
             DateFormat dateFormatter = DateFormat.getDateTimeInstance(
                     DateFormat.LONG, DateFormat.LONG);
             // 取得系统时间
             java.util.Date utilDate = new java.util.Date();
             // 转换成数据库时间
             java.sql.Date date = new java.sql.Date(utilDate.getTime());
              PreparedStatement stm=con.prepareStatement(sql);
              stm.setString(1,username);
              stm.setString(2,content);
              stm.setString(3,answer);
              stm.setDate(4,date);
              stm.setInt(5,level);
              stm.executeUpdate();
              stm.close();
              request.setAttribute("state","success");
         }catch(Exception e)
         {
             request.setAttribute("state","lost");
             System.out.println(e.toString());
         }
    }

}