Posted on 2009-05-18 15:29
Gavin.lee 閱讀(434)
評論(0) 編輯 收藏 所屬分類:
JDBC
這個是我在wap項目中用到的DB BEAN。
這個看起來，使用起來都會很方便。感覺封裝的很好，其實不然。這里用的并不是SQL預編譯，PreparedStatement。每每在使用的時候都是直接合成后送到這里。安全性非常的不好。很容易引起SQL注入性侵入。另外這樣寫的通用性非常的不好。表現的最典型的就是update。下面先看做法，再貼兩個使用的實例：
package com.yixun.wap;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/** *//** *//** *//** *//** *//** *//** *//**
*
* @descripte get DB connection
* @author Gavin.lee
* @date 2009-5-18下午03:11:34
* @version 1.0
*
*/
public class DBBean 
{
private String Driver = "";
private String Url ="";
private String Username ="";
private String Password ="";
public CallableStatement cstmt;
private Statement statement = null;
private Connection conn = null;
private ResultSet rs = null;
public DBBean() {
Properties prop = new Properties();
try {
//load class by absolute path
InputStream is = Class.forName("com.yixun.wap.DBBean").getResourceAsStream("/dbsource.properties");
prop.load(is);
if (is != null) {
is.close();
}
Driver = prop.getProperty("Driver");
Url = prop.getProperty("Url");
Username = prop.getProperty("Username");
Password = prop.getProperty("Password");
System.setProperty("jdbc.drivers", Driver);
Class.forName(Driver);
conn = DriverManager.getConnection(Url,Username,Password);
} catch (ClassNotFoundException e) {
System.out.println("Unable to load driver.\n" + e.getMessage());
} catch (IOException e) {
System.out.println("Unable to read File stream.\n" + e.getMessage());
} catch (SQLException e) {
System.out.println("Unable to get the connection.\n" + e.getMessage());
}
}
public Connection getConnection(){
return (conn);
}
public ResultSet executeQuery(String sql) {
rs = null;
try {
statement = conn.createStatement();
rs = statement.executeQuery(sql);
} catch (SQLException ex) {
System.err.println("aq.executeQuery: " + ex.getMessage());
System.err.println("aq.executeQuerystrSQL: " + sql);
}
return rs;
}
public void executeUpdate(String sql) {
try {
statement = conn.createStatement();
statement.executeUpdate(sql);
} catch (SQLException ex) {
System.err.println("aq.executeUpdate: " + ex.getMessage());
System.err.println("aq.executeUpadatestrSQL: " + sql);
}
}
public ResultSet executeWapProc(String procName, Object[] params) {
rs = null;
try {
int index = 0;
cstmt = conn.prepareCall(procName);
for (Object obj : params) {
index++;
cstmt.setObject(index, obj);
}
rs = cstmt.executeQuery();
} catch (SQLException ex) {
System.err.println("NewsDbBean**:" + ex.getMessage());
}
return rs;
}
public void destroy() {
try {
if(statement != null)
statement.close();
if(cstmt != null)
cstmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
dbsource.properties文件：
#dbsource.properties
Driver = net.sourceforge.jtds.jdbc.Driver
Url = jdbc:jtds:sqlserver://121.14.110.49:1433/wubai_wapcp
Username = wap
Password = esunxyz500wan!^wap
使用實例：
DBBean db = new DBBean();
String sql1 = "select cpusername,cppassword from logininterface where cpsid='"+ this.ck + "'";
ResultSet rs =null;
try{
rs = db.executeQuery(query);
while(rs.next()){
un=rs.getString("cpusername");
pw=rs.getString("cppassword");
}
}catch(Exception e){
System.err.println(e.getMessage());
}
String sql2 = "insert into BuyService values('"+un+"','"+pw+"','"+0+"','"+this.lotid+"','"+this.playid+"','"+this.expect+"','"+this.allmoney+"','"+this.zhushu+"','"+this.beishu+"','"+this.title+"','"+this.codes+"','"+this.ck+"','"+this.dest_src+"','"+this.R_des+"','"+this.R_um+"','"+this.R_id+"',getdate(),'"+super.getHZFID()+"')";
try{
db.executeUpdate(inset);
}catch(Exception e){
e.printStackTrace();
}finally{
rs=null;
db.destroy();
}
存儲過程的使用："在sql server 中：exec wap_biz_addContentClick'001'"
public void addClick(String id){
NewsDbBean dbBean = new NewsDbBean();
Object[] ob = new Object[1];
ob[0]=id;
try {
String procName="{call wap_biz_addContentClick(?)}";
dbBean.executeWapProc(procName, ob);
} catch (Exception e) {
e.printStackTrace();
}finally{
dbBean.destroy();
}
}
update這里我就不寫了，操作類似上面，就因為它沒有使用預編譯sql，每需要一次跟新，就需要一條sql語句。所以通用性非常不好。