通过web database数据库,进行简单的增删改查案例。
web database 前端数据库
database数据库可以自己定义空间大小,存储在浏览器端,类似本地数据库存储,
存储是多行多列值,用sql语句写作。
sql语句:
添加:insert into 表名(字段1,字段2) values(值1,值2);
修改:update 表名 set 字段2 = value,字段2 = value where 修改条件
删除:delete from 表名 where 条件
查询:select * from 表名 where 条件
属性和方法:
openDatabase(数据库名字,版本,说明,大小) 创建数据库;
transaction(执行函数,错误函数,成功函数) 执行事务提交和回滚的函数;
executeSql(sql语句,参数,成功函数,失败函数) 执行sql语句;
result.rows.length 获取信息的条数
result.rows.item(0) 获取第几条数据
result.rows.item(0)[字段名] 获取属性信息
代码案例:
index主要显示页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<style>
.wrap {
width: 600px;
height: 600px;
border: 1px solid black;
margin: 0 auto;
text-align: center;
}
table {
margin: 0 auto;
}
table,
td {
padding: 10px;
border-collapse: collapse;
border: 1px solid black;
}
#btn {
position: absolute;
bottom: 100px;
}
</style>
<script src="js/database.js"></script>
</head>
<body onload="search()">
<div class="wrap">
<h1>商品信息表</h1>
<table id="tab">
<tr>
<td>商品编号</td>
<td>商品名称</td>
<td>商品数量</td>
<td>商品颜色</td>
<td>商品价格</td>
<td>商品操作</td>
</tr>
</table>
<button id="btn" onclick="toadd()">添加</button>
</div>
<script></script>
</body>
</html>
addFoods添加商品页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<style>
.wrap {
width: 600px;
height: 600px;
border: 1px solid black;
margin: 0 auto;
text-align: center;
}
</style>
<script src="js/database.js"></script>
</head>
<body>
<div class="wrap">
<form action="" name="form">
<h1>添加商品</h1>
<p>商品名称:<input type="text" name="name" /></p>
<p>商品数量:<input type="number" name="count" /></p>
<p>商品颜色:<input type="text" name="color" /></p>
<p>商品价格:<input type="number" name="price" /></p>
<p><input type="button" id="addBtn" value="添加" onclick="add()"/></p>
</form>
</div>
</body>
</html>
updateFoods修改商品页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Document</title>
<style>
.wrap {
width: 600px;
height: 600px;
border: 1px solid black;
margin: 0 auto;
text-align: center;
}
</style>
<script src="js/database.js"></script>
</head>
<body onload="updateById()">
<div class="wrap">
<form action="" name="form">
<h1>修改商品</h1>
<p>商品名称:<input type="text" name="name" /></p>
<p>商品数量:<input type="number" name="count" /></p>
<p>商品颜色:<input type="text" name="color" /></p>
<p>商品价格:<input type="number" name="price" /></p>
<p><input type="button" id="addBtn" value="修改" onclick="update()"/></p>
</form>
</div>
</body>
</html>
js完整代码
//创建数据库
var db = openDatabase("food","1.0","商品信息",1024*1024);
//创建表
db.transaction(function(tx){
tx.executeSql("create table foods (id INTEGER PRIMARY KEY,name TEXT,count INTEGER,color TEXT,price INTEGER)");
})
// 创建本地对象存储id
if(!localStorage.getItem("num")){
localStorage.setItem("num",1);
}
//跳转到添加商品页面
function toadd(){
location.href = "addFoods.html"
}
//添加商品
function add(){
var id = localStorage.getItem("num");
var name = form.name.value;
var count = form.count.value;
var color = form.color.value;
var price = form.price.value;
db.transaction(function(tx){
tx.executeSql("insert into foods values(?,?,?,?,?)",[id,name,count,color,price],function(){
console.log("添加成功");
},
function (tx, error) {
console.log("error" + error.message);
})
})
var num = parseInt(localStorage.getItem("num")) + 1;
localStorage.setItem("num",num);
location.href = "index.html";
}
//查询
function search(){
db.transaction(function(tx){
tx.executeSql("select * from foods",[],function(tx,result){
var arr = result.rows
for(var i=0;i<arr.length;i++){
var id = arr[i]["id"];
var name = arr[i]["name"];
var count = arr[i]["count"];
var color = arr[i]["color"];
var price = arr[i]["price"];
tab.innerHTML += "<tr>"+
"<td>"+id+"</td>"+
"<td>"+name+"</td>"+
"<td>"+count+"</td>"+
"<td>"+color+"</td>"+
"<td>"+price+"</td>"+
"<td><input type='button' value='修改' onclick='toupdata("+id+")'><input type='button' value='删除' onclick='deldata("+id+")'></td></tr>"
}
},
function (tx, error) {
console.log("error" + error.message);
})
})
}
//跳转到修改页面,并传递id参数
function toupdata(id){
location.href = "updateFoods.html?id=" + id;
}
//通过id查找数据
function updateById(){
var id = location.search.substring(1).split("=")[1];
db.transaction(function(tx){
tx.executeSql("select * from foods where id=" + id,[],function(tx,result){
var arr = result.rows;
form.name.value = arr[0].name;
form.count.value = arr[0].count;
form.color.value = arr[0].color;
form.price.value = arr[0].price;
var inp = document.createElement("input");
inp.value = id;
inp.name = "id";
inp.type = "hidden";
document.getElementsByName("form")[0].appendChild(inp);
},
function (tx, error) {
console.log("error" + error.message);
});
})
}
//修改商品
function update(){
var id = form.id.value;
var name = form.name.value;
var count = form.count.value;
var color = form.color.value;
var price = form.price.value;
db.transaction(function(tx){
tx.executeSql("update foods set name = ?,count = ?,color = ?,price = ? where id = ?",[name,count,color,price,id],function(){
console.log("修改成功");
location.href = "index.html";
},
function (tx, error) {
console.log("error" + error.message);
});
})
}
//删除商品
function deldata(id){
var temp = confirm("确定要删除吗");
if(temp){
db.transaction(function(tx){
tx.executeSql("delete from foods where id = ?",[id],function(){
location.reload();
});
})
}
}