create table denglu(

uname varchar(30),

upwd varchar(30)

)

insert into denglu values('詩(shī)書(shū)','123')

insert into denglu values('畫(huà)唱','456')

--drop table denglu

package JDBC;

import java.sql.*;

import java.util.Scanner;

public class dengLu {

public static void main(String[] args) throws Exception{

Scanner s=new Scanner(System.in);

System.out.println("請(qǐng)輸入用戶名");

String uname=s.next();

System.out.println("請(qǐng)輸入用戶密碼");

String upwd=s.next();

//1、導(dǎo)入數(shù)據(jù)包（就是把sqljdbc4-2.0.jar導(dǎo)入文件夾中某處的里面）

//2、加載數(shù)據(jù)包（靜態(tài)加載類加載這個(gè)包，就是用下面的代碼）

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

//3、建立數(shù)據(jù)庫(kù)鏈接的對(duì)象Connection??

// 4.鏈接的數(shù)據(jù)庫(kù)位置（jdbc:sqlserver://localhost;）

// 5.數(shù)據(jù)庫(kù)名6.用戶名7.密碼

//jdbc:sqlserver://localhost;databaseName=yonghu,qqq,123

Connection con=

DriverManager.getConnection("jdbc:sqlserver://localhost;"

+ "databaseName=yonghu","qqq","123");

//8、建立執(zhí)行sql語(yǔ)句的對(duì)象(statement)

String sql="select * from denglu where uname=? and upwd=?";

System.out.println(sql);

//用Statement sta=con.createStatement();會(huì)有注入攻擊，所以不用。

PreparedStatement ps=con.prepareStatement(sql);//傳入sql語(yǔ)句

ps.setObject(1,uname);//"n"代表SQL語(yǔ)句中的第n個(gè)"?"

ps.setObject(2,upwd);

//9、寫(xiě)sql語(yǔ)句，執(zhí)行

//10、建立得到數(shù)據(jù)的對(duì)象（ResultSet）

ResultSet res=ps.executeQuery();

//11、遍歷數(shù)據(jù)

while(res.next()){

System.out.println("用“getObject(1)”得到的用戶名為："

+res.getObject(1)+"；");

System.out.println("用“getObject(列名)”得到的用戶名為:"

+res.getObject("uname")+" ；");

System.out.println("用“getObject(列名)”得到的密碼為: "

+res.getObject("upwd"));

//12、關(guān)閉鏈接

//什么是防注入?直接字符串拼接，用戶再后輸入一個(gè)or 1=1的時(shí)候，那么這個(gè)

//條件是肯定成立的，所以用戶輸入不輸入用戶名沒(méi)有多大的關(guān)系了

//為了解決這個(gè)問(wèn)題，就要使用preparedstatement解決，它默認(rèn)將

//sql語(yǔ)句編譯成函數(shù)，每次只要傳入相應(yīng)的參數(shù)就可以了，就可以

//防止用戶sql注入。

//預(yù)處理:我們每次對(duì)于數(shù)據(jù)庫(kù)操作數(shù)據(jù)庫(kù)都要將sql語(yǔ)句編譯成函數(shù)，

//如果我們使用預(yù)處理的時(shí)候，數(shù)據(jù)庫(kù)就默認(rèn)生成了一個(gè)函數(shù)，以后再

//傳入相同的內(nèi)容的時(shí)候，只需要調(diào)用這個(gè)函數(shù)就可以了，這個(gè)就可以提升

//sql執(zhí)行的效率。

}

}

}

建立學(xué)生表，包含學(xué)生編號(hào)，姓名，年齡，性別，地址，增加學(xué)生實(shí)體類，編寫(xiě)返回學(xué)生信息的集合，制作增刪改查，要求使用預(yù)處理

create table student(

id int primary key identity(1, 1),

name nvarchar (20) ,

age int check (Age>5 and Age<60),

sex nvarchar (10) check(sex='男'or sex='女'),

address nvarchar (20)

)

insert into student values('詩(shī)書(shū)',19,'男','江西')

insert into student values('江唯',20,'女','湖南')

insert into student values('嘉怡',21,'女','上海')

insert into student values('畫(huà)唱',22,'男','北京')

insert into student values('點(diǎn)贊',20,'男','武漢')

