模拟淘宝登录
package com.bjsxt.jdbc2;
import com.bjsxt.entity.User;
import entity.Emp;
import java.nio.file.attribute.UserDefinedFileAttributeView;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
/**
* 模拟淘宝登录的功能
*
*
*/
public class TestLogin {
/**
* 前台
* @param args
*/
public static void main(String[] args) {
//1.输入用户名和密码
Scanner input = new Scanner(System.in);
System.out.println("请输入用户名");
String userId = input.next();
System.out.println("请输入密码");
String password = input.next();
//2.调用后台判断登录是否成功并返回结果给前台
User user = login(userId,password);
//3.在前台输入结果
if(user == null){
System.out.println("登录失败");
}else{
System.out.println("欢迎您:"+user.getRealName());
}
}
public static User login(String userId,String pwd){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
User user2 = null; //默认登录失败
try{
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
//1.加载驱动
Class.forName(driver);
//2.和数据库建立连接
conn = DriverManager.getConnection(url, user, password);
//3.创建一个SQL命令发送器(手枪)
stmt = conn.createStatement();
//4.准备一个SQL命令,并使用SQL命令发送器发送过去,并返回结果
String sql = "select * from t_user where userid = '"+userId+
"' and password='"+pwd+"'";
System.out.println(sql);
rs = stmt.executeQuery(sql);
//5.处理结果(将ResultSet的数据封装到一个List中)
if(rs.next()){
//获取当前行各个字段的值
//String userId = rs.getString("userid");
String realName = rs.getString("REALNAME");
//String password = rs.getString("password");
double money = rs.getDouble("money");
//将当前行各个字段的值封装到一个Emp对象中
user2 = new User(userId,realName,pwd,money);
}
}catch (SQLException e){
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
//6.关闭资源
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt != null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return user2;
}
}
缺点:有sql注入风险
解决:使用PreparedStatement
package com.bjsxt.jdbc2;
import com.bjsxt.entity.User;
import java.sql.*;
import java.util.Scanner;
/**
* 模拟淘宝登录的功能
*
* 关系
* public interface PreparedStatement extends Statement
*
*
* PreparedStatement优点
* 1.安全性高 避免SQL注入 因为不是基于字符串拼接
* 2.代码不繁琐,可读性高。不用进行复杂的字符串拼接
* 3.性能高
*
*/
public class TestLogin2 {
/**
* 前台
* @param args
*/
public static void main(String[] args) {
//1.输入用户名和密码
Scanner input = new Scanner(System.in);
System.out.println("请输入用户名");
String userId = input.next();
System.out.println("请输入密码");
String password = input.next();
//2.调用后台判断登录是否成功并返回结果给前台
User user = login(userId,password);
//3.在前台输入结果
if(user == null){
System.out.println("登录失败");
}else{
System.out.println("欢迎您:"+user.getRealName());
}
}
public static User login(String userId,String pwd){
Connection conn = null;
//Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
User user2 = null; //默认登录失败
try{
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
//1.加载驱动
Class.forName(driver);
//2.和数据库建立连接
conn = DriverManager.getConnection(url, user, password);
//3.创建一个SQL命令发送器(手枪)
//stmt = conn.createStatement();
String sql = "select * from t_user where userid =? and password=?";//? 占位符
pstmt = conn.prepareStatement(sql);
//4.准备一个SQL命令,并使用SQL命令发送器发送过去,并返回结果
//System.out.println(sql);
//rs = stmt.executeQuery(sql);
pstmt.setString(1, userId);// 从1开始
pstmt.setString(2, pwd);
rs = pstmt.executeQuery();
//5.处理结果(将ResultSet的数据封装到一个List中)
if(rs.next()){
//获取当前行各个字段的值
//String userId = rs.getString("userid");
String realName = rs.getString("REALNAME");
//String password = rs.getString("password");
double money = rs.getDouble("money");
//将当前行各个字段的值封装到一个Emp对象中
user2 = new User(userId,realName,pwd,money);
}
}catch (SQLException e){
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
//6.关闭资源
try {
if(rs != null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(pstmt != null){
pstmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return user2;
}
}
事务管理
package com.bjsxt.jdbc2;
import java.sql.*;
/**
* 银行转账
*/
public class TestTransaction {
public static void main(String[] args){
Connection conn = null;
CallableStatement cst;
try{
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user = "root";
String password = "root";
//1.加载驱动
//Class.forName(driver);
//2.和数据库建立连接
conn = DriverManager.getConnection(url, user, password);
//3.创建一个SQL命令发送器(手枪)
Statement stmt = conn.createStatement();
//设置事务不再自动提交
conn.setAutoCommit(false);
//4.准备一个SQL命令,并使用SQL命令发送器发送过去,并返回结果
stmt.executeUpdate("update t_user set money = money - 2000 where userid = 'lisi'");
//stmt.executeUpdate("update t_user set money = money1 + 2000 where userid = 'zhangsan'");
stmt.executeUpdate("update t_user set money = money + 2000 where userid = 'zhangsan'");
//事务成功结束
conn.commit();
}catch (SQLException e){
e.printStackTrace();
//事务失败结束
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
//6.关闭资源
// stmt.close();
// conn.close();
}
}
}