实验目的:
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;
public class Main {
public static void main ( String[ ] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
conn = DriverManager. getConnection ( "jdbc:mysql:///db3?useSSL=false" , "root" , "password" ) ;
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" ;
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 ( "=================================================" ) ;
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 ( "=================================================" ) ;
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 ( "=================================================" ) ;
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 ( ) ;
}
}
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
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;
public class Main {
public static void main ( String[ ] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
conn = DriverManager. getConnection ( "jdbc:mysql:///db3?useSSL=false" , "root" , "password" ) ;
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 = '李四'" ;
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" ) ;
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 ( "=================================================" ) ;
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 ( "=================================================" ) ;
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 ( "=================================================" ) ;
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 ( ) ;
}
}
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161