--select * from student

--delete? from student

--drop table student

package StudentJDBC;

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.*;

import JDBC.renYuanLeiXing;

public class JDBCZSGC {

// SelectDanGeRenYuan搜索單個(gè)人員

public static ResultSet SelectDanGeStudent = null;

public static Scanner s = new Scanner(System.in);

public static void bianLiStudentFangFa

(ArrayList<student> studentJiHe) {

for (student i : studentJiHe) {

System.out.println(i);

}

}

//

//chuanZhiDaoJiHeDeFangFa:為傳值到集合的方法，

//會(huì)把sql執(zhí)行時(shí)，調(diào)用這個(gè)方法得到的值，傳值時(shí)，查到內(nèi)容時(shí)

//，查到的內(nèi)容都會(huì)加到ArrayList集合studentJiHe中，

//studentJiHe的才可能有內(nèi)容，size()大小值才可能不為0

//

//size()大小值>0來(lái)判斷，集合是否有內(nèi)容

//

//chuanZhiDaoJiHeDeFangFa(String sql)前面的

//

//public static ArrayList<student>表示

//

//之后返回的類型為只接受student類的內(nèi)容的ArrayList集合，

//比如下面的return studentJiHe;就可以證實(shí)

//自己總結(jié)的語(yǔ)法：

//public static 類型 被命名的名;或public static 類型

//

//方法名(類型? 被傳的值的被命名的名)?

public static ArrayList<student>

chuanZhiDaoJiHeDeFangFa(ResultSet res) {

ArrayList<student> studentJiHe =

new ArrayList<student>();

try {

while (res.next()) {

student studentClass =

new student();

studentClass.setId

(res.getInt(1));

studentClass.setName

(res.getString(2));

studentClass.setAge

(res.getInt(3));

studentClass.setSex

(res.getString(4));

studentClass.setAddress

(res.getString(5));

studentJiHe.add(studentClass);

//

//

//以上是把內(nèi)容加到集合中，不這么寫(xiě)，

//下面的增刪改查等的集合長(zhǎng)度都會(huì)為0，集合中會(huì)沒(méi)內(nèi)容，

//

//這里getStudent(String sql)

}

} catch (SQLException e) {

e.printStackTrace();

}

return studentJiHe;

}

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.打印所有的人員信息\n"

+ "2.輸入編號(hào)修改指定人員信息\n"

+ "3.輸入編號(hào)查詢單個(gè)人員信息\n"

+ "4.刪除指定人員信息\n"

+ "5.增加人員信息");

int xuanXiangAll = s.nextInt();

//

//xuanXiangAll,xuanXiangAll_a等的命名方式可讓我知道

//xuanXiangAll_a是被某個(gè)xuanXiangAll套住的，

//xuanXiangAll_a在xuanXiangAll里面

if (xuanXiangAll == 1) {

Connection con = bianLiAllStudentXinXiFangFa();

//

//Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

//

//

//

//Connection con = DriverManager.getConnection(

//

//

//

//"jdbc:sqlserver://localhost;databaseName=yonghu", "qqq",

//

//

//

//"123");

//

//

//

//String sql = "select * from student ";

////

////ResultSet res = DBUtils.Select(sql);

////這一句不可用，就算DBUtils中用了prepareStatement也不行

//

//

//PreparedStatement ps = con.prepareStatement(sql);

//

//ResultSet res =ps.executeQuery();

//

//

////用PreparedStatement時(shí)，用executeQuery();其余用executeQuery(sql);

//

//

//while (res.next()) {

//

//

//

//System.out.println("編號(hào):"+res.getInt("id") + ";\t"

//

//

//

//+"名字:"+ res.getString("name") + ";\t"

//

//+"年齡:"+ res.getInt("age") + ";\t"

//+"性別:"+ res.getString("sex") + ";\t"

//+"地址:"+ res.getString("address")+ ";\t");

//

//

//

//}

}

