JAVA（JDBC）通用查询

      第一次写编程类博客，不足之处望指正。

      最近学mysql数据库时，同时看了看java和python代码。一看是看的java代码不能通用，对比了java和python代码，想到虽然不能像python一样简单，但能不能做成通用的呢？由于学习java时间也不长，就在网上看了看别人写的代码，先看到的是csdn上的《JDBC（六） 编写通用的更新和查询方法》。但是这个方法只能返回一条，就想能不能一次返回多条或整表，所以又看了看别人的。发现大多类似（或一样），有的用了格式化，单独建立了个格式化类，增加大量迭代，类型还受限。所以自己用个半天多的时间，在前人的基础上搞了一个。个别类笔者也不熟，觉得东西还凑合，拿出了分享一下，欢迎指出问题。关键处理了一下hashmap的key不能重复的问题，别的也没什么，修改前的附在最后吧。

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import com.sql.entity.Student;


public class SqlSelect1 {
    private static Logger logger=LogManager.getLogger(SqlSelect1.class.getName());
    public static <T> List<T>  query(Class<T> clazz, String sql, Object... args) {        
        T entity = null;
        //笔者虚拟机默认装的mariadb,建立连接与mysql差不多，连接的封装就不发上来了。
        MariaDBConn maria=null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<T>();
        int colLen=0;
        try {
            maria=new MariaDBConn();          
            pstmt = maria.getConn().prepareStatement(sql);
            int argsLen=args.length;
            for (int i = 0; i <argsLen; i++) {
                pstmt.setObject(i + 1, args[i]);
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            //保证集合有序
            Map<String, Object>  values = new LinkedHashMap<String, Object> ();
            while (resultSet.next()) {     
                //使用随机数使key不重复
                double plus=Math.random();
                colLen=resultSetMetaData.getColumnCount();               
                for (int i = 0; i <colLen ; i++) {
                    String columnLable = resultSetMetaData
                            .getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(i + 1);                    
                    values.put(columnLable+plus, columnValue);                    
                }
            }
            if (values.size() > 0) {               
                Iterator<Entry<String, Object>> it = values.entrySet( ).iterator();
                while(it.hasNext()){                   
                    int i=0;
                    entity = clazz.newInstance();
                    while(i<colLen){                         
                        Entry<String, Object> entry=it.next();
                        //剥离随机数，恢复正常key
                        String fieldName =  entry.getKey().toString().split("\\d")[0];
                        Object value = null;
                        if (entry.getValue() instanceof Long) {
                            value = Integer.parseInt(entry.getValue().toString());
                        } else {
                            value = entry.getValue();
                        }
                        Field field = clazz.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        field.set(entity, value);
                        i++;
                    }                                       
                     list.add(entity);                                    
                }              
            }
        } catch (Exception e) {           
            logger.error(e);
        }finally{            
            try {                
                if(pstmt!=null){
                    pstmt.close();    
                }
            } catch (SQLException e) {                
                logger.error(e);
            }
            try {
                if(resultSet!=null){
                    resultSet.close();
                }
            } catch (SQLException e) {                
                logger.error(e);
            }
            maria.closeConn();
        }
        return list;
    }
    //测试：
    public static void main(String[] args) {
        
        List<Student> list=SqlSelect1.query(Student.class, "select id,name,birthday,gender from students");
            int size=list.size();
        for (int i = 0; i < size; i++) {
            System.out.println(list.get(i));            
        }        
    }
}

测试类：

import java.sql.Timestamp;

public class Student {

    private int id;
    private String name;
    private Timestamp birthday;
    private boolean gender;
    public Student() {
        super();
    }
    public Student(String name, Timestamp birthday, boolean gender,
            boolean isDelete) {
        super();
        this.name = name;
        this.birthday = birthday;
        this.gender = gender;
        this.isDelete = isDelete;
    }
    public Student(int id, String name, Timestamp birthday, boolean gender,
            boolean isDelete) {
        super();
        this.id = id;
        this.name = name;
        this.birthday = birthday;
        this.gender = gender;
        this.isDelete = isDelete;
    }
    public int getId() {
        return id;
    }
    public String getName() {
        return name;
    }
    public Timestamp getBirthday() {
        return birthday;
    }
    public boolean isGender() {
        return gender;
    }
    public boolean isDelete() {
        return isDelete;
    }
    public void setId(int id) {
        this.id = id;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setBirthday(Timestamp birthday) {
        this.birthday = birthday;
    }
    public void setGender(boolean gender) {
        
        this.gender = gender;
    }
    public void setDelete(boolean isDelete) {
        this.isDelete = isDelete;
    }
    boolean isDelete;
    public String toString() {
        if(this.gender==false){
            return "[Id=" + id + ", name="
                    + name + ", birthday=" + birthday + ", gender=女" + "]";
        }else{
            return "[Id=" + id + ", name="
                    + name + ", birthday=" + birthday + ", gender=男" + "]";
        }
        
    }

}

查询结果：

[Id=1, name=alice, birthday=1972-01-02 00:00:00.0, gender=女]
[Id=2, name=jake, birthday=1988-04-01 00:00:00.0, gender=男]
[Id=3, name=zero, birthday=1987-02-01 00:00:00.0, gender=男]
[Id=4, name=luffy, birthday=1993-01-09 00:00:00.0, gender=男]
[Id=5, name=mike, birthday=1950-01-03 00:00:00.0, gender=男]
[Id=6, name=James, birthday=1989-01-09 00:00:00.0, gender=男]
[Id=7, name=Ace, birthday=1991-02-10 00:00:00.0, gender=男]

 

修改前代码：

public <T> T query(Class<T> clazz, String sql, Object... args) {

        T entity = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 获取resultSet
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            // 获取resultSetMetaData
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            // 创建一个MAP 键 :SQL查询得到的列的别名，别名和javaBean的属性名对应 值:对应列的值
            Map<String, Object> values = new HashMap<String, Object>();

            // 处理结果集，利用resultSetMetaData填充Map
            while (resultSet.next()) {
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    String columnLable = resultSetMetaData
                            .getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(i + 1);

                    values.put(columnLable, columnValue);
                }
            }

            // 如果map不为空，利用反射创建对象
            if (values.size() > 0) {
                // 反射创建对象
                entity = clazz.newInstance();
                // 遍历Map ,利用反射为属性赋值
                for (Map.Entry<String, Object> entry : values.entrySet()) {
                    String fieldName = entry.getKey();
                    Object value = null;
                    // 因为java中和mysql中数据类型对应不一致
                    // mysql中int对应JAVA中的long，所以当查询出int型数据的时候需要转换
                    if (entry.getValue() instanceof Long) {
                        value = Integer.parseInt(entry.getValue().toString());
                    } else {
                        value = entry.getValue();
                    }
                    // ReflectionUtils.setFieldValue(entity, fieldName, value);
                    Field field = clazz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    field.set(entity, value);
                }

            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return entity;
    }

 

转载于:https://www.cnblogs.com/HHIngo/p/7488365.html