JSP編程技巧：數據庫的利用
一，SQL復習
1，SQL語句分為兩類：DDL(Data Definition Language)和DML(Dat Manipulation Languge,數據操作語言)。前者主要是定義數據邏輯結構，包括定義表、視圖和索引;DML主要是對數據庫進行查詢和更新操作。
2，Create Table(DDL):
- Create Table tabName(
- colName1 colType1 [else],
- colName2 colType2 [else],
- ...,
- colNamen colTypen [else]
- );
- 例如：Cteate Table pJoiner(
- pno char(6) not null,
- eno char(6) nut null
- );
char int varchar等等都是用來定義列數據類型的保留字，其中varchar表示可變字符類型。
3，Select
條件中的子查詢：
- Where Not Exists(
- Select * From tab2 Where col1=col2
- )//當查詢結果為空時，條件為真。
4，INSERT INTO
5，DELETE FROM
6，UPDATE
- SET =
- ...
- =
- [WHERE<條件>]
- 例如：
- Update exployee
- Set age=27
- Where name=趙一
二，JDBC 主要接口：
java.sql.DriverManager類用于處理驅動程式的調入并且對新的數據庫連接提供支持。
java.sql.Connection，指應用程式和特定數據庫的連接。
java.sql.Statement，用于一般sql語句的執行（能是查詢、更新甚至能創建數據庫的執行過程）
java.sql.ResultSet,查詢所返回的結果保存在此對象中，用他能瀏覽和存取數據庫內的記錄。
1，通過jdbc-odbc橋使用odbc數據庫（并不必jdbc Drivers）
先在odbc DSN(Data Source Name)設置處設置pubs sysDSN,sa為username,密碼為空
- Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//加載驅動程式
- con=DriverManager.getConnection("jdbc:odbc:pubs","sa","");//jdbc:odbc:pubs
- con.close();
- //應當catch ClassNotFoundException和SQLException
Connection的getWarning方法返回一個SQLWarning對象，在連接之前應當先檢查。
使用jdbc-odbc的***好處是：免費的。不過性能受odbc的限制，而且一般odbc驅動比較昂貴。
2，使用專門的jdbc驅動程式。//此處是mm jdbc Driver
先將jar文件放在ClassPath里面。
- Class.forName("org.gjt.mm.mysql.Driver");
- con=DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname","root","");
- con.close();
可見使用何種方式連接何種數據庫和數據庫的操作和連接數據庫是無關的。
三，查詢數據庫
Statement stmt=con.createStatement();
stmt.setMaxRows()能控制輸出記錄***數量;
ResultSet rs=stmt.executeQuery("select .....");
ResultSet指向當前記錄:
int userId=rs.getInt("userid");
String userName=rs.getString("username");
...或用序號（從1開始的）
int userId=rs.getInt(1);
Stirng userName=rs.getString(2);
ClassNotFoundException是由于Class.forName()無法載入jdbc驅動程式觸發的
SQLException是jdbc在執行過程中發生問題時產生。有一個額外的方法
- getNextException()
- catch(SQLException e){
- out.println(e.getMessage());
- while(ee=e.getNextException()){
- out.println(e.getMessage());
- }
- }
一般來說并不建議在jsp中編寫數據庫的訪問程式，能將數據庫的訪問封裝在一個javabean中。
四，ResultSet深入
1，ResultSetMetaData
ResultSet rs=stmt.executeQuery("select....");
ResultSetMetaData rsmd=rs.getMetaData(); //獲取ResultSetMateData對象
int numberOfColumns=rsmd.getColumnCount();//返回列數
boolean b=rsmd.isSearchable(int i);//返回第i列是否能用于where子句
String c=rsmd.getColumnLabel(int i);//獲取第i列的列標
Objcet obj=rs.getObject();
if(obj!=null)out.println(obj.toString());
else println("");
2，SQL類型和ResultSet的getObject返回類型及對應的XXX getXXX()方法
SQL類型 JSP類型 對應的getXXX()方法
????????????????????????????????????????????
CHAR String String getString()
VARCHAR String String getString()
LONGVARCHAR String InputStream getAsciiStream()/getUnicodeStream()
NUMERIC java.math.BigDecimal java.math.BigDecimal getBigDecimal()
DECIMAL 同上
BIT Boolean boolean getBoolean()
TINYINT Integer byte getByte()
SMALLINT Integer short getShort()
INTEGER Integer int getInt()
BIGINT Long long getLong()
REAL Float float getFloat()
FLOAT Double double getDouble()
DOUBLE Double double getDouble()
BINARY byte[] byte[] getBytes()
VARBINARY byte[] byte[] getBytes()
LONGVARBINARY byte[] InputStream getBinaryStream()
DATE java.sql.Date java.sql.Date getDate()
TIME java.sql.Time java.sql.Time getTime()
TIMESTAMP java.sql.Timestamp java.sql.Timestamp getTimestamp()
3，null
int i=rs.getInt("age");
if(!rs.wasNull())....//RecordSet::wasNull()用來檢查null
4,存取大字符串和二進制文本
對于數據庫中longvarchar和langvarbinary進行流操作
ResultSet rs=stmt.executeQueryString("select ...");
BufferedReader br=new BufferedReader(new InputStream(rs.getAsciiStream("vol1")));//長文本串
BufferedReader br=new BufferedReader(new InputStream(rs.getUnicodeStream("vol1")));
BufferedReader br=new BufferedReader(new InputStream(rs.getBinaryStream("vol2")));//長二進制文本
//取數據必須在rs.getAsciiStream(), rs.getUnicodeStream(), rs.getBinaryStream()等之后馬上進行
五，瀏覽ResultSet
1，JDBC2.0提供了更多瀏覽ResultSet的方法
首先，確定你的jdbc驅動程式支持jdbc2.0
其次，由Connection生成Statement時要指定參數
Statement stmt=con.getStatement("游標類型", "記錄更新權限");
游標類型：
ResultSet.TYPE_FORWORD_ONLY:只能向前移動
ResultSet.TYPE_SCROLL_INSENSITIVE:可卷動。不過不受其他用戶對數據庫更改的影響。
ResultSet.TYPE_SCROLL_SENSITIVE:可卷動。當其他用戶更改數據庫時這個記錄也會改動。
記錄更新權限：
ResultSet.CONCUR_READ_ONLY,只讀
ResultSet.CONCUR_UPDATABLE,可更新
getStatement()缺省參數：getStatement(ResultSet.TYPE_FORWORD_ONLY, ResultSet.CONCUR_READ_ONLY)
2,如果ResultSet是可卷動的，以下函數能使用：
rs.absolute()//絕對位置，負數表示從后面數
rs.first()***條
rs.last()***一條
rs.previoust()前一條
rs.next()后一條
rs.beforeFirst()***條之前
rs.afterLast()***之后
rs.isFirst(),rs.isLast(),rs.isBeforeFirst(),rs.isAfterLast
注意，剛打開的時候是處于***條記錄之前的
六，更新數據庫
1，stmt.executeUpdate("strSql"),strSql是一條sql更新語句。update,insert,delete返回影響到的條數
2，stmt.execute()方法在不知道sql語句是查詢還是更新的時候用。如果產生一條以上的對象時，返回true,此時可用 stmt.getResultSet()和stmt.getUpdateCount()來獲取execute結果，如果不返回ResultSet對象則返回false.
3,除了Statement的executeUpdate之外還能用ResultSet:
rs.updateInt(1,10);
rs.updateString(2,"sfafd");
rs.updateRow();
七，使用預編譯PreparedStatement
PreparedStatement對象和Statement對象類似，都能用來執行SQL語句。不同在于，數據庫會對PreparedStatement的SQL語句進行預編譯，而且仍舊能輸入參數并重復執行編譯好的查詢速度比未編譯的要快。
PreparedStatement stmt=con.preparedStatement("Insert Into users(userid, username) values(?,?)");
stmt.clearParameters();
stmt.setInt(1,2);
stmt.setString(2,"Big");
stmt.executeUpdate();
八，執行存儲過程
1，JDBC調用存儲過程，并使用存儲過程的返回值。這樣能將處理工作分為服務端和客戶端兩部分，并大大加快系統的設計和研發的時間。比如能重復使用服務器上的組件。使用存儲過程之后大量諸計算工作能交給數據庫服務器來處理，這將降低Web服務器的負載，從而提高整個系統的性能。
2，有兩個表UserMain{UserID,UserName,UserType},UserRef{BrefID, UserID, UserBrief}
下面的存儲過程能接受jdbc傳來的參數，新增內容到UserMain和UserRef，并輸出一個OutUserID.
- CREATE PROCEDURE ap_adduser
- (
- @OutUserID int output, //此為輸出參數,output標記
- @UserName varchar(25), //參數表示方法："@XXX"為變量名，"變量名 類型 [output]"
- @UserType tinyint,
- @UserBrief varchar(255),
- )
- AS
- Declare @UserID int //定義局部變量
- insert into UserMain(UserName, UserType)
- values(@UserName,@UserType)
- select @UserID=@@IDENTITY //賦值用select,此處自動獲得ID
- insert into UserRef(UserID, UserBrief)
- select @OutUserID=@UserID
- GO/*結束，基本結構：
- CREATE PROCEDURE procedureName(
- parameters
- )
- AS
- actions
- GO
- */
JSP頁面中這樣使用：
- CallableStatement stmt=con.prepareCall("{call ap_adduser(?,?,?,?)}");
- stmt.registerOutParameter(1,Types.INTEGER,1);//注冊輸出變量
- stmt.setString(2,"edmund");
- stmt.setInt(3,1);
- stmt.setString(4,"description");
- stmt.execute();
- int userid=stmt.getInt(1);
- stmt.close()
八，使用事務
1，事務中的操作是個整體，要么都執行成功要么都不成功：事務開始后，如果所有的改動都正確，則使用commit方法將這些動作全部存入數據庫，否則就使用rollback取消所有的改動動作，而這時數據庫中的數據和執行事務前的是相同的。
2，使用事務時應當先用 con.setAutoCommit(false)，***使用commit或rollback
3，rollback一般在catch段執行
九，數據庫連接池
1，如果有一個數據庫連接請求并且連接中沒有連接，則生成一個新的連接。這個連接使用完之后并不關閉他，而是將他放入連接池。在這個過程中，還要判斷連接池中的連接是否超期。如果超期則將他關閉。
2，有非常多已有的Connection Pool包能使用。
3，一般將Connection Pool作為一個application作用域的變量使用
- DBConnection con=null;
- try{
- con=pool.getConnection("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:access","","");
- Statement stmt=con.createStatement();
- stmt.setMaxRows(10);
- String query=request.getParameter("quey");
- ResultSet rs=stml.executeQuery(query);
- ResultSetMetaData rsrsmd=rs.getMetaData();
- }
- .....
- finally{
- pool.releaseConnection(con);
- }
也能使用一個Servlet初始化連接池
【編輯推薦】
