JDBC

java

http://www.javabeat.net/introduction-to-spring-jdbc-framework/
http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html
http://javatechig.com/java/spring/introduction-to-spring-jdbc
http://www.java4s.com/spring/spring-jdbc-complete-introduction/
http://www.theserverside.com/news/1364527/Introduction-to-the-Spring-Framework
http://examples.javacodegeeks.com/enterprise-java/spring/jdbc/spring-jdbctemplate-example/
http://hmkcode.com/spring-framework-jdbc-jdbctemplate/
http://www.techfaq360.com/tutorial/spring/JdbcTemplate.jsp
http://www.javatpoint.com/example-of-PreparedStatement-in-Spring-JdbcTemplate
https://dzone.com/tutorials/java/spring/spring-jdbc-tutorial-1.html
https://examples.javacodegeeks.com/enterprise-java/spring/jdbc/spring-jdbctemplate-example/
http://www.java2s.com/Tutorial/Java/0417__Spring/PreparedStatementCreatorAndPreparedStatementSetter.htm
http://forum.spring.io/forum/spring-projects/data/83088-getting-jdbc-template-to-work-with-prepared-statements

getMetaData
http://stackoverflow.com/questions/16870615/extracting-resultsetmetadata-from-spring-jdbctemplate
http://docs.spring.io/spring/docs/2.5.3/reference/jdbc.html
http://techieme.in/techieme/extracting-resultsetmetadata-from-spring-jdbctemplate/
http://www.heimetli.ch/jdbc/JDBCMetaData.html
http://jdbi.org/

// JDBC & Spring JDBC:

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;

private static final ConcurrentHashMap<String, DataSource> dataSources = new ConcurrentHashMap<>();

Context ctx = null;
// Tomcat
// ctx = (Context)(new InitialContext()).lookup("java:/comp/env");

Hashtable<Object, Object> ht = new Hashtable<Object, Object>();
ht.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
ctx = new InitialContext(ht);

strDS = "...";
String dsName = StringUtils.isEmpty(strDS) ? DEFAULT_DS_NAME : strDS;
DataSource ds = (DataSource)ctx.lookup(dsName);
dataSources.put(dsName, ds);

ds.getConnection();

import javax.sql.*

// Query the database and produce a JSON array:
QJdbcTemplate qjdbc = new QJdbcTemplate(new DBMSConnection().getDataSource("QxploreDataSource"), 
    QGlobals.debugger,    "QPasswordSecurityWindowDAO: getSecurityQuestions: ");
String sql = "SELECT SECURITYQUESTIONSID,QUESTION FROM QXPLORE.SECURITYQUESTIONS";
List questionsList = qjdbc.QueryForList(sql);
JSONArray jsonResult = new JSONArray();
for (int i=0; i < questionsList.size(); i++) {
    Map m = (Map)questionsList.get(i);
    JSONArray jsonArray = new JSONArray();
    jsonArray.put(m.get("SECURITYQUESTIONSID"));
    jsonArray.put(m.get("QUESTION"));
    jsonResult.put(jsonArray);
}
return jsonResult.toString();

// Using prepare statement:
PreparedStatement pstmt = null;
Clob myClob = this.con.createClob();
Writer clobWriter = myClob.setCharacterStream(1);
String str = this.readFile(fileName, clobWriter);
System.out.println("Wrote the following: " + clobWriter.toString());

if (this.settings.dbms.equals("mysql")) {
    System.out.println("MySQL, setting String in Clob " + "object with setString method");
    myClob.setString(1, str);
}
System.out.println("Length of Clob: " + myClob.length());

String sql = "INSERT INTO COFFEE_DESCRIPTIONS  VALUES(?,?)";

pstmt = this.con.prepareStatement(sql);
pstmt.setString(1, coffeeName);
pstmt.setClob(2, myClob);
pstmt.executeUpdate();
if (pstmt != null)pstmt.close();

// Using prepare statement:
PreparedStatement st = db.conn.prepareStatement("INSERT INTO TODO(DESCRIPTION,IS_DONE) VALUES(?,?)");
st.setString(1, “some value”);
st.setBoolean(2, true);
st.execute();
Statement st = db.conn.createStatement();
ResultSet rs = st.executeQuery(“SELECT * FROM TODO”);
while (rs.next()) {
  int id = rs.getInt(1)
  String whatever = rs.getString(2);
  boolean b = rs.getBoolean(3);
}
st.close();

