java 连接数据库(oracle)

/**
* java 连接数据库(oracle)
*/
package jeelee.dbmanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.SQLException;

/**
* 连接数据库公共类
* @author JeeLee
*/
public class DBManage
{
public Connection conn=null;//建立一个连接
public Statement stm=null;
public PreparedStatement pstm=null;//有参数的statement
public ResultSet rs=null;//结果集
public ResultSetMetaData rsmd=null;//
public DatabaseMetaData dbmeta=null;//数据源相关信息
public CallableStatement cstm=null;//可调用存储过程的statement
public String dbms=null,dbversion=null,drivername=null,driverversion=null,dbURL=null;
public boolean autoCommit=false,readonly=false;
public String dbuser=null,dbpsw=null,dburl=null;

/**
* 构造函数,初始化连接数据库的账号,密码,DBURL,是否自动提交事物,访问数据库的读写方式
*/
public DBManage()
{
dbuser="liyongjie";
dbpsw="liyongjie";
dburl="jdbc:oracle:thin:@localhost:1521:LYDB";

autoCommit = false ;
readonly = false;
}
/**
* 设置连接数据库的账号
*/
public void setDBUser(String dbuser)
{
this.dbuser=dbuser;
}
/**
* 获得连接数据库的账号
*/
public String getDBUser()
{
return dbuser;
}
/**
* 设置连接数据库的密码
*/
public void setDBPsw(String dbpsw)
{
this.dbpsw=dbpsw;
}
/**
* 获得连接数据库的密码
*/
public String getDBPsw()
{
return dbpsw;
}
/**
* 设置连接数据库的DBURL
*/
public void setDBUrl(String dburl)
{
this.dburl=dburl;
}
/**
* 获得连接数据库的DBURL
*/
public String getDBUrl()
{
return dburl;
}
/**
* 设置连接数据库的账号,密码
*/
public void setDBUserPsw(String dbuser,String dbpsw)
{
this.dbuser=dbuser;
this.dbpsw=dbpsw;
}
/**
* 设置连接数据库的账号,密码,DBURL
*/
public void setDBUserPswUrl(String dburl,String dbuser,String dbpsw)
{
this.dbuser=dbuser;
this.dbpsw=dbpsw;
this.dburl=dburl;
}
/**
* 设置是否自动提交事物
*/
public void setAutoCommit(boolean autoCommit)
{
if(conn == null)
{
System.out.println("请先连接数据库!");
}

this.autoCommit=autoCommit;
try
{
conn.setAutoCommit(autoCommit);
}
catch(Exception e)
{
System.out.println("设置自动提交事物失败!:"+e);
}
}
/**
* 判断是否自动提交事务
*/
public boolean getAutoCommit()
{
return autoCommit;
}
/**
* 设置访问数据库的读写方式(是否为只读)
*/
public void setReadOnly(boolean readonly)
{
if(conn == null)
{
System.out.println("请先连接数据库");
}

this.readonly = readonly ;

try
{
conn.setReadOnly(readonly);
}
catch(Exception e)
{
System.out.println("设置访问数据库的读写方式失败:"+e);
}
}
/**
* 判断访问数据库的读写方式(是否为只读)
*/
public boolean getReadOnly()
{
return readonly ;
}
/**
* 获得数据源相关信息
*/
public DatabaseMetaData getDBMeta()
{
if(conn==null)
{
System.out.println("请先连接数据库");
}
else
{
try
{
dbmeta=conn.getMetaData();//数据源相关信息

drivername=dbmeta.getDriverName();//数据库驱动程序名称
driverversion=dbmeta.getDriverVersion();//数据库驱动程序版本信息
dbms=dbmeta.getDatabaseProductName();//获得数据库的DBMS
dbversion=dbmeta.getDatabaseProductVersion();//获得数据库的版本信息 dbURL=dbmeta.getURL();//获得数据库的DBURL
}
catch(Exception e)
{
System.out.println("获得数据库信息失败:"+e);
}
}

return dbmeta ;
}

/**
* 获得数据库的DBURL
*/
public String getDBURL()
{
return dbURL ;
}
/**
* 获得数据库驱动程序名称
*/
public String getDriverName()
{
return drivername;
}
/**
* 获得数据库驱动程序版本信息
*/
public String getDriverVersion()
{
return driverversion;
}
/**
* 获得数据库的DBMS
*/
public String getDBMS()
{
return dbms;
}
/**
* 获得数据库的版本信息
*/
public String getDBVersion()
{
return dbversion;
}
/**
* 连接数据库
*/
public boolean connect()
{
boolean flag = true ;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver");//加载驱动
}
catch(ClassNotFoundException sqlex1)
{
System.out.println("oracle driver:"+sqlex1);
flag = false ;
}
try
{
conn=DriverManager.getConnection(dburl,dbuser,dbpsw);//连接数据库
}
catch(SQLException sqlex2)
{
System.out.println("Error Message:"+sqlex2.getMessage());
flag = false;
}

return flag ;
}
/**
* 连接数据库
*/
public boolean connect(String dburl,String dbuser,String dbpsw)
{
boolean flag = true ;

this.dburl=dburl;
this.dbuser=dbuser;
this.dbpsw=dbpsw;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver");//加载驱动
}
catch(ClassNotFoundException sqlex1)
{
System.out.println("oracle driver:"+sqlex1);
flag = false;
}
try
{
conn=DriverManager.getConnection(dburl,dbuser,dbpsw);//建立连接
dbmeta=conn.getMetaData();//获得数据源信息
}
catch(SQLException sqlex2)
{
System.out.println("two:"+sqlex2);
System.out.println("SQL Status:"+sqlex2.getSQLState());
System.out.println("Error Code:"+sqlex2.getErrorCode());
System.out.println("Error Message:"+sqlex2.getMessage());
flag = false;
}

