Работа с базами данных – JDBC Основы JDBC

1. Работа с базами данных – JDBC Основы JDBC

2. DDL + DCL • CREATE DATABASE test_db; • CREATE USER 'test_db_user'@'%' IDENTIFIED BY 'test_db_password'; • GRANT ALL ON test_db.* TO 'test_db_user'@'%'; • REVOKE ALL ON test_db.* FROM 'test_db_user'@'%'; • DROP USER test_db_user; • DROP DATABASE test_db;

3. DDL • CREATE TABLE test_db.lectors ( id INT NOT NULL AUTO_INCREMENT,first_name VARCHAR(50) NOT NULL DEFAULT "",last_name VARCHAR(50) NOT NULL DEFAULT "", PRIMARY KEY (id)) ENGINE=INNODB; • CREATE TABLE test_db.lessions ( id INT NOT NULL AUTO_INCREMENT, topic VARCHAR(500) NOT NULL DEFAULT "", date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,lector_id INT NOT NULL DEFAULT 0, PRIMARY KEY (id)) ENGINE=INNODB;

4. DML • INSERT INTO test_db.lectors (first_name, last_name)VALUES ('Max', 'Tyukh'); • INSERT INTO test_db.lectors (first_name, last_name)VALUES ('Max', 'Mashnitsky'), ('Eugene', 'Bochkov'), ('Andrew', 'Grigoruk');

5. DML

6. DML • INSERT INTO test_db.lessions (topic, date, lector_id)VALUES ('Intro', '2013-04-23 16:00:00', 1), ('Basic 1', '2013-04-25 16:00:00', 2), ('Basic 2', '2013-04-30 16:00:00', 2), ('Basic 3', '2013-05-07 16:00:00', 2), ('Basic 4', '2013-05-14 16:00:00', 2), ('Basic 5', '2013-05-16 16:00:00', 2))

7. DML

8. DML Connection conn = DriverManager.getConnection( "jdbc:myDriver://localhost:3306/test_db", "test_db_user", "test_db_password");String q = "INSERT INTO test_db.lessions (topic, lector_id) VALUES (?, ?)"; PreparedStatement stmt = conn.prepareStatement(q); for (inti = 1; i <= 4; i++) { stmt.setString(1, "Core " + i); stmt.setInt(2, 3); // ~~ get lector id from DB stmt.addBatch(); } stmt.executeBatch(); conn.close();

9. DML

10. DML CREATE DEFINER = 'test_db_user'@'%' PROCEDURE test_db.AddLesson (IN _topic VARCHAR(500),IN _date TIMESTAMP,IN _lector_id INT) BEGIN INSERT INTO test_db.lessions (topic, date, lector_id) VALUES (_topic, _date, _lector_id); END

11. DML Connection conn = DriverManager.getConnection( "jdbc:myDriver://localhost:3306/test_db", "test_db_user", "test_db_password");String q = "Call AddLession(?, ?)";CallableStatement stmt = conn.prepareCall(q); stmt.setString(1, "Hibernate 1");stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));stmt.setInt(3, 4); stmt.execute();conn.close();

12. DML

13. Q&A