java封装JDBC操作数据库的方法：增、删、改、查

1.目录结构

2.需要依赖的jar

mysql-connector-java-5.1.42-bin.jar  MySQL数据库驱动的jar包

3.首先是对数据库的操作

首先是连接数据库，操作完成后再关闭数据库

package com.lykion.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConn {

	private static Connection conn;
	private static PreparedStatement ps;
	private static ResultSet rs;
	/**
	 * 连接数据库
	 * @return 
	 */
	public static Connection getConnection() {
		try {
			Class.forName(Contants.driver);		//加载mysql驱动
			System.out.println(Contants.driver + "加载成功！");
		} catch (ClassNotFoundException e) {
			System.out.println(Contants.driver + "加载失败(╯﹏╰)b");
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(Contants.url, Contants.username, Contants.password);		//连接数据库
			System.out.println(Contants.url + "连接成功！");
		} catch (SQLException e) {
			System.out.println(Contants.url + "连接失败(╯﹏╰)b");
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭数据库连接
	 * @throws SQLException
	 */
	public static void closeConnection() {
		if(ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		//关闭数据库
		}
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

4.连接数据库成功后，可以对数据库进行操作

package com.lykion.dao;

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.ArrayList;
import java.util.List;

public class DBUtil {

	/**
	 * 查询所有学生信息{多条查询}
	 * @return
	 */
	public static List<Student> getAllStudentInfo(){
		Connection conn = null;
		PreparedStatement ps = null;
//		Statement stmt;
		ResultSet rs = null;

		List<Student> stu = new ArrayList<Student>();

		try {
			conn = DBConn.getConnection();
			String sql = "SELECT * FROM student01";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String sno = rs.getString("sno");
				String sname = rs.getString("sname");
				String dname = rs.getString("dname");
				String ssex = rs.getString("ssex");
				int cno = rs.getInt("cno");
				int mark = rs.getInt("mark");
				String type = rs.getString("type");
				Student st = new Student(sno, sname, dname, ssex, cno, mark, type);
				stu.add(st);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return stu;
	}

	/**
	 * 通过学号Sno查询{单条查询}
	 * @param sno
	 * @return
	 */
	public static Student getStudentInfoBySno(String sno) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			conn = DBConn.getConnection();
			String sql = "SELECT * FROM student01 WHERE sno=?";
			ps = conn.prepareStatement(sql);
			ps.setNString(1, sno);
			rs = ps.executeQuery();
			while(rs.next()) {
				String sno1 = rs.getString("sno");
				String sname = rs.getString("sname");
				String dname = rs.getString("dname");
				String ssex = rs.getString("ssex");
				int cno = rs.getInt("cno");
				int mark = rs.getInt("mark");
				String type = rs.getString("type");
				stu = new Student(sno1, sname, dname, ssex, cno, mark, type);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(ps!= null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return stu;
	}

	/**
	 * 增加学生信息{插入学生信息}
	 * @param stu
	 */
	public static void insertStuInfo(Student stu) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConn.getConnection();
			String sql = "INSERT INTO student01 (sno,sname,dname,ssex,cno,mark,type) VALUES(?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			//设置占位符对应的值
			ps.setString(1, stu.getSno());
			ps.setString(2, stu.getSname());
			ps.setString(3, stu.getDname());
			ps.setString(4, stu.getSsex());
			ps.setInt(5, stu.getCno());
			ps.setFloat(6, stu.getMark());
			ps.setString(7, stu.getType());
			int insertCount = ps.executeUpdate();
			System.out.println(isSuccess(insertCount));
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 根据Sno删除学生信息
	 * @param sno
	 */
	public static void deleteStuInfo(String sno) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConn.getConnection();
			String sql = "DELETE FROM student01 WHERE sno = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, sno);
			int deleteCount = ps.executeUpdate();
			System.out.println(isSuccess(deleteCount));
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 修改学生信息
	 * @param stu
	 * @throws SQLException
	 */
	public static void modifyStuInfo(Student stu) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DBConn.getConnection();
			String sql = "UPDATE student01 SET sname=?, dname=?, ssex=?, cno=?, mark=?, type=? WHERE sno=?";
			ps = conn.prepareStatement(sql);
			//注意参数站位的位置
			//此处是通过序号sno去更新学生信息，所以sno的位置不是在第一个位置而是在最后一个位置
			ps.setString(1, stu.getSname());
			ps.setString(2, stu.getDname());
			ps.setString(3, stu.getSsex());
			ps.setInt(4, stu.getCno());
			ps.setFloat(5, stu.getMark());
			ps.setString(6, stu.getType());
			ps.setString(7, stu.getSno());

			int count = ps.executeUpdate();
			System.out.println(isSuccess(count));
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * 判断操作是否成功
	 * @param count
	 * @return
	 */
	public static String isSuccess(int count) {
		if(count > 0) {
			return "操作成功！";
		}else {
			return "操作失败";
		}
	}

	/**
	 * 增删改操作   {另一种封装增、删、查、改作方法}
	 * @param conn
	 * @param sql
	 * @param param
	 * @return
	 */
	public static int executeUpdate(Connection conn, String sql, Object[] param) {
		int result = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if(param != null) {
				for(int i=0; i<param.length; i++) {
					ps.setObject(i+1, param[i]);
				}
			}
			result = ps.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return result;
	}
}

5.创建Student实体类

package com.lykion.dao;

public class Student {

	private String sno;
	private String sname;
	private String dname;
	private String ssex;
	private int cno;
	private int mark;
	private String type;

	public Student() {

	}

	public Student(String sno, String sname, String dname, String ssex, int cno, int mark, String type) {
		super();
		this.sno = sno;
		this.sname = sname;
		this.dname = dname;
		this.ssex = ssex;
		this.cno = cno;
		this.mark = mark;
		this.type = type;
	}

	public String getSno() {
		return sno;
	}
	public void setSno(String sno) {
		this.sno = sno;
	}

	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}

	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}

	public String getSsex() {
		return ssex;
	}
	public void setSsex(String ssex) {
		this.ssex = ssex;
	}

	public int getCno() {
		return cno;
	}
	public void setCno(int cno) {
		this.cno = cno;
	}

	public float getMark() {
		return mark;
	}
	public void setMark(int mark) {
		this.mark = mark;
	}

	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
}

6.常量类：

创建常量类的目的就是方便修改数据连接等其他信息的修改

package com.lykion.dao;
/**
* 常量
* @author Uker
*
*/
public class Contants {


public static final String url = "jdbc:mysql://localhost:3306/test";//数据库地址
public static final String username = "root";//数据库用户名
public static final String password = "123456";//数据库密码
public static final String driver = "com.mysql.jdbc.Driver";//mysql驱动
}

7.创建测试类

用来测试对数据库的操作是否成功

package com.lykion.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

public class MainTest {

	public static void main(String[] args) {
		Connection conn = DBConn.getConnection();
		System.out.println("conn:"+conn);

//		/**
//		 * 测试获取所有学生信息{多条查询}
//		 */
//		List<Student> stu = DBUtil.getAllStudentInfo();
//		Iterator<Student> it = stu.iterator();
//		while(it.hasNext()) {
//			Student st = it.next();
//			System.out.println(st.getSno() +","+ st.getSname() +","+ st.getDname() +","+ st.getSsex() +","+ st.getCno() +","+ st.getMark() +","+ st.getType());
//		}

//		/**
//		 * 测试根据Sno查询学生信息{单条查询}
//		 */
//		String sno = "9701";
//		Student stu = DBUtil.getStudentInfoBySno(sno);
//		System.out.println(stu.getSno() +","+ stu.getSname() +","+ stu.getDname() +","+ stu.getSsex() +","+ stu.getCno() +","+ stu.getMark() +","+ stu.getType());

//		/**
//		 * 测试添加学生信息{insert学生信息}
//		 */
//		Student stu = new Student("9805", "邓光", "工程造价", "男", 8, 85, "必修");
//		DBUtil.insertStuInfo(stu);

//		/**
//		 * 测试根据学号sno删除学生信息
//		 */
//		String sno = "9805";
//		DBUtil.deleteStuInfo(sno);

		/**
		 * 测试修改学生信息
		 */
//		UPDATE student01 SET sname='董存瑞',dname='工程造价',ssex='女',cno=5,mark=95,type='必须' WHERE sno='2017';
		Student stu = new Student("2017" ,"朱啸天", "计算机科学与软件工程", "男", 8, 85, "必修");
			try {
				DBUtil.modifyStuInfo(stu);
			} catch (SQLException e) {
				e.printStackTrace();
			}

//		/**
//		 * 测试修改学生信息
//		 */
//		conn = DBConn.getConnection();
//		String sql = "UPDATE student01 SET sname = '老彭' WHERE sno = '9805'";
//		DBUtil.executeUpdate(conn, sql, null);

		DBConn.closeConnection();
	}
}