@SuppressWarnings("unchecked")
public static final List<Record> build(Config config, ResultSet rs) throws SQLException {
List<Record> result = new ArrayList<Record>();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
String[] labelNames = new String[columnCount + 1];
int[] types = new int[columnCount + 1];
buildLabelNamesAndTypes(rsmd, labelNames, types);
while (rs.next()) {
Record record = new Record();
record.setColumnsMap(config.containerFactory.getColumnsMap());
Map<String, Object> columns = record.getColumns();
for (int i = 1; i <= columnCount; i++) {
Object value;
if (types[i] < Types.BLOB) value = rs.getObject(i);
else if (types[i] == Types.CLOB) value = ModelBuilder.handleClob(rs.getClob(i));
else if (types[i] == Types.NCLOB) value = ModelBuilder.handleClob(rs.getNClob(i));
else if (types[i] == Types.BLOB) value = ModelBuilder.handleBlob(rs.getBlob(i));
else value = rs.getObject(i);
columns.put(labelNames[i], value);
}
result.add(record);
}
return result;
}
private void testUnsupportedOperations() throws Exception {
Connection conn = getConnection();
stat = conn.createStatement();
stat.execute("create table test(id int, c clob, b blob)");
stat.execute("insert into test values(1, 'x', x'00')");
ResultSet rs = stat.executeQuery("select * from test order by id");
rs.next();
Clob clob = rs.getClob(2);
byte[] data = IOUtils.readBytesAndClose(clob.getAsciiStream(), -1);
assertEquals("x", new String(data, "UTF-8"));
assertTrue(clob.toString().endsWith("'x'"));
clob.free();
assertTrue(clob.toString().endsWith("null"));
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).truncate(0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).setAsciiStream(1);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).setString(1, "", 0, 1);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).position("", 0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).position((Clob) null, 0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).getCharacterStream(1, 1);
Blob blob = rs.getBlob(3);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).truncate(0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).setBytes(1, new byte[0], 0, 0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).position(new byte[1], 0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).position((Blob) null, 0);
assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).getBinaryStream(1, 1);
assertTrue(blob.toString().endsWith("X'00'"));
blob.free();
assertTrue(blob.toString().endsWith("null"));
stat.execute("drop table test");
conn.close();
}
private void listUser(ReqCtx ctx, HttpServletResponse response) throws IOException {
try {
// String sessionId = ctx.getArgVal(JSESSIONID_tag);
// HttpSession session = SessionCounterListener.getSession(sessionId);
OutputStream s = response.getOutputStream();
// outstmt = con.prepareStatement("SELECT Password,Status FROM User WHERE MobileNumber = ?");
// PreparedStatement stmt = con.prepareStatement("SELECT * FROM User where Name=?");
PreparedStatement stmt = con.prepareStatement("SELECT * FROM User where Status!='deleted'");
// stmt.setString(1,"First");
ResultSet rs = stmt.executeQuery();
ArrayList<HashMap<String, String>> set = new ArrayList<HashMap<String, String>>();
HashMap<String, String> cur;
log(Level.INFO, "Listing User Start");
while (rs.next()) {
cur = new HashMap<String, String>();
cur.put("userKeyID", rs.getString("UserId"));
cur.put("usrName", rs.getString("Name"));
cur.put("mobNum", rs.getString("MobileNumber"));
cur.put("alias", rs.getString("Alias"));
cur.put("usrEmail", rs.getString("EmailAddress"));
cur.put("address", rs.getString("Address"));
cur.put("notes", rs.getString("AddressVerificationNotes"));
cur.put("remainingCreditPostAudit", rs.getString("RemainingCreditPostAudit"));
cur.put("lastAuditedActivityAt", rs.getString("LastAuditedActivityAt"));
java.sql.Blob bT = rs.getBlob("ImageFile");
String str = getBase64OfBlob(bT);
cur.put("imageFile", str);
cur.put("profilePic", getBase64OfBlob(rs.getBlob("ProfilePic")));
cur.put("idProofFile", getBase64OfBlob(rs.getBlob("IdProofFile")));
cur.put("addressProofFile", getBase64OfBlob(rs.getBlob("AddrProofFile")));
// Getting the MAC address corresponding to the current User Id from the Device table
stmt =
con.prepareStatement(
"SELECT MAC FROM Device where UserId=?", Statement.RETURN_GENERATED_KEYS);
stmt.setInt(1, rs.getInt("UserId"));
ResultSet rs2 = stmt.executeQuery();
rs2.next();
cur.put("custDeviceId", rs2.getString(1));
// o.println("Writing : "+cur);
set.add(cur);
}
log(INFO, "Sending the data of " + set.size() + " users");
byte objBytes[] = SerializationUtils.serialize(set);
s.write(objBytes, 0, objBytes.length);
s.close();
// out.close();
} catch (Exception e) {
log(Level.INFO, "Listing User Failed");
errorResponse(ctx, response, e.getMessage());
log(WARNING, "Exception in listUser of Oprtr: " + e);
log.error("stack trace", e);
}
}
public OIRulesGroup mapRow(ResultSet rs, int rowNum) throws SQLException {
return new OIRulesGroup(
rs.getInt("id"),
blobToString(rs.getBlob("label")),
blobToString(rs.getBlob("description")),
rs.getInt("authorId"),
rs.getBoolean("isUncommitted"),
blobToString(rs.getBlob("username")));
}
public Pipeline mapRow(ResultSet rs, int rowNum) throws SQLException {
return new Pipeline(
rs.getInt("id"),
blobToString(rs.getBlob("label")),
blobToString(rs.getBlob("description")),
rs.getBoolean("isDefault"),
rs.getBoolean("isLocked"),
rs.getInt("authorId"),
blobToString(rs.getBlob("username")));
}
/**
* This method is used to get a song by its id
*
* @param songid The id of the song we want to return
* @return Returns a song if the id exists, or else null if it doesn't exist
*/
@Override
public Song getSongById(int songid) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = getConnection();
String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + SONGID + " = ?";
ps = con.prepareStatement(query);
ps.setInt(1, songid);
rs = ps.executeQuery();
if (rs.next()) {
Blob songDataBlob = rs.getBlob(SONGDATA);
byte songdata[] = null;
if (songDataBlob != null) songdata = songDataBlob.getBytes(1, (int) songDataBlob.length());
Blob artworkBlob = rs.getBlob(ARTWORK);
byte art[] = null;
if (artworkBlob != null) art = artworkBlob.getBytes(1, (int) artworkBlob.length());
return new Song(
rs.getInt(SONGID),
rs.getString(FILENAME),
rs.getString(TITLE),
rs.getString(ARTIST),
rs.getString(ALBUM),
rs.getString(GENRE),
rs.getInt(YEAR),
rs.getInt(DURATION),
rs.getDouble(PRICE),
rs.getString(LICENSE),
rs.getInt(PLAYCOUNT),
rs.getDate(UPLOADDATE),
art,
songdata);
}
} catch (SQLException ex1) {
if (DEBUG) ex1.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (con != null) freeConnection(con);
} catch (SQLException e) {
if (DEBUG) e.printStackTrace();
}
}
return null;
}
public void testBlobA() {
try {
String ddl0 = "DROP TABLE BLOBTEST IF EXISTS";
String ddl1 = "CREATE TABLE BLOBTEST(ID IDENTITY, BLOBFIELD BLOB(1000))";
statement.execute(ddl0);
statement.execute(ddl1);
} catch (SQLException e) {
e.printStackTrace();
}
try {
String dml0 = "insert into blobtest(blobfield) values(?)";
String dql0 = "select * from blobtest;";
PreparedStatement ps = connection.prepareStatement(dml0);
byte[] data = new byte[] {1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
Blob blob = new JDBCBlob(data);
ps.setBlob(1, blob);
ps.executeUpdate();
data[4] = 50;
blob = new JDBCBlob(data);
ps.setBlob(1, blob);
ps.executeUpdate();
ps.close();
ps = connection.prepareStatement(dql0);
ResultSet rs = ps.executeQuery();
rs.next();
Blob blob1 = rs.getBlob(2);
rs.next();
Blob blob2 = rs.getBlob(2);
byte[] data1 = blob1.getBytes(1, 10);
byte[] data2 = blob2.getBytes(1, 10);
assertTrue(data1[4] == 5 && data2[4] == 50);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Object fetchBLOB(ResultSet rs, int column, EOAttribute attribute, boolean materialize)
throws SQLException {
NSData data = null;
Blob blob = rs.getBlob(column);
if (blob == null) {
return null;
}
if (!materialize) {
return blob;
}
InputStream stream = blob.getBinaryStream();
try {
int chunkSize = (int) blob.length();
if (chunkSize == 0) {
data = NSData.EmptyData;
} else {
data = new NSData(stream, chunkSize);
}
} catch (IOException e) {
throw new JDBCAdaptorException(e.getMessage(), null);
} finally {
try {
if (stream != null) stream.close();
} catch (IOException e) {
/* Nothing we can do */
}
;
}
return data;
}
static void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSingle.getInstance().getConnection();
st = conn.createStatement();
rs = st.executeQuery("select text from textdemo");
while (rs.next()) {
Blob blob = rs.getBlob(1);
InputStream in = blob.getBinaryStream();
// reader = rs.getCharacterStream(1);
File file = new File("JdbcUtils_bak.java");
OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024];
for (int i = 0; (i = in.read(buff)) > 0; ) {
out.write(buff, 0, i);
}
out.close();
in.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
public String getValue(int idx) {
String val = "";
try {
int cType = getColumnType(idx);
if (cType == 2004) { // BLOB
val = "BLOB";
Blob blob = rs.getBlob(idx);
if (blob == null) {
val = null;
} else {
val = "BLOB size=" + blob.length();
}
} else val = rs.getString(idx);
} catch (SQLException e) {
val = e.getMessage();
int cType = getColumnType(idx);
System.err.print("Column type: " + cType);
}
if (val != null && val.endsWith(" 00:00:00.0")) val = val.substring(0, val.length() - 11);
return val;
}
//
// Examine BLOBs and CLOBs.
//
private void vetLargeObjects(
Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
throws Exception {
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
stmt.execute(
"INSERT INTO t (id, b, c) VALUES (1, "
+ "CAST ("
+ TestUtil.stringToHexLiteral("101010001101")
+ "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");
ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");
rs.next();
Blob blob = rs.getBlob(2);
Clob clob = rs.getClob(3);
vetObject(blob, unsupportedList, notUnderstoodList);
vetObject(clob, unsupportedList, notUnderstoodList);
stmt.close();
conn.rollback();
}
public String getBlob(int idx) {
String val = "";
try {
int cType = getColumnType(idx);
if (cType == 2004) { // BLOB
val = "BLOB";
Blob blob = rs.getBlob(idx);
if (blob == null) {
val = null;
} else {
byte[] bdata = blob.getBytes(1, (int) blob.length());
val = new String(bdata);
}
} else val = rs.getString(idx);
} catch (SQLException e) {
val = e.getMessage();
int cType = getColumnType(idx);
System.err.print("Column type: " + cType);
}
// if (val != null && val.endsWith(" 00:00:00.0")) val = val.substring(0, val.length()-11);
// System.out.print("BLOB=" + val);
return val;
}
/*
* (non-Javadoc)
* @see org.datanucleus.store.rdbms.mapping.AbstractLargeBinaryRDBMSMapping#getObject(java.lang.Object,
* int)
*/
@Override
public Object getObject(ResultSet rs, int param) {
byte[] bytes = null;
try {
// Retrieve the bytes of the object directly
bytes = rs.getBytes(param);
if (bytes == null) {
return null;
}
} catch (SQLException sqle) {
try {
// Retrieve the bytes using the Blob (if getBytes not supported e.g HSQLDB 2.0)
Blob blob = rs.getBlob(param);
if (blob == null) {
return null;
}
bytes = blob.getBytes(1, (int) blob.length());
if (bytes == null) {
return null;
}
} catch (SQLException sqle2) {
throw new NucleusDataStoreException(
Localiser.msg("055002", "Object", "" + param, column, sqle2.getMessage()), sqle2);
}
}
return getObjectForBytes(bytes, param);
}
/** 读取数据库带图片的一条记录 */
@Test
public void getImage() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
conn = JDBCUtils.getConnection();
try {
stmt = conn.createStatement();
String sql = "select * from bt_user where id=1";
rs = stmt.executeQuery(sql);
if (rs.next()) {
Blob blob = rs.getBlob("headimage");
InputStream is = blob.getBinaryStream();
String path = "D:\\work\\Workspaces\\day14_jdbc\\src\\cn\\itcast\\mysql\\bt\\mm2.jpg";
OutputStream os = new FileOutputStream(path);
byte[] buffer = new byte[1024];
int len = -1;
while ((len = is.read(buffer)) != -1) {
os.write(buffer, 0, len);
}
// os.flush();
os.close(); // close中有flush
is.close();
}
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, stmt, rs);
}
}
@Override
public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
Article a = new Article();
a.setId(rs.getInt("id"));
a.setTitle(rs.getString("title"));
Blob blob = rs.getBlob("content");
int len = (int) blob.length();
byte[] data = blob.getBytes(0, len);
String content = new String(data);
a.setContent(content);
a.setCtime(rs.getTimestamp("ctime"));
a.setUptime(rs.getTimestamp("uptime"));
a.setTags(rs.getString("tags"));
a.setCategory(rs.getString("category"));
a.setAuthorId(rs.getInt("authorId"));
a.setEditorId(rs.getInt("editorId"));
a.setMediaId(rs.getInt("mediaId"));
a.setPics(rs.getString("pics"));
a.setExtra(rs.getString("extra"));
return a;
}
private void testBlob(int length) throws Exception {
Random r = new Random(length);
byte[] data = new byte[length];
r.nextBytes(data);
Blob b = conn.createBlob();
OutputStream out = b.setBinaryStream(1);
out.write(data, 0, data.length);
out.close();
stat.execute("delete from test");
PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
prep.setInt(1, 1);
prep.setBlob(2, b);
prep.execute();
prep.setInt(1, 2);
b = conn.createBlob();
b.setBytes(1, data);
prep.setBlob(2, b);
prep.execute();
prep.setInt(1, 3);
prep.setBlob(2, new ByteArrayInputStream(data));
prep.execute();
prep.setInt(1, 4);
prep.setBlob(2, new ByteArrayInputStream(data), -1);
prep.execute();
ResultSet rs;
rs = stat.executeQuery("select * from test");
rs.next();
Blob b2 = rs.getBlob(2);
assertEquals(length, b2.length());
byte[] bytes = b.getBytes(1, length);
byte[] bytes2 = b2.getBytes(1, length);
assertEquals(bytes, bytes2);
rs.next();
b2 = rs.getBlob(2);
assertEquals(length, b2.length());
bytes2 = b2.getBytes(1, length);
assertEquals(bytes, bytes2);
while (rs.next()) {
bytes2 = rs.getBytes(2);
assertEquals(bytes, bytes2);
}
}
/**
* @throws SQLException
* @throws IOException
* @see com.tl.db.dialect.Dialect#getBlob(java.sql.ResultSet, java.lang.String)
*/
public IBlob getBlob(ResultSet rs, String name) throws SQLException, IOException {
Blob blob = rs.getBlob(name);
if (blob != null) {
byte[] content = LobHelper.readBlob(blob);
return LobHelper.createBlob(content);
}
return null;
}
public InputStream getBlobStream(int columnIndex) throws SQLException {
Blob blob = resultSet.getBlob(columnIndex + 1);
if (blob == null) {
return null;
} else {
return blob.getBinaryStream();
}
}
@Override
public Blob getBlob(int i) throws SQLException {
try {
return _res.getBlob(i);
} catch (SQLException e) {
handleException(e);
return null;
}
}
@Override
public Blob getBlob(String colName) throws SQLException {
try {
return _res.getBlob(colName);
} catch (SQLException e) {
handleException(e);
return null;
}
}
@Override
public ArrayList<TenantBean> getTenantByExpectedYearofGrad(int year) {
try {
Connector c = new Connector();
Connection connection = c.getConnection();
String query = "select * from tenant where expectedyearofgrad = ?";
PreparedStatement ps = connection.prepareStatement(query);
ps.setInt(1, year);
ResultSet resultSet = ps.executeQuery();
ArrayList<TenantBean> list = new ArrayList<TenantBean>();
TenantBean bean = new TenantBean();
int tenantID, expectedyearofgrad;
Long contact;
String fname, lname, gender, address, degree, school;
boolean status;
Blob image;
while (resultSet.next()) {
tenantID = resultSet.getInt("tenantID");
contact = resultSet.getLong("contact");
expectedyearofgrad = resultSet.getInt("expectedyearofgrad");
fname = resultSet.getString("fname");
lname = resultSet.getString("lname");
gender = resultSet.getString("gender");
address = resultSet.getString("address");
degree = resultSet.getString("degree");
school = resultSet.getString("school");
status = resultSet.getBoolean("status");
image = resultSet.getBlob("image");
bean = new TenantBean();
bean.setTenantID(tenantID);
bean.setContact(contact);
bean.setExpectedyearofgrad(expectedyearofgrad);
bean.setFname(fname);
bean.setLname(lname);
bean.setGender(gender);
bean.setDegree(degree);
bean.setAddress(address);
bean.setSchool(school);
bean.setStatus(status);
bean.setImage(image);
list.add(bean);
}
return list;
} catch (SQLException ex) {
Logger.getLogger(TenantDAOImplementation.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
@Override
protected byte[] getBlobAsBytes(ResultSet rs, int col) throws SQLException {
if (_useBytesMethodsForBlob) {
return rs.getBytes(col);
} else {
Blob dataAsBlob = rs.getBlob(col);
return dataAsBlob.getBytes(1, (int) dataAsBlob.length());
}
}
/**
* Gets a blob with its current version from the database. If an exception or error occures, this
* method closes the database connection.
*
* @param blobId The id of the blob.
* @param forUpdate Whether a pessimistic lock should be applied on the blob or not.
* @param connection The database connection.
* @return The blob and its current version.
*/
protected ConnectedBlob connectBlob(
final long blobId, final boolean forUpdate, final Connection connection) {
QBlobstoreBlob qBlob = QBlobstoreBlob.blobstoreBlob;
try {
SQLQuery<Tuple> query =
new SQLQuery<>(connection, querydslConfiguration)
.select(
qBlob.blobId,
qBlob.version_.as("version_"),
Expressions.as(blobSelectionExpression, "blob_"))
.from(qBlob)
.where(qBlob.blobId.eq(blobId));
if (forUpdate) {
query.forUpdate();
} else if (lockBlobForShareQueryFlag != null) {
query.addFlag(lockBlobForShareQueryFlag);
}
SQLBindings sqlBindings = query.getSQL();
String sql = sqlBindings.getSQL();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
int paramIndex = 0;
for (Object binding : (List<Object>) sqlBindings.getBindings()) {
paramIndex++;
preparedStatement.setObject(paramIndex, binding);
}
long version;
Blob blob;
ResultSet resultSet = preparedStatement.executeQuery();
try {
if (!resultSet.next()) {
throw new NoSuchBlobException(blobId);
}
version = resultSet.getLong("version_");
blob = resultSet.getBlob("blob_");
} finally {
final boolean closeResultSetIsNotNecessaryAsItWillBeClosedByStatement = false;
closeResultSet(resultSet, closeResultSetIsNotNecessaryAsItWillBeClosedByStatement);
}
BlobChannel blobChannel;
if (blobAccessMode == BlobAccessMode.BYTES) {
blobChannel = new BytesBlobChannel(blob);
} else {
blobChannel = new StreamBlobChannel(blob);
}
return new ConnectedBlob(blobId, blobChannel, version, preparedStatement);
} catch (SQLException | RuntimeException | Error e) {
closeCloseableDueToThrowable(connection, e);
// TODO throw unchecked sql exception
throw new RuntimeException(e);
}
}
public DataSourceVO<?> mapRow(ResultSet rs, int rowNum) throws SQLException {
DataSourceVO<?> ds =
(DataSourceVO<?>)
SerializationHelper.readObjectInContext(rs.getBlob(5).getBinaryStream());
ds.setId(rs.getInt(1));
ds.setXid(rs.getString(2));
ds.setName(rs.getString(3));
ds.setDefinition(ModuleRegistry.getDataSourceDefinition(rs.getString(4)));
return ds;
}
/**
* According to the JDBC spec, BLOB and CLOB objects must stay open even if the result set is
* closed (see ResultSet.close).
*/
private void testLobStaysOpenUntilCommitted() throws Exception {
Connection conn = getConnection();
stat = conn.createStatement();
stat.execute("create table test(id identity, c clob, b blob)");
PreparedStatement prep = conn.prepareStatement("insert into test values(null, ?, ?)");
prep.setString(1, "");
prep.setBytes(2, new byte[0]);
prep.execute();
Random r = new Random(1);
char[] chars = new char[100000];
for (int i = 0; i < chars.length; i++) {
chars[i] = (char) r.nextInt(10000);
}
String d = new String(chars);
prep.setCharacterStream(1, new StringReader(d), -1);
byte[] bytes = new byte[100000];
r.nextBytes(bytes);
prep.setBinaryStream(2, new ByteArrayInputStream(bytes), -1);
prep.execute();
conn.setAutoCommit(false);
ResultSet rs = stat.executeQuery("select * from test order by id");
rs.next();
Clob c1 = rs.getClob(2);
Blob b1 = rs.getBlob(3);
rs.next();
Clob c2 = rs.getClob(2);
Blob b2 = rs.getBlob(3);
assertFalse(rs.next());
// now close
rs.close();
// but the LOBs must stay open
assertEquals(0, c1.length());
assertEquals(0, b1.length());
assertEquals(chars.length, c2.length());
assertEquals(bytes.length, b2.length());
assertEquals("", c1.getSubString(1, 0));
assertEquals(new byte[0], b1.getBytes(1, 0));
assertEquals(d, c2.getSubString(1, (int) c2.length()));
assertEquals(bytes, b2.getBytes(1, (int) b2.length()));
stat.execute("drop table test");
conn.close();
}
@Override
public List<barang> getBarangbyKeterangan(String keterangan) throws BarangException {
List<barang> list = new ArrayList<barang>();
PreparedStatement statement = null;
barang barang = null;
try {
connection.setAutoCommit(false);
statement = connection.prepareStatement(getByket);
statement.setString(1, "%" + keterangan + "%");
ResultSet rs = statement.executeQuery();
while (rs.next()) {
barang = new barang();
barang.setIdBarang(rs.getString("idbarang"));
barang.setIdBarcode(rs.getString("idbarcode"));
barang.setNamaBarang(rs.getString("namabarang"));
barang.setTipe(rs.getString("tipe"));
barang.setMerek(rs.getString("merek"));
barang.setHargamodal(rs.getInt("hargamodal"));
barang.setEceran(rs.getInt("eceran"));
barang.setGrosir(rs.getInt("grosir"));
barang.setSatuan(rs.getString("satuan"));
barang.setStok(rs.getInt("stok"));
barang.setStokMinimum(rs.getInt("stok_minimum"));
barang.setSupplier(rs.getString("supplier"));
barang.setKeterangan(rs.getString("keterangan"));
barang.setGambarHasil(rs.getBlob("gambar"));
barang.setKategori(rs.getString("kategori"));
list.add(barang);
}
connection.commit();
// return list;
} catch (SQLException exception) {
try {
connection.rollback();
} catch (SQLException ex) {
}
// throw new barangException(exception.getMessage());
} finally {
try {
connection.setAutoCommit(true);
} catch (SQLException ex) {
}
if (statement != null) {
try {
statement.close();
} catch (SQLException exception) {
}
}
}
return list;
}
public static String getBlobValue(ResultSet result, String strField)
throws java.sql.SQLException {
String strValueReturn = "";
Blob blob = result.getBlob(strField);
if (result.wasNull()) {
strValueReturn = "";
} else {
int length = (int) blob.length();
if (length > 0) strValueReturn = new String(blob.getBytes(1, length));
}
return strValueReturn;
}
private static JSONObject toJSONObject(ResultSetMetaData rsmd, int numColumns, ResultSet rs)
throws JSONException, SQLException {
JSONObject obj = new JSONObject();
for (int i = 1; i < numColumns + 1; i++) {
String column_name = rsmd.getColumnName(i);
switch (rsmd.getColumnType(i)) {
case java.sql.Types.ARRAY:
obj.put(column_name, rs.getArray(i));
break;
case java.sql.Types.BIGINT:
obj.put(column_name, rs.getInt(i));
break;
case java.sql.Types.BOOLEAN:
obj.put(column_name, rs.getBoolean(i));
break;
case java.sql.Types.BLOB:
obj.put(column_name, rs.getBlob(i));
break;
case java.sql.Types.DOUBLE:
obj.put(column_name, rs.getDouble(i));
break;
case java.sql.Types.FLOAT:
obj.put(column_name, rs.getFloat(i));
break;
case java.sql.Types.INTEGER:
obj.put(column_name, rs.getInt(i));
break;
case java.sql.Types.NVARCHAR:
obj.put(column_name, rs.getNString(i));
break;
case java.sql.Types.VARCHAR:
obj.put(column_name, rs.getString(i));
break;
case java.sql.Types.TINYINT:
obj.put(column_name, rs.getInt(i));
break;
case java.sql.Types.SMALLINT:
obj.put(column_name, rs.getInt(i));
break;
case java.sql.Types.DATE:
obj.put(column_name, rs.getDate(i));
break;
case java.sql.Types.TIMESTAMP:
obj.put(column_name, rs.getTimestamp(i));
break;
default:
obj.put(column_name, rs.getObject(i));
break;
}
}
return obj;
}
/**
* Get value from given ResultSet at given index with given SQL type.
*
* @param rs The ResultSet to get the value from.
* @param index The index of the value in the ResultSet.
* @param sqlType The SQL type of the value.
* @return The value.
* @throws SQLException If a database access error occurs.
*/
public static Object getValue(final ResultSet rs, final int index, final int sqlType)
throws SQLException {
switch (sqlType) {
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
return rs.getString(index);
case Types.DECIMAL:
case Types.NUMERIC:
return rs.getBigDecimal(index);
case Types.INTEGER:
int intVal = rs.getInt(index);
return (rs.wasNull() ? null : new Integer(intVal));
case Types.TIME:
return rs.getTime(index, getCalendar());
case Types.DATE:
return rs.getDate(index);
case Types.TIMESTAMP:
return rs.getTimestamp(index, getCalendar());
case Types.FLOAT:
case Types.DOUBLE:
double doubleVal = rs.getDouble(index);
return (rs.wasNull() ? null : new Double(doubleVal));
case Types.REAL:
float floatVal = rs.getFloat(index);
return (rs.wasNull() ? null : new Float(floatVal));
case Types.SMALLINT:
short shortVal = rs.getShort(index);
return (rs.wasNull() ? null : new Short(shortVal));
case Types.TINYINT:
byte byteVal = rs.getByte(index);
return (rs.wasNull() ? null : new Byte(byteVal));
case Types.LONGVARBINARY:
case Types.VARBINARY:
case Types.BINARY:
return rs.getBytes(index);
case Types.BLOB:
Blob blob = rs.getBlob(index);
return (blob == null ? null : blob.getBinaryStream());
case Types.CLOB:
return rs.getClob(index);
case Types.BIGINT:
long longVal = rs.getLong(index);
return (rs.wasNull() ? null : new Long(longVal));
case Types.BIT:
boolean boolVal = rs.getBoolean(index);
return (rs.wasNull() ? null : new Boolean(boolVal));
default:
Object value = rs.getObject(index);
return (rs.wasNull() ? null : value);
}
}
public void testSerialize() throws IOException, ClassNotFoundException {
SparseDoubleMatrix2D matrix2D =
new SparseDoubleMatrix2D(
new double[][] {
new double[] {1, 2, 3}, new double[] {4, 5, 6}, new double[] {7, 8, 9}
});
System.out.println(String.format("matrix2D: %s", matrix2D));
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
System.err.println("Could not initiate JDBC driver.");
ex.printStackTrace();
}
try {
Connection conn =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/wittcarl_recurrence_plot_clustering?user=root&password=mysql");
PreparedStatement preps =
conn.prepareStatement(
"INSERT INTO `wittcarl_recurrence_plot_clustering`.`blob` (`id`, `signature`) VALUES (null, ?);");
preps.setObject(1, matrix2D);
preps.execute();
PreparedStatement get = conn.prepareStatement("SELECT id, signature FROM `blob` WHERE id=3");
ResultSet resultSet = get.executeQuery();
resultSet.next();
int id = resultSet.getInt(1);
InputStream is = resultSet.getBlob(2).getBinaryStream();
ObjectInputStream oip = new ObjectInputStream(is);
Object object = oip.readObject();
String className = object.getClass().getName();
oip.close();
is.close();
resultSet.close();
// de-serialize list a java object from a given objectID
SparseDoubleMatrix2D restored = (SparseDoubleMatrix2D) object;
System.out.println(String.format("id: %s", id));
System.out.println(String.format("restored: %s", restored));
conn.close();
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
