首页 > 代码库 > 深入浅出JDBC-操作时间与大对象(Clob/Blob)
深入浅出JDBC-操作时间与大对象(Clob/Blob)
一、时间(Date、Time、Timestamp)
java.sql.Date/java.sql.Time/java.sql.Timestamp extends java.util.Date
public class TimeData { PreparedStatement pStatement=null; //操作日期类型的数据 public void insertDate(Connection connection,long time){ try { String sql="insert into user(regTime)values(?)"; pStatement=connection.prepareStatement(sql); //插入的时间为当前日期 pStatement.setDate(1,new Date(time)); pStatement.executeUpdate(); }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.getInstance().close(pStatement); DBUtil.getInstance().close(connection); } } //操作时间类型的数据 public void insertTimestamp(Connection connection,long time){ try { String sql="insert into user(loginTime)values(?)"; pStatement=connection.prepareStatement(sql); java.sql.Timestamp timestamp=new java.sql.Timestamp(time); //插入的时间为当前时间 pStatement.setTimestamp(1,timestamp); pStatement.executeUpdate(); }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.getInstance().close(pStatement); DBUtil.getInstance().close(connection); } } /** *批量添加某一区间的数据 * @param connection * @param first 注册时间 * @param seconed 登录时间 * @param rowNum 添加的记录的条数 */ public void insertBatch(Connection connection,long first,long seconed,int rowNum){ try { String sql="insert into user(regTime,loginTime) values(?,?)"; pStatement=connection.prepareStatement(sql); for(int i=0;i<rowNum;i++){ long regTime=new Random().nextInt(100000000)+first; long loginTime=seconed-new Random().nextInt(10000000); pStatement.setDate(1,new java.sql.Date(regTime)); pStatement.setTimestamp(2,new Timestamp(loginTime)); pStatement.executeUpdate(); } }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.getInstance().close(pStatement); DBUtil.getInstance().close(connection); } } //插入指定时间的数据 public long StringToDate(String dateString){ try { SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); return sdf.parse(dateString).getTime(); } catch (ParseException e) { e.printStackTrace(); } return 0; } //查询日期、时间 public void query(Connection connection){ Statement statement=null; ResultSet rs=null; try { statement=connection.createStatement(); String sql="select regTime,loginTime from user"; statement.executeQuery(sql); rs=statement.executeQuery(sql); while(rs.next()){ Date date=rs.getDate("regTime"); Timestamp ts=rs.getTimestamp("loginTime"); System.out.println(date+"-----"+ts); } } catch (SQLException e) { e.printStackTrace(); } }}
二、时间单元测试
public class TestTimeData { Connection connection=DBUtil.getInstance().getConnection(); TimeData timeData=new TimeData(); @Test public void insertDate(){ timeData.insertDate(connection,System.currentTimeMillis()); } @Test public void insertTimestamp(){ timeData.insertTimestamp(connection,System.currentTimeMillis()); } @Test public void insertAppointedDate(){ //插入指定时间 long t=timeData.StringToDate("2017-01-01 15:30:00"); timeData.insertDate(connection,t); } @Test public void insertBatchDate(){ //批量插入某一时间段时间 long start=timeData.StringToDate("2016-01-01 12:50:20"); long end=timeData.StringToDate("2017-12-30 02:00:00"); timeData.insertBatch(connection,start,end,50); } @Test public void query(){ timeData.query(connection); }}
三、大数据对象(Clob、Blob)
public class BigData{ PreparedStatement pStatement=null; /** * 插入海量文字通过读取本地文件 * @param filePath 文件的物理路径 * @param connection */ public void insertClobByReadFile(Connection connection,String filePath ) { try { String sql="insert into user(resume)values(?)"; pStatement=connection.prepareStatement(sql); pStatement.setClob(1,new FileReader(new File(filePath))); pStatement.executeUpdate(); }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.getInstance().close(pStatement); DBUtil.getInstance().close(connection); } } /** * 读取存储在数据库的海量文字文件 */ public void queryClob(Connection connection,int id) { ResultSet rs=null; try { String sql="select resume from user where id=?"; pStatement=connection.prepareStatement(sql); pStatement.setInt(1,id); rs=pStatement.executeQuery(); while (rs.next()){ Clob clob=rs.getClob("resume"); Reader r=clob.getCharacterStream(); int temp=0; while((temp=r.read())!=-1){ System.out.print((char)temp); } } }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.getInstance().close(rs); DBUtil.getInstance().close(pStatement); DBUtil.getInstance().close(connection); } } /** * 存储图片文件 * @param connection * @param imgPath 图片的物理路径 */ public void insertBlob(Connection connection,String imgPath) { try { pStatement=connection.prepareStatement("insert into user(headImg) values(?);"); //获得图片的输入流 pStatement.setBlob(1,new FileInputStream(new File(imgPath))); pStatement.execute(); }catch (Exception e){ e.printStackTrace(); }finally { DBUtil.getInstance().close(pStatement); DBUtil.getInstance().close(connection); } } /** * 获取图片文件，并且输入到本地：F:/hello.jpg。 * @param connection * @param id */ public void queryBlob(Connection connection,int id){ ResultSet rs; try { String sql="select headImg from user where id=?"; pStatement=connection.prepareStatement(sql); pStatement.setInt(1,id); rs=pStatement.executeQuery(); while(rs.next()){ //获得文件的blob对象 Blob blob=rs.getBlob("headImg"); //获取文件的输入流 InputStream is=blob.getBinaryStream(); //建立输出流用于输入图片查看结果 OutputStream os=new FileOutputStream(new File("F:/hello.txt")); int temp=0; while((temp=is.read())!=-1){ os.write(temp); } os.flush(); os.close(); } }catch (Exception e){ e.printStackTrace(); } }}
大数据对象单元测试
public class TestBigData { //获取数据库连接 Connection connection=DBUtil.getInstance().getConnection(); BigData bigData=new BigData(); @Test public void insertClob(){ try { String filePath="F:/testclob.txt"; bigData.insertClobByReadFile(connection,filePath); } catch (Exception e) { e.printStackTrace(); } } @Test public void queryClob(){ try { bigData.queryClob(connection,1); } catch (Exception e) { e.printStackTrace(); }finally { DBUtil.getInstance().close(connection); } } @Test public void insertBlob(){ String imgPath="F:/testclob.txt"; bigData.insertBlob(connection,imgPath); } @Test public void queryBlob(){ bigData.queryBlob(connection,15906); }}
深入浅出JDBC-操作时间与大对象(Clob/Blob)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。