java EE实现动态SQL的

结果图

情况一：

情况二：

预先软件准备

Eclipse软件

JDK 最好是1.5以上

Tomcat

MySQL

Windows xp及其以上版本的操作系统

浏览器(随便哪一种的较新版本)

jquery-1.11.2.js------------网络上自己下载（“百度”à“jquery-1.11.2.js包”）

mysql-connector-java-5.0.5-bin.jar------网络上自己下载(“百度”à”mysql驱动jar包”)

项目截图

具体代码如下

JSP网页

report_select.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5 <head>
 6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7 <script type="text/javascript" src="../js/jquery-1.11.2.js"></script>
 8 <script type="text/javascript" src="../js/mannager.js"></script>
 9 <title>打印选项</title>
10 </head>
11 <body>
12 <button type="button" class="button" onclick="SelectAllBox()" value="全部选中">全部选中</button>
13 &nbsp;&nbsp;
14 <button type="button" class="button" onclick="UnSelectAllBox()" value="全部不选中">全部不选中</button>
15 <br>
16 <form action="../Report_select" method="post">
17 <fieldset style="width: 80%; clear: left">
18        <legend>&nbsp;
19      <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op00" id="user_user" value="user">用户名单
20        &nbsp;</legend>
21        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_username" value="username">用户名
22        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_pwd_encrypt" value="pwd_encrypt">密码密文
23        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_sex" value="sex">性别
24        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_ID_number" value="ID_number">身份证号
25        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_phone" value="phone">联系电话
26      <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_email" value="email">邮箱
27        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_home_address" value="home_address">家庭住址
28        <input type="checkbox" class="checkbox" onclick="getSelect(this.id)" name="user_op0" id="user_info_status" value="info_status">状态
29 <br><input type="submit" value="确定">
30 </fieldset>
31 </form>
32 </body>
33 </html>

report_make.jsp

  1 <%@ page language="java" contentType="text/html; charset=UTF-8"
  2 
  3     pageEncoding="UTF-8"%>
  4 
  5 <%@ page import="bean.*,dao.*,java.util.*"%>
  6 
  7 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  8 
  9 <html>
 10 
 11 <head>
 12 
 13 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 14 
 15 <title>打印确认并输出文档</title>
 16 
 17 </head>
 18 
 19 <body>
 20 
 21 <%
 22 
 23     String[] s = (String[]) request.getAttribute("user");
 24 
 25     LinkedList<User> list = null;
 26 
 27     list = (LinkedList<User>) request.getAttribute("list");
 28 
 29     int i = 0;
 30 
 31 %>
 32 
 33 <center>
 34 
 35 <table border=1>
 36 
 37     <tr>
 38 
 39        <%
 40 
 41            for (i = 0; i < s.length; i++) {
 42 
 43        %>
 44 
 45        <th><%=s[i]%></th>
 46 
 47        <%
 48 
 49            }
 50 
 51        %>
 52 
 53     </tr>
 54 
 55     <tr>
 56 
 57        <%
 58 
 59            int j = 0;
 60 
 61            while (list.size() > j) {
 62 
 63               /*
 64 
 65                *user表的预选择项：
 66 
 67                * 0 username
 68 
 69                * 1 pwd_encrypt
 70 
 71                * 2 sex
 72 
 73                * 3 ID_number
 74 
 75                * 4 phone
 76 
 77                * 5 email
 78 
 79                * 6 home_address
 80 
 81                * 7 info_status
 82 
 83                */
 84 
 85               for (i = 0; i < s.length; i++) {
 86 
 87                   if (s[i].equalsIgnoreCase("username")) {
 88 
 89        %>
 90 
 91        <td><%=list.get(j).getUsername()%></td>
 92 
 93        <%
 94 
 95            i++;
 96 
 97                      if (i == s.length)
 98 
 99                          continue;
100 
101                   }
102 
103                   if (s[i].equalsIgnoreCase("pwd_encrypt")) {
104 
105        %>
106 
107        <td><%=list.get(j).getPwd_encrypt()%></td>
108 
109        <%
110 
111            i++;
112 
113                      if (i == s.length)
114 
115                          continue;
116 
117                   }
118 
119                   if (s[i].equalsIgnoreCase("sex")) {
120 
121        %>
122 
123        <td><%=list.get(j).getSex()%></td>
124 
125        <%
126 
127            i++;
128 
129                      if (i == s.length)
130 
131                          continue;
132 
133                   }
134 
135                   if (s[i].equalsIgnoreCase("ID_number")) {
136 
137        %>
138 
139        <td><%=list.get(j).getID_number()%></td>
140 
141        <%
142 
143            i++;
144 
145                      if (i == s.length)
146 
147                          continue;
148 
149                   }
150 
151                   if (s[i].equalsIgnoreCase("phone")) {
152 
153        %>
154 
155        <td><%=list.get(j).getPhone()%></td>
156 
157        <%
158 
159            i++;
160 
161                      if (i == s.length)
162 
163                          continue;
164 
165                   }
166 
167                   if (s[i].equalsIgnoreCase("email")) {
168 
169        %>
170 
171        <td><%=list.get(j).getEmail()%></td>
172 
173        <%
174 
175            i++;
176 
177                      if (i == s.length)
178 
179                          continue;
180 
181                   }
182 
183                   if (s[i].equalsIgnoreCase("home_address")) {
184 
185        %>
186 
187        <td><%=list.get(j).getHome_address()%></td>
188 
189        <%
190 
191            i++;
192 
193                      if (i == s.length)
194 
195                          continue;
196 
197                   }
198 
199                   if (s[i].equalsIgnoreCase("info_status")) {
200 
201        %>
202 
203        <td><%=list.get(j).getInfo_status()%></td>
204 
205        <%
206 
207                   }
208 
209               }
210 
211        %>
212 
213     </tr>
214 
215     <%
216 
217        j++;
219        }
221     %>
223 </table>
225 </center>
227 </body>
229 </html>

