JSP中使用数据库.doc

1、 JSP中使用数据库例子 1showByJdbcOdbc.jsp3000“);out.print(“);out.print(“);out.print(“+“雇员号“);out.print(“+“姓名“);out.print(“+“出生日期“);out.print(“+“薪水“);out.print(“);while(rs.next() out.print(“);out.print(“+rs.getString(1)+“); out.print(“+rs.getString(2)+“);out.print(“+rs.getDate(“birthday“)+“); out.print(“+rs.g

2、etFloat(“salary“)+“);out.print(“) ; out.print(“);con.close();catch(SQLException e) out.print(e);%例子 2showBySQLDriver.jsp3000“);out.print(“);out.print(“);out.print(“+“雇员号“);out.print(“+“姓名“);out.print(“+“出生日期“);out.print(“+“薪水“);out.print(“);while(rs.next() out.print(“);out.print(“+rs.getString(1)+“)

3、; out.print(“+rs.getString(2)+“);out.print(“+rs.getDate(“birthday“)+“); out.print(“+rs.getFloat(“salary“)+“);out.print(“) ; out.print(“);con.close();catch(SQLException e) out.print(e);%例子 3QueryBean.javapackage database.operation;import java.sql.*;public class QueryBean String databaseName=“; /数据库名S

4、tring tableName=“; /表名String user=“ ; /用户String secret=“ ; /密码 StringBuffer queryResult; /查询结果public QueryBean() queryResult=new StringBuffer();try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);catch(Exception e) public void setDatabaseName(String s) databaseName=s.trim();queryResult

5、=new StringBuffer(); public String getDatabaseName() return databaseName;public void setTableName(String s) tableName=s.trim();queryResult=new StringBuffer();public String getTableName() return tableName;public void setSecret(String s) secret=s.trim();queryResult=new StringBuffer();public String get

6、Secret() return secret;public void setUser(String s) user=s.trim();queryResult=new StringBuffer();public String getUser() return user; public StringBuffer getQueryResult() Connection con;Statement sql; ResultSet rs;try queryResult.append(“); String uri= “jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=“

7、+databaseName;String id=user;String password=secret;con=DriverManager.getConnection(uri,id,password);DatabaseMetaData metadata=con.getMetaData();ResultSet rs1=metadata.getColumns(null,null,tableName,null);int 字段个数=0;queryResult.append(“);while(rs1.next() 字段个数+;String clumnName=rs1.getString(4);query

8、Result.append(“+clumnName+“);queryResult.append(“);sql=con.createStatement();rs=sql.executeQuery(“SELECT * FROM “+tableName);while(rs.next() queryResult.append(“);for(int k=1;k“+rs.getString(k)+“);queryResult.append(“);queryResult.append(“);con.close();catch(SQLException e) queryResult.append(“请输入正确

9、的用户名和密码“);return queryResult; inquire.jsp输入数据库名: 输入表的名字: 输入用户名：输入密码： 在表查询到记录:例子 4RandomQueryBean.javapackage database.operation;import java.sql.*;import java.util.*;public class RandomQueryBean String databaseName=“; /数据库名称String tableName=“; /表的名字int count; /记录总数int randomNumber; /随机输出的记录数StringBuf

10、fer randomQueryResult; /查询结果Connection con;Statement sql; ResultSet rs; public RandomQueryBean() randomQueryResult=new StringBuffer();try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);catch(Exception e) public void setDatabaseName(String s) databaseName=s.trim();randomQueryResult=new

11、 StringBuffer();public String getDatabaseName() return databaseName;public void setTableName(String s) tableName=s.trim();randomQueryResult=new StringBuffer(); public String getTableName() return tableName;public void setRandomNumber(int n) randomNumber=n;randomQueryResult=new StringBuffer(); public

12、 int getRandomNumber() return randomNumber;public int getCount() try String uri=“jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=“+databaseName;con=DriverManager.getConnection(uri,“sa“,“sa“);sql= con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);rs=sql.executeQuery(“SELECT

13、* FROM “+tableName);rs.last();count=rs.getRow();con.close();catch(SQLException exp) count=-1;return count; public StringBuffer getRandomQueryResult() randomQueryResult=new StringBuffer();try randomQueryResult.append(“);String uri= “jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=“+databaseName;con=Drive

