ReadyStatement and resultSet interact with batch and receive methods
Now I am starting to use the java.sql package and I am doing some experiments with it.
I have these two tables
first:
`user` (
`userID` INT NOT NULL AUTO_INCREMENT ,
`nickname` VARCHAR(20) NULL ,
PRIMARY KEY (`userID`) )
and the second:
`club` (
`clubID` INT NOT NULL AUTO_INCREMENT ,
'clubName` VARCHAR(20) NOT NULL ,
`userID` INT NULL ,
PRIMARY KEY (`clubID`) ,...
where userID is the foreign key associated with the user ID of the first table.
And this is the code that should explain what I want to do. (this is for one user club only)
Class.forName("com.mysql.jdbc.Driver");
this.connect = DriverManager.getConnection("jdbc:mysql://localhost/" + this.database + "?user=" + this.user + "&password=" + this.password);
String s;
s = ("insert into " + this.database + ".user (nickname) values (?)");
this.preparedStatement = this.connect.prepareStatement(s);
this.preparedStatement.setString(1, "username");
this.preparedStatement.executeUpdate();
s = ("SELECT userID from " + this.database + ".user where nickname = 'username'");
this.preparedStatement = this.connect.prepareStatement(s);
this.resultSet = this.preparedStatement.executeQuery();
int n=0;
while (resultSet.next())
{
n = this.resultSet.getInt("userID");
}
s = ("insert into " + this.database + ".club (clubName, userID) values (?, ?)");
this.preparedStatement = this.connect.prepareStatement(s);
this.preparedStatement.setString(1, "club");
this.preparedStatement.setInt(2, n);
this.preparedStatement.executeUpdate();
If I did this process for more pairs (username, clubname) saved in a HashMap for example, how could I use the addBatch () method of the preparedStatement Inteface ??
I have to use three batches for the eache action: 1 insert username 2 (and write) user id 3 insert club name associated with correct user id
Or could I include the whole process in just one batch?
And one more question: why, if I try to remove the while loop surrounding the resultSet.getInt () method, does it give me an error?
Thanks in advance to everyone who will try to help me!
source to share
You cannot include the entire process in just one batch. The package is for one request. Here is an example link for a good example.
You can execute multiple queries as different batches as follows.
try {
DataSource dataSource = null;// specify data source
Connection con = dataSource.getConnection();
Statement s = con.createStatement();
// or
// PreparedStatement s =
// con.prepareStatement("INSERT INTO profile (fullname) VALUES ('Visruth CV')");
s.addBatch("INSERT INTO tran1 (username, password, profileid) VALUES ('visruth', 'password', 1)");
s.addBatch("INSERT INTO testtab (name) VALUES ('testtab')");
s.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
}
If you remove the loop while (resultSet.next())
, it will throw a NullPointerException because the current cursor position at resultSet
is on the default line, when you do resultSet.next()
, the cursor will move to the next line if there is a line (from the default line to the first line, from the first line to the second line, etc.) .d.), and at the same time resultSet.next()
will return true (only if it jumps), otherwise false. If it resultSet
contains more than one line, you can put it inside a while loop, if you don't just need to use an if condition.
source to share