【java web】JDBC详解

JDBC

快速入门

public static void main(String[] args) throws Exception {
    // 1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");

    // 2.获取连接
    String url = "jdbc:mysql://127.0.0.1:3306/db1?useSSL=false";
    String username = "root";
    String password = "2ycf4jd";
    Connection conn = DriverManager.getConnection(url, username, password);

    // 3.定义sql
    String sql = "update daqi set name = '达九' where id = 1123";

    // 4.获取执行sql的对象 Statement
    Statement stmt = conn.createStatement();

    // 5.执行sql
    int count = stmt.executeUpdate(sql);

    // 6.处理结果
    System.out.println(count);

    // 7.释放资源
    stmt.close();
    conn.close();
}

Connection

1.获取执行对象

2.管理事务（代码如下）

try {
    // 开启事务
    conn.setAutoCommit(false);
    
    // 5.执行sql
    int count1 = stmt.executeUpdate(sql1);

    // 6.处理结果
    System.out.println(count1);

    // 5.执行sql
    int count2 = stmt.executeUpdate(sql2);

    // 6.处理结果
    System.out.println(count2);

    // 提交事务
    conn.commit();
} catch (Exception e) {
    // 回滚事务
    conn.rollback();
    e.printStackTrace();
}

ResultSet

String sql = "select * from yuan_gong";

Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery(sql);

while (rs.next()) {
    int id = rs.getInt(1);
    String ename = rs.getString(2);
    /*
    int id = rs.getInt("id");
    String ename = rs.getString("ename");
    
    */

 	System.out.println(id);
    System.out.println(ename);
}
rs.close();
stat.close();
conn.close();

PreparedStatement

预编译SQL语句并执行，预防SQL注入问题（将敏感字符进行转义）

// useServerPrepStmts=true开启预编译
String url = "jdbc:mysql:///db1?useServerPrepStmts=true";

具体

String sql = "select * from yuan_gong where id = ? and ename = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);
// 给？ 赋值
pstmt.setInt(1,1);
pstmt.setString(2,"张三");

ResultSet rs = pstmt.executeQuery();


if (rs.next()) {
    System.out.println("登录成功~");
} else {
    System.out.println("登录失败~");
}
rs.close();
pstmt.close();
conn.close();