美图欣赏 | 设为首页 | 加入收藏 | 网站地图

当前位置:电脑中国 > 编程 > Java教程 >

JAVA(JDBC)通用查询

2017-09-07 10:51|来源:未知 |作者:dnzg |点击:

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

      最近学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=男" + "]";
        }
        
    }

}
					
(责任编辑:dnzg)