这一学期的期末考试也就考了这些。以下是代码,由于简单,在此不多说。
index.jsp:
1 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
2
3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
4 <html>
5 <head>
6 <title>My JSP 'index.jsp' starting page</title>
7 <meta http-equiv="pragma" content="no-cache">
8 <meta http-equiv="cache-control" content="no-cache">
9 <meta http-equiv="expires" content="0">
10 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
11 <meta http-equiv="description" content="This is my page">
12 <link rel="stylesheet" href="layui/css/layui.css" media="all" />
13 <script src="js/jquery.3.2.1.js"></script>
14 <script src="js/jquery.cookie.js"></script>
15 <script type="text/javascript">
16 <%
17 String account = null;
18 if(request.getAttribute("is") != null){
19 if((Integer)request.getAttribute("is") == 1){
20 String account1 = (String)request.getAttribute("ACCOUNT");
21 account = new String(account1.getBytes("iso-8859-1"),"utf-8");
22
23 %>
24 var account = '<%=account%>';
25 $.cookie('ACCOUNT',account);
26 <%
27 response.setHeader("refresh", "1;URL=main.jsp");
28 }else{
29 %>
30 alert("账号或密码错误!");
31 <%
32 }
33 }
34 %>
35 $(function(){
36 $('#register').click(function() {
37 $(location).attr('href', 'register.jsp');
38 });
39 });
40 </script>
41 </head>
42
43 <body>
44 <div style="margin-top: 14%;">
45 <form class="layui-form" id="from1" action="LoginServlet.action" method="post">
46 <div style="margin-right: 4%;" class="layui-form-item">
47 <label class="layui-form-label">账号</label>
48 <div class="layui-input-block">
49 <input id="account" name="account" lay-verify="title" placeholder="请输入账号" class="layui-input" type="text">
50 </div>
51 <label style="margin-top: 1%;" class="layui-form-label">密码</label>
52 <div style="margin-top: 1%;" class="layui-input-block">
53 <input id="passwords" name="passwords" lay-verify="title" placeholder="请输入密码" class="layui-input" type="password">
54 </div>
55 </div>
56 <center>
57 <div class="layui-form-item">
58 <input id="login" type="submit" value="登录" class="layui-btn layui-btn-warm layui-btn-radius" />
59 <input id="register" type="button" value="注册" style="margin-left: 10%;" class="layui-btn layui-btn-warm layui-btn-radius" />
60 </div>
61 </center>
62 </form>
63 </div>
64 </body>
65 </html>
main.jsp:
1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
2
3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
4 <html>
5 <head>
6 <title>My JSP 'main.jsp' starting page</title>
7
8 <meta http-equiv="pragma" content="no-cache">
9 <meta http-equiv="cache-control" content="no-cache">
10 <meta http-equiv="expires" content="0">
11 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
12 <meta http-equiv="description" content="This is my page">
13 <link rel="stylesheet" href="layui/css/layui.css" media="all" />
14 <link rel="stylesheet" href="js/jexcel/css/jquery.jexcel.css" />
15 <link rel="stylesheet" href="js/jexcel/css/jquery.jcalendar.css" />
16 <script src="js/jquery.3.2.1.js"></script>
17 <script type="text/javascript" src="js/jexcel/jquery.jexcel.js" ></script>
18 <script type="text/javascript" src="js/jexcel/jquery.jcalendar.js" ></script>
19 <script src="js/jquery.cookie.js"></script>
20 <script type="text/javascript">
21 $(function(){
22 if('<%=(String)request.getAttribute("isSelect")%>' == '1'){
23 $('#update').show(1500);
24 $('#div2').show(1500);
25 $('#score-student-list').hide(1500);
26 $('#account').val('<%=(String)request.getAttribute("account")%>');
27 $('#phone').val('<%=(String)request.getAttribute("phone")%>');
28 $('#email').val('<%=(String)request.getAttribute("email")%>');
29 $('#ids').val('<%=(String)request.getAttribute("userId")%>');
30 $('#name').val('<%=(String)request.getAttribute("userName")%>');
31 $('#password').val('<%=(String)request.getAttribute("passwords")%>');
32 $('#sex').val('<%=(String)request.getAttribute("sex")%>');
33 }
34 if('<%=(String)request.getAttribute("isSelectAll")%>' == '1'){
35 $('#update').hide(1500);
36 $('#score-student-list').show(1500);
37 $('#div2').hide(1500);
38 var arrData = [];
39 var arr = null;
40 <%
41 int index = 0;
42 final int cloNumber = 6;
43 List<Object> selectDataAll = (List<Object>) request.getAttribute("listAllData");
44 if(selectDataAll != null){
45 for(int i=0 ; i<selectDataAll.size() ; i++){
46 if(index <= cloNumber){
47 %>
48 if(arr == null){
49 arr = new Array;
50 }
51 arr.push('<%=selectDataAll.get(i)%>');
52 <%
53 if(index++ == cloNumber){
54 index = 0;
55 %>
56 arrData.push(arr);
57 arr = null;
58 <%
59 }
60
61 }
62 }
63 }
64 %>
65 $('#score-student-list').jexcel({
66 data: arrData, // 数据(二维数组)
67 colHeaders: ['身份证', '真实姓名', '账号', '手机', '邮箱', '密码','性别'], // 表头
68 colWidths: [200, 200, 200, 200, 200, 200,180], // 每列的宽度
69 columns: [{
70 type: 'text'
71 },{
72 type: 'text'
73 },{
74 type: 'text'
75 },{
76 type: 'text'
77 },{
78 type: 'text'
79 }, {
80 type: 'text'
81 },{
82 type: 'text'
83 },
84 ]}
85 );
86 }
87
88 if('<%=(String)request.getAttribute("isInsert")%>' == '1'){
89 alert("修改成功");
90 $('#div2').hide(1500);
91 }
92
93 if('<%=(String)request.getAttribute("isDelete")%>' == '1'){
94 alert("删除成功");
95 $(location).attr('href', 'index.jsp');
96 }
97
98 $('#select').click(function(){
99 <%--<%=request.getParameter("ACCOUNT") %>"--%>
100 document.demo.action="MainServlet.action?Type=select&accounts="+$.cookie('ACCOUNT');
101 document.demo.submit();
102 });
103
104 $('#save').click(function(){
105 document.demo.action="MainServlet.action?Type=saves";
106 document.demo.submit();
107 });
108
109 $('#cancel').click(function(){
110 document.demo.action="MainServlet.action?Type=cancel&accounts="+$.cookie('ACCOUNT');
111 document.demo.submit();
112 });
113
114 $('#all').click(function(){
115 document.demo.action="MainServlet.action?Type=all";
116 document.demo.submit();
117 });
118
119 $('#update').click(function(){
120 $('input').removeAttr("disabled");
121 $('#div3').show(1500);
122 });
123 });
124 </script>
125 </head>
126
127 <body>
128 <form name="demo" method="post">
129 <div style="margin-left: 1%; margin-top: 2%;" class="layui-btn-group">
130 <input type="button" id="select" value="查看" class="layui-btn" ></input>
131 <input type="button" id="update" value="编辑" class="layui-btn "></input>
132 <input type="button" id="cancel" value="注销" class="layui-btn"></input>
133 <input type="button" id="all" value="查看所有" class="layui-btn"></input>
134 </div>
135 <hr />
136 <div id="div2" style="display:none">
137 <div class="layui-inline">
138 <label class="layui-form-label">账号</label>
139 <div class="layui-input-inline">
140 <input name="account" id="account" class="layui-input" type="text" disabled />
141 </div>
142 </div>
143 <hr />
144 <div class="layui-inline">
145 <label class="layui-form-label">手机</label>
146 <div class="layui-input-inline">
147 <input name="phone" id="phone" class="layui-input" type="tel" disabled />
148 </div>
149 </div>
150 <hr />
151 <div class="layui-inline">
152 <label class="layui-form-label">邮箱</label>
153 <div class="layui-input-inline">
154 <input name="email" id="email" class="layui-input" type="email" disabled />
155 </div>
156 </div>
157 <hr />
158 <div class="layui-inline">
159 <label class="layui-form-label">身份证</label>
160 <div class="layui-input-inline">
161 <input name="ids" id="ids" class="layui-input" type="text" disabled />
162 </div>
163 </div>
164 <hr />
165 <div class="layui-inline">
166 <label class="layui-form-label">真实姓名</label>
167 <div class="layui-input-inline">
168 <input name="name" id="name" class="layui-input" type="text" disabled/>
169 </div>
170 </div>
171 <hr />
172 <div class="layui-inline">
173 <label class="layui-form-label">密码</label>
174 <div class="layui-input-inline">
175 <input name="password" id="password" class="layui-input" type="text" disabled/>
176 </div>
177 </div>
178 <hr />
179 <div class="layui-inline">
180 <label class="layui-form-label">性别</label>
181 <div class="layui-input-inline">
182 <input name="sex" id="sex" class="layui-input" type="text" disabled/>
183 </div>
184 </div>
185 </div>
186 </form>
187 <div id="div3" style="margin-left: 7%;margin-top: 2%;display:none;">
188 <div style="margin-left: 2%;" class="layui-btn-group">
189 <input type="button" id="save" value="保存" class="layui-btn"></input>
190 </div>
191 </div>
192 <div class="score-student-list" id="score-student-list" style="display:none;margin-left: 1%;"></div>
193 </body>
194 </html>
register.jsp:
1 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
2
3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
4 <html>
5 <head>
6
7 <title>My JSP 'register.jsp' starting page</title>
8
9 <meta http-equiv="pragma" content="no-cache">
10 <meta http-equiv="cache-control" content="no-cache">
11 <meta http-equiv="expires" content="0">
12 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
13 <meta http-equiv="description" content="This is my page">
14 <link rel="stylesheet" href="layui/css/layui.css" media="all" />
15 <script src="js/jquery.3.2.1.js"></script>
16 <script src="layui/layui.js"></script>
17 <script type="text/javascript">
18 layui.use('form', function() {
19 var form = layui.form;
20 form.render();
21 });
22
23 $(function(){
24 $('#register').click(function(){
25 var islen = ($('#password1').val().length >= 6 && $('#password1').val().length <= 12) && ($('#password2').val().length >= 6 && $('#password2').val().length <= 12);
26 var isID = $('#user_id').val().length > 0 && $('#user_id').val().length <= 18;
27 if (islen && isID) {
28 if ($('#password1').val() == $('#password2').val()) {
29 document.demo.action="RegisterServlet.action";
30 document.demo.submit();
31 <%
32 if(request.getAttribute("is") != null){
33 if((Integer)request.getAttribute("is") == 1){
34 response.setHeader("refresh", "1;URL=index.jsp");
35 }else{
36 %>
37 alert("注册失败");
38 <%
39 }
40 }
41 %>
42 } else {
43 alert('密码不一致!');
44 }
45 } else {
46 if (!isID) {
47 alert('请输入正确的身份证!');
48 } else if(!islen){
49 alert('密码长度不一致');
50 }else{
51 alert('请输入合法的信息!');
52 }
53 }
54 });
55 });
56 </script>
57 </head>
58
59 <body>
60 <div style="margin-top: 5%;">
61 <form name="demo" class="layui-form" method="post">
62 <div class="layui-form-item">
63 <label class="layui-form-label">账号</label>
64 <div class="layui-input-inline">
65 <input id="account" name="account" lay-verify="required" placeholder="请输入账号" class="layui-input" type="text">
66 </div>
67 </div>
68
69 <div class="layui-form-item">
70 <div class="layui-inline">
71 <label class="layui-form-label">手机</label>
72 <div class="layui-input-inline">
73 <input id="phone" name="phone" lay-verify="required|phone" class="layui-input" type="tel">
74 </div>
75 </div>
76 <div class="layui-inline">
77 <label class="layui-form-label">邮箱</label>
78 <div class="layui-input-inline">
79 <input id="email" name="email" lay-verify="email" class="layui-input" type="email">
80 </div>
81 </div>
82 </div>
83 <div class="layui-form-item">
84 <label class="layui-form-label">身份证</label>
85 <div class="layui-input-inline">
86 <input id="user_id" name="user_id" lay-verify="required" placeholder="请输入身份证" class="layui-input" type="text">
87 </div>
88 </div>
89 <div class="layui-form-item">
90 <label class="layui-form-label">真实姓名</label>
91 <div class="layui-input-inline">
92 <input id="user_name" name="user_name" lay-verify="required" placeholder="请输入真实姓名" class="layui-input" type="text">
93 </div>
94 </div>
95
96 <div class="layui-form-item">
97 <label class="layui-form-label">密码</label>
98 <div class="layui-input-inline">
99 <input id="password1" name="password1" lay-verify="pass" placeholder="请输入密码" class="layui-input" type="password">
100 </div>
101 <div class="layui-form-mid layui-word-aux">请填写6到12位密码</div>
102 </div>
103 <div class="layui-form-item">
104 <label class="layui-form-label">确认密码</label>
105 <div class="layui-input-inline">
106 <input id="password2" name="password2" lay-verify="pass" placeholder="请输入密码" class="layui-input" type="password">
107 </div>
108 <div class="layui-form-mid layui-word-aux">请填写6到12位密码</div>
109 </div>
110
111 <div class="layui-form-item">
112 <label class="layui-form-label">性别</label>
113 <div class="layui-input-block">
114 <input name="sex" value="男" title="男" checked="" type="radio">
115 <input name="sex" value="女" title="女" type="radio">
116 <input name="sex" value="人妖" title="人妖" disabled="" type="radio">
117 </div>
118 </div>
119 <div class="layui-form-item">
120 <div class="layui-input-block">
121 <input id='register' type="button" class="layui-btn" lay-filter="demo1" value="立即注册"></input>
122 <input type="reset" class="layui-btn layui-btn-primary" value="重置"></input>
123 </div>
124 </div>
125 </form>
126 </div>
127 </body>
128 </html>
web.xml:
1 <?xml version="1.0" encoding="UTF-8"?>
2 <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
3 <display-name></display-name>
4 <servlet>
5 <servlet-name>LoginServlet</servlet-name>
6 <servlet-class>com.yuw.servlet.LoginServlet</servlet-class>
7 </servlet>
8 <servlet>
9 <servlet-name>MainServlet</servlet-name>
10 <servlet-class>com.yuw.servlet.MainServlet</servlet-class>
11 </servlet>
12 <servlet>
13 <servlet-name>RegisterServlet</servlet-name>
14 <servlet-class>com.yuw.servlet.RegisterServlet</servlet-class>
15 </servlet>
16 <servlet-mapping>
17 <servlet-name>LoginServlet</servlet-name>
18 <url-pattern>/LoginServlet.action</url-pattern>
19 </servlet-mapping>
20 <servlet-mapping>
21 <servlet-name>MainServlet</servlet-name>
22 <url-pattern>/MainServlet.action</url-pattern>
23 </servlet-mapping>
24 <servlet-mapping>
25 <servlet-name>RegisterServlet</servlet-name>
26 <url-pattern>/RegisterServlet.action</url-pattern>
27 </servlet-mapping>
28 <welcome-file-list>
29 <welcome-file>index.jsp</welcome-file>
30 </welcome-file-list>
31 </web-app>
LoginDB.java:
1 package com.yuw.accessDB;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.List;
8
9 import com.yuw.dataAccess.SelectData;
10 import com.yuw.jdbc.example.JDBCDataBase;
11 import com.yuw.transfer.DataTransfer;
12
13
14 public class LoginDB implements SelectData{
15
16 private List<Object> listDataReceive = null;
17 private Connection connection = null;
18 private PreparedStatement preparedStatement = null;
19 private ResultSet resultSet = null;
20
21 public LoginDB(){
22
23 }
24 /*
25 * @base 当前数据库连接
26 * @dataTransfer 当前数据库连接的数据
27 */
28 public LoginDB(DataTransfer<List<Object>> dataTransfer , JDBCDataBase base){
29 this.connection = base.example();
30 this.listDataReceive = dataTransfer.transfer();
31 }
32
33
34 public int select(String sql) {
35 // TODO Auto-generated method stub
36 try {
37 preparedStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
38 int index = 1;
39 for(Object object : listDataReceive){
40 preparedStatement.setString(index++, (String)object);
41 System.out.println((String)object);
42 }
43 resultSet = preparedStatement.executeQuery();
44 resultSet.last();
45 if(resultSet.getRow() == 1){
46 return 1;
47 }
48 } catch (SQLException e) {
49 // TODO Auto-generated catch block
50 e.printStackTrace();
51 }finally{
52 close();
53 }
54 return 0;
55 }
56
57 public void close(){
58 if(resultSet != null){
59 try {
60 resultSet.close();
61 } catch (SQLException e) {
62 // TODO Auto-generated catch block
63 e.printStackTrace();
64 }
65 }
66 if(preparedStatement != null){
67 try {
68 preparedStatement.close();
69 } catch (SQLException e) {
70 // TODO Auto-generated catch block
71 e.printStackTrace();
72 }
73 }
74 if(connection != null){
75 try {
76 connection.close();
77 } catch (SQLException e) {
78 // TODO Auto-generated catch block
79 e.printStackTrace();
80 }
81 }
82 }
83 public int selectAll(String sql) {
84 // TODO Auto-generated method stub
85 return 0;
86 }
87 }
MainDB.java:
1 package com.yuw.accessDB;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.Arrays;
9 import java.util.List;
10
11 import com.yuw.dataAccess.DeleteData;
12 import com.yuw.dataAccess.InsertData;
13 import com.yuw.dataAccess.SelectData;
14 import com.yuw.jdbc.example.JDBCDataBase;
15 import com.yuw.transfer.DataTransfer;
16 import com.yuw.transfer.DataTransferReport;
17
18 public class MainDB implements DataTransferReport<List<Object>>,SelectData,InsertData,DeleteData{
19 private List<Object> listDataReceive = null;
20 private List<Object> listDataSend = null;
21 private Connection connection = null;
22 private PreparedStatement preparedStatement = null;
23 private ResultSet resultSet = null;
24
25 public MainDB(){
26
27 }
28 /*
29 * @base 当前数据库连接
30 * @dataTransfer 当前数据库连接的数据
31 */
32 public MainDB(DataTransfer<List<Object>> dataTransfer , JDBCDataBase base){
33 this.connection = base.example();
34 this.listDataReceive = dataTransfer.transfer();
35 }
36
37 public List<Object> getListDataSend() {
38 return listDataSend;
39 }
40 public void setListDataSend(List<Object> listDataSend) {
41 this.listDataSend = listDataSend;
42 }
43 public int select(String sql) {
44 // TODO Auto-generated method stub
45 try {
46 preparedStatement = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
47 int index = 1;
48 for(Object object : listDataReceive){
49 preparedStatement.setString(index++, (String)object);
50 }
51 resultSet = preparedStatement.executeQuery();
52 resultSet.last();
53 if(resultSet.getRow() == 1){
54 List<Object> list = new ArrayList<Object>();
55 list.addAll(Arrays.asList(resultSet.getString("userId")
56 ,resultSet.getString("userName")
57 ,resultSet.getString("account")
58 ,resultSet.getString("password")
59 ,resultSet.getString("phone")
60 ,resultSet.getString("email")
61 ,resultSet.getString("sex")));
62 setListDataSend(list);
63 return 1;
64 }
65
66
67 } catch (SQLException e) {
68 // TODO Auto-generated catch block
69 e.printStackTrace();
70 }finally{
71 //close();
72 }
73
74 return 0;
75 }
76
77 public List<Object> transfer() {
78 // TODO Auto-generated method stub
79 return this.listDataSend;
80 }
81
82 public void close(){
83 if(resultSet != null){
84 try {
85 resultSet.close();
86 } catch (SQLException e) {
87 // TODO Auto-generated catch block
88 e.printStackTrace();
89 }
90 }
91 if(preparedStatement != null){
92 try {
93 preparedStatement.close();
94 } catch (SQLException e) {
95 // TODO Auto-generated catch block
96 e.printStackTrace();
97 }
98 }
99 if(connection != null){
100 try {
101 connection.close();
102 } catch (SQLException e) {
103 // TODO Auto-generated catch block
104 e.printStackTrace();
105 }
106 }
107 }
108 public int insert(String sql) {
109 int msg = 0;
110 try {
111 if(listDataReceive != null){
112 preparedStatement = connection.prepareStatement(sql);
113 int index = 1;
114 for(Object object : listDataReceive){
115 preparedStatement.setString(index++, (String)object);
116 }
117 msg = preparedStatement.executeUpdate();
118 }
119 } catch (SQLException e) {
120 // TODO Auto-generated catch block
121 e.printStackTrace();
122 }finally{
123 close();
124 }
125 return msg;
126 }
127
128 public int delete(String sql) {
129 int msg = 0;
130 try {
131 if(listDataReceive != null){
132 preparedStatement = connection.prepareStatement(sql);
133 int index = 1;
134 for(Object object : listDataReceive){
135 preparedStatement.setString(index++, (String)object);
136 }
137 msg = preparedStatement.executeUpdate();
138 }
139 } catch (SQLException e) {
140 // TODO Auto-generated catch block
141 e.printStackTrace();
142 }finally{
143 close();
144 }
145 return msg;
146
147 }
148 public int selectAll(String sql) {
149 // TODO Auto-generated method stub
150 List<Object> total = new ArrayList<Object>();
151 List<Object> row = null;
152 try {
153 PreparedStatement preparedStatement = connection.prepareStatement(sql);
154 resultSet = preparedStatement.executeQuery();
155 while (resultSet.next()) {
156 if(row == null){
157 row = new ArrayList<Object>();
158 row.addAll(Arrays.asList(resultSet.getString("userId"),
159 resultSet.getString("userName"),
160 resultSet.getString("account"),
161 resultSet.getString("phone"),
162 resultSet.getString("email"),
163 resultSet.getString("password"),
164 resultSet.getString("sex")));
165 total.addAll(row);
166 }
167 row = null;
168 }
169 setListDataSend(total);
170 //if(listDataSend != null){
171 return 1;
172 //}
173 } catch (SQLException e) {
174 // TODO Auto-generated catch block
175 e.printStackTrace();
176 }
177 return 0;
178 }
179 }
RegisterDB.java:
1 package com.yuw.accessDB;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.SQLException;
6 import java.util.List;
7
8 import com.yuw.dataAccess.InsertData;
9 import com.yuw.jdbc.example.JDBCDataBase;
10 import com.yuw.transfer.DataTransfer;
11
12 public class RegisterDB implements InsertData {
13
14 // 接收数据
15 private List<Object> listDataReceive = null;
16 private Connection connection = null;
17 private PreparedStatement preparedStatement = null;
18
19 public RegisterDB(){
20
21 }
22 /*
23 * @base 当前数据库连接
24 * @dataTransfer 当前数据库连接的数据
25 */
26 public RegisterDB(DataTransfer<List<Object>> dataTransfer , JDBCDataBase base){
27 this.connection = base.example();
28 this.listDataReceive = dataTransfer.transfer();
29 }
30 /*
31 * @see dataAccess.InsertData#insert(java.lang.String)
32 */
33 public int insert(String sql) {
34 int msg = 0;
35 try {
36 if(listDataReceive != null){
37 preparedStatement = connection.prepareStatement(sql);
38 int index = 1;
39 for(Object object : listDataReceive){
40 preparedStatement.setString(index++, (String)object);
41 }
42 msg = preparedStatement.executeUpdate();
43 }
44 } catch (SQLException e) {
45 // TODO Auto-generated catch block
46 e.printStackTrace();
47 }finally{
48 close();
49 }
50 return msg;
51 }
52 /*
53 * 关闭
54 */
55 public void close(){
56 if(preparedStatement != null){
57 try {
58 preparedStatement.close();
59 } catch (SQLException e) {
60 // TODO Auto-generated catch block
61 e.printStackTrace();
62 }
63 }
64 if(connection != null){
65 try {
66 connection.close();
67 } catch (SQLException e) {
68 // TODO Auto-generated catch block
69 e.printStackTrace();
70 }
71 }
72 }
73 }
AlterData.java:
1 package com.yuw.dataAccess;
2
3 /*
4 * 修改数据
5 */
6 public interface AlterData
7 {
8 int alter(String sql);
9 }
DeleteData.java:
1 package com.yuw.dataAccess;
2 /*
3 * 删除数据
4 */
5 public interface DeleteData
6 {
7
8 int delete(String sql);
9 }
InsertData.java:
1 package com.yuw.dataAccess;
2
3 /*
4 * 插入数据
5 */
6 public interface InsertData{
7
8 int insert(String sql);
9 }
SelectData.java:
1 package com.yuw.dataAccess;
2 /*
3 * 查询数据
4 */
5 public interface SelectData
6 {
7
8 int select(String sql);
9
10 int selectAll(String sql);
11 }
JDBCDataBase.java:
1 package com.yuw.jdbc.example;
2
3 import java.sql.Connection;
4 /**
5 * 数据库连接
6 */
7 public interface JDBCDataBase{
8
9 Connection example();
10 }
SQLserverJDBC.java:
1 package com.yuw.jdbc.example;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 /*
6 * 连接 sql server 2012 数据库
7 */
8 public class SQLserverJDBC implements JDBCDataBase{
9 private static final String DriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
10 private static final String URL = "jdbc:sqlserver://localhost:1433;DatabaseName=testDB";
11 private static final String UserName = "sa";
12 private static final String UserCipher = "123456";
13 /*
14 * 单例模式:整个应用只实例化JDBC一个
15 */
16 private SQLserverJDBC() {
17
18 }
19 private static class LazyHolder {
20 private static final SQLserverJDBC sqlserverJDBC = new SQLserverJDBC();
21 }
22
23 public static final SQLserverJDBC getInstance() {
24 return LazyHolder.sqlserverJDBC;
25 }
26 public Connection example() {
27 Connection dataBase = null;
28 try{
29 Class.forName(DriverName);
30 dataBase = DriverManager.getConnection(URL, UserName, UserCipher);
31 } catch (Exception e ){
32 // TODO Auto-generated catch block
33 System.out.println("连接失败");
34 e.printStackTrace();
35 }
36 return dataBase;
37 }
38
39 }
LoginServlet.java:
1 package com.yuw.servlet;
2
3 import java.io.IOException;
4 import java.io.UnsupportedEncodingException;
5 import java.util.ArrayList;
6 import java.util.Arrays;
7 import java.util.List;
8
9 import javax.servlet.ServletException;
10 import javax.servlet.http.HttpServlet;
11 import javax.servlet.http.HttpServletRequest;
12 import javax.servlet.http.HttpServletResponse;
13
14 import com.yuw.accessDB.LoginDB;
15 import com.yuw.dataAccess.SelectData;
16 import com.yuw.jdbc.example.SQLserverJDBC;
17 import com.yuw.transfer.DataTransfer;
18
19
20 public class LoginServlet extends HttpServlet implements DataTransfer<List<Object>>{
21
22 private static final long serialVersionUID = 1L;
23 // 发送数据
24 private List<Object> listDataSend = null;
25
26 public List<Object> getListDataSend() {
27 return listDataSend;
28 }
29
30 public void setListDataSend(List<Object> listDataSend) {
31 this.listDataSend = listDataSend;
32 }
33
34 public void doPost(HttpServletRequest request, HttpServletResponse response)
35 throws ServletException, IOException {
36 response.setContentType("text/plain; charset=utf-8");
37
38 List<Object> data = new ArrayList<Object>();
39 data.addAll(Arrays.asList(toCharacterEncoding(request.getParameter("account")),
40 toCharacterEncoding(request.getParameter("passwords"))));
41 setListDataSend(data);
42 final String sql = "select * from user_Account where account = ? and password = ?;";
43 int t = select(sql);
44 request.setAttribute("is",t);
45 request.setAttribute("ACCOUNT",request.getParameter("account"));
46 request.getRequestDispatcher("index.jsp").forward(request, response);
47
48
49 }
50 /*
51 * 查询
52 */
53 public int select(String sql){
54 //正在发送数据信息
55 LoginDB loginDB = new LoginDB(this, SQLserverJDBC.getInstance());
56 return selectUsersAccount(loginDB,sql);
57
58 }
59 private int selectUsersAccount(SelectData selectData ,String sql){
60
61 return selectData.select(sql);
62
63 }
64 public List<Object> transfer() {
65 // TODO Auto-generated method stub
66 return this.listDataSend;
67 }
68 /*
69 * 处理乱码
70 */
71 public String toCharacterEncoding(String str) throws UnsupportedEncodingException{
72
73 return new String(str.getBytes("iso-8859-1"),"utf-8");
74
75 }
76
77 }
MainServlet.java:
1 package com.yuw.servlet;
2
3 import java.io.IOException;
4 import java.io.UnsupportedEncodingException;
5 import java.util.ArrayList;
6 import java.util.Arrays;
7 import java.util.List;
8
9 import javax.servlet.ServletException;
10 import javax.servlet.http.HttpServlet;
11 import javax.servlet.http.HttpServletRequest;
12 import javax.servlet.http.HttpServletResponse;
13
14 import org.junit.Test;
15
16 import com.yuw.accessDB.MainDB;
17 import com.yuw.dataAccess.DeleteData;
18 import com.yuw.dataAccess.InsertData;
19 import com.yuw.dataAccess.SelectData;
20 import com.yuw.jdbc.example.SQLserverJDBC;
21 import com.yuw.transfer.DataTransfer;
22 import com.yuw.transfer.DataTransferReport;
23
24 public class MainServlet extends HttpServlet implements DataTransfer<List<Object>>{
25
26 private static final long serialVersionUID = 1L;
27 private static final int ONE = 1;
28 // 发送数据
29 private List<Object> listDataSend = null;
30 // 接收数据
31 private List<Object> listDataReceive = null;
32 private MainDB mainDB = null;
33 //前台标识
34 private final String[] flag = new String[]{"userId","userName","account","passwords","phone","email","sex"};
35 static String le = "";
36 public List<Object> getListDataReceive() {
37 return listDataReceive;
38 }
39
40 public void setListDataReceive(DataTransferReport<List<Object>> dataTransferReport) {
41 this.listDataReceive = dataTransferReport.transfer();
42 }
43
44 public List<Object> getListDataSend() {
45 return listDataSend;
46 }
47
48
49 public void setListDataSend(List<Object> listDataSend) {
50 this.listDataSend = listDataSend;
51 }
52
53 public void doPost(HttpServletRequest request, HttpServletResponse response)
54 throws ServletException, IOException {
55
56 response.setContentType("text/plain; charset=utf-8");
57 List<Object> data = new ArrayList<Object>();
58 if("select".equals(request.getParameter("Type"))){
59 data.addAll(Arrays.asList(toCharacterEncoding(request.getParameter("accounts"))));
60 setListDataSend(data);
61 final String sql = "select users.*,user_Account.password from users,user_Account where users.account = user_Account.account and user_Account.account = ?;";
62 int index = 0;
63 if(select(sql) == ONE){
64 for(Object object : listDataReceive){
65 request.setAttribute(flag[index++], (String)object);
66 }
67 request.setAttribute("isSelect", "1");
68 request.getRequestDispatcher("main.jsp").forward(request, response);
69 }
70 }else if("saves".equals(request.getParameter("Type"))){
71 data.addAll(Arrays.asList(toCharacterEncoding(request.getParameter("account")),
72 toCharacterEncoding(request.getParameter("account")),
73 toCharacterEncoding(request.getParameter("account")),
74 toCharacterEncoding(request.getParameter("password")),
75 toCharacterEncoding(request.getParameter("ids")),
76 toCharacterEncoding(request.getParameter("name")),
77 toCharacterEncoding(request.getParameter("account")),
78 toCharacterEncoding(request.getParameter("phone")),
79 toCharacterEncoding(request.getParameter("email")),
80 toCharacterEncoding(request.getParameter("sex"))));
81 setListDataSend(data);
82 final String sql = "begin tran saves "+
83 "delete users where account=?;"+
84 "delete user_Account where account=?;"+
85 "insert into user_Account values(?,?);"+
86 "insert into users values(?,?,?,?,?,?);"+
87 "commit tran saves";
88 if(insert(sql) == ONE){
89 request.setAttribute("isInsert", "1");
90 request.getRequestDispatcher("main.jsp").forward(request, response);
91 }
92 }else if("cancel".equals(request.getParameter("Type"))){
93 data.addAll(Arrays.asList(toCharacterEncoding(request.getParameter("accounts")),
94 toCharacterEncoding(request.getParameter("accounts"))));
95 setListDataSend(data);
96 final String sql ="begin tran deletes "+
97 "delete users where account=?;"+
98 "delete user_Account where account=?;"+
99 "commit tran deletes";
100 if(delete(sql) == ONE){
101 request.setAttribute("isDelete", "1");
102 request.getRequestDispatcher("main.jsp").forward(request, response);
103 }
104 }else if("all".equals(request.getParameter("Type"))){
105 final String sql = "select users.*,user_Account.password from users, user_Account where users.account = user_Account.account;";
106 if(selectAll(sql) == ONE){
107 request.setAttribute("isSelectAll", "1");
108 request.setAttribute("listAllData", listDataReceive);
109 request.getRequestDispatcher("main.jsp").forward(request, response);
110 }
111 }
112 }
113 @Test
114 public void TestselectAll(){
115 final String sql = "select * from users, user_Account where users.account = user_Account.account;";
116 MainServlet mainServlet = new MainServlet();
117 if(mainServlet.selectAll(sql) ==1){
118 for(Object object : listDataReceive){
119 System.out.println(object);
120 }
121 }
122 }
123 public int selectAll(String sql){
124 //正在发送数据信息
125 mainDB = new MainDB(this, SQLserverJDBC.getInstance());
126 return selectAllUsersAccount(mainDB,sql);
127 }
128
129 private int selectAllUsersAccount(SelectData selectData,String sql){
130 int t = selectData.selectAll(sql);
131 setListDataReceive(mainDB);
132 return t;
133 }
134 public int delete(String sql){
135 //正在发送数据信息
136 mainDB = new MainDB(this, SQLserverJDBC.getInstance());
137 return deleteUsersAccount(mainDB,sql);
138 }
139
140 private int deleteUsersAccount(DeleteData deleteData,String sql){
141 return deleteData.delete(sql);
142 }
143
144 public int insert(String sql){
145 //正在发送数据信息
146 mainDB = new MainDB(this, SQLserverJDBC.getInstance());
147 return insertUsersAccount(mainDB,sql);
148 }
149
150 private int insertUsersAccount(InsertData insertData,String sql){
151 return insertData.insert(sql);
152 }
153
154 public int select(String sql){
155 //正在发送数据信息
156 mainDB = new MainDB(this, SQLserverJDBC.getInstance());
157 return selectUsersAccount(mainDB,sql);
158
159 }
160
161 private int selectUsersAccount(SelectData selectData ,String sql){
162 int t = selectData.select(sql);
163 setListDataReceive(mainDB);
164 return t;
165
166 }
167
168 public List<Object> transfer() {
169 // TODO Auto-generated method stub
170 return this.listDataSend;
171 }
172
173 public String toCharacterEncoding(String str) throws UnsupportedEncodingException{
174 return new String(str.getBytes("iso-8859-1"),"utf-8");
175
176 }
177 }
RegisterServlet.java:
1 package com.yuw.servlet;
2
3 import java.io.IOException;
4 import java.io.UnsupportedEncodingException;
5 import java.util.ArrayList;
6 import java.util.Arrays;
7 import java.util.List;
8
9 import javax.servlet.ServletException;
10 import javax.servlet.http.HttpServlet;
11 import javax.servlet.http.HttpServletRequest;
12 import javax.servlet.http.HttpServletResponse;
13
14 import com.yuw.accessDB.RegisterDB;
15 import com.yuw.dataAccess.InsertData;
16 import com.yuw.jdbc.example.SQLserverJDBC;
17 import com.yuw.transfer.DataTransfer;
18
19
20 public class RegisterServlet extends HttpServlet implements DataTransfer<List<Object>>{
21
22 private static final long serialVersionUID = 1L;
23 // 发送数据
24 private List<Object> listDataSend = null;
25
26 public List<Object> getListDataSend() {
27 return listDataSend;
28 }
29
30
31 public void setListDataSend(List<Object> listDataSend) {
32 this.listDataSend = listDataSend;
33 }
34
35
36 public void doPost(HttpServletRequest request, HttpServletResponse response)
37 throws ServletException, IOException {
38
39 response.setContentType("text/plain; charset=utf-8");
40 List<Object> data = new ArrayList<Object>();
41 data.addAll(Arrays.asList(toCharacterEncoding(request.getParameter("account")),
42 toCharacterEncoding(request.getParameter("password1")),
43 toCharacterEncoding(request.getParameter("user_id")),
44 toCharacterEncoding(request.getParameter("user_name")),
45 toCharacterEncoding(request.getParameter("account")),
46 toCharacterEncoding(request.getParameter("phone")),
47 toCharacterEncoding(request.getParameter("email")),
48 toCharacterEncoding(request.getParameter("sex"))));
49
50 setListDataSend(data);
51 final String sql = "begin tran insets "+
52 "insert into user_Account values(?,?);"+
53 "insert into users values(?,?,?,?,?,?);"+
54 "commit tran insets";
55 int t = insert(sql);
56 request.setAttribute("is",t);
57 request.getRequestDispatcher("register.jsp").forward(request, response);
58 }
59 /*
60 * 插入
61 */
62 public int insert(String sql) {
63 //正在发送数据信息
64 RegisterDB registerDB = new RegisterDB(this, SQLserverJDBC.getInstance());
65 return insertUser(registerDB,sql);
66 }
67 /*
68 * 插入
69 */
70 private int insertUser(InsertData insertData , String sql){
71
72 return insertData.insert(sql);
73
74 }
75
76 public String toCharacterEncoding(String str) throws UnsupportedEncodingException{
77 return new String(str.getBytes("iso-8859-1"),"utf-8");
78
79 }
80
81 public List<Object> transfer() {
82 // TODO Auto-generated method stub
83 return this.listDataSend;
84 }
85 }
DataTransfer.java:
1 package com.yuw.transfer;
2
3 public interface DataTransfer<T>{
4 /*
5 * 数据集通讯
6 * Selvlet与DB进行数据交互的中间件
7 */
8 T transfer();
9 }
DataTransferReport.java:
1 package com.yuw.transfer;
2
3 public interface DataTransferReport<T>
4 {
5
6 /*
7 * 数据集通讯(回送)
8 * DB与Servlet进行数据交互的中间件
9 */
10 T transfer();
11
12 }