案例描述:在Oracle中创建一张个人信息表,包含三个字段,id、name和age,其中id为自增主键
具体SQL编写如下所示:
CREATE TABLE personal_info (
id NUMBER(10) NOT NULL,
name VARCHAR2(50) NOT NULL,
age NUMBER(3) NOT NULL,
CONSTRAINT personal_info_pk PRIMARY KEY (id)
);
CREATE SEQUENCE personal_info_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;
CREATE OR REPLACE TRIGGER personal_info_trg
BEFORE INSERT ON personal_info
FOR EACH ROW
BEGIN
:NEW.id := personal_info_seq.NEXTVAL;
END;
在上面的SQL中创建了一张表,使用到了Oracle中的序列来实现主键自增。在往Oracle数据库表中新增一条记录时,序列的使用方式如下:
try {
// 加载Oracle JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
// 连接Oracle数据库
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "username", "password");
// 创建PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO personal_info (id, name, age) VALUES (personal_info_seq.NEXTVAL, ?, ?)");
// 设置参数
pstmt.setString(1, "张三");
pstmt.setInt(2, 20);
// 执行插入操作
int rows = pstmt.executeUpdate();
if (rows > 0) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
// 关闭PreparedStatement对象
pstmt.close();
// 关闭数据库连接
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
具体开发中,可以根据实际需求来适当修改