🚩 Home / java / jdbc.md

JDBC

jdbc 四大参数

    private final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private final String DB_URL = "jdbc:mysql://localhost:3306/linuxBSS?useSSL=false&serverTimezone=UTC";
    private final String USER = "root";
    private final String PASSWORD = "tobenoone";

    Class.forName(JDBC_DRIVER);
    this.connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);

增删改

stmt.executeUpdate(sql);//

stmt.executeQuery(sql);//

结果集光标与元数据

对于resultSet的光标操作:

beforeFirst();
afterLast();
first();
last();
isBeforeFirst();
isAfterLast();
isFirst();
isLast();
next();
previous();
absolute();
relative();
getRow();

元数据

  • getMetaData();返回resultSetMetaData;
  • 获取结果集列数,getcolumnCount();
  • 获取指定列名,getColumnName();

结果集特性:

  • 是否可滚动
  • 是否敏感
  • 是否可更新

PreparedStatement

  • 防sql攻击
  • 提高可读性
  • 提高效率

步骤:

  1. 给出sql模板

  2. Connection给出preparedStatement

    String sql = "select * from table where username = ? and password = ?"; PrepareStatement psstmt = conn.prepareStatement(sql);

  3. 为参数赋值

    psstmt.setString(1, name); // 为第一个问号赋值 psstmt.setString(2, pw); // 为第二个问号赋值

    psstmt.executeQuery(); // 执行

JDBCUtil

/*
 * v1.0
*/
public class MainTest {
    private static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String DB_URL = "jdbc:mysql://39.106.10.62:3306/dbtest?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
    private static String USER_NAME = "root";
    private static String USER_PASSWORD = "xxxxxx";

    public static void main(String[] args) throws Exception{
        Class.forName(JDBC_DRIVER);
        Connection connection = null;
        PreparedStatement pStatement = null;
        ResultSet set = null;
        try {
            connection = DriverManager.getConnection(DB_URL, USER_NAME, USER_PASSWORD);

            String sql = "SELECT * FROM stu WHERE uid > ?";
            pStatement = connection.prepareStatement(sql);
            pStatement.setInt(1, 99);
            set = pStatement.executeQuery();

            while(set.next()) {
                System.out.print(set.getInt("uid"));
                System.out.print(", ");
                System.out.print(set.getString("name"));
                System.out.print(", ");
                System.out.println(set.getInt("age"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (set != null) set.close();
            if (pStatement != null) pStatement.close();
            if (connection != null) connection.close();
        }
     }
}

/*
 * v2.0
*/
public class MainTest {

    public static void main(String[] args) throws Exception{
        InputStream inputStream = MainTest.class.getClassLoader().getResourceAsStream("dbconfig.properties");
        Properties properties = new Properties();
        properties.load(inputStream);

        Class.forName(properties.getProperty("JDBC_DRIVER"));
        Connection connection = null;
        PreparedStatement pStatement = null;
        ResultSet set = null;
        try {
            connection = DriverManager.getConnection(properties.getProperty("DB_URL"),
                    properties.getProperty("USER"), properties.getProperty("PASSWORD"));

            String sql = "SELECT * FROM stu WHERE uid > ?";
            pStatement = connection.prepareStatement(sql);
            pStatement.setInt(1, 99);
            set = pStatement.executeQuery();

            while(set.next()) {
                System.out.print(set.getInt("uid"));
                System.out.print(", ");
                System.out.print(set.getString("name"));
                System.out.print(", ");
                System.out.println(set.getInt("age"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (set != null) set.close();
            if (pStatement != null) pStatement.close();
            if (connection != null) connection.close();
        }
     }
}


dbconfig.properities文件:
JDBC_DRIVER=com.mysql.cj.jdbc.Driver
DB_URL=jdbc:mysql://39.106.10.62:3306/dbtest?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
USER=root
PASSWORD=xxxxxx

面向接口编程

DAO(Data Access Objects)属于数据层操作。

JSP → 工厂模式 → 具体实现类 → 完成数据库操作

DAO设计模式组成部分

  • DAO接口:定义所有的用户操作
  • DAO实实现类:实现DAO接口,在该类通过数据库连接类操作数据
  • DAO工厂类:通过自身静态方法获取实现了实例
  • 数据传递对象:包含属性和表中字段完全对应的类
  • 数据库连接类:通过连接数据库获得连接对象以及关闭数据库

时间类型

Date → java.sql.Date

Time → java.sql.Time

TimeStamp → java.sql.TimeStamp

需要java.util.data/time/timestamp → java.sql.data/time/timestamp

大数据操作

Blob型存储二进制文件。

批处理

pstmt.addBatch();

pstmt.executeBatch();

需要修改连接数据库url的配置参数

事务四大特性(ACID)

  • 原子性,事务操作不可分割
  • 一致性,数据库状态与业务规则保持一致
  • 隔离性,并发操作时不同事务应该隔离开
  • 持久性,事务一旦提交成功,数据操作必须持久化到数据库中

MySQL中的事务

  1. start transcation;
  2. 数据库操作
  3. rollback(回滚到第一步之前) commit(提交事务)

JDBC中的事务

con.setAutoCommit(False);
con.commit();

con.rollback();

四大隔离级别三大并发问题。

dbcp