14、rManager.getConnection(uri,“sa“,“sa“);DatabaseMetaData metadata=con.getMetaData();ResultSet rs1=metadata.getColumns(null,null,tableName,null);int 字段个数=0;randomQueryResult.append(“);while(rs1.next() 字段个数+;String clumnName=rs1.getString(4);randomQueryResult.append(“+clumnName+“);randomQueryResult.appe

15、nd(“);sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);rs=sql.executeQuery(“SELECT * FROM “+tableName);rs.last();count=rs.getRow(); Vector vector=new Vector();for(int i=1;i0) int i=(int)(Math.random()*vector.size();int index=(vector.elementAt(i).intValue();/vector中

16、随机抽取一个元素rs.absolute(index); /游标移到这一行randomQueryResult.append(“);for(int k=1;k“+rs.getString(k)+“);randomQueryResult.append(“);抽取数目-; vector.removeElementAt(i); /将抽取过的元素从 vector中删除randomQueryResult.append(“);con.close();catch(SQLException e) randomNumber=0;return randomQueryResult; random.jsp随机查询数据库表

17、中的记录:输入数据库的名字：输入表的名字： 输入要随机查询的记录数:在表中共有条记录.随机输出 条记录：例子 5ConditionQuery.javapackage database.operation;import java.sql.*;public class ConditionQuery String number ; /雇员号float salaryMax,salaryMin; /薪水范围StringBuffer queryResultByNumber,queryResultBySalary;public ConditionQuery() queryResultByNumber=new

18、 StringBuffer();queryResultBySalary=new StringBuffer();try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);catch(Exception e) public void setNumber(String s) number=s.trim();queryResultByNumber=new StringBuffer();queryResultBySalary=new StringBuffer();public String getNumber() return n

19、umber;public void setSalaryMax(float n) salaryMax=n;queryResultByNumber=new StringBuffer();queryResultBySalary=new StringBuffer();public float getSalaryMax() return salaryMax;public void setSalaryMin(float n) salaryMin=n;queryResultByNumber=new StringBuffer();queryResultBySalary=new StringBuffer();p

20、ublic float getSalaryMin() return salaryMin;public StringBuffer getQueryResultByNumber() String condition=“SELECT * FROM employee Where number = “+number+“;queryResultByNumber=f(condition);return queryResultByNumber; public StringBuffer getQueryResultBySalary() String condition=“SELECT * FROM employ

21、ee Where salary = “+salaryMin;queryResultBySalary=f(condition);return queryResultBySalary; private StringBuffer f(String condition) StringBuffer str=new StringBuffer();Connection con;Statement sql; ResultSet rs;try String uri=“jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=factory“;con=DriverManager.ge

22、tConnection(uri,“sa“,“sa“);sql=con.createStatement();rs=sql.executeQuery(condition);str.append(“);str.append(“+“number“);str.append(“+“name“);str.append(“+“birthday“);str.append(“+“salary“);while(rs.next() str.append(“);str.append(“+rs.getString(1)+“);str.append(“+rs.getString(2)+“);str.append(“+rs.

23、getDate(3)+“);str.append(“+rs.getFloat(4)+“);str.append(“);str.append(“);con.close();catch(SQLException e) System.out.println(e);return str; condition.jsp雇员查询(根据雇员号查询)输入雇员号:雇员查询(根据薪水查询)薪水在至之间根据雇员号查询到的记录:薪水在至之间的记录:例子 6OrderQuery.javapackage database.operation;import java.sql.*;public class OrderQuery

24、 String orderType; /排序方式StringBuffer orderResult; /排序结果public OrderQuery() orderResult=new StringBuffer();try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);catch(Exception e) public void setOrderType(String s) orderType=s.trim();try byte bb=orderType.getBytes(“ISO-8859-1“);orderType=

25、new String(bb,“gb2312“); catch(Exception e)public String getOrderType() return orderType;public StringBuffer getOrderResult() String orderCondition=“SELECT * FROM employee ORDER BY “+orderType;Connection con;Statement sql; ResultSet rs;try String uri=“jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=fact

26、ory“;con=DriverManager.getConnection(uri,“sa“,“sa“);sql=con.createStatement();rs=sql.executeQuery(orderCondition);orderResult.append(“);orderResult.append(“+“number“);orderResult.append(“+“name“);orderResult.append(“+“birthday“);orderResult.append(“+“salary“);while(rs.next() orderResult.append(“);or

27、derResult.append(“+rs.getString(1)+“);orderResult.append(“+rs.getString(2)+“);orderResult.append(“+rs.getDate(3)+“);orderResult.append(“+rs.getFloat(4)+“);orderResult.append(“);orderResult.append(“);con.close();catch(SQLException e) System.out.println(e);return orderResult; sort.jsp排序记录：按姓氏排序 按薪水排序

28、根据排序方式:,排序的记录例子 7KeyWordQuery.javapackage database.operation;import java.sql.*;public class KeyWordQuery String ziduan=“, /字段 keyWord=“; /关键字StringBuffer queryResult;public KeyWordQuery() queryResult=new StringBuffer();try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);catch(Exception

29、 e) public void setKeyWord(String s) keyWord=s.trim();try byte bb=keyWord.getBytes(“ISO-8859-1“);keyWord=new String(bb,“gb2312“); catch(Exception e)public String getKeyWord() return keyWord;public void setZiduan(String s) ziduan=s.trim();try byte bb=ziduan.getBytes(“ISO-8859-1“);ziduan=new String(bb

30、,“gb2312“); catch(Exception e)public String getZiduan() return ziduan;public StringBuffer getQueryResult() String condition=“SELECT * FROM employee Where “+ziduan+“ Like %“+keyWord+“%“;StringBuffer str=f(condition); return str;public StringBuffer f(String condition) StringBuffer str=new StringBuffer

31、();Connection con;Statement sql; ResultSet rs;try String uri=“jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=factory“;con=DriverManager.getConnection(uri,“sa“,“sa“);sql=con.createStatement();rs=sql.executeQuery(condition);str.append(“);str.append(“+“雇员号“);str.append(“+“姓名“);str.append(“+“出生日期“);str.app

32、end(“+“薪水“);while(rs.next() str.append(“);str.append(“+rs.getString(1)+“);str.append(“+rs.getString(2)+“);str.append(“+rs.getDate(3)+“);str.append(“+rs.getString(4)+“);str.append(“);str.append(“);con.close();catch(SQLException e) System.out.println(e);return str; keyWord.jsp选择:姓名 雇员号 含有: 含有关键字的记录：例子

33、 8AddBean.javapackage database.operation;import java.sql.*;public class AddBean String number=“, /雇员号 name=“, /姓名birthday=“; /出生日期float salary; /薪水String addMessage=“; public AddBean() try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);catch(Exception e) public void setNumber(String s

34、) number=s.trim();public void setName(String s) name=s.trim();try byte bb=name.getBytes(“ISO-8859-1“);name=new String(bb,“gb2312“); catch(Exception e)public void setBirthday(String s) birthday=s.trim();public void setSalary(float n) salary=n;public String getAddMessage() String insertCondition=“INSE

35、RT employee VALUES( “+number+“, “+name+“,“+birthday+“,“+salary+“)“;String str=“;Connection con;Statement sql; try String uri=“jdbc:sqlserver:/127.0.0.1:1433;DatabaseName=factory“;con=DriverManager.getConnection(uri,“sa“,“sa“);sql=con.createStatement();if(number.length()0) int m=sql.executeUpdate(ins

36、ertCondition);if(m!=0) str=“对表中添加“+m+“条记录成功“;else str=“添加记录失败“;else str=“必须要有雇员号“;con.close();catch(SQLException e) str=“输入的雇员不允许重复“+e;return str; add.jsp雇员号是主键，不可重复，请输入新记录的相应字段的值：输入雇员号：输姓名：输入出生日期(*-*-*)：输入薪水：你添加记录操作的结果：数据库当前的数据记录是：例子 9ShowRecordByPage.javapackage database.operation;import java.sql.

37、*;import com.sun.rowset.*;public class ShowRecordByPage int pageSize=10; /每页显示的记录数int pageAllCount=0; /分页后的总页数int showPage=1 ; /当前显示页 StringBuffer presentPageResult; /显示当前页内容CachedRowSetImpl rowSet; /用于存储 ResultSet对象String databaseName=“; /数据库名称String tableName=“; /表的名字String user=“ ; /用户String pass

38、word=“ ; /密码 String 字段=new String100 ;int 字段个数=0;public ShowRecordByPage() presentPageResult=new StringBuffer();try Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“).newInstance();catch(Exception e) public void setPageSize(int size) pageSize=size;字段个数=0;String uri=“jdbc:sqlserver:/127.0.

