1

I have two tables student(id, name, city), teacher(id, name, salary). And there are several rows which are needed to insert Mysql DB.

INSERT INTO student VALUES ('12', 'Tom', 'New York');
INSERT INTO student VALUES ('13', 'Jack', 'New York');
INSERT INTO teacher VALUES ('01', 'Joy', '42000');
INSERT INTO teacher VALUES ('02', 'Ryan', '39000');

The connector is JDBC in JAVA, could I write a single query to do it.

Eric
  • 1,271
  • 4
  • 14
  • 21

1 Answers1

8

Use a PreparedStatement and batch insert:

List<Student> students = ...
Connection con = ...
String insertSql = "INSERT INTO student VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(insertSql);
for (Student student : students) {
    pstmt.setString(1, student.getId()); //not sure if String or int or long
    pstmt.setString(2, student.getName());
    pstmt.setString(3, student.getCity());
    pstmt.addBatch();
}
pstmt.executeBatch();
//close resources...

Similar for your Teachers.

More info:

Community
  • 1
  • 1
Luiggi Mendoza
  • 85,076
  • 16
  • 154
  • 332
  • I've still got a lot to learn. I was only suggesting that you can insert multiple rows in one query and still use placeholders. – Sotirios Delimanolis Sep 25 '13 at 19:21
  • Hi Sotirios, Thanks for your comment. Just quick question, what's "placeholders"? – Eric Sep 25 '13 at 19:40
  • 1
    @Eric A placeholder is the `?` in the sql String. You use them with the `PreparedStatement` to set the values you actually want to use. They are helpful in, among other things, eliminating SQL injection. – Sotirios Delimanolis Sep 25 '13 at 19:45
  • @SotiriosDelimanolis oh,That's good to know! Thank you for sharing knowledge, I think I know how to learn this part. – Eric Sep 25 '13 at 19:53
  • 1
    @Eric I've updated the answer with link providing useful info – Luiggi Mendoza Sep 25 '13 at 19:58
  • @LuiggiMendoza Thank you so much, I am sure it will help not only me but also others don't know about this part. – Eric Sep 25 '13 at 20:04
  • @Eric you're welcome. And yes, that's what Q/A mean: help future readers to improve their programming and design skills. – Luiggi Mendoza Sep 25 '13 at 20:06