bonjour,
j'ai fait un programme en java qui me permet de se connecter a une base de données MYSQL.
je voudrais savoir comment je peux extraire des données et après les envoyer via des Sockets.
alors si quelqu'un peut m'aider par un code ou une idée merci d'avance
/**
* Copyright (C) 2007-2010 CVIS
*
* @file LDMReader.java
* @author <a href='mailto:steve.pechberti@inrets.fr'> Steve PECHBERTI </a>
* @version 1.0.1.6
* @date 2007/01/15
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
*
* See the GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
*
*/
package org.cvisproject.cint.eda.provider.digitalmap.db.reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Set;
import java.util.StringTokenizer;
import org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition;
import org.cvisproject.cint.eda.sdk.gis.interfaces.IMapPOI;
import org.cvisproject.cint.eda.sdk.gis.interfaces.IRoadElement;
import org.cvisproject.cint.eda.sdk.gis.interfaces.position.IRAWPosition;
import org.cvisproject.cint.eda.sdk.gis.mapformat.MapPOI;
import org.cvisproject.cint.eda.sdk.gis.mapformat.RoadElement;
public class DBReader {
protected IRAWPosition m_Pos;
private HashMap<String, IRoadElement> m_Elements;
private HashMap<String, IRoadElement> m_Elements_bis;
private HashMap<Integer, IMapPOI> m_POI;
static final private String driver = "com.mysql.jdbc.Driver";
static final private String url_db = "jdbc:mysql://localhost/platadbosm";
//static final private String url_db = "jdbc:mysql://localhost/plataDBBALI";
static final private String user = "root";
static final private String passwd = "";
//static final private String passwd = "livic";
String tableSpeed="speed";
Connection con=null;
Statement s = null;
int i=0;
public DBReader() {
//ouverture de base
ConnectBase();
m_Pos = null;
m_Elements = new HashMap<String, IRoadElement>();
m_Elements_bis = new HashMap<String, IRoadElement>();
m_POI=new HashMap<Integer, IMapPOI>();
}
public void ConnectBase() {
try {
/** Etape 1: charger le pilote */
Class.forName(driver);
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
/** Etape 2: Etablissement de la connexion à la base de données */
con = DriverManager.getConnection(url_db, user, passwd);
/** Etape 3: produire le Statement */
s = con.createStatement();
System.out.println("driver load successfully");
} catch (Exception e) {
System.out.println("echec pilote : " + e);
}
}
public void DeConnectBase() {
try {
con.close();
} catch (Exception e) {
}
}
public synchronized Collection<IRoadElement> getRoadElementsIn(IRAWPosition _pt, double _rayon) {
Date eDate=new Date();
int k=0,l=0,m=0;
String attributes;
String table;
String clause;
ResultSet res;
String ID;
String LENGTH;
String NAME;
String SENS;
String COMMON;
String FW;
String REF;
String slatlon;
int all_geo_seg;
double LATITUDE;
double LONGITUDE;
double latmin,latmax,lonmin,lonmax;
StringTokenizer tokenizer,tokenizer2;
ArrayList<String> eltToRemove= new ArrayList<String>();
if(s == null) return null;
if(m_Pos != null ) {
if( (m_Pos.get_long() == _pt.get_long()) && (m_Pos.get_lat() == _pt.get_lat())
&& m_Elements_bis != null)
return null;
}
//System.out.println("nbre elements depart"+m_Elements_bis.size() );
//on tag les elements pour ne pas les remplir plusieurs fois
for (String selt: m_Elements_bis.keySet()) {
m_Elements_bis.get(selt).setiupdate(0);
}
m_Pos = _pt;
latmin=_pt.get_lat()- _rayon;
latmax=_pt.get_lat()+ _rayon;
lonmin=_pt.get_long()- _rayon;
lonmax=_pt.get_long()+ _rayon;
//récupération de tous les segments
attributes = "segment.id_seg,segment.name,segment.nature,segment.ref,segment.sens,segment.common,AsText(geometry)";
table = "segment";
clause = "MBRIntersects(GeomFromText('POLYGON((" +
latmin + " " + lonmin + "," +
latmin + " " + lonmax + "," +
latmax + " " + lonmax + "," +
latmax + " " + lonmin + "," +
latmin + " " + lonmin + "))'),geometry)";
try {
this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
m++;
ID = res.getString(1);
LENGTH = "100";
NAME = res.getString(2);
FW = res.getString(3);
REF = res.getString(4);
SENS = res.getString(5);
COMMON = res.getString(6);
slatlon = res.getString(7);
if (m_Elements_bis.containsKey(ID)){
m_Elements_bis.get(ID).setiupdate(1);
} else {
RoadElement elt = new RoadElement();
elt.setiupdate(2);
elt.setID(ID);
elt.setLength(Double.valueOf(LENGTH));
elt.setName(NAME);
elt.setDirection(IRoadElement.Direction.getDirectionForOpenStreetMap(SENS));
elt.setNature(FW);
elt.setRef(REF);
//segment communs
ArrayList allSegCommuns = new ArrayList();
if (COMMON!=null){
tokenizer = new StringTokenizer(COMMON);
while (tokenizer.hasMoreTokens()){
allSegCommuns.add(tokenizer.nextToken());
}
}
elt.setCommon(allSegCommuns);
//geometry
IRAWPosition[] geoms = null;
slatlon=slatlon.substring(11, slatlon.length()-1);
tokenizer = new StringTokenizer(slatlon,",");
all_geo_seg=tokenizer.countTokens();
geoms = new IRAWPosition[all_geo_seg];
int j=0;
while (tokenizer.hasMoreTokens()){
String mlatlon=tokenizer.nextToken();
tokenizer2 = new StringTokenizer(mlatlon);
LATITUDE = Double.valueOf(tokenizer2.nextToken());
LONGITUDE = Double.valueOf(tokenizer2.nextToken());
geoms[j++] = new RAWPosition(LONGITUDE, LATITUDE, 0.0);
}
elt.setGeometry(geoms);
m_Elements_bis.put(elt.getID(), elt);
}
}
} catch (Exception e) {
System.out.println("Probleme lecture DB Segment ");
}
//System.out.println("nbre Segments trouvés"+ m);
for (String selt: m_Elements_bis.keySet()) {
if (m_Elements_bis.get(selt).getiupdate()==2) {
k++;
getSpeedInDB(selt);
}
}
//on remove les elements non taggés
//tous les elements à 0 sont à effacer,on les stocke
for (String selt: m_Elements_bis.keySet()) {
if (m_Elements_bis.get(selt).getiupdate()==0) {
l++;
eltToRemove.add(selt);
}
}
for (i=0;i<eltToRemove.size();i++){
m_Elements_bis.remove(eltToRemove.get(i));
}
//System.out.println("nbre elements updates"+k );
//System.out.println("nbre elements deletes"+l );
//System.out.println("nbre elements "+m_Elements_bis.size() );
setArrayElement(m_Elements_bis);
Date sDate=new Date();
//System.out.println("temps DB = "+ (sDate.getTime()-eDate.getTime()));
return m_Elements_bis.values();
}
public synchronized Collection<IMapPOI> getPOIIn(IRAWPosition _beg, IRAWPosition _end) {
String attributes;
String table;
String clause;
String order;
ResultSet res;
double LATITUDE;
double LONGITUDE;
int VALUE;
int ID_POI;
RAWPosition rp = null;
//récupération panel
String typePanel="Panel";
attributes = "id_poi,latitude,longitude,value";
table = "poi";
clause = "(longitude BETWEEN "+ _beg.get_long() + " AND " + _end.get_long() + " AND " +
"latitude BETWEEN "+ _beg.get_lat() + " AND " + _end.get_lat() + ")" + " AND type = " + "\""+ typePanel + "\"" ;
order="id_poi";
try{
this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause+ " ORDER BY " + order);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
ID_POI=res.getInt("ID_POI");
LATITUDE = res.getDouble("latitude");
LONGITUDE = res.getDouble("longitude");
VALUE = res.getInt("value");
MapPOI poi=new MapPOI(ID_POI, LATITUDE, LONGITUDE,VALUE);
m_POI.put(ID_POI, poi);
}
} catch (Exception e) {
System.out.println("Probleme lecture DB POI ");
}
//récupération vehicule
typePanel="Vehicule";
attributes = "id_poi,latitude,longitude,value";
table = "poi";
clause = "(longitude BETWEEN "+ _beg.get_long() + " AND " + _end.get_long() + " AND " +
"latitude BETWEEN "+ _beg.get_lat() + " AND " + _end.get_lat() + ")" + " AND type = " + "\""+ typePanel + "\"" ;
order="id_poi";
try{
this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause+ " ORDER BY " + order);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
ID_POI=res.getInt("ID_POI");
LATITUDE = res.getDouble("latitude");
LONGITUDE = res.getDouble("longitude");
VALUE = res.getInt("value");
MapPOI poi=new MapPOI(ID_POI, LATITUDE, LONGITUDE,VALUE);
m_POI.put(ID_POI, poi);
}
} catch (Exception e) {
System.out.println("Probleme lecture DB POI ");
}
setArrayPOI(m_POI);
return m_POI.values();
}
public synchronized Collection<IMapPOI> getPOIVehiculeIn(IRAWPosition _beg, IRAWPosition _end) {
String attributes;
String table;
String clause;
String order;
ResultSet res;
double LATITUDE;
double LONGITUDE;
int VALUE;
int ID_POI;
RAWPosition rp = null;
//récupération géomètrie
String typePanel="Vehicule";
attributes = "id_poi,latitude,longitude,value";
table = "poi";
clause = "(longitude BETWEEN "+ _beg.get_long() + " AND " + _end.get_long() + " AND " +
"latitude BETWEEN "+ _beg.get_lat() + " AND " + _end.get_lat() + ")" + " AND type = " + "\""+ typePanel + "\"" ;
order="id_poi";
try{
this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause+ " ORDER BY " + order);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
ID_POI=res.getInt("ID_POI");
LATITUDE = res.getDouble("latitude");
LONGITUDE = res.getDouble("longitude");
VALUE = res.getInt("value");
MapPOI poi=new MapPOI(ID_POI, LATITUDE, LONGITUDE,VALUE);
m_POI.put(ID_POI, poi);
}
} catch (Exception e) {
System.out.println("Probleme lecture DB POI ");
}
setArrayPOI(m_POI);
return m_POI.values();
}
private void setArrayElement(HashMap<String, IRoadElement> _m_Elements){
m_Elements=_m_Elements;
}
public HashMap<String, IRoadElement> getCurrentRoadElementMap() {
return m_Elements;
}
public Set<String> getCurrentRoadElementKeysCollection() {
return m_Elements.keySet();
}
public Collection<IRoadElement> getCurrentRoadElementCollection() {
return m_Elements.values();
}
private void setArrayPOI(HashMap<Integer, IMapPOI> _m_POI){
m_POI=_m_POI;
}
public Collection<IMapPOI> getCurrentPOICollection() {
//System.out.println("nbre de poi à afficher "+m_POI.size());
return m_POI.values();
}
public void getSpeedInDB(String selt) {
String attributes,clause,order;
double SPEED,ABSCURDEBUT,ABSCURFIN;
ResultSet res;
try {
//récupération vitesse
attributes = "value,abs_cur_beg,abs_cur_end";
clause = "(id_seg LIKE "+ m_Elements_bis.get(selt).getID() + ")";
order="id_speed";
this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause+ " ORDER BY " + order);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
SPEED = res.getDouble("value");
ABSCURDEBUT = res.getDouble("abs_cur_beg");
ABSCURFIN = res.getDouble("abs_cur_end");
m_Elements_bis.get(selt).getMandatorySpeedDistribution().addNewRules(new double[]{ABSCURDEBUT,ABSCURFIN,SPEED}, new double[]{ABSCURDEBUT,ABSCURFIN,SPEED});
}
} catch (Exception e) {
System.out.println("Probleme lecture DB speed ");
}
}
public void getSpeedInDBforElement(RoadElement elt) {
String attributes,clause,order;
double SPEED,ABSCURDEBUT,ABSCURFIN;
ResultSet res;
elt.getMandatorySpeedDistribution("newSLD");
try {
//récupération vitesse
attributes = "value,abs_cur_beg,abs_cur_end";
clause = "(id_seg LIKE "+ elt.getID() + ")";
order="id_speed";
this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause+ " ORDER BY " + order);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
SPEED = res.getDouble("value");
ABSCURDEBUT = res.getDouble("abs_cur_beg");
ABSCURFIN = res.getDouble("abs_cur_end");
elt.getMandatorySpeedDistribution().addNewRules(new double[]{ABSCURDEBUT,ABSCURFIN,SPEED}, new double[]{ABSCURDEBUT,ABSCURFIN,SPEED});
}
} catch (Exception e) {
System.out.println("Probleme lecture DB speed ");
}
}
public void setSpeedInDB(String id_seg, Integer id_speed, Double value, Double abs_cur_beg, Double abs_cur_end, String myDate) {
String request;
request = "UPDATE " + tableSpeed + " SET value = " + value + ", abs_cur_beg = " + abs_cur_beg + ", abs_cur_end = " + abs_cur_end +", lastmodified = " + "\""+ myDate + "\""+ " WHERE id_seg = " + id_seg + " AND id_speed = " + id_speed;
//System.out.println("request "+request);
try {
s.executeUpdate (request);
} catch (SQLException e) {
System.out.println("echec requete : " + e);
}
}
public void setPartSpeedInDB(String id_seg, Integer id_speed, Double value) {
String request;
request = "UPDATE " + tableSpeed + " SET value = " + value + " WHERE id_seg = " + id_seg + " AND id_speed = " + id_speed;
//System.out.println("request "+request);
try {
s.executeUpdate (request);
} catch (SQLException e) {
System.out.println("echec requete : " + e);
}
}
public void setPOIInDB(String id_seg, Integer id_speed, Double value, Double abs_cur, Double abs_cur_end, String myDate) {
String typePanel="Panel";
String request;
request = "UPDATE poi SET value = " + value + " WHERE id_seg = " + id_seg + " AND abs_cur LIKE " + abs_cur+ " AND type = " + "\""+ typePanel + "\"" ;
System.out.println("request "+request);
try {
s.executeUpdate (request);
} catch (SQLException e) {
}
}
public void setVehiculePOIInDB(String emetteurID, double _lat, double _long) {
int iallreadyhere = 0;
ResultSet res = null;
String request;
String typePanel="Vehicule";
int value=0;
try {
this.s.executeQuery("SELECT COUNT(*) AS vehicule FROM poi WHERE id_poi = " + emetteurID);
res.next();
iallreadyhere= res.getInt("vehicule");
} catch (Exception e) {}
if (iallreadyhere==0){
if (emetteurID=="AA-000-AA"){
value=1;
}else{
value=1;
}
request = "INSERT INTO poi (id_poi, type,latitude,longitude,value)";
request += ") VALUES (" +emetteurID;
request += ", " + typePanel;
request += ", " +_lat;
request += ", " +_long;
request += ", " + value;
request += ");";
try {
int r = s.executeUpdate (request);
} catch (SQLException e) {
}
}else{
request = "UPDATE poi SET latitude = " + _lat + " AND longitude = " + _long + " WHERE id_poi = " + emetteurID ;
try {
s.executeUpdate (request);
} catch (SQLException e) {}
}
}
//renvoie la date en milliseconde de la derniere modification dans la table Speed
public long getLastUpdateDate() {
long lresult=0;;
Timestamp result = null;
ResultSet res;
try {
this.s.executeQuery("SELECT MAX(lastmodified) AS maxdate FROM " + tableSpeed );
res =s.getResultSet();
res.first();
result=res.getTimestamp("maxdate");
System.out.println("date DB last modification "+result);
lresult=result.getTime();
} catch (SQLException e) {
System.out.println("Probleme lecture DB LastUpdateDate");
} catch (Exception e) {
System.out.println("Probleme lecture DB LastUpdateDate");
}
return lresult;
}
public ArrayList segmentIDTOUpdateList(long _div) {
ArrayList alseglistToUpdate=new ArrayList();
ResultSet res;
String attributes,clause;
String resultat;
String olddate= millisecondesToDateFormatedTimeStamp(_div);
try {
attributes = "id_seg,id_speed,value,abs_cur_beg,abs_cur_end,lastmodified";
clause = "(lastmodified > "+ "\""+ olddate + "\""+ ")";
this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
resultat = res.getString("id_seg");
resultat = resultat + "," +res.getInt("id_speed");
alseglistToUpdate.add(resultat);
}
} catch (Exception e) {
System.out.println("Probleme lecture DB speed ");
}
return alseglistToUpdate;
}
public String segmentIDTOUpdate(String _segid) {
ResultSet res;
String attributes,clause;
String resultat = null;
StringTokenizer tokenizer = new StringTokenizer(_segid,",");
String ID_SEG=tokenizer.nextToken();
Integer ID_SPEED=Integer.valueOf(tokenizer.nextToken());
try {
attributes = "id_seg,id_speed,value,abs_cur_beg,abs_cur_end,lastmodified";
clause = "id_seg = " + ID_SEG + " AND id_speed = " + ID_SPEED;
this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause);
res =s.getResultSet();
res.beforeFirst();
while (res.next()) {
resultat = res.getString("id_seg");
resultat = resultat + "," +res.getInt("id_speed");
resultat = resultat + "," +res.getDouble("value");
resultat = resultat + "," +res.getDouble("abs_cur_beg");
resultat = resultat + "," +res.getDouble("abs_cur_end");
resultat = resultat + "," +res.getTimestamp("lastmodified");
}
} catch (Exception e) {
System.out.println("Probleme lecture DB speed ");
}
return resultat;
}
public String millisecondesToDateFormatedTimeStamp(long _d) {
String sD = null;
java.text.SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date d=new Date(_d);
sD=format.format(d);
return sD;
}
static public void main(String[] _argv) {
double EHORIZON_RANGE = 0.002;
DBReader dbr = new DBReader();
//Date d=new Date();
//Date d2= new Date(d.getTime()-7200000);
//Date d1=dbr.setSpeedInDB("31110866", 0, 30.0, 0.0, 111.333997669694);
//long titi=dbr.getLastUpdateDate();
//String al=dbr.segmentIDTOUpdate("31110866,0");
dbr.getRoadElementsIn((org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition) new RAWPosition(2.089261547 , 48.78770688, 0.0 ), EHORIZON_RANGE);
//dbr.getPOIIn((org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition)new RAWPosition(2.10241166666667 - EHORIZON_RANGE, 48.782663333333 - EHORIZON_RANGE, 0.0),
// (org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition)new RAWPosition(2.10241166666667 + EHORIZON_RANGE, 48.782663333333+ EHORIZON_RANGE, 0.0));
}
}
ce code me permet de se connecter a la base de données et j'ai fait la sélection maintenant je veux envoyer les paramètre du géométrie via des Sockets vers un serveur C++
Jeu du carré rouge modifié, quel niveau atteindrez-vous ? http://squared.go.yj.fr