JDBC Curriculum

Good afternoon.

I am involved in 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 an interview.

I also teach the Scala for Java Developers course on the online education platform udemy.com (similar to Coursera / EdX).

By some thought, it turns out that in 4-5 hours the video (standard format on the platform), the maximum of useful things that can be described in 5-10 minute pieces, falls into the First part (6 topics: “Intro” , “Connect to database” , “ Query database ” , “ ResultSet ” , “ Optimizations ” ,“Transactions” + 35 nested subtopics).

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

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

I know that a reasonable remark is that you can "just read the JDBC 4.2 Specification." Yes you can. This plan is built 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
  • DAO Alternative: 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

Second part

• Local Tx-Manager: by-hands
  • Base implementation: ThreadLocal Tx-context
  • @Transactional annotation
  • AOP implementation of @Transactional
  • Application server = Tx-context + Auth-context + Thread management
• Distributed transactions
  • 2PC-protocol
  • javax.jdbc.xa. * - XA-standart implementation 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 in English is being prepared, the original in Russian has no place to put it yet), on February 27, the author starts a course of 16 Scala for Java Developers webinars (3 months).
Contact:
skype: GolovachCourses
email: GolovachCourses@gmail.com