欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

使用JDBC链接数据库,对数据库进行增删改查

程序员文章站 2022-05-06 20:53:40
...

常用类和接口

连接带数据库(Connection),建立指令操作符(statement、preparedStatement),执行查询指令(executeQuery),获得查询结果(ResultSet)等。

代码演示:


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

/**
 * 关于工具类,一般情况下定义的方法全是静态方法
 * 不用创建对象,只加载一次,大大提高了效率
 * 直接使用类名,静态方法名
 * @author Administrator
 *
 */

public class DBUtil {
	public static Properties prop = new Properties();
	
	static {
		try {
			prop.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static String CLASS_NAME = prop.getProperty("class_name");
	public static String URL = prop.getProperty("url");
	public static String USERNAME = prop.getProperty("username");
    public static String PASSWORD = prop.getProperty("password");

	
	/**
	 * 连接数据库的方法Connection
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(CLASS_NAME);
			conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 封装获取Statement对象的方法
	 */
	public static Statement getStatement(Connection conn) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stmt;
	}
	
	/**
	 * 封装获取ResultSet对象的方法
	 */
	public static ResultSet getResultSet(Statement stmt,String sql) {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	/**
	 * 获取PreparedStatement对象
	 */
	public static PreparedStatement getPreparedStatement(Connection conn,String sql) {
		PreparedStatement prep = null;
		try {
			prep = conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return prep;
	}

	
	
	/**
	 * 关闭三个资源的方法
	 */
	public static void close(Connection conn,Statement stmt,ResultSet rs ) {
		close(conn, stmt);
		
		try {
			if (rs != null) {
				rs.close();
				//rs = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public static void close(Connection conn,Statement stmt) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * DML操作的静态方法(insert/update/delete)
	 * @param args
	 */
	public static int executeUpdate(Statement stmt,String sql) {
		int recordsCount = 0;
		try {
			recordsCount = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recordsCount;
	}
	
	
	/*
	 * 封装一个回滚的方法
	 * 
	 */
	
	public static void rollback(Connection conn) {
		try {
			conn.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		System.out.println(getConnection());
	}

}

      在上面的方法中,分别进行封装,得到了getconntion()、getStatement()、getPreparedStatement()、getResultSet()、close()、rollback()方法,同时建立了一个db.properties驱动文件,里面存放驱动类、url、用户名。密码和sql语句的信息

db.properties文件
class_name=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/shop
username=root
password=root

三、我们进行对数据库中的数据进行增删改查。

       建立一个user的类。

package com.tjise.dao;

import java.util.List;

import com.tjise.pojo.User;


/**
 * 关于User实体类的相关操作
 * @author Administrator
 *
 */

public interface UserDao {
	/**
	 * 查询所有数据的方法
	 */
	List<User> findAll();
	
	/**
	 * 添加
	 */
	void add(User user);
	
	/**
	 * 删除
	 */
	void delete(Integer id);
	/**
	 * 修改
	 */
	void update(User user);
	
	/**
	 * 根据id查询
	 */
	User findById(Integer id);
	
}

import java.sql.Timestamp;

/**
 * 成员变量私有,提供对外公开的方法set/get
 * @author Administrator
 */

public class User {
	
	private Integer id;
	private String username;
	private String password;
	private String phone;
	private String addr;
	private Timestamp rdate;//时间戳

	public User() {

	}

	public User(Integer id, String username, String password, String phone, String addr, Timestamp rdate) {
		this.id = id;
		this.username = username;
		this.password = password;
		this.phone = phone;
		this.addr = addr;
		this.rdate = rdate;
	}

	public User(String username, String password, String phone, String addr) {
		this.username = username;
		this.password = password;
		this.phone = phone;
		this.addr = addr;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public Timestamp getRdate() {
		return rdate;
	}

	public void setRdate(Timestamp rdate) {
		this.rdate = rdate;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", phone=" + phone + ", addr="
				+ addr + ", rdate=" + rdate + "]";
	}

	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((addr == null) ? 0 : addr.hashCode());
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((password == null) ? 0 : password.hashCode());
		result = prime * result + ((phone == null) ? 0 : phone.hashCode());
		result = prime * result + ((rdate == null) ? 0 : rdate.hashCode());
		result = prime * result + ((username == null) ? 0 : username.hashCode());
		return result;
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (addr == null) {
			if (other.addr != null)
				return false;
		} else if (!addr.equals(other.addr))
			return false;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (password == null) {
			if (other.password != null)
				return false;
		} else if (!password.equals(other.password))
			return false;
		if (phone == null) {
			if (other.phone != null)
				return false;
		} else if (!phone.equals(other.phone))
			return false;
		if (rdate == null) {
			if (other.rdate != null)
				return false;
		} else if (!rdate.equals(other.rdate))
			return false;
		if (username == null) {
			if (other.username != null)
				return false;
		} else if (!username.equals(other.username))
			return false;
		return true;
	}
	
}

Userdao.java

import java.util.List;

import com.tjise.pojo.User;


/**
 * 关于User实体类的相关操作
 * @author Administrator
 *
 */

public interface UserDao {
	/**
	 * 查询所有数据的方法
	 */
	List<User> findAll();
	
	/**
	 * 添加
	 */
	void add(User user);
	
	/**
	 * 删除
	 */
	void delete(Integer id);
	/**
	 * 修改
	 */
	void update(User user);
	
	/**
	 * 根据id查询
	 */
	User findById(Integer id);
	
}

UserDaoImpl.java

import java.sql.Connection;
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 com.tjise.dao.UserDao;
import com.tjise.pojo.User;
import com.tjise.util.DBUtil;



public class UserDaoImpl implements UserDao {
	
	public static Connection conn = DBUtil.getConnection();
	
	//MySQL数据库的数据访问
	@Override
	public List<User> findAll() {
		
		Statement stmt = null;
		ResultSet rs = null;
		
		List<User> users = null;
		String sql = "select * from t_user";
		
		conn = DBUtil.getConnection();
		stmt = DBUtil.getStatement(conn);
		rs = DBUtil.getResultSet(stmt, sql);
		users = new ArrayList<>();
		
		try {
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setPhone(rs.getString("phone"));
				user.setAddr(rs.getString("addr"));
				user.setRdate(rs.getTimestamp("rdate"));
				
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, stmt, rs);
		}
		
		return users;
	}

	@Override
	public void add(User user) {
		PreparedStatement prep= null;
		String sql = "insert into t_user values(null,?,md5(?),?,?,now())";
		
		prep = DBUtil.getPreparedStatement(conn, sql);
		try {
			prep.setObject(1, user.getUsername());
			prep.setObject(2, user.getPassword());
			prep.setObject(3, user.getPhone());
			prep.setObject(4, user.getAddr());
			
			prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, prep);
		}
		
	}

	@Override
	public void delete(Integer id) {
		
		PreparedStatement prep= null;
		String sql = "delete from t_user where id=?";
		
		prep = DBUtil.getPreparedStatement(conn, sql);
		try {
			prep.setObject(1, id);
			prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, prep);
		}
		
	}

	@Override
	public void update(User user) {
		PreparedStatement prep= null;
		String sql = "update t_user set username=?,password=?,phone=?,addr=? where id=?";
		
		conn = DBUtil.getConnection();
		prep = DBUtil.getPreparedStatement(conn, sql);
		
		try {
			prep.setObject(1, user.getUsername());
			prep.setObject(2, user.getPassword());
			prep.setObject(3, user.getPhone());
			prep.setObject(4, user.getAddr());
			prep.setObject(5, user.getId());
			
			prep.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, prep);
		}
		
	}
}


相关标签: JDBC连接