package com.test.dao;

import java.sql.*;

/**

 * Created by wdw on 2017/9/16.

 */

public class OraclePersionDao {

    // 数据库驱动类

    private String dbDriver = "oracle.jdbc.driver.OracleDriver";

    // 连接数据库url

    private String dbURL = "jdbc:oracle:thin:@192.168.31.128:1521:orcl";

    // 连接数据库用户名

    private String dbUser = "PHIP";

    // 连接数据库密码

    private String dbPwd = "PHIP";

    // 获取数据库连接方法, 返回Connection对象

    private Connection con = null;

    //数据执行语句

    private Statement stat = null;

    private String sql = null;

    private ResultSet rs = null;

    //创建数据库连接

    public Connection getDBConnect() {

        try {

// 加载数据库驱动

            Class.forName(dbDriver);

            con = DriverManager.getConnection(dbURL, dbUser, dbPwd);

        } catch (Exception e) {

            System.out.println(e);

        }

        return con;

    }

    //增加

    public void Add() {

        con = getDBConnect();

        sql = "insert into sys_users(id,code,name)" +

                "values('1','lucy','w')";

        try {

            stat = con.createStatement();

            stat.executeUpdate(sql);

        }

        catch (SQLException e) {

// TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    //删除

    public void Delete() {

        con = getDBConnect();

        sql = "delete  from sys_users " +

                "where ID=1";

        try {

            stat = con.createStatement();

            stat.executeUpdate(sql);

        } catch (SQLException e) {

// TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    //修改

    public void Update() {

        con = getDBConnect();

        sql = "update sys_users set code='c2' where id='1'";

        try {

            stat = con.createStatement();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    //查询

    public void Search() {

        con = getDBConnect();

// 创建表的sql语句

        sql = "SELECT * FROM sys_users";

        try {

            stat = con.createStatement();

            rs = stat.executeQuery(sql);

            while (rs.next()) {

                String id = rs.getString(1);

                String stuname = rs.getString(2);

                String stusex = rs.getString(3);

                System.out.println(id + "," + stuname + "," + stusex);

            }

        } catch (SQLException e) {

// TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

//功能:关闭数据库的连接

    public void close() {//6.释放资源

        try { // 捕捉异常

            try {

                if (rs != null) { // 当ResultSet对象的实例rs不为空时

                    rs.close(); // 关闭ResultSet对象

                }

            } finally {

                try {

                    if (stat != null) { // 当Statement对象的实例stmt不为空时

                        stat.close(); // 关闭Statement对象

                    }

                } finally {

                    if (con != null) { // 当Connection对象的实例conn不为空时

                        con.close(); // 关闭Connection对象

                    }

                }

            }

        } catch (Exception e) {

            e.printStackTrace(System.err); // 输出异常信息

        }

    }

}

 private static void MySqlOperator() {

        OraclePersionDao oracl = new OraclePersionDao();

        oracl.Add();

        oracl.Search();

        oracl.Update();

        oracl.Delete();

    }