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的连接方式:
瘦连接
程序所在的机器上无需安装Oracle有关的任何程序,可以远程连接到该网络的远程服务器上
胖连接
需要连接本地Oracle客户端,通过客户端去连接Oracle
3.3、IDEA连接Oracle配置
sql.Date与util.Date的转换
newDate(owners.getAddDate().getTime());
3.4、PLSQL报错
打开虚拟机,win+R打开命令行
输入sqlplus /nolog登录Oracle
输入conn sys / as sysdba;以dba身份连接,然后输入登录密码(你自己设置的)
输入shutdown immediate;关闭数据库
输入startup;开启数据库
等待出现数据库装载完毕。数据库已经打开
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);
}