Using reflection to implement sql query of mysql database to return List<E> generic (persistent) objects

Keywords: SQL Java Hibernate Apache

Using reflection to realize sql query and get persistent objects

The Title can't think of simple words. If you want to find out the relevant information, you should first see if the method structure is in line with your appetite.
public static <E> List<E> getObjectBySql(String sql,E e)
{   
    List<E> list = new ArrayList<E>();
    //...
    //Method area
    //...
    return list; 
}

Topic

After reviewing the hql in hibernate, I found that it was novel to get persistent objects. I studied it for a while.
The principle of implementation is to use reflection mechanism to get all the methods and variable names of the Class object (similar to String.Class) passed in.
The implementation steps are as follows:

import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * Created by HaoWeiTao on 2017/5/15.
 */
public class Eazy01 {
    private static DataSource ds ;//Connection pool

    static {
        Properties properties = new Properties();//Get a properties object
        try {
            properties.load(new FileInputStream("src\\main\\dbcp.properties"));//Load properties file
            ds = BasicDataSourceFactory.createDataSource(properties);//Loading data through connection pool factory
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static <E> List<E> getObjectBySql(String sql,E e)
    {
        Connection conn = null;     //Connect
        Statement st = null;
        ResultSet rs = null;
        List<E> list = new ArrayList<E>();
        try {
            conn = ds.getConnection();  //Get a connection
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();//Get the result set metadata object, through which the column number, column name and other information can be obtained.
            int count = 0;
            while (rs.next())
            {
                Class<?> eClas = e.getClass();
                E eObj = (E) eClas.newInstance();
                int len = rsmd.getColumnCount();
                count++;
                for (int i=1;i<=len;i++)
                {
                    //Column names
                    String columnName = rsmd.getColumnName(i);
                    //Capitalize column names
                    String columnNameUpCase = columnName.substring(0,1).toUpperCase()+columnName.substring(1);
                    //Get the corresponding set method name for each column in the generic object
                    String columnSetMethod = "set"+columnNameUpCase;
                    Field filed = eClas.getDeclaredField(columnName);
                    Class<?> clazz = filed.getType();   //Get the type corresponding to the field name
                    //The field is of type int
                    if (clazz.isAssignableFrom(int.class)) {
                        //getMethod(), the first parameter is the name of the method, the next parameter is the type of the parameter in the method, and there are several types of parameters in the method.
                        Method method = eClas.getMethod(columnSetMethod, int.class);
                        //The invoke() method takes the first parameter as the object to execute the method, and the second parameter as the method is executed.
                        method.invoke(eObj,rs.getInt(i));
                    }
                    //Fields of string type
                    else if (clazz.isAssignableFrom(String.class)) {
                        Method method = eClas.getMethod(columnSetMethod,String.class);
                        method.invoke(eObj,rs.getString(i));
                    }
                    //The field is double type
                    else if (clazz.isAssignableFrom(double.class)) {
                        Method method = eClas.getMethod(columnSetMethod,double.class);
                        method.invoke(eObj,rs.getDouble(i));
                    } //Field is float type
                    else if (clazz.isAssignableFrom(float.class)) {
                        Method method = eClas.getMethod(columnSetMethod,float.class);
                        method.invoke(eObj,rs.getFloat(i));
                    }//Field is Integer type
                    else if (clazz.isAssignableFrom(Integer.class)) {
                        Method method = eClas.getMethod(columnSetMethod,Integer.class);
                        method.invoke(eObj,rs.getInt(i));
                    }//Field is Float type
                    else if (clazz.isAssignableFrom(Float.class)) {
                        Method method = eClas.getMethod(columnSetMethod,Float.class);
                        method.invoke(eObj,rs.getFloat(i));
                    }//The field is double type
                    else if (clazz.isAssignableFrom(Double.class)) {
                        Method method = eClas.getMethod(columnSetMethod,Double.class);
                        method.invoke(eObj,rs.getDouble(i));
                    }//Field is Date type
                    else if (clazz.isAssignableFrom(Date.class)) {
                        Method method = eClas.getMethod(columnSetMethod,Date.class);
                        method.invoke(eObj,rs.getDate(i));
                    }
                }
                list.add(eObj);
            }
        } catch (SQLException e0) {
            e0.printStackTrace();
        } catch (IllegalAccessException e1) {
            e1.printStackTrace();
        } catch (InstantiationException e1) {
            e1.printStackTrace();
        } catch (NoSuchFieldException e1) {
            e1.printStackTrace();
        } catch (NoSuchMethodException e1) {
            e1.printStackTrace();
        } catch (InvocationTargetException e1) {
            e1.printStackTrace();
        }
        return list;
    }

    public static void main(String[] args) {
        String sql = "select * from title"; //Query sql
        List<Title> list = getObjectBySql(sql,new Title());
        for (Title title:list) {
            System.out.println(title);
        }
    }
}
}

Title entity class

/**
 * Created by HaoWeiTao on 2017/5/15.
 */
public class Title {
    private int id;
    private int titleId;
    private String titleName;
    private double beilv;
    private int isEffective;

    public Title() {
    }

    public Title(int id, int titleId, String titleName, double beilv, int isEffective) {
        this.id = id;
        this.titleId = titleId;
        this.titleName = titleName;
        this.beilv = beilv;
        this.isEffective = isEffective;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }

    public int getTitleId() {
        return titleId;
    }

    public void setTitleId(int titleId) {
        this.titleId = titleId;
    }

    public String getTitleName() {
        return titleName;
    }

    public void setTitleName(String titleName) {
        this.titleName = titleName;
    }

    public double getBeilv() {
        return beilv;
    }

    public void setBeilv(double beilv) {
        this.beilv = beilv;
    }

    public int getIsEffective() {
        return isEffective;
    }

    public void setIsEffective(int isEffective) {
        this.isEffective = isEffective;
    }

    @Override
    public String toString() {
        return "Title{" +
                "id=" + id +
                ", titleId=" + titleId +
                ", titleName=" + titleName +
                ", beilv=" + beilv +
                ", isEffective=" + isEffective +
                '}';
    }
}

Here is the database source data

`

Here are the results

`

"Corresponding jar packages have mysql-connect and dbcp"

Posted by ckuipers on Wed, 13 Feb 2019 20:54:18 -0800