JDBC原生态SQL查询封装

package read;

public class ColumnVo {

	private String COLUMN_NAME;						//字段名
	private Class DATE_TYPE;						//字段类型
	private String IS_NULLABLE;						//字段长度
	private String COLUMNT_TYPE;					//字段类型（包含长度）
	private String COLUMN_KEY;						//主键（PRI）
	private String EXTRA;							//自增长auto_increment
	private String COLUMN_COMMENT;					//备注
	
	
	
	
	public String getCOLUMN_NAME() {
		return COLUMN_NAME;
	}
	public void setCOLUMN_NAME(String cOLUMNNAME) {
		COLUMN_NAME = cOLUMNNAME;
	}
	public Class getDATE_TYPE() {
		return DATE_TYPE;
	}
	public void setDATE_TYPE(Class dATETYPE) {
		DATE_TYPE = dATETYPE;
	}
	public String getIS_NULLABLE() {
		return IS_NULLABLE;
	}
	public void setIS_NULLABLE(String iSNULLABLE) {
		IS_NULLABLE = iSNULLABLE;
	}
	public String getCOLUMN_KEY() {
		return COLUMN_KEY;
	}
	public void setCOLUMN_KEY(String cOLUMNKEY) {
		COLUMN_KEY = cOLUMNKEY;
	}
	public String getEXTRA() {
		return EXTRA;
	}
	public void setEXTRA(String eXTRA) {
		EXTRA = eXTRA;
	}
	public String getCOLUMN_COMMENT() {
		return COLUMN_COMMENT;
	}
	public void setCOLUMN_COMMENT(String cOLUMNCOMMENT) {
		COLUMN_COMMENT = cOLUMNCOMMENT;
	}
	public ColumnVo() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "ColumnVo [COLUMN_COMMENT=" + COLUMN_COMMENT + ", COLUMN_KEY="
				+ COLUMN_KEY + ", COLUMN_NAME=" + COLUMN_NAME + ", DATE_TYPE="
				+ DATE_TYPE + ", EXTRA=" + EXTRA + ", IS_NULLABLE="
				+ IS_NULLABLE + "]";
	}
	
	
	
}

  
  

   
   

  
  

package read;

import java.util.List;

public class TableVo {

	private String tableSchema;			//数据库名
	private String tableName;			//表名
	private List<ColumnVo> columns;		//表中所有列
	public String getTableSchema() {
		return tableSchema;
	}
	public void setTableSchema(String tableSchema) {
		this.tableSchema = tableSchema;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public List<ColumnVo> getColumns() {
		return columns;
	}
	public void setColumns(List<ColumnVo> columns) {
		this.columns = columns;
	}
	public TableVo() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "TableVo [columns=" + columns + ", tableName=" + tableName
				+ ", tableSchema=" + tableSchema + "]";
	}
	
}

  
  

   
   

  
  

package read;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * @author yy
 */
public class Reflect {

	
	private static String  url = "jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf8";
	private static String user = "root";
	private static String password = "root";
	private static String driverName = "com.mysql.jdbc.Driver";
	
	
	/**
	 * @author yy
	 * @param args
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args){
		try {
			String first_index = "0";
			String last_index = "2";
			String pageing = " limit "+first_index+","+last_index;
			
			String sql_datas = "select t.* from t_test_user as t"+pageing;
			
			String sql_field = "SELECT t.COLUMN_NAME,t.DATA_TYPE,t.IS_NULLABLE,t.COLUMN_KEY,t.EXTRA,t.COLUMN_COMMENT FROM information_schema.columns AS t WHERE table_name='t_test_user' AND table_schema = 'mytest'";
			//查询到的表数据
			List datas = getData(sql_datas);
			
			//表的字段属性
			TableVo table = getFieldAttribute(sql_field);
			
			//输出结果
			for (ColumnVo column:table.getColumns()) {
				System.out.println(column.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("ERROR");
		}
		
	}
	
	
	/**
	 * 获取数据
	 */
	public static List<Map<String,?>> getData(String sql){
		
		List<Map<String,?>> list = Reflect.excuteSqlByJDBC(sql);
		for (Map<String, ?> map : list) {
			System.out.print("[");
			for (Map.Entry<String, ?> entry : map.entrySet()) {
				System.out.print(entry.getKey()+":"+entry.getValue()+":"+entry.getValue().getClass().getSimpleName()+",");
			}
			System.out.println("]");
		}
		
		return list;
	}
	