// Insert CLOB or BLOG into a table:
PreparedStatement pstmt = null;
Clob myClob = this.con.createClob();
Writer clobWriter = myClob.setCharacterStream(1);
String str = this.readFile(fileName, clobWriter);
System.out.println("Wrote the following: " + clobWriter.toString());

if (this.settings.dbms.equals("mysql")) {
    System.out.println("MySQL, setting String in Clob " + "object with setString method");
    myClob.setString(1, str);
}
System.out.println("Length of Clob: " + myClob.length());

String sql = "INSERT INTO COFFEE_DESCRIPTIONS  VALUES(?,?)";
pstmt = this.con.prepareStatement(sql);
pstmt.setString(1, coffeeName);
pstmt.setClob(2, myClob);
pstmt.executeUpdate();
if (pstmt != null)pstmt.close();

// Adding and retrieving BLOB SQL objects is similar to adding and retrieving 
// CLOB SQL objects. Use the Blob.setBinaryStream method to retrieve an OutputStream 
// object to write the BLOB SQL value that the Blob Java object (which called the method) 
// represents.

// Reading a clob or blob from the database:
String description = null;
Clob myClob = null;
PreparedStatement pstmt = null;
String sql = "select COF_DESC from COFFEE_DESCRIPTIONS where COF_NAME = ?";
pstmt = this.con.prepareStatement(sql);
pstmt.setString(1, coffeeName);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    myClob = rs.getClob(1);
    System.out.println("Length of retrieved Clob: " + myClob.length());
}
description = myClob.getSubString(1, numChar);
if (pstmt != null) pstmt.close();

// Release resources held by large objects:
// Blob, Clob, and NClob Java objects remain valid for at least the duration of the 
// transaction in which they are created. This could potentially result in an 
// application running out of resources during a long running transaction. 
// Applications may release Blob, Clob, and NClob resources by invoking their 
// free method.
Clob aClob = con.createClob();
int numWritten = aClob.setString(1, val);
aClob.free();

// The differences between a prepared statement and a normal statement: with the 
// prepared statement, the database will cache the statement for subsequent uses.

// The execute method returns true if the statement was executed successfully, 
// or false if the statement was not executed successfully. The executeQuery 
// method return the result set.

// The executeUpdate statement returns the number of rows that were updated.

// The setMaxRows() method is used to limit the number of rows that are returned. 
// The setFetchSize() set the number of records that will be fetch from the database. 
// I think setFetchSize is a child of setMaxRows.

// We can execute a batch of queries using JDBC by using addBatch, and executeBatch

this.dbtemplate = new QJdbcTemplate(
    DBMSConnection.getDataSource("SupportDataSource"), 
    QGlobals.debugger,
    "NagiosChecks: processData");

String sql = "SELECT * FROM SUPPORT.LAST_PRODUCTION_TO_TRAINING 
    WHERE LAST_COPIED_TIME < SYSDATE - INTERVAL '30' MINUTE";
List list = this.dbtemplate.QueryForList(sql);
boolean lagging = false;
if (list.size() > 0) {
        lagging = true;
}        
sql = "SELECT * FROM SUPPORT.SYNC_DATA_SQL WHERE ADDDATE < SYSDATE - INTERVAL '30' MINUTE";
list = this.dbtemplate.QueryForList(sql);
if (list.size() > 0) {
        lagging = true;
}

SELECT * FROM SUPPORT.ACTIVATIONSCHEDULE WHERE STATUS != 'C' AND 
    RELEASE_DATE < SYSDATE - INTERVAL '8' HOUR AND 
    ADDDATE < SYSDATE - INTERVAL '1' HOUR

SELECT * FROM SUPPORT.QSUP_PROVIDER_INFO_UPLOAD WHERE (STATUS='P') AND 
    (TO_DATE(TRUNC(UPLOAD_DATE)) + 1 < SYSDATE - INTERVAL '8' HOUR)

SELECT * FROM SUPPORT.HOMEPAGE_PUBLISH_SCHEDULE WHERE STATUS != 'C' AND 
    RELEASEDATE < SYSDATE - INTERVAL '8' HOUR

SELECT * FROM SUPPORT.GENERIC_FILE_UPLOAD WHERE 
    STATUS IN ('UPLOADED','PARSING STARTED') AND MODDATE < SYSDATE - INTERVAL '1' HOUR

SELECT * FROM SUPPORT.GENERIC_FILE_UPLOAD WHERE STATUS IN ('PARSING COMPLETED') AND 
    MODDATE < SYSDATE - INTERVAL '24' HOUR

