纵有疾风起
人生不言弃

Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构

Oracle安装

Navicat for Oracle配置

Oracle JDBC连接(增删改查)

 

Oracle安装

环境windows 7 64位

安装版本:oracle 11g64位)

下载地址 

http://www.oracle.com/technetwork/cn/database/database-technologies/express-edition/downloads/index.html

  1. 点击oracle安装文件,根据其默认的模式进行安装。

http://wenku.baidu.com/link?url=PakzxG2Vj0lapOE0j3O8WOIWwzHQV6jsItUIpaMtovOyB1oqqZl3Erw33GBQCraVydLxziDHLsWDioI3IAD9wKMTms8OZ2t3tzFu_u6emGK

  1. Oracle–windows环境

这个环境配置与java的环境配置类似,计算机右击》高级系统设置》高级》环境变量》在系统变量中新建:

(1) 配置ORACLE_HOME

变量名 ORACLE_HOME

变量值 E:\Oracle\product\11.2.0\dbhome_1

说明:这里的变量值是你自己安装的Oracle数据库的安装路径,路径中你会找到project然后依次找到dbhome_1,然后将路径复制到环境变量值中。

2配置TNS_ADMIN

变量名 TNS_ADMIN

变量值 E:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN

说明:这里配置的是Oracle的监听,监听配置不正确会出问题的。

3)配置 NLS_LAN

变量名 NLS_LANG

变量值 SIMPLIFIED CHINESE_CHINA.ZHS16GBK 

说明:建议大家直接复制上面的变量值。这里的意思就是统一编码格式。(GBK)

http://jingyan.baidu.com/article/e73e26c0c20f1a24adb6a73e.html

3. 监听配置

1)右击“计算机”,打开“管理”,点开左边菜单“服务与进程”下的“服务”,手动启动“OracleOraDb11g_home1TNSListener”

2)点击net manager,在本地》监听程序》LISTENER,添加监听机器的ip地址

3)在窗口右栏下拉选项中的“数据库服务”,点击添加数据库按钮,添加全局数据库名和SID 

http://www.cnblogs.com/kane1990/p/OracleNetManager.html

4.数据库创建,按默认配置,定义数据库的全局变量

http://www.it165.net/database/html/201306/3974.html

 Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图

 

  1. SQL Developer配置

启动SQL Developer可能会出现:

 Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图1

原因:oracle 11g中安装的Oracle SQL Developer是32位的,而我们现在给他指定的java.exe却是64位的,所以会出现这种错误。

解决方法:oracle11g自带sqldeveloper32位的,可以再官网下载64位的sqldeveloper替换掉dbhome_1下的sqldeveloper文件夹。

 

  1. 登陆及命令

(1)本地登陆:

    C:\users\administrator>sqlplus / as sysdba  //以sysdba的权限登录

C:\users\administrator>sqlplus / as sysdba  //以sysdba的权限登录

SQL>conn  user_name/password; //连接用户

SQL>show user; //显示当前用户

SQL>startup force; //启动

SQL>shutdown immediate; //关闭

SQL> grant dba to scott;  //将sysdba权限授予scott用户

SQL> revoke dba from scott; //收回scott权限

SQL> create user 用户名 identified by “密码”; //创建用户

(2)远程登陆:通过端口登陆

sqlplus user/password@ip:port/service_name

 Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图2

开启监听器:计算机——管理——服务——oracle*Listener

程序——Oracle——Net Manager

Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图3

(3)登录数据库并创建用户

C:\users\administrator>sqlplus / as sysdba  //以sysdba的权限登录

>conn sys@orcl as sysdba (不用口令)

>create user kang identified by “123456”;

> grant dba to kang;  //将sysdba权限授予scott用户

>conn kang/123456;

>sqlplus kang/123456d@192.168.0.110:1521/orcl

Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图4
Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图4

 

Navicat for Oracle配置

Navicat破解版32

下载地址:http://download.csdn.net/detail/streetmilk/5701651

下载插件:

instantclient-basic-nt-12.1.0.1.0.zip

instantclient-sqlplus-win32-11.2.0.1.0.zip

解压到navicat安装目录,打开navicat

工具——>选项——>OCI

 Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图5

分别为:

D:\Navicat Premium\instantclient_12_1\oci.dll

D:\Navicat Premium\instantclient_12_1\instantclient-sqlplus-win32-11.2.0.1.0\instantclient_11_2\sqlplus.exe

 

连接oracle

 Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图6

 

连接上即可,如果连不上可能是端口和权限的问题。

 

Oracle JDBC连接

navicat中测试成功后,再用jdbc连接

通用数据连接:以后碰到mysql、sqlserver、informix都是类似的

Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构插图7

sqlplus kang/123456d@192.168.0.110:1521/orcl

 

其中数据库名称对应service_name:orcl ,数据库主机对应计算机名(可选),IP和port对应IP地址和端口,用户密码必选,SID和service_name一样

 

获取连接

 

