JSP JDBC 读取SQL Server 数据2

  1 <%--
  2   Created by IntelliJ IDEA.
  3   User: hellohongfu
  4   Date: 2017/12/21
  5   Time: 0:16
  6   To change this template use File | Settings | File Templates.
  7 --%>
  8 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
  9 <%@page import="java.io.*,java.util.*"  %>
 10 <%@ page import="org.apache.poi.hssf.usermodel.*" %>
 11 <%@ page import="org.apache.poi.poifs.filesystem.*" %>
 12 <%@ page import="org.apache.poi.ss.usermodel.*" %>
 13 <%@ page import="org.apache.poi.xssf.usermodel.*" %>
 14 
 15 <%@ page import="java.sql.*" %>
 16 
 17 <html>
 18 <head>
 19     <title>excel demo</title>
 20 </head>
 21 <body>
 22 
 23 <%
 24     InputStream inp = new FileInputStream("c:\demo.xlsx");
 25     //InputStream inp = new FileInputStream("workbook.xlsx");
 26 
 27     Workbook wb = WorkbookFactory.create(inp);
 28     Sheet sheet = wb.getSheetAt(0);
 29     for (int k = 1; k <= sheet.getLastRowNum(); k++){
 30         Row row=sheet.getRow(k);
 31         Cell cell = row.getCell(1);
 32         if (cell != null){
 33             String value =cell.getStringCellValue();
 34             out.println("cell value:"+value+"<br>");
 35         }
 36 
 37     }
 38 
 39     Row row = sheet.getRow(2);
 40     Cell cell = row.getCell(3);
 41 
 42 
 43 
 44     out.println(cell.getStringCellValue());
 45     if (cell == null)
 46         cell = row.createCell(3);
 47     cell.setCellType(CellType.STRING);
 48 
 49     // Write the output to a file
 50     FileOutputStream fileOut = new FileOutputStream("workbook.xls");
 51     wb.write(fileOut);
 52     fileOut.close();
 53 %>
 54 
 55 <table>
 56     <thead>
 57     <tr>
 58         <th>
 59             id
 60         </th>
 61         <th>
 62             company
 63         </th>
 64         <th>
 65             name
 66         </th>
 67     </tr>
 68     </thead>
 69 <tbody>
 70 <%
 71     String connectionUrl = "jdbc:sqlserver://DESKTOP-U9122RH;" +
 72             "databaseName=ryqy;integratedSecurity=true;";
 73 
 74     // Declare the JDBC objects.
 75     Connection con = null;
 76     Statement stmt = null;
 77     ResultSet rs = null;
 78     out.println("test sql connection  <br>");
 79     try {
 80         // Establish the connection.
 81         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
 82         con = DriverManager.getConnection(connectionUrl);
 83 
 84         // Create and execute an SQL statement that returns some data.
 85         String querySql = "select * from account";
 86         stmt = con.createStatement();
 87         rs = stmt.executeQuery(querySql);
 88 
 89         // Iterate through the data in the result set and display it.
 90         int isExists=0;
 91         while (rs.next()) {
 92             isExists=1;
 93 
 94 
 95             System.out.println(rs.getString(1) + " " + rs.getString(2));
 96 
 97             String id=rs.getString(1);
 98             String company=rs.getString(1);
 99             String name =rs.getString(3);
100 
101             out.println("<tr><td>"+id+"</td><td>"+company+"</td><td>"+name+"</td></tr>");
102 
103 
104         }
105         out.println("test sql connection success <br>");
106         if(isExists==1)
107         {
108             out.println("已经存在不用再创建！ <br>");
109         }else
110         {
111 
112             out.println("不存在 <br>");
113         }
114 
115     }
116 
117     // Handle any errors that may have occurred.
118     catch (Exception e) {
119         e.printStackTrace();
120         out.println("test sql connection fault  <br>");
121     }
122 
123     finally {
124         if (rs != null) try { rs.close(); } catch(Exception e) {}
125         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
126         if (con != null) try { con.close(); } catch(Exception e) {}
127     }
128 %>
129 </tbody>
130 </table>
131 </body>
132 </html>
View Code