Java C9. Advanced JDBC pptx
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (263.02 KB, 34 trang )
Chapter 9. Advanced JDBC
ITSS Java Programming
NGUYEN Hong Quang, HUT
Statement and Prepared Statement (1)
Making of Statement object
All of parameters must be in
the statement
Making of Preparerd
Statement object
Some parameters are
not in the statement
Setting value of
parameter
Execution of Prepared
Statement
Execution of Statement
Statement and Prepared Statement (2)
Prepared Statement : compile only one time
Statement : compile each time to run
If we have to use one SQL statement several
times, it would better to use Preparerd
Statement
Creation of Prepared Statement
PreparedStatement
java.sql.Connection.prepareStatement(String sql) throws
SQLException
Arguments : setting by parameters “?”
Exemple :
Connection con;
PreparedStatement stmt;
try {
con = DriverManager.
getConnection(url,userName,userPassword);
stmt = con.prepareStatement
(
"Update Student Set mark = ? Where id = ?")
;
} catch (SQLException e) { e.printStackTrace(); }
Setting of parameters
Format :
stmt. setXXX (index, value);
setXXX : method corresponding with each data type
Index : numerical value corresponding to the position of
the parameter
Value : value of parameter
Setting of parameters : Exemple
PreparedStatement stmt;
stmt = con.prepareStatement
(
"Update Student Set mark = ? Where id = ?")
;
Exemple :
stmt = con.prepareStatement("Update Student Set mark = ?
Where NAME = ?");
stmt.setInt(1, 7);
stmt.setString(2, "Tran Duy Dong");
Updated parameter methods
Execute SQL Statement
Reference type and update type SQL Statement
executeQuery (String sql)
Executes the SELECT statement.
executeUpdate (String sql)
Executes the given SQL statement, which may be
an INSERT, UPDATE, or DELETE statement.
Exemple :
ResultSet res = stmt.executeQuery();
count = stmt.executeUpdate(); // number of updated
records
Application example
prompt ＞ java UpdateStudent <name>
<newMark>
Configuration of input parameters
1
2
3
Args[0] : name
Args[1] : mark
Declaration (1)
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.SQLException;
public class UpdateStudent {
public static void main(String[] args) {
if( args.length != 2 ){
System.
out.println("Usage : UpdateStudent <name>
<newMark>");
System.
exit(1);
}
Declaration (2)
Connection con = null;
PreparedStatement stmt = null;
String userName = "root";
String userPassword = "quangnh“;
String drv = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/qlsv";
String name = args[0];
int newMark = Integer.parseInt(args[1]);
System.out.println("Update database : ");
Update database (1)
try {
// JDBC driver‘s reading
Class.forName (drv);
// Connection to database
con = DriverManager.getConnection(url,userName,userPassword);
stmt = con.prepareStatement("Update Student Set mark = ? " +
"Where NAME = ?");
stmt.setInt(1, newMark);
stmt.setString(2, name);
Update database (2)
int count = 0;
count = stmt.executeUpdate();
if (count == 0)
System.out.println("None record updated!");
else
System.out.println(count + " records updated!");
}
catch (ClassNotFoundException e){
System.err.println ("Do not connect to database");
}
catch (SQLException e) {
e.printStackTrace();
}
Close connection
try {
stmt.close();
con.close();
}
catch (SQLException e) {
e.printStackTrace();
}
Outline of transaction
A transaction consists of group of commands
that must be all completed to guarantee the
consistency of the database.
When database are updated by using the
transaction function, it makes two phases :
the transaction either succeeds in its entirety and
it can be committed
it fails somewhere in the middle : carry out a
rollback and the database automatically undoes
the effect of all updates that occurred since the
last committed transaction.
Process of transaction
Create connection
Stop auto-commit
Execute SQL commands
SELECT, UPDATE, INSERT, DELETE
Commit
Execute SQL commands
SELECT, UPDATE, INSERT, DELETE
Commit
Transaction
Transaction
Disable automatic commit mode
Auto-commit mode is setting default. Each
command is itshelf a transaction.
Syntax :
void java.sql.Connection.setAutoCommit(boolean
autoCommit) throws SQLException
Control auto-commit mode by setting autoCommit
argument : Enable(true) / Disable (false)
Exemple
Connection con = null;
String userName = “student";
String userPassword = “student";
String url = "jdbc:mysql://localhost:3306/qlsv";
con = DriverManager.
getConnection
(url,userName,userPassword);
con.setAutoCommit(false);
Committing a transaction
void java.sql.Connection.commit() throws SQLException
Example :
con = DriverManager.
getConnection
(url,userName,userPassword);
con.setAutoCommit(false);
// multiple database update processing
…
con.commit();
Cancellation of transaction
void java.sql.Connection.rollback() throws SQLException
Example :
con = DriverManager.
getConnection
(url,userName,userPassword);
con.setAutoCommit(false);
// multiple database update processing
…
con.rollback();
Sample application
Sample application : Coding
public class TestTransaction {
String name = args[0];
String autoCommit = args[1];
int mark = 9;
int newMark = 10;
System.
out.println("Update database : ");
Sample application : identify the mark of
the student
// Connection to database
con =
DriverManager.
getConnection(url,userName,userPassword);
con.setAutoCommit(false);
// identify the mark of the student
stmt = con.prepareStatement("Select Mark From Student
Where Name = ?");
stmt.setString(1, name);
rs = stmt.executeQuery();
if (rs.next())
mark = rs.getInt("mark");
System.
out.println("Student " + name + ". Mark before update
: " + mark);
Sample application : Update new mark
// identify newMark
if (mark == 10) newMark = 5;
else newMark = mark + 1;
// update the newMark
stmt = con.prepareStatement("Update Student Set Mark = ?
Where Name = ?");
stmt.setInt(1, newMark);
stmt.setString(2, name);
int count = 0;
count = stmt.executeUpdate();
