Operations often used in Java to connect to Sql databases

I won’t say much nonsense, I will share with you the common connection problems of Java operating SQL databases.

1. Connect, query, update, close

These basic data operations are put together and written into a tool-like model, which is the model2 model SQL tool. I originally wanted to add other operations, such as transaction processing, but I haven't thought of a more perfect method yet. Please look at the code in detail. The comments are 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 the previous 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 the connection and get 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 to handle transactions is missing*//*** Create database update function* @param sql Corresponding update sql statement* @param params Additional parameters required* @return true Update 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++) {//Here is 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 qsl statement to query* @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 like this, if you call other classes, you can follow the following method.

 SQLBean sqlBean = new SQLBean();String[] params={};//If there is a parameter, write it in 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();//Close the connection if exception}sqlBean.close();//Close the database connection

2. Transaction processing

When processing transactions, the automatic submission is always canceled first, then the command is executed, and the final submission is submitted, and then the exception occurs when rolling back. As for how to write a method, I haven't thought of a good idea yet.

 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 submission first ps = conn.createStatement();ps.addBatch("Sentence 1 to operate");ps.addBatch("Sentence 2 to operate");ps.addBatch("Sentence 3 to operate");ps.addBatch("Sentence 4 to operate");ps.executeBatch();//Submit the above command conn.commit();//Submit the transaction conn.setAutoCommit(true);//Enable automatic submission} catch (ClassNotFoundException e) {e.printStackTrace();System.err.println("Database Link Exception");} catch (SQLException e) {e.printStackTrace();System.err.println("Transaction Processing Exception");try {if (conn!=null){conn.rollback();//Rolling back operation conn.setAutoCommit(true);}} catch (SQLException e1) {e1.printStackTrace();}} finally {//Finish 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 stored procedures

 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);//Execute call.execute();//The total number of outputs and total pages System.out.println("Total number of records"+call.getInt(4)+"--Total number of pages"+call.getInt(5));//Loop out the 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. Whether it can be operated depends on whether the JDBC driver you reference supports it.

 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 to scroll ResultSet.CONCUR_READ_ONLY);//Set the rs to read-only ResultSet rs = stmt.executeQuery("select * from emp order by sal");rs.next();//Move down one line System.out.println(rs.getInt(1));rs.last();//Point to the last line System.out.println(rs.getString(1));Syst.out.println(rs.isLast());Syst.out.println(rs.isAfterLast());Syst em.out.println(rs.getRow());rs.previous();//Move a line up System.out.println(rs.getString(1));rs.absolute(6);//This method directly locates the line number System.out.println(rs.getString(1));rs.close();stmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}}}

5. Updateable 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 of data 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();//Move the cursor to the newly created line rs.moveToCurrentRow();//Delete the line rs.absolute(5);rs.deleteRow();//Cancel update//rs.cancelRowUpdates();}catch(SQLException e){e.printStackTrace();}}}

The above is the operations that I often use to connect to Sql databases in Java that the editor shares with you. I hope it will be helpful to you.