JDBC的本质
JDBC是一套接口,数据库厂商实现类,降耦合,提高拓展性
创建不同的JDBC对象可通过反射
实现类得去数据库官网下载后jar包导入到环境变量classpath中
.;路径
JDBC编程的6个步骤
package jdbc_study;
/*
* jdbc mysql数据库
*/
import java.sql.Connection;
import java.sql.DriverManager;
import com.mysql.jdbc.Statement;
public class testJDBC02 {
public static void main(String[] args) throws Exception{
//注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//获取连接
String url = "jdbc:mysql://127.0.0.1:3306/xiaoqiu?useSSL=false";//ip地址可更改,连接别人的数据库
/*
* jdbc:mysql://协议
* 127.0.0.1 ip地址
* 3306端口号
* xiaoqiu,mysql数据库库名
*/
String user= "root";
String password="" ;
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("mysql数据库已连接"+conn);
//获取数据库操作对象
java.sql.Statement statement=conn.createStatement();
String sql ="delete from t_xiaoqiu where name='xiaoqiu'";
//执行sql语句
int count=statement.executeUpdate(sql);//专门执行DML语句的方法
System.out.println(count==1?"插入成功":"插入失败");//count表示形象表中记录的条数
if (statement!=null) {
statement.close();
}
if (conn!=null) {
conn.close();
}
}
}
注册驱动常用方法
Class.forName("com.mysql.jdbc.Driver");//forname方法会加载这个类,执行static代码块中的内容,因为是字符串可以写到配置文件中
try {
Connection conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/xiaoqiu?useSSL=false","root","");
System.out.println(conn);
处理增删改
package jdbc_study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.ResourceBundle;
/*
* 从配置文件中读取
*/
public class testJDBC05 {
public static void main(String[] args) {
ResourceBundle bundle =ResourceBundle.getBundle("jdbc_study/JDBC");
String driver=bundle.getString("driver");
String url=bundle.getString("url");
String user=bundle.getString("user");
String password=bundle.getString("password");
try {
Class.forName(driver);
Statement statement =null;
Connection conn =null;
try {
conn =DriverManager.getConnection(url,user,password);
statement = conn.createStatement();
String sql="insert t_xiaoqiu(name,sex) values('xiaoqiu',1)";
statement.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
通过调用statement.executeupdate(sql)方法可以执行,返回int型数据表示该sql语句对表内容条数的变化
Insert update delete
通过调用statement.executequery(sql)返回一个resultset对象,通过调用resultset.getString方法可以得到每一行的字段数据
Resultset.next()光标判断下一行是否为空,通过while循环来遍历整个查询结果
JDBC->的配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/你的库名?useSSL=false
user=root
password=******
处理查询结果集
package jdbc_study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
/*
* 处理查询结果集
* resultset通过statement.executequery(sql);
*/
public class testJDBC06 {
public static void main(String[] args) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc_study/JDBC");
String driver = bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
try {
Class.forName(driver);
conn=DriverManager.getConnection(url, user, password);
statement=conn.createStatement();
String sql = "select * from emp";
rs=statement.executeQuery(sql);
while (rs.next()) {
/*
* 除了用String类型取出之外,还能用特定的类型取出
*/
//用列的下标
String empno=rs.getString(1);
String ename=rs.getString(2);
String job=rs.getString(3);
System.out.println(empno+"|"+ename+"|"+job);
/*
* 以列的名字查询
* 如果在select语句中对字段的名称重命名了需要传入重命名后的字段名
*/
// String empno1=rs.getString("empno");
// String ename1=rs.getString("ename");
// String job1=rs.getString("job");
// System.out.println(empno1+"|"+ename1+"|"+job1);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
SQL注入
package jdbc_study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
/*
* 写一个登录系统
* 请输入用户名
xiaoqiu
请输入密码
xq' or '1'='1
*
*/
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;
import com.mysql.jdbc.Statement;
public class user_login {
public static void main(String[] args) {
user_login login=new user_login();
Map map=login.InitLogin();
System.out.println(login.loginsuccess(map));
}
private Map<String,String> InitLogin() {//初始化登录界面
Map map = new HashMap<>();
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String loginName=sc.nextLine();
System.out.println("请输入密码");
String loginPwd=sc.nextLine();
map.put("loginName",loginName);
map.put("loginPwd",loginPwd);
return map;
}
private boolean loginsuccess(Map<String, String> map) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc_study/JDBC");
String driver= bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn=null;
ResultSet rs=null;
java.sql.Statement statement=null;
boolean loginSuccess=false;
try {
String loginName = map.get("loginName");
String loginPwd = map.get("loginPwd");
conn = DriverManager.getConnection(url,user,password);
statement=conn.createStatement();
String sql="select * from t_user where loginName='"+loginName+"'"+"and loginPwd='"+loginPwd+"'";
rs=statement.executeQuery(sql);
if(rs.next()) {
loginSuccess=true;
}
return loginSuccess;
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return loginSuccess;
}
}
解决SQL注入
使用preparedStatement
package jdbc_study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Scanner;
/*
* 解决sql注入
*/
public class user_login2 {
public static void main(String[] args) {
user_login2 login=new user_login2();
Map map=login.InitLogin();
System.out.println(login.loginsuccess(map));
}
private Map<String,String> InitLogin() {//初始化登录界面
Map map = new HashMap<>();
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String loginName=sc.nextLine();
System.out.println("请输入密码");
String loginPwd=sc.nextLine();
map.put("loginName",loginName);
map.put("loginPwd",loginPwd);
System.out.println(map);
return map;
}
private boolean loginsuccess(Map<String, String> map) {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc_study/JDBC");
String driver= bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
Connection conn=null;
ResultSet rs=null;
java.sql.PreparedStatement ps=null;
boolean loginSuccess=false;
try {
String loginName = map.get("loginName");
String loginPwd = map.get("loginPwd");
conn = DriverManager.getConnection(url,user,password);
String sql="select * from t_user where loginName= ? and loginPwd= ?";
//select * from t_user where loginName='xiaoqiu' and loginPwd='123';
ps=conn.prepareStatement(sql);
ps.setString(1,loginName);
ps.setString(2,loginPwd);
rs=ps.executeQuery();
if (rs.next()) {
loginSuccess=true;
}
return loginSuccess;
} catch (SQLException e) {
e.printStackTrace();
}finally {
if (rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (ps!=null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return loginSuccess;
}
}
JDBC处理事务
在JDBC中,默认执行一条DML语句,自动提交
package jdbc_study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ResourceBundle;
/*
* 测试事务
*/
public class testJDBC09 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResourceBundle bundle = ResourceBundle.getBundle("jdbc_study/JDBC");
String driver= bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,user,password);
String sql ="update t_act set balance=? where actno=?";
ps=conn.prepareStatement(sql);
ps.setDouble(1,0);
ps.setString(2,"111");
int count=ps.executeUpdate();
ps.setDouble(1,30000);
ps.setString(2,"222");
count+=ps.executeUpdate();
System.out.println(count==2?"转账成功":"转账失败");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
如果一条DML语句执行成功后出现异常,下面的语句就都不能执行,但数据已经发生了更改,需要开启事务
package jdbc_study;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ResourceBundle;
/*
* 测试事务
*/
public class testJDBC09 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResourceBundle bundle = ResourceBundle.getBundle("jdbc_study/JDBC");
String driver= bundle.getString("driver");
String url = bundle.getString("url");
String user = bundle.getString("user");
String password = bundle.getString("password");
try {
Class.forName(driver);
conn=DriverManager.getConnection(url,user,password);
conn.setAutoCommit(false);//关闭自动提交机制
String sql ="update t_act set balance=? where actno=?";
ps=conn.prepareStatement(sql);
ps.setDouble(1,0);
ps.setString(2,"111");
int count=ps.executeUpdate();
ps.setDouble(1,30000);
ps.setString(2,"222");
count+=ps.executeUpdate();
System.out.println(count==2?"转账成功":"转账失败");
conn.commit();//提交
} catch (Exception e) {
try {
conn.rollback();//如果出现异常,进行回滚
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
乐观锁和悲观锁