Oracle-3JDBC连接Oracle

3.1、导入jar包

因为Oracle是收费的，所以Maven中央仓库中没有对应的jar包，所以需要导入本地jar包

选中本地下载的Jar包进行导入

3.2、创建基本数据访问类

用于获取数据库连接，关闭资源

public class Basedao {
​
    static{
        //加载驱动
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundExceptione) {
            throw new RuntimeException(e);
        }
    }
    //获取数据库连接
    public static java.sql.ConnectiongetConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:oracle:thin@192.168.191.6:1521:orcl","wateruser","zh1234");
//瘦连接远程服务器的ip地址与数据库名，用户名，登录密码     
    }
    //关闭资源
    public static void CloseAll(java.sql.ResultSetrs,java.sql.Statementstmt,java.sql.Connectioncont){
        if(rs!=null) {
            try {
                rs.close();
​
            } catch (SQLExceptione) {
                throw new RuntimeException(e);
            }
        }
        if(stmt!=null) {
            try {
                stmt.close();
​
            } catch (SQLExceptione) {
                throw new RuntimeException(e);
            }
        }
        if(cont!=null) {
            try {
                cont.close();
​
            } catch (SQLExceptione) {
                throw new RuntimeException(e);
            }
        }
    }
}

Oracle的连接方式：

1. 瘦连接

程序所在的机器上无需安装Oracle有关的任何程序，可以远程连接到该网络的远程服务器上

2. 胖连接

需要连接本地Oracle客户端，通过客户端去连接Oracle

3.3、IDEA连接Oracle配置

sql.Date与util.Date的转换

newDate(owners.getAddDate().getTime());

3.4、PLSQL报错

1. 打开虚拟机，win+R打开命令行

2. 输入sqlplus /nolog登录Oracle

3. 输入conn sys / as sysdba;以dba身份连接，然后输入登录密码(你自己设置的)

4. 输入shutdown immediate;关闭数据库

5. 输入startup;开启数据库

6. 等待出现数据库装载完毕。数据库已经打开

3.5、增加数据

static Connection connection;
static PreparedStatement stmt;
//增加业主
public static void add(Owners owners){
    try {
        connection=Basedao.getConnection();
        stmt=connection.prepareStatement("INSERT INTO WATERUSER.T_OWNERS VALUES(?,?,?,?,?,?,?)");
        stmt.setLong(1,owners.getId());
        stmt.setString(2,owners.getName());
        stmt.setLong(3,owners.getAddress());
        stmt.setString(4,owners.getHousenumber());
        stmt.setString(5,owners.getWatermeter());
        stmt.setDate(6,new Date(owners.getAddDate().getTime()));
        stmt.setLong(7,owners.getOwnertypeid());
        stmt.execute();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        Basedao.CloseAll(null,stmt,connection);//关闭资源
    }

测试类：

@Test
public void test1(){
    Owners owners=new Owners(1L,"张三",2L,"2-2","2-5",new Date(),1L);
    OwnersDao.add(owners);
}

3.6、修改数据

//修改业主
    public static void update(Owners owners){
        try {
            connection=Basedao.getConnection();
            stmt=connection.prepareStatement("UPDATE T_OWNERS SET NAME=?,ADDRESSID=?,HOUSENUMBER=?," +
                    "WATERMETER=?,ADDDATE=?,OWNERTYPEID=? WHERE ID=?");
            stmt.setString(1,owners.getName());
            stmt.setLong(2,owners.getAddress());
            stmt.setString(3,owners.getHousenumber());
            stmt.setString(4,owners.getWatermeter());
            stmt.setDate(5,new Date(owners.getAddDate().getTime()));
            stmt.setLong(6,owners.getOwnertypeid());
            stmt.setLong(7,owners.getId());
            stmt.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            Basedao.CloseAll(null,stmt,connection);//关闭资源
        }
    }

测试：

@Test
public void test2(){
    Owners owners=new Owners(1L,"张三",2L,"4-4","2-5",new Date(),1L);
    OwnersDao.update(owners);
}

3.7、删除数据

    //删除业主
    public static void delete(long id){
        try {
            connection=Basedao.getConnection();
            stmt=connection.prepareStatement("DELETE FROM T_OWNERS WHERE ID=?");
            stmt.setLong(1,id);
            stmt.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            Basedao.CloseAll(null,stmt,connection);//关闭资源
        }
    }

测试：

    @Test
    public void test3(){
        OwnersDao.delete(1L);
    }