JDBC 创建数据库实例

//1、注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、连接数据库
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306", "root", "root");
//3、创建数据库
Statement stmt = conn.createStatement();
String sql = "create database t_student";
stmt.executeUpdate(sql);
stmt.close();
conn.close();

JDBC 选择数据库实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
conn.close();

JDBC 删除数据库实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "drop database t_students";
stmt.executeUpdate(sql);
stmt.close();
conn.close();

JDBC 创建表实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "create table registration " +
        "(id integer not NULL, " +
        " first varchar(255), " +
        " last varchar(255), " +
        " age integer, " +
        " primary key ( id ))";
stmt.executeUpdate(sql);
conn.close();

JDBC 删除表实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "drop table registration";
stmt.executeUpdate(sql);
conn.close();

JDBC 插入记录实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "insert into registration values (100, 'Zara', 'Ali', 18)";
stmt.executeUpdate(sql);
sql = "insert into registration values (101, 'Mahnaz', 'Fatma', 25)";
stmt.executeUpdate(sql);
sql = "insert into registration values (102, 'Zaid', 'Khan', 30)";
stmt.executeUpdate(sql);
sql = "insert into registration values(103, 'Sumit', 'Mittal', 28)";
stmt.executeUpdate(sql);
conn.close();

JDBC 查询记录实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "select id, first, last, age from registration";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
    int id  = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
rs.close();
conn.close();

JDBC 更新记录实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "update registration set age = 30 where id in (100, 101)";
stmt.executeUpdate(sql);
sql = "select id, first, last, age from registration";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
    int id  = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
rs.close();
conn.close();

JDBC 删除记录实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "delete from registration where id = 101";
stmt.executeUpdate(sql);
sql = "select id, first, last, age from registration";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
    int id  = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
rs.close();
conn.close();

JDBC WHERE 子句实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "SELECT id, first, last, age FROM Registration";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
sql = "SELECT id, first, last, age FROM Registration WHERE id >= 101 ";
rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    //Display values
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
rs.close();
conn.close();

JDBC LIKE 子句实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "select id, first, last, age from registration";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
sql = "select id, first, last, age from registration  where first like '%za%' ";
rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
rs.close();
conn.close();

JDBC 排序实例

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t_students", "root", "root");
Statement stmt = conn.createStatement();
String sql = "select id, first, last, age from registration  order by first asc";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
sql = "select id, first, last, age from registration  order by first desc";
rs = stmt.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    int age = rs.getInt("age");
    String first = rs.getString("first");
    String last = rs.getString("last");
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);
    System.out.print(", First: " + first);
    System.out.println(", Last: " + last);
}
rs.close();
conn.close();