总结关于在ORACLE用JDBC访问数据
JDBC访问数据库的步骤:
1、注册驱动
2、连接数据库
3、创建一个statement
4、执行sql
5、处理结果集
6、关闭数据连接
注意execute、executeUpdate、executeQuery
以上均为statement接口中的方法
statement 接口
execute(String sql)
执行给定的 SQL 语句,该语句可能返回多个结果。
executeQuery(String sql)
执行给定的 SQL 语句,该语句返回单个 ResultSet 对象
executeUpdate(String sql)
执行给定 SQL 语句,该语句可能为 INSERT、UPDATE 或 DELETE 语句,或者不返回任何内容的 SQL 语句(如 SQL DDL 语句)
头2种一般在查询中使用
最后一个在插入、更新、删除时使用
execute(String sql)
执行给定的 SQL 语句,该语句可能返回多个结果。
executeQuery(String sql)
执行给定的 SQL 语句,该语句返回单个 ResultSet 对象
executeUpdate(String sql)
执行给定 SQL 语句,该语句可能为 INSERT、UPDATE 或 DELETE 语句,或者不返回任何内容的 SQL 语句(如 SQL DDL 语句)
头2种一般在查询中使用
最后一个在插入、更新、删除时使用
下面是增删改查的具体程序
基于MVC设计思想,为了日后的维护更加方便所以以下做法:
Sql语句
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINTS EMP_PK PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
Propoties文件:db2.properties
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
user=scott
password=tiger
读Properties文件并进行数据库连接 : DBUtils.java
package util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtils {
private static String driver;
private static String url;
private static String user;
private static String password;
static{
Properties props = new Properties();
try {
props.load(DBUtils.class.getClassLoader().getResourceAsStream(
"db2.properties"));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection openConnection() throws SQLException{
Connection con = DriverManager.
getConnection(url,user,password);
return con;
}
public static void closeConnection(Connection con){
if(con != null){
try {
con.close();
} catch (SQLException e) {
}
}
}
public static void main(String[] args)throws Exception {
Connection con = openConnection();
System.out.println(con);
}
}
entity实体类 emp.java
import java.sql.Date;
public class Emp {
private Integer empNo;
private String eName;
private String job;
private Integer managerId;
private Date hireDate;
private double salary;
private double comm;
private Integer deptNo;
}
//get/set方法略
DAOFactory.java
import dao.impl.EmpDAOImpl;
public class DAOFactory {
private static EmpDAO empDAO = new EmpDAOImpl();
public static EmpDAO getEmpDAO() {
return empDAO;
}
}
EmpDAO.java
import java.util.List;
import entity.Emp;
//规定出了Emp表在此项目中的全部方法
public interface EmpDAO {
//增加操作
public void insert(Emp emp) throws Exception;
//修改操作
public void update(Emp emp)throws Exception;
//删除操作
public void delete(int empNO )throws Exception;
//按ID查询操作
public Emp queryByNO(int empNo)throws Exception;
//查询全部
public List<Emp> queryAll() throws Exception;
//模糊查询
public List<Emp> queryByLike(String cond)throws Exception;
}
EmpDAOImpl.java(实现接口的类)
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import util.DBUtils;
import dao.EmpDAO;
import entity.Emp;
public class EmpDAOImpl implements EmpDAO {
//插入数据
public void insert(Emp emp) throws Exception {
String sql = "INSERT INTO Emp (empNo,eName,job,mgr,hireDate,sal,comm,deptNo) VALUES(?,?,?,?,?,?,?,?)";
PreparedStatement pst = null;
Connection conn = null;
try {
conn = DBUtils.openConnection();
pst = conn.prepareStatement(sql);
pst.setInt(1, emp.getEmpNo());
pst.setString(2, emp.geteName());
pst.setString(3, emp.getJob());
pst.setInt(4, emp.getManagerId());
pst.setDate(5, emp.getHireDate());
pst.setDouble(6, emp.getSalary());
pst.setDouble(7, emp.getComm());
pst.setInt(8, emp.getDeptNo());
//进行数据更新操作
pst.executeQuery();
//关闭statement
pst.close();
} catch (Exception e) {
e.printStackTrace();//打印错误信息
throw new Exception("数据访问异常");
}finally{
DBUtils.closeConnection(conn);
}
}
//修改数据
public void update(Emp emp) throws Exception {
String sql = "UPDATE emp SET eName = ?,job=?,mgr=?,hireDate=?,sal=?,comm=?,deptNo=? where empNo=?";
Connection conn = null;
PreparedStatement pst = null;
try {
conn = DBUtils.openConnection();
pst = conn.prepareStatement(sql);
pst.setString(1,emp.geteName());
pst.setString(2, emp.getJob());
pst.setInt(3, emp.getManagerId());
pst.setDate(4, emp.getHireDate());
pst.setDouble(5, emp.getSalary());
pst.setDouble(6, emp.getComm());
pst.setInt(7, emp.getDeptNo());
pst.setInt(8, emp.getEmpNo());
pst.executeUpdate();//executeQuery都可以 executeUpdate更合适于insert update delete这些无返回操作的sql
pst.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("数据访问异常");
}finally{
DBUtils.closeConnection(conn);
}
}
//删除数据(按条件删除)
public void delete(int empNo) throws Exception {
String sql ="DELETE FROM emp WHERE empno = ? ";
Connection conn = null;
PreparedStatement pst = null;
try {
conn = DBUtils.openConnection();
pst = conn.prepareStatement(sql);
pst.setInt(1, empNo);
pst.executeQuery();
pst.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("数据访问异常");
}finally{
DBUtils.closeConnection(conn);
}
}
//按条件查找数据
public Emp queryByNO(int empNo) throws Exception {
Emp emp = null;//先创建对象
String sql = "SELECT empno, eName,job,mgr,hireDate,sal,comm,deptNo FROM emp WHERE empNo= ? ";
Connection conn = null;
PreparedStatement pst = null;
try {
conn = DBUtils.openConnection();
pst = conn.prepareStatement(sql);
pst.setInt(1, empNo);
ResultSet rs = pst.executeQuery();
if(rs.next()){ //因为只有一条记录所以不需要while 用if就已经满足啦
//查询内容,之后查询出来的内容赋值给emp对象
emp = new Emp(); //以下是查询出来的结果集 sql有才能在这里写的
emp.setEmpNo(rs.getInt("EMPNO"));
emp.seteName(rs.getString("ENAME"));
emp.setJob(rs.getString("JOB"));
emp.setManagerId(rs.getInt("MGR"));
emp.setHireDate(rs.getDate("HIREDATE"));
emp.setSalary(rs.getDouble("SAL"));
emp.setComm(rs.getDouble("COMM"));
emp.setDeptNo(rs.getInt("DEPTNO"));
}
rs.close();
pst.close();
} catch (Exception e) {
e.printStackTrace();
throw new Exception("数据访问异常");
}finally{
DBUtils.closeConnection(conn);
}
return emp;
}
//模糊查询数据
public List<Emp> queryAll() throws Exception {
Connection conn = null;
PreparedStatement pst = null;
List<Emp> empList = new ArrayList();//List<emp> empList = new ArrayList();这样的话更加明确类型
String sql = "SELECT empno, eName,job,mgr,hireDate,sal,comm,deptNo FROM emp ";
try {
conn = DBUtils.openConnection();
pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
while(rs.next()){
Emp emp = new Emp();//每一条记录都要生成一个新的emp对象
//以下是查询出来的结果集 sql有才能在这里写的
emp.setEmpNo(rs.getInt("EMPNO"));
emp.seteName(rs.getString("ENAME"));
emp.setJob(rs.getString("JOB"));
emp.setManagerId(rs.getInt("MGR"));
emp.setHireDate(rs.getDate("HIREDATE"));
emp.setSalary(rs.getDouble("SAL"));
emp.setComm(rs.getDouble("COMM"));
emp.setDeptNo(rs.getInt("DEPTNO"));
empList.add(emp);
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("数据访问异常");
}finally{
DBUtils.closeConnection(conn);
}
return empList;
}
public List queryByLike(String ename) throws Exception {
Connection conn = null;
PreparedStatement pst = null;
List empList = new ArrayList();
String sql = "SELECT empno, eName,job,mgr,hireDate,sal,comm,deptNo FROM emp WHERE ename like ?";
try {
conn = DBUtils.openConnection();
pst = conn.prepareStatement(sql);
pst.setString(1, "%"+ename+"%");//%左右不能有空格,否则不能查询
ResultSet rs = pst.executeQuery();
while(rs.next()){
Emp emp = new Emp();
emp.setEmpNo(rs.getInt("EMPNO"));
emp.seteName(rs.getString("ENAME"));
emp.setJob(rs.getString("JOB"));
emp.setManagerId(rs.getInt("MGR"));
emp.setHireDate(rs.getDate("HIREDATE"));
emp.setSalary(rs.getDouble("SAL"));
emp.setComm(rs.getDouble("COMM"));
emp.setDeptNo(rs.getInt("DEPTNO"));
empList.add(emp);
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("数据访问异常");
}finally{
DBUtils.closeConnection(conn);
}
return empList;
}
}
测试方法.TestDAO.java
package test;
import java.sql.Date;
import java.util.List;
import org.junit.Test;
import dao.DAOFactory;
import dao.EmpDAO;
import entity.Emp;
public class TestEmp {
@Test
public void TestInsert() throws Exception{
EmpDAO empDAO = DAOFactory.getEmpDAO();
Emp emp = new Emp();
emp.setEmpNo(7935);
emp.seteName("JOHNSON");
emp.setJob("NURSE");
emp.setManagerId(7902);
emp.setHireDate(new Date(System.currentTimeMillis()));
emp.setSalary(4000.0);
emp.setComm(0);
emp.setDeptNo(20);
empDAO.insert(emp);
}
@Test
public void TestUpdate() throws Exception{
EmpDAO empDAO = DAOFactory.getEmpDAO();
Emp emp = new Emp();
emp.setEmpNo(7935);
emp.seteName("JOHNSONkk");
emp.setJob("PLARYER");
emp.setManagerId(7902);
emp.setHireDate(new Date(System.currentTimeMillis()));
emp.setSalary(60000);
emp.setComm(0);
emp.setDeptNo(30);
empDAO.update(emp);
}
@Test
public void TestDelete() throws Exception{
EmpDAO empDAO= DAOFactory.getEmpDAO();
empDAO.delete(7937);
System.out.println("it had deleted");
}
@Test
public void TestQueryByNO() throws Exception{
EmpDAO empDAO= DAOFactory.getEmpDAO();
Emp emp = empDAO.queryByNO(7936);
System.out.println(emp);
System.out.println("编号"+emp.getEmpNo()+"姓名"+emp.geteName()+"工作"+emp.getJob()+"所属经理"+emp.getManagerId()+"/n" +
"工作日期"+emp.getHireDate()+"薪金"+emp.getSalary()+"COMM"+emp.getComm()+"部门编号"+emp.getEmpNo());
System.out.println("it had queryByNo");
}
@Test
public void TestQueryALL() throws Exception{
EmpDAO empDAO= DAOFactory.getEmpDAO();
List<Emp> empList = empDAO.queryAll();
for(Emp emp: empList){
System.out.println("编号"+emp.getEmpNo()+"姓名"+emp.geteName()+"工作"+emp.getJob()+"所属经理"+emp.getManagerId()+"/n" +
"工作日期"+emp.getHireDate()+"薪金"+emp.getSalary()+"COMM"+emp.getComm()+"部门编号"+emp.getEmpNo());
}
System.out.println("it had queryByNo");
}
@Test
public void TestQueryLike() throws Exception{
EmpDAO empDAO= DAOFactory.getEmpDAO();
List<Emp> empList = empDAO.queryByLike("O");
for(Emp emp: empList){
System.out.println("编号"+emp.getEmpNo()+"姓名"+emp.geteName()+"工作"+emp.getJob()+"所属经理"+emp.getManagerId()+"/n" +
"工作日期"+emp.getHireDate()+"薪金"+emp.getSalary()+"COMM"+emp.getComm()+"部门编号"+emp.getEmpNo());
}
System.out.println("it had queryByNo");
}
}
希望对广大初学者有帮助吧