实验目的:
1、熟悉数据库基本操作
2、掌握利用JDBC进行数据库的连接
3、利用语句对象Statement和PreparedStatement对表、记录、列进行增、删、改、查等操作
4、将数据库操作封装成类
5、了解三层架构编程思想
实验内容:
1、JDBC单表记录的增删改查(20分)
已知:建立表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的记录并显示;
提交说明:粘贴JAVA程序代码;
package cn.edu.sdut.acm;
import java.sql.*;
import java.util.*;
import java.util.Date;
/*
要求:用JAVA程序实现如下功能:
1、向表中增加记录并显示所有记录(数据自己指定);
2、从表中删除id=1的记录并显示所有记录;
3、修改表中记录:查询条件id=2,将name修改为:山东理工,修改完毕显示所有记录;
4、查询表中id=3的记录并显示;
**/
public class Main{
public static void main(String[] args){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1. 获取驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接对象
conn = DriverManager.getConnection("jdbc:mysql:///db3?useSSL=false", "root", "password");
// 3. 定义sql语句
String sql1 = "create table student(\n" +
"id int PRIMARY KEY auto_increment,\n" +
"name varchar(20) not null,\n" +
"schoolin date not null,\n" +
"score float not null)";
String sql2 = "insert into student values(null,'李丽', '2015-09-01', 86)";
String sql3 = "insert into student values(null,'王五', '2016-09-01', 99)";
String sql4 = "insert into student values(null,'张三', '2014-09-01', 88)";
String sql5 = "select * from student";
String sql6 = "delete from student where id = 1";
String sql7 = "update student set name = '山东理工' where id = 2";
String sql8 = "select * from student where id = 3";
// 4. 创建执行sql的对象
stmt = conn.createStatement();
// 创建表
int count1 = stmt.executeUpdate(sql1);
// 向表中增加记录
int count2 = stmt.executeUpdate(sql2);
int count3 = stmt.executeUpdate(sql3);
int count4 = stmt.executeUpdate(sql4);
// 显示所有记录
rs = stmt.executeQuery(sql5);
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
Date schoolin = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id+" ====== "+name+" ====== "+schoolin+" ====== "+score);
}
System.out.println("=================================================");
// 从表中删除id=1的记录
int count5 = stmt.executeUpdate(sql6);
// 显示所有记录
rs = stmt.executeQuery(sql5);
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
Date schoolin = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id+" ====== "+name+" ====== "+schoolin+" ====== "+score);
}
System.out.println("=================================================");
// 查询条件id=2,将name修改为:山东理工
int count6 = stmt.executeUpdate(sql7);
// 显示所有记录
rs = stmt.executeQuery(sql5);
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
Date schoolin = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id+" ====== "+name+" ====== "+schoolin+" ====== "+score);
}
System.out.println("=================================================");
// 查询表中id=3的记录并显示
rs = stmt.executeQuery(sql8);
rs.next();
int id = rs.getInt("id");
String name = rs.getString("name");
Date schoolin = rs.getDate("schoolin");
float score = rs.getFloat("score");
System.out.println(id+" ====== "+name+" ====== "+schoolin+" ====== "+score);
System.out.println("=================================================");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
2、简易版银行管理管理
已知:现有账户表account,创建的SQL语句如下。
create table account(
id int PRIMARY KEY auto_increment,
name varchar(20) not null,
money double not null);
基本要求:
鼓励利用面向对象方式实现功能,利用三层架构实现。
功能要求:
(1)开户——增加记录
增加3个账户,姓名分别为:张三、李四、王五,账户初始金额:0元;
开户成功,显示所有账户信息。
(2)销户——删除记录
对姓名为“王五”的账户给予销户。成功操作后,显示所有账户信息。
(3)存钱——修改记录
张三、李四账户分别存入2000元。成功操作后,显示所有账户信息。
(4)取钱——修改记录
张三账户取出1000元,显示张三账户信息。
(5)转账——修改记录
李四给张三转账500元,显示张三和李四账户信息。
(6)查询所有账户信息。
提交说明:粘贴JAVA程序代码。
package cn.edu.sdut.acm;
import java.sql.*;
import java.util.*;
import java.util.Date;
/*
基本要求:
鼓励利用面向对象方式实现功能,利用三层架构实现。
功能要求:
(1)开户——增加记录
增加3个账户,姓名分别为:张三、李四、王五,账户初始金额:0元;
开户成功,显示所有账户信息。
(2)销户——删除记录
对姓名为“王五”的账户给予销户。成功操作后,显示所有账户信息。
(3)存钱——修改记录
张三、李四账户分别存入2000元。成功操作后,显示所有账户信息。
(4)取钱——修改记录
张三账户取出1000元,显示张三账户信息。
(5)转账——修改记录
李四给张三转账500元,显示张三和李四账户信息。
(6)查询所有账户信息。
**/
public class Main{
public static void main(String[] args){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1. 获取驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接对象
conn = DriverManager.getConnection("jdbc:mysql:///db3?useSSL=false", "root", "password");
// 3. 定义sql语句
String sql1 = "create table account(\n" +
"id int PRIMARY KEY auto_increment,\n" +
"name varchar(20) not null,\n" +
"money double not null);";
String sql2 = "insert into account values(null,'张三', 0)";
String sql3 = "insert into account values(null,'李四', 0)";
String sql4 = "insert into account values(null,'王五', 0)";
String sql5 = "select * from account";
String sql6 = "delete from account where name = '王五'";
String sql7 = "update account set money = 2000 where name = '张三'";
String sql8 = "update account set money = 2000 where name = '李四'";
String sql9 = "update account set money = 1000 where name = '张三'";
String sql10 = "select * from account where name = '张三'";
String sql11 = "update account set money = 1500 where name = '张三'";
String sql12 = "update account set money = 1500 where name = '李四'";
// 4. 创建执行sql的对象
stmt = conn.createStatement();
// 创建表
int count1 = stmt.executeUpdate(sql1);
// 增加3个账户,姓名分别为:张三、李四、王五,账户初始金额:0元;
int count2 = stmt.executeUpdate(sql2);
int count3 = stmt.executeUpdate(sql3);
int count4 = stmt.executeUpdate(sql4);
// 开户成功,显示所有账户信息
rs = stmt.executeQuery(sql5);
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id+" ====== "+name+" ====== "+money);
}
System.out.println("=================================================");
// 对姓名为“王五”的账户给予销户。
int count5 = stmt.executeUpdate(sql6);
// 显示所有记录
rs = stmt.executeQuery(sql5);
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id+" ====== "+name+" ====== "+money);
}
System.out.println("=================================================");
// 张三、李四账户分别存入2000元。
int count6 = stmt.executeUpdate(sql7);
int count7 = stmt.executeUpdate(sql8);
// 显示所有记录
rs = stmt.executeQuery(sql5);
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id+" ====== "+name+" ====== "+money);
}
System.out.println("=================================================");
// 张三账户取出1000元,显示张三账户信息。
int count8 = stmt.executeUpdate(sql9);
rs = stmt.executeQuery(sql10);
rs.next();
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
System.out.println(id+" ====== "+name+" ====== "+money);
System.out.println("=================================================");
// 李四给张三转账500元,显示张三和李四账户信息。
int count9 = stmt.executeUpdate(sql11);
int count10 = stmt.executeUpdate(sql12);
rs = stmt.executeQuery(sql5);
while (rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
money = rs.getDouble("money");
System.out.println(id+" ====== "+name+" ====== "+money);
}
System.out.println("=================================================");
rs = stmt.executeQuery(sql5);
while (rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
money = rs.getDouble("money");
System.out.println(id+" ====== "+name+" ====== "+money);
}
System.out.println("=================================================");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}