JDBC

The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

1. Can create multiple Indexes on the same set of columns in SQL Server. In Oracle it throws the error Error: ORA-01408: such column list already indexed



1. URL+UserID + Pwd + DriverClass -> DataSource -> Connection -> JdbcTemplate -> Query / PreparedStatementCreator (This interface creates a PreparedStatement given a connection) -> ResultSet -> ResultSetExtractor -> RowMapper

Connection.createStatement()  - Static SQL;
Connection.prepareStatement(String sql) - Parameterised SQL ( with ? marks )
Connection.prepareCall(String sql) - StoredProcs

Drivermanager.public static Connection getConnection(String url,Properties info)

1. Class.forName("oracle.jdbc.driver.OracleDriver"); // Load Driver Class
2. Driver myDriver = new oracle.jdbc.driver.OracleDriver(); // Create Driver Instance
3. DriverManager.registerDriver( myDriver ); // Register Instance
4. Get Connection
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS); / DriverManager.getConnection(String url);
5. Connection.createStatement() / prepareCall(String sql) / prepareStatement(String sql)
6. boolean Statement.execute(Sql) // int executeUpdate(Sql) // ResultSet executeQuery(Sql)
6. conn.close();


Transactions
Default is AutoCommit. Remove AutoCommit for Distributed Transactions/ More Control
conn.setAutoCommit(false);
conn.commit( );
conn.rollback( );

Batch SQL
String SQL = "INSERT INTO Employees (id, first, last, age)  VALUES(201,'Raj', 'Kumar', 35)";
stmt.addBatch(SQL);

String SQL = "UPDATE Employees SET age = 35 WHERE id = 100";
stmt.addBatch(SQL);

// Create an int[] to hold returned values
int[] count = stmt.executeBatch();


pstmt.addBatch();
pstmt.addBatch();
pstmt.executeBatch();


PlatformTransactionManager | DataSourceTransactionManager