Spring JdbcTemplate Helper Class

In daily development, we often need to access the database, which inevitably need to deal with resultset. Spring JdbcTemplate wraps the native JDBC API, allowing programmers to focus more on business logic coding, such as querying database operations:

Public User Queryuserbyid (long id) {

        return Jdbctemplate.queryforobject ("select * from Tb_employee WHERE id=?", New Ob Ject[]{id}, new Rowmapper<user> () {

            @Override public
            User Maprow (ResultSet rs, int i) throws SQLException {
                User user = new user ();
                User.setid (Rs.getlong ("id"));
                User.setname (rs.getstring ("name"));
                User.setage (Rs.getint ("Age"));
                return user;}}
        );
    

We just need to write our own rowmapper to convert resultset to JavaBean.

But this is still not concise enough, especially when the properties of JavaBean is particularly long, the realization of rowmapper will make people very irritable. As a self-made wheel, it automatically converts the resultset to an array, Map, JavaBean, and other objects. Simplified Spring jdbctemplate 1. Iconverter interface

Package com.bytebeats.toolkit.orm;

Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.List;
Import Java.util.Map;

Public interface Iconverter {

    object[] ToArray (ResultSet rs) throws SQLException;

    Map<string, object> Tomap (ResultSet rs) throws SQLException;

    <T> T Tobean (ResultSet rs, class<t> type) throws SQLException;

    <T> list<t> Tobeanlist (ResultSet rs, class<t> type) throws SQLException;


}

The Iconverter interface defines the behavior of converting resultset to object[], Map, JavaBean, list<javabean>.

Next, querying the database using spring JdbcTemplate is a pleasant thing to do. 2. Querying the database

1. User Class

package Com.bytebeats.toolkit.samples.model;
Import Com.bytebeats.toolkit.orm.Column;

Import Java.util.Date;
    public class User {private long ID;
    private String name;
    private int age;
    private double amount;

    @Column ("Create_time") private Date createtime;
    Public long GetId () {return id;
    } public void SetId (long id) {this.id = ID;
    } public String GetName () {return name;
    } public void SetName (String name) {this.name = name;
    } public int Getage () {return age;
    public void Setage (int.) {this.age = age;
    } public double Getamount () {return amount;
    } public void Setamount (double amount) {this.amount = amount;
    Public Date Getcreatetime () {return createtime;
    } public void Setcreatetime (Date createtime) {this.createtime = Createtime; }
}

2, Iuserdao

Package Com.bytebeats.toolkit.samples.db.dao;

Import Com.bytebeats.toolkit.samples.model.User;
Import java.util.List;

Public interface Iuserdao {

    User Queryuserbyid (long id);

    List<user> queryusers ();

    int Insert (user user);

}

3, Userdaoimpl class

Package Com.bytebeats.toolkit.samples.db.dao.impl;
Import Com.bytebeats.toolkit.orm.IConverter;
Import Com.bytebeats.toolkit.samples.db.dao.IUserDao;
Import Com.bytebeats.toolkit.samples.model.User;
Import org.springframework.beans.factory.annotation.Autowired;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.jdbc.core.RowMapper;

Import Org.springframework.stereotype.Repository;
Import Javax.annotation.Resource;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.util.List; @Repository ("Userdao") public class Userdaoimpl implements Iuserdao {@Resource (name = "Pkjdbctemplate") private

    JdbcTemplate JdbcTemplate;

    @Autowired Private Iconverter Converter; Public User Queryuserbyid (long id) {return jdbctemplate.queryforobject ("select * from Tb_employee WHERE id=?", N EW Object[]{id}, New Rowmapper<user> () {@Override public User Maprow (ResultSet rs, int i)

 Throws SQLException {               Return Converter.tobean (RS, user.class);
    }
        }); List<user> queryusers () {return Jdbctemplate.query ("SELECT * from Tb_employee", New Rowmappe

                R<user> () {@Override public User Maprow (ResultSet rs, int i) throws SQLException {
            Return Converter.tobean (RS, user.class);
    }
        });
    } public int Insert (user user) {return 0;
 }
}

A line of code to fix resultset to JavaBean conversion, is not very easy. Implementation Principle principle:

Annotation + Reflection Technology
Get a list of the JavaBean properties through reflection, get the column list of the database table by ResultSetMetaData, and then find the property relative to the column name (same name & type) and assign a value. The property name of the JavaBean may be inconsistent with the column name, and its corresponding column name can be specified through the column annotation.

Not suspense, directly on the code, talk is cheap, show me the code. Basicrowconverter

Package com.bytebeats.toolkit.orm;
Import Com.bytebeats.toolkit.annotation.ThreadSafe;
Import Java.sql.ResultSet;
Import Java.sql.ResultSetMetaData;
Import java.sql.SQLException;
Import Java.util.HashMap;
Import java.util.List;

Import Java.util.Map; @ThreadSafe public class Basicrowconverter implements Iconverter {Private final beanconverter converter = new Beanco

    Nverter (); @Override public object[] ToArray (ResultSet rs) throws SQLException {ResultSetMetaData meta = rs.getmetadata (
        );
        int cols = Meta.getcolumncount ();

        Object[] result = new Object[cols];
        for (int i = 0; i < cols; i++) {Result[i] = Rs.getobject (i + 1);
    } return result; } @Override public map<string, object> Tomap (ResultSet rs) throws SQLException {map<string, OBJ
        ect> result = new hashmap<string, object> ();
        ResultSetMetaData RSMD = Rs.getmetadata ();

 int cols = Rsmd.getcolumncount ();       for (int i = 1; I <= cols; i++) {String columnName = Rsmd.getcolumnlabel (i);
            if (null = = ColumnName | | 0 = = columnname.length ()) {columnName = Rsmd.getcolumnname (i);
        } result.put (ColumnName, Rs.getobject (i));
    } return result; } @Override Public <T> T Tobean (ResultSet rs, class<t> type) throws SQLException {return th
    Is.converter.toBean (RS, type);

        } @Override Public <T> list<t> tobeanlist (ResultSet rs, class<t> type) throws SQLException {
    Return This.converter.toBeanList (RS, type);
 }
}

Beanconverter class

Package com.bytebeats.toolkit.orm;
Import Com.bytebeats.toolkit.model.ClassInfo;
Import Com.bytebeats.toolkit.annotation.ThreadSafe;
Import Com.google.common.cache.CacheBuilder;
Import Com.google.common.cache.CacheLoader;

Import Com.google.common.cache.LoadingCache;
Import Java.beans.BeanInfo;
Import java.beans.IntrospectionException;
Import Java.beans.Introspector;
Import Java.beans.PropertyDescriptor;
Import Java.lang.reflect.Field;
Import java.lang.reflect.InvocationTargetException;
Import Java.lang.reflect.Method;
Import Java.sql.ResultSet;
Import Java.sql.ResultSetMetaData;
Import java.sql.SQLException;
Import Java.sql.Timestamp;
Import java.util.*;

Import java.util.concurrent.ExecutionException;

    @ThreadSafe public class Beanconverter {protected static final int property_not_found =-1;

    private static final map<class<?>, object> primitivedefaults = new Hashmap<class<?>, object> (); Private final loadingcache<class<?>, Classinfo> Columntopropertycache = Cachebuilder.newbuilder (). MaximumSize ($). Build (New Cacheloader<clas S<?&gt, classinfo> () {@Override public ClassInfo load (class<?> cls) throws E
                    xception {ClassInfo ClassInfo = new ClassInfo ();
                    Classinfo.setcolumntopropertyoverrides (Getcolumntoproperty (CLS));
                    Classinfo.setprops (Getpropertydescriptors (CLS));
                return ClassInfo;

    }
    });
        static {Primitivedefaults.put (Integer.type, integer.valueof (0));
        Primitivedefaults.put (Short.type, Short.valueof ((short) 0));
        Primitivedefaults.put (Byte.type, byte.valueof ((Byte) 0));
        Primitivedefaults.put (Float.type, float.valueof (0f));
        Primitivedefaults.put (Double.type, double.valueof (0d));
        Primitivedefaults.put (Long.type, long.valueof (0L));
    Primitivedefaults.put (Boolean.type, Boolean.false);    Primitivedefaults.put (Character.type, Character.valueof ((char) 0));  } public Beanconverter () {} public <T> T Tobean (ResultSet rs, class<t> type) throws SQLException
        {ClassInfo ClassInfo = null;
        try {ClassInfo = Columntopropertycache.get (type);
        } catch (Executionexception e) {throw new IllegalArgumentException ("", e);
        } ResultSetMetaData RSMD = Rs.getmetadata (); int[] Columntoproperty = this.mapcolumnstoproperties (RSMD, Classinfo.getprops (),

        Classinfo.getcolumntopropertyoverrides ());
    Return Createbean (RS, type, Classinfo.getprops (), columntoproperty); } public <T> list<t> tobeanlist (ResultSet rs, class<t> type) throws SQLException {List&lt ;

        t> results = new arraylist<t> ();
        if (!rs.next ()) {return results;
        } ClassInfo ClassInfo = null; try {ClassInfo = ColumntopropertYcache.get (type);
        } catch (Executionexception e) {throw new IllegalArgumentException ("", e);
        } ResultSetMetaData RSMD = Rs.getmetadata (); int[] Columntoproperty = this.mapcolumnstoproperties (RSMD, Classinfo.getprops (),

        Classinfo.getcolumntopropertyoverrides ());
        do {Results.add (This.createbean (RS, type, Classinfo.getprops (), columntoproperty));

        } while (Rs.next ());
    return results; } Private <T> T Createbean (ResultSet rs, class<t> type, propertydescriptor[] Pro
        PS, int[] columntoproperty) throws SQLException {T bean = this.newinstance (type);
                for (int i = 1; i < columntoproperty.length; i++) {if (columntoproperty[i] = = Property_not_found) {
            Continue
            } PropertyDescriptor prop = Props[columntoproperty[i]];

       class<?> PropType = Prop.getpropertytype ();     Object value = null;

                if (propType! = null) {value = This.processcolumn (RS, I, propType);
                if (value = = null && proptype.isprimitive ()) {value = Primitivedefaults.get (PropType);
            } this.setvaluetobean (bean, prop, value);
    }} return bean; 

        private void Setvaluetobean (object target, PropertyDescriptor prop, object value) throws SQLException {

        Method setter = Prop.getwritemethod ();
        if (setter = = null) {return;
        } class<?>[] params = Setter.getparametertypes ();
                try {//convert types for some popular ones if (value instanceof java.util.Date) {
                Final String TargetType = Params[0].getname (); if ("Java.sql.Date". Equals (TargetType)) {value = new Java.sql.Date ((java.util.Date) value). GetTime ()
      );          } else if ("Java.sql.Time". Equals (TargetType)) {value = new Java.sql.Time ((
                (java.util.Date) value). GetTime ()); } else if ("Java.sql.Timestamp". Equals (TargetType)) {Timestamp Tsvalue = (Timestamp)
                    Value
                    int nanos = Tsvalue.getnanos ();
                    Value = new Java.sql.Timestamp (Tsvalue.gettime ());
                ((Timestamp) value). Setnanos (Nanos); }}//Don ' t call setter if the value object is ' t ' the right type if (This.iscompatib
            Letype (value, Params[0])) {Setter.invoke (target, New object[]{value}); } else {throw new SQLException ("Cannot set" + prop.getname () + ": incompatible
            Types, cannot convert "+ value.getclass (). GetName () +" to "+ params[0].getname ()); }} catch (illegalArgumentException e) {throw new SQLException ("Cannot set" + prop.getname () + ":" + e.g

        Etmessage ()); } catch (Illegalaccessexception e) {throw new SQLException ("Cannot set" + Prop.getname (

        ) + ":" + e.getmessage ()); } catch (InvocationTargetException e) {throw new SQLException ("Cannot set" + Prop.getna
        Me () + ":" + e.getmessage ());  }} Private Boolean Iscompatibletype (object value, class<?> type) {//Do Object check First, then

        Primitives if (value = = NULL | | type.isinstance (value)) {return true;

        } else if (Type.equals (integer.type) && value instanceof Integer) {return true;

        } else if (Type.equals (long.type) && value instanceof Long) {return true; } else if (Type.equals (double.type) && value instanceof Double) {return true;

        } else if (Type.equals (float.type) && value instanceof Float) {return true;

        } else if (Type.equals (short.type) && value instanceof short) {return true;

        } else if (Type.equals (byte.type) && value instanceof Byte) {return true;

        } else if (Type.equals (character.type) && value instanceof Character) {return true;

        } else if (Type.equals (boolean.type) && value instanceof Boolean) {return true;
    } return false; } protected int[] Mapcolumnstoproperties (ResultSetMetaData rsmd, Propertyd Escriptor[] Props, map<string, string> columntopropertyoverrides) throws
        SQLException {int cols = Rsmd.getcolumncount ();
        int[] Columntoproperty = new Int[cols + 1];

       Arrays.fill (Columntoproperty, Property_not_found); for (int col = 1; Col <= cols; col++) {String columnName = Rsmd.getcolumnlabel (col);
            if (null = = ColumnName | | 0 = = columnname.length ()) {columnName = Rsmd.getcolumnname (col);
            } String propertyname = Columntopropertyoverrides.get (ColumnName);
            if (propertyname = = null) {propertyname = ColumnName; } for (int i = 0; i < props.length; i++) {if (Propertyname.equalsignorecase (PROPS[I].GETN
                    Ame ())) {Columntoproperty[col] = i;
                Break
    }}} return columntoproperty; } private propertydescriptor[] Getpropertydescriptors (class<?> c) throws SQLException {Bea
        Ninfo beanInfo = null;

        try {beanInfo = Introspector.getbeaninfo (c);
             } catch (Introspectionexception e) {throw new SQLException (       "Bean Introspection failed:" + e.getmessage ());
    } return Beaninfo.getpropertydescriptors (); } protected <T> T newinstance (class<t> c) throws SQLException {try {return c.newins

        Tance (); } catch (Instantiationexception e) {throw new SQLException ("Cannot create" + c.getname (

        ) + ":" + e.getmessage ()); } catch (Illegalaccessexception e) {throw new SQLException ("Cannot create" + c.getname (
        ) + ":" + e.getmessage ());

     }
    }