// Turn on autocommit mode
JdbcTemplate jdbc = new JdbcTemplate(new DBMSConnection().getDataSource(...));
jdbc.getDataSource().getConnection().setAutoCommit(true);

// Commit if we have turned off auto-commit:
JdbcTemplate jdbc = new JdbcTemplate(new DBMSConnection().getDataSource(...));
...
jdbc.getDataSource().getConnection().commit();

// Perform an insert or update:
String sql = "INSERT INTO tableName(col1, col2) VALUES(?,?)";
jt.update(sql, new Object[]{val1, val2});

// Query for String
strSQL = " select ENTITIESID from qxplore.entities where FACILITY_ABBREVIATION = ? ";
entitiesId = jt.queryForObject(strSQL,new Object[]{facAbbr},String.class).toString();

// Query for int:
sql = "SELECT COUNT(*) FROM QXPLORE.ENTITIES_PROJECTS WHERE ENTITIESID=? AND PROJECTSID=806";
int count = jt.queryForInt(sql, new Object[]{entitiesID});

List listTempResult = qjdbc.QueryForList(sql, new Object[]{projectsid,projectsid});
// Iterate over the resultset from the above SQL statement, retrieve the corresponding 
// HashMap for each facility from the otherDataMap variable, and put the value for 
// IS_REDESIGN, STATUS, and ADDDATE into this HashMap.  Perhaps, we will convert 
// otherDataMap to JSON
Iterator lstItr = listTempResult.iterator();
while (lstItr.hasNext()) {
    m = (Map) lstItr.next();
    strEntitiesID = m.get("ENTITIESID").toString();
    String status = m.get("STATUS").toString();
    String adddate = this.returnEmptyStringIfNull(m,"ADDDATE");
    String redesign = m.get("IS_REDESIGN").toString();
    hm = (HashMap) otherDataMap.get(strEntitiesID);
    hm.put("STATUS",status);
    hm.put("ADDDATE", adddate);
    hm.put("IS_REDESIGN", redesign);
}

// Using toArray:
sql = "INSERT INTO QXPLORE.ENTITIES_SERVICES(" + 
    this.join(columnNames,",") + ") VALUES(" + 
    this.join(insertMarkers, ",") + ")"; 
jt.update(sql, columnValues.toArray());                    
sql = "UPDATE QXPLORE.ENTITIES_SERVICES SET " + this.join(updateFragments, ",") + 
    " WHERE ENTITIESID=" + entitiesID;
jt.update(sql, columnValues.toArray());

How can we turn on autocommit mode?

//Instantiating JdbcTemplate
JdbcTemplate jdbc = new JdbcTemplate(new DBMSConnection().getDataSource(...));
jdbc.getDataSource().getConnection().setAutoCommit(true);

How can we commit if we have auto-commit turned off?

JdbcTemplate jdbc = new JdbcTemplate(new DBMSConnection().getDataSource(...));
...
jdbc.getDataSource().getConnection().commit();

How can we perform an insert or update?

String sql = "INSERT INTO tableName(col1, col2) VALUES(?,?)";
jt.update(sql, new Object[]{val1, val2});

How can we query for String?

strSQL = " select ENTITIESID from qxplore.entities where FACILITY_ABBREVIATION = ? ";
entitiesId = jt.queryForObject(strSQL,new Object[]{facAbbr},String.class).toString();

How can we query for Int?

String sql = "SELECT COUNT(*) FROM QXPLORE.ENTITIES_SERVICES WHERE ENTITIESID=" + entitiesID;
int found = jt.queryForInt(sql);

sql = "SELECT COUNT(*) FROM QXPLORE.ENTITIES_PROJECTS WHERE ENTITIESID=? AND PROJECTSID=806";
int ace = jt.queryForInt(sql, new Object[]{entitiesID});

How can we iterate over the result of a query?

String sql = "...";
QJdbcTemplate qjdbc = new QJdbcTemplate(DBMSConnection.getDataSource("SupportDataSource"), QGlobals.debugger,    "FacilitySearchDAO: getProductActivationInfo: ");
List listTempResult = qjdbc.QueryForList(sql, new Object[]{projectsid,projectsid});

/*
 * Iterate over the resultset from the above SQL statement, retrieve the corresponding HashMap for each facility from the otherDataMap 
 * variable, and put the value for IS_REDESIGN, STATUS, and ADDDATE into this HashMap
 */
