DIV CSS 佈局教程網

用 JavaScript 操作 Access 資料庫的完整實例解析
編輯:關於JavaScript     

這是一個相當早期的技術組合:透過 ActiveXObject 建立 ADODB 連線,讓 JavaScript 直接讀寫 .mdb 檔。今天看來限制很多,但它背後的分層思路——把連線管理、DOM 操作、日期格式化、Cookie、查詢鎖與 SQL 組裝各自剝離成獨立命名空間——仍然值得參考。以下先說明前提,再逐層拆解程式碼。

前提與限制(務必先讀)

1. 僅能在 Internet Explorer 且本機安全性設定允許 ActiveX 的環境下執行,其他瀏覽器一律不支援。

2. 需要安裝 Microsoft.Jet.OLEDB.4.0 提供者。

3. 把資料庫連線字串與 SQL 寫在前端,等於把整個資料庫交給使用者。這段程式碼只適合本地單機小工具,任何對外的網站都必須把資料存取移到伺服器端。

資料庫層:roc.db

var roc = roc || {};
roc.db = roc.db || {};

// 建立連線
roc.db.createDb = function(){
  var conn = new ActiveXObject("ADODB.Connection"),
      fso  = new ActiveXObject("Scripting.FileSystemObject"),
      connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fso.GetFile("./db/Sigma.mdb");
  conn.Open(connstr);
  roc.db.conn = conn;
  return roc.db.conn;
};

// 取得連線(有就重用,沒有就建立)
roc.db.getDb = function(){
  if( roc.db.conn ){ return roc.db.conn; }
  else { return roc.db.createDb(); }
};

// 關閉連線
roc.db.closeConn = function(){
  if( roc.db.conn ){ roc.db.conn.close(); roc.db.conn = null; }
};

// 取得結果集
roc.db.getRs = function( sqlStr ){
  var mysql = roc.dom.trim( sqlStr );
  if(mysql == ''){ return; }
  var rs = new ActiveXObject("ADODB.Recordset"), myConn = roc.db.getDb();
  rs.open( sqlStr, myConn );
  return rs;
};

// 關閉結果集
roc.db.closeRs = function( rs ){ rs.close(); rs = null; };

// 更新、插入
roc.db.execute = function( sqlStr ){
  var myConn = roc.db.getDb();
  myConn.execute( sqlStr );
  roc.db.closeConn();
};

DOM 工具層:roc.dom

roc.dom = roc.dom || {};

roc.dom.id = function( id ){
  if(typeof id == 'string' || id instanceof String) {
    return document.getElementById(id);
  } else if(id && id.nodeName && (id.nodeType == 1 || id.nodeType == 9)) {
    return id;
  }
  return null;
};

/**
 * @method tagName 依標籤取得指定 dom 元素
 * @param {String} tagName 元素標籤名稱
 * @param {HTMLElement} el 元素所屬的文件物件,預設為當前文檔
 * @return {HTMLElement}
 */
roc.dom.tagName = function(tagName, el) {
  var el = el || document;
  return el.getElementsByTagName(tagName);
};

// 刪除左右兩端的空格
roc.dom.trim = function (str) {
  return (str + '').replace(/(^\s*)|(\s*$)/g, "");
};

// 顯示目標元素
roc.dom.show = function (element) {
  element = roc.dom.id(element);
  element.style.display = '';
  return element;
};

// 隱藏目標元素
roc.dom.hide = function (element) {
  element = roc.dom.id(element);
  element.style.display = 'none';
  return element;
};

// 判斷元素是否含有指定 class
roc.dom.hasClass = function(el, className){
  var re = new RegExp('(^|\\s)' + className + '(\\s|$)');
  return re.test(el.className);
};

// 為元素加上 class
roc.dom.addClass = function(el, className){
  if(!roc.dom.hasClass(el, className)){ el.className = el.className + ' ' + className; }
};

// 為元素移除 class
roc.dom.removeClass = function(el, className){
  var re = new RegExp('(^|\\s)' + className + '(?:\\s|$)');
  el.className = el.className.replace(re, '$1');
};

// 跨瀏覽器綁定事件
roc.util = roc.util || {};
roc.util.addEvent = function(elem, type, fn, useCapture) {
  if(elem.addEventListener) {          // DOM2.0
    elem.addEventListener(type, fn, useCapture);
    return true;
  } else if(elem.attachEvent) {        // IE5+
    elem.attachEvent('on' + type, fn);
    return true;
  } else {                             // DOM 0
    elem['on' + type] = fn;
  }
};

日期與 Cookie:roc.date、roc.cookie

roc.date = roc.date || {};

