SQL模板简单实用

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();
    }
   }