Java JDBC改进:shopping,商品,增删改查,DBUtils,人员信息表,导包【诗书画唱】


添加5条数据进行测试在控制台将数据打印,两表查询。

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('海报')
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)
--外键会影响删除
)
insert into shangPin values('诗书苹果',19,1,'1年'),('画唱香蕉',20,2,'2年')
,('好吃面条',21,3,'3年') ,('帅哥CD',22,4,'4年') ,('诗书画唱海报',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"
+ ",商品价格:"
+ res.getInt("sp_Price") + "\t"
+ ",商品类型ID:"
+ res.getInt("sp_TypeID") + "\t"
+ ",商品保质期:"
+ res.getString("sp_Baozhiqi"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

将商品ID为3的商品名称修改为“西瓜”,价格改为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("删除失败");
}
}
}



添加一条商品名称为“橘子”,价格为4元,保质期为当前时间+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("增加失败");
}
}
}



将增加后的商品进行重新遍历出来
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"
+ ",商品价格:"
+ res.getInt("sp_Price") + "\t"
+ ",商品类型ID:"
+ res.getInt("sp_TypeID") + "\t"
+ ",商品保质期:"
+ res.getString("sp_Baozhiqi"));
}
}
}


制作一个人员信息表,包含人员编号,姓名,类型编号(外键),地址
人员类型表
包含类型编号(对应着人员表的类型),类型名称。
添加5条信息
1.请选择功能:1.打印所有的人员信息 2.输入编号修改指定人员信息
3.输入编号查询单个人员信息 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('经理'),('Java构造师')
,('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('项点赞',1,'点赞村' )
,('项投币',3, '点赞县' )
,('项关注',1,'点赞市' )
,('项交友',4,'点赞省' )
,('项诗书画唱',2,'点赞国' )

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搜索单个人员
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("选项:1.打印所有的人员信息 2.输入编号修改指定人员信息"
+ "3.输入编号查询单个人员信息 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("---执行查询单个人员信息的操作---");
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("---执行修改操作---");
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单个人员信息
for (renYuanXinXi danGeRenYuanXinXi : renYuanXinXiJiHe)
{
System.out.println(danGeRenYuanXinXi);
}
System.out.println("请选择你要修改该人员的哪项内容");
System.out.println("1.人员名字 2.人员类别 3.人员地址");
//XiuGaixuanXiang修改选项
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("---执行修改类型的操作---");
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("编号输入错误");
}
} else if (xuanXiang == 5) {
System.out.println("---执行增加操作---");
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("---执行删除操作---");
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 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("编号输入错误");
}
}
}



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 + "]";
}
}


导如JDBC包的方法:


