JDBC：使用自己封装的DBUtils来做，新建职位表，将内容封到一个集合中去,快捷方式等
--题目：
--作业(使用自己封装的DBUtils来做):
-- 新建职位表
-- 职位编号 职位名称
--1 总经理
-- 2 经理
-- 3 普通员工
--新建人员表:
-- 人物编号 人物名称 人物年龄 手机号 职位ID
-- 1 张三 20 111 1
-- 2 李四 18 222 2
-- 依照上方添加5条数据
-- 。。。
-- 1.使用JDBC删除李四
--2.使用JDBC增加一条数据 内容为
-- 3.修改张三的手机号为888
-- 4.查询职位表和人员表的所有信息（先普通查询后记得将内容封到一个集合中去）
我常熬夜到12点或2点等时写的答案：
--create database zhiwei
--drop database zhiwei
create table zhiwei(
zhiwei_ID int primary key identity(1,1),
zhiwei_Name varchar(30) not null
)
--drop table zhiwei
create table yuangong (
yuangong_ID int primary key identity(1, 1),
yuangong_Name varchar(30) not null,
yuangong_Age int check (yuangong_Age>0 and yuangong_Age<100),
yuangong_Phone varchar (100),
zhiwei_ID int,
Foreign key (zhiwei_ID) references zhiwei(zhiwei_ID))
--drop table yuangong
insert into zhiwei values('总经理')
insert into zhiwei values('经理' )
insert into zhiwei values('员工')
insert into yuangong values('张三',20,'66662233668',1)
insert into yuangong values('李四',18,'66662233666',2)
insert into yuangong values('项点赞',20,'66668888666',3)
insert into yuangong values( '项投币',20,'66662222333',4)
insert into yuangong values('诗书画唱',20,'12352013142',5)
select * from yuangong
select * from zhiwei
--delete from zhiwei where zhiwei_ID>2
--update yuangong set yuangong_Phone='888' where yuangong_Name='张三'
--select * from yuangong as yg inner join zhiwei as zw on(yg.zhiwei_ID=zw.zhiwei_ID)
--select *from yuangong a inner join zhiwei b on a. zhiwei_ID=b. zhiwei_ID
// 1.使用JDBC删除李四
// 用普通调用方法遍历时要2个.java文件：
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc {
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 YYCZDJTFF(String sql) {
/* YYCZDJTFF为用于查找的静态方法 */
con = getCon();
try {
sta = con.createStatement();
res = sta.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
public static boolean ZXG(String sql) {
/* ZXG为用于增加(Z)或修改(X)或删除(S)的静态方法,命名不可太长不然这里会不管用 */
boolean b = false;
con = getCon();
try {
sta = con.createStatement();
int num = sta.executeUpdate(sql);
if (num > 0) {
b = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return b;
}
}
package a;
public class zhi_jie_diao_yong_jdbc {
public static void main(String[] args) throws Exception {
String sql = "delete yuangong where yuangong_Name='李四'";
if (jdbc.ZXG(sql)) {
/* ZJDJTFF为用于增加(Z)或修改(X)或删除(S)的静态方法,命名不可太长不然这里会不管用 */
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
}
}
//2.使用JDBC增加一条数据 内容为 '给诗书画唱点赞'
package a;
public class zhi_jie_diao_yong_jdbc {
public static void main(String[] args) throws Exception {
String sql = "insert into yuangong(yuangong_Name) values( '给诗书画唱点赞')";
if (jdbc.ZXG(sql)) {
/* ZXG为用于增加(Z)或修改(X)或删除(S)的静态方法,命名不可太长不然这里会不管用 */
System.out.println("增加成功");
} else {
System.out.println("增加失败");
}
}
}
// 3.修改张三的手机号为888
package a;
public class zhi_jie_diao_yong_jdbc {
public static void main(String[] args) throws Exception {
String sql = "update yuangong set yuangong_Phone='888' where yuangong_Name='张三'";
if (jdbc.ZXG(sql)) {
/* ZJDJTFF为用于增加(Z)或修改(X)或删除(S)的静态方法,命名不可太长不然这里会不管用 */
System.out.println("修改成功");
} else {
System.out.println("修改失败");
}
}
}
// 4.查询职位表和人员表的所有信息（方法一：普通查询法）
// 用普通调用方法遍历时要2个.java文件：
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc {
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 YYCZDJTFF(String sql) {
/* YYCZDJTFF为用于查找的静态方法 */
con = getCon();
try {
sta = con.createStatement();
res = sta.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
public static boolean ZXG(String sql) {
/* ZXG为用于增加(Z)或修改(X)或删除(S)的静态方法,命名不可太长不然这里会不管用 */
boolean b = false;
con = getCon();
try {
sta = con.createStatement();
int num = sta.executeUpdate(sql);
if (num > 0) {
b = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return b;
}
}
package a;
import java.sql.ResultSet;
public class zhi_jie_diao_yong_jdbc {
public static void main(String[] args) throws Exception {
ResultSet res = jdbc.YYCZDJTFF("select * "
+ "from yuangong as yg inner join zhiwei as"
+ " zw on(yg.zhiwei_ID=zw.zhiwei_ID)");
while (res.next()) {
System.out.println("yuangong_ID:" + res.getObject(1) + "\t"
+ ",yuangong_Name:" + res.getObject(2) + "\t"
+ ",yuangong_Age:" + res.getObject(3) + "\n"
+ ",yuangong_Phone::" + res.getObject(4) + "\t"
+ ",zhiwei_ID:" + res.getObject(5) + "\t" + ",zhiwei_ID:"
+ res.getObject(6) + "\t" + ",zhiwei_Name:"
+ res.getObject(7) + "\t");
}
}
}
/*4.查询职位表和人员表的所有信息（方法二：将内容封到一个集合中去,用集合做法）*/
//用集合做时要4个.java文件:
package a;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbc {
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 YYCZDJTFF(String sql) {
/* YYCZDJTFF为用于查找的静态方法 */
con = getCon();
try {
sta = con.createStatement();
res = sta.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
public static boolean ZXG(String sql) {
/* ZXG为用于增加(Z)或修改(X)或删除(S)的静态方法,命名不可太长不然这里会不管用 */
boolean b = false;
con = getCon();
try {
sta = con.createStatement();
int num = sta.executeUpdate(sql);
if (num > 0) {
b = true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return b;
}
}
package a;
import java.sql.ResultSet;
import java.util.ArrayList;
public class zhi_jie_diao_yong_jdbc {
public static ArrayList<yuangong> getYuangong(String sql) throws Exception {
ArrayList<yuangong> arr_yuangong = new ArrayList<yuangong>();
ResultSet res = jdbc.YYCZDJTFF(sql);
while (res.next()) {
yuangong yg = new yuangong();
yg.setYuangong_ID(res.getInt(1));
yg.setYuangong_Name(res.getString(2));
yg.setYuangong_Age(res.getInt("Yuangong_Age"));
yg.setYuangong_Phone(res.getString("yuangong_phone"));
zhiwei zw = new zhiwei();
zw.setZhiwei_ID(res.getInt("zhiwei_ID"));
zw.setZhiwei_Name(res.getString("zhiwei_Name"));
yg.setZhiwei_ID(zw);
arr_yuangong.add(yg);
}
return arr_yuangong;
}
public static void main(String[] args) throws Exception {
ArrayList<yuangong> arr_yuangong = getYuangong("select *from yuangong a inner join"
+ " zhiwei b on a. zhiwei_ID=b. zhiwei_ID");
for (yuangong i : arr_yuangong) {
System.out.println(i);
}
}
}
/*快捷方式：鼠标右键，点Source，再点含get字符的选项可生成get，set语句，出现：
或点含Superclass字符的选项可出现：
*/
package a;
public class yuangong {
private int yuangong_Age;
private int yuangong_ID;
private String yuangong_Name;
private String yuangong_Phone;
private zhiwei zhiwei_ID;
public yuangong() {
}
public yuangong(int yuangong_Age, int yuangong_ID, String yuangong_Name,
String yuangong_Phone, zhiwei zhiwei_ID) {
super();
this.yuangong_Age = yuangong_Age;
this.yuangong_ID = yuangong_ID;
this.yuangong_Name = yuangong_Name;
this.yuangong_Phone = yuangong_Phone;
this.zhiwei_ID = zhiwei_ID;
}
public int getYuangong_Age() {
return yuangong_Age;
}
public int getYuangong_ID() {
return yuangong_ID;
}
public String getYuangong_Name() {
return yuangong_Name;
}
public String getYuangong_Phone() {
return yuangong_Phone;
}
public zhiwei getZhiwei_ID() {
return zhiwei_ID;
}
public void setYuangong_Age(int yuangong_Age) {
this.yuangong_Age = yuangong_Age;
}
public void setYuangong_ID(int yuangong_ID) {
this.yuangong_ID = yuangong_ID;
}
public void setYuangong_Name(String yuangong_Name) {
this.yuangong_Name = yuangong_Name;
}
public void setYuangong_Phone(String yuangong_Phone) {
this.yuangong_Phone = yuangong_Phone;
}
public void setZhiwei_ID(zhiwei zhiwei_ID) {
this.zhiwei_ID = zhiwei_ID;
}
@Override
public String toString() {
return "yuangong [yuangong_Age=" + yuangong_Age + ", yuangong_ID="
+ yuangong_ID + ", yuangong_Name=" + "\n" + yuangong_Name
+ ", yuangong_Phone=" + yuangong_Phone + ", zhiwei_ID="
+ zhiwei_ID + "]";
}
}
package a;
public class zhiwei {
private int zhiwei_ID;
private String zhiwei_Name;
public zhiwei() {
}
// public zhiwei(int zhiwei_ID, String zhiwei_Name) {
// super();
// this.zhiwei_ID = zhiwei_ID;
// this.zhiwei_Name = zhiwei_Name;
// }
public int getZhiwei_ID() {
return zhiwei_ID;
}
public String getZhiwei_Name() {
return zhiwei_Name;
}
public void setZhiwei_ID(int zhiwei_ID) {
this.zhiwei_ID = zhiwei_ID;
}
public void setZhiwei_Name(String zhiwei_Name) {
this.zhiwei_Name = zhiwei_Name;
}
@Override
public String toString() {
return "zhiwei [zhiwei_ID=" + zhiwei_ID + "\n" + ", zhiwei_Name="
+ zhiwei_Name + "]";
}
}
//扩展知识和个人感悟或理解等的图片：
