Oracle连接数据库
要注意 五个接口、七个步骤
-
Connection 连接
-
Statement : 执行静态sql的对象接口
-
PrepareStatement :执行预编译的sql对象接口
-
CallableStatement : 执行sql存储过程的接口
-
ResultSet: 数据库的结果集
步骤如下:
- 导入jdbc驱动包 (取决于你的数据库是什么)
- 创建数据库连接对象
- 创建连接对象Statement
- 写一个sql
- 填充结果集
- 遍历结果集
- 关闭连接
如果如果是删除、修改、更新的话。那就只有六步了。没有遍历
1.导入依赖
如果是不用maven的话,可以到oracle的官网下载。
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.8.0.0</version>
</dependency>
2. 直接上手代码
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@ip地址:1521:服务名";
String username = "你的用户名";
String password = "密码";
Connection con = null;
Statement statement = null;
ResultSet rs = null;
try {
String sql = "update EMP set SAL = 1000 where EMPNO = 7369";
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//创建数据库连接对象
con = DriverManager.getConnection(url, username, password);
//创建statement对象
statement = con.createStatement();
//填充结果集
statement.executeQuery(sql);
System.out.println("修改完成");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (statement!=null){
statement.close();
}
if (rs!=null){
rs.close();
}
if (con!=null){
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3. 封装
像上面一样,每次都是连接驱动,重新连接数据库,很多代码的重复的,那么有没有办法可以减少一些重复的呢。
我建议这么写
因为现在操作的是SCOTT用户下的EMP表
所以可以先建立一个emp的实体类
emp.java
package odbj.pojo;
import java.util.Date;
public class Emp {
private Integer empno;
private String ename = "张三";
private String job = "java";
private Integer mgr = 7902;
private Date hiredate;
private Double sal = 0.0;
private Integer comm = 0;
private Integer deptno = 20;
public Emp() {
}
public Integer getEmpno() {
return empno;
}
public Emp setEmpno(Integer empno) {
this.empno = empno;
return this;
}
public String getEname() {
return ename;
}
public Emp setEname(String ename) {
this.ename = ename;
return this;
}
public String getJob() {
return job;
}
public Emp setJob(String job) {
this.job = job;
return this;
}
public Integer getMgr() {
return mgr;
}
public Emp setMgr(Integer mgr) {
this.mgr = mgr;
return this;
}
public Date getHiredate() {
return hiredate;
}
public Emp setHiredate(Date hiredate) {
this.hiredate = hiredate;
return this;
}
public Double getSal() {
return sal;
}
public Emp setSal(Double sal) {
this.sal = sal;
return this;
}
public Integer getComm() {
return comm;
}
public Emp setComm(Integer comm) {
this.comm = comm;
return this;
}
public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Integer comm, Integer deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Integer getDeptno() {
return deptno;
}
public Emp setDeptno(Integer deptno) {
this.deptno = deptno;return this;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate=" + hiredate +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
编写dao层 EmpDao.java
package odbj.dao;
import odbj.pojo.Emp;
import java.util.List;
public interface EmpDAO{
List<Emp> selectAll();
Emp selectById(Integer id);
void updateSal(Integer sal,Integer empno);
void insert(Emp emp);
void deleteById(Integer id);
}
实现类 实现 empDao
可以参考我的实现
package odbj.dao.impl;
import odbj.dao.EmpDAO;
import odbj.pojo.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class EmpDaoImpl implements EmpDAO {
private final String url = "jdbc:oracle:thin:@ip地址:1521:hhy";
private final String username = "scott";
private final String password = "1";
public Connection con = null;
public PreparedStatement ptm = null;
public ResultSet rs = null;
/**
* 封装Connection对象
*/
private Connection getConnect() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
/**
* 封装PreparedStatement对象
*
* @param sql
* @return
*/
private PreparedStatement getPrepareStatement(String sql) {
try {
con = getConnect();
ptm = con.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ptm;
}
/**
* 封装close方法
*/
private void close() {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (ptm != null) {
ptm.close();
ptm = null;
}
if (con != null) {
con.close();
con = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("关闭完成_(:3」∠)_。。。");
}
/**
* //查询所有的emp对象
*
* @return
*/
public List<Emp> selectAll() {
ArrayList<Emp> emps = new ArrayList<>();
try {
String sql = "select e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO from EMP e";
//加载数据库驱动
con = getConnect();
//创建statement对象
ptm = con.prepareStatement(sql);
//填充结果集
rs = ptm.executeQuery();
while (rs.next()) {
Emp emp = new Emp();
emp.setEmpno(rs.getInt("empno"))
.setEname(rs.getString("ename"))
.setJob(rs.getString("job"))
.setMgr(rs.getInt("mgr"))
.setHiredate(rs.getDate("hiredate"))
.setComm(rs.getInt("comm"))
.setDeptno(rs.getInt("deptno"));
emps.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
//关闭数据流
close();
return emps;
}
/**
* 查询单个的emp对象
*
* @param id 根据id来
* @return
*/
public Emp selectById(Integer id) {
Emp emp = new Emp();
try {
String sql = "select e.EMPNO, e.ENAME, e.JOB, e.MGR, e.HIREDATE, e.SAL, e.COMM, e.DEPTNO from EMP e where e.EMPNO = ?";
//加载数据库驱动
con = getConnect();
//创建statement对象
ptm = con.prepareStatement(sql);
ptm.setInt(1, id);
//填充结果集
rs = ptm.executeQuery();
while (rs.next()) {
emp.setEmpno(rs.getInt("empno"))
.setEname(rs.getString("ename"))
.setJob(rs.getString("job"))
.setMgr(rs.getInt("mgr"))
.setHiredate(rs.getDate("hiredate"))
.setComm(rs.getInt("comm"))
.setDeptno(rs.getInt("deptno"));
}
} catch (SQLException e) {
e.printStackTrace();
}
//关闭数据流
close();
return emp;
}
//修改工资
public void updateSal(Integer sal, Integer empno) {
String sql = "update EMP set SAL = ? where EMPNO = ?";
ptm = getPrepareStatement(sql);
try {
ptm.setInt(1, sal);
ptm.setInt(2, empno);
ptm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
close();
System.out.println("更新工资完成\\(ΦωΦ)/。。。");
}
@Override
public void insert(Emp emp) {
try {
String sql = "insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)\n" +
"values (?,?,?,?,SYSDATE,?,?,?)";
ptm = getPrepareStatement(sql);
ptm.setInt(1, emp.getEmpno());
ptm.setString(2, emp.getEname());
ptm.setString(3, emp.getJob());
ptm.setInt(4, emp.getMgr());
ptm.setDouble(5, emp.getSal());
ptm.setInt(6, emp.getComm());
ptm.setInt(7, emp.getDeptno());
ptm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("插入数据完成。。。");
close();
}
@Override
public void deleteById(Integer id) {
String sql = "delete EMP where EMPNO = ?";
try {
ptm = getPrepareStatement(sql);
ptm.setInt(1,id);
ptm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
close();
}
}
要注意的是。我这里用的是PreparedStatement对象来执行sql语句,而不是用的Statement。之所以用这个,简而言之,就是可以防止sql注入。
如果是执行insert、delete、update语句的时候可以使用executeUpdate()方法来执行sql语句。
遇到的问题
- 就是sql语句里面不要写 分号
;
要不然会报错,大概就想下面这个样子