Spring jdbc 操作实例

package com.szxhdz.dao;

import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.szxhdz.beans.MsgMessage;
import com.szxhdz.util.Factory;

public class MessageDao {
    private static MessageDao instance =null;
    public static synchronized MessageDao getInstance(){
        if(instance==null)
        instance = (MessageDao)Factory.getBean("messageDao");
        return instance;
    }
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int getCount() {
        int count = getJdbcTemplate().queryForInt(
                "select count(*) from msg_message");
        return count;
    }
    public int getCount(String sql) {
        int count = getJdbcTemplate().queryForInt(sql);
        return count;
    }

    public int getNextval() {
        int nextval = getJdbcTemplate().queryForInt(
                "select get_id.nextval from dual");
        return nextval;
    }

    private class MessageRowMapper implements RowMapper {

        public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {
            MsgMessage msgMessage = new MsgMessage();
             try {
                Blob blob = rs.getBlob("m_content");
                int i = (int) blob.length();
                byte[] bytes = blob.getBytes(1, i);
                msgMessage.setMcontent(new String(bytes, "GBK"));
            } catch (Exception e) {
                e.printStackTrace();
                // TODO: handle exception
            }
            msgMessage.setMdatetime(rs.getString("m_datetime"));
            msgMessage.setMdeptid(new Integer(rs.getInt("M_DEPTID")));
            msgMessage.setMfromip(rs.getString("M_FROMIP"));
            msgMessage.setMhidetitle(new Integer(rs.getInt("M_HIDETITLE")));
            msgMessage.setMid(new Integer(rs.getInt("M_ID")));
            msgMessage.setMkeywords(rs.getString("M_KEYWORDS"));
            msgMessage.setMkindid(new Integer(rs.getInt("M_KINDID")));
            msgMessage.setMneedsignin(new Integer(rs.getInt("M_NEEDSIGNIN")));
            msgMessage.setMoriginalfilename(rs.getString("M_ORIGINALFILENAME"));
            msgMessage.setMreadtimes(new Integer(rs.getInt("M_READTIMES")));
            msgMessage.setMreviewtimes(new Integer(rs.getInt("M_REVIEWTIMES")));
            msgMessage.setMsavefilename(rs.getString("M_SAVEFILENAME"));
            msgMessage.setMsavepathfilename(rs.getString("M_SAVEPATHFILENAME"));
            msgMessage.setMsource(rs.getString("M_SOURCE"));
            msgMessage.setMstate(new Integer(rs.getInt("M_STATE")));
            msgMessage.setMsummary(rs.getString("M_SUMMARY"));
            msgMessage.setMtimestampposition(new Integer(rs.getInt("M_TIMESTAMPPOSITION")));
            msgMessage.setMtitle(rs.getString("M_TITLE"));
            msgMessage.setMtitleimgfilename(rs.getString("M_TITLEIMGFILENAME"));
            msgMessage.setMupdatetime(rs.getString("M_UPDATETIME"));
            msgMessage.setMuserid(new Integer(rs.getInt("M_USERID")));

            return msgMessage;
        }
    }

    public MsgMessage getMsgMessage(int id) {
        List list = getJdbcTemplate().query(
                "select * from msg_message where m_id=?",
                new Object[] { new Integer(id) }, new MessageRowMapper());
        MsgMessage msgMessage = (MsgMessage) list.get(0);
        return msgMessage;
    }

    public List getMsgMessages() {
        List list = getJdbcTemplate().query("select * from msg_Message order by m_id desc ",
                new MessageRowMapper());
        return list;
    }
    /**
     *
     * @param kinds   信息类别
     * @param curPage　当前页
     * @param pageSize　每页记录数
     * @return
     */
    public List getMsgMessages(String sql,int startRow,int endRow) {
   //     System.out.println(sql);
        StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
        pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
        pagingSelect.append(sql);
        pagingSelect.append(" ) row_ where rownum <= "+endRow+") where rownum_ >= "+startRow+"");
        List list = getJdbcTemplate().query(pagingSelect.toString(), new MessageRowMapper());
        return list;
    }
    /**
     * 审核
     * @param m_id
     */

    public void Auditing(String m_id){
        String sql="update msg_message set m_state=1 where m_id in("+m_id+")";
        getJdbcTemplate().update(sql);
    }
    /**
     * 反审核
     * @param m_id
     */
    public void UnAuditing(int m_id){
        String sql="update msg_message set m_state=0 where m_id="+m_id;
        getJdbcTemplate().update(sql);
    }
    public int getState(int m_id){
        Integer state =(Integer)getJdbcTemplate().queryForObject("select m_state from msg_message t where m_state=?",new Object[]{new Integer(m_id)},Integer.class);
        return state.intValue();
    }

/**
 * 删除所选的记录
 */
    public void deleteMsgMessage(String ids) {
        String sql = "delete from msg_Message where m_id in(" + ids + ")";
        getJdbcTemplate().update(sql);
    }
    /**
     * 所选记录移入回收站
     * @param ids
     */
    public void moveToTrash(String ids) {
        String sql = "update msg_Message set m_state=-1 where m_id in(" + ids + ")";
        getJdbcTemplate().update(sql);
    }
    /**
     * 得到要签收的信息
     * @param u_id
     * @return
     */
    public List getMessagesSign(int u_id){
        String  sql="select * from msg_message where M_STATE=1 and m_Id in (select distinct(S_MSGID) from msg_sign_in where S_SIGNINDATE is null and s_userid="+u_id+")";
        List list = getJdbcTemplate().query(sql, new MessageRowMapper());
        return list;
    }
    /**
     * 得到这条消息的所有签收用户的签收状态
     * @param m_id
     * @return
     */
    public List getSignMessageByMsgId(String m_id){
        String sql="select u_name ,s.* from msg_user u,(select i.s_userid,i.s_msgid,i.s_limitdate,trunc(i.s_signindate) as s_signindate,i.s_remark from msg_sign_in i where s_msgId=" + m_id  + ") s where s.s_userid=u.u_id";
        List list = getJdbcTemplate().queryForList(sql);
        return list;
    }
    public void deleteHisMessage(String ids) {
        String sql = "delete from msg_message_His where m_id in(" + ids + ")";
        getJdbcTemplate().update(sql);
    }
}