学生表Student有学生ID,Name;课程表Course有课程ID,Name;成绩表有ID,学生ID,课程ID,成绩Score,请问批量插入20个学生,10个课程,成绩该怎么插入。我的代码贴一下。
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, "第"+j+"门课");
pstmt2.addBatch();
pstmt2.executeBatch();
}
/**
* 成绩怎么插入啊。。
*/
conn.commit();
conn.close();
pstmt1.close();
pstmt2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
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, "第"+j+"门课");
pstmt2.addBatch();
pstmt2.executeBatch();
}
/**
* 成绩怎么插入啊。。
*/
conn.commit();
conn.close();
pstmt1.close();
pstmt2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
解决方案 »
- mysql_real_escape_string 的问题
- 我的mysql没有统计函数?
- mysql dump....
- 图书借还关系
- charset设置~!
- 请问建表时为什么要单独再建一张映射表?
- select u_name,u_id from (select * from user_info where U_name='bbb') where U_pass='aaa' 不对吗?
- mysql 有没有 类似PRO*C或者esql的 预编译器?
- 高分求助:有3中以frm,myd,myi后缀的数据库文件各是什么意思,我怎样将他们挂在MYSQL数据库上
- JSP+MySQL问题!怎么这种样子???
- 为什么循环执行“load data infile” ,MySQL的响应速度越来越慢?
- mysql windows 多主从一 怎么做
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();
}