/*
La table plus simple il n'y a pas :
USE [jspTest]
GO
/****** Objet : Table [dbo].[test1] Date de génération du script : 09/25/2006 23:23:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test1](
[id] [int] IDENTITY(1,1) NOT NULL,
[prenom] [nchar](50) COLLATE French_CI_AS NOT NULL,
[nom] [nchar](50) COLLATE French_CI_AS NOT NULL,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
*/
// 1 - la classe permettant d'accéder aux données :
/*
* SqlConnection.java
*
* Created on 23 septembre 2006, 12:26
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package sqlserver.mydb.common;
/**
*
* @author david
*/
import java.io.*;
import java.sql.*;
public class SqlConnection {
/**
* Creates a new instance of SqlConnection
*/
public SqlConnection() {
}
private String driver;
private String url;
private Connection cnx;
private Statement st;
public boolean ConnectToSQL()
{
try
{
Class.forName(getDriver());
cnx=DriverManager.getConnection(getUrl());
st=cnx.createStatement();
return true;
}
catch(SQLException ex)
{
System.out.println(ex.getMessage().toString());
return false;
}
catch(ClassNotFoundException ex)
{
System.out.println(ex.getMessage().toString());
return false;
}
}
public boolean CloseSql()
{
try
{
this.cnx.close();
return true;
}
catch(SQLException ex)
{
System.out.println(ex.getMessage().toString());
return false;
}
}
public ResultSet Requete(String req)
{
ResultSet rs=null;
try{
rs=st.executeQuery(req);
return rs;
}
catch(SQLException ex)
{
rs=null;
return rs;
}
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
public void setUrl(String url)
{
this.url=url;
}
}
// 2 - La page jsp
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>JSP Page</h1>
<%
boolean isOK=false;
sqlserver.mydb.common.SqlConnection myConn=new sqlserver.mydb.common.SqlConnection();
myConn.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
myConn.setUrl("jdbc:sqlserver://localhost:1433;databaseName=jspTest;user=*****;password=******");
isOK=myConn.ConnectToSQL();
if(!isOK)
{
out.println("Erreur");
}
else
{
out.println("OK");
ResultSet rs1=myConn.Requete("select * from test1");
int i=0;
while(rs1.next()){
i++;
out.println("<li>" + i + " " + rs1.getString("Prenom") + "-" + rs1.getString("nom"));
}
myConn.CloseSql();
}
%>
</body>
</html>