废话不多说了，直接给大家分享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数据库经常用到的操作，希望对大家有所帮助。
