articletypedao.java

本程序是作者用JBuilder 开发的一个娱乐系统模块.

package com.singnet.article.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.singnet.article.ArticleTypeInfo;
import com.singnet.util.Format;
import com.singnet.util.Pager;

public class ArticleTypeDao {
    public ArticleTypeDao() {
    }

    /**
     *
     * @return Connection
     * @throws Exception
     */
    public Connection getConnection() throws Exception {
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:/MySqlDS");
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = ds.getConnection();
        } catch (SQLException sqlEx) {
            System.out.println("Error connect to pool.");
        }
        return conn;
    }

    /**
     *
     * @param videotypeinfo ArticleTypeInfo
     * @return boolean
     * @throws Exception
     */
    public boolean addArticleType(ArticleTypeInfo articletypeinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        
        String str_sql =" insert into articletype(typename,remark) values ('"
        	+articletypeinfo.getTypename()+"','"
        +articletypeinfo.getRemark()+"')";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql);
            re = true;
        } catch (SQLException e) {
            e.printStackTrace();
            re = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return re;
    }

    /**
     *
     * @param articletypeinfo ArticleTypeInfo
     * @return boolean
     * @throws Exception
     */
    public boolean editArticleType(ArticleTypeInfo articletypeinfo) throws
            Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql = " update articletype set typename='"
        	+articletypeinfo.getTypename()+"',remark='"
        +articletypeinfo.getRemark()+"' where id="
        +articletypeinfo.getId()+"";

        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql);
            re = true;
        } catch (SQLException e) {
            e.printStackTrace();
            re = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return re;
    }

    /**
     *
     * @param id String
     * @return boolean
     * @throws Exception
     */
    public boolean deleteArticleType(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        boolean re = false;
        String str_sql1="delete from article where articletypeid="+id+"";
        String str_sql2 = "delete  from articleclass where articletypeid=" + id + "";
          String str_sql = "delete  from articletype where id=" + id + "";
      
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            stmt.executeUpdate(str_sql1);//删除视频记录
            stmt.executeUpdate(str_sql);
            stmt.executeUpdate(str_sql2);
            
            re = true;
        } catch (SQLException e) {
            e.printStackTrace();
            re = false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return re;
    }

    /**
     *
     * @param id String
     * @return ArticleTypeInfo
     * @throws Exception
     */
    public ArticleTypeInfo getArticleTypeByid(String id) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ArticleTypeInfo articletypeinfo = new ArticleTypeInfo();
        ResultSet rs = null;
        if(id==null||id.equals("")||id.length()<1||!Format.isNumber(id))
        {
        	id="0";
        }
        String str_sql = " select * from articletype where id="+id+"";
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	articletypeinfo.setId(rs.getString("id"));
            	articletypeinfo.setRemark(rs.getString("remark"));
            	articletypeinfo.setTypename(rs.getString("typename"));
            	
                 
            }else
            {
            	articletypeinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return articletypeinfo;
    }
    public ArticleTypeInfo getArticleTypeByTypename(String typename) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        ArticleTypeInfo articletypeinfo = new ArticleTypeInfo();
        ResultSet rs = null;
        if(typename==null||typename.equals("")||typename.length()<1)
        {
        	typename="0";
        }
        String str_sql = " select * from articletype where typename='"+typename+"'";
         
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(str_sql);
            if (rs.next()) {
            	articletypeinfo.setId(rs.getString("id"));
            	articletypeinfo.setRemark(rs.getString("remark"));
            	articletypeinfo.setTypename(rs.getString("typename"));
            	
                 
            }else
            {
            	articletypeinfo=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return articletypeinfo;
    }
    public ArrayList queryArticleTypeInfo(Pager pager) throws Exception {
        ArrayList list = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(pager.getQueryCase());
            list = new ArrayList();
            while (rs.next()) {
                ArticleTypeInfo articletypeinfo = new ArticleTypeInfo();
                
                articletypeinfo.setId(rs.getString("id"));
            	articletypeinfo.setRemark(rs.getString("remark"));
            	articletypeinfo.setTypename(rs.getString("typename"));
            	
                list.add(articletypeinfo);

            }
        } catch (SQLException e) {
            e.printStackTrace();

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception sqlEx) {
                }
            }
        }
        return list;
    }
    /**
     *
     * @param pager Pager
     * @return int
     * @throws Exception
     */
    public int getTotal(Pager pager) throws Exception {
           Connection conn = null;
           Statement stmt = null;
           ResultSet rs = null;
           int total = 0;
           try {
               conn = getConnection();
               stmt = conn.createStatement();
               rs = stmt.executeQuery(pager.getQueryTotal());
               if (rs.next()) {
                   total = rs.getInt("t");
               }
           } catch (SQLException e) {
               e.printStackTrace();
               total = 0;

           } finally {
               if (conn != null) {
                   try {
                       conn.close();
                   } catch (Exception sqlEx) {
                   }
               }
           }
           return total;
       }

}