Java connections SQL database frequently used operations _java

No more nonsense, directly to share Java operations SQL database common connectivity problems.

1. Connect, inquire, update, close

These several data base operations, so put together, write a model of the tool class, that is, the MODEL2 mode of SQL tools. Here I would like to add other operations, such as transaction processing, but at present has not thought of a more perfect method, specific look at the code, the note is very detailed

Import java.sql.*;
Import Java.util.Iterator;
Import Java.util.Map;
Import Java.util.Set;
/** * Created by nl101 on 2016/1/29.
* * public class Sqlbean {//initialization operations are written in front Connection conn = null;
PreparedStatement PS =null;
ResultSet rs = null;
String drivername = "Oracle.jdbc.driver.OracleDriver";
String url = "Jdbc:oracle:thin: @localhost: 1521:ORCL";
String userName = "SCOTT";
String PassWord = "123456"; /** * Initializes the connection, obtains conn/public Sqlbean () {try {class.forname (drivername), conn = Drivermanager.getconnection (Url,username,
PassWord);
catch (ClassNotFoundException e) {e.printstacktrace ();
SYSTEM.ERR.PRINTLN ("Database link exception");
catch (SQLException e) {e.printstacktrace ();
SYSTEM.ERR.PRINTLN ("Database link exception");  }/* The function of the transaction is missing/*/** * CREATE DATABASE update function * @param SQL corresponding UPDATE SQL statement * @param params need additional parameters * @return True update success false update failed/public Boolean update (String sql,string[] params) {int k = 0; try {PS = conn.preparestatement (sql); for (int i = 0; i < para Ms.length; i++) {//Here is the ps.setstring that sets the parameter starting from 1 (i+1,paRams[i]);
} k = Ps.executeupdate ();
catch (SQLException e) {e.printstacktrace ();
SYSTEM.ERR.PRINTLN ("Database update exception");
Return k>0?true:false; /** * Database Query function * @param sql to query the QSL statement * @param params additional parameters * @return Query result set/public ResultSet query (String sql,string[)
params) {try {PS = conn.preparestatement (sql); for (int i = 0; i < params.length; i++) {ps.setstring (i+1,params[i));
rs = Ps.executequery ();
catch (SQLException e) {e.printstacktrace ();
SYSTEM.ERR.PRINTLN ("database query exception");
} return RS;  /** * Shut down database statement/public void Close () {try {if (rs!=null) Rs.close (); rs = null; if (ps!=null) ps.close (); ps = null; if (conn!=null) conn.close ();
conn = null;
catch (SQLException e) {e.printstacktrace ();}} }

Written in this way, other classes can be invoked according to the following methods.

Sqlbean Sqlbean = new Sqlbean ();
String[] params={};//If there is a parameter, write it
ResultSet rs = sqlbean.query ("Select ename from emp", params);//sql statement
//Loop out
try {while
(Rs.next ()) {
System.out.println (rs.getstring (1));
}
catch (SQLException e) {
E.printstacktrace ();
Sqlbean.close ()///exception closes connection
}
sqlbean.close ()//Close database connection

2. Handling of transactions

Transaction processing, always first cancel autocommit, and then execute the command, the last commit, and then the exception is rolled back, as to how to write a method, temporarily not think of a good way

Import Bean.
Sqlbean;
Import java.sql.*;
/** * Created by nl101 on 2016/1/29.
*/public class Test {public static void main (string[] args) {Connection conn = null;
Statement PS =null;
String drivername = "Oracle.jdbc.driver.OracleDriver";
String url = "Jdbc:oracle:thin: @localhost: 1521:ORCL";
String userName = "SCOTT";
String PassWord = "7946521"; try {class.forname (drivername); conn = Drivermanager.getconnection (Url,username,password); Conn.setautocommit (False
)//Cancel the automatic submit PS = Conn.createstatement () first;
Ps.addbatch ("Statement 1 to be manipulated");
Ps.addbatch ("Statement 2 to be manipulated");
Ps.addbatch ("Statement 3 to be manipulated");
Ps.addbatch ("Statement 4 to be manipulated"); Ps.executebatch ();//Commit the above command Conn.commit ()//COMMIT TRANSACTION Conn.setautocommit (TRUE);//Open autocommit} catch (ClassNotFoundException E
) {e.printstacktrace ();
SYSTEM.ERR.PRINTLN ("Database link exception");
catch (SQLException e) {e.printstacktrace ();
SYSTEM.ERR.PRINTLN ("Transaction handling Exception"); try {if (conn!=null) {conn.rollback ();/rollback operation Conn.setautocommit (true);} catch (SQLException E1) {e1.printstacktrace ()
; }}finally {//Last offClosed database try {if (rs!= null) Rs.close (); rs = null; if (PS!= null) ps.close (); ps = null; if (conn!= null) Conn.close (); c
Onn = null;
catch (SQLException e) {e.printstacktrace ();}} }
}

3. Call the stored procedure

Call = Ct.preparecall ("{Call Sp_pro4 (?,?,?,?,?,?)}");
Set Input Parameters
call.setstring (1, "EMP");
Call.setint (2, 4);
Call.setint (3, 1);
Set output parameter
call.registeroutparameter (4, oracletypes.number);
Call.registeroutparameter (5, oracletypes.number);
Call.registeroutparameter (6, oracletypes.cursor);
Implementation of
Call.execute ();
Total output and total number of pages
System.out.println ("Total Records" +call.getint (4)
+ "--Total Pages" +call.getint (5));
Cyclic removal table
ResultSet rs = (ResultSet) call.getobject (6);
while (Rs.next ()) {for
(int i = 0; i < 7; i++) {
System.out.print (rs.getstring (i+1) + "");
}
System.out.println ();
}

4. Removable result set

Sun simply provides the interface. Can you do this, depending on whether the JDBC driver you're referencing supports

Import java.sql.*;
public class Testscroll {public
static void Main (String args[]) {
try {
new Oracle.jdbc.driver.OracleDriver ();
String url = "JDBC:ORACLE:THIN:@192.168.0.1:1521:SXT";
Connection conn = DriverManager
. getconnection (URL, "Scott", "Tiger");
Statement stmt = conn.createstatement (
resultset.type_scroll_insensitive,//Set this RS to scroll
resultset.concur_ READ_ONLY)//Set this RS to read-only
ResultSet rs = stmt
. ExecuteQuery ("SELECT * from emp order by Sal");
Rs.next ()//Normal down line
System.out.println (Rs.getint (1));
Rs.last ();//pointing to the last line of
System.out.println (rs.getstring (1));
System.out.println (Rs.islast ());
System.out.println (Rs.isafterlast ());
System.out.println (Rs.getrow ());
Rs.previous (),//Move up one line
System.out.println (rs.getstring (1));
Rs.absolute (6);//The method is directly positioned to the line number
System.out.println (rs.getstring (1));
Rs.close ();
Stmt.close ();
Conn.close ();
} catch (SQLException e) {
e.printstacktrace ();}}}

5. Updatable result Sets

Import java.sql.*;
public class Testupdatars {public
static void Main (String args[]) {
try{
new Oracle.jdbc.driver.OracleDriver ();
String url= "JDBC:ORACLE:THIN:@192.168.0.1:1521:SXT";
Connection conn=drivermanager.getconnection (URL, "Scott", "Tiger");
Statement stmt=conn.createstatement (resultset.type_scroll_insensitive,resultset.concur_updatable);
ResultSet rs=stmt.executequery ("SELECT * from EMP2");
Rs.next ();
Update One row
of data rs.updatestring ("ename", "AAAA");
Rs.updaterow ();
Inserts a new row
rs.movetoinsertrow ();
Rs.updateint (1, 9999);
Rs.updatestring ("ename", "AAAA");
Rs.updateint ("Mgr", 7839);
Rs.updatedouble ("Sal", 99.99);
Rs.insertrow ();
Move the cursor to the newly created row
rs.movetocurrentrow ();
Delete Row
Rs.absolute (5);
Rs.deleterow ();
Cancel Update
//rs.cancelrowupdates ();
} catch (SQLException e) {
e.printstacktrace ();}}}

The above is a small set for everyone to share the Java connection SQL database often used in the operation, I hope to help.