<span style="font-size:18px;">oracle中如何 Priamry key自增
摸索了一晚上,终于实现了key的自增,现在将实现的过程总结如下:</span>
<span style="font-size:18px;">
(1)首先创建一个表:这里是我创建的一个User表,包括id,user_name,password三个字段
实现的函数如下:
public void create(String tableName) {
jdbcTemplate.execute("create table "+tableName+"(id integer,user_name varchar2(40),password varchar 2(40),primary key (id))");
}</span>
<span style="font-size:18px;">
(2)然后创建一个序列:
String sql="create sequence "+seq //这里的seq是我自定义的序列名
+ " start with 8"
+ "increment by 1"
+ "minvalue 1"
+ "maxvalue 999999"
+ "nocycle nocache";
jdbcTemplate.execute(sql);
(3)为主键id设置自增:
String sql1="insert into td(id) values (seq.NEXTVAL)";
jdbcTemplate.execute(sql1);
(4)创建触发器:
String sql2="create trigger tri_user before insert on td for each row begin select seq.NEXTVAL into :new.id from dual;end;";
jdbcTemplate.execute(sql2);
注意这里的语法要写正确,我开始没写正确,出现了错误:
(5)插入数据
我自己写了一个save函数,如下:
public void save(User user) {
//Assert.isNull(user, "user is not null");
jdbcTemplate.update("insert into td(id,user_name,password) values(?,?,?)",
new Object[]{user.getId(),user.getUser_name(),user.getPassword()},
new int[]{java.sql.Types.INTEGER,java.sql.Types.VARCHAR,java.sql.Types.VARCHAR});
}
我的测试类如下:
public class jdbcTest {
@Test
public void test() {
ApplicationContext ctx = new ClassPathXmlApplicationContext(
"/beans.xml");
UserDAO userDAO = (UserDAO) ctx.getBean("userDAO");
User u3=new User();
u3.setUser_name("cwww");
u3.setPassword("123456");
userDAO.save(u3);
}
不断插入后生成的表如下:
<img src="https://img-blog.csdn.net/20141121084153621?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvY3d6aHNp/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />