List of usage examples for java.sql Types NULL
int NULL
To view the source code for java.sql Types NULL.
Click Source Link
The constant in the Java programming language that identifies the generic SQL value NULL.
From source file:jp.co.tis.gsp.tools.dba.dialect.MysqlDialect.java
@Override public void setObjectInStmt(PreparedStatement stmt, int parameterIndex, String value, int sqlType) throws SQLException { if (sqlType == UN_USABLE_TYPE) { stmt.setNull(parameterIndex, Types.NULL); } else if (StringUtil.isBlank(value) || "".equals(value)) { stmt.setNull(parameterIndex, sqlType); } else if (sqlType == Types.TIMESTAMP) { stmt.setTimestamp(parameterIndex, Timestamp.valueOf(value)); } else {// w w w . ja v a 2s .c o m stmt.setObject(parameterIndex, value, sqlType); } }
From source file:org.apache.jmeter.protocol.jdbc.AbstractJDBCTestElement.java
private String resultSetsToString(PreparedStatement pstmt, boolean result, int[] out) throws SQLException, UnsupportedEncodingException { StringBuilder sb = new StringBuilder(); int updateCount = 0; if (!result) { updateCount = pstmt.getUpdateCount(); }//from www.ja v a2 s .c om do { if (result) { ResultSet rs = null; try { rs = pstmt.getResultSet(); sb.append(getStringFromResultSet(rs)).append("\n"); // $NON-NLS-1$ } finally { close(rs); } } else { sb.append(updateCount).append(" updates.\n"); } result = pstmt.getMoreResults(); if (!result) { updateCount = pstmt.getUpdateCount(); } } while (result || (updateCount != -1)); if (out != null && pstmt instanceof CallableStatement) { List<Object> outputValues = new ArrayList<>(); CallableStatement cs = (CallableStatement) pstmt; sb.append("Output variables by position:\n"); for (int i = 0; i < out.length; i++) { if (out[i] != java.sql.Types.NULL) { Object o = cs.getObject(i + 1); outputValues.add(o); sb.append("["); sb.append(i + 1); sb.append("] "); sb.append(o); if (o instanceof java.sql.ResultSet && RS_COUNT_RECORDS.equals(resultSetHandler)) { sb.append(" ").append(countRows((ResultSet) o)).append(" rows"); } sb.append("\n"); } } String[] varnames = getVariableNames().split(COMMA); if (varnames.length > 0) { JMeterVariables jmvars = getThreadContext().getVariables(); for (int i = 0; i < varnames.length && i < outputValues.size(); i++) { String name = varnames[i].trim(); if (name.length() > 0) { // Save the value in the variable if present Object o = outputValues.get(i); if (o instanceof java.sql.ResultSet) { ResultSet resultSet = (ResultSet) o; if (RS_STORE_AS_OBJECT.equals(resultSetHandler)) { jmvars.putObject(name, o); } else if (RS_COUNT_RECORDS.equals(resultSetHandler)) { jmvars.put(name, o.toString() + " " + countRows(resultSet) + " rows"); } else { jmvars.put(name, o.toString()); } } else { jmvars.put(name, o == null ? null : o.toString()); } } } } } return sb.toString(); }
From source file:com.emr.utilities.CSVLoader.java
/** * Parse CSV file using OpenCSV library and load in * given database table. //from w w w . j a va 2 s. co m * @param csvFile {@link String} Input CSV file * @param tableName {@link String} Database table name to import data * @param truncateBeforeLoad {@link boolean} Truncate the table before inserting * new records. * @param destinationColumns {@link String[]} Array containing the destination columns */ public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad, String[] destinationColumns, List columnsToBeMapped) throws Exception { CSVReader csvReader = null; if (null == this.connection) { throw new Exception("Not a valid connection."); } try { csvReader = new CSVReader(new FileReader(csvFile), this.seprator); } catch (Exception e) { String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e); JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace, "File Error", JOptionPane.ERROR_MESSAGE); throw new Exception("Error occured while executing file. " + stacktrace); } String[] headerRow = csvReader.readNext(); if (null == headerRow) { throw new FileNotFoundException( "No columns defined in given CSV file." + "Please check the CSV file format."); } //Get indices of columns to be mapped List mapColumnsIndices = new ArrayList(); for (Object o : columnsToBeMapped) { String column = (String) o; column = column.substring(column.lastIndexOf(".") + 1, column.length()); int i; for (i = 0; i < headerRow.length; i++) { if (headerRow[i].equals(column)) { mapColumnsIndices.add(i); } } } String questionmarks = StringUtils.repeat("?,", headerRow.length); questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1); String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName); query = query.replaceFirst(KEYS_REGEX, StringUtils.join(destinationColumns, ",")); query = query.replaceFirst(VALUES_REGEX, questionmarks); String log_query = query.substring(0, query.indexOf("VALUES(")); String[] nextLine; Connection con = null; PreparedStatement ps = null; PreparedStatement ps2 = null; PreparedStatement reader = null; ResultSet rs = null; try { con = this.connection; con.setAutoCommit(false); ps = con.prepareStatement(query); File file = new File("sqlite/db"); if (!file.exists()) { file.createNewFile(); } db = new SQLiteConnection(file); db.open(true); //if destination table==person, also add an entry in the table person_identifier //get column indices for the person_id and uuid columns int person_id_column_index = -1; int uuid_column_index = -1; int maxLength = 100; int firstname_index = -1; int middlename_index = -1; int lastname_index = -1; int clanname_index = -1; int othername_index = -1; if (tableName.equals("person")) { int i; ps2 = con.prepareStatement( "insert ignore into person_identifier(person_id,identifier_type_id,identifier) values(?,?,?)"); for (i = 0; i < headerRow.length; i++) { if (headerRow[i].equals("person_id")) { person_id_column_index = i; } if (headerRow[i].equals("uuid")) { uuid_column_index = i; } /*if(headerRow[i].equals("first_name")){ System.out.println("Found firstname index: " + i); firstname_index=i; } if(headerRow[i].equals("middle_name")){ System.out.println("Found firstname index: " + i); middlename_index=i; } if(headerRow[i].equals("last_name")){ System.out.println("Found firstname index: " + i); lastname_index=i; } if(headerRow[i].equals("clan_name")){ System.out.println("Found firstname index: " + i); clanname_index=i; } if(headerRow[i].equals("other_name")){ System.out.println("Found firstname index: " + i); othername_index=i; }*/ } } if (truncateBeforeLoad) { //delete data from table before loading csv try (Statement stmnt = con.createStatement()) { stmnt.execute("DELETE FROM " + tableName); stmnt.close(); } } if (tableName.equals("person")) { try (Statement stmt2 = con.createStatement()) { stmt2.execute( "ALTER TABLE person CHANGE COLUMN first_name first_name VARCHAR(50) NULL DEFAULT NULL AFTER person_guid,CHANGE COLUMN middle_name middle_name VARCHAR(50) NULL DEFAULT NULL AFTER first_name,CHANGE COLUMN last_name last_name VARCHAR(50) NULL DEFAULT NULL AFTER middle_name;"); stmt2.close(); } } final int batchSize = 1000; int count = 0; Date date = null; while ((nextLine = csvReader.readNext()) != null) { if (null != nextLine) { int index = 1; int person_id = -1; String uuid = ""; int identifier_type_id = 3; if (tableName.equals("person")) { reader = con.prepareStatement( "select identifier_type_id from identifier_type where identifier_type_name='UUID'"); rs = reader.executeQuery(); if (!rs.isBeforeFirst()) { //no uuid row //insert it Integer numero = 0; Statement stmt = con.createStatement(); numero = stmt.executeUpdate( "insert into identifier_type(identifier_type_id,identifier_type_name) values(50,'UUID')", Statement.RETURN_GENERATED_KEYS); ResultSet rs2 = stmt.getGeneratedKeys(); if (rs2.next()) { identifier_type_id = rs2.getInt(1); } rs2.close(); stmt.close(); } else { while (rs.next()) { identifier_type_id = rs.getInt("identifier_type_id"); } } } int counter = 1; String temp_log = log_query + "VALUES("; //string to be logged for (String string : nextLine) { //if current index is in the list of columns to be mapped, we apply that mapping for (Object o : mapColumnsIndices) { int i = (int) o; if (index == (i + 1)) { //apply mapping to this column string = applyDataMapping(string); } } if (tableName.equals("person")) { //get person_id and uuid if (index == (person_id_column_index + 1)) { person_id = Integer.parseInt(string); } if (index == (uuid_column_index + 1)) { uuid = string; } } //check if string is a date if (string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4} \\d{2}:\\d{2}:\\d{2}") || string.matches("\\d{2}-[a-zA-Z]{3}-\\d{4}")) { java.sql.Date dt = formatDate(string); temp_log = temp_log + "'" + dt.toString() + "'"; ps.setDate(index++, dt); } else { if ("".equals(string)) { temp_log = temp_log + "''"; ps.setNull(index++, Types.NULL); } else { temp_log = temp_log + "'" + string + "'"; ps.setString(index++, string); } } if (counter < headerRow.length) { temp_log = temp_log + ","; } else { temp_log = temp_log + ");"; System.out.println(temp_log); } counter++; } if (tableName.equals("person")) { if (!"".equals(uuid) && person_id != -1) { ps2.setInt(1, person_id); ps2.setInt(2, identifier_type_id); ps2.setString(3, uuid); ps2.addBatch(); } } ps.addBatch(); } if (++count % batchSize == 0) { ps.executeBatch(); if (tableName.equals("person")) { ps2.executeBatch(); } } } ps.executeBatch(); // insert remaining records if (tableName.equals("person")) { ps2.executeBatch(); } con.commit(); } catch (Exception e) { if (con != null) con.rollback(); if (db != null) db.dispose(); String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e); JOptionPane.showMessageDialog(null, "Error occured while executing file. Error Details: " + stacktrace, "File Error", JOptionPane.ERROR_MESSAGE); throw new Exception("Error occured while executing file. " + stacktrace); } finally { if (null != reader) reader.close(); if (null != ps) ps.close(); if (null != ps2) ps2.close(); if (null != con) con.close(); csvReader.close(); } }
From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java
/** * Set the specified PreparedStatement parameter to null, * respecting database-specific peculiarities. *//* w w w . j a v a 2s .c om*/ private static void setNull(PreparedStatement ps, int paramIndex, int sqlType, String typeName) throws SQLException { if (sqlType == SqlTypeValue.TYPE_UNKNOWN) { boolean useSetObject = false; Integer sqlTypeToUse = null; DatabaseMetaData dbmd = null; String jdbcDriverName = null; boolean checkGetParameterType = !shouldIgnoreGetParameterType; if (checkGetParameterType && !driversWithNoSupportForGetParameterType.isEmpty()) { try { dbmd = ps.getConnection().getMetaData(); jdbcDriverName = dbmd.getDriverName(); checkGetParameterType = !driversWithNoSupportForGetParameterType.contains(jdbcDriverName); } catch (Throwable ex) { logger.debug("Could not check connection metadata", ex); } } if (checkGetParameterType) { try { sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex); } catch (Throwable ex) { if (logger.isDebugEnabled()) { logger.debug( "JDBC 3.0 getParameterType call not supported - using fallback method instead: " + ex); } } } if (sqlTypeToUse == null) { // JDBC driver not compliant with JDBC 3.0 -> proceed with database-specific checks sqlTypeToUse = Types.NULL; try { if (dbmd == null) { dbmd = ps.getConnection().getMetaData(); } if (jdbcDriverName == null) { jdbcDriverName = dbmd.getDriverName(); } if (checkGetParameterType) { driversWithNoSupportForGetParameterType.add(jdbcDriverName); } String databaseProductName = dbmd.getDatabaseProductName(); if (databaseProductName.startsWith("Informix") || jdbcDriverName.startsWith("Microsoft SQL Server")) { useSetObject = true; } else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect") || jdbcDriverName.startsWith("SQLServer") || jdbcDriverName.startsWith("Apache Derby")) { sqlTypeToUse = Types.VARCHAR; } } catch (Throwable ex) { logger.debug("Could not check connection metadata", ex); } } if (useSetObject) { ps.setObject(paramIndex, null); } else { ps.setNull(paramIndex, sqlTypeToUse); } } else if (typeName != null) { ps.setNull(paramIndex, sqlType, typeName); } else { ps.setNull(paramIndex, sqlType); } }
From source file:com.squid.core.domain.operators.ExtendedType.java
private String getTypeName(int SQLType) { switch (SQLType) { case Types.ARRAY: return "ARRAY"; case Types.BIGINT: return "INTEGER"; case Types.BINARY: return "BINARY"; case Types.BIT: return "BIT"; case Types.BLOB: return "BLOB"; case Types.BOOLEAN: return "BOOLEAN"; case Types.CHAR: return "CHAR"; case Types.CLOB: return "CLOB"; case Types.DATALINK: return "DATALINK"; case Types.DATE: return "DATE"; case Types.DECIMAL: return "DECIMAL"; case Types.DOUBLE: return "DOUBLE"; case Types.FLOAT: return "FLOAT"; case Types.INTEGER: return "INTEGER"; case Types.JAVA_OBJECT: return "JAVA_OBJECT"; case Types.LONGNVARCHAR: return "LONGNVARCHAR"; case Types.LONGVARBINARY: return "LONGVARBINARY"; case Types.NCHAR: return "NCHAR"; case Types.NCLOB: return "NCLOB"; case Types.NULL: return "UNDEFINED";// case Types.NUMERIC: return "NUMERIC"; case Types.NVARCHAR: return "NVARCHAR"; case Types.OTHER: return "UNDEFINED";// case Types.REAL: return "REAL"; case Types.REF: return "REF"; case Types.ROWID: return "ROWID"; case Types.SMALLINT: return "SMALLINT"; case Types.SQLXML: return "SQLXML"; case Types.STRUCT: return "STRUCT"; case Types.TIME: return "TIME"; case Types.TIMESTAMP: return "TIMESTAMP"; case Types.TINYINT: return "TINYINT"; case Types.VARBINARY: return "VARBINARY"; case Types.VARCHAR: return "VARCHAR"; default:/*ww w . j av a 2 s .com*/ return "UNDEFINED";// } }
From source file:com.keybox.manage.db.PublicKeyDB.java
/** * inserts new public key//from w w w.j a v a 2 s . c o m * * @param publicKey key object */ public static void insertPublicKey(PublicKey publicKey) { Connection con = null; try { con = DBUtils.getConn(); PreparedStatement stmt = con.prepareStatement( "insert into public_keys(key_nm, type, fingerprint, public_key, profile_id, user_id) values (?,?,?,?,?,?)"); stmt.setString(1, publicKey.getKeyNm()); stmt.setString(2, SSHUtil.getKeyType(publicKey.getPublicKey())); stmt.setString(3, SSHUtil.getFingerprint(publicKey.getPublicKey())); stmt.setString(4, publicKey.getPublicKey().trim()); if (publicKey.getProfile() == null || publicKey.getProfile().getId() == null) { stmt.setNull(5, Types.NULL); } else { stmt.setLong(5, publicKey.getProfile().getId()); } stmt.setLong(6, publicKey.getUserId()); stmt.execute(); DBUtils.closeStmt(stmt); } catch (Exception e) { log.error(e.toString(), e); } DBUtils.closeConn(con); }
From source file:org.ensembl.healthcheck.util.ConnectionBasedSqlTemplateImpl.java
private void bindParamsToPreparedStatement(PreparedStatement st, Object[] arguments) throws SQLException { int i = 0;//from w w w. jav a 2 s . c om if (arguments != null) { for (Object arg : arguments) { i++; if (arg == null) { st.setNull(i, Types.NULL); } else if (arg instanceof String) { st.setString(i, (String) arg); } else if (arg instanceof Integer) { st.setInt(i, (Integer) arg); } else if (arg instanceof Boolean) { st.setBoolean(i, (Boolean) arg); } else if (arg instanceof Short) { st.setShort(i, (Short) arg); } else if (arg instanceof Date) { st.setTimestamp(i, new java.sql.Timestamp(((Date) arg).getTime())); } else if (arg instanceof java.sql.Date) { st.setDate(i, new java.sql.Date(((Date) arg).getTime())); } else if (arg instanceof Double) { st.setDouble(i, (Double) arg); } else if (arg instanceof Long) { st.setLong(i, (Long) arg); } else if (arg instanceof BigDecimal) { st.setObject(i, arg); } else if (arg instanceof BigInteger) { st.setObject(i, arg); } else { // Object try { ByteArrayOutputStream bytesS = new ByteArrayOutputStream(); ObjectOutputStream out = new ObjectOutputStream(bytesS); out.writeObject(arg); out.close(); byte[] bytes = bytesS.toByteArray(); bytesS.close(); st.setBytes(i, bytes); } catch (IOException e) { throw new SQLException( "Could not serialize object " + arg + " for use in a PreparedStatement "); } } } } }
From source file:architecture.ee.web.community.page.dao.jdbc.JdbcPageDao.java
private void insertPageVersion(Page page) { Date now = Calendar.getInstance().getTime(); if (page.getVersionId() > 1) { page.setModifiedDate(now);/* w ww . ja v a 2s .co m*/ } if (page.getPageState() == PageState.PUBLISHED) { // clean up on publish cleanupVersionsOnPublish(page); } // INSERT V2_PAGE_VERSION getExtendedJdbcTemplate().update(getBoundSql("ARCHITECTURE_COMMUNITY.INSERT_PAGE_VERSION").getSql(), new SqlParameterValue(Types.NUMERIC, page.getPageId()), new SqlParameterValue(Types.NUMERIC, page.getVersionId()), new SqlParameterValue(Types.VARCHAR, page.getPageState().name().toLowerCase()), new SqlParameterValue(Types.VARCHAR, page.getTitle()), page.getSummary() == null ? new SqlParameterValue(Types.NULL, null) : new SqlParameterValue(Types.VARCHAR, page.getSummary()), new SqlParameterValue(Types.NUMERIC, page.getVersionId() <= 1 ? page.getUser().getUserId() : SecurityHelper.getUser().getUserId()), new SqlParameterValue(Types.DATE, page.getCreationDate()), new SqlParameterValue(Types.DATE, page.getModifiedDate())); }
From source file:com.keybox.manage.db.PublicKeyDB.java
/** * updates existing public key/*from w w w.j a va 2 s . c o m*/ * * @param publicKey key object */ public static void updatePublicKey(PublicKey publicKey) { Connection con = null; try { con = DBUtils.getConn(); PreparedStatement stmt = con.prepareStatement( "update public_keys set key_nm=?, type=?, fingerprint=?, public_key=?, profile_id=? where id=? and user_id=? and enabled=true"); stmt.setString(1, publicKey.getKeyNm()); stmt.setString(2, SSHUtil.getKeyType(publicKey.getPublicKey())); stmt.setString(3, SSHUtil.getFingerprint(publicKey.getPublicKey())); stmt.setString(4, publicKey.getPublicKey().trim()); if (publicKey.getProfile() == null || publicKey.getProfile().getId() == null) { stmt.setNull(5, Types.NULL); } else { stmt.setLong(5, publicKey.getProfile().getId()); } stmt.setLong(6, publicKey.getId()); stmt.setLong(7, publicKey.getUserId()); stmt.execute(); DBUtils.closeStmt(stmt); } catch (Exception e) { log.error(e.toString(), e); } DBUtils.closeConn(con); }
From source file:org.netflux.core.FieldMetadata.java
/** * Returns a string representation of the provided type. * /* w w w .j av a 2 s . c o m*/ * @param type the type from {@link Types} to transform to a string. * @return a string representation of the provided type. */ public static String typeToString(int type) { switch (type) { case Types.ARRAY: return "ARRAY"; case Types.BIGINT: return "BIGINT"; case Types.BINARY: return "BINARY"; case Types.BIT: return "BIT"; case Types.BLOB: return "BLOB"; case Types.BOOLEAN: return "BOOLEAN"; case Types.CHAR: return "CHAR"; case Types.CLOB: return "CLOB"; case Types.DATALINK: return "DATALINK"; case Types.DATE: return "DATE"; case Types.DECIMAL: return "DECIMAL"; case Types.DISTINCT: return "DISTINCT"; case Types.DOUBLE: return "DOUBLE"; case Types.FLOAT: return "FLOAT"; case Types.INTEGER: return "INTEGER"; case Types.JAVA_OBJECT: return "JAVA_OBJECT"; case Types.LONGVARBINARY: return "LONGVARBINARY"; case Types.LONGVARCHAR: return "LONGVARCHAR"; case Types.NULL: return "NULL"; case Types.NUMERIC: return "NUMERIC"; case Types.OTHER: return "OTHER"; case Types.REAL: return "REAL"; case Types.REF: return "REF"; case Types.SMALLINT: return "SMALLINT"; case Types.STRUCT: return "STRUCT"; case Types.TIME: return "TIME"; case Types.TIMESTAMP: return "TIMESTAMP"; case Types.TINYINT: return "TINYINT"; case Types.VARBINARY: return "VARBINARY"; case Types.VARCHAR: return "VARCHAR"; default: return "*unsupported type*"; } }