la base de données est sur un serveur de la société 10.154.249.173 je veux que je connecte à la base et j'affiche les résultats des requétes sql j'ai fait une page jsp qui se charge de l'affichage et un controleur TopBean.java voici le code de la classe TopBean.java:
package com.sagem.serveurpfe.controleur;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.sagem.serveurpfe.resultatliste.Top10threatcategory;
import com.sagem.serveurpfe.resultatliste.Topthreatsources;
import com.sagem.serveurpfe.resultatliste.Topuserdetected;
import com.sagem.serveurpfe.resultatliste.Unwantedporgrams;
public class TopBean {
private List <Top10threatcategory> ts1 ;
private List <Topthreatsources> ts2;
private List <Topuserdetected> ts3;
private List <Unwantedporgrams> ts4;
public String init()
{
ts1= new ArrayList<Top10threatcategory>();
ts2= new ArrayList<Topthreatsources>();
ts3= new ArrayList<Topuserdetected>();
ts4= new ArrayList<Unwantedporgrams>();
try{
System.out.println("connexion en cours...");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:sqlserver://10.154.249.173;user=sa;password=$agem001;database=ePO4_BENSRV33");
System.out.println("Connexion effective !");
Statement stmt = con.createStatement();
ResultSet rs1 = stmt.executeQuery("select top 1000 count(*) as 'count', [EPOEvents].[ThreatType], [EPOEvents].[ThreatName]from [EPOEvents] where ( ( ([EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ([EPOEvents].[AnalyzerName] not like N'%for%' ) ) and ([EPOEvents].[DetectedUTC] between '2015-02-27T14:44:04.306' and '2015-03-06T14:44:04.306' ) and ( ([EPOEvents].[ThreatType] = N'app' ) or ([EPOEvents].[ThreatType] = N'app_adware' ) or ([EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ([EPOEvents].[ThreatType] = N'app_pua' ) or ([EPOEvents].[ThreatType] = N'app_puo' ) or ([EPOEvents].[ThreatType] = N'app_package' ) or ([EPOEvents].[ThreatType] = N'app_rootkit' ) or ([EPOEvents].[ThreatType] = N'app_P2P' ) or ([EPOEvents].[ThreatType] = N'app_IM' ) or ([EPOEvents].[ThreatType] = N'app_keylogger' ) or ([EPOEvents].[ThreatType] = N'app_pwcracker' ) or ([EPOEvents].[ThreatType] = N'app_dialer' ) or ([EPOEvents].[ThreatType] = N'app_spyware' ) or ([EPOEvents].[ThreatType] = N'virus' ) or ([EPOEvents].[ThreatType] = N'comp' ) or ([EPOEvents].[ThreatType] = N'trojan' ) or ([EPOEvents].[ThreatType] = N'joke' ) or ([EPOEvents].[ThreatType] = N'test' ) ) ) group by [EPOEvents].[ThreatType], [EPOEvents].[ThreatName] order by 'count' desc, [EPOEvents].[ThreatType] asc,[EPOEvents].[ThreatName] asc");
ResultSet rs2 = stmt.executeQuery("select count(*) as 'count', [EPOEvents].[TargetUserName] from [EPOEvents] where ( ( ( [EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ( [EPOEvents].[AnalyzerName] not like N'%for%' ) ) and ( [EPOEvents].[DetectedUTC] between '2015-02-27T14:42:29.555' and '2015-03-06T14:42:29.555' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) or ( [EPOEvents].[ThreatType] = N'app_keylogger' ) or ( [EPOEvents].[ThreatType] = N'app_pwcracker' ) or ( [EPOEvents].[ThreatType] = N'app_dialer' ) or ( [EPOEvents].[ThreatType] = N'app_spyware' ) or ( [EPOEvents].[ThreatType] = N'virus' ) or ( [EPOEvents].[ThreatType] = N'comp' ) or ( [EPOEvents].[ThreatType] = N'trojan' ) or ( [EPOEvents].[ThreatType] = N'joke' ) or ( [EPOEvents].[ThreatType] = N'test' ) ) ) group by [EPOEvents].[TargetUserName] order by 'count' desc, [EPOEvents].[TargetUserName] asc");
ResultSet rs3 = stmt.executeQuery("select count(*) as 'count', [EPOEvents].[SourceHostName] from [EPOEvents] where ( ( ( [EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ( [EPOEvents].[AnalyzerName] not like N'%for%' ) ) and ( [EPOEvents].[DetectedUTC] between '2015-03-06T06:45:02.025' and '2015-03-06T14:45:02.025' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) or ( [EPOEvents].[ThreatType] = N'app_keylogger' ) or ( [EPOEvents].[ThreatType] = N'app_pwcracker' ) or ( [EPOEvents].[ThreatType] = N'app_dialer' ) or ( [EPOEvents].[ThreatType] = N'app_spyware' ) or ( [EPOEvents].[ThreatType] = N'virus' ) or ( [EPOEvents].[ThreatType] = N'comp' ) or ( [EPOEvents].[ThreatType] = N'trojan' ) or ( [EPOEvents].[ThreatType] = N'joke' ) or ( [EPOEvents].[ThreatType] = N'test' ) ) and ( ( [EPOEvents].[SourceHostName] is null or ( [EPOEvents].[SourceHostName] <> N'_' )) and ( not ( [EPOEvents].[SourceHostName] is null or ltrim( rtrim( [EPOEvents].[SourceHostName] ) ) = '' ) ) ) ) group by [EPOEvents].[SourceHostName] order by 'count' desc, [EPOEvents].[SourceHostName] asc");
ResultSet rs4 = stmt.executeQuery("select count(*) as 'count', datepart( YEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) as 'EPOEvents.DetectedUTC.year' , datepart( DAYOFYEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) as 'EPOEvents.DetectedUTC.dayofyear' from [EPOEvents] where ( ( ( [EPOEvents].[AnalyzerName] like N'VirusScan Ent%' ) and ( [EPOEvents].[AnalyzerName] not like N'%for%' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) ) ) and ( [EPOEvents].[DetectedUTC] between '2015-02-27T14:40:43.883' and '2015-03-06T14:40:43.883' ) ) group by datepart( YEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ), datepart( DAYOFYEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) order by datepart( YEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) desc, datepart( DAYOFYEAR, dateadd( MILLISECOND, 3600000, [EPOEvents].[DetectedUTC] ) ) desc");
while (rs1.next()) {
Top10threatcategory tr1= new Top10threatcategory();
System.out.print(rs1.getString(0));
tr1.setNbthreat(rs1.getString(0));
tr1.setTypethreat(rs1.getString(1));
tr1.setNomthreat(rs1.getString(2));
ts1.add(tr1);
}
/*
while (rs2.next()) {
Topthreatsources tr2= new Topthreatsources();
tr2.setNbthreat(rs2.getString(0));
tr2.setSourceName(rs2.getString(1));
ts2.add(tr2);
}
while (rs3.next()) {
Topuserdetected tr3= new Topuserdetected();
tr3.setNbthreat(rs3.getString(0));
tr3.setUsername(rs3.getString(1));
ts3.add(tr3);
}
while (rs4.next()) {
Unwantedporgrams tr4= new Unwantedporgrams();
tr4.setNbthreat(rs4.getString(0));
tr4.setYearofthreat(rs4.getString(1));
tr4.setMonthofthreat(rs4.getString(2));
ts4.add(tr4);
}
*/
} catch (Exception e) {
e.printStackTrace();
System.out.println("erreur de connexion !");
}
return null;
}
public List<Top10threatcategory> getTs1() {
init();
return ts1;
}
public void setTs1(List<Top10threatcategory> ts1) {
this.ts1 = ts1;
}
public List<Topthreatsources> getTs2() {
return ts2;
}
public void setTs2(List<Topthreatsources> ts2) {
this.ts2 = ts2;
}
public List<Topuserdetected> getTs3() {
return ts3;
}
public void setTs3(List<Topuserdetected> ts3) {
this.ts3 = ts3;
}
public List<Unwantedporgrams> getTs4() {
return ts4;
}
public void setTs4(List<Unwantedporgrams> ts4) {
this.ts4 = ts4;
}
}
et voici le code de la main.jsp :
<%@ page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsf/core" prefix="f"%>
<%@ taglib uri="http://java.sun.com/jsf/html" prefix="h"%>
<f:view>
<html>
<head>
</head>
<body>
<h:form><h:commandButton value="actualiser" action="#{topBean.init}"></h:commandButton></h:form></body>
</html>
</f:view>
la connexion est effective mais elle génére une SQLserverException : le jeu de résultat est fermé
Partager