b09 lthdt java ket noi co so du lieu
TRANSCRIPT
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
1/23
Lp trnh hng i tngJava kt ni c s d liu
Ging vin: TS. Nguyn Mnh Hng
Hc vin Cng ngh Bu chnh Vin thng (PTIT)
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
2/23
2
Ni dung
Kt ni vi DB bng JDBC Chun b cu lnh QSL Ly kt qu ra x l Lm vic vi transaction Bi tp
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
3/23
Kt ni DB bng JDBC
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
4/23
4
Kt ni bng JDBC (1)
public Connection getConnection(String dbClass, String dbUrl)throws SQLException {
Connection conn = null;
try {Class.forName(dbClass);
Connection conn = DriverManager.getConnection (dbUrl);}catch(ClassNotFoundException e) {
e.printStackTrace();}catch(SQLException e) {
throws e;}
returnconn;}
String dbClass = "com.mysql.jdbc.Driver";String dbUrl =
"jdbc:mysql://your.database.domain/yourDBname";
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
5/23
5
Kt ni bng JDBC (2)public Connection getConnection(String dbClass, String dbUrl, StringuserName, Stringpassword) throws SQLException {
Connection conn = null;
try {Class.forName(dbClass);
Connection conn =DriverManager.getConnection (dbUrl, userName, password);}catch(ClassNotFoundException e) {
e.printStackTrace();}catch(SQLException e) {
throws e;}
returnconn;}
String dbClass = "com.mysql.jdbc.Driver";String dbUrl =
"jdbc:mysql://your.database.domain/yourDBname";
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
6/23
Chun b cu lnh SQL
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
7/237
Dng Statement (1)
String query = "Select * FROM users";
String query = "INSERT INTO users VALUES( aaa , bbb )";
String query = "UPDATE password FROM users WHERE id = 111VALUE( ccc )";
String query = "DELETE FROM users HERE id = 111";
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
8/238
Dng Statement (2)
try {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(query);
}catch(ClassNotFoundException e) {e.printStackTrace();
}catch(SQLException e) {e.printStackTrace();
}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
9/239
Dng PreparedStatement
PreparedStatement updateSales = null;
String updateString = "update products " + "set SALES = ? where ID = ?";
try { updateSales = conn.prepareStatement(updateString);
updateSales.setInt(1,value); updateSales.setInt(2,productId); updateSales.executeUpdate();
} catch (SQLException e ) { throw e;
}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
10/2310
Bi tp
C hai bng CSDL:
Transaction: productId (int), value (int),dateOfTransaction(Date)
Sumary: productId (int), totalValue (int)
Vit cc preparedStatement cho vic tm cc giao dchc gi tr >= X
Vit cc preparedStatement cho vic tm cc sn phmc gi tr giao dch trung bnh >= X
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
11/2311
Dng StoreProcedure (1)
String createProcedure ="create procedure GET_MAX_OF_SALE(IN productId int,
OUT value int) " + "begin " +
"select MAX(value) into productValue " + "from products " + "where ID = productId; " + "select productValue; " + "end";
try {Statement stmt = conn.createStatement(); stmt.executeUpdate(createProcedure);
} catch (SQLException e ) { throw e;
}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
12/2312
Dng StoreProcedure (2)
try {CallableStatement cs =
conn.prepareCall("{call SHOW_MAX_OF_SALE(?,?)}");ResultSet rs = cs.executeQuery();
cs.setInt(1, productId);cs.registerOutParameter(2, Types.INT);cs.executeQuery();
int maxValue = cs.getInt(2);
} catch (SQLException e ) {
throw e;}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
13/2313
Bi tp
C hai bng CSDL:
Transaction: productId (int), value (int),dateOfTransaction(Date)
Sumary: productId (int), totalValue (int)
Vit cc storeProcedure cho vic tm cc giao dch c gitr >= X
Vit cc storeProcedure cho vic tm cc sn phm cgi tr giao dch trung bnh >= X
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
14/23
Ly d liu ra
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
15/2315
D liu t ResultSet (1)
String query = "Select * FROM users";
try {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {System.out.println(rs.getString(1));
}}catch(SQLException e) {
e.printStackTrace();}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
16/2316
D liu t ResultSet (2)
String query = "Select * FROM users";
try {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(query);
// get number of row in resultSetint rowcount = 0;if (rs.last()) {
rowcount = rs.getRow();rs.beforeFirst(); // not rs.first()
}
while (rs.next()) { // do something with data...
}}catch(SQLException e) {
e.printStackTrace();
}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
17/23
17
Bi tp
C hai bng CSDL:
Transaction: productId (int), value (int),dateOfTransaction(Date)
Sumary: productId (int), totalValue (int)
Vit chng trnh y cho vic tm cc giao dch cgi tr >= X
Vit chng trnh y cho vic tm cc sn phm cgi tr giao dch trung bnh >= X
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
18/23
Lm vic vi Transaction
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
19/23
19
iu khin ch commit (1)
try { conn.setAutoCommit(false);
....
conn.commit();
} catch (SQLException e ) { if (conn != null) { try { conn.rollback();
} catch(SQLException excep) { throw excep;
}}
throw e;} finally {
conn.setAutoCommit(true);}
}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
20/23
20
iu khin ch commit (2)publicvoidupdateSales(int productId, int value) throwsSQLException {
PreparedStatement updateSales = null;PreparedStatement updateTotal = null;
String updateString = "update products " + "set SALES = ? where ID = ?";
String updateStatement = "update totalSale " + "set TOTAL = TOTAL + ? where productId = ?";
try {
conn.setAutoCommit(false);updateSales = conn.prepareStatement(updateString);updateTotal = conn.prepareStatement(updateStatement);
updateSales.setInt(1, value);updateSales.setInt(2, productId);updateSales.executeUpdate();
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
21/23
21
iu khin ch commit (3)
updateTotal.setInt(1, value);updateTotal.setInt(2, productId);updateTotal.executeUpdate();
conn.commit();} catch (SQLException e ) {
if (conn != null) {
try { conn.rollback();
} catch(SQLException excep) { throw excep;
}}
throw e;} finally { if (updateSales != null) { updateSales.close(); } if (updateTotal != null) { updateTotal.close(); } conn.setAutoCommit(true);
}}
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
22/23
22
Bi tp
Ci t mt ng dng CSDL Vit hm main cho phn phn ci t
-
8/3/2019 b09 LTHDT Java Ket Noi Co So Du Lieu
23/23
Questions?