import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DBOperate {
private static Connection con ;
private static Statement stat ;
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//建立与数据库的连接
con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "larms", "oracle");
// 创建语句类对象
stat = con.createStatement();
} catch (ClassNotFoundException e) {
System.out.println("驱动加载失败");
} catch (SQLException ee) {
System.out.println("连接数据库失败");
}
}
数据库连接函数,方便连接
//private static Statement connectDB() {
//if(stat != null){
//return stat ;
//}
声明连接变量
//try {
//if(con == null){
//Class.forName("oracle.jdbc.driver.OracleDriver");
建立与数据库的连接
//con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "larms", "oracle");
//}
创建语句类对象
//stat = con.createStatement();
//} catch (ClassNotFoundException e) {
//System.out.println("驱动加载失败");
//} catch (SQLException ee) {
//System.out.println("连接数据库失败");
//}
//return stat;
//}
public static void main(String[] args) throws SQLException {
//new Thread(new Runnable() {
//public void run() {
//List<Map<String, Object>> list = select("select * from ALL_TABLES");
//for (Map<String, Object> map : list) {
//Set<String> set = map.keySet() ;
//System.out.print("record:[");
//for (String key : set) {
//System.out.print(key+":"+map.get(key)+"\t");
//}
//System.out.print("]");
//System.out.println();
//}
//}
//}).start();
//
new Thread(new Runnable() {
public void run() {
List<Object[]> list2 = select2("select table_name from ALL_TABLES where owner = 'LARMS' ");
for (Object[] objects : list2) {
for (Object object : objects) {
System.out.print(object+"\t");
}
System.out.println();
}
}
}).start();
//execute("delete Ci_Ind_Funds");
//execute("insert into Ci_Ind_Funds (CUST_NO, ASS_CAR_PRICE, ASS_CAR_NUM, ASS_HOME_PRICE, ASS_HOME_NUM, ASS_HOME_AREA, ASS_OTHER_PRICE, ASS_OTHER_NUM, DEBT_IN_LOAN_PRICE, DEBT_OUT_LOAN_PRICE, DEBT_OTHER_PRICE) values ('20110000000000822', '0', '0', '1231', '1', '0', '0', '0', '0', '0', '0')");
}
public static List<Map<String, Object>> select(String sql) {// UPDATE CI_BASIC_EVENT SET
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
ResultSet set = null;
//Statement stat = connectDB();
Connection cnn = null;
try {
cnn = stat.getConnection();
beginTransaction();
set = stat.executeQuery(sql);
ResultSetMetaData data = set.getMetaData();
while (set.next()) {
int count = data.getColumnCount() ;
Map<String, Object> map = new HashMap<String, Object>();
for(int i = 1 ; i<= count ; i++){
String columnName = data.getColumnName(i);
Object valueObject = set.getObject(columnName);
map.put(columnName, valueObject);
}
list.add(map);
}
commitTransaction();
} catch (Exception e) {
e.printStackTrace();
rollBackTransaction();
} finally {
try {
stat.close();
cnn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public static List<Object[]> select2(String sql) {// UPDATE CI_BASIC_EVENT SET
List<Object[]> list = new ArrayList<Object[]>();
ResultSet set = null;
//Statement stat = connectDB();
Connection cnn = null;
try {
cnn = stat.getConnection();
beginTransaction();
set = stat.executeQuery(sql);
ResultSetMetaData data = set.getMetaData();
while (set.next()) {
int count = data.getColumnCount() ;
Object[] objects = new Object[count];
for(int i = 1 ; i<= count ; i++){
String columnName = data.getColumnName(i);
Object valueObject = set.getObject(columnName);
objects[i-1] = valueObject;
}
list.add(objects);
}
commitTransaction();
} catch (Exception e) {
e.printStackTrace();
rollBackTransaction();
} finally {
try {
stat.close();
cnn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public static boolean execute(String sql) throws Exception {// UPDATE CI_BASIC_EVENT SET
// EVE_DESC = '1' WHERE SEQ_NO = 29
//Statement stat = connectDB();
//Connection con = stat.getConnection() ;
try {
stat.execute(sql);
} catch (Exception e) {
e.printStackTrace();
throw e ;
}
//finally{
//try {
//stat.close();
//con.close();
//} catch (SQLException e) {
//e.printStackTrace();
//}
//}
return true ;
}
/**
* 开始事务
*
* @param cnn
*/
public static void beginTransaction() {
if (con != null) {
try {
if (con.getAutoCommit()) {
con.setAutoCommit(false);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 提交事务
*
* @param cnn
*/
public static void commitTransaction() {
if (con != null) {
try {
if (!con.getAutoCommit()) {
con.commit();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 回滚事务
*
* @param cnn
*/
public static void rollBackTransaction() {
if (con != null) {
try {
if (!con.getAutoCommit()) {
con.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void test(){
try {
stat.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}