else if (xuanXiangAll == 2) {

System.out.println("---執(zhí)行修改操作---");

System.out.println("請(qǐng)輸入你要修改的人員編號(hào)");

bianLiAllStudentXinXiFangFa();

int id = s.nextInt();

String sqlDanGe = "select * from student where id=?";

Connection con = bianLiAllStudentXinXiFangFa();

PreparedStatement ps1=con.prepareStatement(sqlDanGe);

ps1.setObject(1,id);

System.out.println(sqlDanGe);

//ResultSet res1 = ps1.executeQuery();

SelectDanGeStudent = ps1.executeQuery();

ArrayList<student> studentJiHe = chuanZhiDaoJiHeDeFangFa

(SelectDanGeStudent);//return出，打印出內(nèi)容

System.out.println(studentJiHe.size());

if (studentJiHe.size()>0) {

// danGeRenYuanXinXi單個(gè)人員信息

for (student danGeRenYuanXinXi : studentJiHe)

{

System.out.println(danGeRenYuanXinXi);

}

System.out.println("請(qǐng)選擇你要修改該人員的哪項(xiàng)內(nèi)容");

System.out.println("1.人員名字2.人員地址");

int xuanXiangAll_a = s.nextInt();

if (xuanXiangAll_a == 1) {

System.out.println("請(qǐng)輸入該人員的新名字");

String newName = s.next();

String sqlXiuGai = "update student set name=?"

+ " where id=?";

//報(bào)錯(cuò)為對(duì)象錯(cuò),就可能為SQL語(yǔ)句中的表名錯(cuò)了

PreparedStatement ps11=con.prepareStatement(sqlXiuGai);

//自己總結(jié)的語(yǔ)法:prepareStatement(要執(zhí)行的SQL語(yǔ)句),

//要執(zhí)行的SQL語(yǔ)句不要寫(xiě)錯(cuò)或共用了,下面的ps11位置的內(nèi)容要

//和上面的ps11位置的內(nèi)容一樣的內(nèi)容

ps11.setObject(1,newName);

ps11.setObject(2,id);

if (ps11.executeUpdate() > 0) {

System.out.println("名字修改成功");

bianLiAllStudentXinXiFangFa();

} else {

System.out.println("修改失敗，請(qǐng)重試");

}

}? else if (xuanXiangAll_a == 2) {

System.out.println("請(qǐng)輸入新地址");

String newDiZhi = s.next();

String sqlXiuGai = "update student set address=?"

+ " where id=?";

PreparedStatement ps11=con.prepareStatement(sqlXiuGai);

ps11.setObject(1,newDiZhi);

ps11.setObject(2,id);

if (ps11

.executeUpdate() > 0) {

System.out.println("修改成功");

bianLiAllStudentXinXiFangFa();

} else {

System.out.println("修改失敗，請(qǐng)重試");

}

}

} else {

System.out.println("編號(hào)輸入錯(cuò)誤");

}

}?

else if (xuanXiangAll == 3) {

System.out.println("---執(zhí)行查詢單個(gè)人員信息的操作---");

System.out.println("請(qǐng)輸入你要查詢的人員ID");

bianLiAllStudentXinXiFangFa();

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection con = DriverManager.getConnection(

"jdbc:sqlserver://localhost;databaseName=yonghu", "qqq",

"123");

int id = s.nextInt();

String sql = "select * from student where "

+ "id=?";

PreparedStatement ps=con.prepareStatement(sql);//傳入sql語(yǔ)句

ps.setObject(1,id);

ResultSet res = ps.executeQuery();

//res.getObject("uname");

while (res.next()) {

System.out.println("編號(hào):"+res.getInt("id") + ";\t"

+"名字:"+ res.getString("name") + ";\t"

+"年齡:"+ res.getInt("age") + ";\t"

+"性別:"+ res.getString("sex") + ";\t"

+"地址:"+ res.getString("address")+ ";\t");

}

}

