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>