import java.sql.*;

import java.util.ArrayList;

import java.util.List;

//使用jdbc连接

public class TestOra {

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        BaseDao basedao = new BaseDao();

        Connection conn = basedao.getConnection();

        basedao.add(conn);

        basedao.delete(conn);

        basedao.update(conn);

        basedao.query(conn);

        basedao.close();

    }

}

class BaseDao {

    private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";

    private static String user = "c##scott";

    private static String password = "tiger";

    private Connection conn;

    private static Statement sm;

    private static ResultSet rs;

    private static String sql;

    // 连接数据库函数 

    public Connection getConnection() {

        try {

            // 初始化驱动包

            Class.forName("oracle.jdbc.OracleDriver");

            // 根据数据库连接字符，名称，密码给conn

            System.out.println("开始尝试连接数据库！");

            conn = DriverManager.getConnection(url, user, password);

        } catch (Exception e) {

            e.printStackTrace();

        }

        return conn;

    }

    // 查询函数

    public void query(Connection conn) {

        sql = "select * from EMP";

        try {

            sm = conn.createStatement();

            rs = sm.executeQuery(sql);

            while (rs.next()) {

                System.out.println("ID: " + rs.getString(1) + "\tNAME： "

                        + rs.getString(2) + "\tAGE： " + rs.getString(3));

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    // 添加表数据

    public void add(Connection conn) {

        sql = "insert into EMP(ID,NAME,AGE)" + " values ('0005','lucyyyy','14')";

        try {

            sm = conn.createStatement();

            sm.executeUpdate(sql);

            System.out.println("添加成功");

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    // 删除数据

    public void delete(Connection conn) {

        sql = "delete from EMP " + "where ID='2'";

        try {

            sm = conn.createStatement();

            sm.executeUpdate(sql);

            System.out.println("删除成功");

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    // 修改数据

    public void update(Connection conn) {

        sql = "update EMP set ID='2' where NAME='lucy'";

        try {

            sm = conn.createStatement();

            sm.executeUpdate(sql);

            System.out.println("更新成功");

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

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

        try { // 捕捉异常

            try {

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

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

                }

            } finally {

                try {

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

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

                    }

                } finally {

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

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

                    }

                }

            }

        } catch (Exception e) {

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

        }

    }

}