手写JDBC，java连接oracle数据库

手写JDBC,写一个学生表，用java实现数据库的CRUD增删改查:

分层思想：

一、数据库层

1.创建表

CREATE TABLE student(
   stuno NUMBER(10) PRIMARY KEY, --学生编号
   stuname VARCHAR2(20), --姓名
   stusex VARCHAR2(4), --性别
   studesc VARCHAR2(200) --描述
)

2.创建序列

CREATE SEQUENCE seq_stuno START WITH 0 MINVALUE 0 INCREMENT BY 1
并在表中插入三条基本数据：

INSERT INTO student VALUES(seq_stuno.nextval,'张三','男','热爱学习，四有青年');
INSERT INTO student VALUES(seq_stuno.nextval,'李四','女','沉鱼落雁，闭月羞花');
INSERT INTO student VALUES(seq_stuno.nextval,'王五','男','成绩稳定，动手能力强');

二、VO(Value Object值对象)  实体层

3个对应关系：

   java 数据库

1.类 表

2.属性 字段

3.对象 记录

为什么有VO层？java编程是面向对象的，数据库编程是面向关系型数据库的，程序员如果一会写java代码， 一会儿写sql语句，体验是非常糟糕的，有了VO层，就可以把记录的CRUD操作变成java的CRUD操作。

VO层写 javabean 代码，例如：

StudentBean.java

写代码前需要写一个工具类来 加载驱动、获得连接，写在util包下，ConnOral.java

package util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class ConnOrcl {

public static Connection getConnection(){
Connection conn = null;
String className = "oracle.jdbc.driver.oracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:TEST";//TEST是要连接的数据库名
String user = "jidi16"; //用户名
String password = "123456";//密码
//1.加载驱动
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
System.out.println("类找不到");
e.printStackTrace();
}

try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
System.out.println("获取连接失败");
e.printStackTrace();
}

return conn;
}

public static void closeConnection(ResultSet rs,Statement stmt ,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
System.out.println("rs关闭失败");
e.printStackTrace();
}
}

if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
System.out.println("stmt通道关闭失败");
e.printStackTrace();
}
}

if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("conn连接关闭失败");
e.printStackTrace();
}
}
}
}

实体类：StudentBean

package vo;


import util.ConnOrcl;


public class StudentBean {
//类、属性、对象  分别对应 数据库中的 表、字段、记录
private int stuno;
private String stuname;
private String stusex;
private String studesc;

public StudentBean(){
ConnOrcl.getConnection();//保证一new对象就获取到连接
}

public StudentBean(int stuno, String stuname, String stusex, String studesc) {
super();
this.stuno = stuno;
this.stuname = stuname;
this.stusex = stusex;
this.studesc = studesc;
}
@Override
public String toString() {
return "StudentBean [stuno=" + stuno + ", stuname=" + stuname
+ ", stusex=" + stusex + ", studesc=" + studesc + "]";
}
//getter/setter方法
public int getStuno() {
return stuno;
}
public void setStuno(int stuno) {
this.stuno = stuno;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public String getStusex() {
return stusex;
}
public void setStusex(String stusex) {
this.stusex = stusex;
}
public String getStudesc() {
return studesc;
}
public void setStudesc(String studesc) {
this.studesc = studesc;
}


}

三、DAO层(Data Access Object)数据访问层（数据访问对象）

为上一层服务，面向接口编程，写一个DAO接口，再写Impl实现类

常见的三种通道：（相当于io通道，用它来发送sql语句）

1.PreparedStatement 预编译，使用?占位

语句固定不变时使用，例：每次都是insert into student values(seq_student.nextval,参数1，参数2，参数3); 

第一次通过通道发送sql语句， 编译  执行，

第二次的时候直接发参数，效率和执行速度会提升。

例：增\删\改\查1 都 固定不变，所以下面例子中用PreparedStatement

2.Statement 普通通道

每次发的语句都不一样，使用2通道，例：查全部

3.CallableStatement 调用存储过程

例：StudentDAO.java

package dao;


import java.util.List;


import vo.StudentBean;


public interface StudentDAO {
//1.增
public int addStudent(StudentBean student);
//2.删
public int deleteStudent(StudentBean student);
//3.改
public int updateStudent(StudentBean student);
//4.查单条
public StudentBean queryOneStudent(int id);
//5.查全部
public List<StudentBean> queryAllStudents(String sql);
}

为这个接口写实现类：

StudentDAOImpl.java

package dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import util.ConnOrcl;
import vo.StudentBean;


public class StudentDAOImpl implements StudentDAO {


//1.组合一个Connection
private Connection conn;

public StudentDAOImpl(){
//一步做到1.加载驱动 2.获得链接
conn = ConnOrcl.getConnection();
}

@Override
public int addStudent(StudentBean student) {
//受影响的行数
int count = 0;

String sql = "insert into student values(seq_stuno.nextval,"
+ "?,?,?)";
PreparedStatement pstmt = null;
try {
//开启事务，默认自动提交，现在手动提交
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getStuname());
pstmt.setString(2, student.getStusex());
pstmt.setString(3, student.getStudesc());

count = pstmt.executeUpdate();
conn.commit();
if(count >= 1){
System.out.println("添加一条学生记录成功");
}else{
System.out.println("没有添加学生记录");
}


} catch (SQLException e) {
System.out.println("开启PreparedStatement通道失败 或 添加学生记录失败");
e.printStackTrace();
}finally{
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("关闭事务失败");
e.printStackTrace();
}
ConnOrcl.closeConnection(null, pstmt, conn);
}

return count;
}


