java jdbc result json mapper

>code>package com.example;

import java.sql.*;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.google.gson.JsonPrimitive;

public class DbMapper {
 
 public static JsonArray select(String sql) throws Exception{
  
  
  Connection dbConnection = CommonConstant.getDbConn();
  
  Statement getFromDb = dbConnection.createStatement();
  ResultSet rs = getFromDb.executeQuery(sql);
  ResultSetMetaData rsmd = rs.getMetaData();
  JsonArray recordsArray = new JsonArray();
  
  int numColumns = rsmd.getColumnCount();
     String[] arrColumnNames = new String[numColumns];
     int[] columnTypes = new int[numColumns];

     for (int i = 0; i < arrColumnNames.length; i++) {
         arrColumnNames[i] = rsmd.getColumnLabel(i + 1);
         columnTypes[i] = rsmd.getColumnType(i + 1);
         /*System.out.print(i + " : ");
         System.out.print(columnNames[i]);
         System.out.print(i + " = ");
         System.out.print(columnTypes[i]);
         System.out.print("\n");
         */
     }
     
     //JsonPrimitive jsonPrimitiveNull = new JsonPrimitive(null);
     long startTime = System.currentTimeMillis();
  while (rs.next()) {
    boolean b;
          long l;
          double d;
          float f;
          String s;
          
   JsonObject currentRecord = new JsonObject();

     for( int i=0; i<arrColumnNames.length; i++) {
      
      
    String column_name = arrColumnNames[i];
    
    //System.out.println();
    //System.out.println("while loop: "+column_name + " : "+ columnTypes[i]);

     switch (columnTypes[i]) {
     case Types.BIT: // tiny int 가 비트로 와서 수정함 
     case Types.TINYINT:
      l = rs.getInt(i + 1);
                  if (rs.wasNull()) {
                      //jgen.writeNull();
                      currentRecord.add(column_name,null);
                  } else {
                      //currentRecord.add(column_name,new JsonPrimitive(l));
                   System.out.println(column_name);
                   System.out.println(l);
                   System.out.println(new JsonPrimitive(l));
                   
                      currentRecord.add(column_name,new JsonPrimitive(l));
                      
                  }
                  break;
              case Types.INTEGER:
              case Types.SMALLINT:
 
                  l = rs.getInt(i + 1);
                  if (rs.wasNull()) {
                      //jgen.writeNull();
                      currentRecord.add(column_name,null);
                  } else {
                      //currentRecord.add(column_name,new JsonPrimitive(l));
                      currentRecord.add(column_name,new JsonPrimitive(l));
                      
                  }
                  break;

              case Types.BIGINT:
                  l = rs.getLong(i + 1);
                  if (rs.wasNull()) {
                      currentRecord.add(column_name,null);
                  } else {
                      currentRecord.add(column_name,new JsonPrimitive(l));
                  }
                  break;

              case Types.DECIMAL:
              case Types.NUMERIC:
                  //jgen.writeNumber(rs.getBigDecimal(i + 1));
               rs.getBigDecimal(i + 1);
               if (rs.wasNull()) {
                currentRecord.add(column_name,null);
               }else{
                currentRecord.add(column_name,new JsonPrimitive(rs.getBigDecimal(i + 1)));
               }
                  
                  break;

              case Types.FLOAT:
              /* f = rs.getFloat(i + 1);
               //System.out.println("=====================================================================");
                  //System.out.println(f);
                  //System.out.println("=====================================================================");
                  if (rs.wasNull()) {
                      currentRecord.add(column_name,null);
                  } else {
                      //jgen.writeNumber(d);
                   currentRecord.add(column_name,new JsonPrimitive(f));
                  }
                  break;*/
              case Types.REAL:
              case Types.DOUBLE:
                  d = rs.getDouble(i + 1);
                  System.out.println(d);
                  if (rs.wasNull()) {
                      currentRecord.add(column_name,null);
                  } else {
                      //jgen.writeNumber(d);
                   currentRecord.add(column_name,new JsonPrimitive(d));
                  }
                  break;

              //case Types.NVARCHAR:
              case Types.VARCHAR:
              //case Types.LONGNVARCHAR:
              case Types.LONGVARCHAR:
                  //jgen.writeString(rs.getString(i + 1));
                  s = rs.getString(i + 1);
                  if (rs.wasNull()) {
                      currentRecord.add(column_name,null);
                  } else {
                      //jgen.writeNumber(d);
                   currentRecord.add(column_name,new JsonPrimitive(s));
                  }           
                  break;

              case Types.BOOLEAN:
              
                  b = rs.getBoolean(i + 1);
                  if (rs.wasNull()) {
                      currentRecord.add(column_name,null);
                  } else {
                      //jgen.writeBoolean(b);
                      currentRecord.add(column_name,new JsonPrimitive(b));
                  }
                  break;

              case Types.BINARY:
              case Types.VARBINARY:
              case Types.LONGVARBINARY:
                  //jgen.writeBinary(rs.getBytes(i + 1));
                  //currentRecord.add(column_name,new JsonPrimitive(""+rs.getBytes(i + 1).toString()));
               //System.out.println(rs.getString(i + 1));
               String str = new String(rs.getBytes(i+1),"UTF-8");
               currentRecord.add(column_name,new JsonPrimitive(str));
               //currentRecord.add(column_name,new JsonPrimitive(""+rs.getString(i + 1)));
                  break;
/*
              case Types.TINYINT:
              case Types.SMALLINT:
                  l = rs.getShort(i + 1);
                  if (rs.wasNull()) {
                      currentRecord.add(column_name,null);
                  } else {
                      currentRecord.add(column_name,new JsonPrimitive(l));
                  }
                  break;
*/
              case Types.DATE:
                  // provider.defaultSerializeDateValue(rs.getDate(i + 1), jgen);
                  Date date = rs.getDate(i + 1);
                  if(rs.wasNull()){
                   currentRecord.add(column_name,null);
                  }else{
                   currentRecord.add(column_name,new JsonPrimitive(rs.getDate(i + 1).toString()));  
                  }
                  
                  break;

              case Types.TIMESTAMP:
                  //provider.defaultSerializeDateValue(rs.getTime(i + 1), jgen);
               Time t1 = rs.getTime(i + 1);
                  if(rs.wasNull()){
                   currentRecord.add(column_name,null);
                  }else{
                   currentRecord.add(column_name,new JsonPrimitive(rs.getTime(i + 1).toString()));    
                  }
                  
                  break;

              case Types.BLOB:
                  //Blob blob = rs.getBlob(i+1);
                  /*Blob blob = rs.getBlob("photo");
                  // materialize BLOB onto client
                  return blob.getBytes(1, (int) blob.length());*/
                  //byte[] b1 = blob.getBytes(i+1, (int) blob.length());
                  //System.out.println(b1.toString());
                  //provider.defaultSerializeValue(blob.getBinaryStream(), jgen);
                  //currentRecord.add(column_name,new JsonPrimitive(b1.toString()));
                  //blob.free();
                  String str1 = new String(rs.getBytes(i+1),"UTF-8");
               currentRecord.add(column_name,new JsonPrimitive(str1));
               
                  break;

              case Types.CLOB:
                  Clob clob = rs.getClob(i+1);
                  //provider.defaultSerializeValue(clob.getCharacterStream(), jgen);
                  currentRecord.add(column_name,new JsonPrimitive(clob.toString()));
                  //clob.free();
                  break;

              case Types.ARRAY:
                  throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type ARRAY");

              case Types.STRUCT:
                  throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type STRUCT");

              case Types.DISTINCT:
                  throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type DISTINCT");

              case Types.REF:
                  throw new RuntimeException("ResultSetSerializer not yet implemented for SQL type REF");

              case Types.JAVA_OBJECT:
              default:
               rs.getObject(i+1);
                  if (rs.wasNull()) {
                   currentRecord.add(column_name,null);
                  }else{
                   currentRecord.add(column_name,new JsonPrimitive(rs.getObject(i + 1).toString()));
                  }
                  //provider.defaultSerializeValue(rs.getObject(i + 1), jgen);
                  
                  break;
              }
     }
     //System.out.println(currentRecord);
     recordsArray.add(currentRecord);
  }
  long stopTime = System.currentTimeMillis();
       long elapsedTime = stopTime - startTime;
       //System.out.println("select switch  :"+elapsedTime);
  return recordsArray;
 }
}

댓글