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