tlinkdao.java

数学网

package math.tlink.dao;

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

import javax.sql.DataSource;



import math.article.model.Article;
import math.dao.DAO;
import math.tlink.model.Tlink;
import math.tlink.model.TlinkList;

public class TlinkDAO extends DAO {

	public TlinkDAO(DataSource ds) {
		super(ds);
		// TODO Auto-generated constructor stub
	}
	
	public void insert(Tlink tlink) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			
			conn = ds.getConnection();
			String sql = "insert into  tlink (tlink_name,tlink_link,tlink_talkerid,tlink_time,tlink_content,tlink_image) values (?,?,?,date(),?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tlink.getTlink_name());
			pstmt.setString(2, tlink.getTlink_link());
			pstmt.setInt(3, tlink.getTlink_talkerid());
			pstmt.setString(4,tlink.getTlink_content());
			pstmt.setString(5,tlink.getTlink_image());
			pstmt.executeUpdate();
			
			close(pstmt);
		}
		catch (SQLException e) {
			
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	/////////////////////////////////////////////////////////////////////////////////
	public List list(int offset, int limit, String condition)
	throws SQLException {
		ArrayList list = new ArrayList();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from tlink " + condition
					+ " order by tlink_time desc";
			pstmt = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		
			rs = pstmt.executeQuery();
			if (offset > 0) {
				rs.absolute(offset);
			}
			int recCount = 0;
			while ((recCount++ < limit) && rs.next()) {
				TlinkList tlink = new TlinkList();
				tlink.setTlink_id(rs.getInt("tlink_id"));
				tlink.setTlink_name(rs.getString("tlink_name"));
				tlink.setTlink_link(rs.getString("tlink_link"));
				tlink.setTlink_talkerid(rs.getInt("tlink_talkerid"));
				tlink.setTlink_time(rs.getDate("tlink_time"));
				tlink.setTlink_content(rs.getString("tlink_content"));
				tlink.setTlink_image(rs.getString("tlink_image"));
				tlink.setPageroffset(offset);
				list.add(tlink);
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return list;
		}
	////////////////////////////////////////////////////////////////////////////////
	public void update(Tlink tlink) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
		 {
				String sql = "update  tlink  set tlink_name=?,tlink_link=?,tlink_talkerid=?,tlink_time=date(),tlink_content=?,tlink_image=? where tlink_id = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tlink.getTlink_name());
				pstmt.setString(2, tlink.getTlink_link());
				pstmt.setInt(3, tlink.getTlink_talkerid());
				pstmt.setString(4,tlink.getTlink_content());
				pstmt.setString(5,tlink.getTlink_image());
				pstmt.setInt(6, tlink.getTlink_id());
				pstmt.executeUpdate();
			
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	////////////////////////////////////////////////////////////////////////////////
	
	public void delete(Tlink tlink) throws SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			String sql = "delete from  tlink where tlink_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, tlink.getTlink_id());
			pstmt.executeUpdate();

			close(pstmt);
		} catch (SQLException e) {
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	
	////////////////////////////////////////////////////////////////////////////////
	
	public Tlink select(int id) throws 
	SQLException {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Tlink tlink = new Tlink();
		try {
			conn = ds.getConnection();
			String sql = "SELECT * from tlink where tlink_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();

			if (rs.next()) {

				tlink.setTlink_id(id);
				tlink.setTlink_link(rs.getString("tlink_link"));
				tlink.setTlink_name(rs.getString("tlink_name"));
				tlink.setTlink_talkerid(rs.getInt("tlink_talkerid"));
				tlink.setTlink_time(rs.getDate("tlink_time"));
				tlink.setTlink_content(rs.getString("tlink_content"));
				tlink.setTlink_image(rs.getString("tlink_image"));
				
			}
			close(rs);
			close(pstmt);
		} catch (SQLException e) {
			close(rs);
			close(pstmt);
			rollback(conn);
			e.printStackTrace();
		} finally {
			close(conn);
		}
		return tlink;
	}
	
	
	
	//////////////////////////////////////////////////////////////////////////////////
}