Java JDBC改進(jìn)：shopping，商品，增刪改查，DBUtils，人員信息表，導(dǎo)包【詩書畫唱】
添加5條數(shù)據(jù)進(jìn)行測試在控制臺將數(shù)據(jù)打印，兩表查詢。
SQL部分：
--create database shopping
--use shopping
--select * from shangPin
--drop table shangPinLeiXing
--select * from shangPin
--select * from shangPinLeiXing
--select * from shangPinLeiXing as qclx join shangPin as qc on qclx.sp_TypeID=qc.sp_TypeID
--drop database? shopping
--create database? shopping
--drop table shangPin
--drop table shangPinLeiXing
create table shangPinLeiXing(
sp_TypeID int primary key identity(1,1),
sp_TypeName varchar(50) not null,
)
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('海報(bào)')
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 varchar(50)
Foreign key(sp_TypeID) references shangPinLeiXing(sp_TypeID)
--外鍵會(huì)影響刪除
)
insert into shangPin values('詩書蘋果',19,1,'1年'),('畫唱香蕉',20,2,'2年')
,('好吃面條',21,3,'3年') ,('帥哥CD',22,4,'4年') ,('詩書畫唱海報(bào)',20,5,'5年')
Java部分：
package fuXi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class shangPin {
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 "
+ "shangPinLeiXing as splx join shangPin as sp "
+ "on splx.sp_TypeID=sp.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"
+ "，商品價(jià)格："
+ res.getInt("sp_Price") + "\t"
+ "，商品類型ID："
+ res.getInt("sp_TypeID") + "\t"
+ "，商品保質(zhì)期："
+ res.getString("sp_Baozhiqi"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
將商品ID為3的商品名稱修改為“西瓜”，價(jià)格改為3
package fuXi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class shangPin {
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 shangPin set sp_Name='西瓜' ,sp_Price=4 where sp_ID=3";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失敗");
}
}
}
刪除ID為2的商品
SQL部分：（同上）
Java部分：
package fuXi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class shangPin {
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 shangPin where sp_ID=2";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("刪除成功");
} else {
System.out.println("刪除失敗");
}
}
}
添加一條商品名稱為“橘子”，價(jià)格為4元，保質(zhì)期為當(dāng)前時(shí)間+3天
package fuXi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class shangPin {
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 shangPin values('橘子',2,1,getdate()+3)";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失敗");
}
}
}
將增加后的商品進(jìn)行重新遍歷出來
package fuXi;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class shangPin {
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 shangPin "
+ "values('橘子',2,1,getdate()+3)";
int num = sta.executeUpdate(sql);
if (num > 0) {
System.out.println("增加成功");
} else {
System.out.println("增加失敗");
}
System.out.println(" ");
String sql2 = "select * from "
+ "shangPinLeiXing as splx join shangPin as sp "
+ "on splx.sp_TypeID=sp.sp_TypeID";
ResultSet res = sta.executeQuery(sql2);
while (res.next()) {
System.out.println("商品編號："?
+ res.getInt("sp_ID") + "\t"
+ "，商品名稱："
+ res.getString("sp_Name") + "\t"
+ "，商品價(jià)格："
+ res.getInt("sp_Price") + "\t"
+ "，商品類型ID："
+ res.getInt("sp_TypeID") + "\t"
+ "，商品保質(zhì)期："
+ res.getString("sp_Baozhiqi"));
}
}
}
制作一個(gè)人員信息表，包含人員編號，姓名，類型編號(外鍵)，地址
人員類型表
包含類型編號（對應(yīng)著人員表的類型），類型名稱。
添加5條信息
1.請選擇功能：1.打印所有的人員信息?????2.輸入編號修改指定人員信息
3.輸入編號查詢單個(gè)人員信息???4.刪除指定人員信息????5.增加人員信息
可以使用DBUtils。
SQL部分：
--delete renYuanXinXi where renYuanLeiXingID=1
--select * from renYuanLeiXing
--select*from renYuanXinXi
--drop table renYuanLeiXing
--drop table renYuanXinXi?
--select* from renYuanXinXi?
--select * from renYuanLeiXing
--select *from renYuanXinXi a inner join renYuanLeiXing b on a.renYuanLeiXingID=b.renYuanLeiXingID where b.renYuanLeiXingid=1
create table renYuanLeiXing(
renYuanLeiXingID int primary key identity(1, 1),
renYuanLeiXingName nvarchar(100) not null)
insert into renYuanLeiXing values('經(jīng)理'),('Java構(gòu)造師')
,('python程序員'),('美工')
create table renYuanXinXi (
renYuanID int primary key identity(1, 1),
renYuanName nvarchar(100) not null,
renYuanLeiXingID int,
renYuanDiZhi nvarchar (500),
foreign key(renYuanLeiXingID) references renYuanLeiXing (renYuanLeiXingID))
insert into renYuanXinXi values('項(xiàng)點(diǎn)贊',1,'點(diǎn)贊村' )
,('項(xiàng)投幣',3, '點(diǎn)贊縣' )
,('項(xiàng)關(guān)注',1,'點(diǎn)贊市' )
,('項(xiàng)交友',4,'點(diǎn)贊省' )
,('項(xiàng)詩書畫唱',2,'點(diǎn)贊國' )
Java部分：
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Random;
import java.util.Scanner;
public class mains {
// SelectDanGeRenYuan搜索單個(gè)人員
public static ResultSet SelectDanGeRenYuan = null;
public static Scanner s = new Scanner(System.in);
public static void bianLiRenYuan
(ArrayList<renYuanXinXi> renYuanJiHe) {
for (renYuanXinXi i : renYuanJiHe) {
System.out.println(i);
}
}
private static void bianLiRenYuanLeiXing
(ArrayList<renYuanLeiXing> renYuanLeiXingJiHe) {
for (renYuanLeiXing i : renYuanLeiXingJiHe) {
System.out.println(i);
}
}
public static ArrayList<renYuanXinXi>?
getRengYuan(ResultSet res) {
ArrayList<renYuanXinXi> renYuanJiHe =
new ArrayList<renYuanXinXi>();
try {
while (res.next()) {
renYuanXinXi rY = new renYuanXinXi();
rY.setRenYuanID(res.getInt("renYuanID"));
rY.setRenYuanName(res.getString("renYuanName"));
rY.setRenYuanDiZhi(res.getString("renYuanDiZhi"));
renYuanLeiXing rYLX = new renYuanLeiXing();
rYLX.setRenYuanLeiXingID
(res.getInt("renYuanLeiXingID"));
rYLX.setRenYuanLeiXingName
(res.getString("renYuanLeiXingName"));
rY.setRenYuanLeiXingID(rYLX);
renYuanJiHe.add(rY);
}
} catch (SQLException e) {
e.printStackTrace();
}
return renYuanJiHe;
}
public static ArrayList<renYuanXinXi>
getRenYuan(String sql) {
ResultSet res = DBUtils.Select(sql);
ArrayList<renYuanXinXi> renYuanJiHe =?
new ArrayList<renYuanXinXi>();
try {
while (res.next()) {
renYuanXinXi rY = new renYuanXinXi();
rY.setRenYuanID(res.getInt("renYuanID"));
rY.setRenYuanName(res.getString("renYuanName"));
rY.setRenYuanDiZhi(res.getString("renYuanDiZhi"));
renYuanLeiXing rYLX = new renYuanLeiXing();
rYLX.setRenYuanLeiXingID
(res.getInt("renYuanLeiXingID"));
rYLX.setRenYuanLeiXingName
(res.getString("renYuanLeiXingName"));
rY.setRenYuanLeiXingID(rYLX);
renYuanJiHe.add(rY);
}
} catch (SQLException e) {
e.printStackTrace();
}
return renYuanJiHe;
}
public static ArrayList<renYuanLeiXing>?
getRenYuanBianLi(String sql) {
ArrayList<renYuanLeiXing> renYuanLeiXingJiHe =?
new ArrayList<renYuanLeiXing>();
ResultSet res = DBUtils.Select(sql);
try {
while (res.next()) {
renYuanLeiXing renYuanLeiXing =?
new renYuanLeiXing();
renYuanLeiXing.setRenYuanLeiXingID(res.
getInt("renYuanLeiXingID"));
renYuanLeiXing.setRenYuanLeiXingName
(res.getString("renYuanLeiXingName"));
renYuanLeiXingJiHe.add(renYuanLeiXing);
}
} catch (SQLException e) {
e.printStackTrace();
}
return renYuanLeiXingJiHe;
}
private static ArrayList<renYuanLeiXing>?
getRenYuanLeiXing() {
String sql = "select * from renYuanLeiXing";
ResultSet res = DBUtils.Select(sql);
ArrayList<renYuanLeiXing> renYuanLeiXingJiHe =?
new ArrayList<renYuanLeiXing>();
try {
while (res.next()) {
renYuanLeiXing renYuanLeiXing =?
new renYuanLeiXing();
renYuanLeiXing.setRenYuanLeiXingID
(res.getInt(1));
renYuanLeiXing.setRenYuanLeiXingName
(res.getString(2));
renYuanLeiXingJiHe.add(renYuanLeiXing);
}
} catch (SQLException e) {
e.printStackTrace();
}
return renYuanLeiXingJiHe;
}
public static void main(String[] args)?
throws Exception {
boolean b = true;
while (true) {
renYuanFengZhuangFangFa();
}?
}
private static void renYuanFengZhuangFangFa()?
throws ClassNotFoundException,
SQLException {
System.out.println("選項(xiàng):1.打印所有的人員信息? ? ?2.輸入編號修改指定人員信息"
+ "3.輸入編號查詢單個(gè)人員信息? ?4.刪除指定人員信息? ? 5.增加人員信息");
int xuanXiang = s.nextInt();
if (xuanXiang == 1) {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://localhost;databaseName=zhiwei", "sa",
"1234abcd");
Statement sta = con.createStatement();
String sql = "select * from renYuanXinXi a inner "
+ "join renYuanLeiXing b on "
+ "a.renYuanLeiXingID=b.renYuanLeiXingID";
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("人員編號:"+res.getInt("renYuanID") + ";\t"
+"人員名字:"+ res.getString("renYuanName") + ";\t"
+"人員類型名稱:"+ res.getString("renYuanLeiXingName") + ";\t"
+"人員地址:"+ res.getString("renYuanDiZhi")+ ";\t");
}
}?
else if (xuanXiang == 3) {
System.out.println("---執(zhí)行查詢單個(gè)人員信息的操作---");
System.out.println("請輸入你要查詢的人員ID");
int renYuanID = s.nextInt();
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://localhost;databaseName=zhiwei", "sa",
"1234abcd");
Statement sta = con.createStatement();
String sql = "select * from renYuanXinXi a inner "
+ "join renYuanLeiXing b "
+ "on a.renYuanLeiXingID=b.renYuanLeiXingID where "
+ "renYuanID="+renYuanID;
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("人員編號:"+res.getInt("renYuanID") + ";\t"
+"人員名字:"+ res.getString("renYuanName") + ";\t"
+"人員類型名稱:"+ res.getString("renYuanLeiXingName") + ";\t"
+"人員地址:"+ res.getString("renYuanDiZhi")+ ";\t");
}
}?
else if (xuanXiang == 2) {
System.out.println("---執(zhí)行修改操作---");
System.out.println("請輸入你要修改的人員編號");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://localhost;databaseName=zhiwei", "sa",
"1234abcd");
Statement sta = con
.createStatement();
String sql = "select * from renYuanXinXi a inner "
+ "join renYuanLeiXing b "
+ "on a.renYuanLeiXingID=b.renYuanLeiXingID";
ResultSet res = sta.executeQuery(sql);
while (res.next()) {
System.out.println("人員編號:"+res.getInt("renYuanID") + ";\t"
+"人員名字:"+ res.getString("renYuanName") + ";\t"
+"人員類型名稱:"+ res.getString("renYuanLeiXingName") + ";\t"
+"人員地址:"+ res.getString("renYuanDiZhi")+ ";\t");
}
int renYuanID = s.nextInt();
String sqlDanGe = "select * from renYuanXinXi a "
+ "inner join renYuanLeiXing b on a.renYuanLeiXingID="
+ "b.renYuanLeiXingID where renYuanID="
+ renYuanID + "";
SelectDanGeRenYuan = sta
.executeQuery(sqlDanGe);
ArrayList<renYuanXinXi> renYuanXinXiJiHe = getRengYuan
(SelectDanGeRenYuan);
if (renYuanXinXiJiHe.size() > 0) {
// danGeRenYuanXinXi單個(gè)人員信息
for (renYuanXinXi danGeRenYuanXinXi : renYuanXinXiJiHe)
{
System.out.println(danGeRenYuanXinXi);
}
System.out.println("請選擇你要修改該人員的哪項(xiàng)內(nèi)容");
System.out.println("1.人員名字? ? 2.人員類別? ? 3.人員地址");
//XiuGaixuanXiang修改選項(xiàng)
int XiuGaixuanXiang = s.nextInt();
if (XiuGaixuanXiang == 1) {
System.out.println("請輸入該人員的新名字");
String newName = s.next();
String sqlXiuGai = "update renYuanXinXi set renYuanName='"
+ newName + "' where renYuanID="
+ renYuanID + "";
if (sta
.executeUpdate(sqlXiuGai) > 0) {
System.out.println("名字修改成功");
} else {
System.out.println("修改失敗，請重試");
}
} else if (XiuGaixuanXiang == 2) {
System.out.println("---執(zhí)行修改類型的操作---");
String sqlXiuGai = "select * from renYuanLeiXing";
Class.forName("com.microsoft.sqlserver.jdbc."
+ "SQLServerDriver");
ResultSet resXiuGai = sta
.executeQuery(sqlXiuGai);
System.out.println("類型ID\t類型名稱");
while (resXiuGai.next()) {
System.out.println(resXiuGai.getInt("renYuanLeiXingID")
+ "\t"
+ resXiuGai.getString("renYuanLeiXingName"));
}
int ShuRuDeShangPinTypeID = s.nextInt();
sqlXiuGai = "update renYuanXinXi set renYuanLeiXingID='"
+ ShuRuDeShangPinTypeID + "' where renYuanID="
+ renYuanID + "";
if (sta
.executeUpdate(sqlXiuGai) > 0) {
System.out.println("類型修改成功");
} else {
System.out.println("修改失敗，請重試");
}
} else if (XiuGaixuanXiang == 3) {
System.out.println("請輸入新地址");
String newDiZhi = s.next();
String sqlXiuGai = "update renYuanXinXi set renYuanDiZhi='"
+ newDiZhi
+ "' where renYuanID="
+ renYuanID + "";
if (sta
.executeUpdate(sqlXiuGai) > 0) {
System.out.println("修改成功");
} else {
System.out.println("修改失敗，請重試");
}
}
} else {
System.out.println("編號輸入錯(cuò)誤");
}
} else if (xuanXiang == 5) {
System.out.println("---執(zhí)行增加操作---");
System.out.println("請輸入名字");
String renYuanName = s.next();
System.out.println("請輸入類型ID");
Class.forName("com.microsoft.sqlserver."
+ "jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://localhost;databaseName=zhiwei", "sa",
"1234abcd");
Statement sta = con.createStatement();
String sql = "select * from renYuanLeiXing";
ResultSet res = sta.executeQuery(sql);
System.out.println("類型ID\t類型名稱");
while (res.next()) {
System.out.println(res.getInt(1) + "\t" +?
res.getString(2));
}
int renYuanLeiXingID = s.nextInt();
System.out.println("請輸入人員地址");
String renYuanDiZhi = s.next();
sql = "insert into renYuanXinXi values('" +
renYuanName + "'," +renYuanLeiXingID + ",'"?
+ renYuanDiZhi + "')";
if (sta.executeUpdate(sql) > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失敗，請重試!");
}
} else if (xuanXiang == 4) {
System.out.println("---執(zhí)行刪除操作---");
System.out.println("請輸入你要?jiǎng)h除的商品ID");
Class.forName("com.microsoft.sqlserver."
+ "jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://localhost;databaseName=zhiwei",?
"sa",
"1234abcd");
Statement sta = con.createStatement();
String sql = "select * from renYuanXinXi a "
+ "inner join renYuanLeiXing b "
+ "on a.renYuanLeiXingID=b.renYuanLeiXingID";
ResultSet res = sta.executeQuery(sql);
//System.out.println("人員編號\t人員名字\t人員類型名稱\t人員地址");
while (res.next()) {
System.out.println("人員編號:"+
res.getInt("renYuanID") + ";\t"
+"人員名字:"+ res.getString("renYuanName") + ";\t"
+"人員類型名稱:"+ res.getString
("renYuanLeiXingName") + ";\t"
+"人員地址:"+ res.getString("renYuanDiZhi")+ ";\t");
}
int IdDelete = s.nextInt();
sql = "delete renYuanXinXi where renYuanID=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, IdDelete);
if (ps.executeUpdate() > 0) {
System.out.println("刪除成功");
} else {
System.out.println("刪除失敗，請重試");
}
} else {
System.out.println("編號輸入錯(cuò)誤");
}
}
}
package JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtils {
private static Connection con = null;
private static ResultSet res = null;
private static Statement sta = null;
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getCon() {
if (con == null) {
try {
con = DriverManager.getConnection(
"jdbc:sqlserver://DESKTOP-49FTFSP;"
+ "databaseName=zhiwei", "sa", "1234abcd");
} catch (SQLException e) {
e.printStackTrace();
}
}
return con;
}
public static ResultSet Select(String sql) {
con = getCon();
try {
sta = con.createStatement();
res = sta.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
public static boolean ZSG(String sql) {
boolean b = false;
con = getCon();
try {
sta = con.createStatement();
int num = sta.executeUpdate(sql);
if (num > 0) {
b = true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return b;
}
}
package JDBC;
public class renYuanLeiXing {
private int renYuanLeiXingID;
private String? renYuanLeiXingName;
public renYuanLeiXing() {
}
public int getRenYuanLeiXingID() {
return renYuanLeiXingID;
}
public void setRenYuanLeiXingID(int renYuanLeiXingID) {
this.renYuanLeiXingID = renYuanLeiXingID;
}
public String getRenYuanLeiXingName() {
return renYuanLeiXingName;
}
public void setRenYuanLeiXingName(String renYuanLeiXingName) {
this.renYuanLeiXingName = renYuanLeiXingName;
}
@Override
public String toString() {
return "renYuanLeiXing [renYuanLeiXingID=" + renYuanLeiXingID
+ ", renYuanLeiXingName=" + renYuanLeiXingName + "]";
}
}
package JDBC;
public class renYuanXinXi {
private int? renYuanID;
private String renYuanDiZhi;
private String renYuanName;
private renYuanLeiXing renYuanLeiXingID;
public renYuanXinXi() {
}
public int getRenYuanID() {
return renYuanID;
}
public void setRenYuanID(int renYuanID) {
this.renYuanID = renYuanID;
}
public String getRenYuanDiZhi() {
return renYuanDiZhi;
}
public void setRenYuanDiZhi(String renYuanDiZhi) {
this.renYuanDiZhi = renYuanDiZhi;
}
public String getRenYuanName() {
return renYuanName;
}
public void setRenYuanName(String renYuanName) {
this.renYuanName = renYuanName;
}
public renYuanLeiXing getRenYuanLeiXingID() {
return renYuanLeiXingID;
}
public void setRenYuanLeiXingID(renYuanLeiXing renYuanLeiXingID) {
this.renYuanLeiXingID = renYuanLeiXingID;
}
@Override
public String toString() {
return "renYuan [renYuanID=" + renYuanID + ", renYuanDiZhi=" + renYuanDiZhi
+ ", renYuanName=" + renYuanName + ", renYuanLeiXingID="
+ renYuanLeiXingID + "]";
}
}
導(dǎo)如JDBC包的方法：
