<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Web SQL Storage</title>
<style>
#sidebar, #main {
display: block; float: left;
}
#sidebar {
width: 25%;
}
#main {
width: 75%;
}
form ol {
list-style: none; margin: 0; padding: 0;
}
form li {
padding: 0; margin: 0;
}
form li label {
display: block;
}
#title, #note {
width: 100%; font-size: 20px; border: 1px solid #000000;
}
#title {
height: 20px;
}
#note {
height: 40px;
}
#page {
list-style: none;
}
#page li {
display: inline-block; width: 20px; border: 1px solid #eeeeee; text-align: center; margin: 3px; height: 20px;
}
#page li a {
text-decoration: none; font-size: 16px; line-height: 20px; display: block;
}
#page li a:hover {
background-color: red;
}
</style>
</head>
<body>
<section id='sidebar'>
<input type='button' id='new_button' value='New Note'>
<ul id='notes'> <!--列表--> </ul>
<ul id='page'> <!--分页--> </ul>
</section>
<section id='main'>
<form action=''>
<ol>
<li>
<input type='submit' id='save_button' value='Save'>
<input type='submit' id='delete_button' value='Delete'>
</li>
<li>
<label for='title'>Title</label>
<input type='text' id='title'>
</li>
<li>
<label for='note'>Note</label>
<textarea name='note' id='note' cols='30' rows='10'></textarea>
</li>
</ol>
</form>
</section>
</body>
</html>
<script src='js/jquery-3.5.1.js'></script>
<script>
// 分页参数获取
let page = 1,
pageSize = 2;
let getPageInfo = function() {
let argArr = window.location.search.replace('?', '').split('&');
for (let i = 0; i < argArr.length; i++) {
let p = argArr[i].split('=');
if (p[0] === 'page') {
page = p[1];
}
if (p[0] === 'pageSize') {
pageSize = p[1];
}
}
};
getPageInfo();
page = (page <= 0) ? 1 : page;
let offset = (page - 1) * pageSize;
console.log('Page = ' + page + ' pageSize = ' + pageSize + ' offset = ' + offset);
// 数据库 CRUD
let db = null;
// 创建数据库链接
let connectToDb = function() {
// openDatabase('数据库名称', '版本号', '描述', '大小')
db = window.openDatabase('notes', '1.0', 'Notes Database', 1024 * 1024 * 3);
};
connectToDb();
// 创建数据库 - 表
let createNOtesTable = function() {
db.transaction(function(tx) {
tx.executeSql(
'Create table if not exists notes (id Integer primary key , title text , note text);',
[],
function() {
console.log('Success');
},
function(tx, error) {
console.error(error.message);
}
);
});
};
createNOtesTable();
// 查询所有记录数 - 还支持 limit 分页
let fetchNotes = function() {
db.transaction(function(tx) {
tx.executeSql('select id, title, note from notes limit ?, ?;',
[offset, pageSize],
function(SQLTransaction, data) {
console.log('遍历');
for (let i = 0; i < data.rows.length; i++) {
let row = data.rows.item(i),
id = row['id'],
title = row['title'];
addToNotesList(id, title);
}
}
);
});
};
fetchNotes();
let addToNotesList = function(id, title) {
let notes = $('#notes'),
item = $('<li>');
item.attr('data-id', id);
item.html(title);
notes.append(item);
};
// 分页 HTML
let paging = function() {
db.transaction(function(tx) {
tx.executeSql('select count(id) total from notes limit ?;',
[1],
function(SQLTransaction, data) {
console.log('分页-遍历');
let row = data.rows.item(0),
total = row['total'];
addToNotePageList(total);
}
);
});
};
let addToNotePageList = function(total) {
let totalPage = Math.ceil(total / pageSize), $page = $('#page'), pageStr = '';
console.log('记录数为：' + total + ' 总计页数：' + totalPage);
for (let i = 1; i <= totalPage; i++) {
pageStr += '<li><a href="?page=' + i + '&pageSize=' + pageSize + '">' + (i) + '</a></li>';
}
$page.html(pageStr);
};
paging(); // 调用分页
// 点击[表格]表示加载数据
$('#notes').click(function(e) {
if ($(e.target).is('li')) {
let el = $(e.target);
loadNote(el.attr('data-id'));
}
});
let loadNote = function(id) {
db.transaction(function(tx) {
tx.executeSql(
'select id, title, note from notes where id = ?',
[id],
function(SQLTransaction, data) {
let row = data.rows.item(0),
title = $('#title'),
note = $('#note');
title.val(row['title']);
title.attr('data-id', row['id']);
note.val(row['note']);
$('#delete_button').show();
}
);
});
};
// 点击【保存】，如果有ID，表示新装，没有则更新
$('#save_button').click(function(e) {
e.preventDefault();
let title = $('#title'),
note = $('#note');
if (title.attr('data-id')) {
updateNote(title, note);
} else {
insertNote(title, note);
}
});
// 新增 记录
let insertNote = function(title, note) {
db.transaction(function(tx) {
tx.executeSql(
'insert into notes (title, note) values (?, ?);',
[title.val(), note.val()],
function(tx, result) {
let id = result.insertId;
alert('Record ' + id + ' Saved!');
title.attr('data-id', result.insertId);
addToNotesList(id, title.val());
$('#delete_button').show();
}
);
});
};
// 更新记录
let updateNote = function(title, note) {
let id = title.attr('data-id');
db.transaction(function(tx) {
tx.executeSql(
'update notes set title = ?, note = ? where id = ?;',
[title.val(), note.val(), id],
function(tx, result) {
alert('Record ' + id + ' Updated!');
$('#notes>li[data-id=' + id + ']').html(title.val());
},
function() {
alert('The note was not updated!');
}
);
});
};
// 点击 删除按钮事件
$('#delete_button').click(function(e) {
e.preventDefault();
let title = $('#title');
deleteNote(title);
});
// 删除
let deleteNote = function(title) {
let id = title.attr('data-id');
db.transaction(function(tx) {
tx.executeSql(
'delete from notes where id = ?;',
[id],
function(tx, result) {
alert('Record ' + id + ' Deleted!');
$('#notes>li[data-id=' + id + ']').remove();
},
function() {
alert('The note was not deleted!');
}
);
});
};
// 添加新增的时，初始化表单
$('#new_button').click(function(e) {
e.preventDefault();
newNote();
});
let newNote = function() {
$('#delete_button').hide();
let title = $('#title'),
note = $('#note');
title.removeAttr('data-id');
title.val('');
note.val('');
};
</script>
