1、首先先创建两张表admin和student表
--管理员
create table admin(
mid int primary key,
username varchar2(25),
pwd varchar2(20)--一定注意最后一行没有逗号
);
--给管理员表创建序列,把序列当作管理员的id
create sequence seq_admin;
--由于这里我们不做注册验证的功能
--所以直接从数据库中添加管理员的账户
--这里赋值的时候直接将序列的下一个赋值给mid
insert into admin values(seq_admin.nextval,'admin','admin');
select * from admin;
--学生表
create table student(
id int primary key,
name varchar2(25),
age int,
sex varchar2(2),
grade varchar2(4),
tel varchar2(11),
emai varchar2(50),
addr varchar2(100)
);
--创建学生表的索引
create sequence seq_student;
2、创建数据库的连接工具类JDBCUtil.java
package com.zt.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class JDBCUtil {
//用main函数来测试驱动是否连接成功
public static void main(String[] args) {
JDBCUtil jdbc = new JDBCUtil();
Connection con = jdbc.getConnection();
System.out.println(con);
}
//打印结果:oracle.jdbc.driver.T4CConnection@3a0d2766
//加载驱动
//这里的rb是给静态块调用的,所以一定要为static属性
//静态只能调用静态的
private static ResourceBundle rb = ResourceBundle.getBundle("jdbc");
static{
try {
Class.forName(rb.getString("driver"));
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接
public Connection getConnection (){
//注意这里的连接函数的名字要和DAO里面对应起来
//这里用的什么名字,在DAO文件里面用jdbc.getConnection来调用
Connection con = null;
try {
//这里获取连接是DriverManager.getConnection(url, user, password)
//需要选择三个参数的
con = DriverManager.getConnection(rb.getString("url"),rb.getString("user"),rb.getString("pwd"));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
//删除连接
public void closeAll(Connection con,Statement st,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(con!=null){
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3、创建实体类admin和student
package com.zt.entity;
public class Admin {
private String username;
private String pwd;
public Admin() {
super();
// TODO Auto-generated constructor stub
}
public Admin(String username, String pwd) {
super();
this.username = username;
this.pwd = pwd;
}
@Override
public String toString() {
return "Admin [username=" + username + ", pwd=" + pwd + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((pwd == null) ? 0 : pwd.hashCode());
result = prime * result + ((username == null) ? 0 : username.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Admin other = (Admin) obj;
if (pwd == null) {
if (other.pwd != null)
return false;
} else if (!pwd.equals(other.pwd))
return false;
if (username == null) {
if (other.username != null)
return false;
} else if (!username.equals(other.username))
return false;
return true;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
}
package com.zt.entity;
//package entity;
public class Student {
private int id;
private String name;
private int age;
private String sex;
private String grade;
private String tel;
private String email;
private String addr;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(int id, String name, int age, String sex, String grade,
String tel, String email, String addr) {
super();
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.grade = grade;
this.tel = tel;
this.email = email;
this.addr = addr;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + ", sex="
+ sex + ", grade=" + grade + ", tel=" + tel + ", email=" + email
+ ", addr=" + addr + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((addr == null) ? 0 : addr.hashCode());
result = prime * result + age;
result = prime * result + ((email == null) ? 0 : email.hashCode());
result = prime * result + ((grade == null) ? 0 : grade.hashCode());
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result + ((sex == null) ? 0 : sex.hashCode());
result = prime * result + ((tel == null) ? 0 : tel.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Student other = (Student) obj;
if (addr == null) {
if (other.addr != null)
return false;
} else if (!addr.equals(other.addr))
return false;
if (age != other.age)
return false;
if (email == null) {
if (other.email != null)
return false;
} else if (!email.equals(other.email))
return false;
if (grade == null) {
if (other.grade != null)
return false;
} else if (!grade.equals(other.grade))
return false;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (sex == null) {
if (other.sex != null)
return false;
} else if (!sex.equals(other.sex))
return false;
if (tel == null) {
if (other.tel != null)
return false;
} else if (!tel.equals(other.tel))
return false;
return true;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
}
4、创建数据库操纵方法函数类studentDAO.Java adminDAO.Java
对于studentDAO无非就是增删改查四个方法,然后查又分为查询所有,按照id查询,模糊查询等
那么我们先写添加
public class StudentDAO {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
JDBCUtil jdbc = new JDBCUtil();
/*
* 添加学生
* 添加的学生信息通过包装成一个student对象作为函数的参数添加进来
* 传递给ps,然后由ps添加进数据库
*/
public boolean save(Student stu){
boolean flg =false;
con = jdbc.getConnection();
try {
String sql = "insert into student(id,name,age,sex,grade,tel,emai,addr) values(seq_student.nextval,?,?,?,?,?,?,?)";
//这里的每一个问号都需要通过ps预处理来赋值
//赋的值需要外界通过一个student对象的参数传进来
//然后由ps添加进数据库
//ps是直接和数据库中的数据表打交道
//这里由ps给问号赋值的时候一定要一一对应,并且是从问号的个数开始的
//这里的索引表示第几个问号,而不是第几个字段
/**
* 在进行赋值之前还需要进行预处理
*/
ps = con.prepareStatement(sql);
//准备好了SQL语句还需要写出来,写这一步就是一个预处理的过程
//不然后面赋值的ps从哪来
//这里的第一个参数直接由数据库自动生成的序列进行赋值
//所以这里从第二个字段开始赋值
ps.setString(1, stu.getName());
ps.setInt(2, stu.getAge());
ps.setString(3, stu.getSex());
ps.setString(4, stu.getGrade());
ps.setString(5, stu.getTel());
ps.setString(6, stu.getEmail());
ps.setString(7, stu.getAddr());
//给每个问号赋值好之后就是开始执行SQL语句了
//这里执行SQL语句应该用executeupdate方法
//返回的是影响的行数,如果执行成功,就是count》0
int count = ps.executeUpdate();
if(count>0){
flg=true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flg;
}
}
然后在测试类里面进行测试
public class Test {
StudentDAO sd = new StudentDAO();
//Student stu = null;
/**
* 测试添加学生
*/
@org.junit.Test
public void testSave(){
Student stu = new Student(-1, "张三", 12, "男","中级" , "12345678901", "12334455@qq.com", "北京");
boolean flg = sd.save(stu);
System.out.println(flg);
}
}
打印结果为true
那么我们在数据库里面验证一下
发现插入成功了,我们发现这里张三的id为1,而不是我们在测试文件里面写的-1,因为这里其实根本没用,因为在DAO文件里面,我们直接将seq_student.nextval赋值给id了,预处理赋值的时候只添加了后面的七个,所以无论我们从用户角度输入什么id,都没用,因为根本没有存入数据库,相当于这里的id是自动添加的,这样的好处是可以防止主键重复
注意这里我们导入了Junit4包,那么我们就不用再通过main函数来调用函数了,而是可以直接运行需要测试的函数名字就行了