java文件
QueryBean.java
package test;
import java.sql.*;
public class QueryBean
{
public static String query_statement; /*定义sql语句*/
public static String param[]; /*查询条件*/
public static ResultSet result=null; /*查询结果*/
public static Connection conn;
//设置构造函数
public QueryBean(String query_statement)throws Exception
{
this.query_statement=query_statement;
//query.setConnection("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://10.0.0.31:1433;DatabaseName=testDb","sa","sa"); }
///数据库连接
public QueryBean()throws Exception{
this.setConnection("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://10.0.0.31:1433;DatabaseName=testDB","sa","sa");
System.out.println("-----------------------");
}
//设置查询参数
public void setParam(String[] param)
{
this.param=param;
}
public static void main(String arg[])throws Exception
{
QueryBean querybean=new QueryBean();
System.out.println("这是一个测试");
} //设置SQL查询语句
public void setQuerystatement(String query_statement)
{
this.query_statement=query_statement;
}
//设置连接参数
public void setConnection(String driverName,String jdbcURL,String username,String passwd) throws Exception
{
Connection conn1;
Class.forName(driverName);
conn1=DriverManager.getConnection(jdbcURL,username,passwd);
conn1.setAutoCommit(false);
this.conn = conn1;
}
/*获取查询结果*/
public static ResultSet getResult()
{
try
{
PreparedStatement select_stm=conn.prepareStatement(query_statement,java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
if (param!=null)
for(int i=0;i<param.length;i++)
select_stm.setString(i+1,param[i]);
result=select_stm.executeQuery();
}catch(Exception e){System.out.println(e);}
return result;
}
/*对数据库进行增加记录操作*/
public static void insertRecord() throws SQLException,java.io.UnsupportedEncodingException
{
try
{
PreparedStatement insert_stm=conn.prepareStatement(query_statement);
if (param!=null)
for(int i=0;i<param.length;i++)
insert_stm.setString(i+1,param[i]);
insert_stm.executeUpdate();
insert_stm.close();
conn.commit();
}
catch(Exception e)
{
System.out.println(e);
conn.rollback();
}
}
/*对数据记录进行更新操作*/
public static void updateRecord() throws SQLException,java.io.UnsupportedEncodingException
{
try
{
PreparedStatement update_stm=conn.prepareStatement(query_statement);
if (param!=null)
for (int i=0;i<param.length;i++)
update_stm.setString(i+1,param[i]);
update_stm.executeUpdate();
update_stm.close();
conn.commit();
}
catch(Exception e)
{
System.out.println(e);
conn.rollback();
}
}
/*删除数据记录*/
public static void deleteRecord() throws SQLException,java.io.UnsupportedEncodingException
{
try
{
PreparedStatement delete_stm=conn.prepareStatement(query_statement);
if (param!=null)
for (int i=0;i<param.length;i++)
delete_stm.setString(i+1,param[i]);
delete_stm.executeUpdate();
delete_stm.close();
conn.commit();
}
catch(Exception e)
{
System.out.println(e);
conn.rollback();
}
}
}
jsp 文件 ********************************
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>
<jsp:useBean id="dbbean" scope="session" class="test.QueryBean" />
<HTML>
<head>
</head>
<BODY>
<% //查询数据库
public ResultSet rs;
public String course="dfg";
public String school="dddddd";
public String studydate="sssssssss";
public String rule="gggggggggg";
public String insertstringSqlString="insert into FIXDATA(SITE_ID,SAMPLING_DATETIME,ACTUAL_VALUE,ALTERANT_VALUE)values("+course+","+school+","+studydate+","+rule+")";
System.out.println("-----------------------");
//String stringSqlString="insert into FIXDATA(SAMPLING_DATETIME,SITE_ID,,CTUAL_VALUE,ALTERANT_VALUE) values( "2006.08.16","zz","aa","7.11","1.11","1.11")";
String stringSqlString="SELECT * FROM FIXDATA";
try
{
//设置连接
//query.setConnection("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://10.0.0.31:1433;DatabaseName=testDb","sa","sa");
//设置SQL语句
//QueryBean insertstr =new QueryBean(insertstringSqlString);
dbbean.QueryBean();
dbbean.QueryBean(insertstringSqlString) ;
dbbean.QueryBean(stringSqlString) ;
//.setQuerystatement(stringSqlString);
//QueryBean
//insertstr.insertRecord() ;
//query.setQuerystatement(insertstringSqlString);
//insertstr.setQuerystatement(stringSqlString);
//得到查询结果
//query.insertRecord(insertSqlString);
rs=dbbean.getResult();
}
catch(SQLException e1)
{
out.print("SQL异常！");
}
%>
<table width="75%" height="117" border="4" align="center" cellpadding="3" cellspacing="2" borderColor=#ffffff borderColorLight="#CCD7F7" borderColorDark="#EBEFFC" class="itemList" id="tabList">
<tr align="center" valign="middle">
<td>时间</td>
<td>站点</td>
<td>污染物</td>
<td>超标值</td>
<td>标准值</td>
<td>倍数</td>
<td>备注</td>
</tr>
<%
while(rs.next())
{
%>
<tr class='tr1' align="center" valign="middle">
<td><%=rs.getString("SITE_ID") %></td>
<td><%=rs.getString("SAMPLING_DATETIME") %></td>
<td><%=rs.getString("ACTUAL_VALUE")%></td>
<td><%=rs.getString("ALTERANT_VALUE")%></td>
<td><%=rs.getString("FLAG")%></td>
<td><%=rs.getString("AREA_ID")%>倍</td>
</tr>
<%
}
%>
<tr align="center" valign="middle">
<td>合计</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</BODY>
</HTML>
QueryBean.java
package test;
import java.sql.*;
public class QueryBean
{
public static String query_statement; /*定义sql语句*/
public static String param[]; /*查询条件*/
public static ResultSet result=null; /*查询结果*/
public static Connection conn;
//设置构造函数
public QueryBean(String query_statement)throws Exception
{
this.query_statement=query_statement;
//query.setConnection("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://10.0.0.31:1433;DatabaseName=testDb","sa","sa"); }
///数据库连接
public QueryBean()throws Exception{
this.setConnection("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://10.0.0.31:1433;DatabaseName=testDB","sa","sa");
System.out.println("-----------------------");
}
//设置查询参数
public void setParam(String[] param)
{
this.param=param;
}
public static void main(String arg[])throws Exception
{
QueryBean querybean=new QueryBean();
System.out.println("这是一个测试");
} //设置SQL查询语句
public void setQuerystatement(String query_statement)
{
this.query_statement=query_statement;
}
//设置连接参数
public void setConnection(String driverName,String jdbcURL,String username,String passwd) throws Exception
{
Connection conn1;
Class.forName(driverName);
conn1=DriverManager.getConnection(jdbcURL,username,passwd);
conn1.setAutoCommit(false);
this.conn = conn1;
}
/*获取查询结果*/
public static ResultSet getResult()
{
try
{
PreparedStatement select_stm=conn.prepareStatement(query_statement,java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
if (param!=null)
for(int i=0;i<param.length;i++)
select_stm.setString(i+1,param[i]);
result=select_stm.executeQuery();
}catch(Exception e){System.out.println(e);}
return result;
}
/*对数据库进行增加记录操作*/
public static void insertRecord() throws SQLException,java.io.UnsupportedEncodingException
{
try
{
PreparedStatement insert_stm=conn.prepareStatement(query_statement);
if (param!=null)
for(int i=0;i<param.length;i++)
insert_stm.setString(i+1,param[i]);
insert_stm.executeUpdate();
insert_stm.close();
conn.commit();
}
catch(Exception e)
{
System.out.println(e);
conn.rollback();
}
}
/*对数据记录进行更新操作*/
public static void updateRecord() throws SQLException,java.io.UnsupportedEncodingException
{
try
{
PreparedStatement update_stm=conn.prepareStatement(query_statement);
if (param!=null)
for (int i=0;i<param.length;i++)
update_stm.setString(i+1,param[i]);
update_stm.executeUpdate();
update_stm.close();
conn.commit();
}
catch(Exception e)
{
System.out.println(e);
conn.rollback();
}
}
/*删除数据记录*/
public static void deleteRecord() throws SQLException,java.io.UnsupportedEncodingException
{
try
{
PreparedStatement delete_stm=conn.prepareStatement(query_statement);
if (param!=null)
for (int i=0;i<param.length;i++)
delete_stm.setString(i+1,param[i]);
delete_stm.executeUpdate();
delete_stm.close();
conn.commit();
}
catch(Exception e)
{
System.out.println(e);
conn.rollback();
}
}
}
jsp 文件 ********************************
<%@ page contentType="text/html;charset=GB2312" %>
<%@ page import="java.sql.*" %>
<jsp:useBean id="dbbean" scope="session" class="test.QueryBean" />
<HTML>
<head>
</head>
<BODY>
<% //查询数据库
public ResultSet rs;
public String course="dfg";
public String school="dddddd";
public String studydate="sssssssss";
public String rule="gggggggggg";
public String insertstringSqlString="insert into FIXDATA(SITE_ID,SAMPLING_DATETIME,ACTUAL_VALUE,ALTERANT_VALUE)values("+course+","+school+","+studydate+","+rule+")";
System.out.println("-----------------------");
//String stringSqlString="insert into FIXDATA(SAMPLING_DATETIME,SITE_ID,,CTUAL_VALUE,ALTERANT_VALUE) values( "2006.08.16","zz","aa","7.11","1.11","1.11")";
String stringSqlString="SELECT * FROM FIXDATA";
try
{
//设置连接
//query.setConnection("com.microsoft.jdbc.sqlserver.SQLServerDriver","jdbc:microsoft:sqlserver://10.0.0.31:1433;DatabaseName=testDb","sa","sa");
//设置SQL语句
//QueryBean insertstr =new QueryBean(insertstringSqlString);
dbbean.QueryBean();
dbbean.QueryBean(insertstringSqlString) ;
dbbean.QueryBean(stringSqlString) ;
//.setQuerystatement(stringSqlString);
//QueryBean
//insertstr.insertRecord() ;
//query.setQuerystatement(insertstringSqlString);
//insertstr.setQuerystatement(stringSqlString);
//得到查询结果
//query.insertRecord(insertSqlString);
rs=dbbean.getResult();
}
catch(SQLException e1)
{
out.print("SQL异常！");
}
%>
<table width="75%" height="117" border="4" align="center" cellpadding="3" cellspacing="2" borderColor=#ffffff borderColorLight="#CCD7F7" borderColorDark="#EBEFFC" class="itemList" id="tabList">
<tr align="center" valign="middle">
<td>时间</td>
<td>站点</td>
<td>污染物</td>
<td>超标值</td>
<td>标准值</td>
<td>倍数</td>
<td>备注</td>
</tr>
<%
while(rs.next())
{
%>
<tr class='tr1' align="center" valign="middle">
<td><%=rs.getString("SITE_ID") %></td>
<td><%=rs.getString("SAMPLING_DATETIME") %></td>
<td><%=rs.getString("ACTUAL_VALUE")%></td>
<td><%=rs.getString("ALTERANT_VALUE")%></td>
<td><%=rs.getString("FLAG")%></td>
<td><%=rs.getString("AREA_ID")%>倍</td>
</tr>
<%
}
%>
<tr align="center" valign="middle">
<td>合计</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</BODY>
</HTML>
HTTP Status 500 - --------------------------------------------------------------------------------type Exception reportmessage description The server encountered an internal error () that prevented it from fulfilling this request.exception org.apache.jasper.JasperException: Unable to compile class for JSPAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable rs; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable course; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable school; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable studydate; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable rule; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable insertstringSqlString; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
The method QueryBean() is undefined for the type QueryBeanAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
The method QueryBean(String) is undefined for the type QueryBeanAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
The method QueryBean(String) is undefined for the type QueryBean
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:375)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause org.apache.jasper.JasperException: Unable to compile class for JSPAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable rs; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable course; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable school; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable studydate; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable rule; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
Illegal modifier for the variable insertstringSqlString; only final is permittedAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
The method QueryBean() is undefined for the type QueryBeanAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
The method QueryBean(String) is undefined for the type QueryBeanAn error occurred at line: 11 in the jsp file: /testbean1/queryJavaBeanJsp.jsp
Generated servlet error:
The method QueryBean(String) is undefined for the type QueryBean
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:328)
org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:414)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:297)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:276)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:264)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:563)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:303)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
next()
<% //查询数据库
public ResultSet rs;
public String course="dfg";
public String school="dddddd";
public String studydate="sssssssss";
public String rule="gggggggggg";
public String insertstringSqlString="insert into
你不可以在<%%>里面给变量名前面加public pvivte等 如果你要定义可以在<%!%>里面定义
“不是现在这样 增加之前的连接一次 查询的连接一次 “
应该怎样改正 才能实现效率高的
package deuxluow.dbconnection;import java.sql.*;import javax.naming.*;
import javax.sql.DataSource;
import java.util.regex.*;
public class DbConnection{
Context initCtx = null;
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
public DbConnection(){
} /************************************************
* openConnection
*
************************************************/
public synchronized void getConnection(String poolname) throws Exception
{
try
{
initCtx = new javax.naming.InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup(poolname);
conn = ds.getConnection();
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
catch(NamingException el)
{
System.out.println(el.getMessage());
}
}
/*************************************************
* get Connection
*************************************************/
public Connection getConnection() throws SQLException{
return conn;
}
/*************************************************
* executeQuery and executeUpdate
* query and update DB
*************************************************/
public ResultSet executeQuery(String query) throws SQLException{
stmt = conn.createStatement();
rset = stmt.executeQuery(query);
return rset;
} public void executeUpdate(String query) throws SQLException{
stmt = conn.createStatement();
stmt.executeUpdate(query);
if(stmt != null) stmt.close();
} public void close() throws SQLException{
if(rset != null) rset.close();
if(stmt != null) stmt.close();
}
public void closeConn() throws SQLException{
if(conn != null) conn.close();
}
public void closeCont() throws Exception{
if(initCtx != null)initCtx.close();
}
public static String sqlfilter(String SQLSrc){
String regEx="#+|-+"; //过虑字符#，-
Pattern p=Pattern.compile(regEx);
Matcher m=p.matcher(SQLSrc);
String result=m.replaceAll("");
return result;
}
protected void finalize() throws Throwable{
close();
}
}