Java文件

jdbcUtil.java

 1 package connect;
 2 import java.sql.*;
 3 public class jdbcUtil {
 4     public static String url ="jdbc:mysql://localhost:3306/“（自己的工程名）”?userUnicode=true&characterEncoding=UTF-8";
 5     public static String user = "root";//数据库的用户名
 6     public static String password = "123456";//数据库的密码
 7     public static String driver = "com.mysql.jdbc.Driver";
 8     public static Connection getConnection() {
 9 
10        Connection conn = null;
11        try {
12            Class.forName(driver);
13            conn = DriverManager.getConnection(url, user, password);
14        } catch (Exception e) {
15            e.printStackTrace();
16        }
17        return conn;
18     }
19     public static void close(ResultSet rs, Statement sm, Connection conn) {
20        try {
21            if (rs != null)
22               rs.close();
23            if (sm != null)
24               sm.close();
25            if (conn != null)
26               conn.close();
27        } catch (Exception e) {
28            e.printStackTrace();
29        }
30     }
31     public static ResultSet doQuery(String sql) {
32        Connection conn = getConnection();
33        if (conn == null)
34            return null;
35        Statement sm = null;
36        ResultSet rs = null;
37        try {
38            sm = conn.createStatement();
39            rs = sm.executeQuery(sql);
40            return rs;
41        } catch (SQLException e) {
42            e.printStackTrace();
43        } finally {
44            if (rs == null && sm != null) {
45               close(null, sm, conn);
46            }
47        }
48        return rs;
49     }
50     public static int doUpdate(String sql, String[] paras) {
51        Connection conn = getConnection();
52        if (conn == null)
53            return 0;
54        PreparedStatement psm = null;
55        int result = 0;
56        try {
57            psm = conn.prepareStatement(sql);
58            for (int i = 0; i < paras.length; i++) {
59               psm.setString(i + 1, paras[i]);
60            }
61            result = psm.executeUpdate();
62        } catch (Exception e) {
63            e.printStackTrace();
64        } finally {
65            close(null, psm, conn);
66        }
67        return result;
68     }  
69 }

jdbcUtil.java

