本身是看b站上学java写的一个小项目，但是在学完jdbc之后加入了数据库的一些操作，将其与数据库绑定，其中很多的问题都是在csdn上解决的。
先是jdbc.properties
user=root password=123456zy url=jdbc:mysql://localhost:3306/phoneBookMessage driverClass=com.mysql.cj.jdbc.Driver
然后是数据库连接的封装，这里还没有使用数据库连接池，以后会更新
package phoneMession;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class Util {
public static Connection ConnectionJdbc() throws IOException, ClassNotFoundException, SQLException {
InputStream is=ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties=new Properties();
properties.load(is);
String user=properties.getProperty("user");
String password=properties.getProperty("password");
String url=properties.getProperty("url");
String driverClass=properties.getProperty("driverClass");
Class.forName(driverClass);
Connection conn= DriverManager.getConnection(url,user,password);
return conn;
}
public static void closeConnection(Connection conn, PreparedStatement preparedStatement){
if (conn!=null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (preparedStatement!=null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void closeConnection(Connection conn, PreparedStatement preparedStatement, ResultSet resultSet){
if (conn!=null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (preparedStatement!=null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet!=null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
下面是电话簿添加内容的一些正则表达式的比对
package phoneMession;
import java.util.Scanner;
public class telNoteRegex {
public int menuItemValidate(int min, int max) {
String regex = "[1-9]{1}";
Scanner scanner = new Scanner(System.in);
while (true) {
System.out.println("请输入正确数字"+min+" "+max);
String input = scanner.nextLine();
if (input.matches(regex)) {
int intnum = Integer.parseInt(input);
if (intnum >= min && intnum <= max) {
return intnum;
} else {
System.out.println("你的数字不符合预期");
}
} else {
System.out.println("你的数字不合法");
}
}
}
public String namevalidate() {
String regex = "[a-zA-Z]{1,10}";
Scanner scanner = new Scanner(System.in);
while (true) {
System.out.println("请输入姓名");
String input = scanner.nextLine();
if (input.matches(regex)) {
return input;
} else {
System.out.println("你的姓名有问题，重新输入");
}
}
}
public String agevalidate() {
String regex = "[1-9]{1}[0-9]{1}";
Scanner scanner = new Scanner(System.in);
while (true) {
System.out.println("请输入年龄");
String input = scanner.nextLine();
if (input.matches(regex)) {
return input;
} else {
System.out.println("你的年龄有问题，重新输入");
}
}
}
public String sexValidate() {
String regex = "[mMfF]{1}";
Scanner scanner = new Scanner(System.in);
while (true) {
System.out.println("请输入性别");
String input = scanner.nextLine();
if (input.matches(regex)) {
return input;
} else {
System.out.println("你的性别有问题，重新输入");
}
}
}
public String telValidate() {
String regex = "(\d{3,4}-\d{7,8})|([1]{1}\d{10})";
Scanner scanner = new Scanner(System.in);
while (true) {
System.out.println("请输入电话");
String input = scanner.nextLine();
if (input.matches(regex)) {
return input;
} else {
System.out.println("你的电话有问题，重新输入");
}
}
}
public String addressValidate() {
String regex = "\w{1,50}";
Scanner scanner = new Scanner(System.in);
while (true) {
System.out.println("请输入地址");
String input = scanner.nextLine();
if (input.matches(regex)) {
return input;
} else {
System.out.println("你的地址有问题，重新输入");
}
}
}
}
这里是创建了person类，为了更好的输出和写入
package phoneMession;
public class Person {
private int id;
private String name;
private String age;
private String sex;
private String telNum;
private String address;
public Person() {
}
public Person(String name, String age, String sex, String address, String telNum) {
this.name = name;
this.age = age;
this.sex = sex;
this.address = address;
this.telNum = telNum;
}
public Person(int id, String name, String age, String sex, String address, String telNum) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.address = address;
this.telNum = telNum;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getTelNum() {
return telNum;
}
public void setTelNum(String telNum) {
this.telNum = telNum;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
StringBuffer sb = new StringBuffer();
sb.append("序列").append(this.id).append("#").append("t");
sb.append("姓名").append(this.name).append("tt");
sb.append("年龄").append(this.age).append("tt");
sb.append("性别").append(this.sex).append("tt");
sb.append("号码").append(this.telNum).append("tt");
sb.append("地址").append(this.address).append("tt");
return sb.toString();
}
}
下面是普通的一个界面，无前端的界面，普通的代码块
package phoneMession;
import com.mysql.cj.protocol.Resultset;
import org.junit.Test;
import javax.xml.transform.Result;
import java.io.IOException;
import java.lang.annotation.Target;
import java.sql.*;
import java.util.*;
public class Operate {
// private List list;
public Operate() {
// this.list = new ArrayList<>();
}
public void addLogic() throws SQLException {
Menu menu = new Menu();
telNoteRegex tnr = new telNoteRegex();
while (true) {
menu.addMenu();
int item = tnr.menuItemValidate(1, 3);
switch (item) {
case 1:
this.addOperation();
break;
case 2:
this.showAll();
break;
case 3:
return;
}
}
}
public void searchLogic() throws SQLException {
Menu menu = new Menu();
telNoteRegex tnr = new telNoteRegex();
while (true) {
menu.SearchMenu();
int item = tnr.menuItemValidate(1, 7);
switch (item) {
case 1:
this.searchByName();
break;
case 2:
this.searchByAge();
break;
case 3:
this.searchBySex();
break;
case 4:
this.searchByAddress();
break;
case 5:
this.searchByTelnum();
break;
case 6:
this.showAll();
break;
case 7:
return;
}
}
}
public void modifyLogic() throws SQLException {
Menu menu = new Menu();
telNoteRegex tnr = new telNoteRegex();
while (true) {
menu.modifyMenu();
int item = tnr.menuItemValidate(1, 3);
switch (item) {
case 1:
this.showAll();
break;
case 2:
this.modifyOperation();
break;
case 3:
return;
}
}
}
public void deleteLogic() throws SQLException {
Menu menu = new Menu();
telNoteRegex tnr = new telNoteRegex();
while (true) {
menu.deleteMenu();
int item = tnr.menuItemValidate(1, 4);
switch (item) {
case 1:
this.showAll();
break;
case 2:
this.deleteOperation();
break;
case 3:
this.deleteAllOperation();
break;
case 4:
return;
}
}
}
public void orderLogic() throws SQLException {
Menu menu = new Menu();
telNoteRegex tnr = new telNoteRegex();
while (true) {
menu.orderMenu();
int item = tnr.menuItemValidate(1, 5);
switch (item) {
case 1:
this.orderName();
break;
case 2:
this.orderAge();
break;
case 3:
this.orderSex();
break;
case 4:
this.showAll();
case 5:
return;
}
}
}
public void addOperation() throws SQLException {
Connection conn = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
String sql = "insert into pb (name,age,sex,address,telnum) values(?,?,?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
telNoteRegex tnr = new telNoteRegex();
String name = tnr.namevalidate();
String age = tnr.agevalidate();
String sex = tnr.sexValidate();
String address = tnr.addressValidate();
String telnum = tnr.telValidate();
try {
preparedStatement.setObject(1, name);
preparedStatement.setObject(2, age);
preparedStatement.setObject(3, sex);
preparedStatement.setObject(4, address);
preparedStatement.setObject(5, telnum);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
preparedStatement.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
// Person p = new Person(name, age, sex, telnum, address);
// this.list.add(p);
// p.setId(this.list.size());
Util.closeConnection(conn, preparedStatement);
}
public void showAll() throws SQLException {
Connection conn = null;
ResultSet resultset = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
String sql = "select id,name,age,sex,address,telnum from pb ";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//preparedStatement.setObject(1);
resultset = preparedStatement.executeQuery();
while (resultset.next()) {
int id = resultset.getInt(1);
String name = resultset.getString(2);
String age = resultset.getString(3);
String sex = resultset.getString(4);
String address = resultset.getString(5);
String telnum = resultset.getString(6);
// System.out.println(id+" "+name+" "+age+" "+sex+" "+address+" "+telnum+" ");
p[i] = new Person(id, name, age, sex, address, telnum);
System.out.println(p[i]);
i++;
}
//for (Person p1:p
// ) {
// System.out.println(p1);
// }
Util.closeConnection(conn, preparedStatement, resultset);
}
public void searchByName() throws SQLException {
Connection conn = null;
ResultSet resultset = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
String sql = "select id,name,age,sex,address,telnum from pb where name =?";
String inputName;
System.out.println("请输入你要查询的名字");
// telNoteRegex tnr = new telNoteRegex();
// String inputName = tnr.namevalidate();
Scanner scanner = new Scanner(System.in);
inputName = scanner.next();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1, inputName);
resultset = preparedStatement.executeQuery();
if (resultset != null) {
while (resultset.next()) {
int id = resultset.getInt(1);
String name = resultset.getString(2);
String age = resultset.getString(3);
String sex = resultset.getString(4);
String address = resultset.getString(5);
String telnum = resultset.getString(6);
// System.out.println(id+" "+name+" "+age+" "+sex+" "+address+" "+telnum+" ");
p[i] = new Person(id, name, age, sex, address, telnum);
System.out.println(p[i]);
i++;
}
} else System.out.println("没有这个名字");
Util.closeConnection(conn, preparedStatement, resultset);
}
public void searchByAge() throws SQLException {
Connection conn = null;
ResultSet resultset = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
String sql = "select id,name,age,sex,address,telnum from pb where age =?";
String inputAge;
System.out.println("请输入你要查询的年龄");
Scanner scanner = new Scanner(System.in);
inputAge = scanner.next();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1, inputAge);
resultset = preparedStatement.executeQuery();
if (resultset != null) {
while (resultset.next()) {
int id = resultset.getInt(1);
String name = resultset.getString(2);
String age = resultset.getString(3);
String sex = resultset.getString(4);
String address = resultset.getString(5);
String telnum = resultset.getString(6);
// System.out.println(id+" "+name+" "+age+" "+sex+" "+address+" "+telnum+" ");
p[i] = new Person(id, name, age, sex, address, telnum);
System.out.println(p[i]);
i++;
}
} else System.out.println("没有该年龄段的人");
;
Util.closeConnection(conn, preparedStatement, resultset);
}
public void searchBySex() throws SQLException {
Connection conn = null;
ResultSet resultset = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
String sql = "select id,name,age,sex,address,telnum from pb where sex =?";
String inputSex;
System.out.println("请输入你要查询的性别（mMfF）");
Scanner scanner = new Scanner(System.in);
inputSex = scanner.next();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1, inputSex);
resultset = preparedStatement.executeQuery();
if (resultset != null) {
while (resultset.next()) {
int id = resultset.getInt(1);
String name = resultset.getString(2);
String age = resultset.getString(3);
String sex = resultset.getString(4);
String address = resultset.getString(5);
String telnum = resultset.getString(6);
// System.out.println(id+" "+name+" "+age+" "+sex+" "+address+" "+telnum+" ");
p[i] = new Person(id, name, age, sex, address, telnum);
System.out.println(p[i]);
i++;
}
} else System.out.println("没有该性别的人");
;
Util.closeConnection(conn, preparedStatement, resultset);
}
public void searchByTelnum() throws SQLException {
Connection conn = null;
ResultSet resultset = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
String sql = "select id,name,age,sex,address,telnum from pb where telnum =?";
String inputTelnum;
System.out.println("请输入你要查询的电话号");
Scanner scanner = new Scanner(System.in);
inputTelnum = scanner.next();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1, inputTelnum);
resultset = preparedStatement.executeQuery();
if (resultset != null) {
while (resultset.next()) {
int id = resultset.getInt(1);
String name = resultset.getString(2);
String age = resultset.getString(3);
String sex = resultset.getString(4);
String address = resultset.getString(5);
String telnum = resultset.getString(6);
// System.out.println(id+" "+name+" "+age+" "+sex+" "+address+" "+telnum+" ");
p[i] = new Person(id, name, age, sex, address, telnum);
System.out.println(p[i]);
i++;
}
} else System.out.println("没有该电话的人");
;
Util.closeConnection(conn, preparedStatement, resultset);
}
public void searchByAddress() throws SQLException {
Connection conn = null;
ResultSet resultset = null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
String sql = "select id,name,age,sex,address,telnum from pb where address =?";
String inputAddress;
System.out.println("请输入你要查询的地址");
Scanner scanner = new Scanner(System.in);
inputAddress = scanner.next();
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1, inputAddress);
resultset = preparedStatement.executeQuery();
// if(resultset.next()) {
while (resultset.next()) {
int id = resultset.getInt(1);
String name = resultset.getString(2);
String age = resultset.getString(3);
String sex = resultset.getString(4);
String address = resultset.getString(5);
String telnum = resultset.getString(6);
// System.out.println(id+" "+name+" "+age+" "+sex+" "+address+" "+telnum+" ");
p[i] = new Person(id, name, age, sex, address, telnum);
System.out.println(p[i]);
i++;
}
// }else System.out.println("没有住在这的人");;
Util.closeConnection(conn, preparedStatement, resultset);
}
public void modifyOperation() throws SQLException {
Connection conn = null;
// ResultSet resultset=null;
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Person p[];
p = new Person[10];//可以处理大小，有一个想法是在设置一个resultSet结果集然后while循环n++，查出有几列
int i = 0;
int id;
String name;
String age;
String sex;
String address;
String telnum;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你要改变的id号");
id = scanner.nextInt();
System.out.println("请输入你要改变的名字");
name = scanner.next();
System.out.println("请输入你要改变的年龄");
age = scanner.next();
System.out.println("请输入你要改变的性别");
sex = scanner.next();
System.out.println("请输入你要改变的地址");
address = scanner.next();
System.out.println("请输入你要改变的电话号");
telnum = scanner.next();
String sql = "update pb set name= ?,age= ?,sex= ?,address= ?,telnum= ? where id=? ";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setObject(1, name);
preparedStatement.setObject(2, age);
preparedStatement.setObject(3, sex);
preparedStatement.setObject(4, address);
preparedStatement.setObject(5, telnum);
preparedStatement.setObject(6, id);
preparedStatement.execute();
// if(resultset.next()) {
// }else System.out.println("没有住在这的人");;
Util.closeConnection(conn, preparedStatement);
}
public void deleteOperation() throws SQLException {
Connection conn = null;
String deleteId;
Scanner scanner = new Scanner(System.in);
System.out.println("请输入你要删除的id号");
deleteId = scanner.next();
{
try {
conn = Util.ConnectionJdbc();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
String sql = "delete from pb where id =?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
try {
preparedStatement.setObject(1, deleteId);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
preparedStatement.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
Util.closeConnection(conn, preparedStatement);
// if(this.list.size()!=0) {
// telNoteRegex tnr = new telNoteRegex();
// System.out.println("请输入记录序号");
// int item = tnr.menuItemValidate(1, this.list.size());
// this.list.remove(list.get(item-1));
//for(int i=0;i
package phoneMession;
public class Menu {
public void mainMenu() {
System.out.println("--------------------");
System.out.println("1.添加记录");
System.out.println("2.查找记录");
System.out.println("3.修改记录");
System.out.println("4.删除记录");
System.out.println("5.排序记录");
System.out.println("6.退出菜单");
System.out.println("--------------------");
}
public void orderMenu() {
System.out.println("--------------------");
System.out.println("1.姓名排序记录");
System.out.println("2.年龄排序记录");
System.out.println("3.性别排序记录");
System.out.println("4.查看全部记录");
System.out.println("5.返回上一记录");
System.out.println("--------------------");
}
public void SearchMenu() {
System.out.println("--------------------");
System.out.println("1.姓名找记录");
System.out.println("2.年龄找记录");
System.out.println("3.性别找记录");
System.out.println("4.地址找记录");
System.out.println("5.号码找记录");
System.out.println("6.查看全部记录");
System.out.println("7.返回上一记录");
System.out.println("--------------------");
}
public void addMenu() {
System.out.println("--------------------");
System.out.println("1.添加记录");
System.out.println("2.查看全部记录");
System.out.println("3.返回上一级菜单");
System.out.println("--------------------");
}
public void subModifyMenu() {
System.out.println("--------------------");
System.out.println("1.姓名");
System.out.println("2.年龄");
System.out.println("3.性别");
System.out.println("4.号码");
System.out.println("5.地址");
System.out.println("6.返回上一级菜单");
System.out.println("--------------------");
}
public void modifyMenu() {
System.out.println("--------------------");
System.out.println("1.查看记录");
System.out.println("2.修改指定记录");
System.out.println("3.返回上一级菜单");
System.out.println("--------------------");
}
public void deleteMenu() {
System.out.println("--------------------");
System.out.println("1.查看全记录");
System.out.println("2.删除指定记录");
System.out.println("3.删除全部记录");
System.out.println("4.返回上一级菜单");
System.out.println("--------------------");
}
}
package phoneMession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
public class App {
public static void main(String[] args) throws SQLException {
App app=new App();
app.start();
}
public void start() throws SQLException {
Menu menu=new Menu();
telNoteRegex tnr=new telNoteRegex();
Operate o=new Operate();
while(true){
menu.mainMenu();
int item=tnr.menuItemValidate(1,6);
switch (item){
case 1:o.addLogic(); break;
case 2:o.searchLogic(); break;
case 3:o.modifyLogic(); break;
case 4:o.deleteLogic(); break;
case 5:o.orderLogic(); break;
case 6: System.exit(0);
}
}
}
}
