JDBC
JDBC基本流程
-
加载驱动(选择数据库)
Class.forName("oracle.jdbc.driver.OracleDriver"); -
获取连接(与数据库建立连接)
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE",
"SCOTT",
"TIGER"
); -
准备SQL
String sql = "SELECT * FROM DEPT"; -
构建处理块(封装发送SQL)
Statement state = conn.createStatement(); -
发送SQL,得到结果
ResultSet result = state.executeQuery(sql); -
处理结果
while(result.next()){
int deptno = result.getInt(1);
String dname = result.getString("dname");
String loc = result.getString(3);
System.out.println(deptno+"-->"+dname+"-->"+loc);
} -
连接关闭
conn.close();
JDBC基本流程优化
注意：首先要在src下创建配置文件（properties文件）写入数据库的参数信息
package com.yjxxt.jdbc;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/*
JDBC基本流程优化
1.异常的捕获
2.软编码方式定义数据库的参数信息
*/
public class Class002_JDBC {
public static void main(String[] args) {
Properties pro = new Properties();
try {
pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
//1.加载驱动(选择数据库)
try {
Class.forName(pro.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.获取连接(与数据库建立连接)
Connection conn = null;
Statement state = null;
ResultSet result = null;
try {
conn = DriverManager.getConnection(
pro.getProperty("url"),
pro.getProperty("username"),
pro.getProperty("password")
);
//3.准备SQL
String sql = "SELECT * FROM DEPT";
//4.构建处理块(封装发送SQL)
state = conn.createStatement();
//5.发送SQL,得到结果
result = state.executeQuery(sql);
//6.处理结果
while(result.next()){
int deptno = result.getInt(1);
String dname = result.getString("dname");
String loc = result.getString(3);
System.out.println(deptno+"-->"+dname+"-->"+loc);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//7.关闭
if(result!=null){
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(state!=null){
try {
state.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
案例
用户基本操作:
用户注册 insert 用户登录 select 根据用户名与密码同时查找,找到结果返回true,没找到结果返回false 根据用户名查找,找到密码与用户号输入的密码比较是否相等,返回true|false 修改用户数据 update 注销用户 delete 注意: 事务默认自动提交
public class Class003_User {
public static void main(String[] args) {
//注册
System.out.println(reg("zhangsan","1234")?"成功":"失败");
//登录
System.out.println(login("zhangsan","1234")?"成功":"失败");;
}
注意：在要提前封装工具类
-
加载驱动
-
获取连接
-
资源关闭
public class JDBCUtils {
private static Properties pro = new Properties();
static{
//加载流
try {
pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
//加载驱动
try {
Class.forName(pro.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
Connection conn = null;
conn = DriverManager.getConnection(
pro.getProperty("url"),
pro.getProperty("username"),
pro.getProperty("password")
);
return conn;
}
//关闭资源
public static void close(Connection conn, Statement state){
close(conn,state,null);
}
public static void close(Connection conn, Statement state, ResultSet result){
if(result!=null){
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(state!=null){
try {
state.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
用户注册
public static boolean reg(String username,String password){
boolean flag = false;
Connection conn = null;
Statement state = null;
//1.获取连接
try {
conn = JDBCUtils.getConnection();
//2.准备sql
String sql = "insert into t_user values('"+username+"',"+password+")";
//3.封装处理块
state = conn.createStatement();
//4.执行,得到结果
int rows = state.executeUpdate(sql);
//5.处理结果
if(rows>0){
flag = true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//6.关闭资源
JDBCUtils.close(conn,state);
}
//7.返回结果
return flag;
}
用户登录
-
使用静态处理块
public static boolean login(String username,String password){
boolean flag = false;
Connection conn = null;
Statement state = null;
ResultSet result = null;
//1.获取连接
try {
conn = JDBCUtils.getConnection();
//2.准备sql
String sql = "select * from t_user where username='"+username+"' and password = "+password;
//3.封装处理块
state = conn.createStatement();
//4.执行,得到结果
result = state.executeQuery(sql);
//5.处理结果
if(result.next()){
flag = true;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//6.关闭资源
JDBCUtils.close(conn,state,result);
}
//7.返回结果
return flag;
} -
使用预处理块
public static boolean login(String username,String password){
boolean flag = false;
Connection conn = null;
PreparedStatement state = null;
ResultSet result = null;
//1.获取连接
try {
conn = JDBCUtils.getConnection();
//2.准备sql
String sql = "select * from t_user where username=? and password =?";
//3.构建预处理块
state = conn.prepareStatement(sql);
//4.为?赋值
state.setString(1,username);
state.setObject(2,password);
//5.执行,得到结果
result = state.executeQuery();
//5.处理结果
if(result.next()){
flag = true;
}
}catch(SQLSyntaxErrorException e){
System.out.println("遇到SQL注入了....");
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//6.关闭资源
JDBCUtils.close(conn,state,result);
}
//7.返回结果
return flag;
}
静态块与预处理块处理sql语句的区别：
-
静态块：将传入参数与sql语句拼接成一个完整的sql语句，传入到数据库中执行，再此期间参数有可能是条件语句，与sql语句拼接会影响结果。
-
预处理块：
-
提高效率，只需加载一次，之后直接传入参数即可
-
防sql注入，因为SQL语句在程序运行前已经进行了预编译，之后的传入的参数不会影响sql语句，无论传入参数是否为判断语句都不会影响sql语句。
-
转账操作
转账操作 A --> SAL-500 B --> SAL+500 注意：需要控制在同一个事务中,设置手动提交,校验之后提交或者回滚
public class Class004_Transfer {
public static void main(String[] args) {
System.out.println(transfer());;
}
//预处理块 PreparedStatement
public static boolean transfer(){
boolean flag = false;
Connection conn = null;
PreparedStatement state1 = null;
PreparedStatement state2 = null;
//1.获取连接
try {
conn = JDBCUtils.getConnection();
//设置手动提交
conn.setAutoCommit(false);
//2.准备sql
String sql1 = "update emp set sal=sal-500 where empno =7499";
String sql2 = "update emp set sal=sal+500 where empno =7369";
//3.构建预处理块
state1 = conn.prepareStatement(sql1);
state2 = conn.prepareStatement(sql2);
//4.为?赋值
//5.执行,得到结果
int rows1 = state1.executeUpdate();
int rows2 = state2.executeUpdate();
//5.处理结果
if(rows1>0 && rows2>0){
flag = true;
conn.commit();
}else{
conn.rollback();
}
}catch(SQLSyntaxErrorException e){
System.out.println("遇到SQL注入了....");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//7.返回结果
return flag;
}
}
浙公网安备 33010602011771号