@Override
public int deleteStudent(StudentBean student) {
//受影响的行数
int count = 0;

String sql = "DELETE FROM student WHERE stuno = ?";
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, student.getStuno());

count = pstmt.executeUpdate();

if(count >= 1){
System.out.println("删除一条学生记录成功");
}else{
System.out.println("没有删除学生记录");
}

} catch (SQLException e) {
System.out.println("开启PreparedStatement通道失败 或 删除学生记录失败");
e.printStackTrace();
}finally{
ConnOrcl.closeConnection(null, pstmt, conn);
}

return count;
}


@Override
public int updateStudent(StudentBean student) {
int count = 0;

String sql = "UPDATE student SET stuname =?, studesc = ? WHERE stuno = ?";
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, student.getStuname());
pstmt.setString(2, student.getStudesc());
pstmt.setInt(3, student.getStuno());

count = pstmt.executeUpdate();

if(count >= 1){
System.out.println("更新一条学生记录成功");
}else{
System.out.println("没有更新学生记录");
}

} catch (SQLException e) {
System.out.println("开启PreparedStatement通道失败 或 更新学生记录失败");
e.printStackTrace();
}finally{
ConnOrcl.closeConnection(null, pstmt, conn);
}

return count;
}


@Override
public StudentBean queryOneStudent(int id) {
StudentBean stu = new StudentBean();
String sql = "select * from student where stuno = ?";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);

rs = pstmt.executeQuery();

while(rs.next()){
stu.setStuno(rs.getInt("stuno"));
stu.setStuname(rs.getString("stuname"));
stu.setStusex(rs.getString("stusex"));
stu.setStudesc(rs.getString("studesc"));
}
} catch (SQLException e) {
System.out.println("开启PreparedStatement通道失败");
e.printStackTrace();
} finally{
ConnOrcl.closeConnection(rs, pstmt, conn);
}

return stu;
}


@Override
public List<StudentBean> queryAllStudents(String sql) {
List<StudentBean> list = new ArrayList<StudentBean>();
StudentBean stu = null;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);

while(rs.next()){
stu = new StudentBean();
stu.setStuno(rs.getInt("stuno"));
stu.setStuname(rs.getString("stuname"));
stu.setStusex(rs.getString("stusex"));
stu.setStudesc(rs.getString("studesc"));

list.add(stu);
}
} catch (SQLException e) {
System.out.println("开启Statement通道失败");
e.printStackTrace();
} finally{
ConnOrcl.closeConnection(rs, stmt, conn);
}

return list;
}

}

最后测试是否能更改数据库表中的数据：

Test.java

package test;


import java.util.List;


import vo.StudentBean;
import dao.StudentDAO;
import dao.StudentDAOImpl;


public class Test {
public static void main(String[] args) {
StudentDAO stuImpl = new StudentDAOImpl();
StudentBean student = new StudentBean();


//1.增
/*student.setStuname("刘六");
student.setStusex("女");
student.setStudesc("66666666666");

stuImpl.addStudent(student);*/

//2.删

/* student.setStuno(4);
stuImpl.deleteStudent(student);*/

//3.改
/*student.setStuname("谢谢文东");
student.setStudesc("学了java后，变身知识青年");
student.setStuno(5);
stuImpl.updateStudent(student);*/

//4.查1
/*StudentBean stu = stuImpl.queryOneStudent(5);
System.out.println(stu);*/

//5.查所有
String sql = "select * from student";
List<StudentBean> list = stuImpl.queryAllStudents(sql);

for(StudentBean s:list){
System.out.println(s);
}
}
}