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("數(shù)據(jù)庫(kù)鏈接異常");
} catch (SQLException e) {
e.printStackTrace();
System.err.println("數(shù)據(jù)庫(kù)鏈接異常");
}
}
/*
處理事務(wù)的函數(shù)欠缺
*/
/**
* 創(chuàng)建數(shù)據(jù)庫(kù)更新函數(shù)
* @param sql 對(duì)應(yīng)的更新sql語(yǔ)句
* @param params 需要附加的參數(shù)
* @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開始設(shè)置參數(shù)的
ps.setString(i+1,params[i]);
}
k = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
System.err.println("數(shù)據(jù)庫(kù)更新異常");
}
return k>0?true:false;
}
/**
* 數(shù)據(jù)庫(kù)查詢函數(shù)
* @param sql 要查詢的qsl語(yǔ)句
* @param params 附加參數(shù)
* @return 查詢結(jié)果集
*/
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("數(shù)據(jù)庫(kù)查詢異常");
}
return rs;
}
/**
* 關(guān)閉數(shù)據(jù)庫(kù)語(yǔ)句
*/
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();
}
}
}

寫成這樣,其他類調(diào)用的話可以按照下面方法.

SQLBean sqlBean = new SQLBean();
String[] params={};//如果有參數(shù)則寫進(jìn)去
ResultSet rs = sqlBean.query("select ename from emp",params);//sql語(yǔ)句
//循環(huán)取出
try {
while(rs.next()){
System.out.println(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
sqlBean.close();//異常則關(guān)閉連接
}
sqlBean.close();//關(guān)閉數(shù)據(jù)庫(kù)連接

2.事務(wù)的處理

事務(wù)的處理,總是先取消自動(dòng)提交,然后執(zhí)行命令,最后提交,然后發(fā)生異常則回滾,至于怎么寫成一個(gè)方法,暫時(shí)還沒(méi)想到好辦法

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);//首先取消自動(dòng)提交
ps = conn.createStatement();
ps.addBatch("需要操作的語(yǔ)句1");
ps.addBatch("需要操作的語(yǔ)句2");
ps.addBatch("需要操作的語(yǔ)句3");
ps.addBatch("需要操作的語(yǔ)句4");
ps.executeBatch();//提交上面的命令
conn.commit();//提交事務(wù)
conn.setAutoCommit(true);//開啟自動(dòng)提交
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.err.println("數(shù)據(jù)庫(kù)鏈接異常");
} catch (SQLException e) {
e.printStackTrace();
System.err.println("事務(wù)處理異常");
try {
if (conn!=null){
conn.rollback();//回滾操作
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {//最后關(guān)閉數(shù)據(jù)庫(kù)
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.調(diào)用存儲(chǔ)過(guò)程

call = ct.prepareCall("{call sp_pro4(?,?,?,?,?,?)}");
//設(shè)置輸入?yún)?shù)
call.setString(1, "emp");
call.setInt(2, 4);
call.setInt(3, 1);
//設(shè)置輸出參數(shù)
call.registerOutParameter(4, OracleTypes.NUMBER);
call.registerOutParameter(5, OracleTypes.NUMBER);
call.registerOutParameter(6, OracleTypes.CURSOR);
//執(zhí)行
call.execute();
//輸出總數(shù)和總頁(yè)數(shù)
System.out.println("總記錄數(shù)"+call.getInt(4)
+"--總頁(yè)數(shù)"+call.getInt(5));
//循環(huán)取出表
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.可移動(dòng)的結(jié)果集

sun只是提供了接口.具體能不能操作,這個(gè)要看你引用的JDBC驅(qū)動(dòng)是否支持

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,//設(shè)置該rs可以滾動(dòng)
ResultSet.CONCUR_READ_ONLY);//設(shè)置該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);//該方法直接定位到行號(hào)
System.out.println(rs.getString(1));
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

5.可更新的結(jié)果集

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();
//更新一行數(shù)據(jù)
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();
//將光標(biāo)移動(dòng)到新建的行
rs.moveToCurrentRow();
//刪除行
rs.absolute(5);
rs.deleteRow();
//取消更新
//rs.cancelRowUpdates();
}catch(SQLException e){
e.printStackTrace();
}
}
}

以上所述是小編給大家分享的Java連接Sql數(shù)據(jù)庫(kù)經(jīng)常用到的操作，希望對(duì)大家有所幫助。

您可能感興趣的文章: