Skip to content

JDBC 基础知识

JDBC简介

JDBC（Java Database Connectivity）是Java官方提供的一套数据库连接标准API，定义在java.sql包中，用于让Java程序统一连接MySQL、Oracle等关系型数据库，执行SQL语句并处理查询结果，它由接口规范和数据库厂商实现的驱动组成，核心包含Connection、Statement、PreparedStatement、ResultSet等对象，是Java访问数据库最基础、最常用的技术。

数据库编程

ODBC

ODBC

JDBC

JDBC

驱动类型

驱动类型

JDBC 体系结构

JDBC体系结构

JDBC类结构

JDBC类结构

接口名 说明
Connection 此接口表示与数据的连接
PreparedStatement 此接口用于执行预编译的 SQL 语句
ResultSet 此接口表示了查询出来的数据库数据结果集
Statement 此接口用于执行 SQL 语句并将数据检索到 ResultSet 中
类名 说明
DriverManager 此类用于加载和卸载各种驱动程序并建立与数据库的连接
Date 此类包含将 SQL 日期格式转换成 Java 日期格式的各种方法
Time 此类用于表示时间
TimeStamp 此类通过添加纳秒字段为时间提供更高的精确度
ExceptionTest.java
1
2
3
4
5
6
7
8
9
public void TestException() {
  try {
    foobar();
  } catch (SQLException ex) {
    System.out.println("已捕获一个 SQLException 异常!");
    System.out.println("消息: " + ex.getMessage());
    System.out.println("错误代码: " + ex.getErrorCode());
  }
}
源代码
ExceptionTest.java
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
import java.sql.SQLException;

public class ExceptionTest {
  /**
   * fooBar
   */
  public void foobar() throws SQLException {
    throw new SQLException("刚引发了一个 SQLException");
  }

  public void TestException() {
    try {
      foobar();
    } catch (SQLException ex) {
      System.out.println("已捕获一个 SQLException 异常!");
      System.out.println("消息: " + ex.getMessage());
      System.out.println("错误代码: " + ex.getErrorCode());
    }
  }

  public static void main(String[] args) {
    ExceptionTest o = new ExceptionTest();
    o.TestException();
  }
}

JDBC步骤

JDBC步骤

JDBC访问Sqlite

驱动：

连接参数：

  • 驱动：org.sqlite.JDBC

  • 连接字符串：jdbc:sqlite:test.db

示例:学生选课系统

student-er

初始化表

xust.demo.stu.StudentDao
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
 * 初始化数据库
 */
public void init() {
  Connection con = null;
  PreparedStatement pstmt = null;
  String sql = "create table Student(" +
    "no TEXT primary key," +
    "name TEXT not null," +
    "gender TEXT," +
    "age INTEGER," +
    "dept TEXT" +
    ");";

  try {
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:demo.db");
    pstmt = con.prepareStatement(sql);
    pstmt.execute();
    System.out.println("Table created successfully");
    pstmt.close();
    con.close();
  } catch (Exception e) {
    e.printStackTrace();
  }   
}
源代码
xust.demo.stu.StudentDao
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package xust.demo.stu;

import java.sql.*;

public class StudentDao{

  /**
   * 初始化数据库
   */
  public void init() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "create table Student(" +
      "no TEXT primary key," +
      "name TEXT not null," +
      "gender TEXT," +
      "age INTEGER," +
      "dept TEXT" +
      ");";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.execute();
      System.out.println("Table created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }   
  }