package com.hidata.generalDBconfig;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException; import java.sql.Statement;/** * @author 刘兀 * @category数据库连接     针对数据源的不同,获取相关参数后构造连接。 * */public class OracleDBConnection {private static Connection conn = null;/*创建数据库连接方法public synchronized static Connection getConnection() { try {Class.forName(JDriver);//装载数据库驱动类}  catch(ClassNotFoundException e){e.getStackTrace();}System.out.println("url   " + url);System.out.println("user   " + user);System.out.println("password   " + password);try{conn = DriverManager.getConnection(url, user, password);}catch (Exception e) {e.printStackTrace();System.out.println("加载数据库引擎失败");System.exit(0);}//System.out.println("数据库驱动成功");return conn;}//conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:abc", "wu", "123456"); /*创建连接Oracle方法*/public synchronized static Connection getConnection() {String JDriver = "oracle.jdbc.driver.OracleDriver";try {Class.forName(JDriver);//装载数据库驱动类}  catch(ClassNotFoundException e){e.getStackTrace();}String url = "jdbc:oracle:thin:@192.168.0.101:1521:abc";String user="wu";String password="123456";System.out.println("url   " + url);System.out.println("user   " + user);System.out.println("password   " + password);try{conn = DriverManager.getConnection(url, user, password);}catch (Exception e) {e.printStackTrace();System.out.println("加载数据库引擎失败");System.exit(0);}System.out.println("数据库驱动成功");return conn;}//关闭数据库连接方法 public synchronized static void closeConection(Connection conn,Statement st, ResultSet rs) {if (rs != null) {try {rs.close();rs = null;} catch (SQLException e) {e.printStackTrace();}}if (st != null) {try {st.close();st = null;} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();conn = null;} catch (SQLException e) {e.printStackTrace();}}}public static void main(String args[]){OracleDBConnection.getConnection();}}

 

  

 

SQL操作

 

package com.util.DBtest;import java.sql.Connection;    import java.sql.DriverManager;    import java.sql.PreparedStatement;import java.sql.ResultSet;    import java.sql.SQLException;    import java.sql.Statement;    import java.util.ArrayList;import java.util.List;import com.hidata.generalDBconfig.OracleDBConnection;import com.util.entity.Oracle_Table_Structure;    public class JDBC_Test {        // 创建静态全局变量         static Connection conn;        static Statement st;        public static void main(String[] args) {    //    create();//        insert();   //插入//        update();   //更新//        delete();   //删除//        query();    //查询    String tableName = "Kang";    List<Oracle_Table_Structure> list = queryTableStruct(tableName);//查询表结构    }            
/*查询表结构*/ public static List<Oracle_Table_Structure> queryTableStruct(String tableName) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; List<Oracle_Table_Structure> result = new ArrayList<Oracle_Table_Structure>(); conn =OracleDBConnection.getConnection(); // 首先要获取连接,即连接到数据库 try { String sql = "select * from user_tab_columns where Table_Name='Kang'"; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { Oracle_Table_Structure struct = new Oracle_Table_Structure(); struct.setTable_name(rs.getString("Table_name")); struct.setData_type(rs.getString("Data_type")); struct.setData_length(rs.getString("Data_length")); struct.setColumn_name(rs.getString("Column_name")); struct.setColumn_id(rs.getString("Column_id")); struct.setChar_length(rs.getString("Char_length")); System.out.println(struct.getTable_name()+" " + struct.getColumn_name() + " " + struct.getData_type() + " "+ struct.getData_length()); result.add(struct); } OracleDBConnection.closeConection(conn, ps, rs); } catch (SQLException e) { System.out.println("查询数据失败"); }return result; }/* 创建表*/ public static void create() {// TODO Auto-generated method stub conn =OracleDBConnection.getConnection(); String sql = "create table kangtest(id number(9) not null primary key,name varchar2(40) not null)"; // 插入数据的sql语句 try {st = (Statement) conn.createStatement();st.executeUpdate(sql); conn.close();st.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} System.out.println("创建表成功");} /* 插入记录*/ public static void insert() { Connection conn = null;PreparedStatement ps = null;ResultSet rs = null; conn =OracleDBConnection.getConnection(); int num = 0; try { String sql = "INSERT INTO kangtest(id, name)" + " VALUES (?,?)"; ps = conn.prepareStatement(sql);ps.setLong(1, num);ps.setString(2, "wufan");ps.executeUpdate();System.out.println("insert success"); OracleDBConnection.closeConection(conn, ps, rs); } catch (SQLException e) { System.out.println("insert failed" + e.getMessage()); } } /* 更新记录*/ public static void update() { Connection conn = null;PreparedStatement ps = null;ResultSet rs = null; conn =OracleDBConnection.getConnection(); int num = 0; try { String sql = "update kangtest set name='liuping' where id = '1'"; ps = conn.prepareStatement(sql);ps.executeUpdate();System.out.println("update success"); conn.close(); OracleDBConnection.closeConection(conn, ps, rs); } catch (SQLException e) { System.out.println("update failed" + e.getMessage()); } } /* 查询记录*/ public static void query() { Connection conn = null;PreparedStatement ps = null;ResultSet rs = null; conn =OracleDBConnection.getConnection(); // 首先要获取连接,即连接到数据库 try { String sql = "select * from staff"; ps = conn.prepareStatement(sql);rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt("id"); int name = rs.getInt("name"); System.out.println(id + " " + name); } OracleDBConnection.closeConection(conn, ps, rs); } catch (SQLException e) { System.out.println("查询数据失败"); } } /* 删除记录*/ public static void delete() { Connection conn = null;PreparedStatement ps = null;ResultSet rs = null; conn =OracleDBConnection.getConnection(); try { String sql = "delete from staff where name = 'lili'"; st = (Statement) conn.createStatement(); int count = st.executeUpdate(sql); OracleDBConnection.closeConection(conn, ps, rs); } catch (SQLException e) { System.out.println("删除数据失败"); } } }

 

 获取表主键

select * from user_cons_columns where constraint_name=(select constraint_name from user_constraints where table_name ='TEST'  and    constraint_type   ='P');   )

  

 

文章转载于:https://www.cnblogs.com/kxdblog/p/4441055.html

原著是一个有趣的人,若有侵权,请通知删除

未经允许不得转载:起风网 » Oracle安装、Navicat for Oracle、JDBCl连接、获取表结构
分享到: 生成海报

评论 抢沙发

评论前必须登录!

立即登录