User.java

 1 package bean;
 2 public class User {
 3     private int uid;
 4     private String username;
 5     private String sex;
 6     private String userpwd;
 7     private String pwd_encrypt;
 8     private String ID_number;
 9     private String phone;
10     private String email;
11     private String home_address;
12     private String info_status;
13     public User() {
14     }
15     public int getUid() {
16        return uid;
17     }
18     public void setUid(int uid) {
19        this.uid = uid;
20     }
21     public String getUsername() {
22        return username;
23     }
24     public void setUsername(String username) {
25        this.username = username;
26     }
27    public String getUserpwd() {
28        return userpwd;
29     }
30     public void setUserpwd(String userpwd) {
31        this.userpwd = userpwd;
32     }
33     public String getPwd_encrypt() {
34        return pwd_encrypt;
35     }
36     public void setPwd_encrypt(String pwd_encrypt) {
37        this.pwd_encrypt = pwd_encrypt;
38     }
39     public void setSex(String sex) {
40        this.sex = sex;
41     }
42     public String getSex() {
43        return sex;
44     }
45     public void setID_number(String iD_number) {
46        ID_number = iD_number;
47     }
48     public String getID_number() {
49        return ID_number;
50     }
51     public void setPhone(String phone) {
52        this.phone = phone;
53     }
54     public String getPhone() {
55        return phone;
56     }
57     public void setEmail(String email) {
58        this.email = email;
59     }
60     public String getEmail() {
61        return email;
62     }
63     public void setHome_address(String home_address) {
64        this.home_address = home_address;
65     }
66     public String getHome_address() {
67        return home_address;
68     }
69     public void setInfo_status(String info_status) {
70        this.info_status = info_status;
71     }
72     public String getInfo_status() {
73        return info_status;
74     }
75 }

User.java

UserDao.java

  1 import java.sql.*;
  2 
  3 import java.util.LinkedList;
  4 
  5 import bean.User;
  6 
  7 import connect.jdbcUtil;
  8 
  9 // 随机获取预选项的SQL处理
 10 
 11     // get all user message
 12 
 13     public static LinkedList<User> getUser(String[] s) {
 14 
 15        String sql = "select ";
 16 
 17        for (int i = 0; i < s.length - 1; i++) {
 18 
 19            sql = sql + s[i] + ",";
 20 
 21        }
 22 
 23        sql = sql + s[s.length - 1] + " from `user`";
 24 
 25        System.out.println(sql);
 26 
 27        ResultSet rs = jdbcUtil.doQuery(sql);
 28 
 29        LinkedList<User> list = new LinkedList<User>();
 30 
 31        try {
 32 
 33            while (rs.next()) {
 34 
 35               User user = new User();
 36 
 37               int i = 0;
 38 
 39               /*
 40 
 41         * 0 username 1 pwd_encrypt 2 sex 3 ID_number 4 phone 5 email 6
 42 
 43                * home_address 7 info_status
 44 
 45                */
 46 
 47               for (i = 0; i < s.length; i++) {
 48 
 49                   if (s[i].equalsIgnoreCase("username")) {
 50 
 51                      user.setUsername(rs.getString("username"));
 52 
 53                      i++;
 54 
 55                      if (i == s.length)
 56 
 57                          continue;
 58 
 59                   }
 60 
 61                   if (s[i].equalsIgnoreCase("pwd_encrypt")) {
 62 
 63                      user.setPwd_encrypt(rs.getString("pwd_encrypt"));
 64 
 65                      i++;
 66 
 67                      if (i == s.length)
 68 
 69                          continue;
 70 
 71                   }
 72 
 73                   if (s[i].equalsIgnoreCase("sex")) {
 74 
 75                      user.setSex(rs.getString("sex"));
 76 
 77                      i++;
 78 
 79                      if (i == s.length)
 80 
 81                          continue;
 82 
 83                   }
 84 
 85                   if (s[i].equalsIgnoreCase("ID_number")) {
 86 
 87                      user.setID_number(rs.getString("ID_number"));
 88 
 89                      i++;
 90 
 91                      if (i == s.length)
 92 
 93                          continue;
 94 
 95                   }
 96 
 97                   if (s[i].equalsIgnoreCase("phone")) {
 98 
 99                      user.setPhone(rs.getString("phone"));
100 
101                      i++;
102 
103                      if (i == s.length)
104 
105                          continue;
106 
107                   }
108 
109                   if (s[i].equalsIgnoreCase("email")) {
110 
111                      user.setEmail(rs.getString("email"));
112 
113                      i++;
114 
115                      if (i == s.length)
116 
117                          continue;
118 
119                   }
120 
121                   if (s[i].equalsIgnoreCase("home_address")) {
122 
123                      user.setHome_address(rs.getString("home_address"));
124 
125                      i++;
126 
127                      if (i == s.length)
128 
129                          continue;
130 
131                   }
132 
133                   if (s[i].equalsIgnoreCase("info_status")) {
134 
135                   user.setInfo_status(rs.getString("info_status"));
136 
137                   }
138 
139               }
140 
141               list.add(user);
142 
143            }
144 
145        } catch (SQLException e) {
146 
147            e.printStackTrace();
148 
149        }
150 
151        return list;
152 
153     }