// 把時間戳格式化為 YYYY-MM-DD HH:mm
roc.date.format = function(timestamp) {
  if(timestamp == '') return '';
  var str = '', temptime = new Date(Number(timestamp));
  str += temptime.getFullYear() + '-';
  str += temptime.getMonth() + 1 + '-';
  str += temptime.getDate() + ' ';
  str += String(temptime.getHours()).length > 1 ? (temptime.getHours() + ':') : ('0' + temptime.getHours() + ':');
  str += String(temptime.getMinutes()).length > 1 ? (temptime.getMinutes()) : ('0' + temptime.getMinutes());
  return str;
};

roc.cookie = roc.cookie || {};

/**
 * @method set 寫入 cookie
 * @param {String} name 鍵
 * @param {String} value 值
 * @param {String} expires 失效時間(小時)
 * @param {String} domain 網域
 * @param {String} path 路徑
 * @param {String} secure 是否僅限 https
 */
roc.cookie.set = function(name, value, expires, domain, path, secure) {
  var text = encodeURIComponent(value), date = expires;
  if(date && typeof date === 'number') {
    date = new Date();
    date.setTime(date.getTime() + (expires * 3600000));
  }
  if(date instanceof Date) { text += '; expires=' + date.toUTCString(); }
  if(domain) { text += '; domain=' + domain; }
  if(path)   { text += '; path=/' + path; } else { text += '; path=/'; }
  if(secure) { text += '; secure'; }
  document.cookie = name + '=' + text;
};

/**
 * @method get 讀取 cookie
 * @param {String} name 鍵
 */
roc.cookie.get = function(name) {
  var ret, m;
  if(name) {
    if((m = document.cookie.match('(?:^| )' + name + '(?:(=([^;]*))|;|$)'))) {
      ret = m[1] ? decodeURIComponent(m[1]) : '';
    }
  }
  return ret;
};

提示與輸入限制:roc.util

roc.util.loger = function( type, msg ){
  switch(type){
    case 'pop': alert(msg); break;
    case 'float': break;
    default: break;
  }
};

// 操作後的閃爍提示
roc.util.resultBlink = function( msg ){
  var $ = roc, opt = $.dom.id("optTip");
  $.util.toogle = $.util.toogle || 0;
  clearTimeout(roc.util.t);
  opt.innerHTML = msg;
  $.dom.show(opt);
  opt.className = "blink" + $.util.toogle % 2;
  $.util.toogle++;
  roc.util.t = setTimeout(function(){ $.dom.hide(opt); }, $.config.BLINK_DELAY);
};

// 限制輸入框只能填數字
roc.util.onlyInputNumber = function( id ){
  var $ = roc, num = $.dom.id( id );
  if( num.tagName.toLowerCase() != 'input' ){ return; }
  $.util.addEvent( num, 'keypress', function( e ){
    var e = e || window.event;
    if(e.keyCode >= 48 && e.keyCode <= 57){ return true; }
    return false;
  });
};

roc.config = roc.config || { BLINK_DELAY: 3000, SELECT_DELAY: 1000 };

查詢層:roc.search

這一層負責三件事:批量取出表單值並依型別加上引號、依條件組出 SQL、把 Recordset 轉成 HTML 表格字串。

roc.search = roc.search || {};

// 批量取得表單值,用於 insert
roc.search.getValues = function( e ){
  var $ = roc, allIsNull = true,
      wrapStr = function( num ){ return '"' + num + '"'; },
      vals = [];
  for(var i in e[0]){
    var v = $.dom.trim($.dom.id( e[0][i] ).value + '');
    if( v != ''){ allIsNull = false; }
    switch( e[1][i] ){
      case 'date': case 'text': vals.push( wrapStr(v) ); break;
      case 'num': vals.push( v ); break;
      default: break;
    }
  }
  if(allIsNull){ return false; }
  return vals.join(',');
};

// 組裝查詢 SQL
roc.search.getSelSql = function(){
  var $ = roc,
      addr = $.dom.trim($.dom.id("s_uaddr").value),
      phone = $.dom.trim($.dom.id("s_uphone").value),
      style = $.dom.trim($.dom.id("s_style").value),
      year = $.dom.trim($.dom.id("s_year").value),
      month = $.dom.trim($.dom.id("s_month").value),
      date = $.dom.trim($.dom.id("s_date").value),
      datetype = $.dom.trim($.dom.id("s_datetype").value),
      mysql = 'select * from inslist where 1=1 ',
      datetypeName = datetype == 0 ? 'selltime' : 'addtime';
  if( addr  != ''){ mysql += ' and uaddr like "%' + addr + '%"'; }
  if( phone != ''){ mysql += ' and uphone ="' + phone + '"'; }
  if( style != ''){ mysql += ' and typeid = ' + style; }
  if( year  != ''){ mysql += ' and year(' + datetypeName + ') = ' + year; }
  if( month != ''){ mysql += ' and month(' + datetypeName + ') = ' + month; }
  if( date  != ''){ mysql += ' and date(' + datetypeName + ') = ' + date; }
  return mysql;
};