  /**
   * 创建记录
   */
  public void create() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "insert into Student (no, name, gender, age, dept)" +
      "values (?, ?, ?, ?, ?)";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
        pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.executeUpdate();
      System.out.println("Records created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 删除指定no记录
   */
  public void delete(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "delete from Student where no = ?;";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");

      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      pstmt.executeUpdate();
      System.out.println("Records delete successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }


  /**
   * 修改记录
   */
  public void update() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "update Student set No = ?, Name = ?, Gender = ?, Age = ?, Dept = ? where no = ?";

    try {        
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.setString(6, "X1");
      pstmt.executeUpdate();
      System.out.println("Records update successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
   * 读取指定id记录
   */
  public void read(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student where no = ?";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 读取全部记录
   */
  public void readAll() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }
}

运行

1
2
3
javac -encoding utf-8 StudentDao.java -d .
javac -encoding utf-8 StudentDaoTest.java -d .
java -classpath ".;sqlite-jdbc-3.41.0.0.jar" xust.stu.demo.StudentDaoTest

Create操作(insert)

xust.demo.stu.StudentDao
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
 * 创建记录
 */
public void create() {
  Connection con = null;
  PreparedStatement pstmt = null;
  String sql = "insert into Student (no, name, gender, age, dept)" +
    "values (?, ?, ?, ?, ?)";

  try {
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
    pstmt.setString(1, "X1");
    pstmt.setString(2, "X1");
    pstmt.setString(3, "X1");
    pstmt.setInt(4, 1);
    pstmt.setString(5, "X1");
    pstmt.executeUpdate();
    System.out.println("Records created successfully");
    pstmt.close();
    con.close();
  } catch (Exception e) {
    e.printStackTrace();
  }  
}
源代码
xust.demo.stu.StudentDao
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package xust.demo.stu;

import java.sql.*;

public class StudentDao{

  /**
   * 初始化数据库
   */
  public void init() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "create table Student(" +
      "no TEXT primary key," +
      "name TEXT not null," +
      "gender TEXT," +
      "age INTEGER," +
      "dept TEXT" +
      ");";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.execute();
      System.out.println("Table created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }   
  }


  /**
   * 创建记录
   */
  public void create() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "insert into Student (no, name, gender, age, dept)" +
      "values (?, ?, ?, ?, ?)";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
        pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.executeUpdate();
      System.out.println("Records created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 删除指定no记录
   */
  public void delete(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "delete from Student where no = ?;";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");

      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      pstmt.executeUpdate();
      System.out.println("Records delete successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }


  /**
   * 修改记录
   */
  public void update() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "update Student set No = ?, Name = ?, Gender = ?, Age = ?, Dept = ? where no = ?";

    try {        
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.setString(6, "X1");
      pstmt.executeUpdate();
      System.out.println("Records update successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
   * 读取指定id记录
   */
  public void read(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student where no = ?";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 读取全部记录
   */
  public void readAll() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }
}

Retrieve操作(select)

xust.demo.stu.StudentDao
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/**
 * 读取指定id记录
 */
public void read(String no) {
  Connection con = null;
  PreparedStatement pstmt = null;
  String sql = "select * from Student where no = ?";
  try {
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:demo.db");
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, no);
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) {
      System.out.println("No=" + rs.getString("no"));
      System.out.println("Name=" + rs.getString("name"));
      System.out.println("Gender=" + rs.getString("gender"));
      System.out.println("Age=" + rs.getInt("age"));
      System.out.println("Dept=" + rs.getString("dept"));
      System.out.println();
    }
    System.out.println("Record read successfully");
    pstmt.close();
    con.close();
  } catch (Exception e) {
    e.printStackTrace();
  }  
}
源代码
xust.demo.stu.StudentDao
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package xust.demo.stu;

import java.sql.*;

public class StudentDao{

  /**
   * 初始化数据库
   */
  public void init() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "create table Student(" +
      "no TEXT primary key," +
      "name TEXT not null," +
      "gender TEXT," +
      "age INTEGER," +
      "dept TEXT" +
      ");";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.execute();
      System.out.println("Table created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }   
  }


  /**
   * 创建记录
   */
  public void create() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "insert into Student (no, name, gender, age, dept)" +
      "values (?, ?, ?, ?, ?)";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
        pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.executeUpdate();
      System.out.println("Records created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 删除指定no记录
   */
  public void delete(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "delete from Student where no = ?;";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");

      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      pstmt.executeUpdate();
      System.out.println("Records delete successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }


  /**
   * 修改记录
   */
  public void update() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "update Student set No = ?, Name = ?, Gender = ?, Age = ?, Dept = ? where no = ?";

    try {        
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.setString(6, "X1");
      pstmt.executeUpdate();
      System.out.println("Records update successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
   * 读取指定id记录
   */
  public void read(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student where no = ?";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 读取全部记录
   */
  public void readAll() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }
}

Update操作

xust.demo.stu.StudentDao
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
 * 修改记录
 */
public void update() {
  Connection con = null;
  PreparedStatement pstmt = null;
  String sql = "update Student set No = ?, Name = ?, Gender = ?, Age = ?, Dept = ? where no = ?";

  try {        
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:demo.db");
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, "X1");
    pstmt.setString(2, "X1");
    pstmt.setString(3, "X1");
    pstmt.setInt(4, 1);
    pstmt.setString(5, "X1");
    pstmt.setString(6, "X1");
    pstmt.executeUpdate();
    System.out.println("Records update successfully");
    pstmt.close();
    con.close();
  } catch (Exception e) {
    e.printStackTrace();
  }
}
源代码
xust.demo.stu.StudentDao
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package xust.demo.stu;

import java.sql.*;

public class StudentDao{

  /**
   * 初始化数据库
   */
  public void init() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "create table Student(" +
      "no TEXT primary key," +
      "name TEXT not null," +
      "gender TEXT," +
      "age INTEGER," +
      "dept TEXT" +
      ");";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.execute();
      System.out.println("Table created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }   
  }


  /**
   * 创建记录
   */
  public void create() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "insert into Student (no, name, gender, age, dept)" +
      "values (?, ?, ?, ?, ?)";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
        pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.executeUpdate();
      System.out.println("Records created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 删除指定no记录
   */
  public void delete(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "delete from Student where no = ?;";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");

      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      pstmt.executeUpdate();
      System.out.println("Records delete successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }


  /**
   * 修改记录
   */
  public void update() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "update Student set No = ?, Name = ?, Gender = ?, Age = ?, Dept = ? where no = ?";

    try {        
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.setString(6, "X1");
      pstmt.executeUpdate();
      System.out.println("Records update successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
   * 读取指定id记录
   */
  public void read(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student where no = ?";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 读取全部记录
   */
  public void readAll() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }
}

Delete操作

xust.demo.stu.StudentDao
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
 * 删除指定no记录
 */
public void delete(String no) {
  Connection con = null;
  PreparedStatement pstmt = null;
  String sql = "delete from Student where no = ?;";

  try {
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:demo.db");

    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, no);
    pstmt.executeUpdate();
    System.out.println("Records delete successfully");
    pstmt.close();
    con.close();
  } catch (Exception e) {
    e.printStackTrace();
  } 
}
源代码
xust.demo.stu.StudentDao
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package xust.demo.stu;

import java.sql.*;

public class StudentDao{

  /**
   * 初始化数据库
   */
  public void init() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "create table Student(" +
      "no TEXT primary key," +
      "name TEXT not null," +
      "gender TEXT," +
      "age INTEGER," +
      "dept TEXT" +
      ");";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.execute();
      System.out.println("Table created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }   
  }


  /**
   * 创建记录
   */
  public void create() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "insert into Student (no, name, gender, age, dept)" +
      "values (?, ?, ?, ?, ?)";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
        pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.executeUpdate();
      System.out.println("Records created successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 删除指定no记录
   */
  public void delete(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "delete from Student where no = ?;";

    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");

      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      pstmt.executeUpdate();
      System.out.println("Records delete successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }


  /**
   * 修改记录
   */
  public void update() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "update Student set No = ?, Name = ?, Gender = ?, Age = ?, Dept = ? where no = ?";

    try {        
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "X1");
      pstmt.setString(2, "X1");
      pstmt.setString(3, "X1");
      pstmt.setInt(4, 1);
      pstmt.setString(5, "X1");
      pstmt.setString(6, "X1");
      pstmt.executeUpdate();
      System.out.println("Records update successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
   * 读取指定id记录
   */
  public void read(String no) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student where no = ?";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, no);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }  
  }


  /**
   * 读取全部记录
   */
  public void readAll() {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "select * from Student";
    try {
      Class.forName("org.sqlite.JDBC");
      con = DriverManager.getConnection("jdbc:sqlite:demo.db");
      pstmt = con.prepareStatement(sql);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        System.out.println("No=" + rs.getString("no"));
        System.out.println("Name=" + rs.getString("name"));
        System.out.println("Gender=" + rs.getString("gender"));
        System.out.println("Age=" + rs.getInt("age"));
        System.out.println("Dept=" + rs.getString("dept"));
        System.out.println();
      }
      System.out.println("Record read successfully");
      pstmt.close();
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }
}