JSP 使用 JDBC连接SQL Server 

<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.poifs.filesystem.*" %>
<%@ page import="org.apache.poi.ss.usermodel.CellType" %>

<%@ page import="java.sql.*" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Generate SQL </title>
</head>
<body>
<h1>TemplateName <%= request.getParameter("TemplateName")%> </h1>
<p>

        
        
        <%
            String templateName = request.getParameter("TemplateName"); 
            String srcTemplateName=templateName+"_src";
            String trgTemplateName=templateName+"_trg";
            String primaryKeyDefine="PK nvarchar(150)";
            String PrimaryKey ="PK";
            String FilterCondition="a.PK=b.PK";
            String SystemIdentity="WFS";
            String srcQuery="";
            String trgQuery="";
            String SQL ="INSERT INTO [dbo].[BusinessLogicCmpDefine]  ([TemplateName],[SrcTemplateName],[TrgTemplateName],[PrimaryKeyDefine],[PrimaryKey] ,[FilterCondition] ,[SystemIdentity])";
           
            SQL=SQL+" <br> VALUES ('" +templateName+ "','"+srcTemplateName+"','"+trgTemplateName+"','"+primaryKeyDefine+"','"+PrimaryKey+"','"+FilterCondition+"','"+SystemIdentity+"')";
            
            
            out.println(SQL);
           
         


          
        %>
       
</p>
<h2>View <%=srcTemplateName%> </h2>
<p>
        Create view [dbo].[<%=srcTemplateName%>] 
        <br>
        as 
        <br>
        <%
        
        try (FileInputStream fileIn = new FileInputStream("c:\\demo.xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
        
        
         
            for (int k = 1; k <= sheet.getLastRowNum(); k++) {
                HSSFRow row = sheet.getRow(k);
                if(row !=null){
                    HSSFCell cell = row.getCell(1);
                    if(cell==null){
                        out.println("no data<br>");
                    }else{
                        String value =cell.getStringCellValue();

                      

                       
                        if(value.equals(templateName)){
                           
                          
                            HSSFCell srcQueryCell = row.getCell(5);
                            srcQuery=srcQueryCell.getStringCellValue().replaceAll("\r|\n","<br>");;

                            HSSFCell trgQueryCell = row.getCell(6);
                            trgQuery=trgQueryCell.getStringCellValue().replaceAll("\r|\n","<br>");
                            out.println(srcQuery+"<br> ");
                         
                        }
                        
        
                    }
                }
            }
        }
        %>
       
     
</p>
<h2>View <%=trgTemplateName%></h2>
<p>
        Create view [dbo].[<%=trgTemplateName%>] 
        <br>
        as 
        <br>
        <%out.println(trgQuery+"<br> ");%>
</p>

<h2>Excute SQL</h2>
<p>
       exec  USP_CompareR1AndR3Data '<%=templateName%>' ,'WFS'
</p>
<p>
        今天的日期是: <%= (new java.util.Date()).toLocaleString()%>
       
</p>
<%
    String connectionUrl = "jdbc:sqlserver://mysql:1443;" +
    "databaseName=NewDWH_DM_TOOL;integratedSecurity=true;";

    // Declare the JDBC objects.
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    out.println("test sql connection  <br>");
    try {
        // Establish the connection.
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionUrl);

            // Create and execute an SQL statement that returns some data.
            String querySql = "select * from BusinessLogicCmpDefine where TemplateName='"+templateName+"'";
            stmt = con.createStatement();
            rs = stmt.executeQuery(querySql);

            // Iterate through the data in the result set and display it.
            int isExists=0;
            while (rs.next()) {
                isExists=1;
                System.out.println(rs.getString(1) + " " + rs.getString(2));
                
            }
            out.println("test sql connection success <br>");
            if(isExists==1)
            {
                out.println("已经存在不用再创建！ <br>");
            }else
            {

                out.println("不存在 <br>");
            }
            
    }

    // Handle any errors that may have occurred.
    catch (Exception e) {
        e.printStackTrace();
        out.println("test sql connection fault  <br>");
    }

    finally {
        if (rs != null) try { rs.close(); } catch(Exception e) {}
            if (stmt != null) try { stmt.close(); } catch(Exception e) {}
            if (con != null) try { con.close(); } catch(Exception e) {}
    }
%>
</body>
</html>

 

posted on 2017-12-21 15:10  HelloHongfu  阅读(701)  评论(0)  编辑  收藏  举报

导航