package电话薄1;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importjava.util.Scanner;importcom.mchange.v2.c3p0.ComboPooledDataSource;public classOperate {//连接池
ComboPooledDataSource cp = new ComboPooledDataSource("helloc3p0");//用户添加信息业务逻辑控制
public voidaddLogic() {
TelNoteRegex rtn= newTelNoteRegex();
Operate i= newOperate();
Menu m= newMenu();
m.addMenu();switch (rtn.menuRegex(1, 3)) {case 1:
addOperation();//break;
case 2:
List lb =i.showAll();//break;
}
}//用户查询信息业务逻辑控制
public voidsearchLogic() {
TelNoteRegex rtn= newTelNoteRegex();
Menu m= newMenu();
m.searchMenu();switch (rtn.menuRegex(1, 7)) {case 1:
searchByName();break;case 2:
searchByAge();break;case 3:
searchBySex();break;case 4:
searchByTelNum();break;case 5:
searchByAdd();break;case 6:
showAll();break;
}
}//修改信息业务逻辑控制
public voidmodifyLogicLogic() {
TelNoteRegex rtn= newTelNoteRegex();
Menu m= newMenu();
m.modifyMenu();switch (rtn.menuRegex(1, 3)) {case 1:
showAll();case 2:
modify();break;
}
}//修改指定记录信息
public voidmodify() {
TelNoteRegex rtn= newTelNoteRegex();
Menu m= newMenu();
m.subModifyMenu();switch (rtn.menuRegex(1, 6)) {case 1:
modName();break;case 2:
modAge();break;case 3:
modSex();break;case 4:
modTelNum();break;case 5:
modAdd();break;case 6:
modifyLogicLogic();break;
}
}//删除信息业务逻辑控制
public voiddeleteLogic() {
TelNoteRegex rtn= newTelNoteRegex();
Menu m= newMenu();
m.deleteMenu();switch (rtn.menuRegex(1, 4)) {case 1:
showAll();break;case 2:
delete();break;case 3:
deleteAll();break;
}
}//排序信息业务逻辑控制
public voidorderLogic() {
TelNoteRegex rtn= newTelNoteRegex();
Menu m= newMenu();
m.orderMenu();switch (rtn.menuRegex(1, 5)) {case 1:
orderName();break;case 2:
orderAge();break;case 3:
orderSex();break;case 4:
showAll();break;
}
}//添加新用户信息
public ListaddOperation() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//输入姓名并判断
TelNoteRegex name1 = newTelNoteRegex();
String name=name1.nameRegex();//输入年龄并判断
TelNoteRegex age1 = newTelNoteRegex();
String age=age1.ageRegex();//输入性别并判断
TelNoteRegex sex1 = newTelNoteRegex();
String sex=sex1.sexRegex();//输入电话号码并判断
TelNoteRegex telnum1 = newTelNoteRegex();
String telnum=telnum1.telnumRegex();//输入地址
TelNoteRegex address1 = newTelNoteRegex();
String address=address1.addressRegex();
String sql= "insert into person values(sq_person.nextval,?,?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, age);
ps.setString(3, sex);
ps.setString(4, telnum);
ps.setString(5, address);
ps.executeUpdate();
System.out.println("添加成功");
ps.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//查询全部用户信息
public ListshowAll() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();
ResultSet rs= st.executeQuery("select * from person");//遍历结果集
while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按姓名查找
public ListsearchByName() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//输入姓名并判断
TelNoteRegex name1 = newTelNoteRegex();
String name=name1.nameRegex();
String sql= "select * from person where name = '" + name + "'";
ResultSet rs=st.executeQuery(sql);//遍历结果集
if (!rs.next()) {
System.out.println("没有相关数据");
}else{
rs=st.executeQuery(sql);while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按年龄查找
public ListsearchByAge() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//输入年龄并判断
TelNoteRegex age1 = newTelNoteRegex();
String age=age1.ageRegex();
String sql= "select * from person where age = '" + age + "'";
ResultSet rs=st.executeQuery(sql);//遍历结果集
if (!rs.next()) {
System.out.println("没有相关数据");
}else{
rs=st.executeQuery(sql);while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按性别查询
public ListsearchBySex() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//输入性别并判断
TelNoteRegex sex1 = newTelNoteRegex();
String sex=sex1.sexRegex();
String sql= "select * from person where sex = '" + sex + "'";
ResultSet rs=st.executeQuery(sql);//遍历结果集
if (!rs.next()) {
System.out.println("没有相关数据");
}else{
rs=st.executeQuery(sql);while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按号码查找
public ListsearchByTelNum() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//输入号码并判断
TelNoteRegex telnum1 = newTelNoteRegex();
String telnum=telnum1.telnumRegex();
String sql= "select * from person where telnum = '" + telnum + "'";
ResultSet rs=st.executeQuery(sql);//遍历结果集
if (!rs.next()) {
System.out.println("没有相关数据");
}else{
rs=st.executeQuery(sql);while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按号码查找
public ListsearchByAdd() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//输入地址并判断
TelNoteRegex address1 = newTelNoteRegex();
String address=address1.addressRegex();
String sql= "select * from person where address = '" + address + "'";
ResultSet rs=st.executeQuery(sql);//遍历结果集
if (!rs.next()) {
System.out.println("没有相关数据");
}else{
rs=st.executeQuery(sql);while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//修改姓名
public voidmodName() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//获取ID的最小值
ResultSet rs1 = st.executeQuery("select min(id) from person");int min = 0;
rs1.next();
min= rs1.getInt(1);//获取ID的最大值
ResultSet rs2 = st.executeQuery("select max(id) from person");int max = 0;
rs2.next();
max= rs2.getInt(1);
System.out.println("请输入要修改的ID号,最小ID号为:" + min + "最大ID号为:" +max);
Scanner scan= newScanner(System.in);int id =scan.nextInt();//输入姓名并判断
TelNoteRegex name1 = newTelNoteRegex();
String name=name1.nameRegex();
String sql= "update person set name = '" + name + "' where id = '" + id + "'";
ResultSet rs=st.executeQuery(sql);
ResultSet rs5= st.executeQuery("select * from person where id = '" + id + "'");//遍历结果集
if (!rs5.next()) {
System.out.println("没有相关数据,系统退回到主菜单");
}else{
rs=st.executeQuery(sql);
System.out.println("修改成功");
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//修改年龄
public voidmodAge() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//获取ID的最小值
ResultSet rs1 = st.executeQuery("select min(id) from person");int min = 0;
rs1.next();
min= rs1.getInt(1);//获取ID的最大值
ResultSet rs2 = st.executeQuery("select max(id) from person");int max = 0;
rs2.next();
max= rs2.getInt(1);
System.out.println("请输入要修改的ID号,最小ID号为:" + min + "最大ID号为:" +max);
Scanner scan= newScanner(System.in);int id =scan.nextInt();//输入姓名并判断
TelNoteRegex age1 = newTelNoteRegex();
String age=age1.ageRegex();
String sql= "update person set age = '" + age + "' where id = '" + id + "'";
ResultSet rs=st.executeQuery(sql);
ResultSet rs5= st.executeQuery("select * from person where id = '" + id + "'");//遍历结果集
if (!rs5.next()) {
System.out.println("没有相关数据,系统退回到主菜单");
}else{
rs=st.executeQuery(sql);
System.out.println("修改成功");
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//修改性别
public voidmodSex() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//获取ID的最小值
ResultSet rs1 = st.executeQuery("select min(id) from person");int min = 0;
rs1.next();
min= rs1.getInt(1);//获取ID的最大值
ResultSet rs2 = st.executeQuery("select max(id) from person");int max = 0;
rs2.next();
max= rs2.getInt(1);
System.out.println("请输入要修改的ID号,最小ID号为:" + min + "最大ID号为:" +max);
Scanner scan= newScanner(System.in);int id =scan.nextInt();//输入性别并判断
TelNoteRegex sex1 = newTelNoteRegex();
String sex=sex1.sexRegex();
String sql= "update person set sex = '" + sex + "' where id = '" + id + "'";
ResultSet rs=st.executeQuery(sql);
ResultSet rs5= st.executeQuery("select * from person where id = '" + id + "'");//遍历结果集
if (!rs5.next()) {
System.out.println("没有相关数据,系统退回到主菜单");
}else{
rs=st.executeQuery(sql);
System.out.println("修改成功");
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//修改号码
public voidmodTelNum() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//获取ID的最小值
ResultSet rs1 = st.executeQuery("select min(id) from person");int min = 0;
rs1.next();
min= rs1.getInt(1);//获取ID的最大值
ResultSet rs2 = st.executeQuery("select max(id) from person");int max = 0;
rs2.next();
max= rs2.getInt(1);
System.out.println("请输入要修改的ID号,最小ID号为:" + min + "最大ID号为:" +max);
Scanner scan= newScanner(System.in);int id =scan.nextInt();
ResultSet rs5= st.executeQuery("select * from person where id = '" + id + "'");if (!rs5.next()) {
System.out.println("没有相关数据,系统退回到主菜单");//遍历结果集
}else{//输入号码并判断
TelNoteRegex telnum1 = newTelNoteRegex();
String telnum=telnum1.telnumRegex();
String sql= "update person set telnum = '" + telnum + "' where id = '" + id + "'";
ResultSet rs=st.executeQuery(sql);
rs=st.executeQuery(sql);
System.out.println("修改成功");
}
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//修改地址
public voidmodAdd() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();//获取ID的最小值
ResultSet rs1 = st.executeQuery("select min(id) from person");int min = 0;
rs1.next();
min= rs1.getInt(1);//获取ID的最大值
ResultSet rs2 = st.executeQuery("select max(id) from person");int max = 0;
rs2.next();
max= rs2.getInt(1);
System.out.println("请输入要修改的ID号,最小ID号为:" + min + "最大ID号为:" +max);
Scanner scan= newScanner(System.in);int id =scan.nextInt();//输入地址并判断
TelNoteRegex add1 = newTelNoteRegex();
String add=add1.addressRegex();
String sql= "update person set address = '" + add + "' where id = '" + id + "'";
ResultSet rs=st.executeQuery(sql);
ResultSet rs5= st.executeQuery("select * from person where id = '" + id + "'");//遍历结果集
if (!rs5.next()) {
System.out.println("没有相关数据,系统退回到主菜单");
}else{
rs=st.executeQuery(sql);
System.out.println("修改成功");
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//删除指定用户信息
public voiddelete() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();
ResultSet rs1= st.executeQuery("select min(id) from person");
ResultSet rs2= st.executeQuery("select max(id) from person");
System.out.println(rs1);
System.out.println("请输入要删除的ID");
Scanner scan= newScanner(System.in);int id =scan.nextInt();
String sql= "delete from Person where id = '" + id + "'";
ResultSet rs=st.executeQuery(sql);
rs=st.executeQuery(sql);
System.out.println("删除成功");
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//删除全部记录
public voiddeleteAll() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();
String sql= "truncate table person";
ResultSet rs=st.executeQuery(sql);
rs=st.executeQuery(sql);
System.out.println("全部数据已经清空");
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}return;
}//按用户姓名排序
public ListorderName() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();
ResultSet rs= st.executeQuery("select * from person order by name");//遍历结果集
while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按用户年龄排序
public ListorderAge() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();
ResultSet rs= st.executeQuery("select * from person order by age");//遍历结果集
while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}//按用户性别排序
public ListorderSex() {
List rtn = new ArrayList<>();try{
Connection conn=cp.getConnection();
Statement st=conn.createStatement();
ResultSet rs= st.executeQuery("select * from person order by sex");//遍历结果集
while(rs.next()) {
Person pe= newPerson();
pe.setId(rs.getInt("id"));
pe.setName(rs.getString("name"));
pe.setAge(rs.getString("age"));
pe.setSex(rs.getString("sex"));
pe.setTelNum(rs.getString("telnum"));
pe.setAddress(rs.getString("address"));
rtn.add(pe);
System.out.println(pe);
}
rs.close();
st.close();
cp.close();
}catch(Exception e) {
e.printStackTrace();
}returnrtn;
}
}