數據庫是編程中重要的一部分，它囊括了數據操作，數據持久化等各方面。在每一門編程語言中都占有相當大的比例。
本次，我以mysql為例，使用mvc編程思想(請參閱我之前的博客)。簡單演示一下javaweb對數據庫的操作。
1：我們需要掌握簡單的sql語句，并且會簡單操作圖形化的數據庫。我們在數據庫建一個表(users)可以在里面隨便添加幾條數據。
2：接下來，我們獲得驅動并連接到mysql。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
package com.joker.web.db; import java.sql.connection; import java.sql.drivermanager; import java.sql.sqlexception; public class dbconnection { private static connection con = null; // 驅動程序名 private static string drivername = "com.mysql.jdbc.driver"; // 數據庫用戶名 private static string username = "root"; // 密碼 1 private static string userpasswd = "*****"; // 數據庫名 private static string dbname = "jokertest"; // 聯結字符串 private static string url = "jdbc:mysql://localhost/" + dbname + "?user=" + username + "&password=" + userpasswd + "&useunicode=true&characterencoding=gbk"; public static connection getconnection() { try { // 1.驅動 class.forname(drivername); // 2. 連接數據庫 保持連接 con = drivermanager.getconnection(url); } catch (classnotfoundexception e) { // todo auto-generated catch block e.printstacktrace(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } return con; } public static void closeconnection() { if (con != null) { try { con.close(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } } } }<span style="font-size:18px;color:#990000;"> </span> |
3.寫我們的dao文件,即對數據庫的增刪改查
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
package com.joker.web.dao; import java.sql.*; import java.util.*; import com.joker.web.db.dbconnection; import com.joker.web.entity.user; public class userdao { // 查找所有數據，返回list集合 public list<user> selectall() { connection con = dbconnection.getconnection();// 連接數據庫 保持連接 statement stmt; list<user> list = new arraylist<user>(); try { stmt = con.createstatement();// 執行sql語句 resultset rs = stmt.executequery("select * from users");// 查找 // 數據返回結果集 while (rs.next()) { user user = new user(); user.setid(rs.getint("id")); user.setusername(rs.getstring("user_name")); user.setdisplayname(rs.getstring("display_name")); user.setpwd(rs.getstring("pwd")); list.add(user); } } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { dbconnection.closeconnection(); } return list; } // 按條件查找 // where user_name='"+ name + "' and pwd='" + pwd + "' public user selectwhere(string whereoption) { connection con = dbconnection.getconnection();// 連接數據庫 保持連接 statement stmt; user user = null; try { stmt = con.createstatement();// 執行sql語句 string sql = "select * from users "; if (!whereoption.equals("")) { sql += whereoption; } // 查找數據返回結果集 resultset rs = stmt.executequery(sql); while (rs.next()) { user = new user(); user.setusername(rs.getstring("user_name")); user.setdisplayname(rs.getstring("display_name")); user.setpwd(rs.getstring("pwd")); } } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { dbconnection.closeconnection(); } return user; } // 新增 public int insert(user user) { connection con = dbconnection.getconnection(); preparedstatement pstmt = null; string sql = " insert into users(user_name,pwd,display_name) values(?,?,?)"; // 增加用preparestatement int count = 0; try { pstmt = con.preparestatement(sql); pstmt.setstring(1, user.getusername()); pstmt.setstring(2, user.getpwd()); pstmt.setstring(3, user.getdisplayname()); count = pstmt.executeupdate(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { try { pstmt.close(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } dbconnection.closeconnection(); } return count; } // 修改 public int update(user user) { connection con = dbconnection.getconnection(); preparedstatement pstmt = null; string sql = " update users " + " set user_name = ? , " + " pwd = ? , " + " display_name= ? " + " where id= ? "; int count = 0; try { pstmt = con.preparestatement(sql); pstmt.setstring(1, user.getusername()); pstmt.setstring(2, user.getpwd()); pstmt.setstring(3, user.getdisplayname()); pstmt.setint(4, user.getid()); count = pstmt.executeupdate(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { try { pstmt.close(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } dbconnection.closeconnection(); } return count; } // 刪除 public int delete(int id) { connection con = dbconnection.getconnection(); preparedstatement pstmt = null; string sql = " delete from users where id = ?"; int count = 0; try { pstmt = con.preparestatement(sql); pstmt.setint(1, id); count = pstmt.executeupdate(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { try { pstmt.close(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } dbconnection.closeconnection(); } return count; } public list<user> selectpage(int from, int rows) { connection con = dbconnection.getconnection();// 連接數據庫 保持連接 statement stmt; list<user> list = new arraylist<user>(); try { stmt = con.createstatement();// 執行sql語句 resultset rs = stmt.executequery("select * from users limit " + from + "," + rows); while (rs.next()) { user user = new user(); user.setid(rs.getint("id")); user.setusername(rs.getstring("user_name")); user.setdisplayname(rs.getstring("display_name")); user.setpwd(rs.getstring("pwd")); list.add(user); } } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { dbconnection.closeconnection(); } return list; } // 返回所有數劇條數 public int selectcount() { connection con = dbconnection.getconnection();// 連接數據庫 保持連接 statement stmt; int count =0 ; try { stmt = con.createstatement();// 執行sql語句 resultset rs = stmt.executequery("select count(1) count from users"); while (rs.next()) { count = rs.getint("count"); } } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { dbconnection.closeconnection(); } return count; } }<span style="font-size:18px;color:#990000;"> </span> |
4.使用servlet當控制器，在servlet對網頁上的數據進行操作。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
package com.joker.web.servlet; import java.io.ioexception; import java.io.printwriter; import java.util.*; import javax.servlet.servletexception; import javax.servlet.http.httpservlet; import javax.servlet.http.httpservletrequest; import javax.servlet.http.httpservletresponse; import net.sf.json.jsonarray; import net.sf.json.jsonobject; import com.joker.web.dao.userdao; import com.joker.web.entity.user; public class userservlet extends httpservlet { /** * constructor of the object. */ public userservlet() { super(); } /** * destruction of the servlet. <br> */ public void destroy() { super.destroy(); // just puts "destroy" string in log // put your code here } public void doget(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { this.dopost(request, response); } public void dopost(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { request.setcharacterencoding("utf-8"); response.setcontenttype("text/html;charset=utf-8"); string action = request.getparameter("action"); if (action.equals("select")) { select(request, response); } else if (action.equals("update")) { update(request, response); } else if (action.equals("add")) { insert(request, response); } else if (action.equals("delete")) { delete(request, response); } } // 新增 private void insert(httpservletrequest request, httpservletresponse response) throws ioexception { string uname = request.getparameter("username"); string pwd = request.getparameter("user"); string dname = request.getparameter("displayname"); user user = new user(); user.setusername(uname); user.setpwd(pwd); user.setdisplayname(dname); userdao udao = new userdao(); int affcount = udao.insert(user); printwriter out = response.getwriter(); // 將受影響數據的數量返回給jsp out.print(affcount); } // 刪除 private void delete(httpservletrequest request, httpservletresponse response) throws ioexception { string[] ids = request.getparametervalues("uid[]"); userdao ud = new userdao(); int count = 0; for (int i = 0; i < ids.length; i++) { count += ud.delete(integer.parseint(ids[i])); } printwriter out = response.getwriter(); out.print(count); } /** * initialization of the servlet. <br> * * @throws servletexception * if an error occurs */ public void init() throws servletexception { system.out.println("處室執行！！！！！！！！！！！！！！！"); } // 查詢 public void select(httpservletrequest request, httpservletresponse response) throws ioexception { string page = request.getparameter("page"); string rows = request.getparameter("rows"); system.out.println("page:" + page + " rows:" + rows); userdao ud = new userdao(); int rowscount = integer.parseint(rows); int from = (integer.parseint(page) - 1) * rowscount; list<user> list = ud.selectpage(from, rowscount); hashmap<string, object> map = new hashmap<string, object>(); map.put("total", ud.selectcount()); map.put("rows", list); // jsonarray ja = jsonarray.fromobject(list); jsonobject jo = jsonobject.fromobject(map);// 單條數據 printwriter out = response.getwriter(); system.out.println(jo.tostring()); // 將json數據返回給jspdata-grid的url。 out.println(jo.tostring()); } // 修改 public void update(httpservletrequest request, httpservletresponse response) throws ioexception { string id = request.getparameter("id"); string uname = request.getparameter("username"); string pwd = request.getparameter("pwd"); string dname = request.getparameter("displayname"); user user = new user(); user.setid(integer.parseint(id)); user.setusername(uname); user.setpwd(pwd); user.setdisplayname(dname); userdao udao = new userdao(); int affcount = udao.update(user); printwriter out = response.getwriter(); // 將受影響數據的數量返回給jsp out.print(affcount); } }<span style="font-size:18px;color:#990000;"> </span> |
5.使用easy-ui框架，是數據庫內容在頁面進行顯示
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
<%@ page language="java" import="java.util.*" pageencoding="utf-8"%> <% string path = request.getcontextpath(); string basepath = request.getscheme() + "://" + request.getservername() + ":" + request.getserverport() + path + "/"; %> <!doctype html> <html> <head> <base href="<%=basepath%>" rel="external nofollow" > <title>main.jsp</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <link rel="stylesheet" href="css/easyui.css" rel="external nofollow" type="text/css"></link> <link rel="stylesheet" href="css/icon.css" rel="external nofollow" type="text/css"></link> <script type="text/javascript" src="js/jquery-1.8.2.min.js"></script> <script type="text/javascript" src="js/jquery.easyui.min.js"></script> <script type="text/javascript"> var addflags = true; $(function() { $('#dg') .datagrid( { title : '用戶列表', url : 'servlet/userservlet?action=select', fitcolumns : true,/* 自適應寬度 */ striped : true,/* 斑馬線效果 */ pagination : true,/* 底部顯示分頁工具欄 */ singleselect : false,/* 只允許選擇一行 */ rownumbers : true,/* 顯示一個行號列 */ ctrlselect : true,/* 允許使用ctrl鍵+鼠標點擊的方式進行多選操作 */ iconcls : 'icon-ok', checkonselect : true,/* 點擊行的時候該復選框就會被選中或取消選中 */ selectoncheck : true,/* 單擊復選框將永遠選擇行 */ pagination : true, nowrap : true, rownumbers : true, collapsible : true,//是否可折疊的 pagesize : 5,//每頁顯示的記錄條數，默認為10 pagelist : [ 2, 4 ,5],//可以設置每頁記錄條數的列表 toolbar : [ { text : '查詢', iconcls : 'icon-search', handler : function() { $('#dg').datagrid('reload'); } }, '-', { text : '修改', iconcls : 'icon-edit', handler : function() { alert('幫助按鈕'); } }, '-', { text : '添加', iconcls : 'icon-add', handler : function() { if(addflags){ $('#dg').datagrid('insertrow',{ index: 0, // 索引從0開始 row: {} }); var editindex = 0; $('#dg').datagrid('selectrow',editindex) .datagrid('beginedit',editindex); addflags = false; } /* , $('#dlg').dialog('open') .dialog('center').dialog( 'settitle', 'new user'); $('#fm').form('clear') */ } }, '-', { text : '刪除', iconcls : 'icon-remove', handler : function() { $.messager.confirm("信息確認","確定刪除嗎?", function(ret) { if (ret) { var row = $("#dg").datagrid("getselections"); if (row.length == 0) { $.messager.alert("提示:","請選擇刪除的數據"); return; } var ids = []; for ( var i = 0; i < row.length; i++) { ids.push(row[i].id); } $.post("servlet/userservlet?action=delete", {uid : ids}, function(data) { if (data > 0) { $('#dg').datagrid('reload'); alert("刪除成功"); } else { alert("刪除失敗"); } }); } }); } } ], columns : [ [ { field : 'ck', checkbox : true, width : 50, }, { field : 'id', hidden : true, }, { field : 'username', title : '用戶名', align : 'center', editor : 'text', width : 100 }, { field : 'pwd', title : '密碼', width : 100, editor : 'text', align : 'center' }, { field : 'displayname', title : '級別', width : 100, height : 100, editor : 'text', align : 'center' }, { field : 'option', title : '操作', width : 100, align : 'center', formatter : function(value, row, index) { if (row.editing) { var s = '<a href="javascript:void(0);" rel="external nofollow" rel="external nofollow" style="text-decoration:none" onclick="saverow(' + index + ')">保存</a>'+' ' +'<a href = "javascript:void(0);" style="text-decoration:none" onclick="canclerow(' + index + ')">取消</a>'; return s; } else { var e = '<a href="javascript:void(0);" rel="external nofollow" rel="external nofollow" style="text-decoration:none" onclick="editrow(' + index + ')">編輯</a>'; return e; } } } ] ], onbeforeedit : function(index, row) { row.editing = true; $("#dg").datagrid("refreshrow", index); }, onafteredit : function(index, row) { row.editing = false; $("#dg").datagrid("refreshrow", index); } }); //設置分頁控件 var p = $('#dg').datagrid('getpager'); $(p).pagination({ beforepagetext : '第',//頁數文本框前顯示的漢字 afterpagetext : '頁 共 {pages} 頁', displaymsg : '當前顯示 {from} - {to} 條記錄 共 {total} 條記錄' }); }); function editrow(index) { var row = $("#dg").datagrid("getselected"); if (row == null) { alert("請選擇您要編輯的行"); return; } $("#dg").datagrid("beginedit", index); } function saverow(index) { $("#dg").datagrid("endedit", index); var row = $("#dg").datagrid("getselected"); if(addflags) { dbsave(row); } else { dbadd(row); } addflags = true; } function canclerow(index) { $("#dg").datagrid("rejectchanges"); $('#dg').datagrid('reload'); addflags = true; } function dbsave(row) { var name = row.username; var uid = row.id; var pwd = row.pwd; var dname = row.displayname; $.post("servlet/userservlet?action=update", { id : uid, username : name, pwd : pwd, displayname : dname }, function(data) { if (data == "1") { alert("修改成功"); } else { alert("修改失敗"); } }); alert(row.username + "-" + row.pwd + "-" + row.displayname); } function dbadd(row) { var name = row.username; var pwd = row.pwd; var dname = row.displayname; $.post("servlet/userservlet?action=add", { username : name, pwd : pwd, displayname : dname }, function(data) { if (data == "1") { alert("添加成功"); } else { alert("添加失敗"); } }); } </script> <body> <%-- ${sessionscope.user.username} ${sessionscope.user.dislayname} --%> <table class="easyui-datagrid" id="dg"> </table> </body> </html><span style="font-size:18px;color:#990000;"> </span> |
6.各個操作具體圖片:
6.1添加:添加（張三 666 學生 這條數據）默認顯示五條數據
6.2 刪除剛剛插入的數據
6.3 修改數據(修改的id主鍵為10的數據)
以上所述是小編給大家介紹的javaweb連接數據庫mysql的操作技巧，希望對大家有所幫助
原文鏈接：http://blog.csdn.net/qq_34122768/article/details/53006759
