java连接Oracle

    花了两天时间，终于把access数据库里的数据换到Oracle了。当然这其中要感谢同学和博友的热心帮助拉。现将今天的收获整理下。

    我用的是Oracle 10 express edition版本，这个版本建数据库挺麻烦的，所以我就直接在它自带的数据库XE下建表了。

    注意：建表可一定要注意大小写的哦，默认使用的是大写，表名有小写的话，需要加双引号，才可以查询到，否则就出错了。

 

   

    建好数据表后，就是连接了，写了三个文件，DBConfig.property（配置文件，可以选择连接不同的数据库类型），DBConnection.java（数据库连接类），GetData.java（获取数据类）

    注意：在连接之前，要把jdbc驱动(一般在安装路径/oraclexe/app/oracle/product/10.2.0/server/jdbc/lib下，不知道哪个有用，我就两个都放上去了，呵呵)放到tomcat/lib（D:/Program Files/Apache Software Foundation/Tomcat 6.0/lib）下

    DBConfig.property代码如下：

    //0:Access,1:MYSQL,2:MSSQL,3:Oracle DBType=3 #Access #Access AccessDriver=sun.jdbc.odbc.JdbcOdbcDriver #Access AccessURL=jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ= #Access AccessPath=D:/Program Files/Apache Software Foundation/Tomcat 6.0/webapps/db/DreamNews.mdb #MySQL MySQLDriver=org.gjt.mm.mysql.Driver #MySQL #DreamTimeNews MySQLURL=jdbc:mysql://localhost:3306/DreamTimeNews?user=dreamnews&password=dreamnews&useUnicode=true&characterEncoding=GBK #SQL Server #SQL Server SQLDriver=net.sourceforge.jtds.jdbc.Driver #SQL Server #DreamTimeNews SQLURL=jdbc:jtds:sqlserver://localhost:1433/DreamTimeNews;user=dreamnews;password=dreamnews #Oracle OracleDriver=oracle.jdbc.driver.OracleDriver OracleURL=jdbc:oracle:thin:@localhost:1521:XE OracleUsername=hr OraclePassword=hr

DBConnection.java代码如下：

/*************************************************** * * Դ�ļ���: DBConnecion.java * �� �ܣ� ��ݿ�l���� * **************************************************** */ package dataConn; //ָ�������ڵİ� import java.sql.*; //������ݿ������� import java.util.*; import java.io.*; public class DBConnection { private String FileName; //�����ļ��� private int DBType; //��ݿ����� private Connection conn; //l�Ӷ��� private PreparedStatement stmt; //������ private ResultSet rs; //������ private String AccessDriver; //����Access����� private String AccessURL; //����Accessl���ַ� private String AccessPath; //����Access��ݿ��·�� private String MySqlDriver; //MYSQL Server����� private String MySqlURL; //MYSQL Serverl���ַ� private String SqlDriver; //SQL Server����� private String SqlURL; private String SqlUser; private String SqlUpwd; //SQL Serverl���ַ� private String OracleDriver; //Oracle����� private String OracleURL; //Oraclel���ַ� private String OracleUsername; private String OraclePassword; public DBConnection() { conn = null; } public Connection getConn() { DBType= Integer.parseInt(getPara("DBType")); switch(DBType) { case 0:return(getConnToAccess()); case 1:return(getConnToMySql()); case 2:return(getConnToSql()); case 3:return(getConnToOracle()); default:return null; } } public String getPara(String ParaName) { FileName="../DBConfig.property"; Properties prop= new Properties(); try { InputStream is = getClass().getResourceAsStream(FileName); prop.load(is); if(is!=null) is.close(); } catch(Exception e) { return "Error!"; } return prop.getProperty(ParaName); } public Connection getConnToAccess() { try{ AccessDriver = getPara("AccessDriver"); AccessURL = getPara("AccessURL"); AccessPath = getPara("AccessPath"); AccessURL=AccessURL+AccessPath; Class.forName(AccessDriver).newInstance(); conn = DriverManager.getConnection(AccessURL); }catch(Exception e){ e.printStackTrace(); //return "������ݿ��?����ϸ���" ; //System.err.println(e.getMessage()); } return conn; } public Connection getConnToMySql() { try{ MySqlDriver = getPara("MySQLDriver"); MySqlURL = getPara("MySQLURL"); Class.forName(MySqlDriver).newInstance(); conn = DriverManager.getConnection(MySqlURL); }catch(Exception e){ //e.printStackTrace(); //return "������ݿ��?����ϸ���" ; //System.err.println(e.getMessage()); } return conn; } public Connection getConnToSql() { try{ SqlDriver = getPara("SQLDriver"); SqlURL = getPara("SQLURL"); //SqlUser = getPara("username"); //SqlUpwd = getPara("password"); Class.forName(SqlDriver).newInstance(); conn = DriverManager.getConnection(SqlURL); }catch(Exception e){ e.printStackTrace(); //return "������ݿ��?����ϸ���" ; System.err.println(e.getMessage()); } return conn; } public Connection getConnToOracle() { try{ OracleDriver = getPara("OracleDriver"); OracleURL = getPara("OracleURL"); OracleUsername=getPara("OracleUsername"); OraclePassword=getPara("OraclePassword"); Class.forName(OracleDriver).newInstance(); conn = DriverManager.getConnection(OracleURL,OracleUsername,OraclePassword); }catch(Exception e){ //System.out.println(conn); e.printStackTrace(); //return "������ݿ��?����ϸ���" ; //System.err.println(e.getMessage()); } return conn; } public ResultSet executeQuery(String sql) throws Exception { try { this.conn = getConn(); this.stmt = null; this.stmt = conn.prepareStatement(sql); return this.stmt.executeQuery(); } catch (Exception e) { e.printStackTrace(); return null; } } }

GetData.java代码如下

package dataConn; import java.io.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.servlet.*; import javax.servlet.http.*; public class GetData extends HttpServlet { DBConnection DBConn = new DBConnection();//t����ݿ��� public ResultSet rs =null; public Connection con=null; public Statement st=null; public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{ response.setContentType("text/xml;charset=utf-8"); response.setHeader("Cache-Control", "no-cache"); String content = ""; String name=""; String id=""; String detail=""; try{ Connection condb = DBConn.getConn();//����t����ݿ�ĺ��� st = condb.createStatement(); rs=st.executeQuery("select * from TBLABEQU"); while(rs.next()){ id = rs.getString("labEquID"); name = rs.getString("labEquName"); detail = rs.getString("labEquDtl"); content+="<equlist><id>"+id+"</id><name>"+name+"</name><detail>"+ detail+"</detail></equlist>"; } }catch( SQLException e1){ e1.printStackTrace(); } content="<?xml version=/"1.0/" encoding=/"utf-8/"?><equlists>"+content; content+="</equlists>"; System.out.println(content); response.getWriter().write(content); } }

运行结果是以xml形式显示数据库里的数据。

注意：修改这些文件后，需要重起tomcat，才会看到变化。