JDBC综合案例实现步骤、
案例实现思路
具体操作步骤
1、构建项目工程
2、将开发中需要用到的jar包和相关的工具类进行导入
DataSourceConfig配置文件代码
package utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
/*
* c3p0连接池
* 数据库连接信息的配置类
* */
public class DataSourceConfig {
private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
private static String user = "java2021";
private static String password = "java2021";
/*
* 数据源
* */
private static DataSource dataSource;
/*
* 获取数据源,即获取连接池对象
* */
public static synchronized DataSource getDataSource(){
if (dataSource==null){
initialization();
}
return dataSource;
}
// 初始化数据源
private static void initialization() {
if (dataSource != null){
((ComboPooledDataSource)dataSource).close();
}
// 具体的初始化操作
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
try {
comboPooledDataSource.setDriverClass("oracle.jdbc.driver.OracleDriver");
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
// 设置连接池的超时时间
comboPooledDataSource.setCheckoutTimeout(5000);
dataSource = comboPooledDataSource;
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
/*获取数据源*/
DataSource dataSource = DataSourceConfig.getDataSource();
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
3、设计数据
3.1、创建表结构
3.2、创建对应的序列(oracle)
3.3、添加测试数据
-- Create table
create table T_STUDENT
(
SID NUMBER(5) not null,
SNAME VARCHAR2(20) not null,
R_SSEX VARCHAR2(5),
SAGE NUMBER(5) default 18,
SADDRESS VARCHAR2(30),
BIRTHDAY DATE,
SSEX CHAR(1),
SCLASSID NUMBER(5)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_STUDENT
add primary key (SID)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_STUDENT
add constraint FK_CID foreign key (SCLASSID)
references T_CLASS (CID);
4、对项目进行分包
5、根据表结构创建对应的javabean
package com.itxh.javabean;
public class StudentBean {
private int sid;
private String sname;
private String rssex;
private int sage;
public StudentBean() {
}
public StudentBean(int sid, String sname, String rssex, int sage) {
this.sid = sid;
this.sname = sname;
this.rssex = rssex;
this.sage = sage;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getRssex() {
return rssex;
}
public void setRssex(String rssex) {
this.rssex = rssex;
}
public int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
@Override
public String toString() {
return "StudentBean{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", rssex='" + rssex + '\'' +
", sage=" + sage +
'}';
}
}
6、实现dao层
6.1、接口
接口的命名规则:I+含义+Dao
定义管理学员的方法:
添加,修改,删除,查询
package com.itxh.dao;
import com.itxh.javabean.StudentBean;
import java.util.List;
/*
* 定义管理学生信息的方法
* */
public interface IStudentDao {
/*
* 添加学生信息
* @param stu 表示要添加的学生对象
* @return
* -1 表示添加失败
* 其它则表示添加成功
* */
int add(StudentBean stu);
List<StudentBean> queryAll();
int updateStudent(StudentBean stUpdate);
int delete(int id);
StudentBean queryById(int stuId);
}
6.2、实现类
实现类的命名规则:含义+dao+Impl
实现定义管理学生的方法
添加,修改,删除,查询
package com.itxh.dao.impl;
import com.itxh.dao.IStudentDao;
import com.itxh.javabean.StudentBean;
import com.itxh.utils.DataSourceConfig;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements IStudentDao {
private String sql = "";
private PreparedStatement ps;
private Connection conn = null;
private ResultSet rs;
// 保存多个学生对象
private List<StudentBean> stuList = new ArrayList<>();
@Override
public int add(StudentBean stu) {
// System.out.println(stu);
try {
// 获取数据库连接通道 c3p0
conn = DataSourceConfig.getDataSource().getConnection();
// 构建SQL语句,使用占位符,避免SQL注入
sql = "insert into t_student(sid,sname,r_ssex,sage)" +
"values(seq_student.nextval,?,?,?)";
// 获取SQL语句的执行对象
ps = conn.prepareStatement(sql);
// 给占位符赋值
ps.setString(1,stu.getSname());
ps.setString(2,stu.getRssex());
ps.setInt(3,stu.getSage());
// 执行SQL语句
return ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try{
if (ps != null){
ps.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
return -1;
}
@Override
public List<StudentBean> queryAll() {
try {
// 获取数据库连接通道
conn = DataSourceConfig.getDataSource().getConnection();
// 构建SQL语句
sql = "select * from t_student";
// 获取执行SQL语句对象
ps = conn.prepareStatement(sql);
// 执行SQL语句
rs = ps.executeQuery();
// 处理结果集
/*
* rs.next():执行该方法后,会判断当前指针所指向的当前行是否存在下一行,如果存在下一行(数据),
* 则返回TRUE,并将指针指向下一行,将下一行数据保存到rs对象中
* 反之:FALSE
* */
while (rs.next()){
// 获取查询的数据
int sid = rs.getInt("sid");
String sname = rs.getString(2);
String rssex = rs.getString("r_ssex");
int sage = rs.getInt("sage");
// 创建一个学生对象
StudentBean stu = new StudentBean(sid,sname,rssex,sage);
// 将保存好的学生对象添加到集合中
stuList.add(stu);
}
return stuList;
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
@Override
public int updateStudent(StudentBean stUpdate) {
try {
// 获取数据库连接通道 c3p0
conn = DataSourceConfig.getDataSource().getConnection();
// 构建SQL语句,使用占位符,避免SQL注入
sql = "update t_student set sname=?,sage=? where sid=?";
// 获取SQL语句的执行对象
ps = conn.prepareStatement(sql);
// 给占位符赋值
ps.setString(1,stUpdate.getSname());
ps.setInt(2,stUpdate.getSage());
ps.setInt(3,stUpdate.getSid());
// 执行SQL语句
return ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try{
if (ps != null){
ps.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
return -1;
}
@Override
public int delete(int id) {
try {
conn = DataSourceConfig.getDataSource().getConnection();
sql = "delete from t_student where sid = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1,id);
return ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try{
if (ps != null){
ps.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
return -1;
}
@Override
public StudentBean queryById(int stuId) {
try {
// 获取数据库连接通道
conn = DataSourceConfig.getDataSource().getConnection();
// 构建SQL语句
sql = "select * from t_student where sid = ?";
// 获取执行SQL语句对象
ps = conn.prepareStatement(sql);
ps.setInt(1,stuId);
// 执行SQL语句
rs = ps.executeQuery();
// 处理结果集
/*
* rs.next():执行该方法后,会判断当前指针所指向的当前行是否存在下一行,如果存在下一行(数据),
* 则返回TRUE,并将指针指向下一行,将下一行数据保存到rs对象中
* 反之:FALSE
* */
if (rs.next()){
// 获取查询的数据
int sid = rs.getInt("sid");
String sname = rs.getString(2);
String rssex = rs.getString("r_ssex");
int sage = rs.getInt("sage");
// 创建一个学生对象
StudentBean stu = new StudentBean(sid,sname,rssex,sage);
return stu;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
}
7、实现Service层
7.1、接口
接口的命名规则:I+含义+Service(因为当前业务逻辑不复杂,直接将接收到的值或者参数传递到dao层)
定义处理学员业务逻辑的方法:
添加,修改,删除,查询
package com.itxh.service;
import com.itxh.javabean.StudentBean;
import java.util.List;
/*
* 定义处理学生信息业务逻辑的方法
* */
public interface IStudentService {
/*
* 添加学生信息
* @param stu 表示要添加的学生对象
* @return
* -1 表示添加失败
* 其它则表示添加成功
* */
int add(StudentBean stu);
/*
* 查询所有学员信息
* */
List<StudentBean> queryAll();
/*
* 修改学员信息
* */
int updateStudent(StudentBean stUpdate);
int delete(int id);
StudentBean queryById(int stuId);
}
7.2、实现类
实现类的命名规则:含义+service+Impl
实现接口中的方法
添加,修改,删除,查询
package com.itxh.service.impl;
import com.itxh.dao.IStudentDao;
import com.itxh.dao.impl.StudentDaoImpl;
import com.itxh.javabean.StudentBean;
import com.itxh.service.IStudentService;
import java.util.List;
public class StudentServiceImpl extends StudentDaoImpl implements IStudentService {
@Override
public int add(StudentBean stu) {
return super.add(stu);
}
@Override
public List<StudentBean> queryAll() {
return super.queryAll();
}
@Override
public int updateStudent(StudentBean stUpdate) {
return super.updateStudent(stUpdate);
}
@Override
public int delete(int id) {
return super.delete(id);
}
@Override
public StudentBean queryById(int stuId) {
return super.queryById(stuId);
}
}
8、界面的实现
package com.itxh.view;
import com.itxh.javabean.StudentBean;
import com.itxh.service.IStudentService;
import com.itxh.service.impl.StudentServiceImpl;
import java.util.List;
import java.util.Scanner;
/*
* 学生管理系统界面
* */
public class MainView {
private static int count = 0;
public static void main(String[] args) {
shwoMain();
}
private static void shwoMain() {
System.out.println("*************************欢迎进入学生管理系统*****************************");
System.out.println("*************************1、查询所有学生信息*****************************");
System.out.println("*************************2、录入学生信息*****************************");
System.out.println("*************************3、修改学生信息*****************************");
System.out.println("*************************4、删除学生信息*****************************");
System.out.println("*************************5、根据学号查询学生信息*****************************");
System.out.println("*************************6、退出学生管理系统*****************************");
Scanner sc = new Scanner(System.in);
System.out.println("请输入操作指令");
int i = sc.nextInt();
// 创建service对象
IStudentService stuService = new StudentServiceImpl();
switch (i){
case 1://查询所有学员信息
List<StudentBean> stuList = stuService.queryAll();
// 遍历查询出来学员信息
for (StudentBean studentBean : stuList) {
System.out.println(studentBean);
}
shwoMain();
break;
case 2:
System.out.println("请输入你要录入的学员的姓名:");
// 控制台输入学生姓名
String sname = sc.next();
StudentBean stu = new StudentBean();
// 将控制台输入的数据保存到javabean中
stu.setSname(sname);
System.out.println("请输入你要录入的学员的性别:");
String rssex = sc.next();
stu.setRssex(rssex);
System.out.println("请输入你要录入的学员的年龄:");
int sage = sc.nextInt();
stu.setSage(sage);
// 执行service中的方法,获取值,并将其传递到dao层
count = stuService.add(stu);
if (count!=-1){
System.out.println("学员信息录入成功");
}else{
System.out.println("学员信息录入失败");
}
shwoMain();
break;
case 3:
System.out.println("请输入你要修改的学生学号:");
int sid = sc.nextInt();
System.out.println("请输入要修改的学员姓名:");
String name = sc.next();
System.out.println("请输入要修改的学员年龄:");
int age = sc.nextInt();
StudentBean stUpdate = new StudentBean();
stUpdate.setSid(sid);
stUpdate.setSname(name);
stUpdate.setSage(age);
count = stuService.updateStudent(stUpdate);
if (count!=-1){
System.out.println("学员信息修改成功");
}else{
System.out.println("学员信息修改失败");
}
shwoMain();
break;
case 4:
System.out.println("请输入要删除的学员学号:");
int id = sc.nextInt();
count = stuService.delete(id);
if (count!=-1){
System.out.println("学员信息删除成功");
}else{
System.out.println("学员信息删除失败");
}
shwoMain();
break;
case 5:
System.out.println("请输入要查询的学员学号:");
int stuId = sc.nextInt();
StudentBean studentBean = stuService.queryById(stuId);
System.out.println(studentBean);
shwoMain();
break;
case 6:
System.out.println("谢谢使用");
break;
default:
System.out.println("你输入的操作指令错误");
break;
}
}
}