dbquery.java

jsp开发中的 JavaBean 查询数据库 对数据库查询 过滤非法字符 防止注入工具

package student.database;
import java.sql.*;

public class DBQuery
{

    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;

    public void connDB() throws Exception
    {
        String url = "jdbc:mysql://localhost:3309/database";
        //?user=root&password=123&useUnicode=true&characterEncoding=gbk
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        conn = DriverManager.getConnection(url,"root","123");
    }

    public void closeDB() throws SQLException
    {
        if (rs!=null)
        {
            rs.close();
            rs = null;
        }
        if (stmt!=null)
        {
            stmt.close();
            stmt = null;
        }
        if (conn!=null)
        {
            conn.close();
            conn = null;
        }
    }

    public boolean findData(String tablename,String[] condition,String order) throws SQLException
    {
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        StringBuffer sql = new StringBuffer("select * from ");
        sql.append(tablename);
        sql.append(" where");
        for (int i = 0;i < condition.length-1;i += 2)
        {
            sql.append(" " + condition[i]);
            sql.append("='" + condition[i+1] + "'");
            // 如果不是最后一对条件，则在后加上‘and’
            if (i < condition.length-2)
                sql.append(" and");
        }
        sql.append(" order by " + order);
        //System.out.print(sql);
        rs = stmt.executeQuery(sql.toString());
        if (rs.next())
        {
            rs.beforeFirst();
            return true;
        }
        else
            return false;
    }

    public boolean findData(String tablename,String order) throws SQLException
    {
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        StringBuffer sql = new StringBuffer("select * from ");
        sql.append(tablename);
        sql.append(" order by " + order);
        //System.out.print(sql);
        rs = stmt.executeQuery(sql.toString());
        if (rs.next())
        {
            rs.beforeFirst();
            return true;
        }
        else
            return false;
    }

    public boolean findData(String tablename, String[] condition)
			throws SQLException {
		stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		StringBuffer sql = new StringBuffer("select * from ");
		sql.append(tablename);
		sql.append(" where");
		for (int i = 0; i < condition.length - 1; i += 2) {
			sql.append(" " + condition[i]);
			sql.append("='" + condition[i + 1] + "'");
			// 如果不是最后一对条件，则在后加上‘and’
			if (i < condition.length - 2)
				sql.append(" and");
		}
		System.out.print(sql);
		rs = stmt.executeQuery(sql.toString());
		if (rs.next()) {
			rs.beforeFirst();
			return true;
		} else
			return false;
	}

    public boolean insertData(String tablename,String[] data) throws SQLException
    {
        stmt = conn.createStatement();
        StringBuffer sql = new StringBuffer("insert into ");
        sql.append(tablename);
        sql.append(" (");
        for (int i=0;i < data.length-1;i += 2)
        {
            sql.append(data[i]);
            //如果不是最后一个列名，则在后加上','
            if (i < data.length-2)
                sql.append(",");
            else
                sql.append(")");
        }
        sql.append(" values(");
        for (int i=1;i < data.length;i += 2)
        {
            sql.append("'" + data[i] + "'");
            //如果不是最后一个值，则在后加上','
            if (i < data.length-1)
                sql.append(",");
            else
                sql.append(")");
        }
        //System.out.print(sql);
        stmt.executeUpdate(sql.toString());
        return true;
    }

    public boolean modifyData(String tablename, String[] data,String[] condition) throws SQLException
    {
        if (!findData(tablename,condition))
            return false;
        stmt = conn.createStatement();
        StringBuffer sql = new StringBuffer("update ");
        sql.append(tablename);
        sql.append(" set");
        //构造要修改的值语句
        for (int i = 0;i < data.length-1;i += 2)
        {
            sql.append(" " + data[i]);
            sql.append("='" + data[i+1] + "'");
            // 如果不是最后一对条件，则在后加上‘,’
            if (i < data.length-2)
                sql.append(",");
        }
        sql.append(" where");
        //构造被修改的列语句
        for (int i = 0;i < condition.length-1;i += 2)
        {
            sql.append(" " + condition[i]);
            sql.append("='" + condition[i+1] + "'");
            // 如果不是最后一对条件，则在后加上‘and’
            if (i < condition.length-2)
                sql.append(" and");
        }
        System.out.println(sql);
        stmt.executeUpdate(sql.toString());
        return true;
    }

    public boolean deleteData(String tablename,String[] condition) throws SQLException
    {
        if (!findData(tablename,condition))
            return false;
        stmt = conn.createStatement();
        StringBuffer sql = new StringBuffer("delete from ");
        sql.append(tablename);
        sql.append(" where");
        for (int i = 0;i < condition.length-1;i += 2)
        {
            sql.append(" " + condition[i]);
            sql.append("='" + condition[i+1] + "'");
            // 如果不是最后一对条件，则在后加上‘and’
            if (i < condition.length-2)
                sql.append(" and");
        }
        System.out.println(sql);
        stmt.executeUpdate(sql.toString());
        return true;
    }

    public ResultSet getRs()
    {
        return rs;
    }

    public ResultSet executeSelectSql(String sql) throws SQLException {
    	if(sql==null || sql.equals("") || sql.length() < 7) return null;
    	sql = sql.trim();
    	if(sql.substring(0, 6).equalsIgnoreCase("select")){
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
            return rs;
    	}else{
    		return null;
    	}
    }
    
    public boolean executeUpdateSql(String sql) throws SQLException{
    	if(sql==null || sql.equals("") || sql.length() < 7) return false;
        stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        return true;
    }
}