Getting started with JDBC-manipulating SQL statements

Manipulating SQL statements statement, PreparedStatement, CallableStatement

Once the connection is established, you can interact with the database. JDBC Statement , PreparedStatement and provides the CallableStatement relevant APIs for SQL operations. Which CallableStatement inherit from, and PreparedStatement PreparedStatement inherit from Statement . The difference between them is:

• StatementProvides basic SQL operations. is suitable for static SQL statements, and incoming SQL statements cannot accept parameters.
• PreparedStatementArguments can be passed in SQL, suitable for multiple use of SQL statements.
• CallableStatementPL/SQL stored procedures can be called.

Although the interface functions are different, they are used in roughly the same way, in the following steps:

1. Create Statement
2. Execute SQL statement
3. Close Statement

The following methods are commonly used when executing SQL statements:

• boolean execute(String SQL): Returns True if there is ResultSet, or false if it is generated. Typically used for CREATE, ALTER these operations.
• int executeUpdate(String SQL): Returns the number of records affected, typically used for INSERT, UPDATE, DELETE operations.
• ResultSet executeQuery(String SQL): Returns the query result set, the special term SELECT.

The following three examples illustrate how to apply them.

• Statement example.
• PreparedStatement example.
• Statement example.

Batch operations for SQL

SQL batch allows you to add multiple SQL to a statement object and submit the execution results together. This reduces the frequency of communication with SQL. However, SQL batching is not necessarily supported by JDBC requirements. DatabaseMetaData.supportsBatchUpdates() Check support should be used before use.

The SQL batch-related APIs are:

• Statement.addbatch (): Adding SQL statements to the batch
• Statement.executebatch (): Executes the batch and returns an integer array where each element represents the execution result of the corresponding ordinal SQL.
• Statement.clearbatch (): Removes all SQL statements that have been added from the batch.

The following example uses batch processing to add data to a database:

  Public Static voidBatchinsertposts (arraylist<post> posts)throwsSQLException {Connection conn=Getconnectionfromds (dbprops); DatabaseMetaData MD=Conn.getmetadata (); System.out.println ("If Support Batch updates:" +md.supportsbatchupdates ()); String SQL= "INSERT into posts\n" + "VALUES (NULL,?,?, DEFAULT,?)"; PreparedStatement stmt=conn.preparecall (SQL);  for(Post post:posts) {stmt.setstring (1, Post.gettitle ()); Stmt.setstring (2, Post.getcontent ()); Stmt.setboolean (3, Post.isvisible ());        Stmt.addbatch ();        } stmt.executebatch (); Printwarnings (Stmt.getwarnings ()); //Print all warning. See "SQL Exception Handling"Stmt.close ();    Conn.close (); }

SQL Exception Handling

The most common exception in JDBC is SQLException that it is possible to throw this exception either when establishing a connection or when executing an SQL statement. SQLException contains the following information:

• A description of the error. Obtained by a call getMessage() .
• A SQL status code. Gets by calling getSQLState( ) . The SQL status code consists of 5-bit letters and numbers that conform to the XOPEN specification.
• An error code. The meaning of this error code is defined by the implementation, which may be the error code of the database. Gets by calling SQLException.getErrorCode() .
• The cause of the error. The cause of the exception is a chain of one or more Throwable objects. To check for these reasons, recursively traverse sqlexception.getcause () until a null is returned.
• The exception chain. Gets the next exception by Getnextexception ().

The following code example prints each exception in the exception chain SQLException and prints the cause chain for each exception.

     Public Static voidprintsqlexception (SQLException ex) { for(Throwable E:ex) {//Iterator will call Getnextexception ()            if(EinstanceofSQLException)                {e.printstacktrace (System.err); System.err.println ("SQLState:" +((SQLException) e). Getsqlstate ()); System.err.println ("Error Code:" +((SQLException) e). GetErrorCode ()); System.err.println ("Message:" +e.getmessage ()); Throwable T=Ex.getcause ();  while(t! =NULL) {//Print each causeSystem.out.println ("Cause:" +t); T=T.getcause (); }            }        }    }

Except for a fatal error that throws SQLException,   Connection ,   Statement ,   ResultSet   has a   getwarnings ()   method, which returns a   sqlwarning .   SQLWarning   inherit from   SQLException , which can traverse  , SQLException  :

 public  static  void   Printwarnings (sqlwarning warning)  throws   SQLException { while  (Warning! = null  " Message: "+ Warning.getmessage ());            System.out.println ( "SQLState:" + Warning.getsqlstate ());            System.out.print ( "Vendor error code:"  ""  = warning.getnextwarning (); }    }

Getting started with JDBC-manipulating SQL statements