For MySql multi table insert method...

Student Student students ID, Name; course table Course course ID, Name; score ID, student ID, course ID, grades Score, does the bulk insert 20 students, 10 course, result how to insert.... My code stickers.
public class SCTest {
private final String DBNAME = "test";
private final String TABLENAME1 = "s1";
private final String TABLENAME2 = "s2";
private final String TABLENAME3 = "s3";

public void createTable() {
String sql_create1 = "create table "+ DBNAME + "." +
TABLENAME1+"(id int(11) primary key,name varchar(255))";
String sql_create2 = "create table "+ DBNAME + "." +
TABLENAME2+"(id int(11) primary key,name varchar(255))";
String sql_create3 = "create table "+ DBNAME + "." +
TABLENAME3+"(id int(11) primary key,s1Id int(11),s2Id int(11),Score float)";
Connection conn = JdbcUtil.getConnection();
Statement stmt = JdbcUtil.getStatement(conn);

try {
stmt.execute(sql_create1);
stmt.execute(sql_create2);
stmt.execute(sql_create3);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn);
JdbcUtil.close(stmt);
}
}
public void addTest() {

String sql_add1 = "insert into " + DBNAME + "." + TABLENAME1
+ " values(?,?)";
String sql_add2 = "insert into " + DBNAME + "." + TABLENAME2
+ " values(?,?)";
String sql_add3 = "insert into " + DBNAME + "." + TABLENAME3
+ " values(?,?,?,?)";
Connection conn = JdbcUtil.getConnection();
PreparedStatement pstmt1 = JdbcUtil.getPreparedStatement(conn, sql_add1);
PreparedStatement pstmt2 = JdbcUtil.getPreparedStatement(conn, sql_add2);
PreparedStatement pstmt3 = JdbcUtil.getPreparedStatement(conn, sql_add3);
try {
conn.setAutoCommit(false);
for (int i = 1; i <= 20; i++) {
pstmt1.setInt(1, i);
pstmt1.setString(2, "XX"+i);
pstmt1.addBatch();
pstmt1.executeBatch();
}
for (int j = 1; j <=10; j++) {
pstmt2.setInt(1, j);
pstmt2.setString(2, "The "+j+" course");
pstmt2.addBatch();
pstmt2.executeBatch();
}
/**
* Results how to insert. . . .
*/

conn.commit();
conn.close();
pstmt1.close();
pstmt2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Started by Leander at November 22, 2016 - 9:18 PM

insert into tb1();insert into tb2 values();insert into tb3 values();.....

Posted by Sue at December 03, 2016 - 9:32 PM

int count=0;
for (int i = 1; i <= 20; i++)
for (int j = 1; j <=10; j++)
{
count++;
pstmt3.setInt(1, count);
pstmt3.setInt(2, i);
pstmt3.setInt(3, j);
pstmt3.setFloat(4, j*j);
pstmt2.addBatch();
pstmt2.executeBatch();
}

Posted by Philomena at December 06, 2016 - 9:54 PM

The rest aren't say !
At least we should add a rollback! Wrong, in front of the insert is inserted into the! It is not wrong made it!

Posted by Justin at December 08, 2016 - 10:48 PM

On that 3Q

Posted by Leander at December 12, 2016 - 10:53 PM