深入浅出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);
        }
    }
</span><span style="color: #008000;">//</span><span style="color: #008000;">操作时间类型的数据</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> insertTimestamp(Connection connection,<span style="color: #0000ff;">long</span><span style="color: #000000;"> time){
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        String sql</span>="insert into user(loginTime)values(?)"<span style="color: #000000;">;
        pStatement</span>=<span style="color: #000000;">connection.prepareStatement(sql);
        java.sql.Timestamp timestamp</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> java.sql.Timestamp(time);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">插入的时间为当前时间</span>
        pStatement.setTimestamp(1<span style="color: #000000;">,timestamp);
        pStatement.executeUpdate();
    }</span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e){
        e.printStackTrace();
    }</span><span style="color: #0000ff;">finally</span><span style="color: #000000;"> {
        DBUtil.getInstance().close(pStatement);
        DBUtil.getInstance().close(connection);
    }
}

</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 *批量添加某一区间的数据
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> connection
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> first 注册时间
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> seconed  登录时间
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> rowNum 添加的记录的条数
 </span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> insertBatch(Connection connection,<span style="color: #0000ff;">long</span> first,<span style="color: #0000ff;">long</span> seconed,<span style="color: #0000ff;">int</span><span style="color: #000000;"> rowNum){
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        String sql</span>="insert into user(regTime,loginTime) values(?,?)"<span style="color: #000000;">;
        pStatement</span>=<span style="color: #000000;">connection.prepareStatement(sql);
        </span><span style="color: #0000ff;">for</span>(<span style="color: #0000ff;">int</span> i=0;i&lt;rowNum;i++<span style="color: #000000;">){
            </span><span style="color: #0000ff;">long</span> regTime=<span style="color: #0000ff;">new</span> Random().nextInt(100000000)+<span style="color: #000000;">first;
            </span><span style="color: #0000ff;">long</span> loginTime=seconed-<span style="color: #0000ff;">new</span> Random().nextInt(10000000<span style="color: #000000;">);
            pStatement.setDate(</span>1,<span style="color: #0000ff;">new</span><span style="color: #000000;"> java.sql.Date(regTime));
            pStatement.setTimestamp(</span>2,<span style="color: #0000ff;">new</span><span style="color: #000000;"> Timestamp(loginTime));
            pStatement.executeUpdate();
        }
    }</span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e){
        e.printStackTrace();
    }</span><span style="color: #0000ff;">finally</span><span style="color: #000000;"> {
        DBUtil.getInstance().close(pStatement);
        DBUtil.getInstance().close(connection);
    }
}

</span><span style="color: #008000;">//</span><span style="color: #008000;">插入指定时间的数据</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">long</span><span style="color: #000000;"> StringToDate(String dateString){
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        SimpleDateFormat sdf</span>=<span style="color: #0000ff;">new</span> SimpleDateFormat("yyyy-MM-dd hh:mm:ss"<span style="color: #000000;">);
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> sdf.parse(dateString).getTime();
    } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (ParseException e) {
        e.printStackTrace();
    }
    </span><span style="color: #0000ff;">return</span> 0<span style="color: #000000;">;
}

</span><span style="color: #008000;">//</span><span style="color: #008000;">查询日期、时间</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> query(Connection connection){
    Statement statement</span>=<span style="color: #0000ff;">null</span><span style="color: #000000;">;
    ResultSet rs</span>=<span style="color: #0000ff;">null</span><span style="color: #000000;">;
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        statement</span>=<span style="color: #000000;">connection.createStatement();
        String sql</span>="select regTime,loginTime from user"<span style="color: #000000;">;
        statement.executeQuery(sql);
        rs</span>=<span style="color: #000000;">statement.executeQuery(sql);
        </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next()){
            Date date</span>=rs.getDate("regTime"<span style="color: #000000;">);
            Timestamp ts</span>=rs.getTimestamp("loginTime"<span style="color: #000000;">);
            System.out.println(date</span>+"-----"+<span style="color: #000000;">ts);
        }
    } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
        e.printStackTrace();
    }
}

}

二、时间单元测试

public class TestTimeData {
Connection connection</span>=<span style="color: #000000;">DBUtil.getInstance().getConnection();
TimeData timeData</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> TimeData();

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> insertDate(){
    timeData.insertDate(connection,System.currentTimeMillis());
}

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> insertTimestamp(){
    timeData.insertTimestamp(connection,System.currentTimeMillis());
}

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> insertAppointedDate(){
    </span><span style="color: #008000;">//</span><span style="color: #008000;">插入指定时间</span>
    <span style="color: #0000ff;">long</span> t=timeData.StringToDate("2017-01-01 15:30:00"<span style="color: #000000;">);
    timeData.insertDate(connection,t);
}

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> insertBatchDate(){
    </span><span style="color: #008000;">//</span><span style="color: #008000;">批量插入某一时间段时间</span>
    <span style="color: #0000ff;">long</span> start=timeData.StringToDate("2016-01-01 12:50:20"<span style="color: #000000;">);
    </span><span style="color: #0000ff;">long</span> end=timeData.StringToDate("2017-12-30 02:00:00"<span style="color: #000000;">);
    timeData.insertBatch(connection,start,end,</span>50<span style="color: #000000;">);
}

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> 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);
        }
    }
