搜索SQL语句组成--备忘

[luozhangwen]

package com.lzw.dao.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBConnection
{
    private final static String DB_DRIVER = "com.mysql.jdbc.Driver";

    private final static String DB_URL = "jdbc:mysql://127.0.0.1:3306/test";

    private final static String DB_USER = "root";

    private final static String DB_PWD = "root";

    public static Connection getConnection()
    {
        try
        {
            Class.forName(DB_DRIVER).newInstance();
            return DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return null;
    }


    public static ResultSet executeLikeQuery(int id,String name)
    {
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        try
        {
            pstmt = conn.prepareStatement(StringUtils.getOrQueryString(id, name));

            int index = 1;
            List<Object> list  = DBConnection.getQueryParamArray(id,name);
            for (Object object : list)
            {
                pstmt.setObject(index++, "%" + object + "%");
            }
            return pstmt.executeQuery();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }
  
    
    public static ResultSet executeAndQuery(int id,String name)
    {
        Connection conn = DBConnection.getConnection();
        PreparedStatement pstmt = null;
        try
        {
            pstmt = conn.prepareStatement(StringUtils.getAndQueryString(id, name));

            int index = 1;
            List<Object> list  = DBConnection.getQueryParamArray(id,name);
            for (Object object : list)
            {
                pstmt.setObject(index++, object);
            }
            return pstmt.executeQuery();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }
    private static List<Object> getQueryParamArray(int id,String name)
    {
        List<Object> list = new ArrayList<Object>();
        
        if(id != -1)   //整形为-1代表没有.
        {
            list.add(id);
        }
        if(!StringUtils.isEmpty(name))
        {
            list.add(name);
        }
         
        return list;
    }

 
    public static void main(String[] args)
    {
        ResultSet rs = DBConnection.executeLikeQuery(1000001,"1000000");

        try
        {
            while (rs.next())
            {

                System.out.println(rs.getInt("id") + "   " + rs.getString("name"));
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}

[luozhangwen]

class StringUtils
{
        public static boolean isEmpty(String str)
        {
            return (str==null || str.trim().length()==0);
        }
        
        public static String getAndQueryString(int id, String name)
        {

            StringBuffer sb = new StringBuffer("SELECT * FROM test t where 1 = 1");

            if(id != -1)
            {
                sb.append(" AND t.id = ?");
            }
            
            if (!StringUtils.isEmpty(name))
            {
                sb.append(" AND t.name = ?");
            }
            
            String sql = sb.toString();
            return sql;
        }

        
        public static String getOrQueryString(int id, String name)
        {

            StringBuffer sb = new StringBuffer("SELECT * FROM test t where 1 != 1");

            if(id != -1) 
            {
                sb.append(" or t.id like ?");
            }
            
            if (!StringUtils.isEmpty(name))
            {
                sb.append(" or t.name like ?");
            }
            
            String sql = sb.toString();
            return sql;
        }

        
}