JDBC：使用自己封裝的DBUtils來(lái)做，新建職位表，將內(nèi)容封到一個(gè)集合中去,快捷方式等
--題目：
--作業(yè)(使用自己封裝的DBUtils來(lái)做):
? -- 新建職位表
? ?--? 職位編號(hào)? 職位名稱
? ? ?--1? ? 總經(jīng)理
? ? -- 2? ? 經(jīng)理
? ? -- 3? ? 普通員工
? ?--新建人員表:
? ? -- 人物編號(hào) 人物名稱 人物年齡? ? 手機(jī)號(hào)? ? ?職位ID
? ? -- 1? ? 張三? ? 20? ? 111? ? 1
? ? -- 2? ? 李四? ? 18? ? 222? ? 2
? ?--? 依照上方添加5條數(shù)據(jù)
? ?--? 。。。
? ? -- 1.使用JDBC刪除李四
? ? ?--2.使用JDBC增加一條數(shù)據(jù) 內(nèi)容為
? ? -- 3.修改張三的手機(jī)號(hào)為888
? ? -- 4.查詢職位表和人員表的所有信息（先普通查詢后記得將內(nèi)容封到一個(gè)集合中去）
我常熬夜到12點(diǎn)或2點(diǎn)等時(shí)寫(xiě)的答案：
--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('總經(jīng)理')
insert into zhiwei values('經(jīng)理' )
insert into zhiwei values('員工')
insert into yuangong values('張三',20,'66662233668',1)
insert into yuangong values('李四',18,'66662233666',2)
insert into yuangong values('項(xiàng)點(diǎn)贊',20,'66668888666',3)
insert into yuangong values( '項(xiàng)投幣',20,'66662222333',4)
insert into yuangong values('詩(shī)書(shū)畫(huà)唱',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刪除李四
//?用普通調(diào)用方法遍歷時(shí)要2個(gè).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為用于查找的靜態(tài)方法 */
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)的靜態(tài)方法,命名不可太長(zhǎng)不然這里會(huì)不管用 */
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)的靜態(tài)方法,命名不可太長(zhǎng)不然這里會(huì)不管用 */
System.out.println("刪除成功");
} else {
System.out.println("刪除失敗");
}
}
}
//2.使用JDBC增加一條數(shù)據(jù) 內(nèi)容為 '給詩(shī)書(shū)畫(huà)唱點(diǎn)贊'
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( '給詩(shī)書(shū)畫(huà)唱點(diǎn)贊')";
if (jdbc.ZXG(sql)) {
/* ZXG為用于增加(Z)或修改(X)或刪除(S)的靜態(tài)方法,命名不可太長(zhǎng)不然這里會(huì)不管用 */
System.out.println("增加成功");
} else {
System.out.println("增加失敗");
}
}
}
// 3.修改張三的手機(jī)號(hào)為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)的靜態(tài)方法,命名不可太長(zhǎng)不然這里會(huì)不管用 */
System.out.println("修改成功");
} else {
System.out.println("修改失敗");
}
}
}
// 4.查詢職位表和人員表的所有信息（方法一：普通查詢法）
//?用普通調(diào)用方法遍歷時(shí)要2個(gè).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為用于查找的靜態(tài)方法 */
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)的靜態(tài)方法,命名不可太長(zhǎng)不然這里會(huì)不管用 */
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.查詢職位表和人員表的所有信息（方法二：將內(nèi)容封到一個(gè)集合中去,用集合做法）*/
//用集合做時(shí)要4個(gè).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為用于查找的靜態(tài)方法 */
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)的靜態(tài)方法,命名不可太長(zhǎng)不然這里會(huì)不管用 */
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);
}
}
}
/*快捷方式：鼠標(biāo)右鍵，點(diǎn)Source，再點(diǎn)含get字符的選項(xiàng)可生成get，set語(yǔ)句，出現(xiàn)：
或點(diǎn)含Superclass字符的選項(xiàng)可出現(xiàn)：
*/
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 + "]";
}
}
//擴(kuò)展知識(shí)和個(gè)人感悟或理解等的圖片：
