JDBC Curriculum

Good day.

I do IT education.
At the moment I am preparing a course “JDBC for Java Developer” for one western MOOC platform. I think this detailed plan may be useful to someone to prepare for the interview.

I also teach Scala for Java Developers on the udemy.com online education platform (similar to Coursera / EdX).
')
By some reflection it turns out that in 4-5 hours of video (standard format on the platform), the maximum useful, which can be told in 5-10 minute pieces, falls into the first part (6 topics: “Intro” , “Connect to database” , “ Query database " , " ResultSet " , " Optimizations " , " Transactions " + 35 nested sub-topics).

Valuable, but not fit, went to the second part (3 topics: "ThreadLocal Tx-Manager" , "Distributed Transactions" , "Query Meta-Information" + 10 nested subtopics).

It is expected that the trainee after the course will be ready to start “next level” technologies after JDBC - Spring JDBC Template , JavaEE transactions , ORM .

I know that a reasonable point is that you can "just read the JDBC 4.2 Specification." Yes you can. This plan is constructed as a specification analysis + proposed target use cases ( Dao Patter , TxManager , Connection pooling , ...).

First part

• Intro
  • Install MySQL RDBMS + MySQL Workbench
  • RDBMS vs DB, create database
  • JDBC architecture: JDBC API + JDBC Driver
  • JDBC Driver types, transport types
  • Connector / J: JDBC Driver to MySQL
  • JDBC / SQL versions, SQL dialects
• Connect to database
  • Driver, DriverManager, DataSource
  • Connection
  • JDBC URL
  • Connector / J properties
• Query database
  • DDL and DML (TEXT)
  • Statement
  • Statement.executeUpdate: INSERT, UPDATE, DELETE
  • Get auto-generated keys
  • Statement.executeQuery: SELECT, ResultSet
  • Statement.execute
  • SQLException: errorCode and errorState
  • DAO Pattern
  • Alternative DAO: Transaction Script, Active Record, ORM
• ResultSet
  • ResultSet: positioning and transition
  • ResultSet: type
  • ResultSet: concurrency
  • ResultSet: holdability
• Optimizations
  • PreparedStatement = + precompilation - SQL injection
  • Batch update = vectorization
  • Connection pooling
• Transactions
  • ACID properties
  • Transaction boundaries
  • Savepoints
  • Transaction isolation levels
  • MySQL transactions: MyISAM vs InnoDB
  • READ UNCOMMITED, Dirty Read “phenomena”
  • READ COMMITED, NonRepeatable Read “phenomena”
  • REPEATABLE READ, Phantom Read “phenomena”
  • SERIALIZABLE

The second part of

• Local Tx-Manager: by-hands
  • Base realization: ThreadLocal Tx-context
  • @Transactional annotation
  • AOP realization of @Transactional
  • Application server = Tx-context + Auth-context + Thread management
• Distributed Transactions
  • 2PC-protocol
  • javax.jdbc.xa. * - XA-standart realization of 2PC-client
  • Distribured-Tx Manager Architecture
• Query Meta-Information
  • Database meta-info
  • Table meta-info
  • Row meta-info

PS In addition to preparing this course for a third-party platform (a translation version is being prepared in English, the original in Russian is not yet available), the author will start a course from 16 webinars “Scala for Java Developers” from February 27 (3 months).
Contacts:
skype: GolovachCourses
email: GolovachCourses@gmail.com