39、0.1:1433;DatabaseName=“+databaseName;try Connection con=DriverManager.getConnection(uri,user,password);DatabaseMetaData metadata=con.getMetaData();ResultSet rs1=metadata.getColumns(null,null,tableName,null);int k=0;while(rs1.next() 字段个数+;字段k=rs1.getString(4); /获取字段的名字k+;Statement sql=con.createState

40、ment(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);ResultSet rs=sql.executeQuery(“SELECT * FROM “+tableName);rowSet=new CachedRowSetImpl(); /创建行集对象rowSet.populate(rs);con.close(); /关闭连接 rowSet.last();int m=rowSet.getRow(); /总行数int n=pageSize;pageAllCount=(m%n)=0)?(m/n):(m/n+1);catch(Ex

41、ception exp)public int getPageSize() return pageSize; public int getPageAllCount() return pageAllCount; public void setShowPage(int n) showPage=n;public int getShowPage() return showPage;public StringBuffer getPresentPageResult() if(showPagepageAllCount)showPage=1;if(showPage“);str.append(“);for(int

42、 i=0;i“+字段i+“); str.append(“);try rowSet.absolute(page-1)*pageSize+1);for(int i=1;i“);for(int k=1;k“+rowSet.getString(k)+“);str.append(“);rowSet.next();catch(SQLException exp)str.append(“); return str;public void setDatabaseName(String s) databaseName=s.trim();public String getDatabaseName() return

43、databaseName;public void setTableName(String s) tableName=s.trim();public String getTableName() return tableName;public void setPassword(String s) password=s.trim();public void setUser(String s) user=s.trim();public String getUser() return user; showByPage.jsp数据库中表的记录将被分页显示。共有 页.每页最多显示条记录。当前显示第 页,单击

44、“前一页”或“下一页”按纽查看记录“ “ 输入页码：例子 10linkOracle.jsp“);out.print(“);out.print(“+“EMPNO“);out.print(“+“Ename“);out.print(“);while(rs.next() out.print(“);int n=rs.getInt(1); out.print(“+n+“);String e=rs.getString(2); out.print(“+e+“);out.print(“) ; out.print(“);con.close();catch(SQLException e1) out.print(“+

45、e1);%例子 11linkMySql.jsp“);while(rs.next() out.print(“);out.print(“+rs.getString(1)+“); out.print(“+rs.getString(2)+“);out.print(“+rs.getDate(3)+“); out.print(“+rs.getFloat(4)+“);out.print(“) ; out.print(“);con.close();catch(SQLException e1) out.print(e1);%例子 12MySqlBean.javapackage database.operatio

46、n;import java.sql.*;public class MySqlBean String databaseName=“; /数据库名String tableName=“; /表名String user=“ ; /用户String secret=“ ; /密码 StringBuffer queryResult; /查询结果public MySqlBean() queryResult=new StringBuffer();try Class.forName(“com.mysql.jdbc.Driver“);catch(Exception e)public void setDatabase

47、Name(String s) databaseName=s.trim();queryResult=new StringBuffer(); public String getDatabaseName() return databaseName;public void setTableName(String s) tableName=s.trim();queryResult=new StringBuffer();public String getTableName() return tableName;public void setSecret(String s) secret=s.trim();queryResult=new StringBuffer();public String getSecret() return secret;public void setUser(String s) user=s.trim();