Java（含拓展知識和自己的做題圖片記錄）：jdbc學(xué)生表,打印輸出,增刪查改，用集合輸出
--1./*常熬夜到12點或2點寫代碼，一寫就好幾個小時，有時手都酸痛死了，求三連，關(guān)注！*/
--在數(shù)據(jù)庫中新建學(xué)生表，添加列學(xué)生ID(主鍵，自增)、學(xué)生姓名、學(xué)生年齡(大于5歲，小于60歲)、學(xué)生性別(男和女),添加5條測試數(shù)據(jù)，從數(shù)據(jù)庫中查詢出相應(yīng)的數(shù)據(jù)打印輸出
create table student(
Id int primary key identity(1, 1),
Name nvarchar (20) ,
Age int check (Age>5 and Age<60),
sex nvarchar (10) check(sex='男'or sex='女')
)
insert into student values('張三',19,'男')
insert into student values('王麗麗',20,'女')
insert into student values(' 李菲菲',21,'女')?
insert into student values(' 陳德勇',22,'男')?
insert into student values(' 詩書畫唱',20,'男')?
select * from student
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=Student庫", "sa", "1234abcd");
String sql = "select * from Student";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.print(res.getObject("Id") + "? ?");
System.out.print(res.getObject("Name") + "? ?");
System.out.print(res.getObject("Age") + "? ?");
System.out.print(res.getObject("sex") + "? ?");
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
第一題中的擴(kuò)展知識：
--2
--在數(shù)據(jù)庫中新建商品表,商品類型表，商品表包含商品ID（主鍵，自增）商品名稱（非空驗??
?--證），
--商品價格（大于0元），商品類型ID(外鍵)，商品保質(zhì)期(Date類型),商品類型表包含商品類型
--ID(主鍵、自增)，商品類型名稱(非空驗證),各添加5條數(shù)據(jù)進(jìn)行測試，使用JDBC在控制臺打
--印其兩表查詢結(jié)果（含多表查詢語法的個人理解記憶的方法）
/*個人理解:商品類型表就如父類，商品表就如子類，所以要先建商品類型表，有父親才有兒子，再建商品表，要同過值相同的商品類型ID來連接，F(xiàn)oreign key(sp_TypeID) references shangpinleixing(sp_TypeID)在商品表中，
?references 雖然是引證的意思，但為了方便理解和應(yīng)用，可以理解為（但不等同，只是類比，比喻）Java中的extends延續(xù)關(guān)鍵字?；蛏唐奉愋捅頌槌橄蟮念?，商品表為具體的對象，先有類，才有對象，所以先寫商品類型表，再寫商品表。有時出錯可以換一下表的建造順序*/
--create database shop
--use shop
create table shangpinleixing(
sp_TypeID int primary key identity(1, 1),
sp_TypeName varchar(50) not null,
)
create table shangpin(
sp_ID int primary key identity(1, 1),
sp_Name varchar(50) not null,
sp_Price int check(sp_Price>0),
sp_TypeID int,
sp_Baozhiqi Date default (getdate())
Foreign key(sp_TypeID) references shangpinleixing(sp_TypeID)
)
--drop table shangpin
--drop table shangpinleixing
insert into shangpinleixing(sp_TypeName) values('蘋果')
insert into shangpinleixing(sp_TypeName) values('香蕉')
insert into shangpinleixing(sp_TypeName) values('面條')?
insert into shangpinleixing(sp_TypeName) values('CD')?
insert into shangpinleixing(sp_TypeName) values('海報')?
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('詩書蘋果',19,1)
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('畫唱香蕉',20,2)
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('好吃面條',21,3)?
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('帥哥CD',22,4)?
insert into shangpin(sp_Name,sp_Price,sp_TypeID) values('詩書畫唱海報',20,5)?
--select * from shangpin
--select * from shangpinleixing
--出錯時可刪庫，再重鍵庫
--drop database shop
--create database shop
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shop", "sa", "1234abcd");
String sql = "select * from shangpin as sp? join? "
+ "shangpinleixing as splx? on sp.sp_TypeID=splx.sp_TypeID";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("商品編號：" + res.getInt("sp_ID") + "\t"
+ ",商品名稱："
+ res.getString("sp_Name") + "\t"
+ ",商品價格：" + res.getInt("sp_Price") + "\t"
+ ",商品類型ID：" + res.getInt("sp_TypeID") + "\t" +
",商品保質(zhì)期：" + res.getString("sp_Baozhiqi") + "\t"
+ ",商品編號：" + res.getObject(6) + "\t" + ",商品類型名稱："
+ res.getObject(7));
/*
* res.getObject(7)中的7代表第七列的所有內(nèi)容,
* res.getInt("sp_ID")相當(dāng)于res.getObject(1)
*/
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
第二題的擴(kuò)展知識：
--3.
在數(shù)據(jù)庫中新建汽車表，汽車類型表，汽車表包含汽車ID，汽車名稱(非空驗證)，
--汽車價格，汽車類型ID（外鍵），汽車類型表包含汽車類型ID，汽車類型名稱兩列，
--使用JDBC添加5條數(shù)據(jù)進(jìn)行測試，將其結(jié)果放入到集合中，遍歷集合
--create database qiche
create table qicheleixing(
qiche_TypeID int primary key identity(1, 1),
qiche_TypeName varchar(50) not null,
)
create table qiche(
qiche_ID int? primary key identity(1, 1),
qiche_Name varchar(50) not null,
qiche_Price int check(qiche_Price>0),
qiche_TypeID int,
Foreign key(qiche_TypeID) references qicheleixing(qiche_TypeID)
)
--drop table qiche
--drop table qicheleixing
insert into qicheleixing(qiche_TypeName) values('寶馬')
insert into qicheleixing(qiche_TypeName) values('奔馳')
insert into qicheleixing(qiche_TypeName) values('大奔馳')?
insert into qicheleixing(qiche_TypeName) values('大汽車')?
insert into qicheleixing(qiche_TypeName) values('高科技汽車')?
insert into qiche values('詩書寶馬',19000,1)
,('畫唱奔馳',20000,2)
,('好吃大奔馳',21000,3)?
,('帥哥大汽車',22000,4)?
,('詩書畫唱高科技汽車',200000,5)
--select * from qiche
--select * from qicheleixing
--select * from qicheleixing as qclx join qiche as qc on qclx.qiche_TypeID=qc.qiche_TypeID
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=qiche", "sa", "1234abcd");
String sql = "select * from qiche";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("汽車ID:" + res.getInt("qiche_ID") + "\t"
+ "汽車名稱:" + res.getString("qiche_Name") + "\t"
+ "汽車價格:" + res.getInt("qiche_Price") + "\t"
+ "汽車類型ID:" + res.getInt("qiche_TypeID"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
第三題的擴(kuò)展知識：
-
DESKTOP-49FTFSP
成功連接后：
--4.
新建數(shù)據(jù)庫shopping，新建數(shù)據(jù)表（qiche）包含商品ID，商品名稱，商品價格，
--商品保質(zhì)期，--作業(yè)（1）：添加5條數(shù)據(jù)進(jìn)行測試，在控制臺將數(shù)據(jù)打印
--作業(yè)（2）：將商品ID為3的商品名稱修改為“西瓜”，價格改為2.5
--作業(yè)（3）：刪除ID為2的商品
--作業(yè)（4）：添加一條商品名稱為“橘子”，價格為4元，保質(zhì)期為當(dāng)前時間+3天
--作業(yè)（5）：將增加后的商品進(jìn)行重新遍歷出來
--作業(yè)（1）答案：
create database shopping
use shopping
--drop table qiche
--drop table qicheleixing
create table qicheleixing(
sp_TypeID int primary key identity(1,1),
sp_TypeName varchar(50) not null,
)
create table qiche(
sp_ID int primary key identity(1,1),
sp_Name varchar(50) not null,
sp_Price int check(sp_Price>0),
sp_TypeID int ,
sp_Baozhiqi varchar(50)
Foreign key(sp_TypeID) references qicheleixing(sp_TypeID)
--外鍵會影響刪除
)
--select * from qiche
--drop table qicheleixing
insert into qicheleixing(sp_TypeName) values('蘋果')
insert into qicheleixing(sp_TypeName) values('香蕉')
insert into qicheleixing(sp_TypeName) values('面條')?
insert into qicheleixing(sp_TypeName) values('CD')?
insert into qicheleixing(sp_TypeName) values('海報')?
insert into qiche values('詩書蘋果',19,1,'1年'),('畫唱香蕉',20,2,'2年')
,('好吃面條',21,3,'3年') ,('帥哥CD',22,4,'4年') ,('詩書畫唱海報',20,5,'5年')?
--select * from qiche
--select * from qicheleixing
--select * from qicheleixing as qclx join qiche as qc on qclx.sp_TypeID=qc.sp_TypeID
--出錯時可刪庫，再重鍵庫
--drop database? shopping
--create database? shopping
//第4題的第（1）題的方法一(普通遍歷打印):
打印一張表：
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
String sql = "select * from qiche";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("商品編號" + res.getInt("sp_ID") + "\t" + "商品名稱"
+ res.getString("sp_Name") + "\t"
+ res.getInt("sp_Price") + "\t"
+ res.getInt("sp_TypeID") + "\t"
+ res.getString("sp_Baozhiqi"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
打印兩張表：
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
String sql = "select * from "
+ "qicheleixing as qclx join qiche as qc "
+ "on qclx.sp_TypeID=qc.sp_TypeID";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("商品編號：" + res.getInt("sp_ID") + "\t"
+ "，商品名稱："
+ res.getString("sp_Name") + "\t"
+ "，商品價格："
+ res.getInt("sp_Price") + "\t"
+ "，商品類型ID："
+ res.getInt("sp_TypeID") + "\t"
+ "，商品保質(zhì)期："
+ res.getString("sp_Baozhiqi"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
//第4題的第（1）題的方法二(用集合遍歷打印一張表):
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
String sql = "select * from qiche";
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery(sql);
ArrayList<qiche> arr_list = new ArrayList<qiche>();
while (res.next()) {
// 用while(.next())給集合賦值:
qiche sp = new qiche();
sp.setSp_ID(res.getInt("sp_ID"));
sp.setSp_Name(res.getString("sp_Name"));
sp.setSp_Price(res.getInt("sp_Price"));
sp.setSp_TypeID(res.getInt("sp_TypeID"));
sp.setSp_Baozhiqi(res.getString("sp_baozhiqi"));
// 用.add()將模型類添加到集合中:
arr_list.add(sp);
}
for (qiche i : arr_list) {
System.out.println(i);
}
}
}
class qiche {
private String sp_Baozhiqi;
private int sp_ID;
private String sp_Name;
private int sp_Price;
private int sp_TypeID;
public int getSp_ID() {
return sp_ID;
}
public String getSp_Name() {
return sp_Name;
}
public int getSp_Price() {
return sp_Price;
}
public int getSp_TypeID() {
return sp_TypeID;
}
public void setSp_Baozhiqi(String sp_Baozhiqi) {
this.sp_Baozhiqi = sp_Baozhiqi;
}
public void setSp_ID(int sp_ID) {
this.sp_ID = sp_ID;
}
public void setSp_Name(String sp_Name) {
this.sp_Name = sp_Name;
}
public void setSp_Price(int sp_Price) {
this.sp_Price = sp_Price;
}
public void setSp_TypeID(int sp_TypeID) {
this.sp_TypeID = sp_TypeID;
}
@Override
public String toString() {
return "qiche [sp_Baozhiqi=" + sp_Baozhiqi + ", sp_ID=" + sp_ID
+ ", sp_Name=" + sp_Name + ", sp_Price=" + sp_Price
+ ", sp_TypeID=" + sp_TypeID + "]";
}
}
//--第4題的作業(yè)（2）：
將商品ID為3的商品名稱修改為“西瓜”，價格改為4/*個人理解：因為sql中聲明價格為int，所以改不了小數(shù)，除非sql中聲明為小數(shù)*/
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql = "update qiche set sp_Name='西瓜' ,sp_Price=4 where sp_ID=3";
// 這個方法返回的數(shù)據(jù)是一個int類型的數(shù)據(jù)，如果是0,說明修改失敗。
int num = sta.executeUpdate(sql);
if (num > 0) {
// 修改成功
System.out.println("修改成功");
} else {
System.out.println("修改失敗");
}
}
}
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql="delete qiche where sp_ID=3";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("刪除成功");
} else {
System.out.println("刪除失敗");
}
}
}
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql="insert into qiche values('橘子',2,1,getdate()+3)";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失敗");
}
}
}
//作業(yè)（5）
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class jdbc {
public static void main(String[] args) throws Exception {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=shopping", "sa", "1234abcd");
Statement sta = con.createStatement();
String sql = "insert into qiche values('橘子',2,1,getdate()+3)";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失敗");
}
System.out.println("用while (res.next())遍歷打印出來，用sql2語句兩表查詢");
String sql2 = "select * from "
+ "qicheleixing as qclx join qiche as qc "
+ "on qclx.sp_TypeID=qc.sp_TypeID";
ResultSet res = sta.executeQuery(sql2);
while (res.next()) {
System.out.println("商品編號：" + res.getInt("sp_ID") + "\t"
+ "，商品名稱："
+ res.getString("sp_Name") + "\t"
+ "，商品價格："
+ res.getInt("sp_Price") + "\t"
+ "，商品類型ID："
+ res.getInt("sp_TypeID") + "\t"
+ "，商品保質(zhì)期："
+ res.getString("sp_Baozhiqi"));
}
}
}
第四題的擴(kuò)展知識和我的做題歷程，解決報錯等的圖片記錄：
