Java connects to the Sql database in a frequently used operation
- 2020-05-05 11:14:22
- OfStack
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.