/**
* mysql插入数据
*/
public void insertMysql() throws Exception {
Connection conn = DBUtils.getConnection();
String sql = "insert into userinfo(name,pass,mail,pic,ip,createtime,power) values"
+ "(?,?,?,?,?,?,?)";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setString(1, "2");
pstm.setString(2, "3");
pstm.setString(3, "jim@n.com");
FileInputStream in = new FileInputStream("e:/downloads/cat-water.jpg");
pstm.setBinaryStream(4, in, in.available());// mysql使用
pstm.setString(5, "localhost");
Date d = new Date();
Timestamp t = new Timestamp(d.getTime());
pstm.setTimestamp(6, t);// mysql使用
pstm.setString(7, "普通用户");
int i = pstm.executeUpdate();
DBUtils.close(conn, null, pstm);
}
/**
* oracle插入数据
*/
@Test
public void insertOracle() throws Exception {
Connection conn = DBUtils.getConnection();
conn.setAutoCommit(false);
int id = 102;
String sql = "insert into userinfo(id,name,pass,mail,ip,createtime,power,pic) values"
+ "(?,?,?,?,?,?,?,empty_blob()) ";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setInt(1, id);
pstm.setString(2, "kitty");
pstm.setString(3, "000");
pstm.setString(4, "jim@n.com");
pstm.setString(5, "localhost");
Date d = new Date();
pstm.setDate(6, new java.sql.Date(d.getTime()));// oracle使用
pstm.setString(7, "普通用户");
int i = pstm.executeUpdate();
// 插入一个空对象
Statement st = conn.createStatement();
// 用for update方式锁定数据行
ResultSet rs = st.executeQuery("select pic from userinfo where id="
+ id + " for update");
if (rs.next()) {
// 得到java.sql.Blob对象,然后Cast为oracle.sql.BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1);
// 得到数据库的输出流
OutputStream outStream = blob.getBinaryOutputStream();
// 获取要上传的图片
FileInputStream in = new FileInputStream("e:/downloads/cat-water.jpg");
// 将输入流写到输出流
byte[] b = new byte[blob.getBufferSize()];
int len = 0;
while ((len = in.read(b)) != -1) {
outStream.write(b, 0, len);
// blob.putBytes(1,b);
}
in.close();
outStream.flush();
outStream.close();
}
conn.commit();
DBUtils.close(conn, null, pstm);
}