Java connects to the Sql database in a frequently used operation

Without further ado, I'm going to share some common connection problems with java operations on sql databases.

Connect, query, update, close

These are the basic data operations, so put them together and write them into a tool-like pattern, sql in model2 mode. I wanted to add all the other operations, such as transaction processing, but I haven't thought of a perfect method yet

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 first 
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";
/**
*  Initialize connection , To obtain 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 ");
}
}
/*
 Transaction processing functions are lacking 
*/
/**
*  Create a database update function 
* @param sql  Corresponding update sql statements 
* @param params  Additional parameters are required 
* @return true The update is successful  false Update failed 
*/
public boolean update(String sql,String[] params){
int k = 0;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
// From here 1 Start setting the parameters 
ps.setString(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 statements 
* @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;
}
/**
*  Close 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 this way, other class calls can be made using the following method.

SQLBean sqlBean = new SQLBean();
String[] params={};// If you have an argument, write it in 
ResultSet rs = sqlBean.query("select ename from emp",params);//sql statements 
// Loop out 
try {
while(rs.next()){
System.out.println(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
sqlBean.close();// Exceptions close the connection 
}
sqlBean.close();// Close the database connection 

2. Transaction processing

Transaction processing, always cancel auto commit, then execute the command, last commit, and then an exception is rolled back, as for how to write a method, have not yet thought 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);// First, cancel auto-commit 
ps = conn.createStatement();
ps.addBatch(" Statements that need to be manipulated 1");
ps.addBatch(" Statements that need to be manipulated 2");
ps.addBatch(" Statements that need to be manipulated 3");
ps.addBatch(" Statements that need to be manipulated 4");
ps.executeBatch();// Submit the above command 
conn.commit();// Commit the transaction 
conn.setAutoCommit(true);// Enable auto commit 
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.err.println(" Database link exception ");
} catch (SQLException e) {
e.printStackTrace();
System.err.println(" Transaction exception ");
try {
if (conn!=null){
conn.rollback();// A rollback operation 
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {// Finally close the database 
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();
}
}
}
}

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 parameters 
call.registerOutParameter(4, OracleTypes.NUMBER);
call.registerOutParameter(5, OracleTypes.NUMBER);
call.registerOutParameter(6, OracleTypes.CURSOR);
// perform 
call.execute();
// Total output and total pages 
System.out.println(" The total number of records "+call.getInt(4)
+"-- Total number of pages "+call.getInt(5));
// Loop out 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. Movable result set

sun only provides an interface. It depends on whether the JDBC driver you refer to supports

or not

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 the rs You can roll 
ResultSet.CONCUR_READ_ONLY);// Set the rs As read-only 
ResultSet rs = stmt
.executeQuery("select * from emp order by sal");
rs.next();// Move down one row normally 
System.out.println(rs.getInt(1));
rs.last();// To the last row 
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 a line 
System.out.println(rs.getString(1));
rs.absolute(6);// This method locates the line number directly 
System.out.println(rs.getString(1));
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

5. Updatable result set

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 a row 
rs.updateString("ename","AAAA");
rs.updateRow();
// Insert a new row 
rs.moveToInsertRow();
rs.updateInt(1, 9999);
rs.updateString("ename","AAAA");
rs.updateInt("mgr", 7839);
rs.updateDouble("sal", 99.99);
rs.insertRow();
// Moves the cursor to the newly created line 
rs.moveToCurrentRow();
// Delete rows 
rs.absolute(5);
rs.deleteRow();
// Cancel the update 
//rs.cancelRowUpdates();
}catch(SQLException e){
e.printStackTrace();
}
}
}

Above is the site to share Java connection Sql database often used operations, I hope to help you.