Iterator lstItr = listTempResult.iterator();
while (lstItr.hasNext()) {
    m = (Map) lstItr.next();
    strEntitiesID = m.get("ENTITIESID").toString();
    String status = m.get("STATUS").toString();
    String adddate = this.returnEmptyStringIfNull(m,"ADDDATE");
    String redesign = m.get("IS_REDESIGN").toString();
    hm = (HashMap) otherDataMap.get(strEntitiesID);
    hm.put("STATUS",status);
    hm.put("ADDDATE", adddate);
    hm.put("IS_REDESIGN", redesign);
}

How can we do an update using array?

sql = "INSERT INTO QXPLORE.ENTITIES_SERVICES(" + 
    this.join(columnNames,",") + ") VALUES(" + 
    this.join(insertMarkers, ",") + ")"; 
jt.update(sql, columnValues.toArray());                    

sql = "UPDATE QXPLORE.ENTITIES_SERVICES SET " + this.join(updateFragments, ",") + " WHERE ENTITIESID=" + entitiesID;
jt.update(sql, columnValues.toArray());

How can we get the column names from the result?

try {
    con = DBMSConnection.getConnection("ClaimsReportsPoolEDB");
    ResultSetMetaData rsMeta;
    ResultSet rs = null;
    Statement stmt = null;
    stmt = con.createStatement();
    rs = stmt.executeQuery(strSQL);
    rsMeta = rs.getMetaData();
    int iCount = rsMeta.getColumnCount();
    for(int z = 1; z <= iCount; z++) {
        tempColumnName = rsMeta.getColumnName(z);
        if(tempColumnName.lastIndexOf(":") > 0) {
            tempColumnName = tempColumnName.substring(0, tempColumnName.lastIndexOf(":")) + "?";
        }
    }
} catch (Exception e) {
    DBMSConnection.safeClose(rs, stmt, con);
}

Query the database, obtain the result as a list, and iterate through it:

String schema, String table;
String sql = "SELECT COLUMN_NAME,DATA_TYPE FROM ALL_TAB_COLUMNS where TABLE_NAME=? AND OWNER=? ORDER BY COLUMN_NAME";
List columnNames_db = this.productionJdbcTemplate.queryForList(sql, new Object[]{table,schema});
for (int i = 0; i < columnNames_db.size(); i++) {
  m = (Map) columnNames_db.get(i);
  columnName = m.get("COLUMN_NAME").toString();
  dataType = m.get("DATA_TYPE").toString();
}

How can we query the database and produce a JSON array?

QJdbcTemplate qjdbc = new QJdbcTemplate(new DBMSConnection().getDataSource("QxploreDataSource"), 
    QGlobals.debugger,    "QPasswordSecurityWindowDAO: getSecurityQuestions: ");
String sql = "SELECT SECURITYQUESTIONSID,QUESTION FROM QXPLORE.SECURITYQUESTIONS";
List questionsList = qjdbc.QueryForList(sql);
JSONArray jsonResult = new JSONArray();
for (int i=0; i < questionsList.size(); i++) {
    Map m = (Map)questionsList.get(i);
    JSONArray jsonArray = new JSONArray();
    jsonArray.put(m.get("SECURITYQUESTIONSID"));
    jsonArray.put(m.get("QUESTION"));
    jsonResult.put(jsonArray);
}
return jsonResult.toString();

How can we use prepare statement?

PreparedStatement pstmt = null;
Clob myClob = this.con.createClob();
Writer clobWriter = myClob.setCharacterStream(1);
String str = this.readFile(fileName, clobWriter);
System.out.println("Wrote the following: " + clobWriter.toString());

if (this.settings.dbms.equals("mysql")) {
    System.out.println("MySQL, setting String in Clob " + "object with setString method");
    myClob.setString(1, str);
}
System.out.println("Length of Clob: " + myClob.length());

String sql = "INSERT INTO COFFEE_DESCRIPTIONS  VALUES(?,?)";

pstmt = this.con.prepareStatement(sql);
pstmt.setString(1, coffeeName);
pstmt.setClob(2, myClob);
pstmt.executeUpdate();
if (pstmt != null)pstmt.close();

How can we insert CLOB or BLOB into a table?

PreparedStatement pstmt = null;
Clob myClob = this.con.createClob();
Writer clobWriter = myClob.setCharacterStream(1);
String str = this.readFile(fileName, clobWriter);
System.out.println("Wrote the following: " + clobWriter.toString());

