数据库是编程中重要的一部分，它囊括了数据操作，数据持久化等各方面。在每一门编程语言中都占有相当大的比例。
本次，我以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
