List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:org.waarp.common.database.data.AbstractDbData.java
/** * Set Value into PreparedStatement// www. j av a 2 s .c o m * * @param ps * @param value * @param rank * >= 1 * @throws WaarpDatabaseSqlException */ static public void setTrueValue(PreparedStatement ps, DbValue value, int rank) throws WaarpDatabaseSqlException { try { switch (value.type) { case Types.VARCHAR: if (value.value == null) { ps.setNull(rank, Types.VARCHAR); break; } ps.setString(rank, (String) value.value); break; case Types.LONGVARCHAR: if (value.value == null) { ps.setNull(rank, Types.LONGVARCHAR); break; } ps.setString(rank, (String) value.value); break; case Types.BIT: if (value.value == null) { ps.setNull(rank, Types.BIT); break; } ps.setBoolean(rank, (Boolean) value.value); break; case Types.TINYINT: if (value.value == null) { ps.setNull(rank, Types.TINYINT); break; } ps.setByte(rank, (Byte) value.value); break; case Types.SMALLINT: if (value.value == null) { ps.setNull(rank, Types.SMALLINT); break; } ps.setShort(rank, (Short) value.value); break; case Types.INTEGER: if (value.value == null) { ps.setNull(rank, Types.INTEGER); break; } ps.setInt(rank, (Integer) value.value); break; case Types.BIGINT: if (value.value == null) { ps.setNull(rank, Types.BIGINT); break; } ps.setLong(rank, (Long) value.value); break; case Types.REAL: if (value.value == null) { ps.setNull(rank, Types.REAL); break; } ps.setFloat(rank, (Float) value.value); break; case Types.DOUBLE: if (value.value == null) { ps.setNull(rank, Types.DOUBLE); break; } ps.setDouble(rank, (Double) value.value); break; case Types.VARBINARY: if (value.value == null) { ps.setNull(rank, Types.VARBINARY); break; } ps.setBytes(rank, (byte[]) value.value); break; case Types.DATE: if (value.value == null) { ps.setNull(rank, Types.DATE); break; } ps.setDate(rank, (Date) value.value); break; case Types.TIMESTAMP: if (value.value == null) { ps.setNull(rank, Types.TIMESTAMP); break; } ps.setTimestamp(rank, (Timestamp) value.value); break; case Types.CLOB: if (value.value == null) { ps.setNull(rank, Types.CLOB); break; } ps.setClob(rank, (Reader) value.value); break; case Types.BLOB: if (value.value == null) { ps.setNull(rank, Types.BLOB); break; } ps.setBlob(rank, (InputStream) value.value); break; default: throw new WaarpDatabaseSqlException("Type not supported: " + value.type + " at " + rank); } } catch (ClassCastException e) { throw new WaarpDatabaseSqlException("Setting values casting error: " + value.type + " at " + rank, e); } catch (SQLException e) { DbSession.error(e); throw new WaarpDatabaseSqlException("Setting values in error: " + value.type + " at " + rank, e); } }
From source file:edu.ku.brc.specify.toycode.mexconabio.MexConvToSQLNew.java
/** * /*from w w w. java2 s . c o m*/ */ public void convertFROMCSV(final ConfigureCSV config, final CsvReader csv) { String str = ""; int strLen = 0; int inx = 0; CsvReader csvObj = null; Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/mex?characterEncoding=UTF-8&autoReconnect=true", "root", "root"); stmt = conn.createStatement(); StringBuilder pStmtStr = new StringBuilder(); StringBuilder sb = new StringBuilder(); StringBuilder ques = new StringBuilder(); int[] fieldLengths = null; BasicSQLUtils.deleteAllRecordsFromTable(conn, "mex", SERVERTYPE.MySQL); Vector<Integer> types = new Vector<Integer>(); Vector<String> names = new Vector<String>(); File file = new File("/Users/rods/Documents/Untitled.mer"); Element root = XMLHelper.readFileToDOM4J(new File("/Users/rods/Documents/Acer.xml")); if (root != null) { fieldLengths = new int[csv.getColumnCount()]; for (int i = 0; i < fieldLengths.length; i++) { fieldLengths[i] = 0; } int row = 0; while (csv.readRecord()) { row++; for (int col = 0; col < csv.getColumnCount(); col++) { String dataObj = csv.get(col); int len = dataObj.length() + 1; if (len > fieldLengths[inx]) { fieldLengths[inx] = len; } inx++; } if (row % 10000 == 0) System.out.println(row); } System.out.println("--------------"); HashMap<String, Integer> hashSize = new HashMap<String, Integer>(); for (int i = 0; i < names.size(); i++) { hashSize.put(names.get(i), fieldLengths[i]); System.out.println(names.get(i) + " -> " + fieldLengths[i]); } sb.append("CREATE TABLE mex ("); List<?> items = root.selectNodes("/FIELDS/FIELD"); //$NON-NLS-1$ System.out.println(items.size()); inx = 0; for (Iterator<?> capIter = items.iterator(); capIter.hasNext();) { Element fieldNode = (Element) capIter.next(); String nullOK = fieldNode.attributeValue("EMPTYOK"); //$NON-NLS-1$ String fldName = fixName(fieldNode.attributeValue("NAME").trim()); //$NON-NLS-1$ String type = fieldNode.attributeValue("TYPE"); //$NON-NLS-1$ sb.append("`"); sb.append(fldName); sb.append("` "); System.err.println("[" + fldName + "]"); int len = hashSize.get(fldName); if (pStmtStr.length() > 0) pStmtStr.append(','); pStmtStr.append("`" + fldName + "`"); if (ques.length() > 0) ques.append(','); ques.append("?"); if (type.equals("TEXT")) { if (StringUtils.contains(fldName, "img folder")) { sb.append("longtext "); } else { sb.append("VARCHAR(" + len + ") CHARACTER SET utf8 "); } types.add(DataType.TEXT.ordinal()); } else if (type.equals("NUMBER")) { sb.append("DOUBLE "); types.add(DataType.NUMBER.ordinal()); } else if (type.equals("DATE")) { sb.append("DATE "); types.add(DataType.DATE.ordinal()); } else if (type.equals("TIME")) { sb.append("VARCHAR(16) "); types.add(DataType.TIME.ordinal()); } else { System.err.println("Unhandled Type[" + type + "]"); } sb.append(nullOK.equals("YES") ? "DEFAULT NULL," : ","); sb.append("\n"); inx++; } sb.setLength(sb.length() - 2); sb.append(") ENGINE=MyISAM DEFAULT CHARSET=utf8;"); } SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); int rowCnt = 0; try { String stmtStr = "INSERT INTO mex (" + pStmtStr + ") VALUES(" + ques + ")"; System.out.println(stmtStr); try { stmt.executeUpdate("DROP TABLE mex"); } catch (SQLException e) { } System.err.println(sb.toString()); stmt.executeUpdate(sb.toString()); PreparedStatement pStmt = conn.prepareStatement(stmtStr); csv.close(); csvObj = new CsvReader(new FileInputStream(config.getFile()), config.getDelimiter(), config.getCharset()); csvObj.readRecord(); // skip header int row = 0; while (csvObj.readRecord()) { row++; for (int col = 0; col < csvObj.getColumnCount(); col++) { String dataStr = csvObj.get(col); strLen = dataStr.length(); switch (types.get(inx)) { case 3: case 0: { if (strLen > 0) { if (strLen <= fieldLengths[inx]) { pStmt.setString(col, dataStr); } else { System.err.println(String.format("The data for `%s` (%d) is too big %d", names.get(inx), fieldLengths[inx], strLen)); pStmt.setString(col, null); } } else { pStmt.setString(col, null); } } break; case 1: { if (StringUtils.isNotEmpty(dataStr)) { if (StringUtils.isNumeric(dataStr)) { pStmt.setDouble(col, strLen > 0 ? Double.parseDouble(dataStr) : null); } else { System.err.println(col + " Bad Number[" + dataStr + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case 2: { try { if (StringUtils.isNotEmpty(dataStr)) { if (StringUtils.contains(dataStr, "/")) { dataStr = StringUtils.replace(dataStr, "/", "-"); } else if (StringUtils.contains(dataStr, " ")) { dataStr = StringUtils.replace(dataStr, " ", "-"); } pStmt.setDate(col, strLen > 0 ? new java.sql.Date(sdf.parse(dataStr).getTime()) : null); } else { pStmt.setDate(col, null); } } catch (Exception ex) { System.err.println(col + " Bad Date[" + dataStr + "]\n" + str); pStmt.setDate(col, null); } } break; default: { System.err.println("Error - " + types.get(inx)); } } inx++; col++; } pStmt.execute(); row++; } } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (Exception e) { System.err.println("Row: " + rowCnt); System.err.println(str); System.err.println(inx + " " + fieldLengths[inx] + " - Field Len: " + strLen); e.printStackTrace(); } finally { if (csvObj != null) { csvObj.close(); } } } catch (Exception ex) { ex.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
From source file:assignment3.Populate.java
CreateUser() {
try {//from w w w . j av a 2s . com
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("JDBC Driver Missing");
e.printStackTrace();
return;
}
System.out.println("Oracle JDBC Driver Connected");
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "MAYUR", "123456");
conn.setAutoCommit(false);
PreparedStatement ps = conn
.prepareStatement("insert into yelp_user values (?, ?, ?,?, ?, ?,?, ?, ?,?,?)");
JSONParser parser = new JSONParser();
Object obj = parser.parse(new BufferedReader(new FileReader(
"C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\yelp_user.json")));
//Object obj = parser.parse(new BufferedReader(new FileReader("C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\yelp_user1.json")));
JSONArray jsonArray = (JSONArray) (obj);
// JSONArray jsonArray = (JSONArray)(obj1);
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject t = (JSONObject) jsonArray.get(i);
String c = t.get("yelping_since").toString();
Date yelping_since = (Date) java.sql.Date.valueOf(c + "-01");
JSONObject votes = (JSONObject) t.get("votes"); // get all votes details
Long votes_funny = (Long) votes.get("funny");
Long votes_useful = (Long) votes.get("useful");
Long votes_cool = (Long) votes.get("cool");
Long review_count = (Long) t.get("review_count");
String name = t.get("name").toString();
String user_id = t.get("user_id").toString();
JSONArray friends = (JSONArray) (t).get("friends");
int numfriends = 0;
if (friends != null) {
Iterator<String> iterator = friends.iterator();
ArrayList<String> friendid_list = new ArrayList<String>();
while (iterator.hasNext()) {
friendid_list.add(iterator.next());
}
if (friendid_list != null)
numfriends = friendid_list.size();
friendid_list = null;
iterator = null;
}
Long fans = (Long) t.get("fans");
double average_stars = (double) t.get("average_stars");
String type = t.get("type").toString();
ps.setDate(1, yelping_since);
ps.setLong(2, votes_funny);
ps.setLong(3, votes_useful);
ps.setLong(4, votes_cool);
ps.setLong(5, review_count);
ps.setString(6, name);
ps.setString(7, user_id);
ps.setLong(8, fans);
ps.setDouble(9, average_stars);
ps.setString(10, type);
ps.setInt(11, numfriends);
ps.executeUpdate();
System.out.println("Record inserted " + i);
}
conn.commit();
ps.close();
} catch (Exception e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
From source file:gemlite.core.internal.db.AsyncEventHelper.java
/** * Set column value at given index in a prepared statement. The implementation * tries using the matching underlying type to minimize any data type * conversions, and avoid creating wrapper Java objects (e.g. {@link Integer} * for primitive int)./* w w w .ja v a 2 s. c o m*/ * * @param type * the SQL type of the column as specified by JDBC {@link Types} * class * @param ps * the prepared statement where the column value has to be set * @param row * the source row as a {@link ResultSet} from where the value has to * be extracted * @param rowPosition * the 1-based position of the column in the provided * <code>row</code> * @param paramIndex * the 1-based position of the column in the target prepared * statement (provided <code>ps</code> argument) * @param sync * the {@link DBSynchronizer} object, if any; it is used to store * whether the current driver is JDBC4 compliant to enable performing * BLOB/CLOB operations {@link PreparedStatement#setBinaryStream}, * {@link PreparedStatement#setCharacterStream} * * @throws SQLException * in case of an exception in setting parameters */ public final void setColumnInPrepStatement(String type, Object val, PreparedStatement ps, final DBSynchronizer sync, int paramIndex) throws SQLException { switch (type) { case JavaTypes.STRING: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.VARCHAR); else { final String realVal = (String) val; ps.setString(paramIndex, realVal); } break; case JavaTypes.INT1: case JavaTypes.INT2: case JavaTypes.INT3: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.INTEGER); else { final int realVal = (int) val; ps.setInt(paramIndex, realVal); } break; case JavaTypes.DOUBLE1: case JavaTypes.DOUBLE2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DOUBLE); else { final double realVal = (double) val; ps.setDouble(paramIndex, realVal); } break; case JavaTypes.FLOAT1: case JavaTypes.FLOAT2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.FLOAT); else { final float realVal = (float) val; ps.setDouble(paramIndex, realVal); } break; case JavaTypes.BOOLEAN1: case JavaTypes.BOOLEAN2: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.BOOLEAN); else { final boolean realVal = (boolean) val; ps.setBoolean(paramIndex, realVal); } break; case JavaTypes.DATE_SQL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DATE); else { final Date realVal = (Date) val; ps.setDate(paramIndex, realVal); } break; case JavaTypes.DATE_UTIL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DATE); else { final java.util.Date realVal = (java.util.Date) val; ps.setDate(paramIndex, new Date(realVal.getTime())); } break; case JavaTypes.BIGDECIMAL: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.DECIMAL); else { final BigDecimal realVal = (BigDecimal) val; ps.setBigDecimal(paramIndex, realVal); } break; case JavaTypes.TIME: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.TIME); else { final Time realVal = (Time) val; ps.setTime(paramIndex, realVal); } break; case JavaTypes.TIMESTAMP: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.TIMESTAMP); else { final Timestamp realVal = (Timestamp) val; ps.setTimestamp(paramIndex, realVal); } break; case JavaTypes.OBJECT: if (val == null || StringUtils.isEmpty(val.toString())) ps.setNull(paramIndex, Types.JAVA_OBJECT); else { final Object realVal = (Object) val; ps.setObject(paramIndex, realVal); } break; default: throw new UnsupportedOperationException("java.sql.Type = " + type + " not supported"); } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private void setDateValue(int queryType, String paramName, String value, String paramType, PreparedStatement sqlQuery, int i) throws SQLException, DataServiceFault { Date val = null; if (value != null) { val = DBUtils.getDate(value); }// www. j a v a2 s .c o m try { if (QueryTypes.IN.equals(paramType)) { if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) { if (value == null) { sqlQuery.setNull(i + 1, java.sql.Types.DATE); } else { sqlQuery.setDate(i + 1, val); } } else { if (value == null) { ((CallableStatement) sqlQuery).setNull(i + 1, java.sql.Types.DATE); } else { ((CallableStatement) sqlQuery).setDate(i + 1, val); } } } else if (QueryTypes.INOUT.equals(paramType)) { if (value == null) { ((CallableStatement) sqlQuery).setNull(i + 1, java.sql.Types.DATE); } else { ((CallableStatement) sqlQuery).setDate(i + 1, val); } ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DATE); } else { ((CallableStatement) sqlQuery).registerOutParameter(i + 1, java.sql.Types.DATE); } } catch (IllegalArgumentException e) { throw new DataServiceFault(e, "Incorrect date format for parameter : " + paramName + ". Date should be in yyyy-mm-dd format."); } }
From source file:com.flexoodb.engines.FlexJAXBMappedDBDataEngine.java
private void updatePreparedStatement(String tablename, Hashtable<String, Object[]> fieldswithcontent, PreparedStatement ps) throws Exception { Enumeration en = fieldswithcontent.keys(); int i = 0;//from w w w .java 2 s.co m while (en.hasMoreElements()) { i++; try { String field = (String) en.nextElement(); Object[] o2 = fieldswithcontent.get(field); String type = (String) o2[0]; Object o = o2[1]; //System.out.println(field+" "+type+" "+o); if (type.equals("string")) { ps.setString(i, (String) o); } else if (type.equals("byte[]") || type.equals("base64Binary")) { ps.setBinaryStream(i, new ByteArrayInputStream((byte[]) o)); } else if (type.equals("dateTime")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d = null; if (cal.toString().indexOf("0003-11-30") > -1) { ps.setString(i, "0000-00-00 00:00:00"); } else { d = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setTimestamp(i, new java.sql.Timestamp(d.getTime())); } } else if (type.equals("date")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString()); ps.setDate(i, java.sql.Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(d1))); } else if (type.equals("time")) { XMLGregorianCalendar cal = (XMLGregorianCalendar) o; String c = cal.toString(); c = c.replaceFirst("0003-11-30", "0000-00-00"); Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(c); ps.setTime(i, new java.sql.Time(d1.getTime())); } else if (type.equals("integer")) { ps.setInt(i, ((BigInteger) o).intValue()); } else if (type.equals("double")) { ps.setDouble(i, (Double) o); } else if (type.equals("float")) { ps.setFloat(i, (Float) o); } else if (type.equals("long")) { ps.setLong(i, (Long) o); } else { throw new Exception("unknown type [" + type + "] for field [" + field + "] encountered while trying to update table [" + tablename + "]."); } } catch (Exception e) { e.printStackTrace(); throw e; } } }
From source file:org.apache.phoenix.end2end.DateTimeIT.java
private String initAtable() throws SQLException { String tableName = generateUniqueName(); ensureTableCreated(getUrl(), tableName, ATABLE_NAME, (byte[][]) null, null); PreparedStatement stmt = conn.prepareStatement("upsert into " + tableName + "(" + " ORGANIZATION_ID, " + " ENTITY_ID, " + " A_STRING, " + " B_STRING, " + " A_INTEGER, " + " A_DATE, " + " X_DECIMAL, " + " X_LONG, " + " X_INTEGER," + " Y_INTEGER," + " A_BYTE," + " A_SHORT," + " A_FLOAT," + " A_DOUBLE," + " A_UNSIGNED_FLOAT," + " A_UNSIGNED_DOUBLE)" + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); stmt.setString(1, tenantId);/*from w w w .j a v a 2 s . c o m*/ stmt.setString(2, ROW1); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 1); stmt.setDate(6, date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 1); stmt.setShort(12, (short) 128); stmt.setFloat(13, 0.01f); stmt.setDouble(14, 0.0001); stmt.setFloat(15, 0.01f); stmt.setDouble(16, 0.0001); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW2); stmt.setString(3, A_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 2); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 2); stmt.setShort(12, (short) 129); stmt.setFloat(13, 0.02f); stmt.setDouble(14, 0.0002); stmt.setFloat(15, 0.02f); stmt.setDouble(16, 0.0002); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW3); stmt.setString(3, A_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 3); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 3); stmt.setShort(12, (short) 130); stmt.setFloat(13, 0.03f); stmt.setDouble(14, 0.0003); stmt.setFloat(15, 0.03f); stmt.setDouble(16, 0.0003); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW4); stmt.setString(3, A_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 4); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 4); stmt.setShort(12, (short) 131); stmt.setFloat(13, 0.04f); stmt.setDouble(14, 0.0004); stmt.setFloat(15, 0.04f); stmt.setDouble(16, 0.0004); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW5); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 5); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 5); stmt.setShort(12, (short) 132); stmt.setFloat(13, 0.05f); stmt.setDouble(14, 0.0005); stmt.setFloat(15, 0.05f); stmt.setDouble(16, 0.0005); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW6); stmt.setString(3, B_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 6); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, null); stmt.setNull(8, Types.BIGINT); stmt.setNull(9, Types.INTEGER); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 6); stmt.setShort(12, (short) 133); stmt.setFloat(13, 0.06f); stmt.setDouble(14, 0.0006); stmt.setFloat(15, 0.06f); stmt.setDouble(16, 0.0006); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW7); stmt.setString(3, B_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 7); stmt.setDate(6, date == null ? null : date); stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); stmt.setLong(8, 5L); stmt.setInt(9, 5); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 7); stmt.setShort(12, (short) 134); stmt.setFloat(13, 0.07f); stmt.setDouble(14, 0.0007); stmt.setFloat(15, 0.07f); stmt.setDouble(16, 0.0007); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW8); stmt.setString(3, B_VALUE); stmt.setString(4, C_VALUE); stmt.setInt(5, 8); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.9)); long l = Integer.MIN_VALUE - 1L; assert (l < Integer.MIN_VALUE); stmt.setLong(8, l); stmt.setInt(9, 4); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 8); stmt.setShort(12, (short) 135); stmt.setFloat(13, 0.08f); stmt.setDouble(14, 0.0008); stmt.setFloat(15, 0.08f); stmt.setDouble(16, 0.0008); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW9); stmt.setString(3, C_VALUE); stmt.setString(4, E_VALUE); stmt.setInt(5, 9); stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); stmt.setBigDecimal(7, BigDecimal.valueOf(3.3)); l = Integer.MAX_VALUE + 1L; assert (l > Integer.MAX_VALUE); stmt.setLong(8, l); stmt.setInt(9, 3); stmt.setInt(10, 300); stmt.setByte(11, (byte) 9); stmt.setShort(12, (short) 0); stmt.setFloat(13, 0.09f); stmt.setDouble(14, 0.0009); stmt.setFloat(15, 0.09f); stmt.setDouble(16, 0.0009); stmt.execute(); stmt.setString(1, tenantId); stmt.setString(2, ROW10); stmt.setString(3, B_VALUE); stmt.setString(4, B_VALUE); stmt.setInt(5, 7); // Intentionally null stmt.setDate(6, null); stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); stmt.setLong(8, 5L); stmt.setInt(9, 5); stmt.setNull(10, Types.INTEGER); stmt.setByte(11, (byte) 7); stmt.setShort(12, (short) 134); stmt.setFloat(13, 0.07f); stmt.setDouble(14, 0.0007); stmt.setFloat(15, 0.07f); stmt.setDouble(16, 0.0007); stmt.execute(); conn.commit(); return tableName; }
From source file:oscar.form.FrmONAREnhancedRecord.java
int addRecord(Properties props, String table, List<String> namesA, Integer id) throws SQLException { StringBuilder sb = new StringBuilder(); sb.append("INSERT INTO " + table + " ("); for (String name : namesA) { sb.append(name.split("\\|")[0] + ","); }/* w w w . j a v a 2 s . c o m*/ sb.deleteCharAt(sb.length() - 1); sb.append(") VALUES ("); for (String name : namesA) { sb.append("?,"); } sb.deleteCharAt(sb.length() - 1); sb.append(");"); PreparedStatement preparedStmt = null; try { preparedStmt = DbConnectionFilter.getThreadLocalDbConnection().prepareStatement(sb.toString(), Statement.RETURN_GENERATED_KEYS); for (int x = 0; x < namesA.size(); x++) { String t = namesA.get(x); String theName = t.split("\\|")[0]; String type = t.split("\\|")[1]; if (theName.equals("ID")) { if (id == null) { preparedStmt.setNull(x + 1, Types.INTEGER); } else { preparedStmt.setInt(x + 1, id.intValue()); } continue; } if (type.equals("VARCHAR") || type.equals("CHAR")) { String value = props.getProperty(theName); if (value == null) { preparedStmt.setNull(x + 1, getType(type)); } else { preparedStmt.setString(x + 1, value); } } else if (type.equals("INT") || type.equals("TINYINT")) { String value = props.getProperty(theName); if (value != null && value.isEmpty()) { MiscUtils.getLogger().info("empty value for " + theName); } if (value == null || value.isEmpty()) { value = "0"; } else if (value.equalsIgnoreCase("on") || value.equalsIgnoreCase("checked='checked'")) { value = "1"; } preparedStmt.setInt(x + 1, Integer.parseInt(value)); } else if (type.equals("DATE")) { String value = props.getProperty(theName); Date d = null; if (theName.equalsIgnoreCase("formEdited")) { d = new Date(); } else { if ((value == null) || (value.indexOf('/') != -1)) d = UtilDateUtilities.StringToDate(value, dateFormat); else d = UtilDateUtilities.StringToDate(value, _newDateFormat); } if (d == null) preparedStmt.setNull(x + 1, Types.DATE); else preparedStmt.setDate(x + 1, new java.sql.Date(d.getTime())); } else if (type.equals("TIMESTAMP")) { Date d; if (theName.equalsIgnoreCase("formEdited")) { d = new Date(); } else { d = UtilDateUtilities.StringToDate(props.getProperty(theName), "yyyyMMddHHmmss"); } if (d == null) preparedStmt.setNull(x + 1, Types.TIMESTAMP); else preparedStmt.setTimestamp(x + 1, new java.sql.Timestamp(d.getTime())); } else { MiscUtils.getLogger().error("missing type handler for this column " + theName, new Exception()); } } preparedStmt.executeUpdate(); if (id == null) { ResultSet rs = null; try { rs = preparedStmt.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } finally { if (rs != null) rs.close(); } } } finally { if (preparedStmt != null) { preparedStmt.close(); } } return id; }
From source file:org.apache.kylin.rest.service.QueryService.java
/** * @param preparedState// w ww .j a v a 2 s. c om * @param param * @throws SQLException */ private void setParam(PreparedStatement preparedState, int index, PrepareSqlRequest.StateParam param) throws SQLException { boolean isNull = (null == param.getValue()); Class<?> clazz; try { clazz = Class.forName(param.getClassName()); } catch (ClassNotFoundException e) { throw new InternalErrorException(e); } Rep rep = Rep.of(clazz); switch (rep) { case PRIMITIVE_CHAR: case CHARACTER: case STRING: preparedState.setString(index, isNull ? null : String.valueOf(param.getValue())); break; case PRIMITIVE_INT: case INTEGER: preparedState.setInt(index, isNull ? 0 : Integer.valueOf(param.getValue())); break; case PRIMITIVE_SHORT: case SHORT: preparedState.setShort(index, isNull ? 0 : Short.valueOf(param.getValue())); break; case PRIMITIVE_LONG: case LONG: preparedState.setLong(index, isNull ? 0 : Long.valueOf(param.getValue())); break; case PRIMITIVE_FLOAT: case FLOAT: preparedState.setFloat(index, isNull ? 0 : Float.valueOf(param.getValue())); break; case PRIMITIVE_DOUBLE: case DOUBLE: preparedState.setDouble(index, isNull ? 0 : Double.valueOf(param.getValue())); break; case PRIMITIVE_BOOLEAN: case BOOLEAN: preparedState.setBoolean(index, !isNull && Boolean.parseBoolean(param.getValue())); break; case PRIMITIVE_BYTE: case BYTE: preparedState.setByte(index, isNull ? 0 : Byte.valueOf(param.getValue())); break; case JAVA_UTIL_DATE: case JAVA_SQL_DATE: preparedState.setDate(index, isNull ? null : java.sql.Date.valueOf(param.getValue())); break; case JAVA_SQL_TIME: preparedState.setTime(index, isNull ? null : Time.valueOf(param.getValue())); break; case JAVA_SQL_TIMESTAMP: preparedState.setTimestamp(index, isNull ? null : Timestamp.valueOf(param.getValue())); break; default: preparedState.setObject(index, isNull ? null : param.getValue()); } }
From source file:assignment3.Populate.java
CreateReview() {
try {/*from w w w .j a v a 2 s . c o m*/
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("JDBC Driver Missing");
e.printStackTrace();
return;
}
System.out.println("Oracle JDBC Driver Connected");
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "MAYUR", "123456");
JSONParser parser = new JSONParser();
BufferedReader br = new BufferedReader(new FileReader(
"C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\yelp_review.json"));
// BufferedReader br = new BufferedReader(new FileReader("C:\\Users\\mayur\\Downloads\\YelpDataset\\YelpDataset-CptS451\\reviewshortfile.json"));
int batch = 250;
int count = 0;
String sCurrentLine = "";
String temp = "";
while ((sCurrentLine = br.readLine()) != null) {
conn.setAutoCommit(false);
Object obj = parser.parse(sCurrentLine);
JSONObject t = (JSONObject) obj;
JSONObject votes = (JSONObject) t.get("votes"); // get all votes details
Long votes_funny = (Long) votes.get("funny");
Long votes_useful = (Long) votes.get("useful");
Long votes_cool = (Long) votes.get("cool");
String user_id = t.get("user_id").toString();
String review_id = t.get("review_id").toString();
Long stars = (Long) t.get("stars");
String c = t.get("date").toString();
Date review_date = (Date) java.sql.Date.valueOf(c);
String text = t.get("text").toString();
if (text.length() > 1600) {
temp = text.substring(0, 1600);
text = "";
text = temp;
System.out.println("String truncated " + text.length() + "at record" + count);
temp = "";
}
String type = t.get("type").toString();
String business_id = t.get("business_id").toString();
ps = conn.prepareStatement("insert into review values (?, ?, ?,?, ?, ?,?, ?, ?,?)");
ps.setLong(1, votes_funny);
ps.setLong(2, votes_useful);
ps.setLong(3, votes_cool);
ps.setString(4, user_id);
ps.setString(5, review_id);
ps.setLong(6, stars);
ps.setDate(7, review_date);
ps.setString(8, text);
ps.setString(9, type);
ps.setString(10, business_id);
ps.executeUpdate();
count++;
System.out.println("Record Number :" + count);
if (count % batch == 0) {
conn.commit();
ps.close();
conn.close();
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "MAYUR", "123456");
}
}
System.out.println("Record inserted Finally in Review Table " + count);
conn.commit();
ps.close();
conn.close();
} catch (Exception e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
} finally {
try {
ps.close();
conn.close();
} catch (Exception e) {
}
}
}