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 SQLCommand
{
private Connection conn;
private String sqlVaule;
private List values;
/**
*
* */
public void setConnection(Connection con)
{
this.conn=con;
}
/**
* 设定SQL语句
* **/
public void setSqlValue(String sqlValue)
{
this.sqlVaule=sqlValue;
}
/*
*
* 设定SQL参数
* **/
public void setValues(List values)
{
this.values=values;
}
/**
* 执行查询
* @throws SQLException
* @reutrn Result对象
* **/
public Result executeQuery() throws SQLException
{
Result result=null;
ResultSet rs=null;
PreparedStatement prop=null;
Statement stmt=null;
try
{
if(values !=null && values.size()>0)
{
prop=conn.prepareStatement(sqlVaule);
setValues(prop,values);
rs=prop.executeQuery();
}
else
{
stmt=conn.createStatement();
rs=stmt.executeQuery(sqlVaule);
}
result=ResultSupport.toResult(rs);
}
finally
{
if(rs!=null)
{
rs.close();
}
if(stmt!=null)
{
stmt.close();
}
if(prop !=null)
{
prop.close();
}
}
return result;
}
/**
* 执行Update语句
* @throws SQLException
* */
public int executeUpdate() throws SQLException
{
int noOfRows=0;
ResultSet rs=null;
PreparedStatement prop=null;
Statement stmt=null;
try
{
if(values!=null && values.size()>0)
{
prop=conn.prepareStatement(sqlVaule);
setValues(prop,values);
noOfRows=prop.executeUpdate();
}
else
{
stmt=conn.createStatement();
noOfRows=stmt.executeUpdate(sqlVaule);
}
}
finally
{
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(prop!=null)
prop.close();
}
return noOfRows;
}
/**
* 设定语句的参数
* @param PreparedStatement
* @throws SQLException
*
* */
public void setValues(PreparedStatement prop,List values) throws SQLException
{
for(int i=0;i<values.size();i++)
{
Object v=values.get(i);
prop.setObject(i+1, v);
}
}
}
连接类:
import java.sql.*;
public class ConnectionManager
{
private static String driverClassName="com.mysql.jdbc.Driver";
private static String url="jdbc:mysql://localhost:3306/test";
private static String username="root";
private static String pwd="123456";
public static Connection getConnection() throws SQLException
{
Connection con=null;
try
{
Class.forName(driverClassName);
con=DriverManager.getConnection(url,username,pwd);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
}
测试类:
public static void main(String args[])
{
try
{
SQLCommand command=new SQLCommand();
command.setConnection(ConnectionManager.getConnection());
command.setSqlValue("select * from apps");
Result rs=command.executeQuery();
if(rs ==null || rs.getRowCount() ==0)
{
System.out.println("no information!!");
}
else
{
int count=rs.getRowCount();
System.out.println(count);
for(int i=0;i<count;i++)
{
Map row=rs.getRows()[i];
System.out.println(row.get("id"));
System.out.println(row.get("name"));
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}