1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
| package DAO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.Blob;
import com.mysql.jdbc.PreparedStatement;
import Bean.Brackets;
import ConnexionBDD.ConnexionBDD;
//Ici, on fait du Objet Mapping Relationnel (ORM)
public class BracketDaoImpl implements IBracketDao {
// ASSOCIATION DES 3 LABELS AVEC LE BOUTON "SEARCH"
@Override
public List<Brackets> bracketTotal(String area, String position, String elecroute, String bsolution1,
String refImg) {
List<Brackets> brack = new ArrayList<Brackets>();
Connection connection = ConnexionBDD.getConnection();
try {
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(/*
* "SELECT area,position,elecroute,bsolution1,\r\n"
* + "bsolution2,bsolution3,\r\n" +
* " bsolution4,bsolution5\r\n" +
* "FROM brackets\r\n" +
* "WHERE area LIKE ? AND position LIKE ? AND elecroute LIKE ? AND bsolution1 LIKE ? LIMIT 100"
* );
*/
"SELECT area,position,elecroute,bsolution1,img1.photoName as png1,img1.photo as img1, bsolution2, img2.photoName as png2,img2.photo as img2, bsolution3,img3.photoName as png3,img3.photo as img3, bsolution4, img4.photoName as png4,img4.photo as img4, bsolution5, img5.photoName as png5,img5.photo as img5"
+ " FROM brackets2 "
+ " LEFT OUTER JOIN brackets_images as img1 on brackets2.bsolution1=img1.refImg"
+ " LEFT OUTER JOIN brackets_images as img2 on brackets2.bsolution2=img2.refImg"
+ " LEFT OUTER JOIN brackets_images as img3 on brackets2.bsolution3=img3.refImg"
+ " LEFT OUTER JOIN brackets_images as img4 on brackets2.bsolution4=img4.refImg"
+ " LEFT OUTER JOIN brackets_images as img5 on brackets2.bsolution5=img5.refImg"
+ " WHERE area LIKE ? AND position LIKE ? AND elecroute LIKE ? AND brackets2.bsolution1 LIKE ? AND img1.refImg LIKE ?");
ps.setString(1, area);
ps.setString(2, position);
ps.setString(3, elecroute);
ps.setString(4, bsolution1);
ps.setString(5, refImg);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
// ORM
Brackets b = new Brackets();
b.setArea(rs.getString("area"));
b.setPosition(rs.getString("position"));
b.setElecroute(rs.getString("elecroute"));
b.setBsolution1(rs.getString("bsolution1"));
b.setBsolution2(rs.getString("bsolution2"));
b.setBsolution3(rs.getString("bsolution3"));
b.setBsolution4(rs.getString("bsolution4"));
b.setBsolution5(rs.getString("bsolution5"));
brack.add(b);
}
} catch (Exception e) {
e.printStackTrace();
}
return brack;
}
@Override
public java.sql.Blob affImg(String param) {
Connection connection = ConnexionBDD.getConnection();
Blob blob = null;
try {
PreparedStatement ps = (PreparedStatement) connection
.prepareStatement("SELECT photo FROM brackets_images WHERE refImg = ?");
ps.setString(1, param);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
blob = (Blob) rs.getBlob("photo");
}
} catch (Exception e) {
e.printStackTrace();
}
return blob;
}
} |