// 查詢鎖,避免連續觸發造成重複請求
roc.search.getLock = function(){
  if( typeof roc.search.searchLock == 'undefined' ){ roc.search.setLock(false); }
  return roc.search.searchLock;
};
roc.search.setLock = function( key ){ roc.search.searchLock = key; };

// 執行查詢
roc.search.seeking = function(){
  if( !roc.search.getLock() ){ return; }
  var $ = roc, mySql = $.search.getSelSql();
  var html = $.search.getSel( mySql );
  $.search.setLock(false);
  $.dom.id("searchResult").innerHTML = html;
  $.util.resultBlink("查詢完畢");
};

// 把結果集轉為表格 HTML
roc.search.getSel = function( sqlStr ){
  var $ = roc, rs = $.db.getRs( sqlStr ),
      filtRs = function ( str ){ return ( str + '' ) == 'null' ? '' : str; },
      num = 1;
  total_receive = 0; total_prize = 0;
  var html = "<table class='list' id='memoryDetails'>"
    + "<tr class='secondRow doNotFilter'>"
    + " <th>序號</th><th>用戶地址</th><th>用戶電話</th>"
    + " <th>型號</th><th>代收款</th><th>貨款</th><th>余額</th>"
    + " <th>安裝人</th><th>銷售電話</th><th>備注</th>"
    + " <th>銷售日期</th><th>記錄時間</th>"
    + "</tr>";
  while(!rs.EOF) {
    var id = num,
        uaddr = filtRs(rs.Fields("uaddr")), uphone = filtRs(rs.Fields("uphone")),
        typeid = filtRs(rs.Fields("typeid")), received = filtRs(rs.Fields("received")),
        prize = filtRs(rs.Fields("prize")), unreceived = filtRs(rs.Fields("unreceived")),
        installerid = filtRs(rs.Fields("installerid")), sellerid = filtRs(rs.Fields("sellerid")),
        remark = filtRs(rs.Fields("remark")),
        selltime = $.date.format(filtRs(rs.Fields("selltime"))),
        addtime = $.date.format(filtRs(rs.Fields("addtime")));
    html += "<tr><td>" + id + "</td><td>" + uaddr + "</td><td>" + uphone + "</td>"
         +  "<td>" + typeid + "</td><td>" + received + "</td><td>" + prize + "</td>"
         +  "<td>" + unreceived + "</td><td>" + installerid + "</td><td>" + sellerid + "</td>"
         +  "<td>" + remark + "</td><td>" + selltime + "</td><td>" + addtime + "</td></tr>";
    total_receive += Number(received) || 0;
    total_prize   += Number(prize) || 0;
    num++;
    rs.moveNext();
  }
  html += "<tr class='total'><td></td><td>合計</td>"
       +  "<td>" + total_receive + "</td><td>" + total_prize + "</td>"
       +  "<td>" + (total_prize - total_receive) + "</td></tr></table>";
  $.db.closeRs(rs);
  $.db.closeConn();
  return html;
};

// 新增安裝單
roc.search.addEls = [
  [ "uaddr", "uphone", "typeid", "received", "prize", "unreceived", "installerid", "sellerid", "remark", "selltime" ],
  [ 'text', 'text', 'num', 'num', 'num', 'num', 'num', 'num', 'text', 'date' ]
];

roc.search.insert = function(){
  var $ = roc, getV = $.search.getValues( $.search.addEls );
  if(!getV){ $.util.loger('pop','請填寫資訊後再保存!'); return; }
  var sqlStr = 'insert into inslist (uaddr,uphone,typeid,received,prize,unreceived,installerid,sellerid,remark,selltime) values (' + getV + ')';
  $.db.execute( sqlStr );
  $.util.resultBlink('保存安裝單成功');
};

樣式與初始化

// 貨物型號操作
roc.tstyle = roc.tstyle || {};
roc.tstyle.els = [ ['tname','tprize','tdesc'], ['text','text','text'] ];

