JDBC

JDBC

• sun公司定义的接口，各个厂商需要自己实现这个接口，提供数据库驱动jar包，真正执行代码的是驱动jar包中的实现类

步骤：

1. 导入jar包

2. 注册驱动

3. 获取数据库链接对象

4. 定义sql

5. 获取执行对象的statement

6. 执行sql，接收返回结果

7. 处理结果

8. 释放资源

   public static void main(String[] args) throws Exception {
   //注册驱动
   Class.forName("com.mysql.jdbc.Driver");
   //获取数据库连接对象
   Connection connection= DriverManager.getConnection("jdbc；mysql://3306/数据库名","用户名","密码");
   //定义sql语句
   String sql="update account set balance = 1000";
   //获取sql的对象
   Statement statement=connection.createStatement();
   //执行sql语句
   int count=statement.executeUpdate(sql);
   //处理结果
   System.out.println(count);
   //释放资源
   statement.close();
   connection.close();

1. DriverManager:驱动管理对象

注册驱动

• 思考:为什么注册的时候用的是"com.mysql.jdbc.Driver"？

  • 因为在数据库连接驱动的jar包目录下com.mysql.jdbc.Driver中，有一个static代码块，随着jar包的加载而运行，这个static代码块是java.sql.DriverManager.registerDriver，用来进行注册驱动(mysql 5.x版本之后免注册驱动，因为jar目录下已经有配置文件写入了)

获取数据库连接：

• 方法：static Connection getConnection（String url,String user,String password;

2. Connection:数据库连接对象

• 功能：

1.获取执行sql对象

• Statement createStatement（）
• PrepareStatement prepareStatement（String sql）;

2.管理事务：

• 开启事务：
• 提交事务：
• 回滚事务：

3.Statement：执行sql的 对象

• 执行SQL语句

4.ResultSet：结果集对象，封装结果

• Boolean next（）：游标向下一位，判断是否有数据，是否是最后一行
• getxxx(参数)：获取数据

  • xxx代表参数类型

  • 参数：
    1.int: 代表列的编号，从一开始，意思是想查第几列数据，如：getString（1）
    2.String: 代表列的名称，如getDouble（"balance"）

    public class test2 {
    public static void main(String[] args) throws Exception {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    {

    try {
    String sql = "select * from dept";
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "123456");
    statement = connection.createStatement();

    //resultSet获取数据方式
    resultSet = statement.executeQuery(sql);
    resultSet.next(); //如果不next，获取的就是各字段名
    int id=resultSet.getInt(1);
    String name=resultSet.getString("dname");
    String loc=resultSet.getString("loc");
    System.out.println(id+" "+name+" "+loc);

    } catch (SQLException throwables) {
    throwables.printStackTrace();
    } finally {
    connection.close();
    statement.close();

    }
    }

    }
    }

以上Result代码集在获取数据时不知道是否是最后一行，所以应该做如下改进

while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString("dname");
String loc = resultSet.getString("loc");
System.out.println(id + " " + name + " " + loc);
}

测试代码

实体类

package cn.itcast.reflect.demo;

public class dept {
private int id;
private String dname;
private String loc;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getDname() {
return dname;
}

public void setDname(String dname) {
this.dname = dname;
}

public String getLoc() {
return loc;
}

public void setLoc(String loc) {
this.loc = loc;
}

@Override
public String toString() {
return "dept{" +
"id=" + id +
", dname='" + dname + ''' +
", lco='" + loc + ''' +
'}';
}
}

主类

