src/cx/fbn/nevernote/sql/driver/NSqlQuery.java

   1 /*\r
   2  * This file is part of NeverNote \r
   3  * Copyright 2009 Randy Baumgarte\r
   4  * \r
   5  * This file may be licensed under the terms of of the\r
   6  * GNU General Public License Version 2 (the ``GPL'').\r
   7  *\r
   8  * Software distributed under the License is distributed\r
   9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
  10  * express or implied. See the GPL for the specific language\r
  11  * governing rights and limitations.\r
  12  *\r
  13  * You should have received a copy of the GPL along with this\r
  14  * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
  15  * or write to the Free Software Foundation, Inc.,\r
  16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
  17  *\r
  18 */\r
  19 \r
  20 package cx.fbn.nevernote.sql.driver;\r
  21 \r
  22 import java.io.ByteArrayInputStream;\r
  23 import java.sql.Blob;\r
  24 import java.sql.Connection;\r
  25 import java.sql.PreparedStatement;\r
  26 import java.sql.ResultSet;\r
  27 import java.sql.SQLException;\r
  28 import java.sql.Statement;\r
  29 import java.util.HashMap;\r
  30 \r
  31 public class NSqlQuery {\r
  32         \r
  33         private final Connection connection;\r
  34         private String lastError;\r
  35         private ResultSet resultSet;\r
  36         private PreparedStatement preparedStatement;\r
  37         private final HashMap<String, Integer>  positionMap;\r
  38         private ByteArrayInputStream fis;\r
  39         \r
  40         \r
  41         public NSqlQuery(Connection c) {\r
  42                 connection = c;\r
  43                 positionMap = new HashMap<String, Integer>();\r
  44         }\r
  45         \r
  46         \r
  47         public boolean next() {\r
  48                 lastError = null;\r
  49                 \r
  50                 if (resultSet == null) {\r
  51                         lastError = "Result set is null";\r
  52                         return false;\r
  53                 }\r
  54                 try {\r
  55                         return resultSet.next();\r
  56                 } catch (SQLException e) {\r
  57                         e.printStackTrace();\r
  58                         lastError = e.getMessage();\r
  59                         return false;\r
  60                 }\r
  61         }\r
  62         \r
  63         \r
  64         public boolean exec(String sql) {\r
  65                 Statement st;\r
  66                 boolean retVal = false;\r
  67                 lastError = "";\r
  68                 resultSet = null;\r
  69                 try {\r
  70                         st = connection.createStatement();\r
  71                         retVal = st.execute(sql);\r
  72                         resultSet = st.getResultSet();\r
  73                 } catch (SQLException e) {\r
  74                         e.printStackTrace();\r
  75                         lastError = e.getMessage();\r
  76                 }\r
  77                 return retVal;\r
  78         }\r
  79         \r
  80         public boolean exec() {\r
  81                 lastError = "";\r
  82                 resultSet = null;\r
  83                 \r
  84                 if (preparedStatement == null) {\r
  85                         lastError = "No SQL statement prepared";\r
  86                         return false;\r
  87                 }\r
  88 \r
  89                 try {\r
  90                         preparedStatement.execute();\r
  91                         resultSet = preparedStatement.getResultSet();\r
  92                 } catch (SQLException e) {\r
  93                         e.printStackTrace();\r
  94                         lastError = e.getMessage();\r
  95                         return false;\r
  96                 }\r
  97                 \r
  98                 \r
  99                 return true;\r
 100         }\r
 101         \r
 102         \r
 103         public String lastError() {\r
 104                 if (lastError == null)\r
 105                         return "";\r
 106                 return lastError;\r
 107         }\r
 108         \r
 109         \r
 110         public Object valueObject(int position) {\r
 111                 lastError = null;\r
 112                 if (resultSet == null) {\r
 113                         lastError = "ResultSet is null";\r
 114                         return null;\r
 115                 }\r
 116                 \r
 117                 try {\r
 118                         return resultSet.getObject(position+1);\r
 119                 } catch (SQLException e) {\r
 120                         e.printStackTrace();\r
 121                         lastError = e.getMessage();\r
 122                         return null;\r
 123                 }\r
 124         }\r
 125         \r
 126         \r
 127         public String valueString(int position) {\r
 128                 lastError = null;\r
 129                 if (resultSet == null) {\r
 130                         lastError = "ResultSet is null";\r
 131                         return null;\r
 132                 }\r
 133                 \r
 134                 try {\r
 135                         return resultSet.getString(position+1);\r
 136                 } catch (SQLException e) {\r
 137                         e.printStackTrace();\r
 138                         lastError = e.getMessage();\r
 139                         return null;\r
 140                 }\r
 141         }\r
 142         \r
 143         public  boolean valueBoolean(int position, boolean unknown) {\r
 144                 try {\r
 145                         return resultSet.getBoolean(position+1);\r
 146                 } catch (SQLException e) {\r
 147                         e.printStackTrace();\r
 148                         return unknown;\r
 149                 }\r
 150         }\r
 151 \r
 152         public  long valueLong(int position) {\r
 153                 try {\r
 154                         return resultSet.getLong(position+1);\r
 155                 } catch (SQLException e) {\r
 156                         e.printStackTrace();\r
 157                         return 0;\r
 158                 }\r
 159         }\r
 160         \r
 161         public int valueInteger(int position) {\r
 162                 try {\r
 163                         return resultSet.getInt(position+1);\r
 164                 } catch (SQLException e) {\r
 165                         e.printStackTrace();\r
 166                         return 0;\r
 167                 }\r
 168         }\r
 169         \r
 170         public void bindValue(String field, String value) {\r
 171                 Integer position = positionMap.get(field.toLowerCase());\r
 172                 lastError = null;\r
 173                 if (preparedStatement == null) {\r
 174                         lastError = "No prepared statement exists";\r
 175                         return;\r
 176                 }\r
 177                 if (position != null && position > 0) {\r
 178                         try {\r
 179                                 preparedStatement.setString(position, value);\r
 180                         } catch (SQLException e) {\r
 181                                 e.printStackTrace();\r
 182                                 lastError = e.getMessage();\r
 183                         }\r
 184                         return;\r
 185                 }       \r
 186         }\r
 187         \r
 188         public void bindValue(String field, boolean value) {\r
 189                 Integer position = positionMap.get(field.toLowerCase());\r
 190                 lastError = null;\r
 191                 if (preparedStatement == null) {\r
 192                         lastError = "No prepared statement exists";\r
 193                         return;\r
 194                 }\r
 195                 if (position > 0) {\r
 196                         try {\r
 197                                 preparedStatement.setBoolean(position, value);\r
 198                         } catch (SQLException e) {\r
 199                                 e.printStackTrace();\r
 200                                 lastError = e.getMessage();\r
 201                         }\r
 202                         return;\r
 203                 }       \r
 204         }\r
 205         \r
 206         \r
 207 \r
 208         public void bindValue(String field, int value) {\r
 209                 Integer position = positionMap.get(field.toLowerCase());\r
 210                 lastError = null;\r
 211                 if (preparedStatement == null) {\r
 212                         lastError = "No prepared statement exists";\r
 213                         return;\r
 214                 }\r
 215                 if (position > 0) {\r
 216                         try {\r
 217                                 preparedStatement.setInt(position, value);\r
 218                         } catch (SQLException e) {\r
 219                                 e.printStackTrace();\r
 220                                 lastError = e.getMessage();\r
 221                         }\r
 222                         return;\r
 223                 }       \r
 224         }\r
 225         \r
 226         \r
 227 \r
 228         public void bindValue(String field, double value) {\r
 229                 Integer position = positionMap.get(field.toLowerCase());\r
 230                 lastError = null;\r
 231                 if (preparedStatement == null) {\r
 232                         lastError = "No prepared statement exists";\r
 233                         return;\r
 234                 }\r
 235                 if (position > 0) {\r
 236                         try {\r
 237                                 preparedStatement.setDouble(position, value);\r
 238                         } catch (SQLException e) {\r
 239                                 e.printStackTrace();\r
 240                                 lastError = e.getMessage();\r
 241                         }\r
 242                         return;\r
 243                 }       \r
 244         }\r
 245         \r
 246         \r
 247         \r
 248         public void bindValue(String field, byte[] value) {\r
 249                 Integer position = positionMap.get(field.toLowerCase());\r
 250                 lastError = null;\r
 251                 if (preparedStatement == null) {\r
 252                         lastError = "No prepared statement exists";\r
 253                         return;\r
 254                 }\r
 255                 if (position > 0) {\r
 256                         try {\r
 257                                 preparedStatement.setBytes(position, value);\r
 258                         } catch (SQLException e) {\r
 259                                 e.printStackTrace();\r
 260                                 lastError = e.getMessage();\r
 261                         }\r
 262                         return;\r
 263                 }       \r
 264         }\r
 265         \r
 266         \r
 267         \r
 268         public boolean prepare(String statement) {\r
 269                 positionMap.clear();\r
 270                 preparedStatement = null;\r
 271                 lastError = null;\r
 272                 \r
 273                 int position = 1;\r
 274                 for (int i=statement.indexOf(":"); i>0; i=statement.indexOf(":",i)) {\r
 275                         int endField = statement.indexOf(" ",i+1);\r
 276                         int nextComma = statement.indexOf(",",i+1);\r
 277                         int nextBracket = statement.indexOf(")",i+1);\r
 278                         \r
 279                         if (nextComma > 0 && nextComma < endField)\r
 280                                 endField = nextComma;\r
 281                         if (endField == -1)\r
 282                                 endField = nextBracket;\r
 283                         if (nextBracket > 0 && nextBracket < endField)\r
 284                                 endField = nextBracket;\r
 285                         \r
 286                         String fieldName = null;\r
 287                         if (endField > 0) {\r
 288                                 fieldName = statement.substring(i,endField);\r
 289                         }\r
 290                         else {\r
 291                                 fieldName = statement.substring(i);\r
 292                                 endField = statement.length();\r
 293                         }\r
 294                         statement = statement.substring(0,i)+"?" +statement.substring(endField);\r
 295                         positionMap.put(fieldName.toLowerCase(), new Integer(position));\r
 296                         position++;\r
 297                 }\r
 298                 \r
 299                 \r
 300                 try {\r
 301                         preparedStatement = connection.prepareStatement(statement);\r
 302                 } catch (SQLException e) {\r
 303                         e.printStackTrace();\r
 304                         lastError = e.getMessage();\r
 305                         return false;\r
 306                 }\r
 307                 \r
 308                 \r
 309                 return true;\r
 310         }\r
 311         \r
 312         \r
 313         public void bindBlob(String field, byte[] value) {\r
 314                 Integer position = positionMap.get(field.toLowerCase());\r
 315                 lastError = null;\r
 316                 if (preparedStatement == null) {\r
 317                         lastError = "No prepared statement exists";\r
 318                         return;\r
 319                 }\r
 320                 if (position != null && position > 0) {\r
 321                         try {\r
 322                                 fis = new ByteArrayInputStream(value);\r
 323                                 preparedStatement.setBinaryStream(position, fis);\r
 324                         } catch (SQLException e) {\r
 325                                 e.printStackTrace();\r
 326                                 lastError = e.getMessage();\r
 327                         }\r
 328                         return;\r
 329                 }       \r
 330         }\r
 331         \r
 332         \r
 333         public byte[] getBlob(int position) {\r
 334                 Blob dataBinary;\r
 335                 try {\r
 336                         dataBinary = resultSet.getBlob(position+1);\r
 337                         byte[] b;\r
 338                         if (dataBinary == null)\r
 339                                 return null;\r
 340                         b = dataBinary.getBytes(1, (int) dataBinary.length());\r
 341                         return b;\r
 342                 } catch (SQLException e) {\r
 343                         e.printStackTrace();\r
 344                         lastError = e.getMessage();\r
 345                 }\r
 346                 return null;\r
 347         }\r
 348         \r
 349         \r
 350 \r
 351 }\r