UserDao.java

CharacterEncoding.java

package filter;

import java.io.*;

import javax.servlet.*;

public class CharacterEncoding implements Filter {

    private FilterConfig config;

    String encoding = null;

    public CharacterEncoding() {

    }

    public void destroy() {

       config = null;

    }

    public void doFilter(ServletRequest request, ServletResponse response,

           FilterChain chain) throws IOException, ServletException {

       request.setCharacterEncoding(encoding);

       response.setCharacterEncoding(encoding);

       response.setContentType("text/html;charset="+encoding);

       chain.doFilter(request, response);

    }

    public void init(FilterConfig fConfig) throws ServletException {

       this.config = fConfig;

       String str = config.getInitParameter("encoding");

       if(str != null) {

           encoding = str;

       }

    }

}

CharacterEncoding.java

Report_select.java

package servlet;

import java.io.*;

import java.util.*;

import javax.servlet.*;

import javax.servlet.http.*;

import bean.User;

import dao.UserDao;

public class Report_select extends HttpServlet {

private static final long serialVersionUID = 1L;

protected void doGet(HttpServletRequest request,

HttpServletResponse response) throws ServletException, IOException {

doPost(request, response);

}

protected void doPost(HttpServletRequest request,

HttpServletResponse response) throws ServletException, IOException {

String user[] = request.getParameterValues("user_op0");

for(int i = 0;i < user.length;i ++){

System.out.println(i+" "+user[i]);

}

LinkedList<User> list=UserDao.getUser(user);

request.setAttribute("user", user);

request.setAttribute("list", list);

request.getRequestDispatcher("report/report_make.jsp").forward(request, response);

}

Js文件

jquery-1.11.2.js------------网络上自己下载（“百度”—>“jquery-1.11.2.js包”）

manager.js

function SelectAllBox() {

$("input[type=checkbox]").prop("checked", "checked");

}

function UnSelectAllBox() {

$("input[type=checkbox]").prop("checked", null);

}

function getSelect(id) {

var arrayValue = id.split("_");

if (arrayValue[0] == arrayValue[1]) {

if ($("#" + id)[0].checked) {

$("input[type=checkbox][id^=" + arrayValue[0] + "]").prop(

"checked", "checked");

} else {

$("input[type=checkbox][id^=" + arrayValue[0] + "]").prop(

"checked", null);

}

if (arrayValue[0] != arrayValue[1]) {

var superid = arrayValue[0] + "_" + arrayValue[0];

if ($("#" + id)[0].checked) {

$("#" + superid).prop("checked", "checked");

} else {

var flag = false; var $child = $("input[type=checkbox][id^=" + arrayValue[0]

+ "]:not([id$=" + arrayValue[0] + "])")

$child.each(function() {

if (this.checked) {

flag = true;

return;

}

});

if (!flag) {

$("#" + superid).prop("checked", null);

}

Xml文件

Web.Xml增加以下内容

<web-app。。。。

<filter-name>CharacterEncoding</filter-name>

<filter-class>filter.CharacterEncoding</filter-class>

<init-param>

<param-name>encoding</param-name>

<param-value>utf-8</param-value>

</init-param>

</filter>

<filter-mapping>

<filter-name>CharacterEncoding</filter-name>

<url-pattern>/*</url-pattern>

</filter-mapping>

<servlet-name>Report_select</servlet-name>

<servlet-class>servlet.Report_select</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>Report_select</servlet-name>

<url-pattern>/Report_select</url-pattern>

</servlet-mapping>

</web-app>

SQL文档

Project.sql

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`uid` bigint(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) NOT NULL,

`userpwd` varchar(20) NOT NULL,

`pwd_encrypt` varchar(28) DEFAULT NULL,

`sex` varchar(4) DEFAULT NULL,

`ID_number` varchar(19) DEFAULT NULL,

`phone` varchar(11) DEFAULT NULL,

`email` varchar(20) DEFAULT NULL,

`home_address` varchar(40) DEFAULT NULL,

`info_status` varchar(10) DEFAULT NULL,

PRIMARY KEY (`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

发表于 2015-04-01 23:40 小草1434 阅读(544) 评论(0) 编辑收藏举报

刷新页面返回顶部

java EE实现动态SQL的

公告