package cn.itcast.reflect.demo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class test {
public static void main(String[] args) {
List deptList=new test().findAll();
System.out.println(deptList);
}

public List findAll(){
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
dept dept1=null;
List depts=null;
try {
// Class.forName("com.mysql.cj.jdbc.Driver");
connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/db3","root","123456");
String sql="select * from dept";
statement=connection.createStatement();
resultSet=statement.executeQuery(sql);
depts=new ArrayList<>();
while (resultSet.next()){
dept1=new dept();
dept1.setId(resultSet.getInt("id"));
dept1.setDname(resultSet.getString("dname"));
dept1.setLoc(resultSet.getString("loc"));
depts.add(dept1);
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} if (connection!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}

return depts;
}
}

利用JDBC工具类配置

• 配置文件jdbc.properties

  url=jdbc:mysql://localhost:3306/db3
  user=root
  password=123456
  driver=com.mysql.cj.jdbc.Driver

• JDBC工具类

  package cn.itcast.reflect.demo;

  import java.io.FileReader;
  import java.io.IOException;
  import java.net.URL;
  import java.sql.*;
  import java.util.Properties;

  public class JDBCUtils {
  private static String password;
  private static String user;
  private static String url;
  private static String driver;

  static {
  try {
  //创建Properties集合类（键值对）
  Properties properties = new Properties();
  //获取src路径下文件，Classloader类加载器
  ClassLoader classLoader = JDBCUtils.class.getClassLoader();
  URL resource = classLoader.getResource("jdbc.properties");
  String path = resource.getPath();
  //加载文件
  properties.load(new FileReader(path));
  //获取数据
  user = properties.getProperty("user");
  password = properties.getProperty("password");
  url = properties.getProperty("url");
  driver = properties.getProperty("driver");
  //注册驱动
  try {
  Class.forName(driver);
  } catch (ClassNotFoundException e) {
  e.printStackTrace();
  }
  } catch (IOException e) {
  e.printStackTrace();
  }
  }

  /*
  获取连接和连接对象
  * */
  public static Connection getConnection() throws SQLException {
  return DriverManager.getConnection(url, user, password);
  }

  /*
  * 释放资源
  * */
  public static void close(Statement statement, Connection connection) {
  //释放statement
  if (statement != null) {
  try {
  statement.close();
  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }
  }

  //释放connection
  if (connection != null) {
  try {
  connection.close();
  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }
  }
  }

  public static void close(ResultSet resultSet, Statement statement, Connection connection) {
  //释放result
  if (resultSet != null) {
  try {
  resultSet.close();
  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }
  }
  //释放statement
  if (statement != null) {
  try {
  statement.close();
  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }
  }
  //释放connection
  if (connection != null) {
  try {
  connection.close();
  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }
  }
  }
  }

• 主方法

  package cn.itcast.reflect.demo;

  import java.sql.*;
  import java.util.ArrayList;
  import java.util.List;

  public class testUtils {

  public static void main(String[] args) {
  List deptList = new test().findAll();
  System.out.println(deptList);
  }

  public List findAll() {
  Connection connection = null; //连接对象
  Statement statement = null; //执行对象
  ResultSet resultSet = null; //结果集
  dept dept1 = null; //实体类对象
  List depts = new ArrayList<>(); //数据返回的集合
  try {
  connection = JDBCUtils.getConnection(); //获取JDBC工具类连接
  String sql = "select * from dept"; //sql语句
  statement = connection.createStatement(); //connection获取执行sql的对象
  resultSet = statement.executeQuery(sql); //执行sql的对象statement执行sql语句，返回给结果集
  while (resultSet.next()) { //当结果集中还有下一条数据时
  dept1 = new dept();
  dept1.setId(resultSet.getInt("id"));
  dept1.setDname(resultSet.getString("dname"));
  dept1.setLoc(resultSet.getString("loc"));
  depts.add(dept1);
  }
  } catch (Exception e) {
  e.printStackTrace();
  } finally {
  JDBCUtils.close(resultSet, statement, connection);
  }

  return depts;
  }
  }

登录练习

新建数据库user内有username和password

• 配置文件

  url=jdbc:mysql://localhost:3306/user
  user=root
  password=123456
  driver=com.mysql.cj.jdbc.Driver

• JDBC工具类和上面的一样

• 主方法(该练习有sql注入风险)

  import cn.itcast.reflect.demo.JDBCUtils;

  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.Scanner;

  public class user {
  public static void main(String[] args) {
  Scanner scanner=new Scanner(System.in);
  System.out.println("请输入用户名：");
  String s1=scanner.nextLine();
  System.out.println("请输入密码：");
  String s2=scanner.nextLine();
  boolean b=new user().login(s1,s2);
  if (b){
  System.out.println("登录成功！");
  }else System.out.println("登陆失败！");
  }

  public boolean login(String username, String password) {
  if (username == null || password == null) {
  return false;
  }
  Connection connection = null;
  Statement statement = null;
  ResultSet resultSet=null;
  try {
  String sql = "select * from users where username='" + username + "'and password= '"+ password+"' ";
  connection = JDBCUtils.getConnection();
  statement = connection.createStatement();
  resultSet=statement.executeQuery(sql);
  return resultSet.next();
  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }
  finally {
  JDBCUtils.close(resultSet,statement,connection);
  }
  return false;
  }
  }

5.PreparedStatement：执行sql的对象

用来解决sql注入问题，参数使用 ？ 占位符，如下

String sql = "select * from users where username=? and password=?";

获取执行sql的对象

connect.prepareStatement(sql)
给？赋值

prepareStatement.setString(1，username) // 1 为占位符？的位置，String为数据类型

使用PrepareStatement修改数据库

• 主方法（其他类略有修改，参考上文）
  package cn.itcast.reflect;

  import cn.itcast.reflect.demo.JDBCUtils;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;

  public class acount {
  public static void main(String[] args) {
  Connection connection=null;
  PreparedStatement preparedStatement1=null;
  PreparedStatement preparedStatement2=null;
  //用户1
  String sql1="Update acount set bouns = ? where id=?";
  //用户2
  String sql2="Update acount set bouns = ? where id=?";
  try {
  connection= JDBCUtils.getConnection();
  preparedStatement1=connection.prepareStatement(sql1);
  preparedStatement2=connection.prepareStatement(sql2);
  //设置参数
  preparedStatement1.setInt(1,500);
  preparedStatement1.setInt(2,1);

  preparedStatement2.setInt(1,500);
  preparedStatement2.setInt(2,2);

  preparedStatement1.execute();
  preparedStatement2.execute();

  } catch (SQLException throwables) {
  throwables.printStackTrace();
  }finally {
  JDBCUtils.close(preparedStatement1);
  JDBCUtils.close(preparedStatement2);
  }
  }
  }

JDBC事务回滚

package cn.itcast.reflect;

import cn.itcast.reflect.demo.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class acount {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
//用户1
String sql1 = "Update acount set bouns = ? where id=?";
//用户2
String sql2 = "Update acount set bouns = ? where id=?";
try {
connection = JDBCUtils.getConnection();
//开启事务
connection.setAutoCommit(false);
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement2 = connection.prepareStatement(sql2);
//设置参数
preparedStatement1.setInt(1, 500);
preparedStatement1.setInt(2, 1);

preparedStatement2.setInt(1, 500);
preparedStatement2.setInt(2, 2);

preparedStatement1.execute();
preparedStatement2.execute();
//提交事务
connection.commit();
} catch (Exception throwables) {
//事务回滚
throwables.printStackTrace();
try {
if (connection != null)
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
JDBCUtils.close(preparedStatement1);
JDBCUtils.close(preparedStatement2);
}
}
}