mssqlmobiledao.java

来自「一个免费wap站」· Java 代码 · 共 174 行

package com.eline.wap.cmi.dao;

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

import com.eline.wap.catalog.exceptions.CatalogDAOSysException;
import com.eline.wap.cmi.exceptions.CMIDAOSysException;
import com.eline.wap.cmi.model.MobileCapability;
import com.eline.wap.cmi.model.MobileCapabilityCondition;
import com.eline.wap.common.jdbc.DBSqlManager;
import com.eline.wap.common.model.Page;
import com.eline.wap.common.util.AppLogger;

/**
 * 
 * @author Lucifer
 *
 */
public class MSSqlMobileDAO extends DBSqlManager implements MobileDAO {

	/**
	 * @deprecated
	 * @param userAgent
	 * @return
	 * @throws CMIDAOSysException
	 */
	public ArrayList getBrowserCapabilitiesByUserAgent(String userAgent)
			throws CMIDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;

		ArrayList items = new ArrayList();
		
		String sql = "SELECT IndexID, UserAgent, DeviceModel, DeviceManufacturer, ScreenPixelsHeight, ScreenPixelsWidth, ScreenBitDepth, PreferredRenderingMime, PreferredImageMime, PreferredRingMime, SupportedImages, SupportedRings, PreferredRingChords, Cookies, IsColor "
			+ "FROM TAB_MOBILECAP WHERE ? LIKE UserAgent + '%'";
		System.out.println("getBrowserCapabilitiesByUserAgent().sql=" + sql);
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, userAgent + "%");
			System.out.println("userAgent=" + userAgent);

			rst = stmt.executeQuery();
			
			while (rst.next()) {
				MobileCapability capability = populateCapabilityFormResultSet(rst);
				items.add(capability);
			}
		} catch (Exception e) {
			AppLogger.debug("MSSqlMobileDAO.getBrowserCapabilitiesByUserAgent().Exception : " + e.getMessage());
			throw new CMIDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeResultSet(rst);
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CMIDAOSysException(e.getMessage());
			}
		}

		return items;
	}

	protected static String[] SEARCH_BROWSERCAPABILITIES_STATEMENT_FRAGMENTS = {
		"SELECT IndexID, UserAgent, DeviceModel, DeviceManufacturer, ScreenPixelsHeight, ScreenPixelsWidth, ScreenBitDepth, PreferredRenderingMime, PreferredImageMime, PreferredRingMime, SupportedImages, SupportedRings, PreferredRingChords, Cookies, IsColor FROM TAB_MOBILECAP WHERE 1 = 1 ",
		"AND IndexID = ? ",
		"AND ? LIKE UserAgent + '%' ",
		"AND DeviceManufacturer = ? ",
		"AND DeviceModel = ? "
	};

	public Page searchBrowserCapabilities(MobileCapabilityCondition condition, int start, int count) throws CMIDAOSysException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rst = null;
		Page page = new Page();

		String sql = SEARCH_BROWSERCAPABILITIES_STATEMENT_FRAGMENTS[0];
		if (condition != null) {
			if (condition.getIndexId() > 0)
				sql += SEARCH_BROWSERCAPABILITIES_STATEMENT_FRAGMENTS[1];
			if (condition.getUserAgent() != null)
				sql += SEARCH_BROWSERCAPABILITIES_STATEMENT_FRAGMENTS[2];
			if (condition.getDeviceManufacturer() != null)
				sql += SEARCH_BROWSERCAPABILITIES_STATEMENT_FRAGMENTS[3];
			if (condition.getDeviceModel() != null)
				sql += SEARCH_BROWSERCAPABILITIES_STATEMENT_FRAGMENTS[4];
		}
		sql += "ORDER BY IndexID DESC";
		
		AppLogger.debug("MSSqlMobileDAO.searchBrowserCapabilities().sql=" + sql);
		
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareStatement(sql);
			
			// Set condition parameters
			if (condition != null) {
				int index = 1;
				if (condition.getIndexId() > 0)
					stmt.setInt(index++, condition.getIndexId());
				if (condition.getUserAgent() != null)
					stmt.setString(index++, condition.getUserAgent());
				if (condition.getDeviceManufacturer() != null)
					stmt.setString(index++, condition.getDeviceManufacturer());
				if (condition.getDeviceModel() != null)
					stmt.setString(index++, condition.getDeviceModel());
			}
			
			rst = stmt.executeQuery();

        	int totalRecords = 0;
        	for (totalRecords = 0; rst.next(); totalRecords ++) {
        		if ((totalRecords >= start) && (totalRecords < (start + count))) {
        			MobileCapability item = populateCapabilityFormResultSet(rst);
        			page.getItems().add(item);
        		}
        	}
        	page.setTotalRecords(totalRecords);
		} catch (Exception e) {
			AppLogger.debug("MSSqlMobileDAO.searchBrowserCapabilities().e.getMessage()=" + e.getMessage());
			e.printStackTrace();

			throw new CatalogDAOSysException(e.getMessage());
		} finally {
			try {
				super.closeResultSet(rst);
				super.closeStatement(stmt);
				super.closeConnection(conn);
			} catch (Exception e) {
				throw new CatalogDAOSysException(e.getMessage());
			}
		}
		return page;
	}
	
	/**
	 * 
	 * @param rst
	 * @return
	 * @throws SQLException
	 */
	private MobileCapability populateCapabilityFormResultSet(ResultSet rst) throws SQLException {
		MobileCapability item = new MobileCapability();
		try {
			item.setIndexId(rst.getInt("IndexID"));
			item.setUserAgent(rst.getString("UserAgent"));
			item.setScreenPixelsWidth(rst.getInt("ScreenPixelsWidth"));
			item.setScreenPixelsHeight(rst.getInt("ScreenPixelsHeight"));
			item.setScreenBitDepth(rst.getInt("ScreenBitDepth"));
			item.setPreferredRenderingMime(rst.getString("PreferredRenderingMime"));
			item.setPreferredImageMime(rst.getString("PreferredImageMime"));
			item.setPreferredRingMime(rst.getString("PreferredRingMime"));
			item.setDeviceModel(rst.getString("DeviceModel"));
			item.setDeviceManufacturer(rst.getString("DeviceManufacturer"));
			item.setSupportedImages(rst.getString("SupportedImages").split(";"));
			item.setSupportedRings(rst.getString("SupportedRings").split(";"));
			item.setPreferredRingChords(rst.getInt("PreferredRingChords"));
			item.setCookies(rst.getBoolean("Cookies"));
			item.setColor(rst.getBoolean("IsColor"));
		} catch (SQLException e) {
			AppLogger.error("MSSqlMobileDAO.populateCapabilityFormResultSet() SQLException : " + e.getMessage());
			throw e;
		}
		return item;
	}
}