select sysdate from dual;
--账户表
--账户编号,账户卡号,账户密码,账户余额,账户状态,创建时间
drop table account;
create table account
(
id number(10) primary key,
account_number varchar2(50) not null,
account_pwd varchar2(10) not null,
account_money number(10,2) not null,
account_status number(5) check(account_status in(0,1)),
creation_time date default sysdate
);
--创建序列
drop sequence seq_account;
create sequence seq_account;
--添加数据
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633201','123',
10.00,1,to_date('2010-08-09 12:12:12','yyyy-MM-dd hh24:mi:ss'));
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633202','123',
20.00,1,to_date('2010-08-10 12:12:12','yyyy-MM-dd hh24:mi:ss'));
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633203','123',
30.00,1,to_date('2010-08-11 12:12:12','yyyy-MM-dd hh24:mi:ss'));
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633204','123',
40.00,1,to_date('2010-08-12 12:12:12','yyyy-MM-dd hh24:mi:ss'));
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633205','123',
50.00,1,to_date('2010-08-13 12:12:12','yyyy-MM-dd hh24:mi:ss'));
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633206','123',
60.00,1,to_date('2010-08-14 12:12:12','yyyy-MM-dd hh24:mi:ss'));
insert into account(
id,account_number,account_pwd,
account_money,account_status,creation_time
)
values
(seq_account.nextval,'6402211990052633207','123',
70.00,1,to_date('2010-08-15 12:12:12','yyyy-MM-dd hh24:mi:ss'));
select * from (select rownum r,a.* from account a where rownum<=6)t where t.r>3
select * from (select rownum r,a.* from account a where rownum<=#{endrow})t where t.r>#{startrow}
insert into account(id,account_number,account_pwd,account_money,account_status,creation_time) values(seq_account.nextval,#{account_number},#{account_pwd},#{account_money},#{account_status},#{creation_time});
update account set account_number=#{account_number},account_pwd=#{account_pwd},account_money=#{account_money},account_status=#{account_status},creation_time=#{creation_time} where id=#{id}
3.在com.pojo包下创建Account.java类
public class Account {
private Integer id ;
private String account_number ;
private String account_pwd ;
private Double account_money ;
private Integer account_status ;
private Date creation_time ;
public Account() {
super();
}
public Account(String accountNumber, String accountPwd,
Double accountMoney, Integer accountStatus, Date creationTime) {
account_number = accountNumber;
account_pwd = accountPwd;
account_money = accountMoney;
account_status = accountStatus;
creation_time = creationTime;
}
public Account(Integer id, String accountNumber, String accountPwd,
Double accountMoney, Integer accountStatus, Date creationTime) {
super();
this.id = id;
account_number = accountNumber;
account_pwd = accountPwd;
account_money = accountMoney;
account_status = accountStatus;
creation_time = creationTime;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getAccount_number() {
return account_number;
}
public void setAccount_number(String accountNumber) {
account_number = accountNumber;
}
public String getAccount_pwd() {
return account_pwd;
}
public void setAccount_pwd(String accountPwd) {
account_pwd = accountPwd;
}
public Double getAccount_money() {
return account_money;
}
public void setAccount_money(Double accountMoney) {
account_money = accountMoney;
}
public Integer getAccount_status() {
return account_status;
}
public void setAccount_status(Integer accountStatus) {
account_status = accountStatus;
}
public Date getCreation_time() {
return creation_time;
}
public void setCreation_time(Date creationTime) {
creation_time = creationTime;
}
@Override
public String toString() {
return "Account [account_money=" + account_money + ", account_number="
+ account_number + ", account_pwd=" + account_pwd
+ ", account_status=" + account_status + ", creation_time="
+ creation_time + ", id=" + id + "]";
}
}
4.在com.page包下创建分页工具类PageUtil.java
public class PageUtil {
private Integer pageno; //当前页
private Integer pagesize; //页面大小
private Integer totalcount; //同条数
private Integer totalpage; //同页数
private Integer startrow; //起始行
private Integer endrow; //结束行
public PageUtil() {
}
/**
* 普通分页
* @param pageno
* @param pagesize
* @param totalcount
*/
public PageUtil(Integer pageno, Integer pagesize, Integer totalcount) {
this.pageno = pageno;
this.pagesize = pagesize;
this.startrow=(pageno-1)*pagesize;
this.endrow=pageno*pagesize;
this.totalcount = totalcount;
this.setTotalpage(totalcount, pagesize);
}
public Integer getPageno() {
return pageno;
}
public void setPageno(Integer pageno) {
this.pageno = pageno;
}
public Integer getPagesize() {
return pagesize;
}
public void setPagesize(Integer pagesize) {
this.pagesize = pagesize;
}
public Integer getTotalcount() {
return totalcount;
}
public void setTotalcount(Integer totalcount) {
this.totalcount = totalcount;
}
public Integer getTotalpage() {
return totalpage;
}
/**
* 设置总页数
* @param totalcount
* @param pagesize
*/
public void setTotalpage(Integer totalcount,Integer pagesize) {
this.totalpage = totalcount%pagesize==0?totalcount/pagesize:totalcount/pagesize+1;
}
public Integer getStartrow() {
return startrow;
}
public void setStartrow(Integer startrow) {
this.startrow = startrow;
}
public Integer getEndrow() {
return endrow;
}
public void setEndrow(Integer endrow) {
this.endrow = endrow;
}
}
5.在com.mapper包下创建数据访问层映射接口AccountMapper.java
public interface AccountMapper {
/**
* 1.分页查询
* @param pageutil
* @return
*/
@Select("select * from (select rownum r,a.* from account a where rownum<=#{endrow})t where t.r>#{startrow}")
List<Account> finaAll(PageUtil pageutil);
/**
* 2.根据id查询
* @param id
* @return
*/
@Select("select * from account where id=#{id}")
Account findById(Integer id);
/**
* 3.保存账户对象
* @param account
* @return
*/
@Insert("insert into account(id,account_number,account_pwd,account_money,account_status,creation_time) values(seq_account.nextval,#{account_number},#{account_pwd},#{account_money},#{account_status},#{creation_time})")
Integer saveAccount(Account account);
/**
* 4.修改对象
* @param account
* @return
*/
@Update("update account set account_number=#{account_number},account_pwd=#{account_pwd},account_money=#{account_money},account_status=#{account_status},creation_time=#{creation_time} where id=#{id}")
Integer updateAccount(Account account);
/**
* 5.根据id删除
* @param id
* @return
*/
@Delete("delete from account where id=#{id}")
Integer deleteById(Integer id);
}
6.在src下创建数据库属性文件jdbc.properties driver=oracle.jdbc.driver.OracleDriver url=jdbc:oracle:thin:@127.0.0.1:1521:orcl username=**** password=****
7.在src下创建主配置文件mybatis-config.xml
<configuration>
<properties resource="jdbc.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.mapper.AccountMapper"/>
</mappers>
</configuration>
8.在com.util包下创建获取SqlSession工具类MyBatisUtil.java
public class MyBatisUtil {
private static SqlSessionFactory sqlSessionFactory=null;
static{
try {
Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
System.out.println("解析xml文件或创建工厂出错!");
}
}
/**
* 获取SqlSession对象
* @param isCommit
* @return
*/
public static SqlSession getSqlSession(boolean isCommit){
return sqlSessionFactory.openSession(isCommit);
}
}
9.在com.mapper包下创建数据访问层映射接口实现类AccountMapperImpl.java
public class AccountMapperImpl implements AccountMapper {
private SqlSession sqlSession=MyBatisUtil.getSqlSession(true);
private AccountMapper mapper=sqlSession.getMapper(AccountMapper.class);
public Integer deleteById(Integer id) {
return mapper.deleteById(id);
}
public List<Account> finaAll(PageUtil pageutil) {
return mapper.finaAll(pageutil);
}
public Account findById(Integer id) {
return mapper.findById(id);
}
public Integer saveAccount(Account account) {
return mapper.saveAccount(account);
}
public Integer updateAccount(Account account) {
return mapper.updateAccount(account);
}
public SqlSession getSqlSession() {
return sqlSession;
}
public void setSqlSession(SqlSession sqlSession) {
this.sqlSession = sqlSession;
}
public AccountMapper getMapper() {
return mapper;
}
public void setMapper(AccountMapper mapper) {
this.mapper = mapper;
}
}
10.在com.test包下创建测试类Test.java
public class Test {
public static void main(String[] args) {
AccountMapper mapper=new AccountMapperImpl();
System.out.println("---------------1.分页查询前三条:------------------");
List<Account> list=mapper.finaAll(new PageUtil(1, 3, 7));
for (Account account : list) {
System.out.println(account);
}
System.out.println("---------------1.根据id查询第5条:------------------");
Account account=mapper.findById(5);
System.out.println(account);
}
}