JDBC（通俗易懂）简单的操作（增、删、改、查）
项目所写的类：
说明：①、DButil 动态加载数据库驱动，以及获取java.sql.Connection的对象。
②、Personmodel 数据库列（栏位）所对应的字段，定义了相应的set和get方法。
③、Persondao 对数据库相应的增删改查方法 。
④、Personaction 对Persondao里面增删改查方法的进一步补充完善。
⑤、Personview 对应的将要展示数据的类
①②③④⑤的代码：
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DButil {
private static Connection connection;
static {
try {
//动态加载数据库驱动，注意要下载JDBC的jar包
Class.forName("com.mysql.jdbc.Driver");
//获得链接
connection = DriverManager.getConnection("jdbc:mysql://192.168.0.103:3306/testjdbc", "root","15090183397");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
return connection;
}
}
先定义静态变量 connection，然后在静态代码段里面动态加载数据库驱动，并获得链接connection对象，然后定义get方法获取到该connection对象，以后需要与数据库建立连接进行相关操作的话，只要调用DButil的getconnection()方法即可。
②代码：
package DBmodel;
import java.util.Date;
public class Personmodel {
private Integer id;
private String name;
private String sex;
private String birthday;
private String phone;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
数据库设计表:
Personmodel模型类对应数据库的相应列（栏位），然后将数据库相应栏位的操作对应到Personmodel类的某个对象中，然后进行操作，这就是Personmodel模型类的作用。
③如下：
package Dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import DB.DButil;
import DBmodel.Personmodel;
public class Persondao {
private static List<Personmodel> personmodels = new ArrayList<Personmodel>();
@SuppressWarnings("unused")
private Scanner scanner = new Scanner(System.in);
public static void addperson(Personmodel personmodel) throws SQLException {
Connection connection = DButil.getConnection();
String sql = " insert person " + " (name,sex,birthday,phone,email) "
+ " values(?,?,?,?,?);";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, personmodel.getName());
preparedStatement.setString(2, personmodel.getSex());
preparedStatement.setString(3, personmodel.getBirthday());
preparedStatement.setString(4, personmodel.getEmail());
preparedStatement.setString(5, personmodel.getEmail());
preparedStatement.execute();
}
public static void deleteperson(Integer id) throws SQLException {
Connection connection = DButil.getConnection();
String sql = " delete from person " + " where id=?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
preparedStatement.execute();
System.out.println("指定记录删除成功！");
}
public static void updateperson(Personmodel personmodel)
throws SQLException {
Connection connection = DButil.getConnection();
String sql = " update person "
+ " set name=?,sex=?,birthday=?,phone=?,email=?"
+ " where id=?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, personmodel.getName());
preparedStatement.setString(2, personmodel.getSex());
preparedStatement.setString(3, personmodel.getBirthday());
preparedStatement.setString(4, personmodel.getPhone());
preparedStatement.setString(5, personmodel.getEmail());
preparedStatement.setInt(6, personmodel.getId());
preparedStatement.execute();
}
public static Personmodel queryoneperson(Integer id) throws SQLException {
Connection connection = DButil.getConnection();
String sql = "select * from person " + " where id=?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Personmodel personmodel = new Personmodel();
personmodel.setId(resultSet.getInt("id"));
personmodel.setName(resultSet.getString("name"));
personmodel.setSex(resultSet.getString("sex"));
personmodel.setBirthday(resultSet.getString("birthday"));
personmodel.setPhone(resultSet.getString("phone"));
personmodel.setEmail(resultSet.getString("email"));
return personmodel;
}
return null;
}
public static List<Personmodel> queryonepersonbyname(String name)
throws SQLException {
Connection connection = DButil.getConnection();
String sql = "select * from person " + " where name=?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, name);
ResultSet resultSet = preparedStatement.executeQuery();
Personmodel personmodel = null;
List<Personmodel> list = new ArrayList<Personmodel>();
while (resultSet.next()) {
personmodel= new Personmodel();
personmodel.setId(resultSet.getInt("id"));
personmodel.setName(resultSet.getString("name"));
personmodel.setSex(resultSet.getString("sex"));
personmodel.setBirthday(resultSet.getString("birthday"));
personmodel.setPhone(resultSet.getString("phone"));
personmodel.setEmail(resultSet.getString("email"));
list.add(personmodel);
}
return list;
}
public static List<Personmodel> queryallperson() throws SQLException {
Connection connection = DButil.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from person");
while (resultSet.next()) {
Personmodel personmodel = new Personmodel();
personmodel.setId(resultSet.getInt("id"));
personmodel.setName(resultSet.getString("name"));
personmodel.setSex(resultSet.getString("sex"));
personmodel.setBirthday(resultSet.getString("birthday"));
personmodel.setPhone(resultSet.getString("phone"));
personmodel.setEmail(resultSet.getString("email"));
personmodels.add(personmodel);
}
return personmodels;
}
}
这里面写的是数据库的增删改查操作， 首先看第一个方法是增加方法addperson()大家看方法里面代码，先获得connection对象，然后获得预编译对象 preparedStatement ，注意获得对象时调用方法的sql字符串参数，那个是将来要执行的SQL语句，下面preparedStatement的setxxx方法,里面第一个参数值对应sql字符串里面的相应?，第二个参数用了Personmodel的get方法（ preparedStatement .setxxx参数意义可百度或查阅api），然后你会问了，那是什么时候进行的set赋值呢？问得好，这时候Personaction类的意义就显示出来了，里面有相对应的方法对Persondao的增删改查补充完善的相应方法，采用该方式逻辑更加清晰，我举例了addperson()增加记录方法的解释，其他类似。
④如下：
package Action;
import java.sql.SQLException;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
import DBmodel.Personmodel;
import Dao.Persondao;
public class Personaction {
// private static Persondao persondao;
private static Scanner scanner = new Scanner(System.in);
public static void addone() throws SQLException {
Personmodel personmodel = new Personmodel();
System.out.println("请依次输入要新增记录的以下属性值~");
System.out.println("姓名:");
personmodel.setName(scanner.next());
System.out.println("性别:");
personmodel.setSex(scanner.next());
System.out.println("生日(例如：1994-01-01):");
personmodel.setBirthday(scanner.next());
System.out.println("电话:");
personmodel.setPhone(scanner.next());
System.out.println("邮箱:");
personmodel.setEmail(scanner.next());
Persondao.addperson(personmodel);
System.out.println("指定记录添加成功！");
}
public static boolean deleteone() throws SQLException {
List<Personmodel> personmodels = Persondao.queryallperson();
String stringid = null;
while (true) {
System.out.println("请输入要删除记录的指定id：");
stringid= scanner.next();
if (!stringid.matches("\\d+")) {
System.out.println("请输入正整数！");
continue;
}
break;
}
int id = Integer.valueOf(stringid);
for (Personmodel personmodel : personmodels) {
if (personmodel.getId() == id) {
Persondao.deleteperson(id);
System.out.println("删除成功，删除id为" + id + "的数据~");
return true;
}
}
System.out.println("删除失败，指定id不存在~");
return false;
}
public static boolean update() throws SQLException {
List<Personmodel> personmodels = Persondao.queryallperson();
String stringid = null;
while (true) {
System.out.println("请输入要修改记录的指定id：");
stringid= scanner.next();
if (!stringid.matches("\\d+")) {
System.out.println("请输入正整数！");
continue;
}
break;
}
int id = Integer.valueOf(stringid);
for (Personmodel personmodel2 : personmodels) {
if (personmodel2.getId() == id) {
Personmodel personmodel = new Personmodel();
personmodel.setId(id);
System.out.println("请依次输入要更改记录的以下属性值~");
System.out.println("姓名:");
personmodel.setName(scanner.next());
System.out.println("性别:");
personmodel.setSex(scanner.next());
System.out.println("生日(例如：1994-01-01):");
personmodel.setBirthday(scanner.next());
System.out.println("电话:");
personmodel.setPhone(scanner.next());
System.out.println("邮箱:");
personmodel.setEmail(scanner.next());
Persondao.updateperson(personmodel);
System.out.println("指定记录修改成功！");
return true;
}
}
System.out.println("要修改的id记录不存在！");
return false;
}
public static boolean selectone() throws SQLException {
List<Personmodel> personmodels = Persondao.queryallperson();
String stringid = null;
while (true) {
System.out.println("请输入要查询记录的指定id：");
stringid= scanner.next();
if (!stringid.matches("\\d+")) {
System.out.println("请输入正整数！");
continue;
}
break;
}
int id = Integer.valueOf(stringid);
for (Personmodel personmodel : personmodels) {
if (personmodel.getId() == id) {
personmodel= Persondao.queryoneperson(id);
System.out.println("查询成功，id为" + id + "的数据：");
System.out.print("|");
System.out.print("学生编号：" + personmodel.getId() + " ");
System.out.print("姓名：" + personmodel.getName() + " ");
System.out.print("性别：" + personmodel.getSex() + " ");
System.out.print("生日：" + personmodel.getBirthday() + " ");
System.out.print("电话：" + personmodel.getPhone() + " ");
System.out.print("邮件：" + personmodel.getEmail() + " ");
System.out.println();
return true;
}
}
System.out.println("查询失败，指定id不存在~");
return false;
}
public static boolean selectonebyname() throws SQLException {
List<Personmodel> personmodels = Persondao.queryallperson();
System.out.println("请输入要查询记录的指定姓名：");
String name = scanner.next();
for (Personmodel personmodel2 : personmodels) {
if (personmodel2.getName().equals(name)) {
List<Personmodel> list = Persondao.queryonepersonbyname(name);
for (int i = 0; i < list.size(); i++) {
Personmodel personmodel = list.get(i);
System.out.println("查询成功，姓名为" + name + "的数据：");
System.out.print("|");
System.out.print("学生编号：" + personmodel.getId() + " ");
System.out.print("姓名：" + personmodel.getName() + " ");
System.out.print("性别：" + personmodel.getSex() + " ");
System.out.print("生日：" + personmodel.getBirthday() + " ");
System.out.print("电话：" + personmodel.getPhone() + " ");
System.out.print("邮件：" + personmodel.getEmail() + " ");
System.out.println();
}
return true;
}
}
System.out.println("查询失败，指定id不存在~");
return false;
}
public static void selectall() throws SQLException {
List<Personmodel> personmodels = Persondao.queryallperson();
for (Personmodel personmodel : personmodels) {
System.out.print("|");
System.out.print("学生编号：" + personmodel.getId() + " ");
System.out.print("姓名：" + personmodel.getName() + " ");
System.out.print("性别：" + personmodel.getSex() + " ");
System.out.print("生日：" + personmodel.getBirthday() + " ");
System.out.print("电话：" + personmodel.getPhone() + " ");
System.out.print("邮件：" + personmodel.getEmail() + " ");
System.out.println();
}
}
}
刚才讲③的时候，为了大家的方便理解我顺带解释了④，大家看代码即可，我不再赘述啦。需要注意的一点是，我进行了控制台输入语句的判断，判断是否是合法类型。
最后是⑤，代码如下：
package View;
import java.sql.SQLException;
import java.util.Scanner;
import Action.Personaction;
import Dao.Persondao;
public class Personview {
public static Scanner scanner = new Scanner(System.in);
public static void main(String[] args) throws SQLException {
while (true) {
System.out.println("以下是数据库的各种操作：（标号为对应操作）");
System.out.println("0、退出数据库操作：");
System.out.println("1、查询全部记录：");
System.out.println("2、新增单条记录：");
System.out.println("3、更新单条记录：");
System.out.println("4、查询单条记录(通过id查询)：");
System.out.println("5、查询部分记录(通过name查询)：");
System.out.println("6、删除单条记录(通过id删除)：");
String actionid = scanner.next();
// ("\\d+")是正则表达是，判断是否是数字
if (!actionid.matches("\\d+")) {
System.out.println("请输入正整数！");
continue;
}
int idchose = Integer.valueOf(actionid);
if (idchose == 0) {
break;
}
switch (idchose) {
case 1:
Personaction.selectall();
break;
case 2:
Personaction.addone();
break;
case 3:
Personaction.update();
break;
case 4:
Personaction.selectone();
break;
case 5:
Personaction.selectonebyname();
break;
case 6:
Personaction.deleteone();
break;
case 7:
break;
default:
System.out.println("请选择0-7之间操作~");
break;
}
}
// persondao = new Persondao();
// Personaction.addone();
// Personaction.deleteone();
// Personaction.selectall();
// Personaction.selectone();
// Personaction.update();
// Personaction.selectonebyname();
}
}
注意看我的Personview的逻辑设计，主要采用了while循环的方式，不过也有相应的退出操作以及判断是否合法代码，感觉这种方式的设计还是相当容易看懂的。
以上要分享的代码都贴了，该说的也说了，相信大家也都看得明白吧，如果你觉得我的文章对你有帮助的话，还希望你点赞、分享、转发，让更多的人看到，互相学习，交流！ 好了，大家下篇文章见~~
