实验目的：
1、熟悉数据库基本操作
2、掌握利用JDBC进行数据库的连接
3、利用语句对象Statement和PreparedStatement对表、记录、列进行增、删、改、查等操作
4、将数据库操作封装成类
5、了解单元测试Junit
6、掌握事务的概念，在编程中处理事务
7、掌握三层架构编程思想
实验内容：
1、JDBC单表记录的增删改查
已知：建立表student，并向表里插入几条记录，
create table student(
id int PRIMARY KEY auto_increment,
name varchar(20) not null,
schoolin date not null,
score float not null);
insert into student values(null,’李丽’,’2015-09-01’,86);
insert into student values(null,’王五’,’2016-09-01’,99);
insert into student values(null,’张三’,’2014-09-01’,88);
要求：用JAVA程序实现如下功能：
1、向表中增加记录并显示所有记录（数据自己指定）；
2、从表中删除id=1的记录并显示所有记录；
3、修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；
4、查询表中id=3的记录并显示；
//面向过程
package sdut.cn.test;
import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class JDBCTest {
public static void main(String[] args) {
try {
addStudent("李丽", Change("2015-09-01"), 86);
addStudent("王五", Change("2016-09-01"), 99);
addStudent("张三", Change("2014-09-01"), 88);
addStudent("郭老师", Change("2020-09-01"), 62);
selectAll();
deleteStudent(1);
selectAll();
updateStudent("山东理工", 3);
selectAll();
selectOne(3);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 0 String->date
private static Timestamp Change(String time) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
date = sdf.parse(time);
} catch (ParseException e) {
e.printStackTrace();
}
// javax.sql.Date与java.util.Date的转换方式
Timestamp ts = new Timestamp(date.getTime());// 把日期转为为微秒数，然后丢到ts的构造函数中。
return ts;
}
// 1.增加记录
private static void addStudent(String name, Timestamp ts, float score) throws ClassNotFoundException, SQLException {
// 1 driver
Class.forName("com.mysql.jdbc.Driver");
// 2.connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
// 3.String sql
String sql = "insert into student values(null,?,?,?)";
// 4.statement
PreparedStatement pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setString(1, name);
pst.setTimestamp(2, ts);
pst.setFloat(3, score);
// 5.SQL执行
int result = pst.executeUpdate();
// 6.close
pst.close();
con.close();
System.out.println(result > 0 ? "增加记录成功！" : "没有增加成功！");
}
// 2.删除记录
private static void deleteStudent(int id) throws ClassNotFoundException, SQLException {
// 1 driver
Class.forName("com.mysql.jdbc.Driver");
// 2.connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
// 3.String sql
String sql = "delete from student where id=?";
// 4.statement
PreparedStatement pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setInt(1, id);
// 5.SQL执行
int result = pst.executeUpdate();
// 6.close
pst.close();
con.close();
System.out.println(result > 0 ? "删除记录成功！" : "没有删除成功！");
}
// 3修改记录
private static void updateStudent(String name, int id) throws ClassNotFoundException, SQLException {
// 1 driver
Class.forName("com.mysql.jdbc.Driver");
// 2.connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
// 3.String sql
String sql = "update student set name = ? where id = ?";
// 4.statement
PreparedStatement pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setString(1, name);
pst.setInt(2, id);
// 5.SQL执行
int result = pst.executeUpdate();
// 6.close
pst.close();
con.close();
System.out.println(result > 0 ? "修改记录成功！" : "没有修改成功！");
}
// 4查找一条记录
private static void selectOne(int id) throws SQLException, ClassNotFoundException {
// 1 driver
Class.forName("com.mysql.jdbc.Driver");
// 2.connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
// 3.String sql
String sql = "select * from student where id = ?";
// 4.statement
PreparedStatement pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setInt(1, id);
// 5.SQL执行
ResultSet rs = pst.executeQuery();
while (rs.next()) {
int id1 = rs.getInt("id");
String name = rs.getString("name");
Date date = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id1 + " " + name + " " + date + " " + score);
}
// 6.close
rs.close();
pst.close();
con.close();
}
// 5查找所有记录
private static void selectAll() throws SQLException, ClassNotFoundException {
// 1 driver
Class.forName("com.mysql.jdbc.Driver");
// 2.connection
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
// 3.String sql
String sql = "select * from student";
// 4.statement
PreparedStatement pst = con.prepareStatement(sql);
// 5.SQL执行
ResultSet rs = pst.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
Date date = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id + " " + name + " " + date + " " + score);
}
// 6.close
rs.close();
pst.close();
con.close();
}
}
2、JDBC面向对象方式实现数据库CRUD操作
已知：建立表student，并向表里插入几条记录。
create table student(
id int PRIMARY KEY auto_increment,
name varchar(20) not null,
schoolin date not null,
score float not null);
insert into student values(null,’李丽’,’2015-09-01’,86);
insert into student values(null,’王五’,’2016-09-01’,99);
insert into student values(null,’张三’,’2014-09-01’,88);
基本要求：将表操作封装成类，将功能封装成类的方法。
要求：用JAVA程序实现如下功能：
1、向表中增加记录并显示所有记录（数据自己指定）；
2、从表中删除id=1的记录并显示所有记录；
3、修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；
4、查询表中id=3的记录并显示；
（1）StudentDao类
package sdut.cn.studentdao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class StudentDao {
// 定义3个属性
Connection con;
PreparedStatement pst;
ResultSet rs;
// 获得数据库的连接
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");// 加载数据库驱动
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
return con;
}
// 关闭对象
public void closeAll() {
try {
if (rs != null)
rs.close();
if(pst != null)
pst.close();
if(con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//1.增加记录
// String->date
public Timestamp Change(String time) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
date = sdf.parse(time);
} catch (ParseException e) {
e.printStackTrace();
}
// javax.sql.Date与java.util.Date的转换方式
Timestamp ts = new Timestamp(date.getTime());// 把日期转为为微秒数，然后丢到ts的构造函数中。
return ts;
}
public void addStudent(String name, Timestamp ts, float score) throws ClassNotFoundException, SQLException {
// 1 driver
// 2.connection
con = getConnection();
// 3.String sql
String sql = "insert into student values(null,?,?,?)";
// 4.statement
pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setString(1, name);
pst.setTimestamp(2, ts);
pst.setFloat(3, score);
// 5.SQL执行
int result = pst.executeUpdate();
// 6.close
closeAll();
System.out.println(result > 0 ? "增加记录成功！" : "没有增加成功！");
}
// 2.删除记录
public void deleteStudent(int id) throws ClassNotFoundException, SQLException {
// 1 driver
// 2.connection
con = getConnection();
// 3.String sql
String sql = "delete from student where id=?";
// 4.statement
pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setInt(1, id);
// 5.SQL执行
int result = pst.executeUpdate();
// 6.close
closeAll();
System.out.println(result > 0 ? "删除记录成功！" : "没有删除成功！");
}
// 3修改记录
public void updateStudent(String name, int id) throws ClassNotFoundException, SQLException {
// 1 driver
// 2.connection
con = getConnection();
// 3.String sql
String sql = "update student set name = ? where id = ?";
// 4.statement
pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setString(1, name);
pst.setInt(2, id);
// 5.SQL执行
int result = pst.executeUpdate();
// 6.close
closeAll();
System.out.println(result > 0 ? "修改记录成功！" : "没有修改成功！");
}
// 4查找一条记录
public void selectOne(int id) throws SQLException, ClassNotFoundException {
// 1 driver
// 2.connection
con = getConnection();
// 3.String sql
String sql = "select * from student where id = ?";
// 4.statement
pst = con.prepareStatement(sql);
// 4.5 SQL set
pst.setInt(1, id);
// 5.SQL执行
ResultSet rs = pst.executeQuery();
while (rs.next()) {
int id1 = rs.getInt("id");
String name = rs.getString("name");
Date date = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id1 + " " + name + " " + date + " " + score);
}
// 6.close
closeAll();
}
// 5查找所有记录
public void selectAll() throws SQLException, ClassNotFoundException {
// 1 driver
// 2.connection
con = getConnection();
// 3.String sql
String sql = "select * from student";
// 4.statement
pst = con.prepareStatement(sql);
// 5.SQL执行
ResultSet rs = pst.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
Date date = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id + " " + name + " " + date + " " + score);
}
// 6.close
closeAll();
}
}
（2）StudentDaoTest类
package sdut.cn.studenttest;
import java.sql.SQLException;
import sdut.cn.studentdao.StudentDao;
public class StudentDaoTest {
public static void main(String[] args) {
StudentDao sDao = new StudentDao();
try {
sDao.addStudent("李丽", sDao.Change("2015-09-01"), 86);
sDao.addStudent("王五", sDao.Change("2016-09-01"), 99);
sDao.addStudent("张三", sDao.Change("2014-09-01"), 88);
sDao.addStudent("郭老师", sDao.Change("2020-09-01"), 62);
sDao.selectAll();
sDao.deleteStudent(1);
sDao.selectAll();
sDao.updateStudent("山东理工", 3);
sDao.selectAll();
sDao.selectOne(3);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3、简易版银行管理管理
已知：现有账户表account，创建的SQL语句如下。
create table account(
id int PRIMARY KEY auto_increment,
name varchar(20) not null,
money double not null);
基本要求：
（1）利用面向对象方式实现功能；
（2）利用三层架构实现；
（3）应用事务处理。
功能要求：
（1）开户——增加记录
增加3个账户，姓名分别为：张三、李四、王五，账户初始金额：0元；
开户成功，显示所有账户信息。
（2）销户——删除记录
对姓名为"王五"的账户给予销户。成功操作后，显示所有账户信息。
（3）存钱——修改记录
张三、李四账户分别存入2000元。成功操作后，显示所有账户信息。
（4）取钱——修改记录
张三账户取出1000元，显示张三账户信息。
（5）转账——修改记录
李四给张三转账500元，显示张三和李四账户信息。
（6）查询所有账户信息.
//框架:
//1用户交互层Main
package cn.sdut.amain;
import java.util.Scanner;
import cn.sdut.biz.BankBiz;
public class Main {
public static void main(String[] args) {
Scanner reader = new Scanner(System.in);
BankBiz biz = new BankBiz();
// 1 开户——增加3个账户，姓名分别为：张三、李四、王五，账户初始金额：0元，显示所有账户信息
System.out.println("*************");
int n = reader.nextInt();
reader.nextLine();
for (int i = 0; i < n; i++) {
String name = reader.next();
double money = reader.nextDouble();
if (biz.openAccount(name, money))
System.out.println(name + "开户成功！欢迎使用本银行");
else
System.out.println(name + "开户失败！请联系工作人员");
}
biz.selectAccount();
//2销户——删除记录对姓名为"王五"的账户给予销户。成功操作后，显示所有账户信息。
System.out.println("*************");
if (biz.delAccount("王五"))
System.out.println("王五销户成功！感谢您之前对本银行的支持");
else
System.out.println("王五销户失败！请联系工作人员");
biz.selectAccount();
// 3存钱——修改记录张三、李四账户分别存入2000元。成功操作后，显示所有账户信息。
System.out.println("*************");
if (biz.addUpdateAccount("张三", 2000))
System.out.println("恭喜您存钱成功");
else
System.out.println("存钱失败");
if (biz.addUpdateAccount("李四", 2000))
System.out.println("恭喜您存钱成功！");
else
System.out.println("存钱失败！请联系工作人员");
biz.selectAccount();
// 4取钱——修改记录张三账户取出1000元，显示张三账户信息。
System.out.println("*************");
if (biz.delUpdateAccount("张三", 1000))
System.out.println("张三取钱成功！");
else
System.out.println( "张三取钱失败！请联系工作人员");
biz.selectOneAccount("张三");
// 5. 转账——修改记录李四给张三转账500元，显示张三和李四账户信息。
System.out.println("*************");
if (biz.tranAccount("李四", "张三", 500))
System.out.println("转账成功！");
else
System.out.println("转账失败！请联系工作人员");
biz.selectOneAccount("张三");
biz.selectOneAccount("李四");
// 6查询所有账户信息。
System.out.println("*************");
biz.selectAccount();
reader.close();
}
}
//2.BIZ业务逻辑层
package cn.sdut.biz;
import java.sql.SQLException;
import cn.sdut.dao.AccountDao;
import cn.sdut.entity.Account;
public class BankBiz {
AccountDao dao = new AccountDao();
// 开户
public boolean openAccount(String name, double money) {
int result = 0;
try {
Account account = new Account();
account.setName(name);
account.setMoney(money);
result = dao.addAccount(account);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result > 0 ? true : false;
}
// 销户
public boolean delAccount(String name) {
int result = 0;
try {
result = dao.delAccount(name);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result > 0 ? true : false;
}
// 存钱
public boolean addUpdateAccount(String name, double money) {
int result = 0;
try {
result = dao.addUpdateAccount(name, money);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result > 0 ? true : false;
}
// 取钱
public boolean delUpdateAccount(String name, double money) {
int result = 0;
try {
result = dao.delUpdateAccount(name, money);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result > 0 ? true : false;
}
// 转账
public boolean tranAccount(String fromname, String toname, double money) {
boolean result = false;
try {
result = dao.tranAccount(fromname, toname, money);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
// 查询所有账户
public void selectAccount() {
try {
dao.selectAccount();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 查询一个账户
public void selectOneAccount(String name) {
try {
dao.selectOneAccount(name);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//3.数据访问层DAO层
//BaseDao
package cn.sdut.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
Connection con;
PreparedStatement pst;
ResultSet rs;
// 获得数据库连接
public Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");// 加载数据库驱动
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3307/bank?useUnicode=true&characterEncoding=utf-8", "root", "usbw");
return con;
}
// 关闭对象
public void closeAll() throws SQLException {
if (rs != null)
rs.close();
if (pst != null)
pst.close();
if (con != null)
con.close();
}
}
//AccountDaoInterface
package cn.sdut.dao;
import java.sql.SQLException;
import cn.sdut.entity.Account;
public interface AccountDaoInterface {
public int addAccount(Account account) throws ClassNotFoundException, SQLException;
public int delAccount(String name) throws ClassNotFoundException, SQLException;
public int addUpdateAccount(String name, double money) throws ClassNotFoundException, SQLException;
public int delUpdateAccount(String name, double money) throws ClassNotFoundException, SQLException;
public boolean tranAccount(String fromname, String toname, double money) throws ClassNotFoundException, SQLException ;
public void selectAccount() throws ClassNotFoundException, SQLException;
public void selectOneAccount(String name) throws ClassNotFoundException, SQLException;
}
//AccountDao
package cn.sdut.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.sdut.entity.Account;
public class AccountDao extends BaseDao implements AccountDaoInterface {
// 1.开户——增加记录
public int addAccount(Account account) throws ClassNotFoundException, SQLException {
con = getConnection();
String sql = "insert into account values(null,?,?)";
pst = con.prepareStatement(sql);
pst.setString(1, account.getName());
pst.setDouble(2, account.getMoney());
int result = pst.executeUpdate();
closeAll();
return result;
}
// 2.销户——删除记录
public int delAccount(String name) throws ClassNotFoundException, SQLException {
con = getConnection();
String sql = "delete from account where name=?";
pst = con.prepareStatement(sql);
pst.setString(1, name);
int result = pst.executeUpdate();
closeAll();
return result;
}
// 3.存钱——修改记录
public int addUpdateAccount(String name, double money) throws ClassNotFoundException, SQLException {
// 张三、李四账户分别存入2000元。成功操作后，显示所有账户信息。
con = getConnection();
String sql = "update account set money=money+? where name=?";
pst = con.prepareStatement(sql);
pst.setString(2, name);
pst.setDouble(1, money);
int result = pst.executeUpdate();
closeAll();
return result;
}
// 4.取钱——修改记录
public int delUpdateAccount(String name, double money) throws ClassNotFoundException, SQLException {
con = getConnection();
String sql = "update account set money = money - ? where name = ?";
pst = con.prepareStatement(sql);
pst.setDouble(1, money);
pst.setString(2, name);
int result = pst.executeUpdate();
closeAll();
return result;
}
// 5.转账——修改记录
public boolean tranAccount(String fromname, String toname, double money)
throws ClassNotFoundException, SQLException {
// 李四给张三转账500元，显示张三和李四账户信息。
con = getConnection();
con.setAutoCommit(false);// 关闭数据库的自动提交功能,设置成SQL语句的非自动提交,并开启事物
String sql1 = "update account set money = money - ? where name = ?";
pst = con.prepareStatement(sql1);
pst.setString(2, fromname);
pst.setDouble(1, money);
int result1 = pst.executeUpdate();
String sql2 = "update account set money=money+? where name=?";
pst = con.prepareStatement(sql2);
pst.setString(2, toname);
pst.setDouble(1, money);
int result2 = pst.executeUpdate();
int result = result1 * result2;
if (result == 1) {
con.commit();// 提交事务
} else {
con.rollback();// 回滚事物
}
closeAll();
return result > 0 ? true : false;
}
// 6.查询所有账户信息。
public void selectAccount() throws ClassNotFoundException, SQLException {
con = getConnection();
String sql = "select * from account";
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
List<Account> accountList = new ArrayList<Account>();
while (rs.next()) {
Account account = new Account();
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));
accountList.add(account);
}
closeAll();
for (Account ac : accountList) {
System.out.println(ac);
}
}
// 7.查询一个账户信息。
public void selectOneAccount(String name) throws ClassNotFoundException, SQLException {
con = getConnection();
String sql = "select * from account where name=?";
pst = con.prepareStatement(sql);
pst.setString(1, name);
rs = pst.executeQuery();
Account account = null;
if (rs.next()) {
account = new Account();
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));
}
closeAll();
System.out.println(account);
}
}
//Account实体类
package cn.sdut.entity;
public class Account {//实体类，与account表相对应
private int id;
private String name;
private double money;
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 double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
}
}
3393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
