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

一、常用类和接口

连接带数据库（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);
		}
		
	}
}