	/**
	 * 获取表字段信息
	 * @return
	 * @throws ClassNotFoundException
	 */
	public static TableVo getFieldAttribute(String sql) throws ClassNotFoundException{
		
		List<Map<String,?>> list = Reflect.excuteSqlByJDBC(sql);
		TableVo table = new TableVo();
		table.setTableSchema("mytest");
		table.setTableName("t_test_user");
		List cols = new ArrayList();
		for (Map<String, ?> map : list) {
			
			ColumnVo column = new ColumnVo();
			for (Map.Entry<String, ?> entry : map.entrySet()) {
				String key = entry.getKey().toUpperCase();
				Object value = entry.getValue();
				
				if(key.equals("COLUMN_NAME")){
					column.setCOLUMN_NAME(value.toString());
				}
				if(key.equals("DATA_TYPE")){
					if(value.equals("int"))
					{
						column.setDATE_TYPE(Integer.class);
					}
					else if(value.equals("varchar"))
					{
						column.setDATE_TYPE(String.class);
					}
					else if(value.equals("date"))
					{
						column.setDATE_TYPE(Date.class);
					}
					else if(value.equals("double"))
					{
						column.setDATE_TYPE(Double.class);
					}
					else if(value.equals("float"))
					{
						column.setDATE_TYPE(Float.class);
					}
					//mysql字段类型未处理完
					//.
					//.
					//.
				}
				if(key.equals("IS_NULLABLE")){
					column.setIS_NULLABLE(value.toString());
				}
				if(key.equals("COLUMN_KEY")){
					if(value!=null)
					{
						column.setCOLUMN_KEY(value.toString());
					}
				}
				if(key.equals("COLUMN_COMMENT")){
					if(value!=null)
					{
						column.setCOLUMN_COMMENT(value.toString());
					}
				}
				if(key.equals("EXTRA")){
					if(value!=null)
					{
						column.setEXTRA(value.toString());
					}
				}
			}
			cols.add(column);
		}
		
		table.setColumns(cols);
		
		return table;
	}
	

	/**
	 * 查询数据信息
	 * @param sql
	 * @return
	 * @throws SQLException 
	 */
	public static List<Map<String,?>> excuteSqlByJDBC(String sql){
		List<Map<String,?>> list = new ArrayList<Map<String,?>> ();
		
		Statement psmt = null;
		Connection conn = null;
		try {
			//加载驱动
			Class.forName(driverName);
			//建立连接
			conn = DriverManager.getConnection(url, user, password);
			//创建statement
			psmt = conn.createStatement();
			//执行sql
			ResultSet resultset = psmt.executeQuery(sql);
			while(resultset.next()){
				Map map = new HashMap();
				ResultSetMetaData metaData = resultset.getMetaData();
				int columnCount = metaData.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					//列名
					String columnName = metaData.getColumnName(i);
					//列类型
					String columnType = metaData.getColumnTypeName(i).toUpperCase();
					//将字段名作为key,将字段值作为value
					map.put(columnName, getValue(columnType, resultset.getString(i)));
				}
				list.add(map);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(psmt!=null)
			{
				try {
					psmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null)
			{
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		if(list.size()>0)
		{
			return list;
		}
		else
		{
			return null;
		}
	}
	
	
	/**
	 * 返回原数据类型的数据
	 * @param columnType
	 * @param value
	 * @return
	 */
	public static Object getValue(String columnType,String value){
		
		if("INTEGER".equals(columnType) || "INT".equals(columnType))
		{
			Integer temp = Integer.parseInt(value);
			return temp;
		}
		if("VARCHAR".equals(columnType))
		{
			String temp = String.valueOf(value);
			return temp;
		}
		if("DOUBLE".equals(columnType))
		{
			Double temp = Double.valueOf(value);
			return temp;
		}
		if("FLOAT".equals(columnType))
		{
			Float temp = Float.valueOf(value);
			return temp;
		}
		if("DATE".equals(columnType))
		{
			try {
				//Date temp =  new SimpleDateFormat("yyyy-MM-dd").parse(value);
				return value;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		//mysql字段类型未处理完
		//.
		//.
		//.
		return null;
	}
}