Tutorial III: SQLBuilder for searching #1

<< Tutorial II | Top | Next: Tutorial III#2 >>

  Download Source code
   
 
   
 

การ Build SQL เพื่อใช้ในการค้นหาข้อมูลตาม Criteria ที่ผู้ใช้ระบุ

ก่อนอื่นขอยกตัวอย่างก่อนว่า หากไม่ใช้ Framework เลยจะต้องเขียน code อย่างไร (ขอยกตัวอย่างการค้นหาด้วย Criteria เฉพาะบางส่วนเพื่อไม่ให้ยาวจนเกินไป)
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
String sql_wh="";
String prod_name=request.getParameter("prod_name");
if(prod_name!=null && prod_name.length()>0){
	if(sql_wh.length()>0) sql_wh+=" AND ";
	sql_wh+="prod_name like '%"+prod_name+"%'";
}
String prod_price=request.getParameter("prod_price");
if(prod_price!=null && prod_price.length()>0){ 
	if(sql_wh.length()>0) sql_wh+=" AND ";
	sql_wh+="prod_price = "+prod_price;
}
String payment=request.getParameter("payment");
if(payment!=null && payment.length()>0){
	if(sql_wh.length()>0) sql_wh+=" AND ";
	sql_wh+="payment = '"+payment+"'";
}
String stock=request.getParameter("stock");
if(stock!=null && stock.length()>0){
	if(sql_wh.length()>0) sql_wh+=" AND ";
	sql_wh+="stock = "+stock;
}
if(sql_wh.length()>0) sql+=" WHERE " + sql_wh;
   
 

หากต้องการใช้ PreparedStatement จะมีความยุ่งยากเพิ่มขึ้นอีก เนื่องจาก เวลา set parameter ให้กับ PreparedStatement จะต้องระบุลำดับที่ซึ่งไม่ตายตัว เช่น ถ้าไม่ได้กรอก prod_name เอาไว้ prod_price ก็จะเป็น parameter ลำดับที่ 1 แทนที่จะเป็นลำดับที่ 2 เป็นต้น

 

การนำ SQLBuilder มาใช้

เปลี่ยน coding ดังกล่าวให้เป็น ดังนี้

 
1
2
3
4
5

SQLBuilder sb=new SQLBuilder();
String sql="select * from product where 1=1" + sb.genSearchSQL(request);
SmartStmt sStmt=rCtrl.getSmartStmt(sql);
sb.applySearchPstmt(request,sStmt.pstmt);
sStmt.executeQuery();
   
 
  • ขั้นแรกสร้าง Object SQLBuilder ขึ้นมาเสียก่อน (บรรทัดที่ 1)
  • ต่อมาใช้คำสั่ง genSearchSQL() เพื่อสร้าง SQL ในส่วนของเงื่อนไขการค้นหา (บรรทัดที่ 2)
  • สร้าง SmartStmt โดย PreparedStatement จะถูกสร้างในขั้นตอนนี้ (บรรทัดที่ 3)
  • Set ค่า Parameter ให้กับ PreparedStatement (บรรทัดที่ 4)
  • ทำการ executeQuery ตามปกติ (บรรทัดที่ 5)

 

Question: SQLBuilder รู้ได้อย่างไรว่ามีการใช้เงื่อนไขอะไรในการค้นหาข้อมูลบ้าง

การที่จะให้ SQLBuilder สร้าง SQL ให้จะต้องตั้งชื่อ input ใน HTML Form ให้อยู่ในรูปแบบดังนี้

wx_{datatype code}{operator code}__{column name}


(ข้อควระวัง: ระหว่าง operator code กับ column name จะคั่นด้วย underline 2 ตัว ไม่ใช่ตัวเดียว)

โดย datatype code ประกอบด้วย

Code รายละเอียด
I Integer
F Float
T Text
D Date
H Date time

Operator code ประกอบด้วย

Code รายละเอียด
eq Equal
ne Not Equal
gt Greater than
ge Greater than or equal
lt Less than
le Less than or equal
ma Match any position
ms Match at starting
me Match at ending


หลักการทำงานของ SQLBuilder คือ จะดึง request parameter ทั้งหมดมาตรวจสอบ หากเข้าเงื่อนไขขึ้นต้นด้วย wx_ ก็จะถือว่าเป็น ข้อมูลที่เป็นเงื่อนไขการค้นหา จากนั้นก็จะทำการแปล datatye code และ operator code เพื่อทำการ Generate SQL ต่อไป โดยสรุปคือหากต้องการให้ SQLBuilder สร้าง SQL ให้ ก็ตั้งชื่อตาม format ที่กำหนด หากข้อมูลใดต้องการที่จะ สร้าง SQL เองก็ให้ตั้งชื่ออะไรก็ได้

 

Question: ทำไมต้องมีการใส่เงื่อนไข WHERE 1=1 ด้วย

จริงๆ แล้วเป็น Trick ที่ช่วยให้การเขียน code สั้นลง ซึ่งโดยปกติแล้วก่อนใส่ WHERE จะต้องตรวจสอบก่อนว่ามีเงื่อนไขอย่างน้อย 1 ตัว และเงื่อนไขตัวแรกจะำไม่มี AND นำหน้า การใส่เงื่อนไข 1=1 ลงไปทำให้ไม่จำเป็นต้องมีการ check ก่อน (ดูตัวอย่าง code ในกรณีที่ไม่ใช้ Framework ประกอบ)

 

Question: จะสามารถ customize ขั้นตอนการ Generate SQL ได้หรือไม่

ในกรณีที่มีบาง field ที่พิเศษ มีการ generate sql ที่ต่างจากกรณีปกติ ก็สามารถที่จะ generate sql เองได้ตามตัวอย่าง ต่อไปนี้

 
1
2
3
4
5
6
7
8
 
SQLBuilder sb=new SQLBuilder();
String sql="select * from product where status=?" + sb.genSearchSQL(request);
SmartStmt sStmt=rCtrl.getSmartStmt(sql);
String status=request.getParameter("status");
if(status==null || status.length()==0) status="ACTIVE";
sStmt.pstmt.setString(1,status);
sb.applySearchPstmt(request,sStmt.pstmt,2);
sStmt.executeQuery();
   
 

จากตัวอย่าง บรรทัดที่ 2 มีการกำหนด SQL เองสำหรับ column status และสร้าง SQL อัตโนมัติสำหรับ column อื่นๆ ที่เหลือ จากนั้นบรรทัดที่ 5 ทำการ set parameter ให้กับค่า status ซึ่งในขั้นตอนนี้สามารถ customize ได้เองตามความต้องการ

บรรทัดที่ 7 ในการเรียก applySearchPstmt() จะต่างจากกรณีปกติ คือจะมีการระบุด้วยว่า เริ่ม set parameter ตัวที่เท่าไหร่ (หากไม่มีการ customize จะเริ่มจากตัวที่ 1 โดย default)