数据库的简单操作

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();

}

}

}