JDBC查询

BaseDB类：

package com.framework.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class BaseDB {

/*private static final String DRIVER =             "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=qzw";
private static final String USER = "sa";
private static final String PWD = "sa";*/

   public Connection GetConnection() throws IOException{
Connection connection=null;
/*Class.forName(DRIVER).newInstance();
connection= DriverManager.getConnection(URL,USER,PWD);*/

Properties properties=new Properties();
InputStream is=this.getClass().getResourceAsStream("/db.properties");
properties.load(is);
String driver=properties.getProperty("driver");
String url=properties.getProperty("url");
String user=properties.getProperty("user");
String pwd=properties.getProperty("password");
try {
Class.forName(driver);
connection=DriverManager.getConnection(url,user,pwd);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}
return connection;
   } 
   /**
    * 关闭关键字连接
    * @param rSet
    * @param pStatement
    * @param connection
    */
   public static void CloseConnection(Connection connection){
try {
if (connection!=null&&!connection.isClosed()) {
connection.close();
connection=null;

}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
  
   }  
}


SQLCommandBean类
package com.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

public class SQLCommandBean {
private Connection connection;
private String sqlValue;
private List values;


public void setConnection(Connection connection) {
this.connection = connection;
}

public void setSqlValue(String sqlValue) {
this.sqlValue = sqlValue;
}

public void setValues(List values) {
this.values = values;
}

public Result executeQuery() throws SQLException
{
PreparedStatement pStatement=null;
Statement stmt=null;
Result result=null;
ResultSet rSet=null;
try {
if(values != null && values.size()>0)
{
pStatement=connection.prepareStatement(sqlValue);
setValues(pStatement,values);
rSet=pStatement.executeQuery();
}else {
stmt=connection.createStatement();
rSet=stmt.executeQuery(sqlValue);
}
result = ResultSupport.toResult(rSet);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally
{
closeAll(rSet,stmt,pStatement);

}

return result;
}

public int executeUpdate() {
int noOfRows=0;
ResultSet rSet=null;
PreparedStatement pStatement=null;
Statement stmt=null;
try {
if(values !=null && values.size()>0)
{
pStatement=connection.prepareStatement(sqlValue);
setValues(pStatement, values);
noOfRows=pStatement.executeUpdate();
}else {
stmt=connection.createStatement();
noOfRows=stmt.executeUpdate(sqlValue);
}

} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally
{
closeAll(rSet, stmt, pStatement);
}
return noOfRows;
}

public void setValues(PreparedStatement pStatement,List values) throws SQLException
{
for (int i = 0; i < values.size(); i++) {
Object v=values.get(i);
pStatement.setObject(i+1, v);
}
}


public void closeAll(ResultSet rSet, Statement stmt,PreparedStatement pStatement) {
  try {
if (rSet!=null) {
rSet.close();
rSet=null;

}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}

try {
if(stmt!=null)
{
stmt.close();
stmt=null;
}
} catch (Exception e) {
// TODO: handle exception
}
try {
if (pStatement!=null) {
pStatement.close();
pStatement=null;

}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();

}  
   }
}

JSP  result.jsp 测试调用
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@include file="SQLCommand.jsp" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
   
    <title>Result</title>
   
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">   
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->

  </head>
 
  <body>
  <%
  String content = request.getParameter("content");
   Connection conn =null;
   Result rs =null;
   List values =null;
   String sqlValue =null;
   try{
        conn = getConnection();
        sqlValue ="select * from user";
        sqlValue +=" where username like ?";
   values = new ArrayList();
   values.add(content);
   this.setConnection(conn);
   this.setSqlValue(sqlValue);
   this.setValues(values);
   rs = this.executeQuery();
   }catch(Exception ex)
   {
      ex.printStackTrace();
    System.out.println("查询出错了。。。");
   }finally
   {
    closeConnection(conn);
   } 
  for(int i= 0; i<rs.getRowCount(); i++ )
  {
  Map map = rs.getRows()[i];
  System.out.println(map.get("username")+"  " + map.get("email"));
  }
  %>

  </body>
</html>