JSP中SQL数据库编程技术

一，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 <col1>,<col2>,...,<coln>

From <tab1>,<tab2>,...,<tabm>

[Where<条件>]

条件中的子查询：

Where Not Exists(

Select * From tab2 Where col1=col2

)//当查询结果为空时，条件为真。

4，INSERT INTO <tab1> VALUES(<col1>, ...<coln>)

5，DELETE FROM <tab1> [WHERE<条件>]

6，UPDATE <tab1>

SET <tab1>=<vlu1>

...

[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(e=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),

)

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

)

actions

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作用域的变量使用

<jsp:useBean id="pool" scope="application" class="javastart.tools.ConnectionPool" />

<%@page import="java.sql.*"%>

<%@page import="javastart.tools.*"%>

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 rsmd=rs.getMetaData();

}

.....

finally{

pool.releaseConnection(con);

}

也可以使用一个Servlet初始化连接池