JDBC工具

608145721.png

最近工作经常会遇到要做一个很小的微服务，服务中需要查数据库，
尽量不要使用 框架。习惯了Mybatis的方便，突然还不会了，对应该只有JDBC最小了吧；于是写了一 个小小的工具类提供服务，这个类提供一个数据库connection的初始化，然后提供update，select的接口；当然为了更加方便，里面用了点java的反射，提供统一查询接口。如下：

public class MysqlDBService {
    Logger LOG= LoggerFactory.getLogger(MysqlDBService.class);
    private Connection connection;

   public Connection getConnection(){
        return connection;
    }

    public MysqlDBService(){
    }
    public MysqlDBService(String uri) {
        try {
             this.initStatement(uri);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public MysqlDBService (String uri, String name, String password) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
        connection= DriverManager.getConnection(uri,name,password);
    }

    public void initConnection(String uri) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
        connection= DriverManager.getConnection(uri);
    }

    public  <Target> List<Target> parseQueryList(Class<Target> destType,String sql) {
        List<Target> list=  new ArrayList<Target>();
        Field[] fields= destType.getFields();

        try{
           Statement statement= connection.createStatement();
            ResultSet set= statement.executeQuery(sql);
            while (set.next()){
                Target target= destType.newInstance();
                for (Field field:fields){
                    field.setAccessible(true);
                    String typeName= field.getType().getName();
                    String fieldName= CamelCaseUtils.toUnderlineName(field.getName());
                    Object value= null;
                    //LOG.info("statement field is {}",fieldName);
                    if(typeName.equals("java.lang.String")){
                        value= getStrValue(set, fieldName);
                    }else if(typeName.equals("java.util.Date")){
                        value= getDateValue(set, fieldName);
                    }else if(typeName.equals("java.lang.Integer")){
                        value= getIntValue(set, fieldName);
                    }else if(typeName.equals("java.lang.Long")){
                        value= getLongValue(set, fieldName);
                    }else if(typeName.equals("java.math.BigDecimal")){
                        value= getBigDecimalValue(set, fieldName);
                    }else if(typeName.equals("java.sql.Timestamp")){
                        value= getTimestampValue(set, fieldName);
                    }else if(typeName.equals("java.lang.Double")){
                        value= getDoubleValue(set,fieldName);
                    }
                    
                    if(value!=null){
                        field.set(target, value);
                    }
                }
                list.add(target);
            }
        }catch (Exception e){
            LOG.info("parseQueryList failed for Collection {}", destType.getName());
        }
        return list;
    }

    public static String getStrValue(ResultSet set,String fieldName) throws IOException, SQLException {
        try{
            NClob clob= set.getNClob(fieldName);
            if(clob!=null){
                BufferedReader r = new BufferedReader(clob.getCharacterStream());
                StringBuilder b = new StringBuilder();
                String line;
                while((line=r.readLine())!=null) {
                    b.append(line);
                }
                return b.toString();
            }
        }catch (Exception e){
            return set.getString(fieldName);
        }
        return null;
    }

    public List<Integer> parseFieldList(String sql){
        List<Integer> list=  new ArrayList<Integer>();
        try{
           Statement statement= connection.createStatement();
            ResultSet set= statement.executeQuery(sql);
            while (set.next()){
                try{
                    list.add(set.getInt(1));
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public Integer parseOneField(String sql){
        try{
           Statement statement= connection.createStatement();
            ResultSet set= statement.executeQuery(sql);
            while (set.next()){
                try{
                    return set.getInt(1);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }


    public static Integer getIntValue(ResultSet set,String fieldName) throws SQLException {
        return set.getInt(fieldName);
    }

    public static Date getDateValue(ResultSet set,String fieldName) throws SQLException {
        return set.getDate(fieldName);
    }

    public static Long getLongValue(ResultSet set,String fieldName) throws SQLException {
        return set.getLong(fieldName);
    }

    public static BigDecimal getBigDecimalValue(ResultSet set, String fieldName) throws SQLException {
        return set.getBigDecimal(fieldName);
    }

    public static Double getDoubleValue(ResultSet set, String fieldName) throws SQLException {
        return set.getDouble(fieldName);
    }


    public static Timestamp getTimestampValue(ResultSet set, String fieldName)  throws SQLException {
        return set.getTimestamp(fieldName);
    }

    public void update(String sql,Statement statement){
        try {
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int update(String sql){
        try {
           Statement statement= connection.createStatement();
            statement.executeUpdate(sql);
            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

上面Service还依赖于一个驼峰字段转换类，如下：

public class CamelCaseUtils {

    private static final char SEPARATOR = '_';

    public static String toUnderlineName(String s) {
        if (s == null) {
            return null;
        }

        StringBuilder sb = new StringBuilder();
        boolean upperCase = false;
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);

            boolean nextUpperCase = true;

            if (i < (s.length() - 1)) {
                nextUpperCase = Character.isUpperCase(s.charAt(i + 1));
            }

            if ((i >= 0) && Character.isUpperCase(c)) {
                if (!upperCase || !nextUpperCase) {
                    if (i > 0) sb.append(SEPARATOR);
                }
                upperCase = true;
            } else {
                upperCase = false;
            }

            sb.append(Character.toLowerCase(c));
        }

        return sb.toString();
    }

    public static String toCamelCase(String s) {
        if (s == null) {
            return null;
        }

        s = s.toLowerCase();

        StringBuilder sb = new StringBuilder(s.length());
        boolean upperCase = false;
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);

            if (c == SEPARATOR) {
                upperCase = true;
            } else if (upperCase) {
                sb.append(Character.toUpperCase(c));
                upperCase = false;
            } else {
                sb.append(c);
            }
        }

        return sb.toString();
    }

    public static String toCapitalizeCamelCase(String s) {
        if (s == null) {
            return null;
        }
        s = toCamelCase(s);
        return s.substring(0, 1).toUpperCase() + s.substring(1);
    }

    public static void main(String[] args) {
        System.out.println(toUnderlineName("userAvatar"));
    }

}