return flag ;
}
/**
* 获得一个数据库连接
*/
public Connection getConnect()
{
return conn;
}
/**
* 关闭数据库连接
*/
public void connClose()
{
try
{
conn.close();
}
catch(SQLException e)
{
System.out.println("关闭数据库失败:"+e);
}
}

}
//======================= 测试 ===============================
/**
* 连接数据库测试
*/
package jeelee.dbmanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.*;


public class TestDBM
{
Connection conn = null;
PreparedStatement pstm=null;
DatabaseMetaData dbmeta=null;
ResultSet rs = null;
DBManage db =  null ;

public TestDBM()
{
db = new DBManage() ;

if(db.connect())
{
conn = db.getConnect();
System.out.println("连接成功!!");
System.out.println("账号:"+db.getDBUser());
System.out.println("密码:"+db.getDBPsw());
System.out.println("URL 1:"+db.getDBUrl());

dbmeta = db.getDBMeta();
System.out.println("URL 2:"+db.getDBURL());
System.out.println("DBMS:"+db.getDBMS());
System.out.println("DBMS Version:"+db.getDBVersion());
System.out.println("Driver:"+db.getDriverName());
System.out.println("Driver Version:"+db.getDriverVersion());

queryData();
insertData();
updateData();
deleteData();
}
}

/**
* 检索数据
*/
public void queryData()
{
String sql="select object_name, data_object_id, object_type, created, status from user_objects where object_type = ?";
String object_name,object_type,status;
int data_object_id;
Date created;

db.connect();
conn = db.getConnect();
try
{
pstm = conn.prepareStatement(sql);
pstm.setString(1,"TABLE");
rs = pstm.executeQuery();
while(rs.next())
{
object_name = rs.getString(1);
data_object_id = rs.getInt(2);
object_type = rs.getString(3);
created  = rs.getTimestamp(4);
status = rs.getString(5);

System.out.println("object_name="+object_name);
System.out.println("data_object_id="+data_object_id);
System.out.println("object_type="+object_type);
System.out.println("status="+status);
System.out.println("created="+created);
System.out.println("===================================");
}
}
catch(SQLException e)
{
System.out.println("检索数据失败:"+e);
}
finally
{
try
{
rs.close();
pstm.close();
conn.close();
}
catch(SQLException e2)
{
System.out.println("关闭连接失败!");
}
}
}

/**
* insert数据
*/
public void insertData()
{
String sql="insert into test values(?,?,?,?)";
int row = 0 ;
Date date = new Date();
java.sql.Date sdate = new java.sql.Date(date.getTime());

db.connect();
conn = db.getConnect();
try
{
pstm = conn.prepareStatement(sql);
pstm.setString(1,"aa");
pstm.setString(2,"bb");
pstm.setInt(3,1);
pstm.setDate(4,sdate);
row = pstm.executeUpdate();
if(row > 0)
{
System.out.println("插入 "+row+" 行数据");
}
else
{
System.out.println("没有插入数据");
}
conn.commit();
}
catch(SQLException e)
{
System.out.println("insert数据失败:"+e);
}
finally
{
try
{
pstm.close();
conn.close();
}
catch(SQLException e2)
{
System.out.println("关闭连接失败!");
}
}
}

/**
* update数据
*/
public void updateData()
{
String sql="update test set a=? where b=?";
int row = 0 ;

db.connect();
conn = db.getConnect();
try
{
pstm = conn.prepareStatement(sql);
pstm.setString(1,"xx");
pstm.setString(2,"bb");
row = pstm.executeUpdate();
if(row > 0)
{
System.out.println("update "+row+" 行数据");
}
else
{
System.out.println("没有 update 数据");
}
conn.commit();
}
catch(SQLException e)
{
System.out.println("update数据失败:"+e);
}
finally
{
try
{
pstm.close();
conn.close();
}
catch(SQLException e2)
{
System.out.println("关闭连接失败!");
}
}
}

/**
* delete数据
*/
public void deleteData()
{
String sql="delete from test where a=?";
int row = 0 ;

db.connect();
conn = db.getConnect();
try
{
pstm = conn.prepareStatement(sql);
pstm.setString(1,"xx");
row = pstm.executeUpdate();
if(row > 0)
{
System.out.println("delete "+row+" 行数据");
}
else
{
System.out.println("没有 delete 数据");
}
conn.commit();
}
catch(SQLException e)
{
System.out.println("delete 数据失败:"+e);
}
finally
{
try
{
pstm.close();
conn.close();
}
catch(SQLException e2)
{
System.out.println("关闭连接失败!");
}
}
}

public static void main(String[] args)
{
new TestDBM();
}
}