步骤一:创建表数据
create sequence role_seq;
create table role_info(
role_id number(4) constraint role_info_id_pk primary key,
name varchar2(50) not null
);
insert into role_info(role_id,name) values (role_seq.nextval,'管理员');
insert into role_info(role_id,name) values (role_seq.nextval,'柜台人员');
insert into role_info(role_id,name) values (role_seq.nextval,'中层领导');
commit;
步骤二:创建数据库连接工具类
jdbc.properties内容如下:
#ORACLE
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
userName=ZHAOYANG
password=ZHAOYANG
连接数据库工具类:ConnectFactory
package study;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.tomcat.dbcp.dbcp2.ConnectionFactory;
//oracle连接工具类
public class ConnectFactory {
private static Connection connection=null;
static {
try {
Properties p=new Properties();
p.load(ConnectionFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));
String driver=p.getProperty("driver");
String url=p.getProperty("url");
String userName=p.getProperty("userName");
String password=p.getProperty("password");
Class.forName(driver);
connection=DriverManager.getConnection(url,userName,password);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
return connection;
}
public static void main(String[] args) {
System.out.println(connection);
}
}
步骤三:创建实体类RoleInfo
package demo;
//role_info表和Java对象的映射类
public class RoleInfo {
private int roleId;
private String name;
public int getRoleId() {
return roleId;
}
public void setRoleId(int roleId) {
this.roleId = roleId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "RoleInfo [roleId=" + roleId + ", name=" + name + "]";
}
}
步骤四:创建接口类RoleInfoDAO
package dao;
import java.sql.SQLException;
import java.util.List;
import demo.RoleInfo;
//接口中定义了对数据的增改查的功能
public interface RoleInfoDAO {
/**
* 查询所有的角色
* @return所有角色返回List集合
*/
List<RoleInfo> findAll();
/**
* 新增角色
* @paramrole要添加角色
* @return添加后角色,包含角色ID
*/
RoleInfo save(RoleInfo role);
/**
* 修改某个角色
* @paramrole要修改的角色
* @return返回修改后的角色
*/
RoleInfo modify(RoleInfo role);
}
步骤五:创建接口实现类RoleInfoDAOImpl
package imp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import dao.RoleInfoDAO;
import demo.RoleInfo;
import study.ConnectFactory;
public class RoleInfoDAOImpl extends ConnectFactory implements RoleInfoDAO{
private static final String FIND_ALL="SELECT ROLE_ID,NAME FROM ROLE_INFO";
private static final String MODIFY="UPDATE ROLE_INFO SET NAME=? WHERE ROLE_ID=?";
private static final String INSERT="INSERT INTO ROLE_INFO(ROLE_ID,NAME) VALUES (ROLE_SEQ.NEXTVAL,?)";
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
@Override
public List<RoleInfo> findAll(){
try {
conn=ConnectFactory.getConnection();
String sql=FIND_ALL;
ps= conn.prepareStatement(sql);
rs=ps.executeQuery();
RoleInfo role=null;
List<RoleInfo> list=new ArrayList<RoleInfo>();
while(rs.next()) {
role=new RoleInfo();
role.setRoleId(rs.getInt("role_id"));
role.setName(rs.getString("name"));
list.add(role);
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public RoleInfo save(RoleInfo role) {
try {
conn=ConnectFactory.getConnection();
String sql=INSERT;
ps=conn.prepareStatement(sql,new String[] {"role_id"});
ps.setString(1,role.getName());
ps.executeUpdate();
rs=ps.getGeneratedKeys();
rs.next();
int id=rs.getInt(1);
role.setRoleId(id);
return role;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null) {
rs.close();
}
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public RoleInfo modify(RoleInfo role) {
try {
conn=ConnectFactory.getConnection();
String sql=MODIFY;
ps=conn.prepareStatement(sql);
ps.setString(1,role.getName());
ps.setInt(2,role.getRoleId());
int flag=ps.executeUpdate();
return (flag>0)?role:null;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(ps!=null) {
ps.close();
}
if(conn!=null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
步骤六:测试功能是否实现TestRoleInfoDAO
package imp;
import java.util.List;
import dao.RoleInfoDAO;
import demo.RoleInfo;
public class TestRoleInfoDAO {
public static void main(String[] args) {
TestRoleInfoDAO dao=new TestRoleInfoDAO();
dao.testFindAll();
//dao.testSave();
//dao.testModify();
}
public void testFindAll() {
RoleInfoDAO dao=new RoleInfoDAOImpl();
List<RoleInfo> list= dao.findAll();
for(RoleInfo role:list) {
System.out.println(role.toString());
System.out.println("——————————————————————");
}
}
public void testSave() {
RoleInfo role=new RoleInfo();
role.setName("admin");
RoleInfoDAO dao=new RoleInfoDAOImpl();
dao.save(role);
System.out.println(role.toString());
}
public void testModify() {
RoleInfo role=new RoleInfo();
role.setRoleId(5);
role.setName("common");
RoleInfoDAO dao=new RoleInfoDAOImpl();
role=dao.modify(role);
System.out.println(role.toString());
}
}