2021-4-13 JDBC连接数据库增删查的一些方法代码

package sun;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import com.mysql.jdbc.Statement;

import sun.util.ConnectionUtil;
import vo.Emp;

public class TestMysql {
public static void main(String[] args) throws Exception {
Emp emp=new Emp(“李四”,“职员”,7788,10.00,900.00,new Date(),40);
System.out.println(insertEmp(emp));
}

public static boolean insertEmp() throws Exception {
	// 取得Connection连接对象
	Connection conn = ConnectionUtil.getConnection();
	// 定义出sql语句
	String sql = "INSERT INTO emp(empno,ename,job,sal,hiredate,mgr,comm,deptno)"
			+ "VALUES(1006,'李四','总裁',9000.00,NOW(),7788,2000.00,10)";
	// 取得发送sql语句的对象
	PreparedStatement pst = conn.prepareStatement(sql);
	// 执行sql语句
	int row = pst.executeUpdate();
	ConnectionUtil.close(conn);
	return row > 0;
}

public static boolean updateDeposit(Double sum) throws SQLException {
	// 取得连接对象
	Connection conn = ConnectionUtil.getConnection();
	// 创建sql语句
	String sql1 = "UPDATE emp SET deposit=deposit-" + sum + "WHERE ename='SMITH'";
	String sql2 = "UPDATE emp SET deposit=deposit+" + sum + "WHERE ename='ALLEN'";
	// 取消事物的自动提交
	conn.setAutoCommit(false);
	int row1 = 0;
	int row2 = 0;
	try {
		// 取得预编译对象
		PreparedStatement pst1 = conn.prepareStatement(sql1);
		PreparedStatement pst2 = conn.prepareStatement(sql2);
		// 执行sql语句
		row1 = pst1.executeUpdate(sql1);
		row2 = pst2.executeUpdate(sql2);
		// 把之前的代码全部提交
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
		// 如果其中有代码出现了异常则事物回滚
		conn.rollback();
	} finally {
		// 关闭连接
		ConnectionUtil.close(conn);
	}
	return row1 > 0 && row2 > 0;
}

/**
 * 根据编号删除数据
 * 
 * @param id 要删除的数据的编号
 * @return
 * @throws Exception
 */
public static boolean deleteById(Integer id) throws Exception {
	Connection conn = ConnectionUtil.getConnection();
	String sql = "DELETE FROM emp WHERE empno=" + id;
	// 取得预编译对象
	PreparedStatement pst = conn.prepareStatement(sql);
	// 执行sql语句
	int row = pst.executeUpdate();
	// 关闭连接
	ConnectionUtil.close(conn);
	return row > 0;
}

/**
 * 实现数据的批量删除
 * 
 * @param ids 保存了我们要删除的数据的编号的集合
 * @return 如果删除成功返回true 否则返回false
 * @throws Exception
 */
public static boolean deleteBatch(Set<Integer> ids) throws Exception {
	// 取得连接
	Connection conn = ConnectionUtil.getConnection();
	// 准备sql语句
	StringBuffer sql = new StringBuffer("DELETE FROM emp WHERE empno IN(");
	Iterator<Integer> iter = ids.iterator();
	while (iter.hasNext()) {
		sql.append(iter.next() + ",");
	}
	// 删除最后一个逗号
	sql.delete(sql.length() - 1, sql.length());
	// 添加一个右括号
	sql.append(")");
	// 取得预编译对象
	PreparedStatement pst = conn.prepareStatement(sql.toString());
	// 执行sql语句
	int row = pst.executeUpdate();
	ConnectionUtil.close(conn);
	return row > 0;
}

/**
 * 根据编号查询数据
 * 
 * @param id 要查询的数据的编号
 * @throws Exception
 */
public static void selectByID(Integer id) throws Exception {
	// 取得连接对象
	Connection conn = ConnectionUtil.getConnection();
	// 准备sql语句
	try {
		String sql = "SELECT empno,ename,job,hiredate,sal,comm,mgr,deptno FROM emp WHERE empno=" + id;

		// 取得预编译对象
		PreparedStatement pst = conn.prepareStatement(sql);
		// 执行sql语句
		ResultSet rst = pst.executeQuery();
		if (rst.next()) {
			System.out.println("雇员编号:" + rst.getObject(1) + ",员工姓名:" + rst.getObject(2) + ",职位:" + rst.getObject(3)
					+ ",入职日期:" + rst.getObject(4) + ",薪资:" + rst.getObject(5) + ",佣金:" + rst.getObject(6) + ",领导编号:"
					+ rst.getObject(7) + ",部门编号:" + rst.getObject(8));
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		ConnectionUtil.close(conn);
	}
}
/**
 *  实现模糊分页查询
 * @param kw 模糊查询关键字
 * @param cp 当前页
 * @param ls 每页显示的数据
 * @throws Exception
 */
public static void selectSplitAll(String kw,Integer cp,Integer ls) throws Exception{
	if(kw==null) {
		kw="";
	}
	kw="'%"+kw+"%'";
	//取得连接对象
	Connection conn=ConnectionUtil.getConnection();
	//准备sql语句
	String sql="SELECT empno,ename,job,hiredate,sal,comm,mgr,deptno FROM emp"+
	" WHERE ename LIKE "+kw+" LIMIT "+(cp-1)*ls+","+ls;
	//取得预编译对象
	PreparedStatement pst=conn.prepareStatement(sql);
	//执行sql语句
	ResultSet rst=pst.executeQuery();
	while(rst.next()) {
		System.out.println("雇员编号:" + rst.getObject(1) + ",员工姓名:" + rst.getObject(2) + ",职位:" + rst.getObject(3)
		+ ",入职日期:" + rst.getObject(4) + ",薪资:" + rst.getObject(5) + ",佣金:" + rst.getObject(6) + ",领导编号:"
		+ rst.getObject(7) + ",部门编号:" + rst.getObject(8));
		//System.out.println("雇员编号:" + rst.getObject("empno") + ",员工姓名:" + rst.getObject("ename") + ",职位:" + rst.getObject("job")
			//		+ ",入职日期:" + rst.getObject("hiredate") + ",薪资:" + rst.getObject("sal") + ",佣金:" + rst.getObject("comm") + ",领导编号:"
			//		+ rst.getObject("mgr") + ",部门编号:" + rst.getObject("deptno"));
	}
	//关闭连接
	rst.close();
	pst.close();
	ConnectionUtil.close(conn);
}
/**
 *  增加雇员信息的方法，取得自动增长的主键值
 * @return 
 * @throws SQLException 
 */
public static boolean insertEmp1() throws SQLException{
	//取得Connection连接对象
	Connection conn=ConnectionUtil.getConnection();
	//准备sql语句
	String sql="INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno)"
	+"  VALUES('李四','总裁',9000.00,NOW(),7788,2000.00,10)";
	//取得预编译对象
	PreparedStatement pst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	//执行sql语句
	int row=pst.executeUpdate();
	//取得自动增长的主键值
	ResultSet rst=pst.getGeneratedKeys();
	if(rst.next()) {
		System.out.println(rst.getObject(1));
	}
	ConnectionUtil.close(conn);
	return row>0;
}
/**
 * 实现登陆
 * @param name 用户名
 * @param password  密码
 * @return
 */
public static boolean selectLogin(String name,String password) {
	//取得Connection对象
	Connection conn=ConnectionUtil.getConnection();
	String sql="SELECT * FROM myuser WHERE username=? AND password=?";
	System.out.println(sql);
	try {
		PreparedStatement pst=conn.prepareStatement(sql);
		//为占位符设置具体内容
		pst.setString(1, name);
		pst.setString(2, password);
		//发送sql语句
		ResultSet rst=pst.executeQuery();
		if(rst.next()) {
			System.out.println("登陆成功!");
		}else {
			System.out.println("用户密码不正确!");
		}
	}catch(SQLException e) {
		e.printStackTrace();
	}
	return false;
}
/**
 * 增加雇员的方法，通过vo类增加信息
 * @param emp
 * @return
 * @throws SQLException
 */
public static boolean insertEmp(Emp emp) throws SQLException{
	//取得Connection连接对象
	Connection conn=ConnectionUtil.getConnection();
	//定义出sql语句
	String sql="INSERT INTO emp(ename,job,sal,hiredate,mgr,comm,deptno)"+"  VALUES(?,?,?,?,?,?,?)";
	//取得发送sql语句对象
	PreparedStatement pst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	//为占位符赋值
	pst.setObject(1,emp.getEname());
	pst.setObject(2, emp.getJob());
	pst.setObject(3, emp.getSal());
	pst.setObject(4, emp.getHiredate());
	pst.setObject(5, emp.getMgr());
	pst.setObject(6, emp.getComm());
	pst.setObject(7, emp.getDeptno());
	//执行sql语句
	int row=pst.executeUpdate();
	//取得自动增长的主键值
	ResultSet rst=pst.getGeneratedKeys();
	if(rst.next()) {
		System.out.println(rst.getObject(1));
	}
	//关闭连接对象
	ConnectionUtil.close(conn);
	return row>0;
}
/**
 * 通过vo类保存对象实现模糊分页查询
 * @param kw 模糊查询的关键字
 * @param cp 当前页
 * @param ls 每页显示数据
 * @return
 * @throws Exception
 */
public static List<Emp> selectSplitAll1(String kw,Integer cp,Integer ls) throws Exception{
	//创建List集合保存Emp对象
	List<Emp> listEmps=new ArrayList<Emp>();
	if(kw==null) {
		kw="";
	}
	kw="%"+kw+"%";
	//取得连接对象
	Connection conn=ConnectionUtil.getConnection();
	//准备出sql语句
	String sql="SELECT empno,ename,job,hiredate,sal,comm,mgr,deptno FROM emp"+"  WhERE ename LIKE ? LIMIT ?,?";
	//取得预编译对象
	PreparedStatement pst=conn.prepareStatement(sql);
	//为占位符设置内容
	pst.setObject(1, kw);
	pst.setObject(2, (cp-1)*ls);
	pst.setObject(3, ls);
	//执行sql语句
	ResultSet rst=pst.executeQuery();
	while(rst.next()) {
		listEmps.add(new Emp(rst.getInt("empno"),rst.getString("ename"),
				rst.getString("job"),rst.getInt("mgr"),rst.getDate("hiredate"),
				rst.getDouble("sal"),rst.getDouble("comm"),rst.getInt("deptno")));
	}
	//关闭连接
	rst.close();
	pst.close();
	ConnectionUtil.close(conn);
	return listEmps;
}

}

工具类代码

package sun.util;

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

public class ConnectionUtil {
private static String URL = “jdbc:mysql://localhost:3306/demo?useSSL=true&useUnicode=true&characterEncoding=UTF-8”;
// 连接驱动的全名称
private static String DRIVER = “com.mysql.jdbc.Driver”;
// 用户名
private static String USER = “root”;
// 密码
private static String PASSWORD = “1234”;
// 加载驱动信息
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try{
return DriverManager.getConnection(URL,USER,PASSWORD);
}catch(Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

VO类代码
public class Emp implements Serializable {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Double sal;
private Double comm;
private Integer deptno;

setter以及getter，构造方法省略

参考学习地址

http://blog.mbzvip.tech/