JDBC-DAO实现增删改查

原创 于 2020-09-11 19:56:22 发布 · 992 阅读 · 8 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细介绍了一个使用Java进行数据库操作的示例，包括数据库连接、基本的CRUD操作、日期转换和Person信息的封装。通过具体的代码实现，展示了如何在Java中有效管理数据库交互，包括错误处理和资源关闭。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

mysql建立Person表

在这里插入图片描述

列名类型描述
idint主键，自增1
namevarchar(20)非空
ageint非空
bornDateDate无
emailvarchar(20)无
addressvarchar(20)无

dbutil工具类

连接数据库，关闭连接

package person;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class dbutils {
	private static final Properties PROPERTIES = new Properties();
	static {
		InputStream inputStream =dbutils.class.getResourceAsStream("/db.properties");
		try {
			PROPERTIES.load(inputStream);
			Class.forName(PROPERTIES.getProperty("driver"));
		} catch (ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}
	public static Connection getconnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(
						PROPERTIES.getProperty("url"),
						PROPERTIES.getProperty("user"),
						PROPERTIES.getProperty("password")
						);
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return connection;
	}
	public static void clossAll(Connection connection,Statement statement,ResultSet resultSet) {
		try {
			if (resultSet!=null) {
				resultSet.close();
			}
			if (statement!=null) {
				statement.close();
			}
			if (connection!=null) {
				connection.close();
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}
}

DateUtils工具类

日期工具类 主要进行util.date sql.date string的转换

package person;

import java.text.ParseException;
import java.text.SimpleDateFormat;

//日期工具类
public class DateUtils {
	//1.string转util.date
	//2.util.date转sql.date
	//3.util.date转string
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
	
	public static java.util.Date strToUtil(String str){
		java.util.Date util_date=null;
		if(str==null){return null;}
		try {
			util_date = sdf.parse(str);
			return util_date;
		} catch (ParseException e) {
			// TODO 自动生成的 catch 块
			System.out.println("strToUtil错误");
			e.printStackTrace();
		}
		return null;
	}
	public static java.sql.Date utilToSql(java.util.Date util_date){
		if(util_date==null){return null;}
		return new java.sql.Date(util_date.getTime());
	}
	public static String utilToString(java.util.Date util_date) {
		if(util_date==null){return null;}
		return sdf.format(util_date);
	}
}

Person封装工具类

封装一条Person信息，包括id，name，age等

package person;

import java.util.Date;

public class Person {
	private int id;
	private String name;
	private int age;
	private Date bornDate;
	private String email;
	private String address;
	public Person() {
		// TODO 自动生成的构造函数存根
	}
	public Person(int id ,String name, int age, Date bornDate, String email, String address) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
		this.bornDate = bornDate;
		this.email = email;
		this.address = address;
	}
	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 int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public Date getbornDate() {
		return bornDate;
	}
	public void setbornDate(Date bornDate) {
		this.bornDate = bornDate;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "id=" + id + ", name=" + name + ", age=" + age + ", bornDate=" + bornDate + ", email=" + email
				+ ", address=" + address ;
	}
	
}

PersonDaoimpl类DAO操作封装类

对数据库进行增删改查（查单个.所有） 操作 内置增删改查多个方法
public class PersonDaoimpl {
//增

	public int insert(Person per) {
		Connection connection = null;
		PreparedStatement statement = null;
		int result=0;
		try {
			connection = dbutils.getconnection();
			statement = connection.prepareStatement(
					"insert into Person(name,age,bornDate,email,address) values(?,?,?,?,?);");
			statement.setString(1,per.getName());
			statement.setInt(2,per.getAge());
			//setDate是java.sql.Date getbornDate是java.util.Date
			//util.Date用getime()方法获取距1970毫秒值，根据此毫秒值创建sql.Date对象
			statement.setDate(3,DateUtils.utilToSql(per.getbornDate()));
			statement.setString(4, per.getEmail());
			statement.setString(5, per.getAddress());
			result = statement.executeUpdate();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			dbutils.clossAll(connection, statement, null);
		}
		return result;
		
	}

//删

	public int delete(int id) {
		int result = 0;
		Connection connection = null;
		PreparedStatement statement = null;
		String sql ="DELETE FROM person WHERE id=?;";
		try {
			connection = dbutils.getconnection();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			result = statement.executeUpdate();
			return result;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			dbutils.clossAll(connection, statement, null);
		}
		return 0;
	}

//改

	public int update(Person person) {
		Connection connection = null;
		PreparedStatement statement =null;
		String sql ="UPDATE person SET NAME=?,age=?,bornDate=?,email=?,address=? WHERE id=?;";
		try {
			connection = dbutils.getconnection();
			statement = connection.prepareStatement(sql);
			statement.setString(1, person.getName());
			statement.setInt(2, person.getAge());
			statement.setDate(3, DateUtils.utilToSql(person.getbornDate()));
			statement.setString(4, person.getEmail());
			statement.setString(5, person.getAddress());
			statement.setInt(6, person.getId());
			int result = statement.executeUpdate();
			return result;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			dbutils.clossAll(connection, statement, null);
		}
		return 0;
	}

//查单个

	public Person select(int id) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String sql = "select * from person where id=?;";
		Person person = null;
		try {
			connection = dbutils.getconnection();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				int pid = resultSet.getInt(1);
				String name = resultSet.getString(2);
				int age = resultSet.getInt(3);
				Date bornDate = resultSet.getDate(4);//sqlDate是utilDate的子类
				String email = resultSet.getString(5);
				String address = resultSet.getString(6);
				person = new Person(pid, name, age, bornDate, email, address);
			}else {
				System.out.println("没有查询到该用户");
			}
			return person;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			dbutils.clossAll(connection, statement, resultSet);
		}
		return null;
	}

//查所有

	public List<Person> selectall() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String sql = "select * from person;";
		Person person = null;
		List<Person> personlist = new ArrayList<Person>();
		try {
			connection = dbutils.getconnection();
			statement = connection.prepareStatement(sql);
			resultSet = statement.executeQuery();
			while(resultSet.next()) {
				int pid = resultSet.getInt(1);
				String name = resultSet.getString(2);
				int age = resultSet.getInt(3);
				Date bornDate = resultSet.getDate(4);//sqlDate是utilDate的子类
				String email = resultSet.getString(5);
				String address = resultSet.getString(6);
				person = new Person(pid, name, age, bornDate, email, address);
				personlist.add(person);
			}
			return personlist;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			dbutils.clossAll(connection, statement, resultSet);
		}
		return null;
	}

}

测试类

package person;

public class TestPerson {

	public static void main(String[] args)  {
		// TODO 自动生成的方法存根
		PersonDaoimpl persondao = new PersonDaoimpl();
		Person person = new Person(1,"ally",20,DateUtils.strToUtil("2020/9/11"),"123@qq.com","山东潍坊"); 
		int result = persondao.insert(person);
		if (result == 1) {
			System.out.println("新增成功");
		}else {
			System.out.println("新增失败");
		}
		result = persondao.delete(8);
		if (result == 1) {
			System.out.println("删除成功");
		}else {
			System.out.println("删除失败");
		}
		result = persondao.update(person);
		if (result == 1) {
			System.out.println("修改成功");
		}else {
			System.out.println("修改失败");
		}
		Person select = persondao.select(2);
		System.out.println("查询结果："+select.toString());
		List<Person> personlist = persondao.selectall();
		for (Person person2 : personlist) {
			System.out.println(person2.toString());
		}
	}

}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

静安书以沫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值