欢迎光临散文网 会员登陆 & 注册

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

2020-05-23 17:04 作者:诗书画唱  | 我要投稿



添加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包的方法:


Java JDBC改进:shopping,商品,增删改查,DBUtils,人员信息表,导包【诗书画唱】的评论 (共 条)

分享到微博请遵守国家法律