else if (xuanXiangAll == 4) {

System.out.println("---執(zhí)行刪除操作---");

System.out.println("請(qǐng)輸入你要?jiǎng)h除的學(xué)生ID");

//bianliAllStudentXinXi()

//為我用鼠標(biāo)右鍵封裝的遍歷所有學(xué)生信息的方法，調(diào)用同一個(gè)

Connection con = bianLiAllStudentXinXiFangFa();

//因?yàn)橄旅嬗衏on所以這里要聲明Connection con?

int deleteId = s.nextInt();

String sql2 = "delete student where id=?";

PreparedStatement ps2=con.prepareStatement(sql2);

ps2.setInt(1, deleteId);

//ResultSet res2 = ps2.executeQuery();這句這里不必些寫(xiě)，因?yàn)橄旅鏇](méi)必要遍歷

//

//自己的總結(jié):以下順序不可錯(cuò)：1.接受輸入

//2.SQL語(yǔ)句

//3.用prepareStatement();執(zhí)行SQL語(yǔ)句

//4.用setInt();

if (ps2.executeUpdate() > 0) {

System.out.println("刪除成功");

?bianLiAllStudentXinXiFangFa();

//自己總結(jié)的調(diào)用方法的語(yǔ)法：方法名();

} else {

System.out.println("刪除失敗，請(qǐng)重試");

}

}?

else if (xuanXiangAll == 5) {

bianLiAllStudentXinXiFangFa();

System.out.println("---執(zhí)行增加操作---");

Class.forName("com.microsoft.sqlserver."

+ "jdbc.SQLServerDriver");

Connection con = DriverManager.getConnection(

"jdbc:sqlserver://localhost;databaseName=yonghu", "qqq",

"123");

//

//System.out.println("請(qǐng)輸入要增加的編號(hào)");

//

//編號(hào)id在SQL中設(shè)置為自增，所以不必加入

//

//int insertId = s.nextInt();

System.out.println("請(qǐng)輸入要增加學(xué)生的名字");

String insertName = s.next();

System.out.println("請(qǐng)輸入要增加學(xué)生的年齡(60>年齡>5)");

int insertAge = s.nextInt();

System.out.println("請(qǐng)輸入要增加學(xué)生的性別（只可以填“男”或“女”）");

String insertSex = s.next();

System.out.println("請(qǐng)輸入要增加學(xué)生的地址");

String insertAddress = s.next();

String sql = "insert into student values(?,?,?,?)";

PreparedStatement ps=con.prepareStatement(sql);

ps.setObject(1,insertName);

ps.setObject(2,insertAge);

ps.setObject(3,insertSex);

ps.setObject(4,insertAddress);

if (ps.executeUpdate() > 0) {

System.out.println("添加成功!");

bianLiAllStudentXinXiFangFa();

//自己總結(jié)的調(diào)用方法的語(yǔ)法：方法名();

} else {

System.out.println("添加失敗，請(qǐng)重試!");

}

}

else {

System.out.println("編號(hào)輸入錯(cuò)誤");

}

}

private static Connection bianLiAllStudentXinXiFangFa()

throws ClassNotFoundException, SQLException {

Class.forName("com.microsoft.sqlserver."

+ "jdbc.SQLServerDriver");

Connection con = DriverManager.getConnection(

"jdbc:sqlserver://localhost;databaseName=yonghu",

"qqq",

"123");

String sql = "select * from student";

PreparedStatement ps=con.prepareStatement(sql);

ResultSet res = ps.executeQuery();

System.out.println("_________");

while (res.next()) {

System.out.println("編號(hào):"+res.getInt("id") + ";\t"

+"名字:"+ res.getString("name") + ";\t"

+"年齡:"+ res.getInt("age") + ";\t"

+"性別:"+ res.getString("sex") + ";\t"

+"地址:"+ res.getString("address")+ ";\t");

}

System.out.println("_________");

return con;

}

}

package StudentJDBC;

import JDBC.renYuanLeiXing;

public class student {

private int id;

private String name;

private int age;

private String sex ;

private String address ;

//

//

//private renYuanLeiXing renYuanLeiXingID;

public student() {

}

public int getRenYuanID() {

return id;

}

public void setId(int renYuanID) {

this.id = renYuanID;

}

public String getRenYuanDiZhi() {

return name;

}

public void setName1(String renYuanDiZhi) {

this.name = renYuanDiZhi;

}

public student(int id, String name,?

int age, String sex, String address) {

super();

this.id = id;

this.name = name;

this.age = age;

this.sex = sex;

this.address = address;

}

@Override

public String toString() {

return "student [id=" + id + ", name="?

+ name + ", age=" + age + ", sex="

+ sex + ", address=" + address + "]";

}

public int getId() {

return id;

}

public void setId1(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public String getSex() {

return sex;

}

public void setSex(String sex) {

this.sex = sex;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

}