1

I'm using a BufferedWriter to send data into a database, (I know this is not really what it's meant for... don't ask. Basically I need to log to a database but buffer the data so that we don't end up with a major bottleneck) and I'm running into the issue that the writer will break up the data in awkward places (like in middle of an insert statement) which is causing errors obviously. Is there a way to force the writer to only send in complete chunks of data? or is all I can do just call .write and see what happens? (Setting byte size won't help because there are many options for different kinds of insert statements) 

//insertArray is a char[] that's being sent to BufferedWriter's .write method. 
//It contains the information for one record. (I know this is not ideal because of sql injection, 
//and I would prefer to send a prepared statement, but I'm using SQLlite which apparently doesn't support conversion of prepared statements to strings). 
private void callInsert() {

    try {

        writer.write(insertArray, 0, insertArray.length);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

The .write method in my class then opens a database connection and sends in whatever data it has as one long statement: like this: 

insert into students(fname, lname, id) values(('Bob', 'Green', '5), ('Jake', 'Klein', '7'));

Ideally I would like to truncate the data after the last complete insert before writing and then add the rest to the next write. My current issue is that I could end up with this: 

insert into students(fname, lname, id) values(('Bob', 'Green', '5), ('Jake', 'Klein

if the BufferedWriter so chooses to break it up there.

John Kugelman
  • 349,597
  • 67
  • 533
  • 578
user9791370
  • 339
  • 1
  • 10
  • 1
    Let's see some code – Sean Bright Jun 19 '19 at 19:41
  • 4
    Major [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) here. Changing how BufferedWriter writes data is not a good plan of attack. Show us your code. Give us context. I guarantee there's a better answer out there. – John Kugelman Jun 19 '19 at 19:48
  • 1
    This is honestly just a bad way to interact with a database. Sending queries over the wire and having it read them in chunks is just nonsensical. – Louis Wasserman Jun 19 '19 at 21:04
  • 1
    Er... Why aren't you using jdbc or whatever? – Shawn Jun 19 '19 at 22:27
  • @LouisWasserman why? If every single time i have data to insert I have to send the data to the database, that seems kind of inefficient... doesn't it make more sense to wait until i have a bunch of data and insert it all at once? – user9791370 Jun 20 '19 at 16:05
  • Yes, sure, you can do that -- though that's almost certainly premature optimization -- but more to the point, a `BufferedWriter` is not meant to do that sort of buffering. – Louis Wasserman Jun 20 '19 at 16:19
  • What kind of buffering is it supposed to do? – user9791370 Jun 20 '19 at 16:41

1 Answers1

0

When you write to a BufferedWriter it just saves your writes in a buffer. At some point of its choosing it will send that data to the underlying character stream. You have no control over when that happens, with the exception that you can tell it to flush. If you are getting partial data at the other end of the stream, that suggests that the rest is sitting in the buffer.

So your options are

  • flush the BufferedWriter when you've finished a query (but if this works for you, what benefit are you getting from the buffering?)
  • user a plain Writer with no buffering - the data is sent as soon as you write (but that's not how you normally talk to a database, so what benefit are you getting from the character stream?)
  • don't use a Writer at all - JDBC is the usual way to talk to a database, or some wrapper around it like JPA or Hibernate.

You say that your database writes are a bottleneck. Buffering can improve performance if you have many small writes: they are sent in larger chunks. But buffering won't help you if you have a large amount of data to send.

J Banana
  • 154
  • 6