</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 * 读取存储在数据库的海量文字文件
 </span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> queryClob(Connection connection,<span style="color: #0000ff;">int</span><span style="color: #000000;"> id) {
    ResultSet rs</span>=<span style="color: #0000ff;">null</span><span style="color: #000000;">;
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        String sql</span>="select resume from user where id=?"<span style="color: #000000;">;
        pStatement</span>=<span style="color: #000000;">connection.prepareStatement(sql);
        pStatement.setInt(</span>1<span style="color: #000000;">,id);
         rs</span>=<span style="color: #000000;">pStatement.executeQuery();
        </span><span style="color: #0000ff;">while</span><span style="color: #000000;"> (rs.next()){
            Clob clob</span>=rs.getClob("resume"<span style="color: #000000;">);
            Reader r</span>=<span style="color: #000000;">clob.getCharacterStream();
            </span><span style="color: #0000ff;">int</span> temp=0<span style="color: #000000;">;
            </span><span style="color: #0000ff;">while</span>((temp=r.read())!=-1<span style="color: #000000;">){
                System.out.print((</span><span style="color: #0000ff;">char</span><span style="color: #000000;">)temp);
            }
        }
    }</span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e){
        e.printStackTrace();
    }</span><span style="color: #0000ff;">finally</span><span style="color: #000000;"> {
        DBUtil.getInstance().close(rs);
        DBUtil.getInstance().close(pStatement);
        DBUtil.getInstance().close(connection);
    }

}

</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 * 存储图片文件
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> connection
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> imgPath 图片的物理路径
 </span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> insertBlob(Connection connection,String imgPath) {
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        pStatement</span>=connection.prepareStatement("insert into user(headImg) values(?);"<span style="color: #000000;">);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">获得图片的输入流</span>
        pStatement.setBlob(1,<span style="color: #0000ff;">new</span> FileInputStream(<span style="color: #0000ff;">new</span><span style="color: #000000;"> File(imgPath)));
        pStatement.execute();
    }</span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e){
        e.printStackTrace();
    }</span><span style="color: #0000ff;">finally</span><span style="color: #000000;"> {
        DBUtil.getInstance().close(pStatement);
        DBUtil.getInstance().close(connection);
    }
}

</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 *  获取图片文件，并且输入到本地：F:/hello.jpg。
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> connection
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> id
 </span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> queryBlob(Connection connection,<span style="color: #0000ff;">int</span><span style="color: #000000;"> id){
    ResultSet rs;
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        String sql</span>="select headImg from user where id=?"<span style="color: #000000;">;
        pStatement</span>=<span style="color: #000000;">connection.prepareStatement(sql);
        pStatement.setInt(</span>1<span style="color: #000000;">,id);
        rs</span>=<span style="color: #000000;">pStatement.executeQuery();
        </span><span style="color: #0000ff;">while</span><span style="color: #000000;">(rs.next()){
            </span><span style="color: #008000;">//</span><span style="color: #008000;">获得文件的blob对象</span>
            Blob blob=rs.getBlob("headImg"<span style="color: #000000;">);
            </span><span style="color: #008000;">//</span><span style="color: #008000;">获取文件的输入流</span>
            InputStream is=<span style="color: #000000;">blob.getBinaryStream();
            </span><span style="color: #008000;">//</span><span style="color: #008000;">建立输出流用于输入图片查看结果</span>
            OutputStream os=<span style="color: #0000ff;">new</span> FileOutputStream(<span style="color: #0000ff;">new</span> File("F:/hello.txt"<span style="color: #000000;">));
            </span><span style="color: #0000ff;">int</span> temp=0<span style="color: #000000;">;
            </span><span style="color: #0000ff;">while</span>((temp=is.read())!=-1<span style="color: #000000;">){
                os.write(temp);
            }
            os.flush();
            os.close();
        }

    }</span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (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
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> queryClob(){
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        bigData.queryClob(connection,</span>1<span style="color: #000000;">);
    } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
        e.printStackTrace();
    }</span><span style="color: #0000ff;">finally</span><span style="color: #000000;"> {
        DBUtil.getInstance().close(connection);
    }
}

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> insertBlob(){
    String imgPath</span>="F:/testclob.txt"<span style="color: #000000;">;
    bigData.insertBlob(connection,imgPath);
}

@Test
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> queryBlob(){
    bigData.queryBlob(connection,</span>15906<span style="color: #000000;">);
}

}