java连接Oracle数据库

Oracle数据库先创建一个表和添加一些数据

1.先在Oracle数据库中创建一个student表:

1 create table student
2 (
3        id number(11) not null primary key,
4        stu_name varchar(16) not null,
5        gender number(11) default null,
6        age number(11) default null,
7        address varchar(128) default null
8 );

1 create table student 2 ( 3 id number(11) not null primary key, 4 stu_name varchar(16) not null, 5 gender number(11) default null, 6 age number(11) default null, 7 address varchar(128) default null 8 );

2.向表中增添一些数据

insert into student values(1,王小军,1,17,北京市和平里七区30号楼7门102)

insert into student values(1,王小军,1,17,北京市和平里七区30号楼7门102)

---

MyEclipse里编写java代码

1.将ojdbc6.jar导入项目中

先创建一个项目，然后在鼠标移到项目上右键-->new-->folder;folder name:lib;这样就在项目中创建了一个文件夹lib;然后将ojdbc6.jar包导入该文件夹中

该包分享地址:链接： 密码：epkz

鼠标移到该包上;右键-->build path-->add to build path;

2.创建一个类,开始编码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class OperateOracle {

    // 定义连接所需的字符串
    // 192.168.0.X是本机地址(要改成自己的IP地址)，1521端口号，XE是精简版Oracle的默认数据库名
    private static String USERNAMR = "orcl";
    private static String PASSWORD = "orcl";
    private static String DRVIER = "oracle.jdbc.OracleDriver";
    private static String URL = "jdbc:oracle:thin:@192.168.0.X:1521:xe";

    // 创建一个数据库连接
    Connection connection = null;
    // 创建预编译语句对象，一般都是用这个而不用Statement
    PreparedStatement pstm = null;
    // 创建一个结果集对象
    ResultSet rs = null;

    /**
     * 向数据库中增加数据
     * 首先获取表内数据总数,总数+1为新增数据的id值
     * @param stuName:学生姓名
     * @param gender:学生性别,1表示男性,2表示女性
     * @param age:学生年龄
     * @param address:学生住址
     */
    public void AddData(String stuName, int gender, int age, String address) {
        connection = getConnection();
        // String sql =
        // "insert into student values(1,王小军,1,17,北京市和平里七区30号楼7门102)";
        String sql = "select count(*) from student where 1 = 1";
        String sqlStr = "insert into student values(?,?,?,?,?)";
        int count = 0;

        try {
            // 计算数据库student表中数据总数
            pstm = connection.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1) + 1;
                System.out.println(rs.getInt(1));
            }
            // 执行插入数据操作
            pstm = connection.prepareStatement(sqlStr);
            pstm.setInt(1, count);
            pstm.setString(2, stuName);
            pstm.setInt(3, gender);
            pstm.setInt(4, age);
            pstm.setString(5, address);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseResource();
        }
    }

    /**
     * 向数据库中删除数据
     * @param stuName:根据姓名删除数据
     */
    public void DeleteData(String stuName) {
        connection = getConnection();
        String sqlStr = "delete from student where stu_name=?";
        System.out.println(stuName);
        try {
            // 执行删除数据操作
            pstm = connection.prepareStatement(sqlStr);
            pstm.setString(1, stuName);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseResource();
        }
    }

    /**
     * 向数据库中修改数据
     * @param stuName:学生姓名,根据此值查询要修改的某行值
     * @param gender
     * @param age
     * @param address
     */
    public void UpdateData(String stuName, int gender, int age, String address) {
        connection = getConnection();
        String sql = "select id from student where 1 = 1 and stu_name = ?";
        String sqlStr = "update student set stu_name=?,gender=?,age=?,address=? where id=?";
        int count = 0;

        try {
            // 计算数据库student表中数据总数
            pstm = connection.prepareStatement(sql);
            pstm.setString(1, stuName);
            rs = pstm.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
                System.out.println(rs.getInt(1));
            }
            // 执行插入数据操作
            pstm = connection.prepareStatement(sqlStr);
            pstm.setString(1, stuName);
            pstm.setInt(2, gender);
            pstm.setInt(3, age);
            pstm.setString(4, address);
            pstm.setInt(5, count);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseResource();
        }
    }

    /**
     * 向数据库中查询数据
     */
    public void SelectData() {
        connection = getConnection();
        String sql = "select * from student where 1 = 1";
        try {
            pstm = connection.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("stu_name");
                String gender = rs.getString("gender");
                String age = rs.getString("age");
                String address = rs.getString("address");
                System.out.println(id + "	" + name + "	" + gender + "	"
                        + age + "	" + address);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseResource();
        }
    }

    /**
     * 使用ResultSetMetaData计算列数
     */
    public void SelectData2() {
        connection = getConnection();
        String sql = "select * from employees where 1 = 1";
        int count = 0;

        try {
            pstm = connection.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                count++;
            }

            ResultSetMetaData rsmd = rs.getMetaData();
            int cols_len = rsmd.getColumnCount();

            System.out.println("count=" + count + "	cols_len=" + cols_len);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseResource();
        }
    }

    /**
     * 获取Connection对象
     * 
     * @return
     */
    public Connection getConnection() {
        try {
            Class.forName(DRVIER);
            connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
            System.out.println("成功连接数据库");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("class not find !", e);
        } catch (SQLException e) {
            throw new RuntimeException("get connection error!", e);
        }

        return connection;
    }

    /**
     * 释放资源
     */
    public void ReleaseResource() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstm != null) {
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; public class OperateOracle { // 定义连接所需的字符串 // 192.168.0.X是本机地址(要改成自己的IP地址)，1521端口号，XE是精简版Oracle的默认数据库名 private static String USERNAMR = "orcl"; private static String PASSWORD = "orcl"; private static String DRVIER = "oracle.jdbc.OracleDriver"; private static String URL = "jdbc:oracle:thin:@192.168.0.X:1521:xe"; // 创建一个数据库连接 Connection connection = null; // 创建预编译语句对象，一般都是用这个而不用Statement PreparedStatement pstm = null; // 创建一个结果集对象 ResultSet rs = null; /** * 向数据库中增加数据 * 首先获取表内数据总数,总数+1为新增数据的id值 * @param stuName:学生姓名 * @param gender:学生性别,1表示男性,2表示女性 * @param age:学生年龄 * @param address:学生住址 */ public void AddData(String stuName, int gender, int age, String address) { connection = getConnection(); // String sql = // "insert into student values(1,王小军,1,17,北京市和平里七区30号楼7门102)"; String sql = "select count(*) from student where 1 = 1"; String sqlStr = "insert into student values(?,?,?,?,?)"; int count = 0; try { // 计算数据库student表中数据总数 pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); while (rs.next()) { count = rs.getInt(1) + 1; System.out.println(rs.getInt(1)); } // 执行插入数据操作 pstm = connection.prepareStatement(sqlStr); pstm.setInt(1, count); pstm.setString(2, stuName); pstm.setInt(3, gender); pstm.setInt(4, age); pstm.setString(5, address); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 向数据库中删除数据 * @param stuName:根据姓名删除数据 */ public void DeleteData(String stuName) { connection = getConnection(); String sqlStr = "delete from student where stu_name=?"; System.out.println(stuName); try { // 执行删除数据操作 pstm = connection.prepareStatement(sqlStr); pstm.setString(1, stuName); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 向数据库中修改数据 * @param stuName:学生姓名,根据此值查询要修改的某行值 * @param gender * @param age * @param address */ public void UpdateData(String stuName, int gender, int age, String address) { connection = getConnection(); String sql = "select id from student where 1 = 1 and stu_name = ?"; String sqlStr = "update student set stu_name=?,gender=?,age=?,address=? where id=?"; int count = 0; try { // 计算数据库student表中数据总数 pstm = connection.prepareStatement(sql); pstm.setString(1, stuName); rs = pstm.executeQuery(); while (rs.next()) { count = rs.getInt(1); System.out.println(rs.getInt(1)); } // 执行插入数据操作 pstm = connection.prepareStatement(sqlStr); pstm.setString(1, stuName); pstm.setInt(2, gender); pstm.setInt(3, age); pstm.setString(4, address); pstm.setInt(5, count); pstm.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 向数据库中查询数据 */ public void SelectData() { connection = getConnection(); String sql = "select * from student where 1 = 1"; try { pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); while (rs.next()) { String id = rs.getString("id"); String name = rs.getString("stu_name"); String gender = rs.getString("gender"); String age = rs.getString("age"); String address = rs.getString("address"); System.out.println(id + " " + name + " " + gender + " " + age + " " + address); } } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 使用ResultSetMetaData计算列数 */ public void SelectData2() { connection = getConnection(); String sql = "select * from employees where 1 = 1"; int count = 0; try { pstm = connection.prepareStatement(sql); rs = pstm.executeQuery(); while (rs.next()) { count++; } ResultSetMetaData rsmd = rs.getMetaData(); int cols_len = rsmd.getColumnCount(); System.out.println("count=" + count + " cols_len=" + cols_len); } catch (SQLException e) { e.printStackTrace(); } finally { ReleaseResource(); } } /** * 获取Connection对象 * * @return */ public Connection getConnection() { try { Class.forName(DRVIER); connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD); System.out.println("成功连接数据库"); } catch (ClassNotFoundException e) { throw new RuntimeException("class not find !", e); } catch (SQLException e) { throw new RuntimeException("get connection error!", e); } return connection; } /** * 释放资源 */ public void ReleaseResource() { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstm != null) { try { pstm.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

3.创建一个测试类

public class Test {

    public static void main(String[] args) {
        /**
         * 增删改查完成,但是有一定局限性
         * 1.增  问题不大
         * 2.删  要给出一个值去删除(可能值不存在-->没有处理机制,值不唯一怎么处理?)
         * 3.改  同删的问题
         * 4.查  问题不大
         */
        //创建OperateOracle对象
        OperateOracle oo=new OperateOracle();
        //测试增加数据操作
        //oo.AddData("孙中山",1,25,"北京市海淀区红旗路111号");
        //测试删除数据操作
        //oo.DeleteData("孙中山");
        //测试更新数据操作
        oo.UpdateData("孙中山",1,30,"北京市东城区岳山路11号");
        //测试查询数据操作
        //oo.SelectData();
        
        //测试ResultSetMetaData类
        //oo.SelectData2();
    }

}

public class Test { public static void main(String[] args) { /** * 增删改查完成,但是有一定局限性 * 1.增 问题不大 * 2.删 要给出一个值去删除(可能值不存在-->没有处理机制,值不唯一怎么处理?) * 3.改 同删的问题 * 4.查 问题不大 */ //创建OperateOracle对象 OperateOracle oo=new OperateOracle(); //测试增加数据操作 //oo.AddData("孙中山",1,25,"北京市海淀区红旗路111号"); //测试删除数据操作 //oo.DeleteData("孙中山"); //测试更新数据操作 oo.UpdateData("孙中山",1,30,"北京市东城区岳山路11号"); //测试查询数据操作 //oo.SelectData(); //测试ResultSetMetaData类 //oo.SelectData2(); } }

正如测试类中所注释的,此处只可按照正确的方式去连接Oracle数据库,操作增删改查操作,但是对于一些错误操作的处理机制还不够完善。