廢話不多說了，直接給大家分享java操作sql數據庫常見的連接問題。
1.連接,查詢,更新,關閉
這幾個數據基礎操作,所以放到一起,寫成一個工具類的模式,也就是model2模式的sql工具.這里本想把其他操作都加進去,比如事務處理,但是目前還沒想到比較完美的方法,具體看代碼吧,注釋很詳細
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
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 {//初始化操作都寫在前面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";/*** 初始化連接,獲得conn*/public SQLBean(){try {Class.forName(driverName);conn = DriverManager.getConnection(url,userName,passWord);} catch (ClassNotFoundException e) {e.printStackTrace();System.err.println("數據庫鏈接異常");} catch (SQLException e) {e.printStackTrace();System.err.println("數據庫鏈接異常");}}/*處理事務的函數欠缺*//*** 創建數據庫更新函數* @param sql 對應的更新sql語句* @param params 需要附加的參數* @return true更新成功 false更新失敗*/public boolean update(String sql,String[] params){int k = 0;try {ps = conn.prepareStatement(sql);for (int i = 0; i < params.length; i++) {//這里是從1開始設置參數的ps.setString(i+1,params[i]);}k = ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();System.err.println("數據庫更新異常");}return k>0?true:false;}/*** 數據庫查詢函數* @param sql 要查詢的qsl語句* @param params 附加參數* @return 查詢結果集*/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("數據庫查詢異常");}return rs;}/*** 關閉數據庫語句*/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();}}} |
寫成這樣,其他類調用的話可以按照下面方法.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQLBean sqlBean = new SQLBean();String[] params={};//如果有參數則寫進去ResultSet rs = sqlBean.query("select ename from emp",params);//sql語句//循環取出try {while(rs.next()){System.out.println(rs.getString(1));}} catch (SQLException e) {e.printStackTrace();sqlBean.close();//異常則關閉連接}sqlBean.close();//關閉數據庫連接 |
2.事務的處理
事務的處理,總是先取消自動提交,然后執行命令,最后提交,然后發生異常則回滾,至于怎么寫成一個方法,暫時還沒想到好辦法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
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);//首先取消自動提交ps = conn.createStatement();ps.addBatch("需要操作的語句1");ps.addBatch("需要操作的語句2");ps.addBatch("需要操作的語句3");ps.addBatch("需要操作的語句4");ps.executeBatch();//提交上面的命令conn.commit();//提交事務conn.setAutoCommit(true);//開啟自動提交} catch (ClassNotFoundException e) {e.printStackTrace();System.err.println("數據庫鏈接異常");} catch (SQLException e) {e.printStackTrace();System.err.println("事務處理異常");try {if (conn!=null){conn.rollback();//回滾操作conn.setAutoCommit(true);}} catch (SQLException e1) {e1.printStackTrace();}}finally {//最后關閉數據庫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.調用存儲過程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
call = ct.prepareCall("{call sp_pro4(?,?,?,?,?,?)}");//設置輸入參數call.setString(1, "emp");call.setInt(2, 4);call.setInt(3, 1);//設置輸出參數call.registerOutParameter(4, OracleTypes.NUMBER);call.registerOutParameter(5, OracleTypes.NUMBER);call.registerOutParameter(6, OracleTypes.CURSOR);//執行call.execute();//輸出總數和總頁數System.out.println("總記錄數"+call.getInt(4)+"--總頁數"+call.getInt(5));//循環取出表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.可移動的結果集
sun只是提供了接口.具體能不能操作,這個要看你引用的JDBC驅動是否支持
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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,//設置該rs可以滾動ResultSet.CONCUR_READ_ONLY);//設置該rs為只讀ResultSet rs = stmt.executeQuery("select * from emp order by sal");rs.next();//正常下移一行System.out.println(rs.getInt(1));rs.last();//指向最后一行System.out.println(rs.getString(1));System.out.println(rs.isLast());System.out.println(rs.isAfterLast());System.out.println(rs.getRow());rs.previous();//上移一行System.out.println(rs.getString(1));rs.absolute(6);//該方法直接定位到行號System.out.println(rs.getString(1));rs.close();stmt.close();conn.close();} catch (SQLException e) {e.printStackTrace();}}} |
5.可更新的結果集
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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();//更新一行數據rs.updateString("ename","AAAA");rs.updateRow();//插入新行rs.moveToInsertRow();rs.updateInt(1, 9999);rs.updateString("ename","AAAA");rs.updateInt("mgr", 7839);rs.updateDouble("sal", 99.99);rs.insertRow();//將光標移動到新建的行rs.moveToCurrentRow();//刪除行rs.absolute(5);rs.deleteRow();//取消更新//rs.cancelRowUpdates();}catch(SQLException e){e.printStackTrace();}}} |
以上所述是小編給大家分享的Java連接Sql數據庫經常用到的操作，希望對大家有所幫助。