roc.tstyle.insert = function(){
  var $ = roc, getV = $.search.getValues( $.tstyle.els );
  if( !getV ){ $.util.loger('pop','請填寫資訊後再保存!'); return; }
  var sqlStr = 'insert into type ( tname, tprize, tdesc ) values (' + getV + ')';
  $.db.execute( sqlStr );
  $.util.resultBlink('保存成功!');
  $.util.flushInput($.tstyle.els);
};

roc.util.flushInput = function( els ){
  var $ = roc;
  for(var i = 0; i < els.length; i++){
    $.dom.id(els[i] + '').value = '';
  }
};

// 取得類型清單並塞進下拉選單
roc.tstyle.getStyle = function( optId ){
  var $ = roc, mySql = 'select * from type where isdel = 0 ',
      rs = $.db.getRs( mySql ),
      filtRs = function ( str ){ return (str + '') == 'null' ? '' : str; },
      myOpt = $.dom.id( optId ), optIndex = 1;
  while(! rs.EOF ){
    var id = filtRs(rs.Fields('id')), prize = filtRs(rs.Fields('tprize')),
        name = filtRs(rs.Fields('tname')), desc = filtRs(rs.Fields('tdesc'));
    myOpt.options[optIndex] = new Option( name, id );
    myOpt.options[optIndex].title = '價格:' + prize + ' | 描述:' + desc;
    optIndex++;
    rs.moveNext();
  }
  $.db.closeRs(rs);
  $.db.closeConn();
};

;(function(){
  var $ = roc;
  $.dom.id("save").onclick = function(){ $.search.insert(); };
  $.dom.id("searchBtn").onclick = function(){ $.search.seeking(); };

  // 初始化年份下拉
  for(var i = 0; i <= 10; i++){
    $.dom.id("s_year").options[i] = new Option(2010 + i, 2010 + i);
    if( (2010 + i) + '' == (new Date()).getYear() ){ $.dom.id("s_year").options[i].selected = true; }
  }
  // 初始化月份下拉
  for(var i = 1; i <= 12; i++){ $.dom.id("s_month").options[i] = new Option(i, i); }

  // 條件變動時自動觸發查詢(含 debounce)
  $.dom.s_fields = ["s_uaddr","s_uphone","s_style","s_datetype",'s_year','s_month','s_date'];
  for(var i = 0; i < $.dom.s_fields.length; i++){
    var f = $.dom.s_fields[i];
    $.dom.id(f).onpropertychange = function(){
      if(event.propertyName == 'value'){
        $.search.setLock(true);
        if($.search.t){ clearTimeout($.search.t); }
        $.search.t = setTimeout(function(){ $.search.seeking(); }, $.config.SELECT_DELAY);
      }
    };
    $.dom.id(f).onfocus = function(){ $.dom.addClass(this, "focusit"); };
    $.dom.id(f).onblur  = function(){ $.dom.removeClass(this, "focusit"); };
  }

  $.dom.id('saveType').onclick = function(){ $.tstyle.insert(); };
  $.tstyle.getStyle('typeid');
  $.tstyle.getStyle('s_style');

  // 導航頁籤切換
  var lis = $.dom.tagName('li', $.dom.id("ulNav"));
  for(var i = 0; i < lis.length; i++){
    $.dom.hide( $.dom.id(lis[i].id + 'Div'));
    lis[i].onclick = function(){
      for(var n = 0; n < lis.length; n++){
        $.dom.removeClass(lis[n], 'click');
        $.dom.hide( $.dom.id(lis[n].id + 'Div'));
      }
      $.dom.show( $.dom.id(this.id + "Div"));
      $.dom.addClass(this, "click");
      $.cookie.set('showWhichDiv', this.id);
    };
  }
  // 依 Cookie 還原上次所在頁籤
  var showWhichDiv = $.cookie.get("showWhichDiv") || "searchList";
  $.dom.addClass($.dom.id(showWhichDiv), "click");
  $.dom.show($.dom.id(showWhichDiv + 'Div'));

  // 數字欄位限制
  var numFields = ['s_uphone','s_date','uphone','received','prize','unreceived','installerid'];
  for( var i = 0; i < numFields.length; i++ ){ $.util.onlyInputNumber( numFields[i] ); }
})();

回顧:這份程式碼值得借鑑的地方

撇開 ActiveX 的先天限制,它在組織方式上有幾個做法至今仍受用:以命名空間分層(db / dom / date / cookie / util / search),把「查詢鎖 + 防抖」用來抑制 onpropertychange 的高頻觸發,以及用 Cookie 記住使用者上次停留的頁籤。若要在今日的專案裡重現類似功能,把資料層換成 fetch 呼叫後端 API、把 DOM 操作交給框架,其餘結構幾乎可以原封不動沿用。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved