SQL语句在JavaScript中的应用_PHP_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > PHP > SQL语句在JavaScript中的应用

SQL语句在JavaScript中的应用

 2010/11/2 17:34:46    程序员俱乐部  我要评论(0)
  • 摘要:最近一直在用javascript在做项目可是做着做着感觉很多功能代码都是重复的。比如对javascript数组的排序还有对数组数据的删选以及分组所以,后来兴致以上来。一发不可收拾。写了一个能在javascript中应用的SQL库关于JSQL的开源问题其实目前我已经在考虑这个问题如果开源了,能有更多的好朋友一起来维护JSQL目前的大概结构是这样的YESBRAIN-命名空间|JSQL-就是本类库啦!|Memory-JSQL在Objectsarray中的应用封装|Server
  • 标签:SQL语句

最近一直在用javascript在做项目 
可是做着做着 
感觉很多功能代码都是重复的。 

比如对javascript数组的排序 
还有对数组数据的删选以及分组 

所以,后来兴致以上来。 
一发不可收拾。 

写了一个能在javascript中应用的 SQL 库 

关于JSQL的开源问题

其实目前我已经在考虑这个问题

如果开源了,能有更多的好朋友一起来维护

 JSQL 目前的大概结构是这样的

 YESBRAIN - 命名空间
          |
         JSQL - 就是本类库啦!
              |
             Memory - JSQL 在 Objects array 中的应用封装
              |
             Server - JSQL 在 远端 SQL server 中的应用封装
              |
             Client - JSQL 在 浏览器 sqlite 中的应用封装
              |
             DOM - JSQL 在 对 W3C DOM 的的应用封装

有兴趣的朋友可以
QQ: 85302520 联系我
或者 Email : gongji at qq dot com

已经开放了
SVN checkout 地址:http://code.google.com/p/jsql-javascript/source/checkout

里面有几个例子和全部JSQL得源代码


后来又想,怎么不能用javascript直接连接数据库呢? 
又做了一个javascript直连Sql数据的类库 

后来,又想到其实还可以用SQL语句来操作HTML DOM模型 

再再再后来,又看到了HTML5中对web DB的实现 
所以对webDB,就是chrome和safari中的sqlite的封装 

于是乎就有了: 

1.从服务器上获取数据、执行SQL操作: 

 _SQLPROXYURL_ = 'SQLProxy.php';
 _SQLSERVERHOST_ = 'localhost';
 _SQLUSERNAME_ = 'root';
 _SQLPASSWORD_ = '';
 _SQLDATABASE_ = 'HotelManageMent';

 var result = "select * from Room".OnServer().executeSQL();
 for(var i=0; i<result.length; i++) {
     //do something here.... using result[i];
 }

2.操作Javascript Object Array 、执行SQL操作:

 var Room = [
   {
    ID: 'bot',
    name: 'test',
    sex: true
   }, {
    ID: 2,
    name: 'test8',
    sex: true
   }, {
    ID: 3,
    name: 'test5',
    sex: false
   }, {
    ID: 4,
    name: 'test2',
    sex: true
   }];

 SQL = "select Max(id) as bid,Sum(id) as total from records where name like \"test%\" group by sex order by id desc,name asc";
 var result = SQL.executeSQL();
 for(var i=0; i<result.length; i++) {
     //do something here.... using result[i];
 };
 "create table mytable".executeSQL();
 for(var j=0; j<100; j++) {
  "insert into mytable (id,name,sex) values(2,'zhangsan',true) ".executeSQL();
 };

操作Object Array
其实还可以这样操作:

var Room = [
   {
    ID: 'bot',
    name: 'test',
    sex: true
   }, {
    ID: 2,
    name: 'test8',
    sex: true
   }, {
    ID: 3,
    name: 'test5',
    sex: false
   }, {
    ID: 4,
    name: 'test2',
    sex: true
   }];
"update Room set name = 'man' where sex=true".executeSQL();

支持的SQL语句有 SELECT \ INSERT \ UPDATE \ DELETE \ CREATE TABLE \ DROP TABLE

3.上回说道我发现DOM其实也可以用SQL操作
  比方说,你可以直接插入100个图片element操作如下:

  'create table logolist'.ForDOM().executeSQL();
  for(var i=0;i<100;i++) {
   "insert into logolist(nodename,title,src) values ('img','google','images/google.gif')".ForDOM().execute();
  };

  或者是DELETE这些符合条件的元素:

 ("delete from logolist where title='google'").ForDOM().execute();

  亦或是充当selector:

 var result = ("select * from logolist").ForDOM().execute();
 for(var i=0;i<result.length;i++) {
  result[i].src = 'baidu.gif';
 };

 接上文说道的JSQL

4.当然随着HTML5的普及,web DB 已经是大势所趋了

所以,JSQL封装到:

 _CLIENTDATABASE_ = 'HotelManage';
 _CLIENTDBVERSION_ = '0.1';
 _CLIENTDBDESC_ = "First Client DataBase";
 _CLIENTDBSIZE_ = 10240;


 JSQL("create table sqllite(id int)").OnClient().execute();

 for (var i = 0; i < 1000; i++) {
  JSQL("insert into sqllite (id) values ("+i+")").OnClient().execute();
 };
 var result = ("select * from sqllite").OnClient().executeSQL();
 //alert(result);
 for(var i=0;i<result.length;i++) {
  //do something using result[i] 
};

当然web DB 的sqlite同样支持Insert / select / update / delete / create / drop table
甚至更加牛逼的sql语句。

最后附上我用JSQL库做的两个案例:

感谢 gongji 的投稿

12 下一页 IDC123.COM

此案例均在浏览器客户端用js实现,没有经过服务器上的php等任何服务端语言处理:

1.JSQL简单example,批量图片切换。其实这个example也很简单,
就是根据where后的条件批量修改element的属性,此处为Img元素

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript" src="../src/YESBRAIN.js"></script>
<script type="text/javascript" src="../src/JSQL.js"></script>
<script type="text/javascript" src="../src/JSQL-DOM.js"></script>
<title>Switch Logo Images</title>
</head>
<body>
 <button onClick="switchlogos();"> 切换 LOGOS </button>

 <script type="text/javascript">
  title1 = "google";
  title2 = "baidu";
  logo1 = "images/google.gif";
  logo2 = "images/baidu.gif";
  function switchlogos() {
   //alert('switch');
   var tmplogo = logo1;
   var tmptitle = title1;
   logo1 = logo2;
   title1 = title2;
   logo2 = tmplogo;
   title2 = tmptitle;
   ("update logolist set src='" + logo1 + "', title='" + title1 + "'").ForDOM().execute();
  };
  'create table logolist'.ForDOM().executeSQL();
  for(var i=0;i<100;i++) {
   "insert into logolist(nodename,title,src) values ('img','google','images/google.gif')".ForDOM().execute();
  };
  //("delete from logolist where (1=1)").ForDOM().execute();

 </script>
</body>
</html>

[/code]

其中,还用到了JSQL的DOM元素插入操作:

[code="js"]

'create table logolist'.ForDOM().executeSQL();
  for(var i=0;i<100;i++) {
   "insert into logolist(nodename,title,src) values ('img','google','images/google.gif')".ForDOM().execute();
  };

2.JSQL应用案例:基于客户端的成绩统计

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Marks Count</title>
<script type="text/javascript" src="../src/YESBRAIN.js"></script>
<script type="text/javascript" src="../src/JSQL.js"></script>
<script type="text/javascript" src="../src/JSQL-Memory.js"></script>
<script type="text/javascript" src="../src/JSQL-DOM.js"></script>
<style>
 #marks input {
  width: 100px;
 }
</style>

</head>
<body>
<center>
<h1>Marks Count</h1>
<p>
 <button onclick="insertline();"> Add one line </button>
 <button onclick="savemarks();"> Save Changes </button>
 <span>By<span>

 <select id="byfield">
  <option value="chinese" selected="selected">Chinese</option>
  <option value="math">Math</option>
  <option value="english">English</option>
 </select>
 <select id="ascdesc">
  <option value="desc" selected="selected">Desc</option>

  <option value="asc">Asc</option>
 </select>
 <button onclick="order();">Order</button>
</p>
<form name="marks" id="marks">
 <span>Name:</span>
 <input type="text" id="name[1]" value="zhangsan">
 <span>Sex:</span>

 <input type="text" id="sex[1]" value="female">
 <span>Chinese:</span>
 <input type="text" id="chinese[1]" value="96">
 <span>Math:</span>
 <input type="text" id="math[1]" value="94">
 <span>English:</span>
 <input type="text" id="english[1]" value="98">

 <br>
</form>
</center>

<script type="text/javascript">
 var marks = [
     {
      name: 'Lisi',
      sex: 'Female',
      chinese: '88',
      math: '90',
      english: '92'
     },
     {
      name: 'Wangwu',
      sex: 'Female',
      chinese: '92',
      math: '80',
      english: '82'
     },
     {
      name: 'Lilei',
      sex: 'Female',
      chinese: '93',
      math: '88',
      english: '87'
     },
     {
      name: 'HanMeimei',
      sex: 'Male',
      chinese: '97',
      math: '92',
      english: '100'
     }, 
     {
      name: 'Wangjuan',
      sex: 'Male',
      chinese: '92',
      math: '93',
      english: '90'
     }    
 ];
 
 function addto(index,name,sex,chinese,math,english) {
  "insert into marks (nodename,innerHTML) values ('span','Name:')".ForDOM().execute();
  ("insert into marks (nodename,type,id,value) values ('input','text','name[" + (index) + "]','" + name + "')").ForDOM().execute();
  "insert into marks (nodename,innerHTML) values ('span','Sex:')".ForDOM().execute();
  ("insert into marks (nodename,type,id,value) values ('input','text','sex[" + (index) + "]','" + sex + "')").ForDOM().execute();
  "insert into marks (nodename,innerHTML) values ('span','Chinese:')".ForDOM().execute();
  ("insert into marks (nodename,type,id,value) values ('input','text','chinese[" + (index) + "]','" + chinese + "')").ForDOM().execute();
  "insert into marks (nodename,innerHTML) values ('span','Math:')".ForDOM().execute();
  ("insert into marks (nodename,type,id,value) values ('input','text','math[" + (index) + "]','" + math + "')").ForDOM().execute(); 
  "insert into marks (nodename,innerHTML) values ('span','English:')".ForDOM().execute();
  ("insert into marks (nodename,type,id,value) values ('input','text','english[" + (index) + "]','" + english + "')").ForDOM().execute();
  ("insert into marks (nodename) values ('br')").ForDOM().execute();  
 };
 
 for(var i=0; i<marks.length;i++) {
  addto(i+2,marks[i].name,marks[i].sex,marks[i].chinese,marks[i].math,marks[i].english);
 };
 
 function insertline() {
  var count = "select count(*) as Count from marks where id like 'name%'".ForDOM().query();
  var index = count[0].Count + 1;
  addto(index,"","","","","");
 };
 
 function dellastline() {
  var count = "select count(*) as Count from marks where id like 'name%'".ForDOM().query();
  var index = count[0].Count;
  ("delete from marks where id like '%["+ index +"]'").ForDOM().execute();
 };
 
 "create table savedmarks".execute();
 
 function savemarks() {
  var names = ("select value from marks where id like 'name%'").ForDOM().query();
  var sexs = ("select value from marks where id like 'sex%'").ForDOM().query();
  var chineses = ("select value from marks where id like 'chinese%'").ForDOM().query();
  var maths = ("select value from marks where id like 'math%'").ForDOM().query();
  var englishs = ("select value from marks where id like 'english%'").ForDOM().query();
  "delete from savedmarks".execute();
  for(var i=0;i<names.length;i++) {
   ("insert into savedmarks (name,sex,chinese,math,english) values ('"+ names[i].value +"','" + sexs[i].value + "'," + chineses[i].value + "," + maths[i].value + "," + englishs[i].value + ")").execute();
  };
  //alert(savedmarks);
 };
 
 function orderby(field,asc) {
  savemarks();
  var marks = ("select * from savedmarks order by " + field + " " + asc).query();
  "delete from marks".ForDOM().execute();
  for(var i=0; i<marks.length;i++) {
   addto(i+2,marks[i].name,marks[i].sex,marks[i].chinese,marks[i].math,marks[i].english);
  };
 };
 function order() {
  var byfield = document.getElementById("byfield").value;
  var ascdesc = document.getElementById("ascdesc").value;
  orderby(byfield,ascdesc);
 };
</script>

</body>
</html>

主要用到四个JS库,其中YESBRAIN.js是基库,JSQL.js是接口。JSQL-Memory.js和JSQL-DOM分别是JSQL对 javascript Objects Array 和 Html DOM 的 SQL 实现。

感谢 gongji 的投稿

发表评论
用户名: 匿名