_01Simple_JDBCDemo01
package com.jdbc._01Simple;
import java.sql.*;
/**
* JDBC的第一个程序编写: 修改mydb库中的emp表中的7369这个员工的部门编号为30
* 准备工作: 准备好项目,然后加载第三方jar包,即MYSQL的驱动程序。注意, add as Library
*/
public class JDBCDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//第一步:加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
/*
第二步:获取连接,(使用DriverManager 自动识别程序,并向数据库发送连接请求,如果成功,则返回连接会话)
参数url: 连接数据库的路径地址 jdbc:mysql://ip:port/库名?serverTimezone=Asia/Shanghai&useTimezone=true
参数user: 连接数据库的用户名 root
参数password: 连接数据库的密码 111111
*/
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true","root","111111");
//第三步:通过连接会话对象,来获取可以发送sql语句的对象
Statement stat = conn.createStatement();
//第四步:使用Statement的方法来发送sql语句
/*
execute(String sql): 发送DDL的方法
executeUpdate(String sql): 发送DML的方法,返回受影响的行数
executeQuery(String sql): 发送DQL的方法,返回结果集
Statement该对象发送的sql语句,每次都会解析、编译,效率低,所以使用PreparedStatement
解析SQL: 校验SQL的语法格式是否正确
编译SQL: 验证书写的表名,字段等是否正确,是否与数据库中一致,若存在,则编译通过
*/
// 修改mydb库中的emp表中的7369这个员工的部门编号为30
int num = stat.executeUpdate("update emp set deptno= 30 where empno = 7369");
//第五步:处理结果集
System.out.println("受影响的条数"+num);
//第六步:关闭资源
stat.close();
}
}
_01Simple_JDBCDemo02
package com.jdbc._01Simple;
import java.sql.*;
/**
* 使用JDBC来完成第二个程序:查询emp表中的20号部门所有的员工,信息不使用*
*/
public class JDBCDemo02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 第一步 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//第二步:获取连接,(使用DriverManager 自动识别程序,并向数据库发送连接请求,如果成功,则返回连接会话)
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true","root","111111");
// 第三步:通过连接会话对象,来获取可以发送sql语句的对象,并发送sql
Statement stat = conn.createStatement();
ResultSet sql = stat.executeQuery("select empno,ename,job,mgr,deptno,sal,comm,hiredate from emp where deptno = 20 ");
//注意: DBMS执行之后的结果被发送到客户端,被封装到ResultSet对象中
// 第四步:处理查询结果集 里面存储了N行记录,指针默认在第一行之前,因此想要获取每一行的数据,需要移动指针
while(sql.next()){ //向下移动指针
int empno = sql.getInt(1);
String ename = sql.getString(2);
String job = sql.getString(3);
int mgr = sql.getInt(4);
int deptno = sql.getInt(5);
double sal = sql.getDouble("sal");
double comm = sql.getDouble("comm");
Date hiredate = sql.getDate("hiredate");
System.out.println(empno + "\t" + ename + "\t" + job + "\t" + mgr + "\t" + deptno + "\t" + sal + "\t" + comm + "\t" + hiredate);
}
// 第五步:关闭资源
conn.close();
}
}
_01Simple_JDBCDemo03
package com.jdbc._01Simple;
import java.sql.*;
public class JDBCDemo03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true","root","111111");
Statement stat = conn.createStatement();
int sql = stat.executeUpdate("insert into emp values (10000,'superman','hero',7369,'2024-08-30',2000,500,40 )");
System.out.println(sql);
stat.close();
}
}
_01Simple_JDBCDemo04
package com.jdbc._01Simple;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo04 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true","root","111111");
Statement stat = conn.createStatement();
int sql = stat.executeUpdate("delete from emp where empno=10000");
System.out.println(sql);
conn.close();
}
}
_02TestDBUtil_JDBCDemo01
package com.jdbc._02TestDBUtil;
import com.jdbc.Util.DBUtil;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 使用DBUtil 重构 _01Simple.JDBCdemo01中的代码
*/
public class JDBCDemo01 {
public static void main(String[] args) throws SQLException {
//调用DBUtil的连接对象,获取连接会话
Connection conn = DBUtil.getConnection();
Statement stat = conn.createStatement();
int num = stat.executeUpdate("update emp set deptno= 30 where empno = 7369");
System.out.println("受影响的条数" + num);
DBUtil.closeConnection(conn);
}
}
_02TestDBUtil_JDBCDemo02
package com.jdbc._02TestDBUtil;
import com.jdbc.Util.DBUtil;
import java.sql.*;
/**
* 使用JDBC来完成第二个程序:查询emp表中的20号部门所有的员工,信息不使用*
*/
public class JDBCDemo02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = DBUtil.getConnection();
Statement stat = conn.createStatement();
ResultSet sql = stat.executeQuery("select empno,ename,job,mgr,deptno,sal,comm,hiredate from emp where deptno = 20 ");
while(sql.next()){ //向下移动指针
int empno = sql.getInt(1);
String ename = sql.getString(2);
String job = sql.getString(3);
int mgr = sql.getInt(4);
int deptno = sql.getInt(5);
double sal = sql.getDouble("sal");
double comm = sql.getDouble("comm");
Date hiredate = sql.getDate("hiredate");
System.out.println(empno + "\t" + ename + "\t" + job + "\t" + mgr + "\t" + deptno + "\t" + sal + "\t" + comm + "\t" + hiredate);
}
DBUtil.closeConnection(conn);
}
}
_03Batch_BatchTest
package com.jdbc._03Batch;
import com.jdbc.Util.DBUtil;
import java.sql.Connection;
import java.sql.Statement;
/**
* 为什么使用JDBC的批处理: 因为Statement发送的SQL语句,DBMS每次逗号解析,编译,性能较低
*
* 批处理的方法: 可以提前将一部分SQL存储在缓存区中,然后一次性的将缓存区中的SQL刷到DBMS里
* 这样可以大大减少了客户端与数据库的交互次数,从而变相的提高效率。
*
* testbatch
* id int primary key auto_increment,
* name varchar(20),
* ender char(1)
*/
public class BatchTest {
public static void main(String[] args) {
Connection conn =null;
try {
conn = DBUtil.getConnection();
// 向数据库中插入1030条记录
Statement stat = conn.createStatement();
for (int i = 0; i < 1030; i++) {
String[] genders= {"f","m"};
String gender = genders[(int)(Math.random()*2)];
String sql = "insert into testbatch values(null,'zhaoyun"+i+"','"+gender+"')";
// 将SQL语句存储在缓存区中
stat.addBatch(sql);
// 缓存区储存50个,就冲刷一次
if (i%50==0){
stat.executeBatch();
}
}
// 清空缓存区
stat.clearBatch();
} catch (Exception e){
e.printStackTrace();
}finally {
DBUtil.closeConnection(conn);
}
}
}
_04SQLIniect_Account
package com.jdbc._04SQLInject;
import java.sql.Date;
import java.util.Objects;
/**
* 定义一个java类型Account 与表的记录进行映射。 一个Account对象表示表中的一条记录信息
*/
public class Account {
private int id;
private String accountId;
private double balance;
private String username;
private String password;
private String idcard;
private Date opertime;
private char gender;
private Account(){}
public Account(int id, String accountId, double balance, String username, String password, String idcard, Date opertime, char gender) {
this.id = id;
this.accountId = accountId;
this.balance = balance;
this.username = username;
this.password = password;
this.idcard = idcard;
this.opertime = opertime;
this.gender = gender;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccountId() {
return accountId;
}
public void setAccountId(String accountId) {
this.accountId = accountId;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getIdcard() {
return idcard;
}
public void setIdcard(String idcard) {
this.idcard = idcard;
}
public Date getOpertime() {
return opertime;
}
public void setOpertime(Date opertime) {
this.opertime = opertime;
}
public char getGender() {
return gender;
}
public void setGender(char gender) {
this.gender = gender;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Account account = (Account) o;
return id == account.id && Double.compare(balance, account.balance) == 0 && gender == account.gender && Objects.equals(accountId, account.accountId) && Objects.equals(username, account.username) && Objects.equals(password, account.password) && Objects.equals(idcard, account.idcard) && Objects.equals(opertime, account.opertime);
}
@Override
public int hashCode() {
return Objects.hash(id, accountId, balance, username, password, idcard, opertime, gender);
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", accountId='" + accountId + '\'' +
", balance=" + balance +
", username='" + username + '\'' +
", password='" + password + '\'' +
", idcard='" + idcard + '\'' +
", opertime=" + opertime +
", gender=" + gender +
'}';
}
}
_04SQLIniect_AppClient
package com.jdbc._04SQLInject;
import java.util.Scanner;
/**
* 使用Scanner来模拟登录案例的客户端界面
*/
public class AppClient {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
//来一个服务端对象
//AppServer server = new AppServer();
AppServer2 server = new AppServer2();
//调用服务端的checkLogin方法检查
Account account = server.checkLogin(username,password);
if(account == null){
System.out.println("用户名或密码不正确,登录失败");
}else{
System.out.println("登录成功,正在跳转......");
}
}
}
_04SQLIniect_AppServer
package com.jdbc._04SQLInject;
import com.jdbc.Util.DBUtil;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* 定义一个服务端。
* 提供一个用于检测用户名和密码是否正确的方法。
* 如果正确,返回一个Account对象
* 如果不正确,返回一个null
*/
public class AppServer {
/**
*
* @param username 要验证的用户名
* @param password 要验证的密码
* @return
*/
public Account checkLogin(String username, String password) {
Account account = null;
Connection conn = null;
try{
//连接数据库
conn = DBUtil.getConnection();
Statement stat = conn.createStatement();
//用户名和密码同时作为where里的条件,进行查询,如果能查询到数据,说明用户名和密码是正确的
String sql = "select * from bank_account where user_name = '" + username + "' and user_pwd = '" + password + "'";
//发送到数据库中执行
ResultSet resultSet = stat.executeQuery(sql);
if(resultSet.next()){
int id = resultSet.getInt("id");
String account_id = resultSet.getString("account_id");
double balance = resultSet.getDouble("account_balance");
String user_name = resultSet.getString("user_name");
String user_pwd = resultSet.getString("user_pwd");
String idcard = resultSet.getString("user_idcard");
Date oper_time = resultSet.getDate("oper_time");
String gender = resultSet.getString("gender");
account = new Account(id,account_id,balance,user_name,user_pwd,idcard,oper_time,gender.charAt(0));
}
}catch (Exception e){
e.printStackTrace();
}finally {
DBUtil.closeConnection(conn);
}
return account;
}
}
_04SQLIniect_AppServer2
package com.jdbc._04SQLInject;
import com.jdbc.Util.DBUtil;
import java.sql.*;
/**
* 定义一个服务端。
* 提供一个用于检测用户名和密码是否正确的方法。
* 如果正确,返回一个Account对象
* 如果不正确,返回一个null
*
*
* SQL注入: 黑客通过传值的方式,改变SQL的条件结构,比如由两个条件,变成了三个条件
* "....where username ='"+username+"' and password='"+password+"'
*
* password的值,传入了 111111' or '1'='1 因此就会变成
*
* "....where username ='"+username+"' and password='111111' or '1'='1'
*
* 可以看出,多一个or连接 1=1恒成立的条件。
*
* 也就是说Statement可以通过SQL注入的方法将条件的个数改变,换句话说,就是改变了SQL语句的整体结构。
*
* 因此Sun公司有设计了一个Statement的子接口PreparedStatement。
*
* PrepatedStatement这个接口会先将SQL结构提前发送到DBMS中,并且DBMS会将该结构锁死。黑客再次通过SQL注入
* 的方法传入了带有or连接的恒成立的条件,DBMS也只会将其当成一个参数,而不是条件。
*
* 同一个SQL语句或者是相似的SQL语句,PreparedStatemnt只会解析一次。因此效率相对于Statement也高。
*
*/
public class AppServer2 {
/**
*
* @param username 要验证的用户名
* @param password 要验证的密码
* @return
*/
public Account checkLogin(String username, String password) {
Account account = null;
Connection conn = null;
try{
//连接数据库
conn = DBUtil.getConnection();
/**
* 调用prepareStatement(String sql) 先确定SQL的结构,发送到DBMS中
* 使用?来表示占位,用于传参。
*/
String sql = "select * from bank_account where user_name = ? and user_pwd = ? ";
PreparedStatement stat = conn.prepareStatement(sql);
//提前发送完毕后,要继续给?赋值 ?从左到右的索引 是从1开始
stat.setString(1, username);
stat.setString(2, password);
//再次将参数发送到数据库中执行
ResultSet resultSet = stat.executeQuery();
if(resultSet.next()){
int id = resultSet.getInt("id");
String account_id = resultSet.getString("account_id");
double balance = resultSet.getDouble("account_balance");
String user_name = resultSet.getString("user_name");
String user_pwd = resultSet.getString("user_pwd");
String idcard = resultSet.getString("user_idcard");
Date oper_time = resultSet.getDate("oper_time");
String gender = resultSet.getString("gender");
account = new Account(id,account_id,balance,user_name,user_pwd,idcard,oper_time,gender.charAt(0));
}
}catch (Exception e){
e.printStackTrace();
}finally {
DBUtil.closeConnection(conn);
}
return account;
}
}
_05Transfer_BankTransferDemo
package com.jdbc._05Transfer;
import com.jdbc.Util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 演示银行转账业务:
* 两个账号,一个金额
*/
public class BankTransferDemo {
public static void main(String[] args) throws Exception {
boolean flag = transfer("6225113088436225","6225113088436226",10000);
System.out.println("flag:"+flag);
}
/**
*
* @param fromAccount 转出账号
* @param toAccount 转入账号
* @param money 要转出的金额
* @return 成功 true 失败false
*/
public static boolean transfer(String fromAccount, String toAccount, int money) throws Exception {
//第一大步:先校验参数是否合理
if(money < 0){
return false;
}
if(fromAccount == null || toAccount == null
|| fromAccount.trim().length() == 0
|| toAccount.trim().length() == 0){
return false;
}
//验证两个账号是否真实有效,去数据库中验证
Connection conn = DBUtil.getConnection();
String sql = "select * from bank_account where account_id=?";
//先验证转出账号
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, fromAccount);
ResultSet resultSet = ps.executeQuery();
if(!resultSet.next()){
System.out.println("转出账号不存在");
//说明转出账号不存在
return false;
}
//再验证转入账号
PreparedStatement ps2 = conn.prepareStatement(sql);
ps2.setString(1, toAccount);
ResultSet resultSet1 = ps2.executeQuery();
if(!resultSet1.next()){
System.out.println("转入账号不存在");
//说明转入账号不存在
return false;
}
//第二大步: 上述验证都通过了,就可以转账了
//获取转出账号的余额
double fromBalance = resultSet.getDouble("account_balance");
if (fromBalance<money){
System.out.println("余额不足");
return false;
}
//余额充足,可以转出
String Sql2 = "update bank_account set account_balance=? where account_id=?";
PreparedStatement prep = conn.prepareStatement(Sql2);
prep.setDouble(1, fromBalance-money);
prep.setString(2, fromAccount);
prep.executeUpdate();
String str = null;
System.out.println(str.length());
//转入
double toBalance = resultSet1.getDouble("account_balance");
PreparedStatement prep2 = conn.prepareStatement(Sql2);
prep2.setDouble(1, toBalance+money);
prep2.setString(2, toAccount);
prep2.executeUpdate();
DBUtil.closeConnection(conn);
return true;
}
}
Util_DBUtil
package com.jdbc.Util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
/**
* 自定义一个连接、关闭数据库的工具类型。
* 提供一个连接方法: getConnection()
* 提供一个关闭方法: closeConnection(Connection conn)
*/
public class DBUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static {
try {
//使用IO流读取jdbc.properties配置文件
//getResourceAsStream的形参是相对路径,从当前类所在的包中寻找
InputStream inputStream = DBUtil.class
.getClassLoader()
.getResourceAsStream("jdbc.properties");
// 创建Properties对象
Properties prop = new Properties();
//调用集合对象的load方法,读取流中信息
prop.load(inputStream);
// 从对象身上获取相关的键值对,注意传入的key是文件中等号前的名
driverClass = prop.getProperty("driverClass");
url = prop.getProperty("url");
user = prop.getProperty("username");
password = prop.getProperty("password");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static void main(String[] args) {
//调用连接数据库的方法
Connection connection = DBUtil.getConnection();
System.out.println(connection);
//调用关闭数据库的方法
closeConnection(connection);
}
// 连接数据库
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
// 关闭连接
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
jdbc.properties
driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai&useTimezone=true
username=root
password=111111