if (this.settings.dbms.equals("mysql")) {
    System.out.println("MySQL, setting String in Clob " + "object with setString method");
    myClob.setString(1, str);
}
System.out.println("Length of Clob: " + myClob.length());

String sql = "INSERT INTO COFFEE_DESCRIPTIONS  VALUES(?,?)";
pstmt = this.con.prepareStatement(sql);
pstmt.setString(1, coffeeName);
pstmt.setClob(2, myClob);
pstmt.executeUpdate();
if (pstmt != null)pstmt.close();

Adding and retrieving BLOB SQL objects is similar to adding and retrieving CLOB SQL objects. Use the Blob.setBinaryStream method to retrieve an OutputStream object to write the BLOB SQL value that the Blob Java object (which called the method) represents.

How can we read a clob or blob from the database?

public String retrieveExcerpt(String coffeeName, int numChar) throws SQLException {
    String description = null;
    Clob myClob = null;
    PreparedStatement pstmt = null;

    String sql = "select COF_DESC from COFFEE_DESCRIPTIONS where COF_NAME = ?";
    pstmt = this.con.prepareStatement(sql);
    pstmt.setString(1, coffeeName);
    ResultSet rs = pstmt.executeQuery();

    if (rs.next()) {
        myClob = rs.getClob(1);
        System.out.println("Length of retrieved Clob: " + myClob.length());
    }
    description = myClob.getSubString(1, numChar);

    if (pstmt != null) pstmt.close();
    return description;
}

Adding and retrieving BLOB SQL objects is similar to adding and retrieving CLOB SQL objects. Use the Blob.setBinaryStream method to retrieve an OutputStream object to write the BLOB SQL value that the Blob Java object (which called the method) represents.

How can we release resources held by large objects?

Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction. Applications may release Blob, Clob, and NClob resources by invoking their free method.

Clob aClob = con.createClob();
int numWritten = aClob.setString(1, val);
aClob.free();

What does JDBC abbreviate for?

Java Database Connectivity. JDBC is an abstracted API for dealing connecting to different databases. JDBC abstract away the details of connecting to different databases, and perhaps the details of invoking stored procedures, but we still have to make sure that our SQL statements are ANSI-compliant if we wish to switch to a different database in the future.

What are the different type of JDBC drivers?

  1. Type 1: JDBC to ODBC bridge <-> Database Library APIs <-> Databases
  2. Type 2: Native-API driver <-> Database Library APIs <-> Database
  3. Type 3: Network-Protocol driver <-> Middleware <-> Different Databases
  4. Type 4: Pure Java driver <-> Database

These are different types of JDBC drivers. These are not relevant much anymore. We now mostly use the type 4 driver, or pure Java driver.

What are the overall steps to establish a JDBC connection using DriverManager?

  1. Load the driver class
  2. Establish the connection
Class.forName(“org.hsqldb.jdbcDriver”);
conn = DriverManager.getConnection(“jdbc:hsqldb:db_file”,”sa”,””);

What are the classes that we need to import to use JDBC?

import javax.sql.*

Why should we avoid using JDBC directly?

If we use JDBC directly, we have to remember to close the connection. If we use Spring or a data source from the container, Spring manage the connection for us automatically.

How can we do a prepared statement with JDBC?

PreparedStatement st = db.conn.prepareStatement("INSERT INTO TODO(DESCRIPTION,IS_DONE) VALUES(?,?)");
st.setString(1, “some value”);
st.setBoolean(2, true);
st.execute();

Statement st = db.conn.createStatement();
ResultSet rs = st.executeQuery(“SELECT * FROM TODO”);
while (rs.next()) {
  int id = rs.getInt(1)
  String whatever = rs.getString(2);
  boolean b = rs.getBoolean(3);
}
st.close();

What are the differences between a prepared statement and a normal statement?

With the prepared statement, the database will cache the statement for subsequent uses.

What is the difference between the execute method and the executeQuery method?

The execute method returns true if the statement was executed successfully, or false if the statement was not executed successfully. The executeQuery method return the result set.

What is the return value of an executeUpdate statement?

It returns the number of rows that were updated.

What is the purpose of the setFetchSize() and setMaxRows() methods on a statement object?

The setMaxRows() method is used to limit the number of rows that are returned. The setFetchSize() set the number of records that will be fetch from the database. I think setFetchSize is a child of setMaxRows.

How can we execute a batch of queries using JDBC?

Use addBatch, and executeBatch

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License